Oracle游标、过程与引用类型

0. 常用函数

) from dual;//日期转字符nvl(tt,0);//若是空值则为0

1. 游标 对于集合数据的处理，，学会使用带参数的方式。

同一个会话默认最多300个光标 set system set open_cursors=400 scope= {both;仅更改当前memory;spfile;重启生效}

serveroutput ON;DECLARE CURSOR c1 ISSELECT bookno,booktitle FROM bebook; bookno bebook.bookno%type;//引用类型 booktitle bebook.booktitle%type; rowbook bebook%rowtype;//行引用类型BEGIN OPEN c1; LOOPFETCH c1 INTO bookno,booktitle;EXIT WHEN c1 %notfound;dbms_output.put_line(‘this string breaks here.’||bookno||booktitle); END LOOP;END;//带参数的光标 Cursor cemp(dno number) is select ename from emp where deptno = dno;Open cemp(10);

2. rowtype 利用这个数据类型增加程序的健壮性。不会受到表结构更改导致程序更改。 记录可以整体赋值 rowtype参考 rowtype参考

//读入数据到rowtype类型中create table testtable();r testtable%rowtype;select * into r from testtable where pno=…;//rowtype类型数据插入表中declare v_dept dept%rowtype;beginv_dept.deptno := 60;v_dept.dname := ‘sample’;v_dept.loc := ‘chicago’;;declarev_dept dept%rowtype;beginv_dept.deptno := 60;v_dept.dname := ‘sample2’;v_dept.loc := ‘dallas’;;declare rwEmp t_mst_employee%rowtype;begin select * into rwEmp from t_mst_employee where emp_no=’10001′; rwEmp.emp_no := ‘20001’; t_mst_employee ;

3. 过程

create or replace PROCEDURE “STATISTICS_ORDERSUMxxx”(branchNo BEbranch.branchNo%type,reportPerson VARCHAR2,ordersum_table_cursor OUT sys_refcursor)IS testcur sys_refcursor; v_typegoodNo BEproduct.productClass%TYPE; –类别编号 v_pritypegoodNo BEproduct.productClass%TYPE; –上一游标读取的类别编号 v_branchNo BEbranch.branchNo%TYPE;–游标读取的分店编号 v_pribranchNo BEbranch.branchNo%TYPE;–上一个游标读取的分店编号 v_branchname BEbranch.branchName%type;–各分店 v_branchsum NUMBER;–分店总数 v_typenum NUMBER; ordersum_table odreport1%rowtype; –游标定义 CURSOR ordersum_cur(pno VARCHAR2) ISSELECT d.typegoodno,SUM(b.quantity)FROM BDprocureplan a,BDplandetail b,BEproduct c,DTtypegood dWHERE a.branchno =pnoAND a.planno= b.plannoAND b.productno = c.productnoAND c.productclass = d.typegoodnoGROUP BY d.typegoodno; CURSOR branch_cur ISSELECT branchNo,branchName FROM BEbranch; maketime DATE; mycount INT:=0;BEGIN SELECT COUNT(*) INTO mycount FROM bebranch; OPEN branch_cur; LOOPFETCH branch_cur INTO v_branchNo,v_branchname;EXIT WHEN branch_cur%NOTFOUND;ordersum_table.branchname := v_branchname;ordersum_table.branchno := v_branchno;dbms_output.put_line(ordersum_table.branchname);OPEN ordersum_cur( v_branchNo );LOOPFETCH ordersum_cur INTO v_typegoodNo,v_typenum;EXITWHEN ordersum_cur%NOTFOUND;CASE v_typegoodNoWHEN’001’THENordersum_table.clothessum := ordersum_table.clothessum+v_typenum;WHEN’002’THENordersum_table.shoesum:=ordersum_table.shoesum+v_typenum;WHEN’003’THENordersum_table.foodsum:=ordersum_table.foodsum+v_typenum;WHEN’004’THENordersum_table.sourcesum:=ordersum_table.sourcesum+v_typenum;WHEN’005’THENordersum_table.drinksum:=ordersum_table.drinksum+v_typenum;WHEN’006’THENordersum_table.drinkingsum:=ordersum_table.drinkingsum+v_typenum;WHEN’007’THENordersum_table.vegetablesum:=ordersum_table.vegetablesum+v_typenum;WHEN’008’THENordersum_table.fruitsum:=ordersum_table.fruitsum+v_typenum;WHEN’009’THENordersum_table.moatsum:=ordersum_table.moatsum+v_typenum;WHEN’010’THENordersum_table.electricsum:=ordersum_table.electricsum+v_typenum;WHEN’011’THENordersum_table.officesum:=ordersum_table.officesum+v_typenum;WHEN’012’THENordersum_table.studysum:=ordersum_table.studysum+v_typenum;WHEN’013’THENordersum_table.diansum:=ordersum_table.diansum+v_typenum;END CASE;ordersum_table.allsum := ordersum_table.allsum+v_typenum;END LOOP;insert into odreport1 values ordersum_table;CLOSE ordersum_cur; END LOOP; CLOSE branch_cur; COMMIT;END “STATISTICS_ORDERSUMxxx”;美不美乡中水，亲不亲故乡人。