從PL/SQL儲存函式返回陣列

derekzhan發表於2010-08-06
SQL儲存函式陣列

完成此方法指南後，您應該能夠：

  • 在 Oracle 資料庫中建立 VARRAY

  • 使用 oracle.sql.ARRAY 類

  • 從 Java 訪問 VARRAY

簡介

本文件演示如何從 PL/SQL 函式返回陣列並從 java 應用程式訪問它。陣列是一組有序的資料元素。 VARRAY 是大小可變的陣列。它具有資料元素的排列集，並且所有元素屬於同一資料型別。每個元素都具有索引，它是與元素在 VARRAY 中的位置相對應的一個數字。 VARRAY 中元素的數量是 VARRAY 的“大小”。在宣告 VARRAY 型別時，必須指定其最大值。

在此方法指南中，PL/SQL 儲存函式從 SCOTT 模式的 EMP 表中取出所有僱員的姓名，以這些姓名建立一個陣列並將其返回。從 Java 應用程式呼叫此 PL/SQL 儲存函式，向使用者顯示僱員的姓名。

軟體需求

  • Oracle9i Database version 9.0.1 或更新版本。您可從 Oracle 技術網下載 Oracle9i 資料庫。

  • JDK1.2.x 或更高版本。可從此處下載。

  • Oracle9i JDBC 驅動程式。JDBC 驅動程式可從 ORACLE_HOME/jdbc/lib 處獲得。也可從此處下載。

在資料庫中建立一個 SQLVARRAY 型別，在本例中，它是 VARCHAR2 型別。 作為 scott/tiger 使用者連線到資料庫，並在 SQL 提示符處執行以下命令。

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

然後建立下面的函式，它返回一個 VARRAY。

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
  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 應用程式執行 PL/SQL 儲存函式。單擊此處檢視完整的應用程式原始碼。

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("Array is of type " +  simpleArray.getSQLTypeName());

    System.out.println("Array element is of type code "+simpleArray.getBaseType());

    System.out.println("Array is of length " + 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() 方法獲得陣列的內容並將內容列印出來。

總結

本文件說明了如何建立 VARRAY 並從 PL/SQL 儲存函式返回陣列，以及如何從 java 應用程式訪問它

 

參考：http://www.oracle.com/technology/global/cn/sample_code/tech/java/codesnippet/jdbc/varray/index.html

相關文章