Oracle存储过程(或函数)返回游标、动态数组与java调用
1:如何从 PL/SQL 存储函数返回数组在数据库中创建一个 SQLVARRAY 类型，在本例中，它是 VARCHAR2 类型。 作为 scott/tiger 用户连接到数据库，并在 SQL 提示符处执行以下命令。
CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
然后创建下面的函数，它返回一个 VARRAY。
CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAYASl_data EmpArray := EmpArray();CURSOR c_emp IS SELECT ename FROM EMP;BEGIN FOR emp_rec IN c_emp LOOP l_data.extend; l_data(l_data.count) := emp_rec.ename; END LOOP; RETURN l_data;END;
在数据库中创建函数后，可以从 java 应用程序调用它并在应用程序中获得数组数据。
public static void main( ) {//...OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall( "begin ?:= getEMpArray; end;" ); // The name we use below, EMPARRAY, has to match the name of the type defined in the PL/SQL Stored Function stmt.registerOutParameter( 1, OracleTypes.ARRAY,"EMPARRAY" ); stmt.executeUpdate(); // Get the ARRAY object and print the meta data assosiated with it ARRAY simpleArray = stmt.getARRAY(1); System.out.println("the type of the array is " +simpleArray.getSQLTypeName()); System.out.println("the type code of the element in the array is "+simpleArray.getBaseType()); System.out.println("the length of the array is " + simpleArray.length()); // Print the contents of the array String[] values = (String[])simpleArray.getArray(); for( int i = 0; i < values.length; i++ ) System.out.println( "row " + i + " = '" + values +"'" );//...}
在上面的代码段中，可以看到 OracleCallableSatatement 用于调用 PL/SQL 存储函数。在执行 PL/SQL 存储函数前，将返回的数据类型注册为 OracleTypes.ARRAY，并且指定在数据库中定义的类型名称 (EMPARRAY)。然后执行 PL/SQL 存储函数并获得 oracle.sql.ARRAY 形式的返回值。 oracle.sql.ARRAY 类拥有的方法可以获得关于数组的详细信息，如数组类型、数组长度等。使用 oracle.sql.ARRAY 的 getArray() 方法获得数组的内容并将内容打印出来。
2.函数怎样返回游标,以及如何调用
package Demo;import java.io.*;//Importing the Oracle Jdbc driver package makes the code more readableimport oracle.jdbc.*;import java.sql.*;class OracleRef{public static void main (String args []) throws SQLException{ // Load the driver DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); String url = "jdbc:oracle:thin:@localhost:1521:yangyang"; try { String url1 = System.getProperty("JDBC_URL"); if (url1 != null) url = url1; } catch (Exception e) { // If there is any security exception, ignore it // and use the default } // Connect to the database Connection conn = DriverManager.getConnection (url, "scott", "tiger"); // Create the stored procedure init (conn); // Prepare a PL/SQL call CallableStatement call = conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}"); // Find out all the SALES person call.registerOutParameter (1, OracleTypes.CURSOR); call.setString (2, "SALESMAN"); call.execute (); ResultSet rset = (ResultSet)call.getObject (1); // Dump the cursor while (rset.next ()) System.out.println (rset.getString ("ENAME")); // Close all the resources rset.close(); call.close(); conn.close();}// Utility function to create the stored procedurestatic void init (Connection conn) throws SQLException{ Statement stmt = conn.createStatement (); stmt.execute ("create or replace package java_refcursor as " +"type myrctype is ref cursor return EMP%ROWTYPE; " +"function job_listing (j varchar2) return myrctype; " +"end java_refcursor;"); stmt.execute ("create or replace package body java_refcursor as " +"function job_listing (j varchar2) return myrctype is " +" rc myrctype; " +"begin " +" open rc for select * from emp where job = j; " +" return rc; " +"end; " +"end java_refcursor;"); stmt.close();}}
页:
[1]