SQL进阶：SQL高级编程语法实例

之前写过的文章：

主要是告诉大家学习SQL高级编程的参考路径，现在把书里简单的实例代码整理出来，方便以后随时查询使用。深入理解还需要看书学习。

主要参考

《剑破冰山Oracle开发艺术》

《精通Oracle SQL（第2版）》

1 高级分组grouping sets：集合汇总

grouping sets：集合汇总，对每个子集汇总后合并结果

select JOB,D如何减小肚子EPTNO,sum(sal)from emp group by grouping sets(JOB,DEPTNO)order by 1,2;JOB DEPTNO SUM(SAL)-------------------- ---------- ----------ANALYST 6000CLERK 4150MANAGER 8275PRESIDENT 5000SALESMAN 5600 10 8750 20 10875 30 9400rollup：向上汇总select JOB,DEPTNO,count(1)from emp group by rollup(JOB,DEPTNO)order by 1,2;JOB DEPTNO COUNT(1)-------------------- ---------- ----------ANALYST 20 2ANALYST 2CLERK 10 1CLERK 20 2CLERK 30 1CLERK 4MANAGER 10 1MANAGER 20 1MANAGER 30 1MANAGER 3PRESIDENT 10 1PRESIDENT 1SALESMAN 30 4SALESMAN 4 14cube：组合汇总select JOB,DEPTNO,count(1)from emp group by cube(JOB,DEPTNO)order by 1,2;JOB DEPTNO COUNT(1)-------------------- ---------- ----------ANALYST 20 2ANALYST 2CLERK 10 1CLERK 20 2CLERK 301吨等于多少千克 1CLERK 4MANAGER 10 1MANAGER 20 1MANAGER 30 1MANAGER 3PRESIDENT 10 1PRESIDENT 1SALESMAN 30 4SALESMAN 4 10 3 20 bingo游戏 5 30 6 142 行转列传统行转列select job ,sum(case when deptno=10 then sal end) dept10_sum_sal ,sum(case when deptno=20 then sal end) dept20_sum_sal ,sum(case when deptno=30 then sal end) dept30_sum_sal ,sum(case when deptno=40 then sal end) dept40_sum_salfrom empgroup by job;pivot 行转列select *from (select job,deptno,sum(sal) sal from emp group by job,deptno pivot (sum(sal) as sum_sal for deptno in (10 as dept10,20 as dept20,3梦的预见0 as dept30,40 as dept40));unpivot 列转行selec压型钢板t *from pivot_tabunpivot(sum_sal for deptsal_desc in(dept10_sum_sal,dept30_sum_sal,dept30_sum_sal,dept40_sum_sal);文本聚合函数listagg()

listagg()：文本聚合函数，类似wmsys.wm_concat()函数，listagg可以设置排序，wmsys.wm_concat无序

select DEPTNO,listagg(ENAME,',') within group(order by ENAME) from empgroup by DEPTNO;

3 分析函数分析函数结构

func() over(partition by 分组 order by 排序 滑动窗口)

常用分析函数

row_number() 唯一排序

rank() 有跳号并列排名

dense_rank() 无跳号并列排名，值并列排序

lag(字段,1,默认值) 前1行

lead(字段,1,默认值) 后1行

first_value() 第一行

last_value() 最后一行

nth_value(字段,1) 任意一行

percent_rank() 百分比排名，返回0-1间

percentile_cont() 指定百分比，返回排名值，percent_rank的反函数

percentile_disc() 类似percentile_cont()，离散分布

ntile() 分组

ratio_to_report() 计算报告中值的比例：val/sum(val) over()

活动窗口

rows between unbounded preceding and unbounded following：从第一行到最后一行，无order by时默认

rows between unbounded preceding and current row：从第一行到当前行，有order by时默认

rows between 2 preceding and 2 following：前2行到后2行

range between 100 preceding and 100 following：根据order by列的值范围，相当于加条件 where expr between a and b

--计算工资最接近前后2名的均值select DEPTNO,ENAME,SAL,avg(SAL) over(partition by DEPTNO order by SAL rows between 2 preceding and 2 following ) sum_salfrom emporder by DEPTNO,SAL;KEEP语句--KEEP ：从并列排名最大最小值范围中做选择select DEPTNO,ENAME,SAL,HIREDATE,dense_rank() over(partition by a.DEPTNO order by SAL) rn,min(HIREDATE) keep(dense_rank last order by SAL) over(partition by a.DEPTNO) keepfrom emp aorder by DEPTNO,SAL,HIREDATE;4 递归层次查询connect by层次查询

select level,connect_by_root|connect_by_isleaf|connect_by_iscycle

from tablename

where

connect by prior 列1=列2

start with

order siblings by

--传统connect by法select EMPNO,ENAME,MGR,level,cast(sys_connect_by_path(ENAME,'-') as varchar2(200)) as path,connect_by_root(ENAME) as top_managerfrom empstart with MGR is nullconnect by prior EMPNO=MGR;显示当月日历select max(decode(旧版三国演义dow,1,d,null)) Sun ,max(decode(dow,2,d,null)) Mon ,max(decode(dow,3,d,null)) Tue ,max(decode(dow,4,d,null)) Wen ,max(decode(dow,5,d,null)) Thu ,max(decode(dow,6,d,null)) Fri ,max(decode(dow,7,d,null)) Satfrom (select rownum d --第几天 ,trunc((rownum+to_number(to_char(trunc(sysdate,'MM'),'D'))-2)/7) p --第几周 ,to_char(trunc(sysdate,'MM')+rownum-1,'D') dow --星期几from dualconnect by rownum<=to_number(to_char(last_day(sysdate),'dd'))) agroup by porder by p;SUN MON TUE WEN THU FRI SAT------------------------------ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30with层次查询

--with子查询CTE

with 查询名([列名])

as(子查询：锚点成员 union all 递归成员)

[遍历顺序子句：深度优先、广度优先]

[循环子句：终止循环条件]

[多个子查询]

--with子查询法with cte(EMPNO,ENAME,MGR,lev,path,top_manager) as(select EMPNO,ENAME,MGR,1 as lev,ENAME as path,ENAME as top_managerfrom empwhere MGR is nullunion allselect b.EMPNO,b.ENAME,b.MGR,1+a.lev,cast(a.path||'-'||b.ENAME as varchar2(200)),a.top_managerfrom cte ajoin emp b on a.EMPNO=b.MGR)select * from cte;叶子节点--叶子节点 connect_by_isleaf伪列select EMPNO,ENAME,MGR,level,菲尔普斯蝶泳cast(sys_connect_by_path(ENAME,'-') as varchar2(200)) as path,connect_by_root(ENAME) as top_manager,connect_by_isleaffrom chencanhui.empwhere connec抑郁症症状t_by_isleaf=1start with MGR is nullconnect by prior EMPNO=MGR;--叶子节点，深度优先+序列with cte(EMPNO,ENAME,MGR,lev,path,top_manager) as(select EMPNO,ENAME,MGR,1 as lev,ENAME as path,ENAME as top_managerfrom chencanhui.empwhere MGR is nullunion allselect b.EMPNO,b.ENAME,b.MGR,1+a.lev,cast(a.path||'-'||b.ENAME as varchar2(200)),a.top_managerfrom cte ajoin chencanhui.emp b on a.EMPNO=b.MGR)search depth first by mgr set seq --深度优先搜索，生成序列seqselect *from (select a.*,case when lev < lead(lev) over(order by 弱碱性水seq) then 0 else 1 end is_leaffrom cte a) where is_leaf=1;构造序列select rownum rnfrom dualconnect by rownum<=10;with t(rn)as(select 1 rnfrom dualunion allselect rn+1from t where rn<10)select * from t;

5 model子句

model子句：单元格跨行引用，类似excel

model跨行引用虽然很灵活，但语法复杂，执行效率慢，只适合在小的汇总报表使用，本人在工作上基本没使用。

select product, country, year, week, inventory, sale, receiptsfrom sales_fact sfwhere sf.country in ('Australia') and sf.product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]) order by product雷克斯自行车, country,鲨鱼肉好吃吗 year, week ;6 正则表达式正则函数

regexp_substr(字符串,正则表达式,开始位置,出现次数,匹配选项)

匹配选项，i：大小写不敏感； c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符。

regexp_深蓝儿童substr()

regexp_instr()

regexp_replace()

regexp_like()

regexp_count()

Oracle特殊字符集

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:unct:]] 任何标点符号。

[[:print:]] 任何可打印字符。

[[:space:]] 任何空白字符。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

7 高级DMLinsert多表插入--同表插入多次insert allinto parents(pid,cust_nam) values(pid,cust_nam)into children(pid,cust_nam) values(pid,child1)into children(pid,cust_nam) values(pid,child2)into children(pid,cust_nam) values(pid,child3)select pid,cust_nam,child1,child2,child3from people;--条件多表插入insert all/first: all满足多个when则插入多个表，first满足第一个when后序不再插入insert allwhen sum_orders<1000 then into small_customerswhen sum_orders>=1000 and sum_orders<10000 then into medium_customerselseinto large_customersselect customer_id,sum(order_total) sum_ordersfrom oe.ordersgroup by customer_id;--并行插入insert /*+ append */ into small_customersselect customer_id,order_totalfrom oe.orders;commit;update关联--当关联不存在，替换成空值UPDATE T1 SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME);--只对关联存在记录有更新UPDATE T1 SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME);--内联视图更新UPDATE (select t1.fmoney fmoney1,t2.fmoney fmoney2 from t1,t2 where t1.fname = t2.fname)tset fmoney1 =fmoney2;--merge更新merge into t1using (select t2.fname,t2.fmoney from t2) ton (t.fname = t1.fname)when matched then update set t1.fmoney = t.fmoney;merge语句merge into table_nameusing (subquery) on (subquery.column = table.column)when matched then updatewhen not match万豪酒店集团ed then insertmerge into t2using t1 on (t1.name = t2.name)when matched then update set t2.money = t2.money + t1.moneywhen not matched then insert values(t1.name,t1.money);--merge条件更新、delete子句merge into t2using t1 on (t1.name = t2.name)when matched then update set t2.money = t2.money + t1.moneywhere t1.name='A' --条件过滤delete where t1.name='B' when not matched then insert values(t1.name,t1.money);8 综合实例号段生成器--根据号码明细生成号段分组drop table tmp_test;create table tmp_test as select 2014 year,1 num from dual union allselect 2014 year,2 num from dual union allselect 2014 year,3 num from dual union allselect 2014 year,4 num from dual union allselect 2014 year,5 num from dual union allselect 2014 year,7 num from dual union allselect 2014 year,8 num from dual union allselect 2014 year,9 num from dual union allselect 2014 year,11 num from dual union allselect 2014 year,12 num from dual union allselect 2013 year,120 num from duawindows平板l union allselect 2013 year,121 num from dual union allselect 2013 year,122 num from dual union allselect 2013 year,124 num from dual union allselect 2013 year,125 num from dual;--差异分组法select year ,num-rn group_id ,min(num) num_start ,max(num) num_endfrom (select year ,num ,row_number() over(partition by year order by num) rnfrom tmp_test ) agroup by year,num-rnorder by 1,2;--相邻比较法se兼职摄影师lect a.* ,a.s_end num_start ,lead(s_start,1,s_max) over(partition by year order by s_start) num_endfrom (select year ,lag(num,1,num) over(partition by year order by num) s_start ,num s_end ,max(num) over(partition by year) s_maxfrom tmp_test ) awhere nvl(s_end-s_start,0)<>1order by 1,2;--根据号段分组生成号码明细drop table tmp_test2;create table tmp_test2 as select 1 group_id,1 num_start,5 num_end from dual union allselect 2 group_id,7 num_start,9 num_end from dual union allselect 3 group_id,11 num_start,12 num_end from dual;select a.group_软文发布平台id ,a.num_start ,a.num_end ,a.num_start + b.rn-1 numfrom tmp_test2 a , (select rownum rn from (select max(num_end-num_start)+1 max_len from tmp_test2) connect by rownum<=max_len ) b where b.rn<=a.num_end-a.num_start+1order by 1,2,3,4;9 动态SQL

绑定变量 :x

begin for i in 1..10 loop execute immediate 'insert into t values('||i||')'; commit; end loop;end;/begin for i in 1..10 loop execute immediate 'insert into t values( :x )' using i; commit; end loop;end;/10 管道函数管道函数table()

管道函数：能返回行集合、数组的函数

type ... is table of date：创建集合类型

return ... pipelined：声明返回管道函数类型

pipe row()：返回该集合的单个元素

return：以空return语句结束，表名管道函数完毕

table()：使用表函数转成数据库表格式

--创建集合类型create or replace type data_list is table of date;/ --创建管道函数create or replace fu打篮球好处nction pipe_date(p_start date,p_limit number)return data_list pipelined isbegin for i in 0 .. p_limit-1 loop pipe row(p_start+i); --返回该集合的单个元素 end loop; return; --以空return语句结束，表名管道函数完毕end;/ --使用管道函数select *from table(pipe_date(trunc(sysdate),10));split函数实例--oracle没有split函数，可以自己用管道函数定义create or replace type type_split is table of varchar2(200);/ create or replace function split(p_list varchar2, p_sep varchar2:=',') return type_split pipelined is l_idx number; v_list varchar2(200):=p_list;begin loop l_idx:=instr(v_list,p_sep); if l_idx>0 then pipe row(substr(v_list,1,l_idx-1)); v_list:=substr(v_list,l_idx+length(p_sep一定是特别的缘分)); else pipe row(数据波v_list); exit; end if; end loop; return;end; / select split('one,tow,three',',') from dual;TYPE_SPLIT('one', 'tow', 'three') select *from the(select split1到100的英语单词('one,tow,three',',') from dual); select *from table(split('one,tow,three',','));onetowthree11 PLSQL

《Oracle PLSQL 程序设计(第5版)》上下册。

Oracle PLSQL程序设计学习笔记