[자격증 / SQLD] SQL 활용(4)_절차형 SQL

 

DATA ON-AIR에서 28번 절차형 SQL을 요약 정리한 내용이다.

 

 

 

---

 

 

절차형 SQL 개요

절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다. 절차형 SQL을 이용하여 만들 수 있는 저장 모듈은 Procedure, User Defined Function, Trigger가 있다.

• Oracle의 PL/SQL은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.

 

|  저장 모듈

저장 모듈이란 PL/SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다. 이런 PL/SQL을 이용하여 다양한 저장 모듈(Stored Module)을 개발할 수 있다.

 

 

PL(Procedural Language)/SQL

Oracle의 PL/SQL은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.

• 프로그램 문장은 PL/SQL 엔진의 Procedural Statement Executor가 처리
• SQL 문장은 Oracle 서버의 SQL Statement Executor가 실행

• Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다. -> 호환성
• 응용 프로그램의 성능을 향상시킨다.
• 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 

 

|  PL/SQL 구조

다음은 PL/SQL의 Block 구조를 표현한 내용이다.

구조	설명
DECLARE	BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
BEGIN ~ END	개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부
EXCEPTION	BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의하는 예외 처리부

 

 

 

T-SQL

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서, T-SQL은 엄격히 말하면, MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것이다. T-SQL을 이용하여 다양한 저장 모듈(Stored Module)을 개발할 수 있다.

• 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.
• 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며, 전역변수는 이미 SQL서버에 내장된 값이다.
• 데이터 유형(Data Type)을 제공한다. -> int, float, varchar 등의 자료형을 의미
• 연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.
• 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.
• 주석 기능 사용 가능하다. 

 

 

|  T-SQL 구조

다음은 T-SQL의 구조를 표현한 내용이다. PL/SQL과 유사하다.

구조	설명
DECLARE	BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
BEGIN ~ END	개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부 블록 단위로 처리하고자 할 때는 반드시 작성해야 함
ERROR 처리	BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부

 

 

 

저장 모듈 생성과 활용

|  Procedure

CREATE [OR REPLACE] Procedure [Procedure_name]
(argument1 [mode] data_type1, argument2 [mode] data_type2, ...)
IS ...
BEGIN ...
      EXCEPTION ...
END;
/

CREATE Procedure	CREATE OR REPLACE 사용 시 동일 이름의 Procedure를 덮어 쓴다. 삭제 시 DROP Procedure
Mode	IN : 운영체제에서 프로시저로 전달되는 변수 (default) OUT : 프로시저에서 운영체제로 전달되는 변수 INOUT : 거의 사용하지 않음
/	프로시저 컴파일 시작

 

+ DEPT 테이블에 새로운 부서를 등록하는 Procedure 생성 예시

아래는 Procedure의 기능을 Flow Chart로 나타낸 그림이다.

CREATE OR REPLACE PROCEDURE p_DEPT_insert 
(v_DEPTNO IN NUMBER, v_dname IN varchar2, v_loc IN varchar2, v_result OUT varchar2) 
IS 
  cnt NUMBER := 0;  -- 임시로 사용할 변수
BEGIN 
  SELECT COUNT(*) INTO CNT 
  FROM DEPT 
  WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1; 
  if cnt > 0 THEN 
    v_result := '이미 등록된 부서번호이다'; 
  else 
    INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (v_DEPTNO, v_dname, v_loc); 
    COMMIT; 
    v_result := '입력 완료!!'; 
  end if; 
EXCEPTION 
  WHEN OTHERS THEN 
    ROLLBACK; 
    v_result := 'ERROR 발생'; 
END; 
/

 

+Procedure 실행 예시

10번 부서가 이미 등록되어 있는 경우

variable rslt varchar2(30);

EXECUTE p_DEPT_insert(10, 'dev', 'seoul', :rslt);
print rslt;

RSLT
-------------------------------------------------
-- 이미 등록된 부서번호이다.


EXECUTE p_DEPT_insert(50, 'dev', 'seoul', :rslt);
print rslt;

RSLT
-------------------------------------------------
-- 입력 완료!

 

 

|  사용자 정의 함수(User Defined Function)

• Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미
  • cf) 내장 함수(Built-in Function) - 벤더에 의해 정의된 함수
• Procedure와 달리 수행 결과값을 반드시 Return해야 함
  • 예) 절대값을 반환하는 사용자 정의 함수 UTIL_ABS의 정의 및 호출

 

+ 사용자 정의 함수 생성 예시

CREATE OR REPLACE FUNCTION
  util_abs (v_input IN NUMBER) RETURN NUMBER 
IS 
  v_return NUMBER := 0; 
BEGIN 
  IF v_input < 0 THEN v_return := v_input * (-1); 
  ELSE v_return := v_input; 
  END IF; 
RETURN v_return; 
END; 
/

1. 숫자 값을 입력 받는다. 예제에서는 숫자 값만 입력된다고 가정한다.
2. 리턴 값을 받아 줄 변수인 v_return을 선언한다.
3. 입력 값이 음수이면 -1을 곱하여 v_return 변수에 대입한다.
4. v_return 변수를 리턴한다.

 

+ 사용자 정의 함수 실행 예시

SELECT SCHE_DATE, util_abs(HOME_SCORE - AWAY_SCORE) 점수차
FROM   SCHEDULE

 

 

|  Trigger

Trigger란 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

• 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행됨
  • Procedure: Execute 명령어로 실행 / Function : 함수 이름으로 실행
  • Trigger : 생성된 후 DML에 의해 자동으로 실행
• 주로 데이터 무결성 보장을 위해 FK처럼 동작하거나, 실시간 집계성 테이블 생성에 사용됨
• 보안 적용, 유효하지 않은 트랜잭션 예방, 업무 규칙 적용, 감사 제공 등에도 사용
• OLTP 시스템에서는 부하로 인해 성능이 저하될 수 있음
• Row Trigger와 Statement Trigger로 구분
• ROLLBACK 시, 원 트랜잭션 뿐만 아니라 Trigger에 의해 실행된 연산도 모두 취소됨
  • Trigger는 INSERT, DELETE, UPDATE문과 연결된 하나의 트랜잭션 내에서 수행되는 작업으로 이해해야 함
  • Procedure : BEGIN ~ END 사이에 COMMIT, ROLLBACK 가능
  • Trigger : BEGIN ~ END 사이에 COMMIT, ROLLBACK 사용 불가

 

+ 트리거(Trigger) 주요 구문

FOR EACH ROW

Row Trigger / Statement Trigger의 지정을 위한 구문

• "FOR EACH ROW" 사용 -> Row Level Trigger -> SQL 문장의 각 행마다 Trigger 발생
• "FOR EACH ROW" 생략 -> Statement Level Trigger -> SQL 문장에 한 번만 Trigger 발생

왼쪽 : Row Level / 오른쪽 : Statement Level

 

• AFTER / BEFORE : Trigger 시점 명시
• :NEW : 문장 수행 후의 정보를 갖는 구조체 
  •  ex) o_prod := :NEW.product
• :OLD : 문장 수행 전의 정보를 갖는 구조체

구분	:OLD	:NEW
INSERT	NULL	입력된 레코드 값
UPDATE	UPDATE되기 전의 레코드 값	UPDATE된 후의 레코드 값
DELETE	레코드가 삭제되기 전 값	NULL

• 변수선언
  • ORDER.order_date%TYPE; -> ORDER 테이블의 order_date 칼럼과 동일한 타입으로 선언하라는 의미

 

+ 트리거(Trigger) 생성 예

새로운 주문이 입력되면 판매 집계 테이블이 업데이트 되는 시나리오로 진행해보자.

주문 관리 테이블(ORDER)	ORDER_DATE, PRODUCT, QTY, AMOUNT
판매 실적 관리 테이블(SALES)	SALE_DATE, PRODUCT, QTY, AMOUNT

• 새로운 주문 입력 시
  • ORDER 테이블에 새로운 주문 추가 -> (Trigger) -> SALES 테이블 갱신 또는 추가
  • SALES에 해당 주문일자, 해당 상품이 있으면 기존 수량/금액 UPDATE
    • 예 : (2017-01-01, "P01", 5, 250,000) -> (2017-01-01, "P01", 6, 300,000)
  • 그러나 SALES에 해당 주문일자, 해당 상품이 없으면 새 레코드 추가(INSERT)

CREATE OR REPLACE TRIGGER summary_sales
  AFTER INSERT
  ON ORDER 
  FOR EACH ROW 
  
DECLARE
  o_date ORDER.order_date%TYPE;
  o_prod ORDER.product%TYPE;
 
BEGIN
  o_date := :NEW.order_date;
  o_prod := :NEW.product;
  UPDATE SALES SET qty = qty + :NEW.qty, amount = amount + :NEW.amount
  -- amount = amount(SALES 테이블의 amount) + :NEW.amount(ORDER 테이블의 INSERT 후의 amount)를 의미
  WHERE sale_date = o_date AND product = O_prod;

  IF SQL%NOTFOUND THEN
    INSERT INTO SALES VALUES(o_date, o_prod, :NEW.qty, :NEW.amount);
    END IF;
END;
/

 

 

|  프로시저(Procedure)와 트리거(Trigger)의 차이점

프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, 데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다.

프로시저(Procedure)	트리거(Trigger)
CREATE Procedure 문법 사용	CREATE Trigger 문법 사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 불가

 

 

 

---

[관련 글 보러가기]

 

• [자격증 / SQLD] SQL 기본(1)_TABLE과 명령어(DML, DDL, DCL, TCL)
• [자격증 / SQLD] SQL 기본(2)_WHERE 조건절(연산자)과 함수
• [자격증 / SQLD] SQL 기본(3)_GROUP BY, ORDER BY, 조인(JOIN)
• [자격증 / SQLD] SQL 활용(1)_표준 조인과 집합 연산자
• [자격증 / SQLD] SQL 활용(2)_계층형 질의, 서브쿼리
• [자격증 / SQLD] SQL 활용(3)_그룹 함수와 윈도우 함수

 

---

 

[참고자료]

 

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=list

SQL – DATA ON-AIR

https://youtu.be/BenQo-aeKeg?si=DMDZWuGEoykGP7pq