国产99视频精品,精品久久高潮,日本久久一本中文字幕

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

select * from dba_tablespaces

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

desc table_name

2 . 查詢數(shù)據(jù)庫(kù)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 . 隨機(jī)數(shù)函數(shù) `DBMS_RANDOM.RANDOM`

SQL> select dbms_random.random from dual

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

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

6 . 如何使 select 語(yǔ)句使查詢結(jié)果自動(dòng)生成序號(hào) ?

SQL> select rownum, fieldname from table;

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

create table new_table as (select * from old_table) ；

8 . 如何單獨(dú)備份一個(gè)或多個(gè)表？

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

9. 如何單獨(dú)備份一個(gè)或多個(gè)用戶？

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

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

SQL>@$PATH/filename.sql ;

11. 如何快速清空一個(gè)大表 ?

SQL>truncate table table_name ;

12 . 字符串的連接

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

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

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

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

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;

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

19 . 怎樣用 Sql 語(yǔ)句實(shí)現(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ù)庫(kù)表的主鍵字段的名稱 ?

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

26 . 兩個(gè)結(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 . 兩個(gè)結(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 . 日期各部分的寫法

-- 取時(shí)間點(diǎn)的年份的寫法：
SELECT TO_CHAR(SYSDATE ， 'YYYY') FROM DUAL ；
-- 取時(shí)間點(diǎn)的月份的寫法：
SELECT TO_CHAR(SYSDATE ， 'MM') FROM DUAL ；
-- 取時(shí)間點(diǎn)的日的寫法：
SELECT TO_CHAR(SYSDATE ， 'DD') FROM DUAL ；
-- 取時(shí)間點(diǎn)的時(shí)的寫法：
SELECT TO_CHAR(SYSDATE ， 'HH24') FROM DUAL ；
-- 取時(shí)間點(diǎn)的分的寫法：
SELECT TO_CHAR(SYSDATE ， 'MI') FROM DUAL ；
-- 取時(shí)間點(diǎn)的秒的寫法：
SELECT TO_CHAR(SYSDATE ， 'SS') FROM DUAL ；
-- 取時(shí)間點(diǎn)的日期的寫法：
SELECT TRUNC(SYSDATE) FROM DUAL ；
-- 取時(shí)間點(diǎn)的時(shí)間的寫法：
SELECT TO_CHAR(SYSDATE ， 'HH24 ： MI ： SS') FROM DUAL ；
-- 日期，時(shí)間形態(tài)變?yōu)樽址螒B(tài)：
SELECT TO_CHAR(SYSDATE) FROM DUAL ；
-- 將字符串轉(zhuǎn)換成日期或時(shí)間形態(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í)間值之間的秒數(shù)的寫法：
SELECT TO_CHAR(SYSDATE ， 'SSSSS') FROM DUAL ；
-- 返回參數(shù)中一年的第幾周的寫法：
SELECT TO_CHAR(SYSDATE ， 'WW') FROM DUAL ；

30 . ROWNUM

-- 按設(shè)定排序的行的序號(hào)
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)列的多記錄合并

id	name
1	a
1	b
1	c
2	aa
2	bb

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 的第一個(gè)字符為大寫,其余為小寫。

SELECT INITCAP('ABCDE') FROM DUAL ;

LENGTH(CHAR) : 取一字符串 CHAR 的長(zhǎng)度。

SELECT LENGTH('ABCDE') FROM DUAL ;

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

SELECT LOWER('ABCDE') FROM DUAL ;

LPAD(CHAR1 , N , CHAR2) ：用字符串 CHAR2 包括的字符左填 CHAR1 ,使其長(zhǎng)度為 N 。

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

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

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

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

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 代替每一個(gè)列值為 CHAR2 的列,其結(jié)果放在 CHAR1 中。

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

RPAD(CHAR1 , N , CHAR2)：用字符串 CHAR2 右填字符串 CHAR1 ,使其長(zhǎng)度為 N 。

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

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

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

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

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

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

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 個(gè)月增加到 D 日期。

SELECT ADD_MONTHS(SYSDATE , 5) FROM DUAL ;

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

SELECT LAST_DAY(SYSDATE) FROM DUAL ;

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

SELECT MONTH_BETWEEN(D1 , D2) FROM DUAL ;

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

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

ROUNT(D , FMT)：得到按指定的模式 FMT 舍入到的最進(jìn)的日期。

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

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

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 的絕對(duì)值。

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ù)點(diǎn)后 M 位。

SELECT (78.87653 , 2) FROM DUAL ;

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

SELECT SIGN(99) FROM DUAL ;

TRUNC(N , M)：得到在 M 位截?cái)嗟?N 的值。

SELECT TRUNC(7.7788 , 2) FROM DUAL ;

COUNT()：計(jì)算滿足條件的記錄數(shù)。

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

MAX()：對(duì)指定的列求最大值。

SELECT MAX(COL1) FROM TABLE1 ;

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

SELECT MIN(COL1) FROM TABLE1 ;

AVG()：對(duì)指定的列求平均值。

SELECT AVG(COL1) FROM TABLE1 ;

SUM()：計(jì)算列的和。

SELECT SUM(COL1) FROM DUAL ;

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

SELECT TO_NUMBER('999') FROM DUAL ;

empty_b|clob()：返回一個(gè)空的 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表達(dá)式

--簡(jiǎn)單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 在語(yǔ)句中不同位置的用法

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ù)

基本語(yǔ)法： 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. 使用正則表達(dá)式查詢

--非正整數(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. 判斷某個(gè)字段的值是不是數(shù)字

共有三種方法，分別是使用to_number()、regexp_like()和translate()三種函數(shù)來進(jìn)行匹配。如果字段的值是數(shù)字，返回1，否則返回0，具體的實(shí)現(xiàn)如下，三種方法任選壹種即可。運(yù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ù)庫(kù)本身沒有提供檢測(cè)字段是否為數(shù)字的方法，因此我們需要自己創(chuàng)建壹個(gè) isnumeric() 的函數(shù)，然后再調(diào)用它來進(jìn)行判斷。具體的調(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ù)單個(gè)字段 xxxId 來判斷

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

2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段 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ù)記錄（多個(gè)字段）

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ù)記錄（多個(gè)字段），只留有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ù)記錄（多個(gè)字段），不包含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)

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

ORACLE編程詳解