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); |