Summary of calling oracle stored procedures using java) and oracle stored procedures

Summary of calling the oracle stored procedure in java (for example, oracle Stored Procedure)

// 1. call + package name + stored procedure name (for incoming and outgoing values ?) String str = "{call SMSBUSINESS. deleteZhZMember (?,?,?)} "; // 2. Establish Connection conn = null; conn = DriverManager. getConnection (); // 3. Use java. SQL. * class CallableStatement cs = conn. prepareCall (str); // 4. input the in value cs. setInt (1, id); cs. setInt (2,-2); // 5. Set the out value cs. registerOutParameter (3, Types. NUMERIC); // 6. Execute cs. excuse (); // 7. Obtain the out value int flag = cs. getInt (3); // 8. Disconnect conn. close ();

Reference from: **************************************** **************************************** ************************************** this time starts learning to write a stored procedure, the main reason is that the work is needed. I thought it was very simple. However, after some setbacks, I was exhausted with pride. But I finally succeeded. To avoid the latencies of the latencies, I would like to describe it here, we also encourage ourselves. I. Stored PROCEDURE without return values: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) ASBEGIN INSERT INTO HYQ. B _ID (I _ID, I _NAME) VALUES (PARA1, PARA2); END TESTA; then, the following code is used for calling in java:

Package com. hyq. src; import java. SQL. *; import java. SQL. resultSet; public class TestProcedureOne {public TestProcedureOne () {} public static void main (String [] args) {String driver = "oracle. jdbc. driver. oracleDriver "; String strUrl =" jdbc: oracle: thin: @ 127.0.0.1: 1521: hyq "; Statement stmt = null; ResultSet rs = null; Connection conn = null; callableStatement cstmt = null; try {Class. forName (dri Ver); conn = DriverManager. getConnection (strUrl, "hyq", "hyq"); CallableStatement proc = null; // create the object proc = conn. prepareCall ("{call HYQ. TESTA (?,?)} "); // Set the stored procedure call as a keyword. proc. setString (1, "100"); // set the first input parameter proc. setString (2, "TestOne"); // set the second input parameter proc.exe cute (); // execute} catch (SQLException ex2) {ex2.printStackTrace ();} catch (Exception ex2) {ex2.printStackTrace ();} finally {try {if (rs! = Null) {rs. close (); if (stmt! = Null) {stmt. close ();} if (conn! = Null) {conn. close () ;}} catch (SQLException ex1 ){}}}}

Of course, we need to create a table named TESTTB, which contains two fields (I _ID and I _NAME ).

2. Stored PROCEDURE with returned values (non-list): create or replace procedure testb (PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) asbegin select into PARA2 from testtb where I _ID = PARA1; end testb; use the following code when calling in java:

Package com. hyq. src; public class TestProcedureTWO {public TestProcedureTWO () {} public static void main (String [] args) {String driver = "oracle. jdbc. driver. oracleDriver "; String strUrl =" jdbc: oracle: thin: @ 127.0.0.1: 1521: hyq "; Statement stmt = null; ResultSet rs = null; Connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (strUrl, "hyq", "hyq"); CallableSta Tement proc = null; proc = conn. prepareCall ("{call HYQ. TESTB (?,?)} "); // Set the Stored Procedure proc. setString (1, "100"); // set the first parameter input parameter proc. registerOutParameter (2, Types. VARCHAR); // The second output parameter is proc.exe cute () of the VARCHAR type; // run String testPrint = proc. getString (2); // obtain the output parameter System. out. println ("= testPrint = is =" + testPrint);} catch (SQLException ex2) {ex2.printStackTrace ();} catch (Exception ex2) {ex2.printStackTrace ();} finally {try {if (rs! = Null) {rs. close (); if (stmt! = Null) {stmt. close ();} if (conn! = Null) {conn. close () ;}} catch (SQLException ex1 ){}}}}}

Note that the proc. the value 2 in getString (2) is not arbitrary, but corresponds to the out column in the stored procedure. If the out column is in the first position, it is proc. getString (1). If it is the third position, it is proc. getString (3), of course, you can also have multiple return values at the same time, that is, add a few more out parameters.

Iii. Return list because the oracle stored procedure does not return values, all of its return values are replaced by the out parameter, and the list is no exception, but because it is a set, therefore, common parameters cannot be used. pagkage must be used. therefore, you need to create a package in two parts: 1. Create or replace package testpackage as type Test_CURSOR is ref cursor; end TESTPACKAGE; 2. CREATE a stored PROCEDURE: create or replace procedure testc (p_CURSOR out TESTPACKAGE. test_CURSOR) isbegin open p_CURSOR for select * from hyq. TESTTB; end testc; as you can see, it refers to the cursor (which can be understood as a pointer), which is returned as an out parameter. Use the following code when calling in java:

Package com. hyq. src; import java. SQL. *; import java. io. outputStream; import java. io. writer; import java. SQL. preparedStatement; import java. SQL. resultSet; import oracle. jdbc. driver. *; public class TestProcedureTHREE {public TestProcedureTHREE () {} public static void main (String [] args) {String driver = "oracle. jdbc. driver. oracleDriver "; String strUrl =" jdbc: oracle: thin: @ FIG: 1521: hyq "; Statement Stmt = null; ResultSet rs = null; Connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (strUrl, "hyq", "hyq"); CallableStatement proc = null; proc = conn. prepareCall ("{call hyq. testc (?)} "); // Proc under the hyq package. registerOutParameter (1, oracle. jdbc. oracleTypes. CURSOR); // set the output parameter to a CURSOR. the first parameter is the cursor type proc.exe cute (); // execute rs = (ResultSet) proc. getObject (1); // get the first parameter as a cursor and convert it to the ResultSet type while (rs. next () // obtain the data {System. out. println ("<tr> <td>" + rs. getString (1) + "</td> <td>" + rs. getString (2) + "</td> </tr>") ;}} catch (SQLException ex2) {ex2.printStackTrace () ;} catch (Exception ex2) {ex2.printSta CkTrace ();} finally {try {if (rs! = Null) {rs. close (); if (stmt! = Null) {stmt. close ();} if (conn! = Null) {conn. close () ;}} catch (SQLException ex1 ){}}}}

Note that the oracle driver package must be placed in the class path before execution. Otherwise, an error will be reported.