절차형 SQL
절차지향적인 프로그램이 가능하도록 하는 SQL.
→ 연속적인 실행, 분기, 반복 등의 제어 가능
오라클에서는 PL(Procedual Language)/SQL의 절차형 SQL을 제공한다.
1) PL/SQL
: 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어.
→ PL/SQL로 다양한 저장 모듈(프로시저, 사용자 정의 함수, 트리거) 개발 가능.
- PL/SQL 특징
1. Block 구조로 되어있어 각 기능별로 모듈화 가능.
2. 변수, 상수 등을 선언하여 SQL 문장 간 값 교환.
3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능.
4. 오라클에 내장되어 있음.
5. PL/SQL로 작성된 프로시저, 사용자 정의 함수는 작성자의 기준으로 트랜젝션 분할 가능.
- Stored Module(저장 모듈)
PL/SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램.
→ 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램.
→ Oracle의 저장 모듈에는 프로시저(Procedure), 사용자 정의 함수(User Defined Function), 트리거(Trigger)가 있다.
프로시저
호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행.
1) 프로시저 구성
- DECLARE [필수]
: 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의.
- BEGIN / END [필수]
: 프로시저의 시작과 종료.
- CONTROL
: 조건문 또는 반복문이 삽입됨. 순차적 처리.
- SQL
: 데이터 관리를 위한 조회, 추가, 수정, 삭제. (DML, DCL)
- EXCEPTION
: BEGIN / END 실행에서 예외 발생 시 처리하는 방법.
- TRANSACTION
: 수행된 데이터 작업들을 DB에 적용할지 취소할지 결정하는 처리부.
2) 프로시저 생성
CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
프로시저 BODY;
END;- [OR REPLACE]: 동일한 이름이 존재할 경우, 기존의 것을 대체한다.
- 파라미터
- IN: 호출 프로그램이 프로시저에게 값을 전달
- OUT: 프로시저가 호출 프로그램에 값을 반환
- INOUT: 호출 프로그램이 프로시저에게 값을 전달하고 그 값을 다시 반환
- 매개변수명
- 자료형
3) 프로시저 실행
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;4) 프로시저 삭제
DROP PROCEDURE 프로시저명;사용자 정의 함수
SQL에서 사용자가 임의로 만들어서 사용하는 함수.
→ 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고, 그 결과를 리턴하는 SQL의 보조적인 역할
트리거
DML이 실행되었을 때 자동으로 수행되는 절차형 SQL.
- TCL(COMMIT, ROLLBACK)을 사용할 수 없다.
1) 트리거 구성
- DECLARE [필수]
: 트리거의 명칭, 변수, 인수, 데이터 타입을 정의.
- EVENT [필수]
: 트리거가 실행되는 타이밍, 이벤트 명시
- BEGIN / END [필수]
: 트리거의 시작과 종료.
- CONTROL
: 조건문 또는 반복문이 삽입됨. 순차적 처리.
- SQL
: 데이터 관리를 위한 조회, 추가, 수정, 삭제. (DML) → DCL 사용할 수 없다.
- EXCEPTION
: BEGIN / END 실행에서 예외 발생 시 처리하는 방법.
2) 트리거 생성
CREATE [OR REPLACE] TRIGGER 트리거명 [실행시기][옵션] ON 테이블명
REFERENCING [NEW or OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
트리거 BODY;
END;- [OR REPLACE]: 동일한 이름이 존재할 경우, 기존의 것을 대체한다.
- [실행시기]
- BEFORE: 이벤트가 발생하기 전에 트리거 동작 실행
- AFTER: 이벤트가 발생한 후 트리거 동작 실행
- [옵션]
- INSERT
- DELETE
- UPDATE
- [NEW or OLD]
- NEW: 추가되거나 수정에 참여할 테이블
- OLD: 수정되거나 삭제 전 대상이 되는 테이블
- FOR EACH ROW: 각 튜플마다 트리거 적용
3) 트리거 삭제
DROP TRIGGER 트리거명;프로시저 VS 트리거
| 프로시저 | 트리거 |
| CREATE Procedure 문법 사용 | CREATE Trigger 문법 사용 |
| EXECUTE 명령어로 실행 | 생성 후 자동으로 실행 |
| COMMIT, ROLLBACK 사용 가능 | COMMIT, ROLLBACK 실행 안됨 |