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

1:如何从 PL/SQL 存储函数返回数组


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

Sql代码  

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

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

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

Sql代码  

1. 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;  

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 应用程序调用它并在应用程序中获得数组数据。

Java代码  

1. public static void main( ) {//...  
2. OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall( "begin ?:= getEMpArray; end;" );     
3.  // The name we use below, EMPARRAY, has to match the name of the type defined in the PL/SQL Stored Function     
4. stmt.registerOutParameter( 1, OracleTypes.ARRAY,"EMPARRAY" );     
5. stmt.executeUpdate();    // Get the ARRAY object and print the meta data assosiated with it     
6. ARRAY simpleArray = stmt.getARRAY(1);      
7. System.out.println("the type of the array is " +  simpleArray.getSQLTypeName());      
8. System.out.println("the type code of the element in the array is "+simpleArray.getBaseType());      
9. System.out.println("the length of the array is " + simpleArray.length());    // Print the contents of the array      
10. String[] values = (String[])simpleArray.getArray();      
11. for( int i = 0; i < values.length; i++ )        
12. System.out.println( "row " + i + " = '" + values[i] +"'" );//...  
13. }  

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] +"'" );//...
}

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

2.函数怎样返回游标,以及如何调用

Java代码  

1. package Demo;  
2.   
3. import java.io.*;  
4.   
5. //Importing the Oracle Jdbc driver package makes the code more readable  
6. import oracle.jdbc.*;  
7. import java.sql.*;  
8. class OracleRef  
9. {  
10. public static void main (String args [])  
11.     throws SQLException  
12. {  
13.  // Load the driver  
14.  DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  
15.   
16.  String url = "jdbc:oracle:thin:@localhost:1521:yangyang";  
17.  try {  
18.    String url1 = System.getProperty("JDBC_URL");  
19.    if (url1 != null)  
20.      url = url1;  
21.  } catch (Exception e) {  
22.    // If there is any security exception, ignore it  
23.    // and use the default  
24.  }  
25.   
26.  // Connect to the database  
27.  Connection conn =  
28.    DriverManager.getConnection (url, "scott", "tiger");  
29.   
30.  // Create the stored procedure  
31.  init (conn);  
32.   
33.  // Prepare a PL/SQL call  
34.  CallableStatement call =  
35.    conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");  
36.   
37.  // Find out all the SALES person  
38.  call.registerOutParameter (1, OracleTypes.CURSOR);  
39.  call.setString (2, "SALESMAN");  
40.  call.execute ();  
41.  ResultSet rset = (ResultSet)call.getObject (1);  
42.   
43.  // Dump the cursor  
44.  while (rset.next ())  
45.    System.out.println (rset.getString ("ENAME"));  
46.   
47.  // Close all the resources  
48.  rset.close();  
49.  call.close();  
50.  conn.close();  
51.   
52. }  
53.   
54. // Utility function to create the stored procedure  
55. static void init (Connection conn)  
56.     throws SQLException  
57. {  
58.  Statement stmt = conn.createStatement ();  
59.   
60.  stmt.execute ("create or replace package java_refcursor as " +  
61.           "  type myrctype is ref cursor return EMP%ROWTYPE; " +  
62.           "  function job_listing (j varchar2) return myrctype; " +  
63.           "end java_refcursor;");  
64.   
65.  stmt.execute ("create or replace package body java_refcursor as " +  
66.           "  function job_listing (j varchar2) return myrctype is " +  
67.           "    rc myrctype; " +  
68.           "  begin " +  
69.           "    open rc for select * from emp where job = j; " +  
70.           "    return rc; " +  
71.           "  end; " +  
72.           "end java_refcursor;");  
73.  stmt.close();  
74. }  
75. }  
76.    

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();
}
}