Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.