Java Call PLSQL With Input data type Table Array of Records

 ตัวอย่าง Code Java กรณีที่เราต้องการ ส่ง Parameter เข้า Oracle PLSQL แบบ รับ Input เป็น Array of Records  ข้อดีคือทำให้เราสามารถ Call PLSQL ผ่าน JDBC ครั้งเดียวแล้วส่ง List of Data เข้าไป ประมวลผลใน PLSQL ได้เลย (ปรกติก็วนเรียก ทีละ record เอาก็ได้อ่ะนะ ^^)

สิ่งสำคัญที่ต้องทำเพิ่มเติมมีดังต่อไปนี้

1. การ Map Data Type โดยจะต้องทำการ Mapping ทั้ง Type ที่เป็น Record และ Type Array ตามตัวอย่างด้านล่าง

        StructDescriptor sd = StructDescriptor.createDescriptor("MY_RECORD", conn);
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("MY_ARRAY", conn);

conn หมายถึงตัวแปร Connection ที่ connect database ไว้แล้วนะครับ ซึ่ง การ Run สองคำสั่งนี้ ทางฝั่ง Database Oracle จะต้องมีการ Create TYPE ตามที่เราส่ง parameter ไว้แล้วนะครับ ถ้าหากไม่มีจะ เกิด Exception ขึ้นทันที  

ตัวอย่างการ Create type ที่ Oracle PLSQL ก็ประมาณนี้ 

ตัวอย่างการ Create Type ที่เป็น Record (Object)

CREATE OR REPLACE TYPE MY_RECORD AS OBJECT
  (
    VAL1 VARCHAR2(10),
    VAL2 VARCHAR2(20),
    VAL3 NUMBER(15, 5)
  )

ตัวอย่างการ Create Type ที่เป็น Array (Table)

create or replace type MY_ARRAYis table of  MY_RECORD

ขั้นตอนต่อมา คือการ ปั้น Data  ก่อนส่งเข้า PLSQL

        STRUCT[] recArr = new STRUCT[10];
        for (int i = 0; i < 10; i++) {
            STRUCT st=new STRUCT(sd, conn, new Object[] { "data1"+i, "data2",999});
            recArr[i] = st;
        }
        ARRAY oracleArray = new ARRAY(ad, conn, recArr);

จุดสำคัญอยู่ใน loop for นะครับ เป็นจุดที่เราจะ วน list data จาก source ของเราเพื่อยัดลง Array ก่อนนำไป call PLSQL อีกที
ตอน Create Object[] และป้อน data เข้าไปนั้นจะต้องเรียงลำดับ และตรงกับ Field ของ TYPE Record (Object) ที่เราสร้าง

จากนั้นก็ทำการ Call PLSQL ตามปรกติได้เลยครับ

        stmt = (OracleCallableStatement) conn.prepareCall("{call    my_pack.tet_proc(?,?,?) }");
        stmt.setObject(1, oracleArray);
        stmt.registerOutParameter(2, OracleTypes.VARCHAR);
        stmt.registerOutParameter(3, OracleTypes.VARCHAR);
        stmt.execute();

stmt คือ OracleCallableStatement ที่สร้างไว้ก่อนหน้านะครับ เดี๋ยวจะมี code เต็มๆ ให้ดูอีกที และก็ที่สำคัญตอนเรา โยน Input ที่เป็น Array เข้าไปให้เรียก ใช้  stmt.setObject  ได้เลยครับ ส่วนการเรียงลำดับการส่ง parameter ก็ตามโครงสร้าง PLSQL อ่ะนะละไว้ในฐานที่เข้าใจครับ

ตัวอย่าง Code เต็มๆ ตามด้านล่างครับ

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.jdbc.internal.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class Test {
    public static void passArrayOfRec() throws SQLException {
    
        Connection conn = null;
        OracleCallableStatement stmt = null;
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:dbtest", "admin", "1234");
     
        StructDescriptor sd = StructDescriptor.createDescriptor("MY_RECORD", conn);
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("MY_ARRAY", conn);
        
        STRUCT[] recArr = new STRUCT[10];
        for (int i = 0; i < 10; i++) {
            STRUCT st=new STRUCT(sd, conn, new Object[] { "DATA1"+i, "DATA2",999});
            recArr[i] = st;
        }
        ARRAY oracleArray = new ARRAY(ad, conn, recArr);

        stmt = (OracleCallableStatement) conn.prepareCall("{call    mypack.test_proc(?,?,?) }");
        stmt.setObject(1, oracleArray);
        stmt.registerOutParameter(2, OracleTypes.VARCHAR);
        stmt.registerOutParameter(3, OracleTypes.VARCHAR);
        stmt.execute();
    }

    public static void main(String[] args) {
        try {
            Test.passArrayOfRec();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

} 

ลองดูนะครับหวังว่าจะเป็นประโยชน์ไม่มากก็น้อย ^^