[정보처리기사]3.데이터베이스 구축/SQL 활용/절차형 SQL

절차형 SQL: 프로시저, 트리거, 사용자 정의 함수

1. 절차형 SQL(procedural SQL)

- 선언형 프로그래밍 기능

- 클라이언트/서버 데이터베이스 환경에서 저장 프로시저(stored procedure)를 작성

- C, JAVA 등의 프로그래밍 언어와 같이 연속적인 실행/ 분기, 반복 등의 제어가 가능

- 일반적인 프로그래밍 언어에 비해 효율이 떨어지지만, 연속적인 작업 처리 적합

- BEGIN ~ END 형식으로 작성되는 블록(Block) 구조로 기능별 모듈화 가능

1.1 PL/SQL 

• 선언부(Declare) 실행부에서 참조할 모든 변수, 상수, CURSOR, EXCEPTION 선언
• 실행부(Begin/End) BEGIN과 END 사이에 기술되는 영역,
                                데이터를 처리할 SQL문과 PL/SQL 블록을 기술
• 예외부(Exception) 실행부에서 에러가 발생했을 때 문장 기술

→ 장점: 컴파일 불필요, 모듈화 기능, 절차적 언어 사용, 에러 처리 

→ PL/SQL을 활용한 저장형 객체 활용

-저장된 프로시저, 저장된 함수,  트리거(Trigger) ,저장된 패키지    

 

 

2. 절차적 SQL 3가지

2.1 프로시저(Procedure) #C SE T

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

- 처리 결과는 한 개 이상의 값 혹은 반환을 아예 하지 않음

- 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용됨

2.1.2 프로시저 구성

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

BEGIN(필수): 프로시저의 시작을 의미, 실행부

• CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
• SQL: DML, DCL이 삽입되어 데이터 관리를 위한 작업 수행
• EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리
• TRANSACTION: 수행된 데이터 작업들을 DB에 적용할지 말지 결정하는 처리
  WHEN ERROR TEHN ROLLBACK; 에러 있는 경우 롤백하기

END(필수): 프로시저의 종료를 의미, BEGIN/END는 함께 다님

DECLARE
BEGIN
[Control]
[SQL]
[Exception]
[TRANSACTION]
END

 

2.1.3 프로시저 생성,실행,제거

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

-- 프로시저 실행
EXECUTE 프로시저명;
EXEC 프로시저명;
-- 프로시저 제거
DROP PROCEDURE 프로시저명;

* OR REPLACE: 선택적인 예약어, 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저를 대체

2.2. 트리거(Trigger) 2020년 1, 2회 기출문제 # E CSE

- 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관력 작업을 자동 프로시저 수행

- DB에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용됨

- DCL 은 불가하며 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

- 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 생성시 주의

2.2.1 구성

DECLARE(필수): 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부

EVENT(필수): 트리거가 실행되는 이벤트 조건을 명시(INSERT, UPDATE, DELETE)

BEGIN(필수): 트리거의 시작을 의미, 실행부

• CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
• SQL: DML문이 삽입되어 데이터 관리를 위한 작업 수행
• EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리

END(필수): 트리거의 종료를 의미, BEGIN/END는 함께 다님

DECLARE
Event
BEGIN
[Control]
[SQL]
[Exception]
END

 

2.2.2 트리거 생성 , 제거

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

-- 트리거 생성 예시
CREATE TRIGGER the_mooses_are_loose
AFTER INSERT ON animals
FOR EACH ROW
BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END;

-- 트리거 제거
DROP TRIGGER 트리거명;

* 동작시기 옵션: BEFORE(이벤트 전 실행), AFTER(이벤트 후 트리거 실행)

*동작 옵션 :  INSERT, DELETE, UPDATE

* NEW | OLD: NEW(이벤트에 참여할 테이블), OLD(이벤트 전 테이블)

* FOR EACH ROW: 각 튜플마다 트리거 적용

2.3.사용자 정의 함수  #디비컨SER

- 프로시저와 유사하게 SQL을 사용해 일련의 작업을 연속적으로 처리

- 종료 시 예약어 RETURN을 사용해 처리 결과를 단일값으로 반환

- RETURN을 통해 값을 반환해, 출력(OUT) 파라미터가 없음

- DML로 호출하여 실행가능

- INSERT, DELETE, UPDATE로 테이블 조작 불가하고, SELECT로 조회만 할 수 있음

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

구분	프로시저	사용자 정의 함수
반환값	없거나 1개 이상 가능	1개(단일값)
파라미터	입, 출력	입력(IN)
사용 가능 명령문	DML, DCL	SELECT
호출	프로시저, 사용자 정의 함수	사용자 정의 함수
사용 방법	실행문	DML에 포함해서 사용

2.3.1 구성

DECLARE(필수): 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부

BEGIN(필수): 사용자 정의 함수의 시작을 의미, 실행부

• CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
• SQL: SELECT문이 삽입되어 데이터 관리를 위한 작업 수행
• EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리

RETURN(필수): 호출 프로그램에 반활할 값이나 변수를 정의

END(필수): 사용자 정의 함수의 종료를 의미, BEGIN/END는 함께 다님

DECLARE
BEGIN

사용자 정의 함수 BODY;

RETURN 반환값;

END

2.3.2 사용자 정의 함수 생성, 제거 ,변경

-- 사용자 정의 함수 생성
CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터) [지역변수 선언]
RETURNS 데이터 타입
BEGIN
함수 바디
RETURN 단일값
END;
-- 사용자 정의 함수 제거
DROP FUNCTION <사용자 정의 함수, UDF>
-- 사용자 정의 함수 변경
ALTER FUNCTION <사용자 정의 함수, UDF> 
RETURNS 데이터 타입
BEGIN
함수 바디
RETURN 단일값
END;

2.4. 제어문 (if, for loop) 

2.4.1 조건문

• 조건에 따라 실행할 문장 달리함
•  IF ,THEN, ELSE ,END IF 로 이루어짐
  • 조건 1이 부합한다면 실행1을 하고 그렇지 않으면 실행 2를 하라.

DECLARE 

BEGIN 

IF <조건1>
THEN <실행1>
ELSE <실행2>
END IF;

END;

2.4.2 반복문

• 조건이 맞으면 반복으로 수행되던 loop을 끝낸다.
•  LOOP, EXIT WHEN , END LOOP 구성

DECLARE 

BEGIN 

LOOP

EXIT WHEN <조건>

END LOOP;
END;

 

 

 

[정보처리기사 ] 정보처리기사 필기 목차

[정보처리기사] 개정된 정보처리기사 필기 목차