절차형 SQL

 

일반적인 개발 언어처럼 SQL에도 절 자치 향적인 프로그램이 가능하도록 DBMS 벤더별로

PL(Procedural Language)/SQL : ORACLE

SQL/PL : DB2

T-SQL : SQL SERVER

등의 절차형 SQL을 제공하고 있다. 절차형 SQL을 이용하면 SQL 문의 연속적인 실행이나 조건에 따라 분기 처리를 이용하여 특정 기능을 수행하는 저장 모듈인 Procedure, User Defined Function, Trigger가 있다.

 

 

여기선 ORACLE에서 제공하는 PL/SQL만을 다뤄본다.

 

PL/SQL 특징

 

 오라클의 PL/SQL은 BLOCK 구조로 되어 있고, BLOCK 내에는 DML문장, Query 문장 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.  PL/SQL을 이용하여 다양한 저장 모듈들을 개발할 수 있다.

 

저장 모듈이란 PL/SQL을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이다. 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이라고 할 수 있다.

 

 - 블럭구조로 되어 있어 각 기능별로 모듈화가 가능하다

 - 변수/상수 등을 선언하여 SQL 문장 간 값을 교환한다.

 - IF,LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

 - DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

 - PL/SQL은 ORACLE에 내장되어 있으므로 자유롭게 동일 사양이면 옮길 수 있다.

 - PL/SQL은 응용 프로그램의 성능을 향상시킨다.

 - PL/SQL은 여러 SQL 문장을 BLOCK으로 묶고 한 번에 블록 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm

Overview of PL/SQL

 

아키텍처를 보면 PL/SQL 블록을 입력받으면 SQL 문장과 프로시저 문장을 구별하여 처리하는 걸 볼 수 있다.

 

 

PL/SQL 구조

DECLARE : 필수적이며 IS로 치환되기도 한다. BEGIN ~ END에서 사용할 변수나 인수에 대한 정의 및 데이터 타입을 선언한다.

 

BEGIN : 개발자가 처리하고자 하는 SQL문과 필요한 로직이 정의되는 실행 부이다.

 

EXCEPTION : 예외처리부로 BEGIN~END에서 실행되는 SQL문에 발생된 에러를 처리한다.

 

END

 

 

저장 모듈을 생성하는 문법은 대부분 비슷하기 때문에 STORED PROCEDURE로 알아보자.

--CREATE PROCEDURE
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 VALUES(v_DEPTNO,v_DNAME,v_LOC);
	COMMIT;
	v_result :='입력 완료';
	END IF;

	EXCEPTION WHEN OTHERS THEN ROLLBACK;
	v_result :='ERROR 발생';
END;
/

 

[OR REPLACE] : 데이터베이스에 같은 이름의 프로시저가 존재할 시 덮어쓰기를 한다.

[IN/OUT/INOUT] : MODE라고하며 전달받느냐 전달하느냐 둘다하느냐에 따라 사용한다.

/ : 데이터베이스에게 프로시저를 컴파일하라는 명령어이다.

 

주의사항

 

1. 다양한 변수가 있다. 위에 예제에서 cnt라는 변수를 SCALAR 변수라고 한다. SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정할 수 있다.

 

2. PL/SQL에서 사용하는 SQL 구문은 결괏값이 반드시 존재해야 한다. 또한 그 결과 역시 반드시 단 하나여야 한다. 

 

3. 대입 연산자는 := 를 사용한다.

 

4. Exception에는 WHEN ~ THEN 절을 이용하여 에러의 종류별로 처리할 수 있다. OTHERS를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 구별하는 것이 좋다.

 

 

 

 

User Defined Function 

 

UDF는 Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 말한다.

다른 점은 RETURN문을 사용해 하나의 값을 반드시 리턴해야 한다는 것이다.

 

CREATE OR REPLACE FUNCTION CUS_ABS(v_input 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;
/

COMMIT;

 

TRIGGER

 

CREATE OR REPLACE TRIGGER SUMMARY_SALES 
AFTER 
INSERT ON ORDER_LIST 
FOR EACH ROW 
DECLARE
	o_date ORDER_LIST.ORDER_DATE%TYPE;
    o_prod ORDER_LIST.PRODUCT%TYPE;
   BEGIN 
	 o_date := :NEW.ORDER_DATE;
	 o_prod := :NEW.PRODUCT;
	 UPDATE SALE_PER_DATE SET QTY = QTY + :NEW.QTY, AMOUNT + :NEW.AMOUNT WHERE SALE_DATE = o_date AND PRODUCT = o_prod;
	
	IF SQL%NOTFOUND THEN 
	INSERT INTO SALES_PER_DATE VALUES(o_date,o_prod,:NEW.QTY,:NEW.AMOUNT);
	END IF;
END;
/

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

 

즉 사용자가 직접 호출하는 것이 아니고 DB에서 자동적으로 수행하게 된다.

 

트리거는 테이블과 뷰, DB작업을 대상으로 정의할 수 있으며 전체 트랜잭션 작업에 대해 발생되는 트리거와 각 행에 대해서 발생되는 트리거가 있다.

 

 - NEW는 신규로 입력된 레코드의 정보를 가지고 있는 구조체

 - OLD는 수정, 삭제되기 전의 레코드의 정보를 가지고 있는 구조체

 

* ROLLBACK을 하면 하나의 트랜잭션이 취소가 되어 트리거로 입력된 정보까지 하나의 트랜잭션으로 인식하여 영향을 받은 모든 테이블 모두 입력이 취소된다.

 

트리거는 데이터베이스에 자동 호출되지만 결국 하나의 트랜잭션 안에서 일어나는 일련의 작업들이라 할 수 있다.

트리거는 데이터베이스 보안의 적용, 유요 하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.

트리거는 BEGIN~END 저네에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 없다.