[정보처리기사 실기] Ⅷ. SQL 응용 - 절차형 SQL 작성

Ⅷ. SQL 응용 - 절차형 SQL 작성.

1. 프로시저

1.1. 프로시저(Procedure) 개념

절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어.

프로시저 호출을 통해 실행되며, 일련의 SQL 작업을 포함하는 데이터 조작어를 수행.
✱ 데이터 조작어(DML; Data Manipulate Language) : DB에 저장된 자료를 입력, 수정, 삭제, 조회 하는 언어 (SELECT, INSERT, UPDATE, DELETE).

1.2. 프로시저 구성 ( Tip. 디비컨 SET )
[DECLARE]                               • 선언: 변수 및 상수, 타입 선언 
[BEGIN]                                 • 시작/종료(BEGIN/END) : 실행 및 서브루틴 생성
    [CONTROL - 조건문 - 반복문]            • 제어 단위 블록별 실행흐름을 제어
    [SQL - DQL - DML]                   • SQL : 데이터 관리를 위한 조회, 추가, 수정, 삭제
    [EXCEPTION]                         • 예외 : 실행 중 발생 가능한 예외 상황 수행
    [TRANSACTION - COMMIT - ROLLBACK]   •  실행 : 해당 기점만큰 DBMS에 반영 또는 복구
[END]
선언부(DECLARE) : 프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의.

시작/종료부(BEGIN/END) : 프로시저의 시작과 종료를 표현하며, BEGIN/END가 쌍을 이룸 / 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성.

제어부(CONTROL) : 순차적으로 처리 / 조건문과 반복문을 이용하여 문장을 처리.

SQL : DML을 주로 사용 / 자주 사용되지 않지만 DDL중 TRUNCATE 사용.
✱ TRUNCATE : 데이터가 하나도 없이 테이블 구조만 남은 최초 테이블이 만들어진 상태로 돌아가도록 하는 명령.

예외부(EXCEPTION) : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부.

실행부(TRANSACTION) : 프로시저에서 수행된 DML 수행 내역의 DBMS 적용 또는 취소 여부를 결정하는 처리부.

1.3. 프로시저 구성 상세

1.3.1. 선언부(DECLARE)
CREATE [OR REPLACE] PROCEDURE 프로시저명
( 파라미터명 [MODE] 데이터타입 ... )
IS 
변수 선언
CREATE : DBMS 내에 객체를 생성

[OR REPLACE] : OR REPLACE 명령은 기존 프로시저 존재시 현재 컴파일 내용으로 덮어씀

PROCEDURE : 프로시저를 사용한다는 의미

프로시저명 : 해당프로시저명

파라미터명 : 프로시저와 운영체제간 필요한 값을 전송하기위한 인자

[MODE] : IN(값전달) / OUT(값출력) / INOUT(IN/OUT 동시수행) 으로 구성

데이터타입 : CHAR / VARCHAR / NUMBER

IS [AS] : PL/SQL 블록을 시작, IS 또는 AS 키워드 작성(AS:Standalone객체에.. IS:Embedded객체에.. )

변수의 선언 : 프로시저내에서 사용할 변수와 변수에 대한 초깃값을 설정

1.3.2. 시작/종료부 (BEGIN/END)

BEGIN : 프로시저의 시작을 알려주는 명령

END : 프로시저의 끝을 알려주는 명령

1.3.3. 제어부 (CONTROL)
실행흐름을 제어하는 부분 / 크게 조건문과 반복문으로 나눔.
조건문 - IF문
IF 조건 THEN
 문장;
ELSEIF 조건 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;
반복문 - LOOP문
LOOP
 문장;
 EXIT WHEN 탈출조건;
END LOOP;
반복문 - WHILE문
WHILE 반복조건 LOOP
 문장;
 EXIT WHEN 탈출조건;
END LOOP;
반복문 - FOR LOOP문
FOR 인덱스 IN 시작값 .. 종료값 
 LOOP
 문장;
END LOOP;
1.3.4. 프로시저 SQL

데이터 관리를 위한 조회, 추가, 수정, 삭제를 수행하는 부분.

SQL 유형 ( Tip. 세인업데 )

SELECT / INSERT / UPDATE / DELETE

1.3.5. 예외부(EXCEPTION)
실행 중 발생 가능한 예외상황을 수행하는 부분.
EXCEPTION
 WHEN 조건 THEN
    SET  명령어;
1.3.6. 실행부(TRANSACTION)

수행한 DML을 DBMS 에 반영할지 복구할지 결정하는 부분.

COMMIT : 하나의 트랜잭션이 성공적으로 끝나고, 데이터베이스가 일관성 있는 상태에 있을때 하나의 트랜잭션이 끝났을때 사용하는 연산.

ROLLBACK : 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산.

1.4. 프로시저 호출문 작성
응용프로그램에서 호출하거나 내부 스케줄러에 의해 배치 작업을 수행하는 경우 사용.
SQL 툴을 활용할 경우 EXECUTE 또는 EXEC 명령어 이용하여 프로시저 실행.
SQL> EXECUTE 프로시저명(파라미터1, 파라미터2, ...);
-- 예문
EXECUTE SALE_CLOSING('20220315'); -- SAL_CLOSING 프로시저 파라미터1에 20220315에 값 전달하여 호출

2. 사용자 정의함수

2.1. 사용자 정의함수(User-Defined Function) 개념

절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환 할 수 있는 절차형 SQL.

2.2. 사용자 정의함수 구성 ( Tip. 디비컨 SER )
기본적으로 프로시저와 동일 / 종료시 단일 값을 반환하는 부분만 다름.
[DECLARE]                               • 선언: 변수 및 상수, 타입 선언 
[BEGIN]                                 • 시작 : 사용자 함수 실행시 기점
  [CONTROL - 조건문 - 반복문]            • 제어 단위 블록별 실행흐름을 제어
  [SQL - DQL - DML]                   • SQL : 데이터 관리를 위한 조회, 추가, 수정, 삭제
  [EXCEPTION]                         • 예외 : 실행 중 발생 가능한 예외 상황 수행
  [RETURN]                            • 반환 : 호출문에 함수값 적용
[END]                                   • 종료 : 사용자 함수 실행 시 종점 
선언부(DECLARE) : 사용자 정의함수의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의.

시작/종료부(BEGIN/END) : 사용자정의함수의 시작과 종료를 표현하며, BEGIN/END가 쌍을 이룸 / 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성.

제어부(CONTROL) : 순차적으로 처리 / 비교 조건에 따라 블록 또는 문장 실행 / 조건에 따라 반복 실행.

SQL : 조회 용도로 SELECT 문을 사용 / INSERT, DELETE, UPDATE 는 사용 불가. ✱ TRUNCATE : 데이터가 하나도 없이 테이블 구조만 남은 최초 테이블이 만들어진 상태로 돌아가도록 하는 명령.

예외부(EXCEPTION) : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부.

반환부(RETURN) : 호출문에 대한 함수 값을 반환.
2.3. 사용자 정의함수 구문 상세

2.3.1. 선언부(DECLARE)
CREATE [OR REPLACE] FUNCTION 함수명
( 파라미터명 [MODE] 데이터타입 ... )
RETURN 데이터_타입
IS 
변수 선언
CREATE : DBMS 내에 객체를 생성

[OR REPLACE] : OR REPLACE 명령은 기존 프로시저 존재시 현재 컴파일 내용으로 덮어씀

FUNCTION : 사용자정의함수를 사용한다는 의미

함수명 : 함수명

RETURN 데이터 타입 : 함수가 반환할 데이터 타입을 지정

파라미터명 : 사용자정의함수와 운영체제간 필요한 값을 전송하기 위한 인자

[MODE] : IN(값전달) / OUT(값출력) / INOUT(IN/OUT 동시수행) 으로 구성

데이터타입 : CHAR / VARCHAR / NUMBER 등..

IS [AS] : PL/SQL 블록을 시작, IS 또는 AS 키워드 작성(AS:Standalone객체에.. IS:Embedded객체에.. )

변수의 선언 : 함수에서 사용할 변수와 변수에 대한 초깃값을 설정

2.3.2. 시작/종료부 (BEGIN/END)

BEGIN : 함수의 시작을 알려주는 명령

END : 함수의 끝을 알려주는 명령

2.3.3. 제어부 (CONTROL)

실행흐름을 제어하는 부분 / 크게 조건문과 반복문으로 나눔.

2.3.4. SQL

INSERT, DELETE, UPDATE 데이터 조작 불가.

SELECT 를 통해 조회만 가능.

2.3.5. 예외부(EXCEPTION)

실행 중 발생 가능한 예외상황을 수행하는 부분.

2.3.6. 반환부(RETURN)

RETURN 명령을 통해 사용자 정의함수 종료시 반환하는 단일값을 정의.

2.4. 사용자 정의함수 호출문 작성
프로시저와 동일하게 외부에서의 호출을 통해 실행.
SQL> 함수명(파라미터1, 파라미터2, ...);
-- 예문1
SELECT GET_AGE('20220315') FROM DUAL; -- GET_AGE 함수 파라미터1에 20220315에 값 전달하여 호출
-- 예문2
UPDATE USER_INFO
SET AGE = GET_AGE(BIRTH_DATE) 
WHERE ID =  'USER_ID';

3. 트리거

3.1. 트리거(Trigger) 개념

특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트 발생시 DBMS에서 자동으로 실행되도록 구현된 프로그램.

3.2. 트리거(Trigger) 목적

프로시저나 사용자정의함수와 기본 문법 동일.

반환값 없음.

주된 목적이 DML이라는 점에서 프로시저와 유사.

차이점 : EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지, 외부 IN/OUT 이 없음.

3.3. 트리거의 종류

행 트리거 : 데이터 변화가 생길 때마다 실행.

문장 트리거 : 트리거에 의해 단 한 번 실행.

3.4. 트리거의 구성 ( Tip. 디이비컨 SE )

선언부(DECLARE) : 트리거의 명칭을 정의.

이벤트부(EVENT) : 트리거가 실행되는 타이밍, 이벤트를 명시.

시작/종료부(BEGIN/END) : 트리거의 시작과 종료를 표현하며, BEGIN/END가 쌍을 이룸 / 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성.

제어부(CONTROL) : 순차적으로 처리 / 비교 조건에 따라 블록 또는 문장을 실행 / 조건에 따라 반복.

SQL : DML을 주로 사용 / 자주 사용되지 않지만 DDL중 TRUNCATE등 사용.
✱ TRUNCATE : 데이터가 하나도 없이 테이블 구조만 남은 최초 테이블이 만들어진 상태로 돌아가도록 하는 명령.

예외부(EXCEPTION) : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부.

3.5. 트리거의 구문

3.5.1. 선언부(DECLARE)
CREATE [OR REPLACE] TRIGGER 트리거명
CREATE : DBMS 내에 객체를 생성

[OR REPLACE] : OR REPLACE 명령은 기존 트리거 존재시 현재 컴파일 내용으로 덮어씀

TRIGGER : 트리거를 사용한다는 의미

트리거명 : 해당트리거명

3.5.2. 이벤트부(EVENT)
순서 유형 ON 테이블명
[FOR EACH ROW]
대상 테이블에 벌어진 이벤트 유형(INSERT, UPDATE, DELETE) 및 이벤트 순서(BEFORE, AFTER)에 맞게 트리거 수행을 위한 조건 입력.

매번 변경되는 데이터행의 수만큼 실행을 위한 명령(FOR EACH ROW)를 정의하기도 한다.

트리거 이벤트 순서

BEFORE : 이벤트부의 테이블에 대한 INSERT/UPDATE/DELETE 를 수행하기 전에 트리거가 실행되도록 지정하는 명령

AFTER : 이벤트부의 테이블에 대한 INSERT/UPDATE/DELETE 가 성공적으로 실행 되었을때 트리거가 실행하도록 하는 명령

트리거 이벤트 유형

INSERT, UPDATE, DELETE 중 트리거를 수행할 명령어 유형을 선택

INSERT, UPDATE, DELETE 는 여러개 중복 선택 가능 (ex. INSERT OR DELETE)

3.5.3. 시작/종료부 (BEGIN/END)

트리거의 실행 시작과 종료를 알려주는 부분으로 필수.

3.5.4. 제어부 (CONTROL)

단위블록별 실행흐름을 제어하는 부분으로 크게 IF문과 CASE 문으로 나뉜다.

3.5.5. SQL

데이터 관리를 위한 조회, 추가, 수정, 삭제를 수행.

SELECT, INSERT, DELETE, UPDATE 문장을 주로 사용

행 트리거 안에서 OLD 및 NEW 수식자 접두어를 붙여 데이터 변경 전후 열의 값을 참조.

트리거 SQL 접두어

INSERT : OLD = NULL / NEW = 삽입된 값

UPDATE : OLD = 갱신 전의 값 / NEW = 갱신 후의 값

DELETE : OLD = 삭제 전의 값 / NEW = NULL

ex : OLD.USER_ID / NEW.USER_ID …

3.5.6. 예외부(EXCEPTION)

반드시 포함될 필요는 없음.

실행 중 발생 가능한 예외상황을 수행하는 부분.

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

TCL 사용 불가 : 트리거 내에는 COMMIT, ROLLBACK 등의 트랜잭션 제어어(TCL) 사용시 컴파일 에러 발생.

오류에 주의

트리거 실행중 오류 발생시 실행 원인의 데이터작업에도 영향 발생.

특정 테이블에 데이터를 추가 후 트리거 오류 발생시 트리거 이후 작업이 진행되지 않거나 데이터가 추가되지 않음.

정보처리기사 필기 합격 후 실기대비 정리 및 책없이 간단히 보기위해 작성하였습니다.
2020년 수제비 정보처리기사 책 기반으로 정리 하였습니다.

[정보처리기사 실기] Ⅷ. SQL 응용 - 절차형 SQL 작성. (feat.수제비)

이전[정보처리기사 실기] Ⅶ. 기사실기 요약 / 정리 / 단답 / Tip / 알고가기!

다음[정보처리기사 실기] Ⅷ. SQL 응용 - 응용 SQL 작성

Ⅷ. SQL 응용 - 절차형 SQL 작성.

1. 프로시저

1.1. 프로시저(Procedure) 개념

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

1.3. 프로시저 구성 상세

1.3.1. 선언부(DECLARE)

1.3.2. 시작/종료부 (BEGIN/END)

1.3.3. 제어부 (CONTROL)

조건문 - IF문

조건문 - 간단한 케이스문

조건문 - 검색된 케이스문

반복문 - LOOP문

반복문 - WHILE문

반복문 - FOR LOOP문

1.3.4. 프로시저 SQL

1.3.5. 예외부(EXCEPTION)

1.3.6. 실행부(TRANSACTION)

1.4. 프로시저 호출문 작성

2. 사용자 정의함수

2.1. 사용자 정의함수(User-Defined Function) 개념

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

2.3. 사용자 정의함수 구문 상세

2.3.1. 선언부(DECLARE)

2.3.2. 시작/종료부 (BEGIN/END)

2.3.3. 제어부 (CONTROL)

2.3.4. SQL

2.3.5. 예외부(EXCEPTION)

2.3.6. 반환부(RETURN)

2.4. 사용자 정의함수 호출문 작성

3. 트리거

3.1. 트리거(Trigger) 개념

3.2. 트리거(Trigger) 목적

3.3. 트리거의 종류

3.4. 트리거의 구성 ( Tip. 디이비컨 SE )

3.5. 트리거의 구문

3.5.1. 선언부(DECLARE)

3.5.2. 이벤트부(EVENT)

3.5.3. 시작/종료부 (BEGIN/END)

3.5.4. 제어부 (CONTROL)

3.5.5. SQL

3.5.6. 예외부(EXCEPTION)

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