.Net程序员学用Oracle系列(29)：PLSQL 之批量应用和系统包
1、批量数据操作
1.1、批量生成数据
生成 1～5 之间的整数
SELECT ROWNUM,LEVEL,LAG(LEVEL) OVER(ORDER BY LEVEL) lag,LEAD(LEVEL) OVER(ORDER BY LEVEL) lead
FROM DUAL CONNECT BY ROWNUM <= 5;
生成结果：
ROWNUM LEVEL LAG LEAD
---------- ---------- ---------- ----------
1 1 2
2 2 1 3
3 3 2 4
4 4 3 5
5 5 4
随机生成 5 条数据
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_number
FROM DUAL CONNECT BY LEVEL <= 5;
生成结果：
ORA_GUID INC_NUMBER INC_DATE RANDOM_STRING RANDOM_NUMBER
-------------------------------- ---------- ----------- -------------- -------------
44F51C83A2964B1F81C60DBBA8BD7206 1 2017-01-10 ESL1LWPB 83888
6336F565364849889FBDC817B761E315 2 2017-01-10 7HNXAHUR 14726
C0B2BF9E4AAB4B95B9F1CB03A0582097 3 2017-01-10 ZZO8OAK3 82615
82C4A6F29BCA46BE89CA797D74F391C6 4 2017-01-10 K8ZNTRS8 54534
38F0552F1C724805A4D3E6AD54DEB43D 5 2017-01-10 4ZHL52OA 95298
构造等差数列
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') dt3
FROM DUAL CONNECT BY LEVEL <= 5;
构造结果：
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
构造二阶等差数列
SELECT LEVEL num,SUM(LEVEL) OVER(ORDER BY LEVEL) fac FROM DUAL CONNECT BY LEVEL <= 5;
构造结果：
NUM FAC
---------- ----------
1 1
2 3
3 6
4 10
5 15
1.2、批量插入数据
一次向 t3 表中插入 100 万条数据
循环写法（在本人笔记本上操作耗时20几秒）
BEGIN
FOR i IN 1 .. 1000000 LOOP
INSERT INTO t3(f1) VALUES(i);
END LOOP;
COMMIT;
END;
集合写法（在本人笔记本上操作耗时3点几秒）
BEGIN
INSERT INTO t3(f1) SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=1000000;
COMMIT;
END;
直接路径（在本人笔记本上操作耗时1点几秒）
CREATE TABLE t4 AS SELECT LEVEL f1 FROM DUAL CONNECT BY LEVEL<=1000000;
并行写法（在本人笔记本上操作耗时0点几秒）
CREATE TABLE t5 NOLOGGING PARALLEL 4 AS SELECT LEVEL f1 FROM DUAL CONNECT BY LEVEL<=1000000;
将 t2 表中的数据变成当前的 2^5 倍（假如 t2 中原本有 3 条数据，那么最终 t2 表中的行数将是：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、批量生成脚本
生成清空当前用户所有表中数据的语句
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;
生成删除当前用户所有表的语句
SELECT 'DROP TABLE '||LOWER(USER)||'.'||LOWER(t.table_name)||' PURGE;' FROM USER_TABLES t;
生成删除当前用户所有对象的语句
SELECT 'DROP '||t.object_type||' '||LOWER(USER)||'.'||LOWER(t.object_name)||';'
FROM USER_OBJECTS t ORDER BY t.object_type;
生成禁用当前用户所有触发器的语句
SELECT 'ALTER TRIGGER '||LOWER(USER)||'.'||LOWER(t.trigger_name)||' DISABLE;'
FROM USER_TRIGGERS t WHERE t.status='ENABLED';
生成将当前用户所有 T_ 开头的表授权给 fox 的语句
SELECT 'GRANT SELECT ON '||LOWER(USER)||'.'||LOWER(t.table_name)||' TO fox;'
FROM USER_TABLES t WHERE t.table_name LIKE 'T/_%' ESCAPE '/';
生成查询T_COURSE表中数据的语句
SELECT 'SELECT '||WM_CONCAT('t.'||LOWER(t1.column_name))
||' FROM '||LOWER(USER)||'.'||LOWER(MAX(t1.table_name))
||' t WHERE t.'||LOWER(MAX(t2.column_name))||'=:'||LOWER(MAX(t2.column_name))
FROM USER_TAB_COLUMNS t1,USER_TAB_COLUMNS t2
WHERE t1.table_name=t2.table_name AND t2.column_id=1 AND t1.table_name=UPPER('t_course');
生成的语句：
SELECT t.course_id,t.course_name,t.course_desc FROM demo.t_course t WHERE t.course_id=:course_id
生成向T_COURSE表中插入数据的语句
风格一：
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=UPPER('t_course');
生成的语句：
INSERT INTO demo.t_course(course_id,course_name,course_desc) VALUES(:course_id,:course_name,:course_desc)
风格二：
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=UPPER('t_course');
生成的语句：
INSERT INTO demo.t_course(course_id,course_name,course_desc) VALUES(:CourseId,:CourseName,:CourseDesc)
生成修改T_COURSE表中数据的语句
风格一（将 column_id=1 的字段做为条件）：
SELECT 'UPDATE '||LOWER(USER)||'.'||LOWER(MAX(t1.table_name))
||' t SET '||WM_CONCAT('t.'||LOWER(t1.column_name)||'=:'||LOWER(t1.column_name))
||' WHERE t.'||LOWER(MAX(t2.column_name))||'=:'||LOWER(MAX(t2.column_name))
FROM USER_TAB_COLUMNS t1,USER_TAB_COLUMNS t2
WHERE t1.table_name=t2.table_name AND t1.column_id>1 AND t2.column_id=1
AND t1.table_name=UPPER('t_course');
生成的语句：
UPDATE demo.t_course t SET t.course_name=:course_name,t.course_desc=:course_desc WHERE t.course_id=:course_id
风格二（将 column_id=1 的字段做为条件）：
SELECT 'UPDATE '||LOWER(USER)||'.'||LOWER(MAX(t1.table_name))||' t SET '
||WM_CONCAT('t.'||LOWER(t1.column_name)||'=:'||REPLACE(INITCAP(t1.column_name),'_',''))
||' WHERE t.'||LOWER(MAX(t2.column_name))||'=:'||LOWER(MAX(t2.column_name))
FROM USER_TAB_COLUMNS t1,USER_TAB_COLUMNS t2
WHERE t1.table_name=t2.table_name AND t1.column_id>1 AND t2.column_id=1
AND t1.table_name=UPPER('t_course');
生成的语句：
UPDATE demo.t_course t SET t.course_name=:CourseName,t.course_desc=:CourseDesc WHERE t.course_id=:course_id
生成添加或修改T_COURSE表注释的语句
SELECT 'COMMENT ON TABLE '||LOWER(t1.table_name)||' IS '''||t1.comments||''';' sqltext
FROM USER_TAB_COMMENTS t1 WHERE t1.table_name=UPPER('t_course')
UNION ALL
SELECT 'COMMENT ON COLUMN '||LOWER(t.table_name)||'.'||LOWER(t.column_name)||' IS '''||t.comments||''';'
FROM USER_COL_COMMENTS t WHERE t.table_name=UPPER('t_course');
3、生成数据字典
查询语句：
SELECT t1.tablespace_name "表空间",USER "模式",t1.table_name "表名",
NULL "字段序号",NULL "字段名称",NULL "数据类型",NULL "字段长度",
NULL "精度",NULL "小数位",NULL "能否为空",NULL "默认值",t2.comments "注释"
FROM USER_TABLES t1
LEFT JOIN USER_TAB_COMMENTS t2 ON t1.table_name=t2.table_name
UNION ALL
SELECT t1.tablespace_name "表空间",USER "模式",t1.table_name "表名",
t3.column_id "字段序号",t3.column_name "字段名称",t3.data_type "数据类型",
t3.data_length "字段长度",t3.data_precision "精度",t3.data_scale "小数位",
t3.nullable "能否为空",t3.data_default "默认值",t2.comments "注释"
FROM USER_TABLES t1
LEFT JOIN USER_COL_COMMENTS t2 ON t1.table_name=t2.table_name
LEFT JOIN USER_TAB_COLUMNS t3 ON t1.table_name=t3.table_name AND t2.column_name=t3.column_name
ORDER BY "表名","字段序号" NULLS FIRST;
查询结果（限于篇幅，这里仅截取了部分结果集）：
表空间 模式 表名 字段序号 字段名称 数据类型 字段长度 精度 小数位 能否为空 默认值 注释
------- ----- --------- -------- ------------ --------- -------- ----- ------ -------- ------ ------------
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 课程名称
USERS DEMO T_COURSE 3 COURSE_DESC VARCHAR2 2000 Y 课程描述
......
注意：这里有一个非常有意思的现象，通过 PL/SQL Developer 查询得到的结果集中，默认值data_default字段是 LONG 类型的。看到这个之后我曾想在查询语句将其转换成字符串，后来发现 Oracle 并未提供 LONG 类型转字符类型的函数或语法，非要转的话还得自己写函数，总之相当繁琐。后来我发现如果通过 PL/SQL Developer 的结果集窗口直接把数据导出到 Excel 之后，默认值列会自动转换成字符串。再后来我又发现通过命令窗口执行查询语句也会自动把默认值列自动转换成字符串。总之一句话，不用自己费心费力的去转换 LONG 类型了，直接通过 PL/SQL Developer 生成数据字典即可。
4、常见系统包
为了便于开发 PL/SQL 程序，Oracle 数据库提供了数以百计的系统包。本机将会重点讲解其中几个常见系统包及常用方法。
4.1、DBMS_OUTPUT
DBMS_OUTPUT包的主要功能就是在 PL/SQL 程序中输入或输出消息，譬如可以通过它在存储过程和触发器中向缓冲区发送调试消息。
常用子程序的语法及说明：
DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); -- 向缓冲区输出数据并换行
DBMS_OUTPUT.PUT(item IN VARCHAR2); -- 向缓冲区追加数据，但不换行也不显示，执行 NEW_LINE 或 PUT_LINE 就能把之前的数据全都显示出来
DBMS_OUTPUT.NEW_LINE; -- 向缓冲区输出一个换行
DBMS_OUTPUT.DISABLE; -- 用于关闭输入和输出，同时清空缓冲区
DBMS_OUTPUT.ENABLE([buffer_size IN NUMBER]); -- 用于开启输入和输出
综合示例：
BEGIN
DBMS_OUTPUT.PUT_LINE('A'); -- 输出 A
DBMS_OUTPUT.DISABLE; -- 禁用 DBMS_OUTPUT 并清除 A
DBMS_OUTPUT.PUT('B'); -- 因为已关闭输出，所以不会追加 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，但后面没有 NEW_LINE 或 PUT_LINE，所以不会显示
END;
输出结果：
CD
E
4.2、DBMS_RANDOM
DBMS_RANDOM包提供了一个内置的随机数生成器，可用它来快速生成随机数和随机字符串。
RANDOM：返回一个 [-2^31, 2^31) 范围内的整数。
SELECT DBMS_RANDOM.RANDOM res FROM DUAL; -- res: -699438152
NORMAL：返回正态分布中的随机数。此正态分布标准偏差为 1，期望值为 0。这个函数返回的数值中有 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)：返回一个随机字符串，其中 opt 指的是字符串的格式，len 指的是字符串的长度。
SELECT DBMS_RANDOM.STRING('u', 10) res FROM DUAL; -- res: ADKXBWIOMI，全大写字母
SELECT DBMS_RANDOM.STRING('l', 10) res FROM DUAL; -- res: mupmuqdoue，全小写字母
SELECT DBMS_RANDOM.STRING('a', 10) res FROM DUAL; -- res: AdOhEwGByt，混合大小写字母
SELECT DBMS_RANDOM.STRING('x', 10) res FROM DUAL; -- res: OMUBEPN3C2，大写字母或数字
SELECT DBMS_RANDOM.STRING('p', 10) res FROM DUAL; -- res: b+[5$ot=w|，任意可打印字符
VALUE：返回 [0, 1) 范围内的随机数，精度为 38 位。
SELECT DBMS_RANDOM.VALUE res FROM DUAL; -- res: 0.381593460771342
VALUE(low IN NUMBER,high IN NUMBER)：返回 [low, high) 范围内的随机数。
SELECT DBMS_RANDOM.VALUE(10,20) res FROM DUAL; -- res: 13.650786652248
INITIALIZE(val IN BINARY_INTEGER) & SEED(seed IN BINARY_INTEGER|VARCHAR2)：设置用来初始化DBMS_RANDOM包的种子值。INITIALIZE 和 SEED 唯一的区别就是，INITIALIZE 只支持数字，而 SEED 既支持数字又支持字符串。另外，SEED 的作用之一是用来取代 INITIALIZE 的。
在默认情况下，DBMS_RANDOM包是根据用户、时间、会话等信息来进行初始化的，换句话说，即便是同一个语句，每次生成时的种子也是不确定的。这时候就可以通过 INITIALIZE 或 SEED 来设置一个固定的种子，确保每次生成时的随机序列一致。
BEGIN
DBMS_RANDOM.SEED('ABC123'); -- 设置种子值 ABC123
FOR i IN 3 .. 9 LOOP
DBMS_OUTPUT.PUT(DBMS_RANDOM.RANDOM||'|');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
输出结果：
-219386465|-850200733|-240588365|-351313939|-1206831363|852217108|-1045006337|
4.3、其它系统包及常用方法
DBMS_METADATA包中的GET_DDL方法用于获取存储在数据字典中的对象定义语句（DDL 语句），返回值是 CLOB 类型的。
语法：
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'
);
示例：
-- 查询定义 T_COURSE 表的 DDL 语句
SELECT DBMS_METADATA.GET_DDL('TABLE','T_COURSE') FROM DUAL;
-- 查询定义 V_STAFF 视图的 DDL 语句
SELECT DBMS_METADATA.GET_DDL('VIEW','V_STAFF') FROM DUAL;
-- 查询定义 SP_STAFF_STATUS 存储过程的 DDL 语句
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','SP_STAFF_STATUS') FROM DUAL;
-- 查询定义 TRG_STAFF_ID 触发器的 DDL 语句
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRG_STAFF_ID') FROM DUAL;
DBMS_LOB包中的SUBSTR方法用于从指定偏移量截取 LOB 类型的值并以字符串形式返回，COMPARE方法用于比较两个 LOB 类型并以数字形式返回，值相等时返回 0，否则返回其他值。
截取语法：
DBMS_LOB.SUBSTR(
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 32767,
offset IN INTEGER := 1
);
截取示例：
SELECT DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE','T_COURSE')) FROM DUAL;
比较语法：
DBMS_LOB.COMPARE(
lob_1 IN CLOB CHARACTER SET ANY_CS,
lob_2 IN CLOB CHARACTER SET lob_1%CHARSET,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1
);
比较示例（该示例也能查出数据，说明DBMS_LOB.COMPARE函数会自动把字符类型转换成 LOB 类型）：
SELECT * FROM demo.t_course t WHERE DBMS_LOB.COMPARE(t.course_name,'语文')=0;
DBMS_DDL包中的ALTER_COMPILE方法用于编译数据中指定模式对象。
语法：
DBMS_DDL.ALTER_COMPILE(
type VARCHAR2,
schema VARCHAR2,
name VARCHAR2
reuse_settings BOOLEAN := FALSE
);
示例：
BEGIN
DBMS_DDL.ALTER_COMPILE('PROCEDURE','DEMO','SP_STAFF_STATUS');
END;
在本系列博客之前的文章中曾陆续提到DBMS_JOB、DBMS_SCHEDULER、DBMS_XPLAN、DBMS_SQL等系统包，事实上在 Oracle 中还有很多功能强大的系统包。例如，可以通过UTL_FILE系统包读写操作系统文本文件，甚至可以用UTL_HTTP系统包通过 HTTP 访问互联网上的数据，把指定的网页的内容摘取下来。想要挖掘更多实用系统包的园友可以看看《Oracle Database PL/SQL Packages and Types Reference》，这个是有关 Oracle 10g 中系统包的全面介绍手册。
5、总结
本文主要讲述了利用层次查询实现批量生成数据、利用循环实现批量插入数据、利用数据字典实现批量生成脚本和生成数据字典以及运用系统包等技巧。
本文链接：http://www.cnblogs.com/hanzongze/p/oracle-plsql-4.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！
