Oracle Stored Procedures

Summary record stored procedure syntax and how Java programs are invoked

A stored procedure

First, we build a simple table to test the stored procedure

Create TableXuesheng (IDinteger, xing_mingvarchar2( -), Yu_wen Number, Shu_xue Number);Insert  intoXueshengValues(1,'Zhangsan', the, -)Insert  intoXueshengValues(2,'Lisi', -, the)

1) stored procedure with no return value

Create or Replace procedure  is begin  Insert  into Values (3'wangwu');   Commit ; End Xs_proc_no;

2) stored procedures that have a single data value returned

Create or Replace procedureXs_proc (temp_nameinch varchar2, Temp_num out Number) isnum_1 Number; Num_2 Number;begin  SelectYu_wen, Shu_xue intonum_1, Num_2 fromXueshengwhereXing_ming=Temp_name; --dbms_output.put_line (num_1 + num_2);Temp_num:=Num_1+num_2;End;

Of these, the above two are basically similar to SQL Server, but for the return dataset, the above method does not meet our requirements. In Oracle, you typically use the REF CURSOR to return a dataset. The sample code is as follows:

3) Stored procedure with return value (list returned)

First, build our own package. and define a custom REF CURSOR in the package

Create or Replace  as is cursor;     End MyPackage;

After defining the REF cursor, you can write our program code

Create or Replace procedure xs_proc_list (Shuxue   in number,                                         isbegin   open for    Select *  from where > Shuxue; End Xs_proc_list;

Second, the program calls

In this section, we call the stored procedure using the Java language. The key is to use the CallableStatement object, the code is as follows:

String oracledrivername = "Oracle.jdbc.driver.OracleDriver"; //The test used below is the table space in OracleString oracleurltoconnect = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL"; Connection myconnection=NULL; Try{class.forname (oracledrivername); } Catch(ClassNotFoundException ex) {ex.printstacktrace (); }        Try{myconnection=drivermanager.getconnection (Oracleurltoconnect,"XXXX", "xxxx");//here is the database user name and password         } Catch(Exception ex) {ex.printstacktrace (); }        Try{callablestatement proc=NULL; Proc=myconnection.preparecall ("{Call Xs_proc (?,?)}"); Proc.setstring (1, "Zhangsan"); Proc.registeroutparameter (2, Types.numeric);            Proc.execute (); String teststring=proc.getstring (2);         System.out.println (teststring); } Catch(Exception ex) {ex.printstacktrace (); }

For a stored procedure that returns a value for a list, make a simple modification in the preceding code. As follows

 callablestatement proc=null  ; Proc  =myconnection.preparecall ("{Call GETDCSJ (?,?,?,?,?)}"            );            Proc.setstring ( 1 2 3 4 5 =null  ;  int  total_number=proc.getint (3 = (ResultSet) proc.getobject (4); 

The stored procedure above has been modified. In addition, an example of a complex project is querying data that is not more than 10 minutes in length and has more than 100 consecutive records. The GETDCSJ stored procedure called by the above code

Create or Replace procedureGETDCSJ (Var_flaginch varchar2, VAR_JZBHinch varchar2, Number_total out Number, var_cursor_a out Mypackage.my_cursor, var_curs Or_b out Mypackage.my_cursor) is Total Number; cursorCur is    SelectSJ, Flag fromD_DCSJwhereJzbh=VAR_JZBHOrder  bySjdesc        for Update; Last_time date;begin   forCur1inchcur LoopifLast_time is NULL orCur1.sj>=Last_time- Ten /  - /  -  Then      UpdateD_dcsjSetFlag=Var_flagwhere  Current  ofcur; Last_time:=CUR1.SJ; Else      Select Count(*) intoTotal fromD_dcsjwhereFlag=Var_flag;      Dbms_output.put_line (total); ifTotal<  -  Then        UpdateD_dcsjSetFlag= NULL whereFlag=Var_flag; Last_time:= NULL; UpdateD_dcsjSetFlag=Var_flagwhere  Current  ofcur; Else        OpenVar_cursor_a for          Select *             fromD_DCSJwhereFlag=Var_flag andJzbh=VAR_JZBH andZh= 'A'           Order  bySjdesc; Number_total:=Total ; OpenVar_cursor_b for          Select *             fromD_DCSJwhereFlag=Var_flag andJzbh=VAR_JZBH andZh= 'B'           Order  bySjdesc; Number_total:=Total ; Exit; End if; End if; EndLoop; Select Count(*) intoTotal fromD_dcsjwhereFlag=Var_flag;  Dbms_output.put_line (total); ifTotal<  -  Then    OpenVar_cursor_a for      Select *  fromD_dcsjwhereZh= 'C'; OpenVar_cursor_b for      Select *  fromD_dcsjwhereZh= 'C'; Else    OpenVar_cursor_a for      Select *         fromD_DCSJwhereFlag=Var_flag andJzbh=VAR_JZBH andZh= 'A'       Order  bySjdesc; Number_total:=Total ; OpenVar_cursor_b for      Select *         fromD_DCSJwhereFlag=Var_flag andJzbh=VAR_JZBH andZh= 'B'       Order  bySjdesc; Number_total:=Total ; End if; Commit;End;

This article transferred from: http://www.cnblogs.com/liliu/archive/2011/06/22/2087546.html

Oracle Stored Procedures