[Oracle] 관계형DB PL/SQL(Procedure, Trigger,사용자정의함수) 정리

PL/SQL

Procedural Language/Structured Query Language의 줄임말로 데이터베이스 응용 프로그램을 작성하는 데 사용하는 오라클의 SQL 전용 언어. 
SQL 전용 언어로 SQL 문에 변수, 제어, 입출력 등의 프로그래밍 기능을 추가하여 SQL 만으로 처리하기 어려운 문제를 
해결함. 
PL/SQL은 SQL Developer에서 바로 작성하고 컴파일한 후 결과를 실행함. 

(기본 문법: https://docs.oracle.com/cd/E11882_01/index.htm)

 

 

---

Procedure

자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘린다.

함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만

프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램이다.

-- procedure
-- Book테이블에 한 개의 투플을 삽입하는 프로시저
create or replace procedure InsertBook(
    -- 매개변수 선언
    myBookId in number,
    myBookName in varchar2,
    myPublisher  in varchar2,
    myPrice in number
)
as
begin -- 코드 실행 시작
    -- 핵심처리 코드
    -- 매개변수로 받은 데이터를 이용해서 데이터 삽입.
    insert into book (bookid, bookname, publisher, price) 
    values (myBookId, myBookName, myPublisher, myPrice);
end -- 코드 실행 종료
;

 

예제

1

-- procedure
create or replace procedure BookInsertOrUpdate(
    -- 매개변수
    myBookId number,
    myBookName varchar2,
    myPublisher varchar2,
    myPrice number
)
as
 -- 변수 선언
    mycount number;
begin
    -- 입력받은 책이름으로 같은 이름의 책을 보유하는지 여부 확인 숫자
    select count(*) into mycount from book where bookname like myBookName;
    -- 책의 개수로 분기 : 수정 또는 입력
    if mycount != 0 then
        -- 가격 수정
        update book set price = myPrice where bookname like mybookName;
    else 
        -- 데이터(행) 입력
        insert into book values (myBookId, myBookName, myPublisher, myPrice);
    end if;
end
;

 

2

-- procedure

-- Book 테이블에 저장된 도서의 평균가격을 반환하는 프로시저
create or replace procedure avgPrice(
    avgVal out number
)
as
begin
    select avg(price) into avgVal from book where price is not null;
end
;

 

3

-- procedure

-- Orders 테이블의 판매 도서에 대한 이익을 계산하는 프로시저(Interest)
create or replace procedure interest
as
    -- 변수 선언
    myInterest NUMERIC;
    price  NUMERIC;
    -- 1. 커서 생성
    cursor interestCursor is select saleprice from orders;
begin   
    myInterest := 0.0;
    -- 2. 커서 오픈
    open interestCursor;
    -- 반복하면서 각 행의 값을 처리
    loop
        --3. 커서 패치
        fetch interestCursor into price;
        
        -- 반복문의 탈출
        exit when interestCursor%notfound;
        
        -- price 값을 비교
        if price >= 30000 then
            myInterest := myInterest + price*0.1;
        else
            myInterest := myInterest + price*0.05;
        end if;
        
    end loop;
    
    -- 4. 커서 종료
    close interestCUrsor;
    
    -- 수익금 총액을 출력
    dbms_output.put_line('전체 이익금 : ' || myInterest);
       
end
;

 

4

-- procedure
-- 테이터를 받아서 데이터 변경처리를 프로그램 요소를 이용해서 실행


-- create or replace {프로시저 이름} (매개변수)
-- as -- 선언부
-- begin -- 프로그램 코드 시작
-- ......
-- end
-- ;

-- 프로시저 호출 : 실행 요청
-- exec 프로시저이름(매개변수 값,....);
exec insertbook(13, '스포츠과학', '마당과학서적', 25000);
select * from book;

-- 같은 이름의 책을 등록해보고, 다른 이름의 책을 입력
-- 같은 이름의 책 등록
exec bookinsertorupdate(15, '스포츠과학', '마당과학서적', 10000);
exec bookinsertorupdate(15, '스포츠 즐거움', '마당과학서적', 40000);
select * from book;

----------------------------------------------------
set serveroutput on;
declare
    avgVal number;
begin
    -- 프로시저 호출
    avgPrice(avgVal);
    dbms_output.put_line('책값 평균 : '|| avgVal);
end;

----------------------------------------------------
set serveroutput on;
exec interest;

 

 

---

Trigger

데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 따라서 실행되는 프로시저를 말함.

 

예제

-- 임시 테이블 생성
create table book_log (
    bookid_l number,
    bookname_l varchar2(40),
    publisher_l varchar2(40),
    price_l number
);

-- 트리거
create or replace trigger afterInsertBook
after insert on book for each row
declare
    -- 변수 선언
    average number;
begin
    insert into book_log
    values (:new.bookid, :new.bookname, :new.publisher, :new.price);
    dbms_output.put_line('book_log 테이블에 백업!');
end
;

-----------------------------------------------------
-- 트리거 실행 테스트
insert into book values (20, '스포츠 과학2', '이상미디어', 60000);

select * from book;
select * from book_log;

 

---

사용자 정의 함수

사용자 정의 함수는 수학의 함수와 마찬가지로 입력된 값을 가공하여 결과 값을 되돌려줌

 

예시

판매된 도서에 대한 이익을 계산하는 함수(fnc_Interest) 

CREATE OR REPLACE FUNCTION fnc_Interest(
 price NUMBER) RETURN INT
 IS
 myInterest NUMBER;
 BEGIN
 -- 가격이 30,000원 이상이면 10%, 30,000원 미만이면 5%
 IF Price >= 30000 THEN myInterest := Price * 0.1;
 ELSE myInterest := Price * 0.05;
 END IF;
 RETURN myInterest;
END;

--  Orders 테이블에서 각 주문에 대한 이익을 출력 
 SELECT custid, orderid, saleprice, fnc_Interest(saleprice) interest
 FROM Orders;

 

 

---

프로시저, 트리거, 사용자 정의 함수의 공통점과 차이점