PL/SQL

PL/SQL이란?

• 절차형 SQL로, Oracle DBMS에서 SQL언어를 확장하기 위해 사용하는 프로그래밍 언어.
• SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS마다 PL/SQL(oracle), SQL/PL(DB2), T-SQL(SQL server) 등의 절차형 SQL을 제공.

 

PL/SQL 특징

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

 

PL/SQL 구조

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

 

PL/SQL 기본문법

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

DROP Procedure [procedure_name];

• OR REPLACE를 붙이면 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰기 함.
• [mode]에는 3가지 유형이 있는데, 첫번째로 in은 값을 프로시져로 전달하고, 두번째로 out은 처리된 결과를 운영체제로 전달하고, 마지막으로 inout은 in과 out두가지 기능을 동시에 수행.
• / 는 프로시저를 컴파일 하라는 명령.

 

변수

TYPE	설명
NUMBER	정수나 실수
CHAR	고정길이의 문자
VARCHAR2	가변길이의 문자
DATE	날짜와 시간
BOOLEAN	TRUE, FALSE 값

---

Procedure란?

• 특정 작업을 수행하는 이름이 있는 PL/SQL Block
• 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

Procedure 작성

CREATE OR REPLACE Procedure DEPT_insert
(p_DEPTNO in number,
 p_DNAME  in varchar2,
 p_LOC    in varchar2,
 p_RESULT out varchar2)
 
IS
 cnt number := 0;

BEGIN
 SELEECT COUNT(*) INTO CNT
 FROM DEPT
 WHERE DEPTNO = p_DEPTNO
 AND ROWNUM = 1;
 if cnt > 0 then
    p_RESULT := '이미 등록된 부서번호';
 else
    INSERT INTO DEPT(DEPTNO, DNAME, LOC)
    VALUES(p_DEPTNO, p_DNAME, p_LOC);
    COMMIT;
    p_RESULT := '입력완료';
 end if;
 
EXCEPTION
   WHERE OTHERS THEN
      ROLLBACK;
      p_RESULT := 'ERROR';

END;
/

 

Procedure 실행

SELECT * FROM DEPT;

variable rslt varchar2(30);
EXCUTE DEPT_insert(50, 'dev','seoul',:rslt);
print rslt;

//실행한 결과값을 받기 위해 rslt라는 변수를 선언
//EXCUTE로 프로시져를 실행
//print rslt로 실행결과를 확인

---

Trigger란?

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

 

Trigger 작성

CREATE TABLE ORDER_LIST(
ORDER_DATE CHAR(8) NOT NULL,
PRODUCT VARCHAR2(10) NOT NULL,
QTY NUMBER NOT NULL,
AMOUNT NUMBER NOT NULL);

CREATE TABLE SALES_PER_DATE(
SALE_DATE CHAR(8) NOT NULL,
PRODUCT VARCHAR2(10) NOT NULL,
QTY NUMBER NOT NULL,
AMOUNT NUMBER NOT NULL);

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 SALES_PER_DATE
    SET qty = qty + :NEW.qty,
    amount = amout + :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;
/

• AFTER INSERT : INSERT 이후 TRIGGER 발생
• ON ORDER_LIST : ORDER_LIST 테이블에 TRIGGER 작성
• FOR EACH ROW : 각 ROW마다 Trigger 작성
• ORDER_LIST.order_date%TYPE : o_date 변수는 ORDER_LIST의 order_date컬럼의 변수타입으로 선언
• o_date := :NEW.order_date : ORDER_LIST에 입력된 값을 o_date에 저장

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

• SQL$NOTFOUND : 위의 조건에 해당하는 값이 없으면 then 다음을 실행

 

Trigger 실행

SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;
INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 30000);
COMMIT;

SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;

// 추가가 된걸 확인할 수 있다.

---

Procedure와 Trigger의 차이점

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

출처 : SQL 전문가 가이드