[Oracle] PL/SQL, 저장프로시저, 트리거

* PL/SQL

- Procedural Language/Structured Query Language의 줄임말로, 데이터베이스 응용 프로그램을 작성하는 데 사용하는 오라클의 SQL 전용 언어.
- SQL 문에 변수, 제어, 입출력 등의 프로그래밍 기능을 추가하여 SQL 만으로 처리하기 복잡한 문제를 해결하는 용도로 사용
- SQL 언어는 비절차 언어이기 때문에 쿼리문들간에 연결점이 없다. PL/SQL 을 사용하여 연결점을 만들어 프로그램으로 동작하게 하는 언어이다.

 

- PL/SQL Block의 종류

1) 익명 블록 : 이름이 없는 PL/SQL 블록을 말한다.
2) 이름 있는 블록
- 프로시저 : 이름이 있는 PL/SQL 블록
- 트리거 : 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록
- 함수 : 반환값이 존재하는 PL/SQL 블록
- 패키지 : 하나 이상의 프로시저, 함수 등의 묶음

 

DECLARE
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END;
/

- 선언부 : DECLARE로 시작. 실행부와 예외 처리부에서 사용할 각종 변수, 상 수, 커서 등을 선언. 변수 선언과 각 문장의 끝에 반드시 세미콜론(;)을 찍어야 한다.

- 실행부 : 실제 로직을 처리하는 부분 각종 문장(일반 SQL문, 조건문, 반복문 등)이 온다. DML 문만 사용가능

- 예외 처리부 : EXCEPTION 절로 시작. 실행부에서 로직 처리 중 오류 발생시 처리할 내용 기술 생략 가능

 

---

*DECLARE 선언부

DECLARE
	변수이름 데이터타입 := 초기화값;                   -- 변수 선언 및 초기화 
	변수이름 데이터타입;                                   -- 변수 선언만 
	변수이름 CONSTANT 데이터타입 := 초기화값; 	 -- 상수 선언 및 초기화
BEGIN
	변수이름 := 초기화값;
… 
END; /

 

 

 

*BEGIN 실행부

- DML 실행문

- 조건, 반복 등 연산

- 출력

DECLARE 
	V_BOOKNAME VARCHAR2(50); 
BEGIN 
	SELECT BOOKNAME
	INTO V_BOOKNAME 	--BOOKNAME이 V_BOOKNAME변수에 저장
	FROM BOOK 
	WHERE BOOKID = 5;

	DBMS_OUTPUT.PUT_LINE(V_BOOKNAME); 
END;
/

*IF 조건문

DECLARE 
	선언부 
BEGIN 
	IF 조건식 THEN 
		수행 명령어;
	ELSIF 조건식 THEN 
		수행 명령어; 
	ELSIF 조건식 THEN 
		수행 명렁어;
	… 
	ELSE
    	수행 명령어;
	END IF;
END; 
/

--IF조건문을 이용하여 축구관련책이 몇권인지 구하기

DECLARE
    V_BOOKNAME BOOK.BOOKNAME%TYPE;      --변수선언. 아직 값은 없음
    					--데이터타입은 BOOK테이블의 BOOKNAME과 같은 타입으로 설정
    V_COUNT NUMBER;
BEGIN
    V_BOOKNAME := '축구';			--변수 '축구'로 초기화
    IF V_BOOKNAME = '축구' THEN
        SELECT COUNT(*)
        INTO V_COUNT
        FROM BOOK
        WHERE BOOKNAME LIKE '%축구%';
        DBMS_OUTPUT.PUT_LINE('축구 관련 책: '||V_COUNT||'권');
        
    ELSIF V_BOOKNAME = '골프' THEN
        SELECT COUNT(*)
        INTO V_COUNT
        FROM BOOK
        WHERE BOOKNAME LIKE '%골프%';
        DBMS_OUTPUT.PUT_LINE('골프 관련 책: '||V_COUNT||'권');
    ELSE
        DBMS_OUTPUT.PUT_LINE('그 외 책 여러권');
        
    END IF;    
END;

 

 

---

* 저장프로시저

 

-PL/SQL 을 주기적으로 사용해야 할 때 이름을 줘서 오라클에 저장 해 두는 PL/SQL 프로그램

--입력받는 프로시저
CREATE [OR REPLACE] PROCEDURE 프로시저 이름
( 
	입력변수 타입 
) 
IS
	선언부
BEGIN 
	실행부
EXCEPTION 
	예외처리부
END;


--실행
EXECUTE 프로시저 이름 (입력변수); 

CREATE OR REPLACE PROCEDURE PRO_BOOKCOUNT
(
    P_BOOKNAME VARCHAR2
)
IS
    V_COUNT NUMBER;
BEGIN
    --P_BOOKNAME := '골프';
    IF P_BOOKNAME = '축구' THEN
        SELECT COUNT(*)
        INTO V_COUNT
        FROM BOOK
        WHERE BOOKNAME LIKE '%축구%';
        DBMS_OUTPUT.PUT_LINE('축구 관련 책: '||V_COUNT||'권');
        
    ELSIF P_BOOKNAME = '골프' THEN
        SELECT COUNT(*)
        INTO V_COUNT
        FROM BOOK
        WHERE BOOKNAME LIKE '%골프%';
        DBMS_OUTPUT.PUT_LINE('골프 관련 책: '||V_COUNT||'권');
    ELSE
        DBMS_OUTPUT.PUT_LINE('그 외 책 여러권');
        
    END IF;    
END;
/

--실행
EXECUTE PRO_BOOKCOUNT('골프');	--결과값: 골프 관련 책: 1권

 

 

---

* 트리거

-특정 상황이나 동작에 의해 이벤트가 발생할 경우 자동 으로 실행되는 PL/SQL을 말한다.

 

CREATE [OR REPLACE] TRIGGER 트리거 이름 
BEFORE | AFTER 
INSERT OR UPDATE OR DELETE ON 테이블명
BEGIN 
	실행부
END;

--트리거(자동동작)하기 위한 사전작업
--hisorders테이블 생성, 시퀀스생성

create table hisorders(
    hisid number primary key,
    orderid number not null,
    orderstate char(1) check (orderstate in ('O','U','C')),
    changdt date default sysdate
    );
    
select * from hisorders;

--hisid를 자동으로 증가시키기 위한 시퀀스 생성
create sequence hisorders_hisid_seq
increment by 1
start with 1;
---------------------------------------------------------------------
--TRIGGER
create or replace trigger trg_hisorders
after
insert or update or delete on orders    --orders에서 insert, update, delete가 실행되면 동작
FOR EACH ROW

begin
    if inserting then --orders테이블에 insert후 동작
        insert into hisorders(hisid,orderid,orderstate)
        values(hisorders_hisid_seq.nextval,:new.orderid,'O');
    
    elsif updating then    --orders테이블에 update후 동작
        insert into hisorders(hisid,orderid,orderstate)
        values(hisorders_hisid_seq.nextval,:new.orderid,'U');
    
    elsif deleting then    --orders테이블에 delete후 동작
        insert into hisorders(hisid,orderid,orderstate)
        values(hisorders_hisid_seq.nextval,:new.orderid,'D');
        
    end if;
end;
/

--orders에 insert, update하여 트리거가 제대로 작동하는지 확인
insert into orders (orderid, custid, bookid, saleprice, orderdate)
values(12,2,6,2000,sysdate);

select * from hisorders;

update orders
set saleprice = 10000
where orderid = 12;

hisorders테이블 결과