生成 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
一次向 t3 表中插入 100 万条数据(在本人笔记本上操作耗时二十几秒)
- BEGIN
- FOR i IN 1 .. 1000000 LOOP
- INSERT INTO t3(f1) VALUES(i);
- END LOOP;
- COMMIT;
- END;
将 t2 表中的数据变成当前的 2^5 倍(假如 t2 中原本有 3 条数据,那么最终 t2 表中的行数将是:
,即 3*2^5)
- 3+(3*2^0)+(3*2^1)+(3*2^2)+(3*2^3)+(3*2^4)
- BEGIN
- FOR i IN 1 .. 5 LOOP
- INSERT INTO t2 SELECT t2.* FROM t2;
- END LOOP;
- COMMIT;
- END;
生成清空当前用户所有表中数据的语句
- 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(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';
生成语句:
- SELECT t.course_id,
- t.course_name,
- t.course_desc FROM demo.t_course t WHERE 1 = 1;
生成向
表中插入数据的语句
- 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='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='T_COURSE';
生成语句:
- INSERT INTO demo.t_course(course_id,course_name,course_desc)
- VALUES(:CourseId,:CourseName,:CourseDesc);
生成修改
表中数据的语句
- T_COURSE
风格一:
- 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';
生成语句:
- UPDATE demo.t_course t
- SET t.course_id=:course_id,t.course_name=:course_name,t.course_desc=:course_desc
- WHERE 1=1;
风格二:
- 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';
生成语句:
- UPDATE demo.t_course t
- SET t.course_id=:CourseId,t.course_name=:CourseName,t.course_desc=:CourseDesc
- WHERE 1=1;
查询语句:
- 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 查询得到的结果集中,默认值
字段是 LONG 类型的。看到这个之后我曾想在查询语句将其转换成字符串,后来发现 Oracle 并未提供 LONG 类型转字符类型的函数或语法,非要转的话还得自己写函数,总之相当繁琐。后来我发现如果通过 PL/SQL Developer 的结果集窗口直接把数据导出到 Excel 之后,默认值列会自动转换成字符串。再后来我又发现通过命令窗口执行查询语句也会自动把默认值列自动转换成字符串。总之一句话,不用自己费心费力的去转换 LONG 类型了,直接通过 PL/SQL Developer 生成数据字典即可。
- data_default
为了便于开发 PL/SQL 程序,Oracle 数据库提供了数以百计的系统包。本机将会重点讲解其中几个常见系统包及常用方法。
包的主要功能就是在 PL/SQL 程序中输入或输出消息,譬如可以通过它在存储过程和触发器中向缓冲区发送调试消息。
- DBMS_OUTPUT
常用子程序的语法及说明:
- 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
包提供了一个内置的随机数生成器,可用它来快速生成随机数和随机字符串。
- 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):设置用来初始化
包的种子值。INITIALIZE 和 SEED 唯一的区别就是,INITIALIZE 只支持数字,而 SEED 既支持数字又支持字符串。另外,SEED 的作用之一是用来取代 INITIALIZE 的。
- DBMS_RANDOM
在默认情况下,
包是根据用户、时间、会话等信息来进行初始化的,换句话说,即便是同一个语句,每次生成时的种子也是不确定的。这时候就可以通过 INITIALIZE 或 SEED 来设置一个固定的种子,确保每次生成时的随机序列一致。
- DBMS_RANDOM
- 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|
包中的
- DBMS_METADATA
方法用于获取存储在数据字典中的对象定义语句(DDL 语句),返回值是 CLOB 类型的。
- GET_DDL
语法:
- 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
方法用于从指定偏移量截取 CLOB 类型的值并以字符串形式返回。
- SUBSTR
语法:
- 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_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
等系统包,事实上在 Oracle 中还有很多功能强大的系统包。例如,可以通过
- DBMS_SQL
系统包读写操作系统文本文件,甚至可以用
- UTL_FILE
系统包通过 HTTP 访问互联网上的数据,把指定的网页的内容摘取下来。想要挖掘更多实用系统包的园友可以看看《Oracle Database PL/SQL Packages and Types Reference》,这个是有关 Oracle 10g 中系统包的全面介绍手册。
- UTL_HTTP
本文主要讲述了利用层次查询实现批量生成数据、利用循环实现批量插入数据、利用数据字典实现批量生成脚本和生成数据字典以及运用系统包等技巧。
来源: http://www.cnblogs.com/hanzongze/p/Oracle-plsql-4.html