기본 프로시져
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;
프로시져 실행 옵션
- DBA권한이 있는데도 ORA-00942 에러 발생
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT)
AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.
- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
- EXECUTE IMEDIATE DDL명령 실행시 DDL명령에 선언된 OWNER(예를들어 SCOTT.XXX) 가 프로시져를 실행한 유저(예를 들어 DBADM)가 아닐때 ORA-00942에러 발생
- EXCUTE IMMEDIATE 'SQL구문'; 실행시 'SQL구문' 내부에 세미콜론은 ';' 없어야 수행됨. (중요-삽질의 시작..)
커서 활용 샘플
CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_OBJ_INVALID_TO_VALID
/*
*
*/
(
IN_DATE IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD')
)
AUTHID CURRENT_USER
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;
/