[Database] PL/SQL -2-

지구본욱·2025년 4월 26일

오늘은 PL/SQL에서 프로시저, 트리거, 사용자 정의함수의 개념을 살펴보고 어떻게 사용하는지 예시와 함께 알아보고자 한다. (생각보다 재밌다 매우 ㅎㅎ😅)

구분	프로시저	트리거	사용자 정의 함수
정의방법	CREATE PROCEDURE	CREATE TRIGGER	CREATE FUNCTION
호출방법	EXCE 문으로 직접 호출	INSERT,DELETE,UPDATE문이 실행될 때 자동으로 실행	SELECT 문으로 호출
기능의 차이	SQL 문으로 할 수 없는 복잡한 로직 수행	기본값 제공, 데이터 제약 준수, 참조 무결성 작업 등을 수행	속성값을 가공하여 반환, SQL 문에서 직접 사용

프로시저

프로시저란?

PL/SQL은 프로그램 로직을 프로시저로 구현하여 객체 형태로 사용한다.
프로시저는 함수와 비슷한 개념으로 작업 순서가 정해진 독립된 프로그램의 수행단위를 말한다.

프로시저 정의 방법

CREATE [OR REPLACE] PROCEDURE - BEGIN -END 형식

선언부에서는 변수와 매개변수를 선언하고 실행부에서는 로직을 구현
매개변수는 프로시저가 호출될 때, 그 프로시저에 전달되는 값
변수는 프로시저나 트리거 내에서만 사용되는 값
주석은 /* */ 으로하거나 -- 로 표현

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;
/

/* 프로시저InsertBook을테스트하는부분*/
EXEC InsertBook(13, '스포츠과학','과학서적',25000);
SELECT * FROM Book;

매개 변수 선언 부분에서 IN은 입력 값이고 OUT은 출력 값이다. (아무것도 작성하지 않으면 기본으로 IN이다.)

프로시저 제어문

제어문을 사용하는 프로시저

CREATE OR REPLACE PROCEDURE BookInsertOrUpdate( -- 프로시저 선언부
    myBookID NUMBER,
    myBookName VARCHAR2,
    myPublisher VARCHAR2,
 	myPrice INT) -- 매개변수
AS
	mycount NUMBER; -- 지역변수
BEGIN
  SELECT count(*) INTO mycount FROM Book
  WHERE bookname LIKE myBookName; -- 책이 존재하는지 확인 (mycount에 저장)
	IF mycount !=0 THEN -- 책이 이미 존재했다면 정보 업데이트
		UPDATE Book SET price=myPrice
		WHERE bookname LIKE myBookName;
	ELSE -- 책이 존재하지 않는다면 새로 추가
		INSERT INTO book(bookid, bookname, publisher, price)
		VALUES(myBookID, myBookName, myPublisher , myPrice);
	END IF;
END;
/

/* BookInsertOrUpdate 프로시저를실행하여테스트하는부분*/
EXEC BookInsertOrUpdate(15, '스포츠즐거움', '과학서적', 25000);
SELECT * FROM book;
/* BookInsertOrUpdate 프로시저를실행하여테스트하는부분*/
EXEC BookInsertOrUpdate(15, '스포츠즐거움', '과학서적', 20000);
SELECT * FROM book;

결과를 반환하는 프로시저

CREATE OR REPLACE PROCEDURE AveragePrice(AverageVal OUT NUMBER) -- 프로시저 선언(출력 매개변수 선언)
AS
BEGIN
	SELECT AVG(price) INTO AverageVal FROM Book WHERE price IS NOT NULL; -- 출력 변수에 평균 가격 저장
END;
/

/* 프로시저AveragePrice를테스트하는부분*/
SET SERVEROUTPUT ON;
DECLARE
	AverageVal NUMBER; -- 반환값을 저장할 변수
BEGIN
	AveragePrice(AverageVal); -- 매개변수에 해당 변수를 넣으면 값 받아올 수 있음
	DBMS_OUTPUT.PUT_LINE('책값평균: ' ||AverageVal); -- AverageVal 출력부분
END;

커서를 사용하는 프로시저

커서는 실행 결과 테이블을 한 번에 한 행 씩 처리하기 위하여 테이블 행을 순서대로 가르키는데 사용

CREATE OR REPLACE PROCEDURE Interest
AS
	myInterest NUMERIC;
	Price NUMERIC;
	CURSOR InterestCursor IS SELECT saleprice FROM Orders; --Orders 테이블에서 salepreice 값들을 커서로 선언
BEGIN
	myInterest :=0.0;
	OPEN InterestCursor; -- 커서의 사용을 시작 
	LOOP
		FETCH InterestCursor INTO Price; -- 현재 튜블 값을 Price로 불러온 뒤 아래 로직 실행
		EXIT WHEN InterestCursor%NOTFOUND; -- 더이상 튜블 값이 존재하지 않으면 LOOP 탈출
		IF Price >= 30000 THEN
			myInterest := myInterest + Price * 0.1;
		ELSE
			myInterest := myInterest + Price * 0.05;
		END IF;
	END LOOP;
	CLOSE InterestCursor; -- 커서 사용을 종료
	DBMS_OUTPUT.PUT_LINE('전체이익금액= '||myInterest);
END;
/

/* Interest 프로시저를실행하여판매된도서에대한이익금을계산*/
SET SERVEROUTPUT ON;
EXEC Interest;

트리거

트리거란?

트리거는 데이터의 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 따라서 실행되는 프로시저를 말한다.
사용자가 직접 호출하는 것이 아니라, 자동으로 수행되는 것이다.
오라클 DBMS의 경우에는 기본적으로 실행 전과 실행 후 트리거를 지원한다.

CREATE TABLE Book_log ( -- 로그 테이블 생성
	bookid_l NUMBER,
	bookname_l VARCHAR(20),
	publisher_l VARCHAR(40),
	price_l NUMBER);
    
CREATE OR REPLACE TRIGGER AfterInsertBook -- 프로시저 선언
AFTER INSERT ON Book FOR EACH ROW -- Book 테이블에 ROW가 추가될 경우 실행됨
DECLARE
	average NUMBER;
BEGIN
	INSERT INTO Book_log -- 삽입된 튜플 값을 Book_log 테이블에 INSERT
	VALUES(:new.bookid, :new.bookname, :new.publisher , :new.price);
	DBMS_OUTPUT.PUT_LINE('삽입투플을Book_log 테이블에백업..');
END;
/

/* 삽입한내용을기록하는트리거확인*/
INSERT INTO Book VALUES(14, '스포츠과학1','이상미디어',25000);
SELECT * FROM Book WHERE BOOKID=14;
SELECT * FROM Book_log WHERE BOOKID_L=14;

사용자 정의 함수

사용자 정의 함수란?

수학의 함수와 마찬가지로 입력된 값을 가공하여 결과 값을 되돌려 준다.
프로시저와 비슷해보이지만 프로시저는 EXEC 명령에 따라 실행되는 독립된 프로그램이고,
사용자 정의함수는 SELECT 문이나 프로시저 내에서 호출된다.

CREATE OR REPLACE FUNCTION fnc_Interest(Price NUMBER) RETURN NUMBER -- 사용자 함수 정의
IS
	myInterest NUMBER;
BEGIN
	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; -- SELECT 문 안에서 사용자 함수 사용