정보처리기사 공부 SQL 응용 중 절차형 SQL

프로시저

 

프로시저(Procedue)는 절차형 SQL을 활용하여 기능을 수행하는 트랜젝션언어입니다.

 

프로시저의 구조에 대해서 이야기하겠습니다. 

 

• 선언부 (DECLARE) : 프로시저의 이름과 변수, 데이터 타입을 정의하는 부분입니다.
• 시작/종료부 (BEGIN/END) : 다수의 실행이 제어하는 기본 단위로 프로시저의 시작과 종료를 표현하는 쌍입니다.
• 제어부 (CONTROL) : 조건문 IF문과 CASE문, 반복문인 LOOP문, WHILE문, FOR LOOP문을 사용한 문장을 처리합니다. 
• SQL : SQL은 SELECT, INSERT, DELETE, UPDATE과 같은 DML을 주로 사용되며 가끔 DDL도 사용됩니다. 
• 예외부 (EXCEPTION) : BEGIN~END문 사이의 SQL문의 실행에 예외 발생시 처리 방법을 정의하는 처리부분입니다.
• 실행부 (TRANSACTION) : 프로시저에서 수행되는 DML을 DBMS 적용 여부를 COMMIT(적용), ROLLBACK(취소)를 통해 결정

 

다음은 위에서 적용한 프로시저를 적용한 코드입니다.

-- 선언부 (DECLARE)
/* 	SALES_CLOSING이라는 프로시저를 정의하고 
	IN 뒤에는 파라미터로 V_CLOSING_DATE라는 8자리 문자열을 받는다
	IS 뒤에는 프로시저 내에서 사용할 숫자형식의 변수인 V_SALES_TOT_AMT를 정의 */
CREATE PROCEDURE SALES_CLOSING
  (V_CLOSING_DATE IN CHAR(8))
IS
  V_SALES_TOT_AMT NUMBER := 0;

-- 시작/종료부의 시작부 (BEGIN)
BEGIN

-- 제어부 (CONTROL)
/*	파라미터 V_CLOSING_DATE가 문자열 "20000101"일경우 “20200101"으로 값을 변경*/
IF V_CLOSING_DATE < “20000101" THEN
  SET V_CLOSING_DATE = “20200101";
END IF;

-- SQL
/*	SALES_LIST_T라는 판매내역 테이블에서 다음 SQL문을 실행
	결과 값을 V_SALES_TOT_AMT라는 변수로 전달 */
SELECT SUM(SALES_AMT)
INTO V_SALES_TOT_AMT
FROM SALES_LIST_T
WHERE SALES_DATE = V_CLOSING_DATE;

-- 예외부 (EXCEPTION)
/*	SQL의 조회 결과가 없을 경우 SQL의 결과로 NO_DATA_FOUND가 발생
	V_SALES_TOT_AMT은 0, INSERT 구문을 통해 마감된 값을 테이블에 삽입	*/
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  SET V_SALES_TOT_AMT = 0;

  INSERT INTO SALES CLOSED_T( SALES DATE, SALES TOT AMT) 
  VALUES ( V_CLOSING_DATE, V_SALES_TOT_AMT);

-- 실행부 (TRANSACTION)
COMMIT;

-- 시작/종료부의 종료부 (END)
END;

 

위에서 선언된 프로시저를 이용하려면 EXECUTE를 이용해 호출합니다.

 

EXECUTE SALES_CLOSING('20210228');

 

위의 호출문이 정상적으로 실행되면 V_CLOSING_DATE에 "20210228"이라는 8자리 문자열 값이 프로시저로 전달되며.

 

위에서 설명한 프로시저의 구조가 실행됩니다.

 

---

사용자 정의함수 (User-Defined Funtion)

 

사용자 정의함수는 절차형 SQL을 활용해 사용자가 직접 정의하고 작성한 기능을 수행하고 단일 결과 값을 반환하는 절차형 SQL입니다. 

• 선언부 (DECLARE) : 사용자 정의함수의 이름과 변수, 데이터 타입 등을 정의하는 부분입니다. 
• 시작/종료부 (BEGIN/END) : 다수의 실행이 제어하는 기본 단위로 사용자 정의함수의 시작과 종료를 표현하는 쌍입니다.
• 제어부 (CONTROL) : 조건문 IF문과 CASE문을 사용해 문장을 실행합니다. 
• SQL : 데이터 조회 용도로 SELECT를 사용합니다.
• 예외부 (EXCEPTION) : BEGIN~END문 사이의 SQL문의 실행에 예외 발생시 처리 방법을 정의하는 처리부분입니다.
• 반환부 (RETURN) : 호출문에 대한 함수의 값을 반환합니다.

-- 선언부 (DECLARE)
/* GET_AGE라는 사용자 함수를 정의하고	
   운영체제에서 프로시저로 값을 입력받는(IN) 문자열 V_BIRTH_DATE 파라미터 정의 */
CREATE FUNCTION GET_AGE(V_BIRTH_DATE IN CHAR(8))
IS

-- 시작/종료부의 시작부 (BEGIN)
/* V_CURRENT_YEAR, V_BIRTH_YEAR, V_AGE 변수 선언  */
BEGIN
V_CURRENT_YEAR CHAR(4);
V_BIRTH_YEAR CHAR(4);
V_AGE NUMBER;

-- 제어부 (CONTROL)
/* 파라미터 V_BIRTH_DATE가 "30000000"이면 “20200101"으로 값을 변경	*/
IF V_BIRTH_DATE > “30000000" THEN
  SET V_BIRTH_DATE = "20200101";
END IF;

-- SQL
/* SYSDATE로 현재 날짜를 조회해 연도 값만 파싱 후 변수 V_CURRENT_YEA에 입력
   V_BIRTH_DATE에서 연도값만 자르고 변수 V_BIRTH_DATE에 다시 입력
   두 변수의 차이를 구해 1을 더해 나이를 구한 후 AGE에 입력한다	*/
SELECT TO_CHAR(SYSDATE, 'YYYY'), SUBSTR(V_BIRTH_DATE,1,4)
INTO V_CURRENT_YEAR, V_BIRTH_YEAR
FROM DUAL;
 
SET AGE = TO_NUMBER(V_CURRENT_YEAR) - TO_NUMBER(V_BIRTH_YEAR) + 1;
 
-- 예외부 (EXCEPTION) 생략

-- 반환부 (RETURN)
RETURN AGE;

-- 시작/종료부의 종료부 (END)
END;

정의된 사용자 함수를 이용하기 위해서는 다양한 방법으로 호출 가능합니다.

SELECT GET_AGE('19990909')

FROM DUAL;

SELECT문에서 19990909라는 값을 V_BIRTH_DATE에 넘겨줘 SELECT의 결과로 사용자 함수의 결과를 반환받습니다.

 

UPDATE EMPLOYEE

SET AGE = GET_AGE(BIRTH_DATE)

WHERE EMPOYEE_ID = '201712';

또는 다음처럼 직원 테이블을 업데이트하기위해 직원번호가 201712인 직원의 나이를 사용자 정의함수를 통해 변경가능합니다. 

 

 

 

 

 

 

 

---

트리거 (Trigger)

 

트리거는 특정 테이블에 삽입, 수정, 삭제 들의 데이터 변경 이벤트가 일어나면 자동적으로 실행되도록 구현한 프로그램입니다.

 

트리거는 데이터 변화가 생길때마다 실행되는 행 트리거와 단 한번 실행되는 문장 트리거로 나눠집니다.  또한 프로시저나 사용자 정의함수와는 다르게 외부변수가 사용되지 않는다는 특징을 가지고 있습니다.

• 선언부 (DECLARE) : 트리거의 이름과 변수, 데이터 타입 등을 정의하는 부분입니다.
• 이벤트부 (EVENT) : 트리거가 실행되는 타이밍(BEFORE/AFTER)이나 이벤트(INSERT, UPDATE, DELETE)를 명시하는 부분입니다. 
• 시작/종료부 (BEGIN/END) : 다수의 실행이 제어하는 기본 단위로 트리거의 시작과 종료를 표현하는 쌍입니다.
• 제어부 (CONTROL) : 조건문 IF문과 CASE문을 사용해 문장을 실행합니다. 
• SQL : SELECT, INSERT, DELETE, UPDATE와 같은 DML이 주로 사용되며 가끔 DDL도 사용됩니다.
• 예외부 (EXCEPTION) : BEGIN~END문 사이의 SQL문의 실행에 예외 발생시 처리 방법을 정의하는 처리부분입니다.

-- 선언부 (DECLARE)
/* PUT_EMPLOYEE_HIST라는 트리거를 생성 */
CREATE TRIGGER PUT_EMPLOYEE_HIST

-- 이벤트 부 (EVENT)
/* EMPLOYEE의 수정 및 삭제가 발생할 경우 PUT_EMPLOYEE_HIST 트리거가 실행된다*/
AFTER UPDATE OR DELETE
ON EMPLOYEE
FOR EACH ROW

-- 시작/종료부의 시작부 (BEGIN)
BEGIN

-- 제어부 (CONTROL)
/* IF문을 이용하여 UPDATE 될 경우와 DELETE될 경우 SQL 이벤트를 분리*/
IF UPDATING
 THEN
    -- SQL (UPDATING)
    /* EMPLOYEE 값을 갱신 전 EMPLOYEE_ID와 EMPLOYEE_NAME을 EMPLOYEE_HIST에 삽입 */
    INSERT INTO EMPLOYEE_HIST(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_STATUS)
    VALUES ( :OLD. EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, "부서이동");

ELSIF DELETING
  THEN
    -- SQL (DEKETING)
    /* EMPLOYEE 값을 삭제 전 EMPLOYEE_ID와 EMPLOYEE_NAME을 EMPLOYEE_HIST에 삽입 */
    INSERT INTO EMPLOYEE_HIST(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_DEPT)
    VALUES ( :OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, "퇴사");
END IF;

-- 예외부 (EXCEPTION) 생략

-- 시작/종료부의 종료부 (END)
END;

트리거는 이벤트 발생시 자동으로 호출 됩니다.

 

---

관련글

2021년 정보처리기사 시험일정 총정리

 

정보처리기사 공부 통합 구현

 

정보처리기사 공부 애플리케이션 테스트 관리