오라클 프로시져에서 커서타입리턴 {PROCEDURE:REFCURSOR}

이번 포스팅에서는  {오라클 커서타입:REFCURSOR}을 리턴하는 프러시저(PROCEDURE)를 생성해 보고 생성된 프로시져를 패키지로 묶어서 배포하고 호출하고 실행까지 해보는 예제를 작성해 보았습니다.

 

커서타입은 정확한 테이블 타입을 몰라도 범용적으로 테이블형태의 데이터를 가공하기에 용의 합니다.

사용된 쿼리문은 간단하게 사용자테이블을 LIKE조건으로 조회하는 부분과 부서테이블의 부서를 LIKE조건으로 검색하는 2개의 프러시저 호출해 보는 예제코드입니다.

 

구조는 {패키지 > 2개의 프로시져}로 구성되어 있습니다.

1. 사용자 조회쿼리

SELECT
A.*
FROM(
    SELECT 
      MEM_ID
    , SUR_NAME
    , GIVEN_NAME
    , SUR_NAME || GIVEN_NAME AS FULL_NAME
    FROM MEMBER
    WHERE MEM_ID LIKE '%'||''||'%'
) A
WHERE 1 = 1
  AND (
    MEM_ID LIKE '%'||''||'%'
    OR
    FULL_NAME LIKE '%'||''||'%'
  )
;

 

 

2. 부서 조회쿼리

SELECT 
  DEPT_ID
, PART_ID
, PART_NM
FROM COM_PART
WHERE 1 = 1
 AND (DEPT_ID LIKE '%'||''||'%' OR PART_NM LIKE '%'||''||'%')
;

 

3. 패키지 헤더 부분 생성

CREATE OR REPLACE PACKAGE MY_PACKAGE AS
  PROCEDURE PROC_MEMBER_LIST (
    P_SEARCH_KEYWORD IN VARCHAR2,
    P_CURSOR OUT SYS_REFCURSOR
  );
  
  PROCEDURE PROC_PART_LIST (
    P_SEARCH_KEYWORD IN VARCHAR2,
    P_CURSOR OUT SYS_REFCURSOR
  );
END MY_PACKAGE;

 

 

4. 패키지 바디 부분 생성 

CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS
  PROCEDURE PROC_MEMBER_LIST (
    P_SEARCH_KEYWORD IN VARCHAR2,
    P_CURSOR OUT SYS_REFCURSOR
  ) AS
  BEGIN
    OPEN P_CURSOR FOR
      SELECT A.*
      FROM (
        SELECT 
          MEM_ID,
          SUR_NAME,
          GIVEN_NAME,
          SUR_NAME || GIVEN_NAME AS FULL_NAME
        FROM MEMBER
        WHERE MEM_ID LIKE '%' || P_SEARCH_KEYWORD || '%'
          OR FULL_NAME LIKE '%' || P_SEARCH_KEYWORD || '%'
      ) A;
  END PROC_MEMBER_LIST;
  
  PROCEDURE PROC_PART_LIST (
    P_SEARCH_KEYWORD IN VARCHAR2,
    P_CURSOR OUT SYS_REFCURSOR
  ) AS
  BEGIN
    OPEN P_CURSOR FOR
      SELECT DEPT_ID, PART_ID, PART_NM
      FROM COM_PART
      WHERE DEPT_ID LIKE '%' || P_SEARCH_KEYWORD || '%'
        OR PART_NM LIKE '%' || P_SEARCH_KEYWORD || '%';
  END PROC_PART_LIST;
END MY_PACKAGE;

 

5. 패키지결과 실행 

패키지 결과를 실행하는 방법 2가지를 소개합니다.

2번째 방식이 더 간결하고 빠르게 확인할 수 있기 때문에 먼저 설명드리겠습니다.

프러시저에서 DBMS_OUTPUT.PUT_LINE으로 프린트한 내용을 확인하시려면 SET SERVEROUTPUT ON; 명령을 실행해서 아웃풋 메시지를 받을 수 있도록 설정해 줍니다.

그리고 해당출력창은 상단 메뉴에서 DBMS 출력(D)을 선택해서 오픈하실 수 있습니다.

 

5. 패키지의 사용자호출 프러시저 실행

VARIABLE P_CURSOR REFCURSOR;
EXECUTE MY_PACKAGE.PROC_MEMBER_LIST('', :P_CURSOR);
PRINT P_CURSOR;

 

 

6. 패키지의 부서호출 프로시져 실행

VARIABLE P_CURSOR REFCURSOR;
EXECUTE MY_PACKAGE.PROC_PART_LIST('5', :P_CURSOR);
PRINT P_CURSOR;

 

7. 다른 방식으로 실행하기

DECLARE
  P_SEARCH_KEYWORD VARCHAR2(100) := '희';
  P_CURSOR SYS_REFCURSOR;
  P_MEM_ID_RESULT VARCHAR2(100);
  P_SUR_NAME_RESULT VARCHAR2(100);
  P_GIVEN_NAME_RESULT VARCHAR2(100);
BEGIN
  MY_PACKAGE.PROC_MEMBER_LIST(P_SEARCH_KEYWORD, P_CURSOR);
  
  LOOP
    FETCH P_CURSOR INTO P_MEM_ID_RESULT, P_SUR_NAME_RESULT, P_GIVEN_NAME_RESULT;
    EXIT WHEN P_CURSOR%NOTFOUND;
    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE('MEM_ID: ' || P_MEM_ID_RESULT || ', SUR_NAME: ' || P_SUR_NAME_RESULT || ', GIVEN_NAME: ' || P_GIVEN_NAME_RESULT);
  END LOOP;
  
  CLOSE P_CURSOR;
END;