[Java] 오라클 패키지 자바에서 사용 예제

// 오라클 패키지 프로시저 선언
 

CREATE OR REPLACE PACKAGE SDK_RESULT_PKG
AS 
   TYPE REF_TYPE IS REF CURSOR;
   PROCEDURE SDK_RESULT(
           V_RESULT OUT SDK_RESULT_PKG.REF_TYPE, 
           MSG_TYPE IN VARCHAR2, 
       FROMDATE IN VARCHAR2,
       TODATE IN VARCHAR2
      );
END SDK_RESULT_PKG;

// 오라클 패키지 프로시저 구현

CREATE OR REPLACE PACKAGE BODY SDK_RESULT_PKG 
AS
   PROCEDURE SDK_RESULT(
       V_RESULT OUT SDK_RESULT_PKG.REF_TYPE, 
       MSG_TYPE IN VARCHAR2, 
       FROMDATE IN VARCHAR2,
       TODATE IN VARCHAR2
      )
   AS
       SMS_SQL VARCHAR2(5000);
       MMS_SQL VARCHAR2(5000);
       VMS_SQL VARCHAR2(5000);
       FMS_SQL VARCHAR2(5000);
       SURVEY_SQL VARCHAR2(5000);
       
     BEGIN
       SMS_SQL := 'SELECT MSG,  ';
       SMS_SQL := SMS_SQL || '     USER_ID, ';
       SMS_SQL := SMS_SQL || '     SEND_DATE, ';
       SMS_SQL := SMS_SQL || '     0 AS REPLY_TYPE, ';
       SMS_SQL := SMS_SQL || '     SUM(DEST_COUNT) AS DEST_COUNT, ';
       SMS_SQL := SMS_SQL || '     SUM(SUCC_COUNT) AS SUCC_COUNT, ';
       SMS_SQL := SMS_SQL || '     SUM(FAIL_COUNT) AS FAIL_COUNT, ';
       SMS_SQL := SMS_SQL || '     SUM((DEST_COUNT-SUCC_COUNT) FAIL_COUNT) AS ING_COUNT, ';
       SMS_SQL := SMS_SQL || '     CALLBACK, ';
       SMS_SQL := SMS_SQL || '     SUBSTR(XMLAGG(XMLELEMENT(AB,MSG_ID || ''^'') ORDER BY MSG_ID).EXTRACT(''//text()''),1) AS MSG_ID ';
       SMS_SQL := SMS_SQL || '     FROM  TBL';
       SMS_SQL := SMS_SQL || '     GROUP BY SMS_MSG, USER_ID, SEND_DATE, CALLBACK, RESERVED5';
    
    OPEN V_RESULT FOR SMS_SQL;

  END SDK_RESULT; 
END SDK_RESULT_PKG;

// 자바 실행

// 자바 실행

Connection Conn=null;
ResultSet Rs = null;
CallableStatement cstmt = null;

DBClassTEST DBClass = new DBClassTEST()

DBClass.DB_Info();
DBClass.Connect();
Conn = DBClass.Conn();

//**** DB 연결 부분은 알아서 구현 위 내용은 임시 클래스
 
Qry = "BEGIN SDK_RESULT_PKG.SDK_RESULT(?,?,?,?); END;";

cstmt = Conn.prepareCall(Qry);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.setString(2,"SMS");
cstmt.setString(3,"200901010000");
cstmt.setString(4,"201012312359");

cstmt.execute();
Rs = ((OracleCallableStatement)cstmt).getCursor(1);

ColumnName="TEST"; //컬럼이름

while(Rs.next()){
 System.out.println(Rs.getString(ColumnName));
}
Rs.close();
cstmt.close();
Conn.close();
DBClass.DB_Close();