Oracle, 저장 서브 프로그램

저장 서브 프로그램

 

PL/SQL 블록은 작성한 내용을 한 번 실행하는데 사용한다.

PL/SQL 블록은 이름이 정해져 있지 않아서 익명 블록이라고도 한다.

익명 블록은 오라클에 저장되지 않기 때문에 한 번 실행 한 후에 다시 실행하려면 PL/SQL 블록을 다시 작성하여 실행해야 한다.

 

저장 서브 프로그램은

 

여러 번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램을 말한다.

저장할 때 한 번 컴파일 한다.

오라클에 저장하여 공유할 수 있어서 메모리, 성능, 재사용성 등 의 장점이 있다.

대표적인 구현 방식은 프로시저, 함수, 패키지, 트리거 이다.

 

구현 방식
프로시저	파라미터 사용 X	작업 수행에 별다른 입력 데이터가 필요하지 않는 경우
	파라미터 사용 O	IN	필요한 값을 직접 입력 받는 형식의 파라미터를 지정할 때
		OUT	프로시저 실행 후 호출한 프로그램으로 값을 반환
		IN OUT	입력받을 때와 프로시저 수행 후 결과 값을 반환할 때( IN, OUT 동시에 수행)
함수	반환값의 자료형과 실행부에서 반환할 값을 RETURN절 및 RETURN문으로 명시
트리거	이벤트가 발생할 때 자동으로 실행 되는 기능을 정의 사용자의 기록을 확인할 수 있다. (LOG를 기록한다.) 트리거는 특정 이벤트가 발생할 때 자동으로 작동하는 서브 프로그램이므로 프로시저, 함수와 같이 EXECUTE 또는 PL/SQL 블록에서 따로 실행하지 못한다.

---

프로시저

 

원하는 부분을 저장하고 사용할 수 있다.

 

파라미터를 사용하지 않는 프로시저

 

[형식]

 

CREATE [OR REPLACE] PROCEDURE 프로시저명

IS | AS

     선언부

BEGIN

      실행부

EXCEPTION

      예외 처리부

END [프로시저 이름];

 

프로시저 실행

EXECUTE 프로시저 이름;

 

파라미터를 사용하는 프로시저

 

CREATE [OR REPLACE] PROCEDURE 프로시저명

(

     파라미터 이름 [MODES] 자료형 [ := | DEFAULT ],

     파라미터 이름 [MODES] 자료형 [ := | DEFAULT ],

)

IS | AS

     선언부

BEGIN

     실행부

EXCEPTION

     예외 처리부

END [프로시저 이름];

파라미터 모드	형식
IN 모드 파라미터	파라미터이름 IN 자료형
OUT 모드 파라미터	파라미터이름 OUT 자료형
IN OUT 모드 파라미터	파라미터이름 IN OUT 자료형

 

 

프로시저 생성

CREATE OR REPLACE PROCEDURE PRO_PARAM_OUT
(
	EMPNO IN EMPLOYEES.EMPLOYEE_ID%TYPE,
	EMPNAME OUT EMPLOYEES.LAST_NAME%TYPE,
	SAL OUT EMPLOYEES.SALARY%TYPE
)
IS
BEGIN
	SELECT LAST_NAME, SALARY INTO EMPNAME, SAL 
	FROM EMPLOYEES
	WHERE EMPLOYEE_ID = EMPNO;
END PRO_PARAM_OUT;

 

호출

-- PL/SQL
DECLARE
	V_EMPNAME EMPLOYEES.LAST_NAME%TYPE;
	V_SAL EMPLOYEES.SALARY%TYPE;
BEGIN
	PRO_PARAM_OUT(100, V_EMPNAME, V_SAL);
	DBMS_OUTPUT.PUT_LINE('V_EMPNAME : ' || V_EMPNAME);
	DBMS_OUTPUT.PUT_LINE('V_SAL : ' || V_SAL);
END;

 

PRO_PARAM_OUT(100, V_EMPNAME, V_SAL)
프로시저 호출

 

EMPNO IN EMPLOYEE_ID%TYPE
IN : 100을 받는다

 

SELECT LAST_NAME, SALARY INTO EMPNAME, SAL
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = EMPNO;
100에 해당되는 데이터를 꺼내와서 INTO EMPNAME, SAL에 저장해라

 

EMPNAME OUT EMPLOYEES.LAST_NAME%TYPE
SAL OUT EMPLOYEES.SALARY%TYPE
OUT :  저장된 값을 내보낸다.

---

함수

 

return절과 return문이 반드시 있어야 한다.

 

[형식]

CREATE [OR REPLACE] FUNCTION 함수명

(

     파라미터 이름 [IN] 자료형 [ := | DEFAULT ], -- 프로시저와 달리 IN모드만 지정한다.

     파라미터 이름 [IN] 자료형 [ := | DEFAULT ], -- ;이 아니라 ,로 지정 ...

)

RETURN 자료형

IS | AS

     선언부

BEGIN

     실행부

     RETURN (반환값);

EXCEPTION

     예외 처리부

END [함수 이름];

---

트리거

 

[형식]

 

CREATE [OR REPLACE] TRIGGER 트리거명

BEFORE | AFTER 

INSERT | UPDATE | DELETE ON 테이블명

REFERENCING OLD as old | New as new

FOR EACH ROW WHEN 조건식

FOLLOW 트리거 이름, 트리거 이름,...

ENABLE | DISABLE

 

DECLARE

     선언부

BEGIN

     실행부

EXCEPTION

     예외 처리

END;

BEFORE | AFTER	DML 명령어가 실행하기 전에 작동하는 트리거 생성 |  DML 명령어가 실행하기 후에 작동하는 트리거 생성
REFERENCING OLD as old | New as new	예전 REFERENCE를 사용할건지 새로운 REFENCE를 적용할건지 선택
FOR EACH ROW WHEN 조건식	행마다 적용

 

BEFORE

CREATE OR REPLACE TRIGGER TRI_EMP_WEEKEND
BEFORE
	INSERT OR UPDATE OR DELETE ON EMP_TAB
BEGIN
	IF TO_CHAR(SYSDATE, 'DY') IN ('토', '일') THEN
		IF INSERTING THEN
			RAISE_APPLICATION_ERROR(-20000, '주말 사원정보 추가 불가');
		ELSIF UPDATING THEN
			RAISE_APPLICATION_ERROR(-20001, '주말 사원정보 수정 불가');
		ELSIF DELETING THEN
			RAISE_APPLICATION_ERROR(-20002, '주말 사원정보 삭제 불가');
		ELSE
			RAISE_APPLICATION_ERROR(-20003, '주말 사원정보 변경 불가');
		END IF;
	END IF;
END;

 

 if to_char(sysdate, 'DY') in ('토', '일') then
IF문을 걸어서 주말에는 못하게 막는다.

 

if inserting then | elsif updating then | elsif deleting then
삽입, 수정, 삭제할 때

 

raise_application_error(-20000, '주말 사원 정보 추가 불가');
경고문구 발생시킨다

SQL 오류: ORA-20000: 주말 사원 정보 추가 불가
ORA-06512: "HR.TRI_EMP_WEEKEND",  6행
ORA-04088: 트리거 'HR.TRI_EMP_WEEKEND'의 수행시 오류

 

AFTER

주로 Log가 쌓이게 하기 위해 사용한다.

 

CREATE OR REPLACE TRIGGER TRI_EMP_LOG
AFTER
	INSERT OR UPDATE OR DELETE ON EMP_TAB 
FOR EACH ROW -- 행 별로 트리거 작동
BEGIN
	IF INSERTING THEN
		INSERT INTO EMP_TAB_LOG 
		VALUES ('EMP_TAB', 'INSERT',
			:NEW.EMPLOYEE_ID, -- 새로 추가 EMPLOYEE_ID
			SYS_CONTEXT('USERENV', 'SESSION_USER'), -- 현재 데이터베이스에 접속한 사용자
			SYSDATE);
	ELSIF UPDATING THEN
		INSERT INTO EMP_TAB_LOG 
		VALUES ('EMP_TAB',
			'UPDATE',
			:OLD.EMPLOYEE_ID, -- 변경 전 EMPLOYEE_ID
			SYS_CONTEXT('USERENV', 'SESSION_USER'),
			SYSDATE);
	END IF;
END

 

삽입, 수정을 할 때마다 로그가 생긴다.