저장 서브프로그램

저장 서브프로그램(stored subprogram)

1. 개념

• PL/SQL 블록은 작성한 내용을 한 번 실행하는 데 사용
• PL/SQL 블록은 이름이 정해져 있지 않아서 익명 블록이라고 한다
• 익명 블록은 오라클에 저장되지 않기 때문에 한 번 실행한 후에 다시 실행하려면 PL/SQL 블록을 다시 작성하여 실행해야 한다

 

2. 특징

• 여러 번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램
• 저장할 때 한 번 컴파일 한다
• 오라클에 저장하여 공유할 수 있어서 메모리, 성능, 재사용성 등 여러 면에서 장점이 있다
• 대표적인 구현 방식은 프로시저, 함수, 패키지, 트리거이다

 

---

 

프로시저

• 특정 처리 작업을 수행하는데 사용하는 저장 서브프로그램
• 파라미터를 사용할 수도 있고 사용하지 않을 수도 있다

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

• 작업 수행에 별다른 입력 데이터가 필요하지 않은 경우

 

1) 프로시저 생성

선언부, 실행부, 예외 처리부로 구성되어 있다

 

[형식]

CREATE [OR REPLACE] PROCEDURE 프로시저명
IS | AS -- DECLARE 키워드는 사용하지 않는다
	선언부
BEGIN
	실행부
EXCEPTION
	예외 처리부
END
	[프로시저 이름];

 

2) 프로시저 실행

[형식]

EXECUTE 프로시저 이름;

 

3) 프로시저 내용 확인

 

4) 프로시저 삭제

 

 

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

[형식]

CREATE [OR REPLACE] PROCEDURE 프로시저명 (
    파라미터 이름  [MODES] 자료형 [ := | DEFAULT ], -- 자리수 지정 및 NOT NULL 사용 불가능
    파라미터 이름  [MODES] 자료형 [ := | DEFAULT ], -- ; 이 아니라 , 로 지정
    ...
)
IS | AS
	선언부
BEGIN
	실행부 -- DECLARE 키워드는 사용하지 않는다
EXCEPTION
	예외 처리부
END
	[프로시저 이름];

 

1) IN 모드 파라미터

• 프로시저 실행에 필요한 값을 직접 입력받는 형식의 파라미터를 지정할 때 사용

2) OUT 모드 파라미터

• 프로시저 실행 후 호출한 프로그램으로 값을 반환

3) IN OUT 모드 파라미터

• IN, OUT 으로 선언한 파라미터 기능을 동시에 수행
• 입력받을 때와 프로시저 수행 후 결과 값을 반환할 때 사용

 

 

3. 프로시저 예제

 

---

 

함수

1. 개념

• 오라클 함수는 내장함수와 사용자 정의 함수로 나눌 수 있다
• 함수는 반환 값의 자료형과 실행부에서 반환할 값을 RETURN절 및 RETURN문으로 명시해야 한다

 

 

2. 함수 생성

[형식]

CREATE [OR REPLACE] FUNCTION 함수명
(
	파라미터 이름  [IN] 자료형 [ := | DEFAULT ], -- 프로시저와 달리 IN모드만 지정한다
	파라미터 이름  [IN] 자료형 [ := | DEFAULT ], -- ; 이 아니라 , 로 지정
	...
)
RETURN 자료형
IS | AS
	선언부
BEGIN
	실행부
	RETURN (반환값);
EXCEPTION
	예외 처리부
END [함수 이름];

 

 

3. 함수 예제

 

---

 

트리거 (Trigger)

1. 개념

• 데이터베이스 안의 특정 상황이나 동작, 즉 이벤트가 발생하면 자동으로 실행되는 기능을 정의하는 PL/SQL 서브프로그램
• 테이블의 데이터를 특정 사용자가 변경하려 할 때 해당 데이터나 사용자 기록을 확인한다든지 상황에 따라 데이터를 변경하지 못하게 막는 것이 가능
• 데이터베이스가 가동되거나 종료될 때 데이터베이스 관리자에게 메일을 보내는 기능도 구현 가능
• 트리거는 특정 이벤트가 발생할 때 자동으로 작동하는 서브프로그램 >> 프로시저, 함수와 같이 EXECUTE 또는 PL/SQL 블록에서 따로 실행 불가

 

 

2. DML 트리거

[형식]

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;

 

1) BEFORE

DML 명령어가 실행하기 전에 작동하는 트리거가 생성

 

 

2) AFTER

DML 명령어가 실행된 후에 작동하는 트리거가 생성

EMP_TAB에 DML 명령어가 실행되면 EMP_TAB_LOG에 행이 추가

 

 

3. 트리거 예제