/ / ================================================= Package, and stored procedures // Please note the declaration of the cursor variable and the method of use // The cursor here is output (OUT) parameter / / ==================== =============================== Create or Replace Package Audit IS
- Public Type Declarations, Cursor VariableType Outlist is Ref Cursor
Procedure sp_audit_gettaxpayerlist (Organize In Varchar2, TaxPayerlist Out Outlist);
End Audit;
create or replace package body audit is-- taxpayerList is cursor variablePROCEDURE sp_audit_GetTaxpayerList (organize in varchar2, taxpayerList out outList) is beginOPEN taxpayerList FORselect strName, strTelephone from "TB_PERSONINFO" a WHERE a.organize = organize;
END;
End Audit;
/ / ======================================================= Is a Java source code, read records from Oracle stored procedures // Note Be sure to use Oracle's JDBC API (you can download it from Oracle website) / / =================== =============================================================================00 = NULL;
Class.Forname ("Oracle.jdbc.driver.Oracledriver"); conn = drivermanager.getConnection ("JDBC: Oracle: Thin: @ 192.168.0.1: 1521: ORA", "Net7b.com", "Net7b"); Oracle .jdbc.OracleCallableStatement stmt = (oracle.jdbc.OracleCallableStatement) conn.prepareCall ( "{call audit.sp_audit_GetTaxpayerList (?,)?}"); stmt.setString (1, "23201020100"); stmt.registerOutParameter (2, oracle .jdbc.oracletypes.cursor; stmt.execute (); rs = stmt.getcursor (2); for (int i = 0; rs.next (); {system.out.println ("---- [ " i "] name: " rs.getstring (1) " Telephone: " rgetstring (2));}}}} Catch (Exception EX) {EX.PrintStackTrace ();}
This is part of an example of using a cursor variable as an input (IN) parameter. I don't have to talk nonsense, I believe that everyone can try to write such an example.
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice NUMBER) ISBEGINIF choice = 1 THENOPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THENOPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THENOPEN generic_cv FOR SELECT * FROM salgrade; END IF; ... END;
