Chapter 03. 절차형 SQL 활용하기

1. 절차형 SQL 종류

• 프로시저 (Procedure) : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리들의 집합
• 사용자 정의 함수 (User-Defined Function) : 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환 할 수 있음
• 트리거 (Trigger) : 데이터 베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행

2. DBMS_OUTPUT 패키지 종류 - 출력부

• DBMS_OUTPUT.PUT(문자열);  - 개행 없이 문자열을 출력하는 프로시저
• DBMS_OUTPUT.PUT_LINE(문자열); - 문자열을 출력 후 개행하는 프로시저

3. 조건문 - 제어부

• IF 문 : 조건이 참/거짓인지에 따라 경로를 선택
  • IF 조건 THEN 문장; ELSIF 조건 THEN 문장; … ELSE 문장; END IF;
• 간단한 케이스 문 : 명확한 값을 가지는 조건에 따라 여러개의 선택 경로 중 하나를 취하고자 할 때 사용
  • CASE 변수 WHEN 값1 THEN SET 명령어; WHEN 값2 THEN SET 명령어; … ELSE SET 명령어; END CASE;
• 검색된 케이스 문 : 명확한 값 및 범위를 가지는 조건에 따라 여러개의 선택 경로 중 하나를 취하고자 할 때 사용
  • CASE 변수 WHEN 조건 1 THEN SET 명령어; WHEN 조건2 THEN SET 명령어; … ELSE SET 명령어; END CASE;

4. 반복문 - 제어부

• LOOP 문 : 특정 조건이 만족될 때까지 반복해서 문장을 실행
  • LOOP 문장; EXIT WHEN 탈출 조건;  END LOOP;
• WHILE 문 : 시작과 종료 조건을 지정하여 참인 동안에는 해당 문장을 반복해서 실행
  • WHILE 반복 조건 LOOP 문장; EXIT WHEN 탈출 조건; END LOOP;
• FOR LOOP 문 : 시작 값과 끝 값을 지정하여 해당 값이 그 구간 내에 있을 때 반복
  • FOR 인덱스 IN 시작값 .. 종료값 LOOP 문장; END LOOP;

5. 예외부

• EXCEPTION WHEN 조건 THEN SET 명령어;

6. 프로시저 구성 (디비컨 SET)

• 선언부 (DECLARE) 
  • 프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의
• 시작/종료부 (BEGIN/END)
  • 프로시저의 시작과 종료 표현하며, BEGIN-END한쌍
  • 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
• 제어부 (CONTROL)
  • 기본적으로는 순차적으로 처리
  • 조건문과 반복문을 이용해서 문장 처리
• SQL
  • DML 주로 사용
  • 자주 사용되지는 않지만 DDL 중 TRUNCATE 사용
• 예외부 (EXCEPTION)
  • BEGIN~END절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부
• 실행부(TRANSACTION)
  • 프로시저에서 수행된 DML 수행 내역의 DBMS의 내용 또는 취소 여부를 결정하는 처리부

7. 프로시저 문법

• CREATE [OR REPLACE] PROCEDURE 프로시저명

      (파라미터_명 [IN | OUT | INPUT] 데이터_타입, …)

      IS

          변수 선언

      BEGIN

          명령어;

     [COMMIT | ROLLBACK]

     END;

 

8. 프로시저 호출문

• SQL> EXECUTE 프로시저_명 (파라미터1, 파라미터_2, …);

9. 사용자 정의 함수 구성 (디비컨 SER)

• 선언부 (DECLARE)
  • 사용자 정의함수의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의
• 시작/종료부 (BEGIN/END)
  • 사용자 정의함수의 시작과 종료를 표현하는데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성
  • 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스 구성
• 제어부 (CONTROL)
  • 기본적으로는 순차적으로 처리
  • 비교 조건에 따라 블록 또는 문장 실행
  • 조건에 따라 반복 실행
• SQL
  • 조회 용도로 SELECT문 사용
  • 데이터를 조작하는 INSERT, DELETE, UPDATE 사용 불가
• 예외부 (EXCEPTION)
  • BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외처리 방법을 정의하는 처리부
• 반환부 (RETURN)
  • 호출문에 대한 함숫값을 반환

10. 사용자 정의 함수 문법

• CREATE [OR REPLACE ] FUNCTION 함수명

     (파라미터_명 IN 데이터_타입, …)

     RETRUN 데이터_타입

     IS

        변수 선언

     BEGIN

        명령어;

        RETURN 변수;

     END;

 

11. 트리거의 목적

• 특정 테이블에 대한 데이터 변경을 시작점으로 설정하고, 그와 관련된 작업을 자동적으로 수행하기 위해 사용
• 일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는데 활용
• 데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 트리거 사용

12. 트리거의 종류

• 행 트리거 : 데이터 변화가 생길 때마다 실행
• 문장 트리거 : 트리거 에 이해 단 한번 실행

13. 트리거 구성 (다이비컨 SE)

• 선언부 (DECLARE)
  • 트리거의 명칭 정의
• 이벤트부(EVENT)
  • 트리거가 실행되는 타이밍, 이벤트를 명시
• 시작/종료부(BEGIN/END)
  • 트리거의 시작과 종료를 표현하는데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성
  • 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
• 제어부(CONTROL)
  • 기본적으로 순차적으로 처리
  • 비교 조건에 따라 블록 또는 문장을 실행
  • 조건에 따라 반복 실행
• SQL
  • DML을 주로 사용하고, 자주 사용되지 않지만 DDL(TRUNCATE 등) 사용
• 예외부 (EXCEPTION)
  • BEGIN~END 절에서 실행되는 SQL문이 실행 될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부

14. 트리거 문법

• CREATE [OR REPLACE] TRIGGER 트리거명

     [BEFORE | AFTER ] 유형 ON 테이블명

     [FOR EACH ROW]

     BEGIN

     END;

 

15. 트리거 작성 시 주의 사항

• TCL 사용 불가
  • COMMIT, ROLLBACK, 등의 트랜잭션 제어어 사용 시 컴파일 에러 발생
• 오류에 주의
  • 트리거 실행 중 오류가 발생하면 트리거 실행의 원인을 제공한 데이터 작업에도 영향
  • 특정 테이블에 데이터를 추가한 후 발생하는 트리거에서 오류가 발생할 경우에는 트리거 이후의 작업이 진행되지 않거나 데이터가 추가되지 않음