Detailed description of Oracle access cursor in out parameters
Oracle access cursor in out parameter
concept
declaration packet structure
Baotou: Responsible for declaration
Package: responsible for implementation
second demand
Query all information about all employees in a department
Sanbaotou
CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor isref cursor; procedure queryEmplist(dno in number,emplist out empcursor);END MYPACKAGE;
tetracladium
The package body needs to implement all methods declared in the package header
CREATE OR REPLACEPACKAGE BODY MYPACKAGE AS procedure queryEmplist(dno in number,emplist out empcursor) ASBEGIN--Open emplist forselect*from emp where deptno = dno;END queryEmplist;END MYPACKAGE;
5. Access stored procedures in packages in applications
1. Code
package demo.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;import org.junit.Test;import demo.utils.JDBCUtils;publicclassTestCursor{/* CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is ref cursor; procedure queryEmplist(dno in number,emplist out empcursor);END MYPACKAGE; * */@Testpublicvoid testCursor(){String sql="{call MYPACKAGE.queryEmplist(?,?)} "; Connection conn =null; CallableStatement call =null; ResultSet rs =null; try{//Get database connection conn =JDBCUtils.getConnection();//Create statement call = conn.prepareCall(sql);//For in parameter, assign call.setInt(1,10);//For out parameter, declare call.registerOutParameter (2,OracleTypes.CURSOR);//execute call.execute();//extract information of all employees in this department rs =((OracleCallableStatement)call).getCursor(2);while (rs.next()){//retrieve employee ID, name, salary and position int empno = rs.getInt("empno");String name =rs.getString("ename");double salary = rs.getDouble("sal");String job = rs.getString ("empjob");System.out.println(empno+"\t"+name+"\t"+salay+"\t"+job);}}catch(Exception e){ e.printStackTrace();}finally{JDBCUtils.release(conn, call, rs);}}}
2. Operation results
7782 CLARK 6450.0 MANAGER7839 KING 10100.0 PRESIDENT7934 MILLER 3300.0 CLERK
The above is an example of Oracle visiting the cursor in the out parameter. If you have any questions, please leave a message or exchange discussions with the community at this site. Thank you for reading. I hope you can help us. Thank you for your support to this site!
