Oracle cursors, procedures, and reference types

0. Common functions

substr(str,start,length);//可以反向索引。length不指定就到结尾to_number(str);//str转numberselectto_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual;//日期转字符nvl(tt,0);//若是空值则为0

1. Cursors
For the processing of collection data, learn how to use parameters.

Default maximum of 300 cursors for the same session
Set system set open_cursors=400 scope=
{Both; Change current memory;spfile only; restart effective}

//Properties//cus1%found cus1%notfound cus1%isopen cus1%rowcount affect row countSETServeroutput on;D Eclare CURSOR C1 is    SELECTBookno,booktitle fromBebook; Bookno bebook.bookno%type;//reference typeBookTitle bebook.booktitle%type; Rowbook Bebook%rowtype;//Row Reference typeBEGINOPEN C1;LOOPFETCH C1 intoBookno,booktitle;EXITWhen C1%notfound; Dbms_output.put_line (' This string is breaks here. '|| bookno| | BookTitle);END LOOP;END;//Cursors with parametersCursor Cemp (DNO number) is SelectEname fromEmpwhereDeptno = DNO; Open Cemp (Ten);

2. RowType
Use this data type to increase the robustness of the program. Program changes are not caused by changes in the table structure.
Records can be assigned as a whole
RowType Reference
RowType Reference

Read data into the RowType type Create table testtable ();R Testtable%rowtype; Select *  into R  from TestTable where pno= ...;RowType type data into the table Insert  into Testtable2 values r;----------------------------------------------------------------------------------------------------------DECLARE v_dept Dept%rowtype; begin V_dept.deptno: = ;V_dept.dname: = ' sample '; V_dept.loc: = ' Chicago '; Insert  into dept Values v_dept; end;DECLARE v_dept Dept%rowtype; begin V_dept.deptno: = ;V_dept.dname: = ' sample2 '; V_dept.loc: = ' Dallas '; Update dept set ROW=v_dept where deptno=v_dept.deptno; end;DECLARE rwemp T_mst_employee%rowtype; beginSelect *  into rwemp  from T_mst_employee where emp_no= ' 10001 ';  Rwemp.emp_no: = ' 20001 '; Insert  into T_mst_employee values rwemp;   update t_mst_employee set ROW=rwemp where emp_no=' 3900 '; end;

3. Process

CreateorReplacePROCEDURE "Statistics_ordersumxxx"(Branchno bebranch.branchno%type, Reportperson VARCHAR2, Ordersum_table_cursor outSys_refcursor) isTestcur Sys_refcursor; V_typegoodno beproduct.productclass%TYPE;--Category numberV_pritypegoodno beproduct.productclass%TYPE;--The category number read by the previous cursorV_branchno bebranch.branchno%TYPE;--The branch number that the cursor readsV_pribranchno bebranch.branchno%TYPE;--The branch number read from the previous cursorV_branchname bebranch.branchname%type;--Each branchV_branchsum number;--Total number of branchesV_typenum number; Ordersum_table Odreport1%rowtype;--Cursor definitionCURSOR ordersum_cur (pno VARCHAR2) is    SELECTD.typegoodno, SUM (b.quantity) from Bdprocureplan A, bdplandetail B, beproduct C, Dttypegood D WH ERE A.branchno =pno andA.planno = B.planno andB.productno = C.productno andC.productclass = D.typegoodnoGROUPby D.typegoodno; CURSOR Branch_cur is    SELECTBranchno,branchname from Bebranch;  Maketime DATE; Mycount int:=0;BEGIN  SELECTCOUNT (*) into mycount from Bebranch;OPENBranch_cur;LOOPFETCH branch_cur into V_branchno,v_branchname;EXIT   whenBranch_cur%notfound;    Ordersum_table.branchname: = V_branchname;    Ordersum_table.branchno: = V_branchno; Dbms_output.put_line (Ordersum_table.branchname);OPENOrdersum_cur (V_branchno);LOOPFETCH ordersum_cur into V_typegoodno,v_typenum;EXIT     whenOrdersum_cur%notfound; CaseV_typegoodno when‘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";

Called in Java

Get callablestatement cstmt = null;String Sautono = null;String sSQL ="{call Auto_no (?,?)}";try {cstmt = conndb. Getconn(). Preparecall(sSQL);Cstmt. setString(1, TableName);Cstmt. Registeroutparameter(2, Oracle. JDBC. Oracletypes. VARCHAR);//register character variableCstmt. Registeroutparameter(2, Oracle. JDBC. Oracletypes. CURSOR);//Register cursor variableCstmt. Execute();Sautono = cstmt. getString(2);//cstmt.getint (2);ResultSet rs = ((oraclecallablestatement)Pager). GetCursor(2);while (RS. Next()) {//do something}

Oracle cursors, procedures, and reference types