0 . 查看系統(tǒng)表空間

select * from dba_tablespaces

1 . 查看表結(jié)構(gòu)

desc table_name

2 . 查詢數(shù)據(jù)庫SID

select name from v$database;

3 . 用 B 表的數(shù)據(jù)更新 A 表數(shù)據(jù) ( 有關(guān)聯(lián)的字段 )

UPDATE A SET name=(SELECT name FROM B WHERE
A.id=B.id) WHERE B.id IS NOT NULL ；

4 . 隨機數(shù)函數(shù) DBMS_RANDOM.RANDOM

SQL> select dbms_random.random from dual

5 . 如何在字符串里加回車即舌？

SQL> select 'line1'||chr(10)||'line2' from dual;

6 . 如何使 select 語句使查詢結(jié)果自動生成序號 ?

SQL> select rownum, fieldname from table;

7 . 怎么可以快速做一個和原表一樣的備份表 ?

create table new_table as (select * from old_table) 砂客；

8 . 如何單獨備份一個或多個表？

exp 用戶 / 密碼 tables=( 表 1 ， … 马绝，表 2)

9. 如何單獨備份一個或多個用戶嫂拴？

exp system/manager owner=( 用戶 1 ，用戶 2 杠园， … 顾瞪，用戶 n) file= 導(dǎo)出文件

10 . 如何執(zhí)行腳本 SQL 文件 ?

SQL>@$PATH/filename.sql ;

11. 如何快速清空一個大表 ?

SQL>truncate table table_name ;

12 . 字符串的連接

select concat(col1 ， col2) from table ;
select col1||col2 from table ;

13 . 怎么把 select 出來的結(jié)果導(dǎo)到一個文本文件中抛蚁？

SQL> spool c:\test.txt;
SQL> select * from emp;
SQL> spool off ;

14. 如何測試 SQL 語句執(zhí)行所用的時間 ?

SQL>set timing on ;
SQL>select * from tablename ;

15 . 改變字段大小 ?

-- 改大行陈醒，改小不行（除非都是空的）
SQL>alter table table_name modify (field_name varchar2(100)) ;

16 . 如何查詢某天的數(shù)據(jù) ?

-- datefield為要查詢的日期字段
SQL>select * from table_name where trunc( datefield ) ＝ to_date('2003-05-02' ，'yyyy-mm-dd') ;

17. 如何修改表名 ?

SQL> alter table old_table_name rename to new_table_name;

18 . 如何搜索出前 N 條記錄瞧甩？

SQL> SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;

-- 搜索一個范圍的數(shù)據(jù) 
SQL> SELECT * FROM (SELECT ROWNUM RN,EMPNO,ENAME FROM EMP) WHERE RN > 5 AND RN < 10 ORDER BY empno;

19 . 怎樣用 Sql 語句實現(xiàn)查找一列中第 N 大值钉跷？

SQL> select * from (select t.* , dense_rank() over (order by sal) rank from employee) where rank = N ；

20 . 如何在給現(xiàn)有的日期加上 2 年肚逸？

SQL> select add_months(sysdate 尘应， 24) from dual 惶凝；

21 . 返回大于等于 N 的最小整數(shù)值 ?

SQL> SELECT CEIL(N) FROM DUAL ；

22 . 返回小于等于 N 的最小整數(shù)值 ?

SQL> SELECT FLOOR(N) FROM DUAL 犬钢；

23 . 返回當(dāng)前月的最后一天 ?

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL 苍鲜；

24 . 如何不同用戶間數(shù)據(jù)導(dǎo)入 ?

IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y ；

25 . 如何找數(shù)據(jù)庫表的主鍵字段的名稱 ?

SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME' 玷犹；

26 . 兩個結(jié)果集互加的函數(shù) ?

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW 混滔；
SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;
SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW ；

27 . 兩個結(jié)果集互減的函數(shù) ?

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW 歹颓；

28. 如何配置 Sequence?

• 創(chuàng)建sequence

create sequence seq_cust start 1 incrememt by

• 建表

create table cust { 
    cust_id smallint not null,
    ...
}

• insert 數(shù)據(jù)

insert into table cust values( seq_cust.nextval, ... )
insert into table cust values( seq_cust.currval, ... )

29 . 日期各部分的寫法

-- 取時間點的年份的寫法：
SELECT TO_CHAR(SYSDATE 坯屿， 'YYYY') FROM DUAL ；
-- 取時間點的月份的寫法：
SELECT TO_CHAR(SYSDATE 巍扛， 'MM') FROM DUAL 领跛；
-- 取時間點的日的寫法：
SELECT TO_CHAR(SYSDATE ， 'DD') FROM DUAL 撤奸；
-- 取時間點的時的寫法：
SELECT TO_CHAR(SYSDATE 吠昭， 'HH24') FROM DUAL ；
-- 取時間點的分的寫法：
SELECT TO_CHAR(SYSDATE 胧瓜， 'MI') FROM DUAL 矢棚；
-- 取時間點的秒的寫法：
SELECT TO_CHAR(SYSDATE ， 'SS') FROM DUAL 府喳；
-- 取時間點的日期的寫法：
SELECT TRUNC(SYSDATE) FROM DUAL 蒲肋；
-- 取時間點的時間的寫法：
SELECT TO_CHAR(SYSDATE ， 'HH24 ： MI ： SS') FROM DUAL 钝满；
-- 日期兜粘，時間形態(tài)變?yōu)樽址螒B(tài)：
SELECT TO_CHAR(SYSDATE) FROM DUAL ；
-- 將字符串轉(zhuǎn)換成日期或時間形態(tài)：
SELECT TO_DATE('2003/08/01') FROM DUAL 弯蚜；
-- 返回參數(shù)的星期幾的寫法：
SELECT TO_CHAR(SYSDATE 孔轴， 'D') FROM DUAL ；
-- 返回參數(shù)一年中的第幾天的寫法：
SELECT TO_CHAR(SYSDATE 熟吏， 'DDD') FROM DUAL 距糖；
-- 返回午夜和參數(shù)中指定的時間值之間的秒數(shù)的寫法：
SELECT TO_CHAR(SYSDATE ， 'SSSSS') FROM DUAL 牵寺；
-- 返回參數(shù)中一年的第幾周的寫法：
SELECT TO_CHAR(SYSDATE 悍引， 'WW') FROM DUAL ；

30 . ROWNUM

-- 按設(shè)定排序的行的序號
SELECT * FROM emp WHERE ROWNUM < 10 帽氓；

31. 如何查找重復(fù)記錄趣斤？

SELECT * FROM TABLE_NAME WHERE ROWID!=( 
    SELECT MAX(ROWID) 
        FROM TABLE_NAME D 
        WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2
    );

32 . 如何刪除重復(fù)記錄？

DELETE FROM TABLE_NAME WHERE ROWID!=( 
    SELECT MAX(ROWID) 
        FROM TABLE_NAME D 
        WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2
    );

33 . 賦予權(quán)限

GRANT
  CONNECT,                
  RESOURCE,               
  --DBA,                  
  --unlimited tablespace,
  CREATE  SESSION,         
  CREATE ANY SEQUENCE,     
  CREATE ANY TABLE,        
  CREATE ANY VIEW ,        
  CREATE ANY INDEX,        
  CREATE ANY PROCEDURE,    
  CREATE ANY DIRECTORY,    
  ALTER  SESSION, 
  ALTER ANY SEQUENCE,     
  ALTER ANY TABLE,        
  --ALTER ANY VIEW ,        --不能修改視圖
  ALTER ANY INDEX,        
  ALTER ANY PROCEDURE,    
  --ALTER ANY DIRECTORY,    --不能修改目錄
  --DROP  SESSION,       --不能刪除Session
  DROP ANY SEQUENCE,     
  DROP ANY TABLE,        
  DROP ANY VIEW ,        
  DROP ANY INDEX,        
  DROP ANY PROCEDURE,    
  DROP ANY DIRECTORY,    
  SELECT ANY TABLE, 
  SELECT ANY DICTIONARY,
  INSERT ANY TABLE, 
  UPDATE ANY TABLE, 
  DELETE ANY TABLE,
  DEBUG ANY PROCEDURE,
  DEBUG CONNECT SESSION,
  exp_full_database,  
  imp_full_database     
TO user;

34 . 導(dǎo)出視圖數(shù)據(jù)

  create table v_table as( select * from view_table);

35 . 解決Temp01.dbf不斷變大的問題

第一步：
alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' drop;

第二步：
alter tablespace temp add tempfile
'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF'
size 2048M reuse autoextend on next 100M;

第三步：
select d.file_name, d.file_id, d.tablespace_name, d.bytes 
from dba_temp_files d;

第四步：
alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' autoextend off; 

36 . 行轉(zhuǎn)列的多記錄合并

1 . 使用 WM_CONCAT 函數(shù)

SQL> select wmsys.wm_concat(distinct name) from t; 
SQL> select id,wmsys.wm_concat(distinct name) con_name from t group by id;  

ID   CON_NAME  
--------------  
1     a,b,c  
2     aa,bb  

2 . 使用 STRAGG

SQL> SELECT TRIM(',' FROM SYS.STRAGG(NAME||NVL2(NAME,',',''))) CON_NAME FROM T;  
  
CON_NAME  
----------  
a,b,c,aa,bb  

3 . 使用 partition by

select id,sys_connect_by_path(name,'>')  con_name  
    from ( select id,name,row_number() over( partition by id order by name) rn  from (select id,name from t )) t  
start 
    with t.rn=1  
connect by 
    t.id=prior t.id and t.rn-1=prior t.rn  

    ID CON_NAME  
------- --------------------  
     1 >a  
     1 >a>b  
     1 >a>b>c  
     2 >aa  
     2 >aa>bb  
     2 >aa>bb>china  
     2 >aa>bb>china>china  

select id,substr(max(sys_connect_by_path(name,'>')),2)  con_name  
  from (select id,name,row_number() over(partition by id order by name) rn  from (select id,name from t ))t  
  start with t.rn=1  
  connect by t.id=prior t.id and t.rn-1=prior t.rn  
  group by id  

   ID CON_NAME  
------- --------------------  
    1 a>b>c  
    2 aa>bb>china>china  

4 . 使用 XMLAGG 函數(shù)

RTRIM(XMLAGG(XMLPARSE(CONTENT field || ',' WELLFORMED)) .GETCLOBVAL(), ',')

5 . 自定義類型 VARCHAR2

-- create function of strcat 
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type; 

-- create type define of strcat_type
create or replace type strcat_type as object 
( 
    currentstr varchar2(4000), 
    currentseprator varchar2(8), 
    static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number, 
    member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number, 
    member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number, 
    member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number 
) 

-- create type body of strcat_type
create or replace type body strcat_type is 
      static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is 
      begin 
        sctx := strcat_type('',','); 
        return ODCIConst.Success; 
      end; 
      member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is 
      begin 
        if self.currentstr is null then 
           self.currentstr := value; 
        else 
          self.currentstr := self.currentstr ||currentseprator || value; 
        end if; 
        return ODCIConst.Success; 
      end; 
      member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is 
      begin 
        returnValue := self.currentstr; 
        return ODCIConst.Success; 
      end; 
      member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is 
      begin 
        if ctx2.currentstr is null then 
          self.currentstr := self.currentstr; 
        elsif self.currentstr is null then 
          self.currentstr := ctx2.currentstr; 
        else 
          self.currentstr := self.currentstr || currentseprator || ctx2.currentstr; 
        end if; 
        return ODCIConst.Success; 
      end; 
      end; 

6 . 自定義類型 clob

-- create function of strcat 
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN clob PARALLEL_ENABLE AGGREGATE USING strcat_type;

-- create type define of strcat_type
create or replace type strcat_type as object
(
  CURR_STR clob,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT strcat_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type, P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type, RETURNVALUE OUT clob, FLAGS IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER
)

-- create type body of strcat_type
create or replace type body strcat_type is
  static function ODCIAggregateInitialize(sctx IN OUT strcat_type)
    return number is
  BEGIN
    SCTX := strcat_type(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type,P1 IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type,  RETURNVALUE OUT clob,  FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;

37 . 去掉重復(fù)字符串

  /****************************************************
  ** Oracle去掉重復(fù)字符串
  ** 函數(shù)名稱：RemoveSameStr
  ** 參    數(shù)：【名稱】         【類型 】      【說明】
  **                 oldStr           varchar2       要處理的字符串
  **                 sign               varchar2       字符串分隔符
  ** 返 回 值： result          varchar2        不包含重復(fù)子串的記錄
  ****************************************************/

create or replace function rm_same_str(oldStr clob, sign varchar2)
  return varchar2 is
    str          clob;
    currentIndex number;
    startIndex   number;
    endIndex     number;
    type str_type is table of varchar2(1000) index by binary_integer;
    arr str_type;
    Result varchar2(4000);
begin
    -- null string
    if oldStr is null then
    return('');
    end if;

    --string is too long
    if length(oldStr) > 32000 then
    return(oldStr);
    end if;
    str := oldStr;

    currentIndex := 0;
    startIndex   := 0;

    loop
    currentIndex := currentIndex + 1;
    endIndex     := instr(str, sign, 1, currentIndex);
    if (endIndex <= 0) then
      exit;
    end if;

    arr(currentIndex) :=substr(str,startIndex + 1,  endIndex - startIndex - 1);

    -- arr(currentIndex) := trim(substr(str,startIndex + 1,endIndex - startIndex - 1));
    startIndex := endIndex;
    end loop;

    --get the last string
    arr(currentIndex) := substr(str, startIndex + 1, length(str));

    --remove the same string
    for i in 1 .. currentIndex - 1 loop
    for j in i + 1 .. currentIndex loop
      if arr(i) = arr(j) then
        arr(j) := '';
      end if;
    end loop;
    end loop;

    str := '';
    for i in 1 .. currentIndex loop
    if arr(i) is not null then
      str := str || sign || arr(i);
      --set arrar to null:
      arr(i) := '';
    end if;
    end loop;

    --remove the prev mark
    Result := substr(str, 2, length(str));

    return(Result);
end rm_same_str;

38 . 主要函數(shù)

• INITCAP(CHAR)：將字符串 CHAR 的第一個字符為大寫,其余為小寫黎休。

SELECT INITCAP('ABCDE') FROM DUAL ;

• LENGTH(CHAR) : 取一字符串 CHAR 的長度浓领。

SELECT LENGTH('ABCDE') FROM DUAL ;

• LOWER(CHAR) ：將字符串 CHAR 全部變?yōu)樾憽?/li>

SELECT LOWER('ABCDE') FROM DUAL ;

• LPAD(CHAR1 , N , CHAR2) ：用字符串 CHAR2 包括的字符左填 CHAR1 ,使其長度為 N 玉凯。

SELECT LPAD('ABCDEFG' , 10'123') FROM DUAL ;
-- 結(jié)果: '123ABCDEFG'

• LTRIM(CHAR , SET) ：從字符串 CHAR 的左邊移去字符串 SET 中的字符,直到第一個不是 SET 中的字符為

SELECT ('CDEFG' , 'CD') FROM DUAL ;
-- 結(jié)果: 'EFG'

• NLS_INITCAP(CHAR)：取字符 CHAR 的第一個字符大寫,其余字符為小寫。

SELECT NLS_INITCAP('ABCDE') FROM DUAL ;

• NLS_LOWER(CHAR)：將字符串 CHAR 包括的字符全部小寫联贩。

SELECT NLS_LOWER('AAAA') FROM DUAL ;

• NLS_UPPER(CHAR)：將字符串 CHAR 包括的字符全部大寫漫仆。

SELECT NLS_UPPER('AAAA') FROM DUAL ;

• REPLACE(CHAR1 , CHAR2 , CHAR3)：用字符串 CHAR3 代替每一個列值為 CHAR2 的列,其結(jié)果放在 CHAR1 中。

SELECT REPLACE(EMP_NO , '123' , '456') FROM DUAL ;

• RPAD(CHAR1 , N , CHAR2)：用字符串 CHAR2 右填字符串 CHAR1 ,使其長度為 N 泪幌。

SELECT RPAD('234' , 8 , '0') FROM DUAL ;

• RTRIM(CHAR , SET)：移去字符串 CHAR 右邊的字符串 SET 中的字符,直到最后一個不是 SET 中的字符為止盲厌。

SELECT RTRIM('ABCDE' , 'DE') FROM DUAL ;

• SUBSTR(CHAR , M , N)：得到字符串 CHAR 從 M 處開始的 N 個字符 . 雙字節(jié)字符,一個漢字為一個字符的。

SELECT SUBSTR('ABCDE' , 2 , 3) FROM DUAL ;

• SUBSTRB(CHAR , M , N)：得到字符串 CHAR 從 M 處開始的 N 個字符 . 雙字節(jié)字符,一個漢字為二個字符的祸泪。

SELECT SUBSTRB('ABCDE' , 2 , 3) FROM DUAL ;

• TRANSLATE(CHAR1 , CHAR2 , CHAR3)：將 CHAR1 中的 CHAR2 的部分用 CHAR3 代替吗浩。

SELECT TRANSLATE('ABCDEFGH' , 'DE' , 'MN') FROM DUAL ;

• UPPER(CHAR)：將字符串 CHAR 全部為大寫。
• ADD_MONTHS(D , N)：將 N 個月增加到 D 日期没隘。

SELECT ADD_MONTHS(SYSDATE , 5) FROM DUAL ;

• LAST_DAY(D)：得到包含 D 日期的月份的最后的一天的日期懂扼。

SELECT LAST_DAY(SYSDATE) FROM DUAL ;

• MONTH_BETWEEN(D1 , D2)：得到兩個日期之間的月數(shù)。

SELECT MONTH_BETWEEN(D1 , D2) FROM DUAL ;

• NEXT_DAY(D , CHAR)：得到比日期 D 晚的由 CHAR 命名的第一個周日的日期右蒲。

SELECT NEXT_DAY(TO_DATE('2003/09/20') , 'SATDAY') FROM DUAL ;

• ROUNT(D , FMT)：得到按指定的模式 FMT 舍入到的最進的日期阀湿。

SELECT ROUNT('2003/09/20' , MONTH) FROM DUAL ;

• SYSDATE：得到當(dāng)前系統(tǒng)的日期和時間。

SELECT SYSDATE FROM DUAL ;

• TO_CHAR(D , FMT)：將日期 D 轉(zhuǎn)換為 FMT 的字符串品嚣。

SELECT TO_CHAR(SYSDATE , 'YYYY/MM/DD') FROM DUAL ;

• TO_DATE(CHAR , FMT)：將字符串 CHAR 按 FMT 的格式轉(zhuǎn)換為日期炕倘。

SELECT TO_DATE('2003/09/20' , 'YYYY/MM/DD') FROM DUAL ;

• ABS(N)：得到 N 的絕對值钧大。

SELECT ABS(-6) FROM DUAL ;

• EXP(N)：得到 N 的 E 的 N 次冪翰撑。

SELECT EXP(1) FROM DUAL ;

• MOD(M , N)：得到 M 除以 N 的余數(shù)。

SELECT MOD(100 , 7) FROM DUAL ;

• POWER(M , N)：得到 M 的 N 冪啊央。

SELECT POWER(4 , 3) FROM DUAL ;

• ROUND(N , M)：將 N 舍入到小數(shù)點后 M 位眶诈。

SELECT (78.87653 , 2) FROM DUAL ;

• SIGN(N)：當(dāng) N<0 時,得到 -1 ;當(dāng) N>0 時,得到 1 ;當(dāng) N=0 時,得到 0 ;

SELECT SIGN(99) FROM DUAL ;

• TRUNC(N , M)：得到在 M 位截斷的 N 的值。

SELECT TRUNC(7.7788 , 2) FROM DUAL ;

• COUNT()：計算滿足條件的記錄數(shù)瓜饥。

SELECT COUNT(*) FROM TABLE1 WHERE COL1='AAA' ;

• MAX()：對指定的列求最大值逝撬。

SELECT MAX(COL1) FROM TABLE1 ;

• MIN()：對指定的列求最小值。

SELECT MIN(COL1) FROM TABLE1 ;

• AVG()：對指定的列求平均值乓土。

SELECT AVG(COL1) FROM TABLE1 ;

• SUM()：計算列的和宪潮。

SELECT SUM(COL1) FROM DUAL ;

• TO_NUMBER(CHAR)：將字符轉(zhuǎn)換為數(shù)值。

SELECT TO_NUMBER('999') FROM DUAL ;

• empty_b|clob()：返回一個空的 LOB 定位符 , 用在初始化 LOB 變量 , 或用在 INSERT 及 UPDATE 聲明去初始 化
  LOB 列或?qū)⑵鋵傩灾脼榭铡?/li>

INSERT INTO TABLE1 VALUES(EMPTY_BLOB()) ;
UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB() ;

• NVL(EXPR1 , EXPR2)：若 EXPR1 是 NULL ,則返回 EXPR2 ,否則返回 EXPR1 趣苏。

SELECT NAME , NVL(TO_CHAR(COMM) , 'NOT APPLICATION') FROM TABLE1 ;

39. CASE WHEN 和 DECODE

1 . case表達式

--簡單Case函數(shù)  
CASE sex  
    WHEN '1' THEN '男'  
    WHEN '2' THEN '女'  
    ELSE '其他' 
END  

--Case搜索函數(shù)  
CASE
    WHEN sex = '1' THEN '男'  
    WHEN sex = '2' THEN '女'  
    ELSE '其他' 
END  

2 . CASE WHEN 在語句中不同位置的用法

• 2.1 SELECT 用法

SELECT 
    grade,
    COUNT ( CASE WHEN sex = 1 THEN 1 ELSE NULL END) 男生數(shù), 
    COUNT ( CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生數(shù)
FROM 
    students
GROUP BY  
    grade

• 2.2 WHERE 用法

SELECT 
    T2.*, T1.*
FROM 
    T1, T2
WHERE ( 
    CASE 
        WHEN T2.COMPARE_TYPE  = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1
        WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1
        ELSE 0
    END ) = 1

• 2.3 GROUP BY 用法

SELECT  
    CASE 
        WHEN salary <= 500 THEN '1'  WHEN salary > 500 AND salary <= 600  THEN '2'  
        WHEN salary > 600 AND salary <= 800  THEN '3'  
        WHEN salary > 800 AND salary <= 1000 THEN '4'  
        ELSE NULL 
    END salary_class, -- 別名命名
    COUNT(*)  
FROM    
    A  
GROUP BY  
    CASE 
        WHEN salary <= 500 THEN '1'  
        WHEN salary > 500 AND salary <= 600  THEN '2'  
        WHEN salary > 600 AND salary <= 800  THEN '3'  
        WHEN salary > 800 AND salary <= 1000 THEN '4'  
        ELSE NULL 
    END;  

3 . DECODE() 函數(shù)

基本語法： decode(條件, 值1, 返回值1, 值2, 返回值2, ..., 值n, 返回值n, 缺省值)

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees;

40. 修改clob字段為varchar2

alter table tablename add (new_column varchar2(4000));
update tablename set new_column=dbms_lob.substr(old_column,1000,1);
alter table tablename drop column old_column;
alter table tablename rename column new_column to old_column;

41. 使用正則表達式查詢

--非正整數(shù) 
select 字段 from 表 where regexp_replace(字段,'\d','') is not null;

--非數(shù)值類型
select 字段 from 表 where regexp_replace(字段,'^[-\+]?\d+(\.\d+)?$','') is not null;

--自定義函數(shù),判斷非值類型
create or replace function isnumber(col varchar2) return   i number;
begin
  i := to_number(col);
  return 1;
exception
  when others then
    return 0;
end;

select 字段 from 表 where isnumber(字段)=0;

42. 判斷某個字段的值是不是數(shù)字

共有三種方法狡相，分別是使用to_number()、regexp_like()和translate()三種函數(shù)來進行匹配食磕。如果字段的值是數(shù)字尽棕，返回1，否則返回0彬伦，具體的實現(xiàn)如下滔悉，三種方法任選壹種即可伊诵。運行結(jié)果：如果字符串是數(shù)字格式則返回1，不是則返回0回官。

--1曹宴、利用 to_number
CREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2)
    RETURN NUMBER
IS
    v_str FLOAT;
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       BEGIN
          SELECT TO_NUMBER (str)
            INTO v_str
            FROM DUAL;
       EXCEPTION
          WHEN INVALID_NUMBER
          THEN
             RETURN 0;
       END;
       RETURN 1;
    END IF;
END isnumeric;
/

--2、利用 regexp_like
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
    RETURN NUMBER
IS
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       IF regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')
       THEN
          RETURN 1;
       ELSE
          RETURN 0;
       END IF;
    END IF;
END isnumeric;
/

--3歉提、利用 translate
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
    RETURN NUMBER
IS
    v_str VARCHAR2 (1000);
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       v_str := translate(str, '.0123456789', '.');

       IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL
       THEN
          RETURN 1;
       ELSE
          RETURN 0;
       END IF;
    END IF;
END isnumeric;

由于 Oracle 數(shù)據(jù)庫本身沒有提供檢測字段是否為數(shù)字的方法浙炼，因此我們需要自己創(chuàng)建壹個 isnumeric() 的函數(shù)，然后再調(diào)用它來進行判斷唯袄。具體的調(diào)用方式如下：

SQL> select isnumeric('123a') from dual;
ISNUMERIC('123A')
-----------------
                0

SQL> select isnumeric('123.509') from dual;
ISNUMERIC('123.509')
--------------------
                1

SQL> select isnumeric('123.205.10.8') from dual;
ISNUMERIC('123.205.10.8')
-------------------------
                0

43. 重復(fù)記錄查詢

1弯屈、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段 xxxId 來判斷

select * from 
  xxx_table
where 
  xxxId in (select xxxId from  xxx_table group by xxxId having count(xxxId) > 1)

2恋拷、刪除表中多余的重復(fù)記錄资厉，重復(fù)記錄是根據(jù)單個字段 xxxId 來判斷，只留有rowid最小的記錄

delete from 
  xxx_table 
where 
  xxxId in (select xxxId from xxx_table group by xxxId having count(xxxId) > 1) and 
  rowid not in (select min(rowid) from xxx_table group by xxxId having count(xxxId)>1)

3蔬顾、查找表中多余的重復(fù)記錄（多個字段）

select * from 
  xxx_table a
where 
  (a.xxxIdA,a.xxxIdB) in   (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1)

4宴偿、刪除表中多余的重復(fù)記錄（多個字段），只留有rowid最小的記錄

delete from xxx_table a
where (a.xxxIdA,a.xxxIdB) in   (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1)
and rowid not in (select min(rowid) from xxx_table group by xxxIdA,xxxIdB having count(*)>1)

5诀豁、查找表中多余的重復(fù)記錄（多個字段）窄刘，不包含rowid最小的記錄

select * from 
  xxx_table a
where 
  (a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1) and
  rowid not in (select min(rowid) from xxx_table group by xxxIdA,xxxIdB having count(*)>1)