3. 데이터베이스 구축 - SQL 활용(프로시저, 트리거, 사용자 정의 함수)

도서 개발 공부/정보 처리 기사 필기

3. 데이터베이스 구축 - SQL 활용(프로시저, 트리거, 사용자 정의 함수)

캐티시 2022. 4. 9. 16:07

113. 프로시저 (Procedure)

: 절차형 SQL을 활용해 특정 기능을 수행하는 일정의 트랜잭션 언어. 호출로 실행되어 미리 정의한 SQL 작업을 수행한다.

※ 절차형 SQL의 상세 정보

41. 절차형 SQL

2. 소프트웨어 개발 - 데이터 입출력 구현(데이터베이스 개요 ~ 절차형 SQL)

39. 데이터 저장소 : 소프트웨어 개발 과정에서 필요한 데이터를 논리적 구조로 조직화하거나 물리적 공간에 구축한 것 논리 데이터 저장소 : 데이터, 데이터 간 연관성, 제약 조건 등을 식별하여

catisstudying.tistory.com

스토어드 프로시저 (Stored Procedure) : 생성된 프로시저는 데이터베이스 내에 저장

=> 여러 프로그램에서 호출하여 사용이 가능

시스템의 일일 마감 작업, 일괄 작업 등에 주로 이용

1) 프로시저 구성도

DECLARE : 프로시저 명칭, 변수, 파라미터, 데이터 타입을 정의하는 선언부
BEGIN : 프로시저의 시작을 알리는 키워드
- CONTROL : 조건문/반복문의 분기
- SQL : DML이나 DCL이 삽입되어 데이터 관리를 위한 작업을 정의
- EXCEPTION : BEGIN~END 블록 내에서 예외 발생 시 이를 처리하는 방법을 정의
- TRANSACTION : 수행된 작업을 DB에 적용(COMMIT)할지 취소(ROLLBACK)할지 결정
END : 프로시저의 종료를 알리는 키워드

2) 프로시저의 이용

1. CREATE PROCEDURE : 프로시저를 정의하여 생성

- 표기 형식

CREATE [OR REPLACE] PROCEDURE 프로시저_이름([[파라미터_모드] [파라미터_이름] 데이터 타입 [, ...]])
IS
[지역변수 선언]
BEGIN
	프로시저 BODY;
END;

CREATE PROCEDURE : 파라미터를 지정하여 프로시저를 정의
- OR REPLACE : 동일한 이름의 프로시저가 있는 경우 해당 프로시저를 이 프로시저가 대체함
파라미터 : 호출 프로그램에서 값을 받는, 또는 호출 프로그램에 값을 반환하는 인수
- 파라미터 모드 (생략하면 기본값은 IN)
  - IN : 호출 프로그램으로부터 값을 받아서 저장하는 파라미터
  - OUT : 호출 프로그램에 결과 값을 반환하는 파라미터
  - INOUT : 호출 프로그램으로부터 값을 받아 프로시저를 처리하고 결과 값을 호출 프로그램에 반환하는 파라미터
- 파라미터 이름
- 데이터 타입
프로시저 BODY : 프로시저의 코드를 기록하는 부분. BEGIN~END 블록 내에 위치하고 반드시 하나 이상의 SQL문 포함해야 함

- 예시

CREATE OR REPLACE PROCEDURE CHANGE_GRADE_TO_A(IN i_STUDENT_NO INT) --- #1
IS --- #2
BEGIN
	UPDATE STUDENT_LIST SET GRADE='A' WHERE STUDENT_NO = i_STUDENT_NO;
    	EXCEPTION --- #3
    		WHEN PROGRAM_ERROR THEN --- #4
        		ROLLBACK;
    	COMMIT;
END;

호출 시 파라미터로 학번(i_STUDENT_NO)를 받아 해당 학생의 성적을 'A'로 수정하는 프로시저
#1 : CHANGE_GRADE_TO_A라는 이름의 프로시저가 이미 존재한다면 지금 정의하는 프로시저로 대체함
#2 : 변수를 선언하는 예약어 IS
#3 : 예외 정의
#4 : PROGRAM_ERROR 발생 시 프로시저를 취소

3. ALTER PROCEDURE : 프로시저의 정의 변경

- 표기 형식

ALTER PROCEDURE 프로시저_이름[([파라미터_모드] [파라미터 이름] 데이터 타입[, ...])]
	[RENAME TO 새_프로시저_이름];
    	[OWNER TO 사용자_명];
        [SET SCHEMA 스키마_이름];
        ...

RENAME TO : 프로시저의 이름 변경
OWNER TO : 프로시저의 소유자 변경
SET SCHEMA : 프로시저가 실행될 스키마 변경

- 예시

ALTER PROCEDURE CHANGE_GRADE_TO_A(INT) RENAME TO SET_GRADE_A;

CHANGE_GRADE_TO_A 프로시저의 이름을 SET_GRADE_A로 변경

2. EXECUTE PROCEDURE : 프로시저 실행

-표기 형식

EXECUTE 프로시저_이름;
EXEC 프로시저_이름;
CALL 프로시저_이름;

- 예시

EXECUTE SET_GRADE_A(0010032);

파라미터를 0010032로 하여 SET_GRADE_A 프로시저 실행

3. DROP PROCEDURE : 프로시저 제거

- 표기 형식

DROP PROCEDURE [IF EXISTS] 프로시저_이름[([파라미터 모드] [파라미터 이름] 데이터 타입 [, ...])] [, ...]
	[CASCADE | RESTRICT];

하나 이상의 프로시저를 제거
IF EXISTS : 제거 하려는 프로시저의 이름의 없더라도 오류 생성을 하지 않음

- 예시

DROP PROCEDURE SET_GRADE_A(INT);

INT 데이터 타입 파라미터를 하나 받는 SET_GRADE_A 프로시저 제거

114. 트리거 (Trigger)

: 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

데이터베이스에 저장되며 데이터 변경, 무결성 유지, 로그 메시지 출력 등의 목적으로 이용
프로시저와 달리 BODY 내에 DCL을 사용할 수 없음
- DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

1) 트리거 구성도

DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
EVENT : 트리거가 실행되는 조건을 명시
BEGIN : 프로시저의 시작을 알리는 키워드
- CONTROL : 조건문/반복문의 분기
- SQL : DML 문장이 삽입되어 데이터 관리를 위한 작업을 정의
- EXCEPTION : BEGIN~END 블록 내에서 예외 발생 시 이를 처리하는 방법을 정의
END : 프로시저의 종료를 알리는 키워드

2) 트리거의 이용

1. CREATE TRIGGER : 트리거를 정의하여 생성

- 표기 형식

CREATE [OR REPLACE] TRIGGER 트리거_이름 동작시기 동작 ON 테이블_이름
[REFERENCING {NEW | OLD} AS 테이블_이름]
[FOR EACH {ROW | STATEMENT} [WHEN (조건식)]]
BEGIN
	트리거 BODY;
END;

CREATE TRIGGER : 트리거의 이름과 실행조건이 될 동작의 시기, 동작 내용, 테이블 이름을 명시하여 정의
- OR REPLACE : 동일한 이름의 트리거가 있는 경우 해당 트리거를 이 트리거가 대체함
- 동작 시기 : 트리거가 실행될 시기를 지정
  - AFTER : 실행 조건이 되는 동작 후 트리거 실행
  - BEFORE : 실행 조건이 되는 동작 전 트리거 실행
  - INSTEAD OF : 실행 조건이 되는 동작 대신 트리거 실행
- 동작 : 트리거의 실행 조건이 될 작업의 종류 지정 INSERT/DELETE/UPDATE
REFERENCING : 트리거가 적용될 테이블의 별칭 지정
- NEW : 추가되거나 수정에 참여할 튜플들의 집합
- OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합
FOR EACH {ROW | STATEMENT} : 각 튜플마다/문장마다 트리거가 적용됨
- WHEN : 트리거를 적용할 튜플의 조건을 지정
트리거 BODY : 트리거의 코드를 기록하는 부분. BEGIN~END 블록 내에 위치하고 반드시 하나 이상의 SQL문 포함해야 함

- 예시

CREATE OR REPLACE TRIGGER SET_DEPT_TRI INSERT ON STUDENT --- #1
REFERENCING NEW AS NEW_STUDENT --- #2
FOR EACH ROW
WHEN (NEW_STUDENT.DEPT IS NULL)
BEGIN
	:NEW_TABLE.DEPT := 'INTERDISCIPLINARY'; --- #3
END;

학생 테이블(STUDENT)에 새로운 튜플이 삽입 될 때 각 튜플마다 학과 정보가 누락되었으면 'INTERDISCIPLINARY'를 학과 데이터로 입력하는 트리거
#1 : SET_DEPT_TRI 라는 이름의 트리거가 이미 존재한다면 지금 정의하는 트리거로 대체함
#2 : 새로 입력되는 학생 튜플 집합을 NEW_STUDENT로 명명
#3 : A := B : A에 B를 치환함

2. ALTER TRIGGER : 트리거 정의 변경

- 표기 형식

ALTER TRIGGER 트리거_이름 ON 테이블_이름 RENAME TO 새_트리거_이름;

RENAME TO : 트리거의 이름 변경

- 예시

ALTER TRIGGER SET_DEPT_TRI ON STUDENT RENAME TO CHANGE_DEPT_TRI;

학생 테이블(STUDENT)에 대한 트리거 SET_DEPT_TRI의 이름을 CHANGE_DEPT_TRI로 변경

3. DROP TRIGGER : 트리거 제거

- 표기 형식

DROP TRIGGER [IF EXISTS] 트리거_이름 ON 테이블_이름 [CASCADE | RESTRICT];

트리거 하나를 제거
IF EXISTS : 제거 하려는 트리거의 이름의 없더라도 오류 생성을 하지 않음

- 예시

DROP TRIGGER CHANGE_DEPT_TRI ON STUDENT;

학생 테이블(STUDENT)에 대한 트리거 SET_DEPT_TRI를 제거

115. 사용자 정의 함수

: 일련의 작업을 연속적으로 처리하고, 종료 시 처리 결과를 단일 값으로 반환하는 절차형 SQL

데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등의 DML문 내에서 호출로 실행됨
RETURN : 처리 결과 값을 반환하는 예약어
사용자 정의 함수는 SELECT를 통한 테이블 조회만 가능
- INSERT, DELETE, UPDATE를 통한 데이터 조작은 불가능
사용자 정의 함수에서 프로시저를 호출할 수 없음
SUM(), AVG() 등의 내장 함수 처럼 DML 문에서 반환 값을 활용하기 위해 사용

※ 프로시저와 사용자 정의 함수의 비교

1) 사용자 정의 함수 구성도

DECLARE : 사용자 정의 함수의 명칭, 파라미터, 변수, 데이터 타입을 정의하는 선언부
BEGIN : 프로시저의 시작을 알리는 키워드
- CONTROL : 조건문/반복문의 분기
- SQL : DML 문장이 삽입되어 데이터 관리를 위한 작업을 정의
- EXCEPTION : BEGIN~END 블록 내에서 예외 발생 시 이를 처리하는 방법을 정의
- RETURN : 호출 프로그램에 반환할 값이나 변수를 정의
END : 프로시저의 종료를 알리는 키워드

2) 사용자 정의 함수의 이용

1. CREATE FUNCTION : 사용자 정의 함수를 생성

CREATE [OR REPLACE] FUNCTION 함수_이름([[파라미터_모드] [파라미터_이름] 데이터_타입 [, ...]])
[RETURN 데이터_타입]
IS
[지역 변수 선언]
BEGIN
	사용자 정의 함수 BODY;
END;

CREATE FUNCTION : 파라미터를 지정하여 함수를 정의
- OR REPLACE : 동일한 이름의 함수가 있는 경우 해당 함수를 이 함수가 대체함
파라미터 : 호출 프로그램에서 값을 받는, 또는 호출 프로그램에 값을 반환하는 인수
- 파라미터 모드 (생략하면 기본값은 IN)
  - IN : 호출 프로그램으로부터 값을 받아서 저장하는 파라미터
  - OUT : 호출 프로그램에 결과 값을 반환하는 파라미터
  - INOUT : 호출 프로그램으로부터 값을 받아 작업을 처리하고 결과 값을 반환하는 파라미터
- 파라미터 이름
- 데이터 타입
RETURN 데이터_타입 : 반환할 값의 데이터 타입 명시
사용자 정의 BODY : 함수의 코드를 기록하는 부분. BEGIN~END 블록 내에 위치하고 반드시 하나 이상의 SQL문 포함해야 함
- RETURN : 반환할 값이나 반환할 값이 저장된 변수를 지정

- 예시

CREATE OR REPLACE FUNCTION GET_YEAR_INFO(IN i_YEAR INT) --- #1
RETURN VARCHAR2 --- #2
IS
BEGIN
	IF i_YEAR = 1 THEN
    		RETURN '신입생';
    	ELSE
        	RETURN '재학생';
        END IF;
END;

학년을 파라미터로 받아 1학년이면 '신입생', 그렇지 않으면 '재학생' 이라는 문자열을 반환하는 함수
#1 : GET_YEAR_INFO 라는 이름의 함수가 이미 존재한다면 지금 정의하는 함수로 대체함
#2 : 반환할 값은 가변길이의 문자열

2. ALTER FUNCTION : 사용자 정의 함수의 정의 변경

- 표기 형식

ALTER FUNCTION 함수_이름[([[파라미터_모드] [파라미터 이름] 데이터 타입[, ...]])]
	[RENAME TO 새_함수_이름];
    	[OWNER TO 사용자_명];
        [SET SCHEMA 스키마_이름];
        ...

RENAME TO : 함수의 이름 변경
OWNER TO : 함수의 소유자 변경
SET SCHEMA : 함수가 실행될 스키마 변경

- 예시

ALTER FUNCTION GET_YEAR_INFO RENAME TO IS_FRESHPERSON;

사용자 정의 함수 GET_YEAR_INFO의 이름을 IS_FRESHPERSON으로 변경

3. 사용자 정의 함수 실행

: dml에서 속성명이나 값이 놓일 자리를 대체하여 사용

- 표기 예시

SELECT 함수_이름 FROM 테이블_이름;
INSERT INTO 테이블_이름(속성1 [, ...]) VALUES(함수_이름);
DELETE FROM 테이블_이름 WHERE 속성1 = 함수_이름;
UPDATE 테이블_이름 SET 속성1 = 함수_이름;
...

- 예시

INSERT INTO STUDENT_INFO
SELECT STUDENT_NO, NAME, DEPT, IS_FRESHPERSON(YEAR)
FROM STUDENT

학생 테이블 (STUDENT)의 모든 튜플의 학번, 이름, 학과, IS_FRESHPERSON의 결과 값을 속성으로 가지는 튜플 삽입

4. DROP FUNCTION : 사용자 정의 함수 제거

- 표기 형식

DROP FUNCTION [IF EXISTS] 함수_이름[([[파라미터 모드] [파라미터 이름] 데이터 타입 [, ...]])] [, ...]
	[CASCADE | RESTRICT];

하나 이상의 함수를 제거
IF EXISTS : 제거하려는 함수의 이름이 없더라도 오류 생성을 하지 않음

- 예시

DROP FUNCTION IS_FRESHPERSON;

사용자 정의 함수 IS_FRESHPERSON 제거

저작자표시 비영리 변경금지