plsql 오라클 프로시저 파라미터 테이블에 사용

오라클 프로시저 파라미터 테이블에 사용

 

 

오라클 프로시저를 사용할 때 파라미터를 테이블에 사용하는 방법에 대해 알아보도록 하겠습니다.

 

 

그럼 일단 아래 예시 프로시저를 확인해볼까요!

 

 

CREATE OR REPLACE PROCEDURE PROC_TABLE_PARAM(p_TB_ID IN VARCHAR2, p_RET OUT VARCHAR2)
IS

/*----------------------------------------------------------------------------
    프로그램명: PROC_TABLE_PARAM 
    작성일 : 2020/08/25
    내  용 : 동적 테이블 파라메터를 이용하여 사용
------------------------------------------------------------------------------*/

    v_FILE_NM   VARCHAR2(100);
    v_ERR_MESG  VARCHAR2(4000);        
    v_RET       VARCHAR2(1000);
    v_SQL       VARCHAR2(1000);
    
BEGIN
    
    BEGIN

        BEGIN

              v_SQL := 'SELECT SUBSTR(FILE_ID, 1, INSTR(FILE_ID , '''||'.'||''',1)-1) AS FILE_ID FROM '|| p_TB_ID ||' WHERE ROWNUM = 1' ;
              EXECUTE IMMEDIATE v_SQL INTO v_FILE_NM ;

        END;

        BEGIN
          
                SELECT REGEXP_REPLACE ( TO_CHAR(WM_CONCAT(ERROR_MSG)), ',', ' | ') AS MSG
                  INTO v_ERR_MESG
                  FROM TEST_A T1
                ;
                
                
        END;
            
        BEGIN
           
            UPDATE TEST_B
            SET    ERR_CODE = p_ERR_TYPE
                 , ERR_MESG = v_ERR_MESG
            WHERE  FILE_ID = v_FILE_NM
            ;      
         END;  
         
         
         COMMIT;
            
    END;

    p_RET := 'OK';
        
EXCEPTION                                                                                          

    WHEN OTHERS THEN
        ROLLBACK;
        p_RET := 'ERROR';
  
END;

 

 

 

여기서 중요하게 봐야 할 부분만 딱 집어보겠습니다.

 

 

#1. 테이블명 파라메터 받기

CREATE OR REPLACE PROCEDURE PROC_TABLE_PARAM(p_TB_ID IN VARCHAR2, p_RET OUT VARCHAR2)

 

p_TB_ID IN VARCHAR2 는 파라메터를 외부에서 받을 수 있습니다.

 

 

테이블명을 외부 프로그램에서 받아오면 프로시져에서 사용할 수 있겠네요

 

 

#2. 테이블명 FROM 절에서 사용하기

v_SQL := 'SELECT SUBSTR(FILE_ID, 1, INSTR(FILE_ID , '''||'.'||''',1)-1) AS FILE_ID FROM '|| p_TB_ID ||' WHERE ROWNUM = 1' ; 
EXECUTE IMMEDIATE v_SQL INTO v_FILE_NM ;

 

 

SQL 문장을 변수에 담아서 문장을 만듭니다.

 

문장을 만들 땐 FROM 절에서 파라메터로 받아온 테이블을 파이프(||) 로 연결하여 사용하였습니다.

 

그리고 EXECUTE IMMEDIATE 처리 하여 SQL 을 실행하도록 처리하였습니다.

 

 

 

간단하게 다이나믹 SQL 을 사용할 수 있었습니다.

 

 

잘 안되거나 궁금한것이 있으면 댓글로 같이 연구해보겠습니다.