1、批量數(shù)據(jù)操作

1.1、批量生成數(shù)據(jù)

生成 1～5 之間的整數(shù)

SELECT ROWNUM,LEVEL,LAG(LEVEL) OVER(ORDER BY LEVEL) lag,LEAD(LEVEL) OVER(ORDER BY LEVEL) lead FROM DUAL CONNECT BY ROWNUM <= 5;

生成結(jié)果：

    ROWNUM      LEVEL        LAG       LEAD---------- ---------- ---------- ----------         1          1                     2         2          2          1          3         3          3          2          4         4          4          3          5         5          5          4

隨機(jī)生成 5 條數(shù)據(jù)

SELECT SYS_GUID() ora_guid,LEVEL inc_number,fn_now+LEVEL/24/3600 inc_date,
DBMS_RANDOM.STRING('X',8) random_string,
TRUNC(DBMS_RANDOM.VALUE(0,100000)) random_numberFROM DUAL CONNECT BY LEVEL <= 5;

生成結(jié)果：

ORA_GUID                         INC_NUMBER INC_DATE    RANDOM_STRING  RANDOM_NUMBER
-------------------------------- ---------- ----------- -------------- -------------44F51C83A2964B1F81C60DBBA8BD7206          1 2017-01-10  ESL1LWPB               838886336F565364849889FBDC817B761E315          2 2017-01-10  7HNXAHUR               14726C0B2BF9E4AAB4B95B9F1CB03A0582097          3 2017-01-10  ZZO8OAK3               8261582C4A6F29BCA46BE89CA797D74F391C6          4 2017-01-10  K8ZNTRS8               5453438F0552F1C724805A4D3E6AD54DEB43D          5 2017-01-10  4ZHL52OA               95298

構(gòu)造等差數(shù)列

SELECT LEVEL n1, LEVEL*2 n2, LEVEL*2-1 n3, fn_today+LEVEL-1 dt1, 
fn_today+NUMTOYMINTERVAL(LEVEL-1,'month') dt2,
fn_today+NUMTOYMINTERVAL(LEVEL-1,'year') dt3FROM DUAL CONNECT BY LEVEL <= 5;

構(gòu)造結(jié)果：

        N1         N2         N3 DT1         DT2         DT3---------- ---------- ---------- ----------- ----------- -----------        1          2          1 2017-01-10  2017-01-10  2017-01-10        2          4          3 2017-01-11  2017-02-10  2018-01-10        3          6          5 2017-01-12  2017-03-10  2019-01-10        4          8          7 2017-01-13  2017-04-10  2020-01-10        5         10          9 2017-01-14  2017-05-10  2021-01-10

構(gòu)造二階等差數(shù)列

SELECT LEVEL num,SUM(LEVEL) OVER(ORDER BY LEVEL) fac FROM DUAL CONNECT BY LEVEL <= 5;

構(gòu)造結(jié)果：

    NUM        FAC---------- ----------        1          1        2          3        3          6        4         10        5         15

1.2、批量插入數(shù)據(jù)

一次向 t3 表中插入 100 萬(wàn)條數(shù)據(jù)（在本人筆記本上操作耗時(shí)二十幾秒）

BEGIN
  FOR i IN 1 .. 1000000 LOOP
    INSERT INTO t3(f1) VALUES(i);  END LOOP;  COMMIT;END;

將 t2 表中的數(shù)據(jù)變成當(dāng)前的 2^5 倍（假如 t2 中原本有 3 條數(shù)據(jù)，那么最終 t2 表中的行數(shù)將是：3+(3*2^0)+(3*2^1)+(3*2^2)+(3*2^3)+(3*2^4)，即 3*2^5）

BEGIN
  FOR i IN 1 .. 5 LOOP
    INSERT INTO t2 SELECT t2.* FROM t2;  END LOOP;  COMMIT;END;

2、批量生成腳本

生成清空當(dāng)前用戶(hù)所有表中數(shù)據(jù)的語(yǔ)句

SELECT 'DELETE FROM '||LOWER(USER)||'.'||LOWER(t.table_name)||' t;' FROM USER_TABLES t;SELECT 'TRUNCATE TABLE '||LOWER(USER)||'.'||LOWER(t.table_name)||';' FROM USER_TABLES t;

生成刪除當(dāng)前用戶(hù)所有表的語(yǔ)句

SELECT 'DROP TABLE '||LOWER(USER)||'.'||LOWER(t.table_name)||' PURGE;' FROM USER_TABLES t;

生成刪除當(dāng)前用戶(hù)所有對(duì)象的語(yǔ)句

SELECT 'DROP '||t.object_type||' '||LOWER(USER)||'.'||LOWER(t.object_name)||';'FROM USER_OBJECTS t ORDER BY t.object_type;

生成禁用當(dāng)前用戶(hù)所有觸發(fā)器的語(yǔ)句

SELECT 'ALTER TRIGGER '||LOWER(USER)||'.'||LOWER(t.trigger_name)||' DISABLE;'FROM USER_TRIGGERS t WHERE t.status='ENABLED';

生成將當(dāng)前用戶(hù)所有 T_ 開(kāi)頭的表授權(quán)給 fox 的語(yǔ)句

SELECT 'GRANT SELECT ON '||LOWER(USER)||'.'||LOWER(t.table_name)||' TO fox;'FROM USER_TABLES t WHERE t.table_name LIKE 'T/_%' ESCAPE '/';

生成查詢(xún)T_COURSE表中數(shù)據(jù)的語(yǔ)句

SELECT 'SELECT '||WM_CONCAT('t.'||LOWER(t.column_name))
  ||' FROM '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))||' t WHERE 1=1;'FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

生成語(yǔ)句：

SELECT t.course_id,t.course_name,t.course_desc FROM demo.t_course t WHERE 1=1;

生成向T_COURSE表中插入數(shù)據(jù)的語(yǔ)句

風(fēng)格一：

SELECT 'INSERT INTO '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||'('||WM_CONCAT(LOWER(t.column_name))
  ||') VALUES('||WM_CONCAT(':'||LOWER(t.column_name))||');'FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

生成語(yǔ)句：

INSERT INTO demo.t_course(course_id,course_name,course_desc)VALUES(:course_id,:course_name,:course_desc);

風(fēng)格二：

SELECT 'INSERT INTO '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||'('||WM_CONCAT(LOWER(t.column_name))
  ||') VALUES('||WM_CONCAT(':'||REPLACE(INITCAP(t.column_name),'_',''))||');'FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

生成語(yǔ)句：

INSERT INTO demo.t_course(course_id,course_name,course_desc)VALUES(:CourseId,:CourseName,:CourseDesc);

生成修改T_COURSE表中數(shù)據(jù)的語(yǔ)句

風(fēng)格一：

SELECT 'UPDATE '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||' t SET '||WM_CONCAT('t.'||LOWER(t.column_name)||'=:'
  ||LOWER(t.column_name))||' WHERE 1=1;'FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

生成語(yǔ)句：

UPDATE demo.t_course tSET t.course_id=:course_id,t.course_name=:course_name,t.course_desc=:course_descWHERE 1=1;

風(fēng)格二：

SELECT 'UPDATE '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||' t SET '||WM_CONCAT('t.'||LOWER(t.column_name)||'=:'
  ||REPLACE(INITCAP(t.column_name),'_',''))||' WHERE 1=1;'FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

生成語(yǔ)句：

UPDATE demo.t_course tSET t.course_id=:CourseId,t.course_name=:CourseName,t.course_desc=:CourseDescWHERE 1=1;

3、生成數(shù)據(jù)字典

查詢(xún)語(yǔ)句：

SELECT t1.tablespace_name "表空間",USER "模式",t1.table_name "表名",NULL "字段序號(hào)",NULL "字段名稱(chēng)",NULL "數(shù)據(jù)類(lèi)型",NULL "字段長(zhǎng)度",NULL "精度",NULL "小數(shù)位",NULL "能否為空",NULL "默認(rèn)值",t2.comments "注釋"FROM USER_TABLES t1LEFT JOIN USER_TAB_COMMENTS t2 ON t1.table_name=t2.table_nameUNION ALLSELECT t1.tablespace_name "表空間",USER "模式",t1.table_name "表名",
t3.column_id "字段序號(hào)",t3.column_name "字段名稱(chēng)",t3.data_type "數(shù)據(jù)類(lèi)型",
t3.data_length "字段長(zhǎng)度",t3.data_precision "精度",t3.data_scale "小數(shù)位",
t3.nullable "能否為空",t3.data_default "默認(rèn)值",t2.comments "注釋"FROM USER_TABLES t1LEFT JOIN USER_COL_COMMENTS t2 ON t1.table_name=t2.table_nameLEFT JOIN USER_TAB_COLUMNS t3 ON t1.table_name=t3.table_name AND t2.column_name=t3.column_nameORDER BY "表名","字段序號(hào)" NULLS FIRST;

查詢(xún)結(jié)果（限于篇幅，這里僅截取了部分結(jié)果集）：

表空間  模式  表名      字段序號(hào) 字段名稱(chēng)     數(shù)據(jù)類(lèi)型  字段長(zhǎng)度  精度 小數(shù)位 能否為空 默認(rèn)值 注釋
------- ----- --------- -------- ------------ --------- -------- ----- ------ -------- ------ ------------
USERS   DEMO  T_COURSE                                                                        
USERS   DEMO  T_COURSE         1 COURSE_ID    NUMBER          22    10      0 N               課程主鍵ID
USERS   DEMO  T_COURSE         2 COURSE_NAME  VARCHAR2        50              Y               課程名稱(chēng)
USERS   DEMO  T_COURSE         3 COURSE_DESC  VARCHAR2      2000              Y               課程描述
......

注意：這里有一個(gè)非常有意思的現(xiàn)象，通過(guò) PL/SQL Developer 查詢(xún)得到的結(jié)果集中，默認(rèn)值data_default字段是 LONG 類(lèi)型的。看到這個(gè)之后我曾想在查詢(xún)語(yǔ)句將其轉(zhuǎn)換成字符串，后來(lái)發(fā)現(xiàn) Oracle 并未提供 LONG 類(lèi)型轉(zhuǎn)字符類(lèi)型的函數(shù)或語(yǔ)法，非要轉(zhuǎn)的話(huà)還得自己寫(xiě)函數(shù)，總之相當(dāng)繁瑣。后來(lái)我發(fā)現(xiàn)如果通過(guò) PL/SQL Developer 的結(jié)果集窗口直接把數(shù)據(jù)導(dǎo)出到 Excel 之后，默認(rèn)值列會(huì)自動(dòng)轉(zhuǎn)換成字符串。再后來(lái)我又發(fā)現(xiàn)通過(guò)命令窗口執(zhí)行查詢(xún)語(yǔ)句也會(huì)自動(dòng)把默認(rèn)值列自動(dòng)轉(zhuǎn)換成字符串?？傊痪湓?huà)，不用自己費(fèi)心費(fèi)力的去轉(zhuǎn)換 LONG 類(lèi)型了，直接通過(guò) PL/SQL Developer 生成數(shù)據(jù)字典即可。

4、常見(jiàn)系統(tǒng)包

為了便于開(kāi)發(fā) PL/SQL 程序，Oracle 數(shù)據(jù)庫(kù)提供了數(shù)以百計(jì)的系統(tǒng)包。本機(jī)將會(huì)重點(diǎn)講解其中幾個(gè)常見(jiàn)系統(tǒng)包及常用方法。

4.1、DBMS_OUTPUT

DBMS_OUTPUT包的主要功能就是在 PL/SQL 程序中輸入或輸出消息，譬如可以通過(guò)它在存儲(chǔ)過(guò)程和觸發(fā)器中向緩沖區(qū)發(fā)送調(diào)試消息。

常用子程序的語(yǔ)法及說(shuō)明：

DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); -- 向緩沖區(qū)輸出數(shù)據(jù)并換行
DBMS_OUTPUT.PUT(item IN VARCHAR2); -- 向緩沖區(qū)追加數(shù)據(jù)，但不換行也不顯示，執(zhí)行 NEW_LINE 或 PUT_LINE 就能把之前的數(shù)據(jù)全都顯示出來(lái)
DBMS_OUTPUT.NEW_LINE; -- 向緩沖區(qū)輸出一個(gè)換行
DBMS_OUTPUT.DISABLE; -- 用于關(guān)閉輸入和輸出，同時(shí)清空緩沖區(qū)
DBMS_OUTPUT.ENABLE([buffer_size IN NUMBER]); -- 用于開(kāi)啟輸入和輸出

綜合示例：

BEGIN
  DBMS_OUTPUT.PUT_LINE('A'); -- 輸出 A
  DBMS_OUTPUT.DISABLE;       -- 禁用 DBMS_OUTPUT 并清除 A
  DBMS_OUTPUT.PUT('B');      -- 因?yàn)橐殃P(guān)閉輸出，所以不會(huì)追加 B
  DBMS_OUTPUT.ENABLE;        -- 啟用 DBMS_OUTPUT
  DBMS_OUTPUT.PUT('C');      -- 追加 C
  DBMS_OUTPUT.PUT('D');      -- 追加 D
  DBMS_OUTPUT.NEW_LINE;      -- 輸出 CD 并換行
  DBMS_OUTPUT.PUT_LINE('E'); -- 輸出 E 并換行
  DBMS_OUTPUT.PUT('F');      -- 追加 F，但后面沒(méi)有 NEW_LINE 或 PUT_LINE，所以不會(huì)顯示END;

輸出結(jié)果：

CDE

4.2、DBMS_RANDOM

DBMS_RANDOM包提供了一個(gè)內(nèi)置的隨機(jī)數(shù)生成器，可用它來(lái)快速生成隨機(jī)數(shù)和隨機(jī)字符串。

RANDOM：返回一個(gè) [-2^31, 2^31) 范圍內(nèi)的整數(shù)。

SELECT DBMS_RANDOM.RANDOM res FROM DUAL; -- res: -699438152

NORMAL：返回正態(tài)分布中的隨機(jī)數(shù)。此正態(tài)分布標(biāo)準(zhǔn)偏差為 1，期望值為 0。這個(gè)函數(shù)返回的數(shù)值中有 68% 是介于 -1 與 +1 之間，95% 介于 -2 與 +2 之間，99% 介于 -3 與 +3 之間。

SELECT DBMS_RANDOM.NORMAL res FROM DUAL; -- res: 0.763005475791809

STRING(opt IN CHAR,len IN NUMBER)：返回一個(gè)隨機(jī)字符串，其中 opt 指的是字符串的格式，len 指的是字符串的長(zhǎng)度。

SELECT DBMS_RANDOM.STRING('u', 10) res FROM DUAL; -- res: ADKXBWIOMI，全大寫(xiě)字母SELECT DBMS_RANDOM.STRING('l', 10) res FROM DUAL; -- res: mupmuqdoue，全小寫(xiě)字母SELECT DBMS_RANDOM.STRING('a', 10) res FROM DUAL; -- res: AdOhEwGByt，混合大小寫(xiě)字母SELECT DBMS_RANDOM.STRING('x', 10) res FROM DUAL; -- res: OMUBEPN3C2，大寫(xiě)字母或數(shù)字SELECT DBMS_RANDOM.STRING('p', 10) res FROM DUAL; -- res: b+[5$ot=w|，任意可打印字符

VALUE：返回 [0, 1) 范圍內(nèi)的隨機(jī)數(shù)，精度為 38 位。

SELECT DBMS_RANDOM.VALUE res FROM DUAL; -- res: 0.381593460771342

VALUE(low IN NUMBER,high IN NUMBER)：返回 [low, high) 范圍內(nèi)的隨機(jī)數(shù)。

SELECT DBMS_RANDOM.VALUE(10,20) res FROM DUAL; -- res: 13.650786652248

INITIALIZE(val IN BINARY_INTEGER) & SEED(seed IN BINARY_INTEGER|VARCHAR2)：設(shè)置用來(lái)初始化DBMS_RANDOM包的種子值。INITIALIZE 和 SEED 唯一的區(qū)別就是，INITIALIZE 只支持?jǐn)?shù)字，而 SEED 既支持?jǐn)?shù)字又支持字符串。另外，SEED 的作用之一是用來(lái)取代 INITIALIZE 的。

在默認(rèn)情況下，DBMS_RANDOM包是根據(jù)用戶(hù)、時(shí)間、會(huì)話(huà)等信息來(lái)進(jìn)行初始化的，換句話(huà)說(shuō)，即便是同一個(gè)語(yǔ)句，每次生成時(shí)的種子也是不確定的。這時(shí)候就可以通過(guò) INITIALIZE 或 SEED 來(lái)設(shè)置一個(gè)固定的種子，確保每次生成時(shí)的隨機(jī)序列一致。

BEGIN
  DBMS_RANDOM.SEED('ABC123'); -- 設(shè)置種子值 ABC123
  FOR i IN 3 .. 9 LOOP
    DBMS_OUTPUT.PUT(DBMS_RANDOM.RANDOM||'|');  END LOOP;
  DBMS_OUTPUT.NEW_LINE;END;

輸出結(jié)果：

-219386465|-850200733|-240588365|-351313939|-1206831363|852217108|-1045006337|

4.3、其它系統(tǒng)包及常用方法

DBMS_METADATA包中的GET_DDL方法用于獲取存儲(chǔ)在數(shù)據(jù)字典中的對(duì)象定義語(yǔ)句（DDL 語(yǔ)句），返回值是 CLOB 類(lèi)型的。

語(yǔ)法：

DBMS_METADATA.GET_DDL(
  object_type IN VARCHAR2,
  name        IN VARCHAR2,
  schema      IN VARCHAR2 DEFAULT NULL,
  version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model       IN VARCHAR2 DEFAULT 'ORACLE',
  transform   IN VARCHAR2 DEFAULT 'DDL');

示例：

http://www.cnblogs.com/hanzongze/p/Oracle-plsql-4.html