"프로시져 샘플"의 두 판 사이의 차이
DB CAFE
(→커서 활용 샘플) |
|||
(사용자 2명의 중간 판 8개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
+ | == 기본 프로시져 == | ||
<source lang=sql> | <source lang=sql> | ||
DECLARE | DECLARE | ||
32번째 줄: | 33번째 줄: | ||
DROP PROCEDURE lee2; | DROP PROCEDURE lee2; | ||
</source> | </source> | ||
+ | |||
+ | == 프로시져 실행 옵션 == | ||
+ | <source lang=sh> | ||
+ | AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT) | ||
+ | AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용. | ||
+ | </source> | ||
+ | |||
+ | * PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가 | ||
+ | |||
+ | == 커서 활용 샘플 == | ||
+ | <source lang=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; | ||
+ | / | ||
+ | </source> | ||
+ | [[Category:oracle]] |
2020년 1월 3일 (금) 01:13 기준 최신판
thumb_up 추천메뉴 바로가기
- 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 프로시져 실행 옵션[편집]
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT)
AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.
- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
3 커서 활용 샘플[편집]
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;
/