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

永不落伍

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



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




Sql代码

• CREATEORREPLACETYPEEMPARRAYisVARRAY(20)OFVARCHAR2(30)
  

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




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



Sql代码

• CREATEORREPLACEFUNCTIONgetEmpArrayRETURNEMPARRAYASl_dataEmpArray:=EmpArray();CURSORc_empISSELECTenameFROMEMP;BEGINFORemp_recINc_empLOOPl_data.extend;l_data(l_data.count):=emp_rec.ename;ENDLOOP;RETURNl_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代码

• publicstaticvoidmain(){//...
  
• OracleCallableStatementstmt=(OracleCallableStatement)conn.prepareCall("begin?:=getEMpArray;end;");
  
• //Thenameweusebelow,EMPARRAY,hastomatchthenameofthetypedefinedinthePL/SQLStoredFunction
  
• stmt.registerOutParameter(1,OracleTypes.ARRAY,"EMPARRAY");
  
• stmt.executeUpdate();//GettheARRAYobjectandprintthemetadataassosiatedwithit
  
• ARRAYsimpleArray=stmt.getARRAY(1);
  
• System.out.println("thetypeofthearrayis"+simpleArray.getSQLTypeName());
  
• System.out.println("thetypecodeoftheelementinthearrayis"+simpleArray.getBaseType());
  
• System.out.println("thelengthofthearrayis"+simpleArray.length());//Printthecontentsofthearray
  
• String[]values=(String[])simpleArray.getArray();
  
• for(inti=0;i<values.length;i++)
  
• System.out.println("row"+i+"='"+values+"'");//...
  
• }
  

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.函数怎样返回游标,以及如何调用





Java代码

• packageDemo;
  
• 
  
• importjava.io.*;
  
• 
  
• //ImportingtheOracleJdbcdriverpackagemakesthecodemorereadable
  
• importoracle.jdbc.*;
  
• importjava.sql.*;
  
• classOracleRef
  
• {
  
• publicstaticvoidmain(Stringargs[])
  
• throwsSQLException
  
• {
  
• //Loadthedriver
  
• DriverManager.registerDriver(neworacle.jdbc.OracleDriver());
  
• 
  
• Stringurl="jdbc:oracle:thin:@localhost:1521:yangyang";
  
• try{
  
• Stringurl1=System.getProperty("JDBC_URL");
  
• if(url1!=null)
  
• url=url1;
  
• }catch(Exceptione){
  
• //Ifthereisanysecurityexception,ignoreit
  
• //andusethedefault
  
• }
  
• 
  
• //Connecttothedatabase
  
• Connectionconn=
  
• DriverManager.getConnection(url,"scott","tiger");
  
• 
  
• //Createthestoredprocedure
  
• init(conn);
  
• 
  
• //PrepareaPL/SQLcall
  
• CallableStatementcall=
  
• conn.prepareCall("{?=calljava_refcursor.job_listing(?)}");
  
• 
  
• //FindoutalltheSALESperson
  
• call.registerOutParameter(1,OracleTypes.CURSOR);
  
• call.setString(2,"SALESMAN");
  
• call.execute();
  
• ResultSetrset=(ResultSet)call.getObject(1);
  
• 
  
• //Dumpthecursor
  
• while(rset.next())
  
• System.out.println(rset.getString("ENAME"));
  
• 
  
• //Closealltheresources
  
• rset.close();
  
• call.close();
  
• conn.close();
  
• 
  
• }
  
• 
  
• //Utilityfunctiontocreatethestoredprocedure
  
• staticvoidinit(Connectionconn)
  
• throwsSQLException
  
• {
  
• Statementstmt=conn.createStatement();
  
• 
  
• stmt.execute("createorreplacepackagejava_refcursoras"+
  
• "typemyrctypeisrefcursorreturnEMP%ROWTYPE;"+
  
• "functionjob_listing(jvarchar2)returnmyrctype;"+
  
• "endjava_refcursor;");
  
• 
  
• stmt.execute("createorreplacepackagebodyjava_refcursoras"+
  
• "functionjob_listing(jvarchar2)returnmyrctypeis"+
  
• "rcmyrctype;"+
  
• "begin"+
  
• "openrcforselect*fromempwherejob=j;"+
  
• "returnrc;"+
  
• "end;"+
  
• "endjava_refcursor;");
  
• stmt.close();
  
• }
  
• }
  
• 
  

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