[정보처리기사 실기] 7장 SQL 응용 - SQL 절차하기

절차형 SQL

개념

- 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

종류

• 프로시져 : 쿼리들을 마치 하나의 함수로 실행
• 사용자 정의 함수 : 일려의 쿼리 처리를 수행, 수행 결과를 단일 값으로 반환할 수 있는 절차형 쿼리
• 트리거 : 디비 시스템에서 삽삭갱 등 이벤트가 발생 할 때마다 관련 작업 자동으로 수행

출력부

(1) DBMS_OUTPUT 패키지 개념

- 메시지를 버퍼에 저장하고 버퍼로 부터 메시지 읽어오기 위한 인터페이스 패키지, 절차형 SQL이 정상적으로 구현되었는지 테스트에 목적

 

(2) 패키지 종류

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

제어부

(1) 조건문

• if, 간단한 케이스, 검색된 케이스

(2) 반복문

• LOOP, WHILE, FOR LOOP

예외부

- 실행 중 발생하는 예외 : EXCEPTION

 

프로시저

프로시저 개념

- 프로시저는 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리 집합

프로시저 구성

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

프로시저 호출

- SQL TOOL 활용해 직접 실행시키는 경우 EXECUTE or EXEC 입력

사용자 정의 함수

사용자 정의 함수 개념

- 일련의 SQL 처리 수행하고, 단일 값으로 변환할 수 있는 절차형 SQL

사용자 정의 함수 구성

- 프로시저와 동일하고 반환에서의 부분만 프로시저와 다르다, 호출을 통해 실행되며, 반환되는 단일 값을 조회 또는 삽입 수정 작업에 이용하는 것이 일반적, 개념과 문법은 동일하지만, 종료시 단일 값 반환하는 것이 큰 차이다.

• 선언부 DECLARE : 명칭, 변수와 인수 그리고 그에 대한 데이터 타입 정의
• 시작/종료부 BEGIN/END : 시작과 종료, 쌍을 이루며, 다수 실행 제어하는 기본적 단위가 되며 논리적 프로세스 구성
• 제어부 CONTROL : 기본적 순차적 처리, 비교 조건에 따라 블록이나 문장, 조건에 따하 반복
• SQL : SELECT만 사용가능, 데이터 조작 언어 못한다.
• 예외부 EXCEPTION : 시작과 종료 절에서 실행되는 쿼리 문이 실행될 때 예외 방생 시 예외 처리 방법 정의
• 반환부 RETURN : 호출문에 대한 함숫값 반환

트리거

개념

- 디비에서 삽삭갱 등 이벤트가 발생할 때 마다 관련 작업이 자동으로 수행되는 절차형 SQL, 테이블과 뷰, 디비 작업을 대상으로 정의

목적

- 데이터 변경을 시작점으로 설정하고, 관련된 작업 자동으로 수행, 데이터 무결성 유지 및 로그 메시지 출력의 별도 처리를 위해 트리거 사용

종류

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

구성

• 프로시저나 사용자 정의 함수와 기본적 문법 동일, 반환 값이 없다는 점, DML을 주된 목적으로 한다는 점에서 프로시저와 동일, EVENT 명령어 통해 이벤트를 인지한다는 점, 외부 변수 IN/OUT이 없다는 점은 프로시저와 사용자 함수와 다르다
• 선언부 DECLARE : 명칭, 변수와 인수 그리고 그에 대한 데이터 타입 정의
• 시작/종료부 BEGIN/END : 시작과 종료, 쌍을 이루며, 다수 실행 제어하는 기본적 단위가 되며 논리적 프로세스 구성
• 제어부 CONTROL : 기본적 순차적 처리, 비교 조건에 따라 블록이나 문장, 조건에 따하 반복
• SQL : DML 주로 사용, 자주 사용되지 않지만 DDL을 사용
• 예외부 EXCEPTION : 시작과 종료 절에서 실행되는 쿼리 문이 실행될 때 예외 방생 시 예외 처리 방법 정의

주의 사항

• TCL 사용 불가 : 트리거 내에는 커밋이나 롤백 등 제어어 사용시 컴파일 에러 발생
• 오류에 주의 : 오류가 발생하게 되면 실행의 원인을 제공한 데이터 작업에도 영향, 데이터를 추가한 후 오류가 발생할 경우 트리거 이후의 작업이 진행되지 않거나 데이터가 추가되지 않음