"프로시져 샘플"의 두 판 사이의 차이

DB CAFE

2019년 7월 8일 (월) 20:07 판

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

1 기본 프로시져[편집]

DECLARE
--변수,상수 선언
BEGIN
--실행 가능 SQL문,PL/SQL문
EXCEPTION
--에러처리
END;

/* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */
Declare
  v_sale number := 2000;
begin
  update test set a = v_sale;
  delete from test where a = v_sale;
  commit;
end;

/* Procedure에서 Procedure를 호출하는 방법 */
A프로시져에서 "B프로시져명(변수, 변수2);"

PROCEDURE 리턴 여러개 
FUNCTION 리턴 한개

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE';

/* 프로시저나 함수 조회 */
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION';

/* PROCEDURE & FUNCTION 삭제하기 */
DROP FUNCTION lee2;
DROP PROCEDURE lee2;

2 커서 활용 샘플[편집]

CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_OBJ_INVALID_TO_VALID
/*

*/
(
    IN_DATE IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD')
)
IS

    CURSOR INVALID_OBJECT IS                          
        
        SELECT A.GRANTEE, A.OWNER, A.TABLE_NAME, A.GRANTOR, A.PRIVILEGE,A.OBJECT_TYPE
          FROM TB_MGR_GRANT A                   
    ;  

                                                  
--    V_SQL   VARCHAR2(200);
    V_G_SQL  VARCHAR2(200);
--    V_L_SQL  VARCHAR2(2000);

    V_GRANTEE  VARCHAR2(100);
    V_OWNER  VARCHAR2(100);
    V_TABLE_NAME  VARCHAR2(100);
    V_GRANTOR  VARCHAR2(100);
    V_PRIVILEGE  VARCHAR2(100);
    V_OBJECT_TYPE  VARCHAR2(100);    
    V_GRANT_REVOKE_GBN VARCHAR2(100);    
    
    V_MSG       long;    
    
BEGIN

    DBMS_OUTPUT.ENABLE;

    FOR V_ROW IN INVALID_OBJECT
    LOOP    
        -- 블록 에러 발생시에도 계속 실행토록 
        BEGIN 
            V_GRANTEE:= V_ROW.GRANTEE;
            V_OWNER:= V_ROW.OWNER;
            V_TABLE_NAME:= V_ROW.TABLE_NAME;
            V_GRANTOR:= V_ROW.GRANTOR;
            V_PRIVILEGE:= V_ROW.PRIVILEGE;
            V_OBJECT_TYPE:= V_ROW.OBJECT_TYPE;
            V_GRANT_REVOKE_GBN := 'GRANT TO USER';            
          
          -- 권한 추가 
          V_G_SQL :=  'GRANT '||V_ROW.PRIVILEGE||' ON '||V_ROW.OWNER||'.'||V_ROW.TABLE_NAME||' TO '||V_ROW.GRANTEE;      
          EXECUTE IMMEDIATE V_G_SQL;
          
          -- LOG 기록 
          INSERT INTO TB_MGR_GRANT_LOG 
                      (GRANTEE      , OWNER     , TABLE_NAME     , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN) 
               VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN);
          COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
               V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
              INSERT INTO TB_MGR_GRANT_LOG 
                          (GRANTEE  , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,OBJECT_TYPE  ,GRANT_REVOKE_GBN,GRANT_REVOKE_SQL)
                   VALUES (V_GRANTEE,V_OWNER,V_TABLE_NAME,V_GRANTOR,V_PRIVILEGE,V_OBJECT_TYPE,V_GRANT_REVOKE_GBN,V_MSG);
              COMMIT;          
        END;
        

    END LOOP;


EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;
/