2022. 9. 4. 14:44ㆍ정처기(실기)/SQL 응용
(1) 절차형 SQL
- 일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.
(2) 절차형 SQL 종류
- 프로시저, 사용자 정의 함수, 트리거가 있다.
| 종류 | 설명 |
| 프로시저 | 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합 |
| 사용자 정의 함수 | 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL |
| 트리거 | 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL |
(3) 출력부
1. DBMS_OUTPUT 패키지 개념
- 메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지이다.
- DBMS_OUTPUT은 절차형 SQL이 정상적으로 구현되었는지 테스트하는 목적으로 많이 사용한다.
- DBMS_OUTPUT.PUT(문자열); -> 개행 없이 문자열을 출력하는 프로시저
- DBMS_OUTPUT.PUT_LINE(문자열); -> 문자열을 출력 후 개행하는 프로시저
(4) 제어부
1. 조건문
① IF 문
- 조건이 참인지 거짓인지에 따라 경로를 선택하는 조건문이다.
ex)
IF 조건 THEN
문장;
ELSIF 조건 THEN
문장;
......
ELSE
문장;
END IF;
② 간단한 케이스 문
- 명확한 값을 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 때 사용하는 조건문이다.
- 범위 같은 더 복잡한 매칭을 수행하려면, 검색된 CASE문을 사용해야 한다.
ex)
CASE 변수
WHEN 값1 THEN
SET 명령어;
WHEN 값2 THEN
SET 명령어;
...
ELSE
SET 명령어;
END CASE;
③ 검색된 케이스 문
- 검색된 케이스 문은 명확한 값 범위를 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 때 사용하는 조건문이다.
ex)
CASE
WHEN 조건1 THEN
SET 명령어;
WHEN 조건2 THEN
SET 명령어;
...
ELSE
SET 명령어;
END CASE;
2. 반복문
① LOOP문
- 특정 조건이 만족될 때까지 반복해서 문장을 실행하는 반복문이다.
- EXIT WHEN에 반복문 탈출 조건을 작성한다.
ex)
LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP;
② WHILE 문
- WHILE 문은 시작과 종료 조건을 지정하여 참인 동안에는 해당 문장을 반복해서 실행하는 명령문이다.
- WHILE 문은 조건이 참일 경우 반복하고, 조건이 거짓이거나 EXIT WHEN 조건이 만족하는 경우 반복문을 빠져나온다.
ex)
WHILE 반복 조건 LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP;
③ FOR LOOP 문
- 시작 값과 끝값을 지정하여 해당 값이 그 구간 내에 있을 때 반복하는 반복문이다.
ex)
FOR 인덱스 IN 시작값... 종료값
LOOP
문장;
END LOOP;
(5) 예외부
- 예외부는 실행 중 발생 가능한 예외상황을 수행하는 부분이다.
ex)
EXCEPTION
WHEN 조건 THEN
SET 명령어;
(6) 프로시저 개념
- 프로시저는 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.
(7) 프로시저 구성
| 구성요소 | 설명 |
| 선언부 (DECLARE) |
프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분 |
| 시작/종료부 (BEGIN/END) |
프로시저의 시작과 종료를 표현하며, BEGIN/END가 쌍을 이룸 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성 |
| 제어부 (CONTROL) |
기본적으로는 순차적으로 처리 조건문과 반복문을 이용하여 문장을 처리 |
| SQL | DML을 주로 사용 자주 사용되지 않지만 DDL 중 TRUNCATE 사용 |
| 예외부 (EXCEPTION) |
BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부 |
| 실행부 (TRANSACTION) |
프로시저에서 수행된 DML 수행 내영의 DBMS의 적용 또는 취소 여부를 결정하는 처리부 |
(8) 프로시저 문법
CREATE [OR REPLACE] PROCEDURE 프로시저_명
(파라미터_명 [IN | out | INOUT] 데이터_타입, ...)
IS
변수 선언
BEGIN
명령어;
[COMMIT | ROLLBACK]
END;- [OR REPLACE] : 기존 프로시저 존재 시에 현재 컴파일하는 내용으로 덮어씀
- 모드(IN | OUT | INOUT) : 변수의 입출력을 구분
- IN : 운영체제에서 프로시저로 값을 전달하는 모드
- OUT : 프로시저에서 처리된 결과를 운영체제로 전달하는 모드
- INOUT : IN과 OUT의 두 가지 기능을 동시에 수행하는 모드
- BEGIN : 프로시저의 시작을 알려주는 키워드
- COMMIT : 하나의 트랜잭션이 성공적으로 끝나고, 데이터베이스가 일관성 있는 상태에 있을 때 하나의 트랜잭션이 끝났을 때 사용하는 연산
- ROLLBACK : 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산
- END : 프로시저의 끝을 알려주는 키워드
(9) 프로시저 호출문 작성
- SQL TOOL을 활용하여 직접 실행시키는 경우에는 EXECUTE 또는 EXEC 명령어를 이용하여 프로시저를 실행한다.
- 프로시저에 입출력 변수가 존재하는 경우 변수를 입력하여 실행해야 한다.
- 데이터 유형의 경우 자동 변환이 되어 별도 오류가 발생하지 않는 경우가 많지만 가급적 프로시저에서 선언한 데이터 타입과 동일하게 입출력 변수를 넣어서 실행하는 것이 좋다.
(10) 사용자 정의 함수
- 사용자 정의 함수는 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL이다.
- 기본적인 사항은 프로시저와 동일하고 반환에서의 부분만 프로시저와 다르다.
- 사용자 정의 함수의 호출을 통해 실행되며, 반환되는 단일 값을 조회 또는 삽입, 수정 작업에 이용하는 것이 일반적이다.
- 기본적인 개념 및 사용법, 문법 등은 프로시저와 동일하며, 종료 시 단일 값을 반환한다는 것이 프로시저와의 가장 큰 차이점이다.
(11) 트리거
- 트리거는 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다.
- 이벤트는 전체 트랜잭션 대상과 각행에 의해 발생하는 경우 모두를 포함할 수 있으며 테이블과 뷰, DB 작업을 대상으로 정의할 수 있다.
1. 트리거의 목적
- 특정 테이블에 대한 데이터 변경을 시작점으로 설정하고, 그와 관련된 작업을 자동적으로 수행하기 위해 트리거를 사용한다.
- 일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는 데 활용한다.
- 데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 트리거를 사용한다.
2. 트리거의 종류
- 행 트리거 : 데이터 변화가 생길 때마다 실행
- 문장 트리거 : 트리거에 의해 단 한 번 실행
3. 트리거의 구성
- 프로시저나 사용자 정의 함수와 기본적 문법은 같다.
- 반환 값이 없다는 점, DML을 주된 목적으로 한다는 점에서는 프로시저와 유사하다.
- EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지한다는 점, 외부변수 IN/OUT이 없다는 점은 프로시저나 사용자 정의 함수와 다르다.
4. 트리거 작성 시 주의사항
- TCL 사용 불가 : 트리거 내에는 COMMIT, ROLLBACK 등의 트랜잭션 제어어(TCL) 사용 시 컴파일 에러 발생
- 오류에 주의 : 트리거 실행 중 오류가 발생하게 되면 트리거 실행의 원인을 제고안 데이터 작업에도 영향, 특정 테이블에 데이터를 추가한 후 발생하는 트리거에서 오류가 발생할 경우에는 트리거 이후의 작업이 진행되지 않거나 데이터가 추가되지 않음
'정처기(실기) > SQL 응용' 카테고리의 다른 글
| 집계성 SQL 작성 (0) | 2022.09.03 |
|---|---|
| 트랜잭션 (0) | 2022.08.25 |