Java invoke Oracle Stored procedure Instance tutorial

A stored procedure

First, we set up a simple table for the test of stored procedures

The code is as follows	Copy Code
CreateTable Xuesheng (ID integer, xing_ming varchar2 (), Yu_wen number, shu_xue number); Insertinto Xuesheng VALUES (1, ' Zhangsan ', 80,90) Insertinto Xuesheng VALUES (2, ' Lisi ', 85,87)

1 stored procedures with no return value

The code is as follows	Copy Code
Createorreplaceprocedure Xs_proc_no is Begin Insertinto Xuesheng VALUES (3, ' Wangwu ', 90, 90); Commit End Xs_proc_no;

2 stored procedures that have a single data value returned

The code is as follows	Copy Code
Create or Replace procedure Xs_proc (Temp_name invarchar2, Temp_num out number is Num_1 number; Num_2 number; Begin Select Yu_wen, Shu_xue Into Num_1, num_2 From Xuesheng where xing_ming = Temp_name; --dbms_output.put_line (num_1 + num_2); Temp_num: = num_1 + num_2; End

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

3 stored procedure with return value (List return)

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

The code is as follows	Copy Code
Create or replace package MyPackage as Type my_cursor is REF CURSOR; End MyPackage;

After you have defined REF CURSOR, you can write our program code

The code is as follows	Copy Code
Create or Replace procedure xs_proc_list (Shuxue Innumber, P_cursor out Mypackage.my_cursor) is Begin Open P_cursor for Select*from Xuesheng where Shu_xue > Shuxue; End Xs_proc_list;

second, the program calls

In this section, we use the Java language to invoke a stored procedure. The key is to use the CallableStatement object with the following code:

The code is as follows

Copy Code

String oracledrivername = "Oracle.jdbc.driver.OracleDriver"; The following test is used for the table space in Oracle String 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 username and password for the database 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

code is as follows	copy code
callablestatement Proc=null; Proc=myconnection.preparecall ("{Call GETDCSJ (?,?,?,?,?)}"); Proc.setstring (1, strdate); Proc.setstring (2, JZBH); Proc.registeroutparameter (3, types.numeric); Proc.registeroutparameter (4, oracletypes.cursor); Proc.registeroutparameter (5, oracletypes.cursor); Proc.execute (); ResultSet Rs=null; int Total_number=proc.getint (3); rs= (ResultSet) Proc.getobject (4);