2020 정보처리기사 필기 - 3.4 SQL 활용(1)

프로시저

프로시저의 개요

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

- 호출을 통해 실행되어 미리 저장해놓은 SQL 작업 수행

- 여러 프로그램에서 호출하여 사용 가능

- 데이터베이스에 저장되어 수행되기 때문에 스토어드 프로시저라고도 함

- 시스템의 일일 마감 작업, 일괄 작업 등에 사용

 

프로시저의 구성

- DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부

- BEGIN / END : 프로시저의 시작과 종료를 의미

- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리

- SQL : DML, DCL이 삽입되어 데이터 관리를 위한 작업(조회, 추가, 수정, 삭제) 수행

- EXCPETION : BEGIN ~ END 안의 구문 실행 시 예외 처리

- TRANSACTION : 수행된 데이터 작업들을 DB에 저장할지 취소할지 결정

 

프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역번수 선언]
BEGIN
    프로시저 BODY;
END;

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

- 파라미터 

    -> IN : 호출 프로그램이 프로시저에게 값을 전달할 때 사용

    -> OUT : 프로시저가 호출 프로그램에게 값을 전달할 때 사용

    -> INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에게 값을 반환할 때 지정

    -> 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정

    -> 자료형 : 변수의 자료형을 지정

- 프로시저 BODY

    -> 프로시저의 코드를 기록

    -> BEGIN과 END 사이에 적어도 하나의 SQL 문이 존재해야 함

- 사원번호를 입력받아 해당 사원의 지급방식을 S로 변경하는 프로시저

CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS
BEGIN
    UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;
    EXCEPTION
        WHEN PROGRAM_ERROR THEN
            ROLLBACK;
    COMMIT;
END;

 

프로시저 실행

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명; 

- 위 3가지 명령어 중 하나를 사용

- 위에 정의한 emp_change_s 프로시저를 사원번호 32를 인수로 하여 실행

EXECUTE emp_change_s(32);
EXEC emp_change_s(32);
CALL emp_change_s(32);

 

프로시저 제거

DROP PROCEDURE 프로시저명;

- 위에 정의한 emp_change_s 프로시저를 제거

DROP PROCEDURE emp_change_s;

 

트리거

트리거의 개요

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

- 데이터베이스에 저장

- 데이터 변경 및 무결성 유지 로그 메시지 출력 등의 목적으로 사용

- DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

- 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미침

 

트리거의 구성

- DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의

- EVENT : 트리거가 실행되는 조건

- BEGIN / END : 트리거의 시작과 끝

- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리

- SQL : DML문이 삽입되어 데이터 관리를 위한 작업(조회, 추가, 수정, 삭제) 수행

- EXCEPTION : BEGIN ~ END 안의 구문에서 예외가 발생 시 처리

 

트리거의 생성

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
    트리거 BODY;
END;    

- 동작 시기 옵션 : 트리거가 실행될 때를 지정

    -> AFTER : 테이블이 변경된 후

    -> BEFORE : 테이블이 변경되기 전

- 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정

    -> INSERT : 새로운 튜플을 삽입할 때

    -> DELETE : 튜플을 삭제할 때

    -> UPDATE : 튜플을 수정할 때

- NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정

    -> NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미

    -> OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

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

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

- 학생 테이블에 새로운 튜플이 삽입될 때 튜플에 학년 정보가 누락되었으면 학년 필드에 신입생을 치환하는 트리거를 학년 정보_tri라는 이름으로 정의

CREATE TRIGGER 학생정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table.학년 IS NULL)
BEGIN
    :new_table.학년 := '신입생';
END;    

 

트리거의 제거

DROP TRIGGER 트리거명;

- 위에 정의한 학생정보_tri를 제거

DROP TRIGGER 학생정보_tri;

 

사용자 정의 함수

사용자 정의 함수의 개요

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

- 데이터베이스에 저장되어 DML문의 호출에 의해 실행

- 예약어 RETURN을 통해 값이 반환되기 때문에 출력 파라미터가 없음

- 테이블 조작은 할 수 없고 SELECT를 통해 검색만 할 수 있음

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

 

사용자 정의 함수의 구성

- 프로시저의 구성과 유사

- RETURN : 호출 프로그램에 반환할 값이나 변수를 정의

 

사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
    사용자 정의 함수 BODY;
    RETURN 반환;
END;

- 프로시저와 유사하며 파라미터에의 구성요소는 IN, 매개변수명, 자료형이 있음

- RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

- i_성별코드를 입력받아 1이면 남자, 2면 여자를 반환하는 사용자 정의 함수를 Get_S_성별로 정의

CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
RETURN VARCHAR2
IS
BEGIN
    IF (i_성별코드 = 1) THEN
        RETURN '남자';
    ELSE
        RETURN '여자';
    END IF;
END;

 

사용자 정의 함수 실행

SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

 

사용자 정의 함수 제거

DROP FUNCTION 사용자 정의 함수명;

- 위에 정의한 Get_S_성별을 제거

DROP FUNCTION Get_S_성별;

필기 정리

2020 정보처리기사 필기 정리