一.劫持查询
select 'DROP TRIGGER ' || owner || '."' ||TRIGGER_NAME || '";'
from dba_triggers
where TRIGGER_NAME like 'DBMS_%_INTERNAL%'
union all
select 'DROP PROCEDURE '|| owner ||'."'|| a.object_name || '";'
from dba_procedures a
where a.object_name like 'DBMS_%_INTERNAL%';
二.简单重复工作的代替
三.查询语句的简单优化
四.动态SQL
五.游标
1.
2.
3.
六.XML生成
七.regexp_substr
Regexp_Substr(String,pattern,position,occurrence ,modifier ) String:操作的字符串; pattern:正则表达式匹配规则,匹配到则返回; position:开始匹配的位置,默认当然是1; occurrence:标识第几个匹配组,默认为1 modifier:模式(i不区分大小写进行检索,c区分大小写进行检索。默认为c)
select regexp_substr('320100|林善成|008527021386513201005EEC6E|1.0','[^|]+',1,1) from dual; 320100
-------------------------------------------------------------------------------------------
select regexp_substr('320100|林善成|008527021386513201005EEC6E|1.0','[^|]+',1,2) from dual; 林善成
-------------------------------------------------------------------------------------------
select regexp_substr('320100|林善成|008527021386513201005EEC6E|1.0','[^|]+',1,3) from dual; 008527021386513201005EEC6E
-------------------------------------------------------------------------------------------
select regexp_substr('320100|林善成|008527021386513201005EEC6E|1.0','[^|]+',1,4) from dual; 1.0
-------------------------------------------------------------------------------------------
select regexp_substr('320100|林善成|008527021386513201005EEC6E|1.0','[^|]+',1,5) from dual;
0000381877|00000113|中国石化集团金陵石油化工有限责任公司|320102194006300831|林善成|1|23|0|320101|5.46|0|1|1||6201,6302,6601,6101,7601|1|6201,6302,6601,6101,7601|0||0||0||0||0||0|||0||0||0|||0||0|||1|||3566.54|0|0|||0|2451.72|0|
八.触发器