Using Java Custom Type In Oracle

因為PL/SQL本身沒有Mutil Thread 機制，
若需要使用到Mutil Thread 就必須利用JVM，
將任務丟給Java並繼續執行下段Statment。

本次的範例為：輸入指定的隊伍人數，JVM會自動產出隊伍成員，且回傳整支隊伍。

Oracle Function :

CREATE OR REPLACE FUNCTION CreatTeam(Num IN NUMBER)
RETURN TEAMWITHPLAYER
AS
tempNum Number;
BEGIN
    FOR i IN 1..Num
    loop
        SELECT  ROUND(DBMS_RANDOM.VALUE(20,30))into tempNum FROM DUAL;
        --addPlayer(姓名、地址、年齡)都採Random輸出
        AddPlayerToTeam.addPlayer('Name A'||i ,'Taipei No.'||i*10,tempNum);
    end loop;
  
    RETURN AddPlayerToTeam.getAll();
END;

上段程式碼為主程式，藉由輸入的Num數產生所成員數目。
最後再傳回整個Team的資料。

Oracle Package

CREATE OR REPLACE PACKAGE AddPlayerToTeam
            IS
              Procedure addPlayer( NameStr IN VARCHAR2 , AddrStr IN VARCHAR2 , AgeStr IN VARCHAR2 );      
              Function getAll Return TEAMWITHPLAYER;
            End AddPlayerToTeam;

CREATE OR REPLACE PACKAGE BODY AddPlayerToTeam
            IS
              Procedure addPlayer( NameStr IN VARCHAR2 , AddrStr IN VARCHAR2 , AgeStr IN VARCHAR2 )  as language java name 'Kunde.Test.Java.AddPlayerToTeam.addString(java.lang.String,java.lang.String,java.lang.String)';
              Function getAll Return TEAMWITHPLAYER as language java name 'Kunde.Test.Java.AddPlayerToTeam.getLog() return oracle.sql.ARRAY';
            End AddPlayerToTeam;

為了在Oracle中使用java必須利用Procedure進行轉介，
在body中加入as language java name ' java code's method name include package name ,class  n parameters  type '。
其中java 的 string 對應到的就是 java.lang.String 。

Oracle UDT

CREATE OR REPLACE TYPE PLAYER AS OBJECT
(
     name varchar2(30),
     address varchar2(60),
     age number(3)
);

CREATE OR REPLACE TYPE EEP.TEAMWITHPLAYER AS TABLE OF PLAYER;

Java Class In Oracle:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED EEP."AddPlayerToTeam" AS
package Kunde.Test.Java;
import java.net.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc2.*;
import oracle.sql.*;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleConnection;
public class AddPlayerToTeam
{
    private static List<Struct> TeamWithPlayer=new ArrayList<Struct>();
    private static OracleDriver ora = new oracle.jdbc.OracleDriver();
    private static Connection conn = null;
    private static OracleConnection oraConn = null;
    private static StructDescriptor sd=null;
    private static int count=0;
  
    static
    {
        try{
            conn = ora.defaultConnection();
            oraConn = (OracleConnection)conn;
            sd = StructDescriptor.createDescriptor("EEP.PLAYER",conn);
        }
        catch(Exception ex)
        {
            System.out.println("Java Init Exception : "+ex);
        }
    }

    public static void addString( String Name ,String Address,String Age) throws Exception
    {
         Object [] attributes ={Name,Address,Age};
         TeamWithPlayer.add(new STRUCT(sd , oraConn , attributes));
    }


    public static void delString()
    {
        TeamWithPlayer=new ArrayList<Struct>();;
        System.gc();
        System.runFinalization();
    }
  
     public static Array getLog()throws Exception
    {
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("EEP.TEAMWITHPLAYER", conn);
        ARRAY result = new ARRAY(descriptor, conn, TeamWithPlayer.toArray(new Struct[TeamWithPlayer.size()]));
        delString();
        return result;
    }
}

執行的script

DECLARE
  RetVal TEAMWITHPLAYER;
  NUM NUMBER;

BEGIN
  NUM := 10;
  RetVal := EEP.CREATTEAM ( NUM );
  Dbms_output.put_line('Name : '||RetVal(NUM).name);
  COMMIT;
END;

結果

Name : Name A10