[2020 정보처리기사 필기] SQL 활용 (1)

108. 프로시저(Procedure) ★★★

1. 프로시저(Procedure)의 개요

- 프로시저란 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL작업을 수행한다.

- 프로시저를 만들어 데이터베이스에 저장하면 여러 프로그램에서 호출하여 사용할 수 있다.

- 프로시저는 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다.

- 프로시저는 시스템의 일일 마감, 일괄(Batch) 작업 등에 주로 사용된다.

- DECLATE, BEGIN/END, CONTROL, SQL, EXCEPTION, TRANSACTION(수행된 작업들을 DB에 적용할지 취소할지 결정하는 처리부)

 

2. 프로시저 생성

- 프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 사용한다.

- CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역변수 선언]

BEGIN

    프로시저 BODY;

END;

- OR REPLACE : 동일한 프로시저 이름이 있는 경우 기존의 프로시저를 대체

- 파라미터 : IN, OUT, INOUT, 매개변수형, 자료형

- 프로시저 BODY : 프로시저의 코드를 기록하는 부분, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 함.

 

3. 프로시저 실행

- 프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE를 줄여 EXEC로 사용하기도 한다.

 

4. 프로시저 제거

- 프로시저를 제거하기 위해서는 DROP PROCEDURE 명령어를 사용한다.

 

핵심

- 프로시저 : 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어

- 프로시저의 구성요소의 기능

- OR REPLACE : 존재하면 대체

- 실행 : EXECUTE, EXEC, CALL

- 삭제 : DROP

---

109. 트리거(Trigger) ★★

1. 트리거(Trigger)의 개요

- 트리거는 데이터베이스 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다.

- 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용된다.

- 트리거의 구문에는 DCL을 사용할 수 없다.

- 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 때 세심한 주의가 필요하다.

 

2. 트리거의 구성

- 트리거는 선언, 이벤트, 시작, 종료로 구성되며, 시작과 종료 구문 사이에는 제어(CONTROL), SQL, 예외(EXCEPTION)가 포함된다.

- DECLARE, EVENT(트리거가 실행되는 조건 명시), BEGIN/END, CONTROL, SQL, EXCEPTION

 

3. 트리거의 생성

- CREATE TRIGGER 명령어를 사용

- OR REPLACE : 동일한 트리거 존재 시 기존의 트리거를 대체

- 동작시기 옵션 : AFTER(테이블 변경된 후 트리거 실행), BEFORE(테이블 변경 전 트리거 실행)

- 동작 옵션 : INSERT, DELETE, UPDATE

- NEW | OLD : 트리거가 적용될 때 테이블의 별칭을 지정, NEW(추가되거나 수정에 참여할 튜플들의 집합), OLD(수정되거나 삭제 전 대상이 되는 튜플들의 집합)

- FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

- WHEN 조건식 : 트리거를 적용할 튜플의 조건을 지정

- BODY : 트리거의 본문 코드, 적어도 하나의 SQL 문 존재해야함

 

4. 트리거의 삭제

- DROP TRIGGER 명령어를 사용한다.

 

핵심

- 트리거 : 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- EVENT : 트리거 실행 조건 명시

- 트리거 생성시 옵션 기능

- 트리거의 삭제 : DROP TRIGGER 트리거명;

---

110. 사용자 정의 함수 ★★

1. 사용자 정의 함수 개요

- 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며, 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL이다.

- 사용자 정의 함수는 데이터베이스에 저장되어 SELECT, INSERT, DEETE, UPDATE 등 DML문의 호출에 의해 실행된다.

- RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없다.

- 사용자 정의 함수는 INSERT, DELETE, UPDATE 를 통한 테이블 조작을 할 수 없고 SELECT를 통한 조회만 할 수 있다.

- 프로시저를 호출하여 사용할 수 없다.

- SUM(), AVG() 등의 내장 함수처럼 DML문에서 반환값을 활용하기 위한 용도로 사용된다.

 

2. 사용자 정의 함수 구성

- 사용자 정의 함수의 구성은 프로시저와 유사하다. 프로시저의 구성에서 RETURN만 추가하면 된다.

 

3. 사용자 정의 함수 생성

- CREATE FUNCTION 명령어를 사용한다.

 

4. 사용자 정의 함수 실행

- DML에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.

 

5. 사용자 정의 함수 제거

- DROP FUNCTION 사용자 정의 함수명;

 

핵심

- 사용자 정의 함수는 출력 파라미터가 없다.

- SELECT만 가능