Make one, write a-java call Oracle stored procedure

First, the demand

Pass in a parameter and return multiple records (list).

Ii. Steps of implementation

1. Write an Oracle stored procedure.

--statements that declare a package and a package body should be executed two times, even if the declaration is previously defined, and if it is executed together, an error will be compiled--calling a stored procedure that returns multiple records needs to be defined within the package--declaring packages and cursors within packages, stored proceduresCREATE OR REPLACEPackage Pack_mypackage isTYPE MyCursor isREFCURSOR; PROCEDUREProc_querysomeinfo (PNoinch VARCHAR2, mycursor out mycursor);ENDMyPackage;--defining the package bodyCREATE OR REPLACEPackage BODY Pack_mypackage isPROCEDUREProc_querysomeinfo (PNoinch VARCHAR2, outcursor out MyCursor) isBEGIN    OPENOutcursor for        SELECT *  fromTableAWHEREP_no=PNo;ENDProc_querysomeinfo;ENDPack_mypackage;

2. Writing Java code

 Packagevip.yaocn.test;Importjava.sql.CallableStatement;Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;/*** Stored Procedure Test * *@authorYacon*/ Public classProceduretest { Public Static voidMain (string[] args)throwsException {String URL= "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL"; String drivername= "Oracle.jdbc.driver.OracleDriver"; String username= "Scott"; String Password= "Tiger"; Connection Conn=NULL; CallableStatement cstmt=NULL; ResultSet RS=NULL; Try{class.forname (drivername); Conn=drivermanager.getconnection (URL, username, password); String Callsql= "{Call Pack_mypackage.proc_getsomeinfo (?,?)}"; Cstmt=Conn.preparecall (Callsql); Cstmt.setstring (1, "1001"); Cstmt.registeroutparameter (2, Oracle.jdbc.OracleTypes.CURSOR);            Cstmt.execute (); RS= (ResultSet) cstmt.getobject (2); if(rs! =NULL) {                 while(Rs.next ()) {System.out.println (rs.getstring (1)); }            }        } Catch(Exception e) {e.printstacktrace (); } finally {            Try {                if(rs! =NULL) {rs.close (); }            } Catch(Exception e) {e.printstacktrace (); }            Try {                if(Cstmt! =NULL) {cstmt.close (); }            } Catch(Exception e) {e.printstacktrace (); }            Try {                if(Conn! =NULL) {conn.close (); }            } Catch(Exception e) {e.printstacktrace (); }        }    }}

3. Expand your knowledge

(1)

Make one, write a-java call Oracle stored procedure