> Programming > Oracle StoredProcedure에서 RowSet 반환 받기.

Oracle StoredProcedure에서 RowSet 반환 받기.

어제 후배가 StoredProcedure(이하 SP)에서 RowSet을 반환 받는 방법을 문의 했었다. 그래서 너무도 간단하게 SP에서 Select문을 노출하면 그게 반환값으로 떨어진다고 알려주었다. 그런데, 얼마뒤에 그렇게 해서는 INTO문이 없어서 에러가 난다고 해서, DB종류를 물어보니 SQLServer가 아닌 오라클 이였다. 알려준 후에 추후에 비슷한 노력을 할 것 같아 블로그에 관련 내용을 정리하기로 했다.

일단 오라클 SP에서 어떤 값을 반환받기 위해서는 파라미터에 OUT 속성을 주어서 정의하고 그 값으로 반환 받아야 한다. 그런데, 지금 반환 받고자 하는 것은 RowSet 이므로 CURSOR 형태가 되는데, 일반 커서는 안되고 REF CUR형을 써야 한다.
그럼 REF CUR형을 반환 받는 간단한 SP를 작성해 보겠다.

CREATE OR REPLACE
PROCEDURE SP_TestRefCursor (DNO IN NUMBER,
                               IO_CURSOR IN OUT REF CURSOR)
    IS 
        V_CURSOR T_CURSOR; 
    BEGIN 
        IF DNO <>  0 
        THEN
             OPEN V_CURSOR FOR 
             SELECT pkid, ename, remark, tkdevice
                  FROM DEVICE
                  WHERE TKDEVICE = DNO;
        ELSE 
             OPEN V_CURSOR FOR 
             SELECT pkid, ename, remark, tkdevice
                  FROM DEVICE
                  WHERE TKDEVICE < DNO;
        END IF;
        IO_CURSOR := V_CURSOR; 
    END; 

이렇게 작성을 하고 나니 에러가 발생한다. 문제는 CURSOR는 식별자로 사용할 수가 없는 것이다.
그래서 그럼 REF CURSOR를 타입을 만들어서 사용을 해야 겠다는 생각을 했다. 그래서 SP 단독 사용에서 패키지로 방법을 전환했다.
그럼 우선 패키지 선언부터 해야겠지? 패키지 선언부 이다.

CREATE OR REPLACE
PACKAGE CURSOR_PKG AS 
    TYPE T_CURSOR IS REF CURSOR; 
    PROCEDURE SP_TestRefCursor (DNO IN NUMBER, 
                               IO_CURSOR IN OUT T_CURSOR); 
END CURSOR_PKG;

다음으로 패키지 바디인 프로시져 정의를 한다.

CREATE OR REPLACE PACKAGE BODY CURSOR_PKG AS
PROCEDURE SP_TestRefCursor (DNO IN NUMBER, 
                          IO_CURSOR IN OUT T_CURSOR)
IS 
    V_CURSOR T_CURSOR; 
BEGIN 
    IF DNO <>  0 
    THEN
         OPEN V_CURSOR FOR 
         SELECT pkid, ename, remark, tkdevice
              FROM DEVICE
              WHERE TKDEVICE = DNO;
    ELSE 
         OPEN V_CURSOR FOR 
         SELECT pkid, ename, remark, tkdevice
              FROM DEVICE
              WHERE TKDEVICE = DNO;
    END IF;
    IO_CURSOR := V_CURSOR; 
END SP_TestRefCursor; 
END CURSOR_PKG;

이상으로 데이터베이스에서 해줄 작업을 끝났다. 패키지와 프로시져를 잘 컴파일을 해두면 된다.
그리고, 다음은 이 RowSet을 소비하는 C#코드이다.

DataSet ds = new System.Data.DataSet();

StringBuilder strBuilder = new StringBuilder();
strBuilder.AppendFormat("user id={0};password={1};data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host={2})(Port={3}))(CONNECT_DATA=(SERVICE_NAME={4})));pooling=true",
    /*userID*/, /*Password*/, /*DB IP*/, /*DB Port*/, /*Service Name*/);
OracleConnection con = new OracleConnection(strBuilder.ToString());

con.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "CURSOR_PKG.SP_TestRefCursor";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("DNO", OracleDbType.Int32,  2, System.Data.ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("IO_CURSOR", OracleDbType.RefCursor,System.Data.ParameterDirection.Output));

OracleDataAdapter adt = new OracleDataAdapter(cmd);
adt.Fill(ds);

if (ds.Tables.Count > 0) 
{
       int i = 0;
       foreach(DataRow dr in ds.Tables[0].Rows) 
       {
             i += 1;
             System.Diagnostics.Debug.WriteLine("Row : {0}\t {1}\t {2}\t {3}\t {4}", i, dr[0], dr[1], dr[2], dr[3]);
       }
}

con.Close();

출력창에 값이 출력되는 것을 볼 수 있었다.

오라클은 아무래도 SQLServer보다 엄격한 데이터 사용 규칙을 정해서 개발해야 하는 것으로 보인다.
이상으로 오라클 SP에서 RowSet을 반환 받는 내용의 정리글 이었습니다.

  1. 익명
    2013/02/21 13:43

    잘 배우고 갑니다. 고맙습니다. ^^

    • 2013/02/21 16:17

      도움이 되셨다니 저도 기쁩니다.
      감사합니다.

  2. 익명
    2014/01/12 13:27

    좋은 정보 감사합니다.

    • 2014/01/15 01:31

      읽고, 이해가 되셨나요? 댓글도 남겨 주시고, 저도 감사합니다.

  3. 익명
    2016/02/23 10:52

    제가 궁금한 사항들이 너무 한번에 많이 해결되서 감사합니다!!!
    하나 궁금한 사항이 있는데, pl/sql에서 rowset(or dataset)으로 출력하기 위해서 sql-server와 달리 cursor를 이용해야 하는 것은 일종의 oracle 방침인건가요ㅠㅜ..?

  1. No trackbacks yet.

답글 남기기

아래 항목을 채우거나 오른쪽 아이콘 중 하나를 클릭하여 로그 인 하세요:

WordPress.com 로고

WordPress.com의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Twitter 사진

Twitter의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Facebook 사진

Facebook의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

Google+ photo

Google+의 계정을 사용하여 댓글을 남깁니다. 로그아웃 / 변경 )

%s에 연결하는 중

%d 블로거가 이것을 좋아합니다: