참조 커서의 이점

• 프로그래밍의 용이성 모든 커서는 결과집합(커서)을 다룰 수 있다.
• 융통성 클라이언트 애플리케이션은 한번에 몇개의 행을 인출해 올지를 선택할 수 있다.
  10,000개의 항목을 배열에 담아 되돌려 주는 대신 크라이언트가 한번에 10개의 항목을
  인출할 수 있는 결과집합을 반환한다.
• 성능 배열의 마지막까지 채운 후 첫번째 행을 반환할 필요가 없다.
  참조커서는 아무 작업도 하지 않고 데이터를 즉시 클라이언트로 보낸다.

참조커서를 이용하여 결과집합을 반환하라

CREATE PACKAGE demo_pkg
AS
    TYPE varchar2_array IS TABLE OV VAARCHAR2(30) INDEX BY BINARY_INTEGER;
    TYPE rc IS REF CURSOR;
    PROCEDURE index_by(p_owner IN VARCHAR2
                     , p_object_name OUT varchar2_array
                     , p_object_type OUT varchar2_array
                     , p_timestamp   OUT varchar2_array);
    PROCEDURE ref_cursor(p_owner  IN VARCHAR2
                       , p_cursor IN OUT rc);
END;
/

CREATE PACKAGE BODY demo_pkg
AS
    PROCEDURE index_by(p_owner IN VARCHAR2
                     , p_object_name OUT varchar2_array
                     , p_object_type OUT varchar2_array
                     , p_timestamp   OUT varchar2_array)
    IS
    BEGIN
        SELECT object_name, object_type, timestamp
          BULK COLLECT INTO p_object_name, p_object_type, p_timestamp
          FROM t
         WHERE owner = p_owner;
    END;
    
    PROCEDURE ref_cursor(p_owner  IN VARCHAR2
                       , p_cursor IN OUT rc)
    IS
    BEGIN
        OPEN p_cursor FOR
        SELECT object_name, object_type, timestamp
          FROM t
         WHERE owner = p_owner;
    END;

END;
/

• Index_by 루틴은 Bulk Collect를 이용하여 데이터를 인출한다
• Ref_cursor 루틴은 단순히 Open을 수행한다.

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class indexby
{
    static long start = new Date().getTime();
    
    public ststic void showElapsed(String msq)
    {
        long end = new Date().getTime();
        System.out.println(msq + " " + (end - start) + " ms");
        start = end;
    }
    
    public ststic void main(String args[]) throws Exception
    {
        DriverManager.registerDriver
        (new oracle.jdbc.driver.OracleDriver());
        
        Connection conn = DriverManager.getConnection
        ("jdbc:oracle:oci8:@ora920.us.oracle.com","scott","tiger");
        
        showElapsed("Connected, going to prepare");
        
        OracleCallableStatement cstmt =
        (OracleCallableStatement)conn.prepareCall
        ("BEGIN demo_pkg.index_by(?,?,?,?); END;")
        
        showElapsed("Prepared, going to bind");
        
        int maxl        = 15000;
        int elemSqlType = OracleTypes.VARCHAR;
        int elemMaxLen  = 30;
        
        cstmt.setString(1, "SYS");
        cstmt.resisterIndexTableOutParameter(2, maxl, elemSqlType, elemMaxLen);
        cstmt.resisterIndexTableOutParameter(3, maxl, elemSqlType, elemMaxLen);
        cstmt.resisterIndexTableOutParameter(4, maxl, elemSqlType, elemMaxLen);
        
        showElapsed("Bound, going to execute");
        
        cstmt.execute();
        
        Datum[] object_name = cstmt.getOraclePlsqlIndexTable(2);
        Datum[] object_type = cstmt.getOraclePlsqlIndexTable(3);
        Datum[] timestamp   = cstmt.getOraclePlsqlIndexTable(4);
        
        showElapsed("First Row " + object_name.length);
        
        String data;
        int i;
        for (i=0;i<object_name.length;i++)
        {
            data = object_name[i].stringValue();
            data = object_type[i].stringValue();
            data =   timestamp[i].stringValue();
        }
        
        showElapsed("Last Row " + i);
        
    }
}

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class refcur
{
    static long start = new Date().getTime();
    
    public ststic void showElapsed(String msq)
    {
        long end = new Date().getTime();
        System.out.println(msq + " " + (end - start) + " ms");
        start = end;
    }
    
    public ststic void main(String args[]) throws Exception
    {


        DriverManager.registerDriver
        (new oracle.jdbc.driver.OracleDriver());
        
        Connection conn = DriverManager.getConnection
        ("jdbc:oracle:oci8:@ora920.us.oracle.com","scott","tiger");
        
        showElapsed("Connected, going to prepare");
        
        ((OracleConnection)conn).setDefaultRowPrefetch(100);
        
        OracleCallableStatement cstmt =
        (OracleCallableStatement)conn.prepareCall
        ("BEGIN demo_pkg.ref_cursor(?,?); END;")
        
        showElapsed("Prepared, going to bind");
        
        cstmt.setString(1, "SYS");
        cstmt.resisterOutParameter(2, OracleTypes.CURSOR);
        
        showElapsed("Bound, going to execute");
        
        cstmt.execute();
        
        ResultSet rset = (ResultSet)cstmt.getObject(2);
        
        if (rset.next())
            showElapsed("First Row");
        
        String data;
        int i;
        for (i=0;i<rset.next();i++)
        {
            data = rset.getString(1);
            data = rset.getString(2);
            data = rset.getString(3);
        }
        
        showElapsed("Last Row " + i);
        
    }
}

• 실행결과