实际应用中我们可以通过sum()统计出组中的总计或者是累加值，具体示例如下：
1.创建演示表
create table emp as select * from scott.emp; alter table emp add constraint emp_pk primary key (empno); create table dept as select * from scott.dept; alter table dept add constraint dept_pk primary key (deptno); select deptno, ename, sal, -- 按照部门薪水累加（order by改变了分析函数的作用，只工作在当前行和前一行，而不是所有行） sum (sal) over (partition by deptno order by sal) CumDeptTot, sum (sal) over (partition by deptno) SalByDept, -- 统计一个部门的薪水 sum (sal) over ( order by deptno,sal) CumTot, -- 所有雇员的薪水一行一行的累加 sum (sal) over () TotSal -- 统计总薪水 from emp order by deptno, sal 3. 结果如下：
10 MILLER 1300.00 1300 8750 1300 2902510 CLARK 2450.00 3750 8750 3750 2902510 KING 5000.00 8750 8750 8750 2902520 SMITH 800.00 800 10875 9550 2902520 ADAMS 1100.00 1900 10875 10650 2902520 JONES 2975.00 4875 10875 13625 2902520 SCOTT 3000.00 10875 10875 19625 2902520 FORD 3000.00 10875 10875 19625 2902530 JAMES 950.00 950 9400 20575 2902530 WARD 1250.00 3450 9400 23075 2902530 MARTIN 1250.00 3450 9400 23075 2902530 TURNER 1500.00 4950 9400 24575 2902530 ALLEN 1600.00 6550 9400 26175 2902530 BLAKE 2850.00 9400 9400 29025 29025
如果我们按照示例想得到每个部门薪水值最高的雇员的纪录，可以有四种方法实现：
先创建示例表
create table emp as select * from scott.emp; alter table emp add constraint emp_pk primary key (empno); create table dept as select * from scott.dept; alter table dept add constraint dept_pk primary key (deptno); select * from emp emp1 where emp1.sal = ( select max (emp2.sal) from emp emp2 where emp2.deptno = emp1.deptno) select * from emp emp1,( select deptno, max (sal) maxsal from emp emp2 group by emp2.deptno) emp3 where emp1.deptno = emp3.deptno and emp1.sal = emp3.maxsal
select deptno,maxsal,empno from ( select max (sal) over (partition by deptno) maxsal,emp. * from emp) emp2 where emp2.sal = emp2.maxsal select deptno,sal,empno from ( select emp. * ,DENSE_RANK() over (partition by deptno order by sal desc ) rownumber from emp) emp2 where rownumber = 1 结果如下：
10 5000.00 7839 20 3000.00 7788 20 3000.00 7902 30 2850.00 7698
有些时候我们希望得到指定数据中的前n列，示例如下：
得到每个部门薪水最高的三个雇员：
先创建示例表
create table emp as select * from scott.emp; alter table emp add constraint emp_pk primary key (empno); create table dept as select * from scott.dept; alter table dept add constraint dept_pk primary key (deptno); 先看一下row_number() /rank()/dense_rank()三个函数之间的区别
select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc ) row_number, -- 1,2,3 rank() over (partition by deptno order by sal desc ) rank, -- 1,1,3 dense_rank() over (partition by deptno order by sal desc ) dense_rank from emp -- 1,1,2 结果如下:
10 5000.00 7839 1 1 1 10 2450.00 7782 2 2 2 10 1300.00 7934 3 3 3 20 3000.00 7788 1 1 1 20 3000.00 7902 2 1 1 20 2975.00 7566 3 3 2 20 1100.00 7876 4 4 3 20 800.00 7369 5 5 4 30 2850.00 7698 1 1 1 30 1600.00 7499 2 2 2 取每个部门的薪水前三位雇员：
select t.deptno,t.rank,t.sal from ( select emp. * ,row_number() over (partition by deptno order by sal desc ) row_number, -- 1,2,3 rank() over (partition by deptno order by sal desc ) rank, -- 1,1,3 dense_rank() over (partition by deptno order by sal desc ) dense_rank from emp -- 1,1,2 ) t where t.rank <= 3 结果如下：
10 1 5000.00 10 2 2450.00 10 3 1300.00 20 1 3000.00 20 1 3000.00 20 3 2975.00 30 1 2850.00 30 2 1600.00 30 3 1500.00 如果想输出成deptno sal1 sal2 sal3这种类型的格式
步骤一（decode）： select t.deptno,decode(row_number, 1 ,sal) sal1,decode(row_number, 2 ,sal) sal2,decode(row_number, 3 ,sal) sal3 from ( select emp. * ,row_number() over (partition by deptno order by sal desc ) row_number, -- 1,2,3 rank() over (partition by deptno order by sal desc ) rank, -- 1,1,3 dense_rank() over (partition by deptno order by sal desc ) dense_rank from emp -- 1,1,2 ) t where t.rank <= 3 结果如下：
10 5000 10 2450 10 1300 20 3000 20 3000 20 2975 30 2850 30 1600 30 1500 步骤二（使用聚合函数去除null,得到最终结果）：
select t.deptno, max (decode(row_number, 1 ,sal)) sal1, max (decode(row_number, 2 ,sal)) sal2, max (decode(row_number, 3 ,sal)) sal3 from ( select emp. * ,row_number() over (partition by deptno order by sal desc ) row_number, -- 1,2,3 rank() over (partition by deptno order by sal desc ) rank, -- 1,1,3 dense_rank() over (partition by deptno order by sal desc ) dense_rank from emp -- 1,1,2 ) t where t.rank <= 3 group by t.deptno 结果如下：
10 5000 2450 1300 20 3000 3000 2975 30 2850 1600 1500
有时候报表上面需要显示该笔操作的上一步骤或者下一步骤的详细信息，这个时候可以按照下面的做法：
先创建示例表：
-- Create table create table LEAD_TABLE( CASEID VARCHAR2 ( 10 ), STEPID VARCHAR2 ( 10 ), ACTIONDATE DATE)tablespace COLM_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); insert into LEAD_TABLE values ( ' Case1 ' , ' Step1 ' ,to_date( ' 20070101 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step2 ' ,to_date( ' 20070102 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step3 ' ,to_date( ' 20070103 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step4 ' ,to_date( ' 20070104 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step5 ' ,to_date( ' 20070105 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step4 ' ,to_date( ' 20070106 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step6 ' ,to_date( ' 20070101 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case1 ' , ' Step1 ' ,to_date( ' 20070201 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case2 ' , ' Step2 ' ,to_date( ' 20070202 ' , ' yyyy-mm-dd ' )); insert into LEAD_TABLE values ( ' Case2 ' , ' Step3 ' ,to_date( ' 20070203 ' , ' yyyy-mm-dd ' )); commit ;
每一条记录都能连接到上/下一行的内容
select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,lag(stepid) over (partition by caseid order by actiondate) prestepid,lag(actiondate) over (partition by caseid order by actiondate) preactiondate from lead_table 结果如下：
Case1 Step1 2007 - 1 - 1 Step2 2007 - 1 - 2 Case1 Step2 2007 - 1 - 2 Step3 2007 - 1 - 3 Step1 2007 - 1 - 1 Case1 Step3 2007 - 1 - 3 Step4 2007 - 1 - 4 Step2 2007 - 1 - 2 Case1 Step4 2007 - 1 - 4 Step5 2007 - 1 - 5 Step3 2007 - 1 - 3 Case1 Step5 2007 - 1 - 5 Step4 2007 - 1 - 6 Step4 2007 - 1 - 4 Case1 Step4 2007 - 1 - 6 Step6 2007 - 1 - 7 Step5 2007 - 1 - 5 Case1 Step6 2007 - 1 - 7 Step4 2007 - 1 - 6 Case2 Step1 2007 - 2 - 1 Step2 2007 - 2 - 2 Case2 Step2 2007 - 2 - 2 Step3 2007 - 2 - 3 Step1 2007 - 2 - 1 Case2 Step3 2007 - 2 - 3 Step2 2007 - 2 - 2
还可以进一步统计一下两者的相差天数
select caseid,stepid,actiondate,nextactiondate,nextactiondate - actiondate datebetween from ( select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,lag(stepid) over (partition by caseid order by actiondate) prestepid,lag(actiondate) over (partition by caseid order by actiondate) preactiondate from lead_table) 结果如下：
Case1 Step1 2007 - 1 - 1 2007 - 1 - 2 1 Case1 Step2 2007 - 1 - 2 2007 - 1 - 3 1 Case1 Step3 2007 - 1 - 3 2007 - 1 - 4 1 Case1 Step4 2007 - 1 - 4 2007 - 1 - 5 1 Case1 Step5 2007 - 1 - 5 2007 - 1 - 6 1 Case1 Step4 2007 - 1 - 6 2007 - 1 - 7 1 Case1 Step6 2007 - 1 - 7 Case2 Step1 2007 - 2 - 1 2007 - 2 - 2 1 Case2 Step2 2007 - 2 - 2 2007 - 2 - 3 1 Case2 Step3 2007 - 2 - 3
Oracle支持通过ref游标在调用存储过程后返回结果集, 使用游标在内存消耗以及时间上都要大大的优于返回数组变量的做法!
示例如下：数据库方面，建立一个Package
create or replace package ref_cur_demo is type rc is ref cursor ; procedure ref_cursor(p_owner in varchar2 ,p_cursor in out rc); end ref_cur_demo; create or replace package body ref_cur_demo is procedure ref_cursor(p_owner in varchar2 ,p_cursor in out rc) is begin open p_cursor for select object_name ,object_type from all_objects where owner = p_owner and rownum < 3 ; end ; end ref_cur_demo; 程序方面使用C#建立一个小型应用程序，主要代码如下：
Oracle.DataAccess.Client.OracleConnection oracleConnection1 = new OracleConnection( " data source=precolm2;user id=colmtest;password=colmtest " ); oracleConnection1.Open(); string strSQL = @" ref_cur_demo.ref_cursor " ; Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strSQL,oracleConnection1); cmd.CommandType = CommandType.StoredProcedure; Oracle.DataAccess.Client.OracleParameter pram = new Oracle.DataAccess.Client.OracleParameter( " p_owner " ,Oracle.DataAccess.Client.OracleDbType.Varchar2); pram.Value = " COLMTEST " ; cmd.Parameters.Add(pram); Oracle.DataAccess.Client.OracleParameter pram1 = new Oracle.DataAccess.Client.OracleParameter( " p_cursor " ,Oracle.DataAccess.Client.OracleDbType.RefCursor); pram1.Direction = ParameterDirection.Output; cmd.Parameters.Add(pram1); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); this .dataGrid1.DataSource = ds.Tables[ 0 ].DefaultView;