Oracle存储过程(或函数)返回游标、动态数组与java调用

[color=darkblue]1:如何从 PL/SQL 存储函数返回数组[/color]


在数据库中创建一个 SQLVARRAY 类型，在本例中，它是 VARCHAR2 类型。 作为 scott/tiger 用户连接到数据库，并在 SQL 提示符处执行以下命令。

CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)

然后创建下面的函数，它返回一个 VARRAY。

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAYAS  l_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[i] +"'" );//...
}

[color=brown]在上面的代码段中，可以看到 OracleCallableSatatement 用于调用 PL/SQL 存储函数。在执行 PL/SQL 存储函数前，将返回的数据类型注册为 OracleTypes.ARRAY，并且指定在数据库中定义的类型名称 (EMPARRAY)。然后执行 PL/SQL 存储函数并获得 oracle.sql.ARRAY 形式的返回值。 oracle.sql.ARRAY 类拥有的方法可以获得关于数组的详细信息，如数组类型、数组长度等。使用 oracle.sql.ARRAY 的 getArray() 方法获得数组的内容并将内容打印出来。 [/color]

[color=darkblue]2.函数怎样返回游标,以及如何调用[/color]

package Demo;

import java.io.*;

//Importing the Oracle Jdbc driver package makes the code more readable
import 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 procedure
static 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();
}
}