PL/SQL

데이터베이스 응용 프로그램을 작성하는 데 사용하는 오라클의 SQL 전용 언어

SQL문에 변수, 제어, 입출력 등의 기능을 추가하여 SQL만으로 처리하기 어려운 문제를 해결한다.

PL/SQL로 개발한 프로그램은 프로시저로 저장 할 수 있으며 필요할 때마다 호출하여 사용할 수 있다.

PL/SQL은 선언부와 실행부로 구성되며, 선언부에서는 변수와 매개변수를 선언하고, 실행부에서는 프로그램 로직을 구현한다.

 

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

 

프로시저

프로시저는 일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념이다.

 

삽입 작업을 하는 프로시저

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;

 

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

제어문은 어떤 조건에서 어떤 코드가 실행되어야 하는지를 제어하기 위한 문법이다.

PL/SQL의 제어문

구문	의미	문법
BEGIN-END	PL/SQL 문을 블록화시킴 중첩 가능	BEGIN SQL 문  END
IF-ELSE	조건의 검사 결과에 따라 문장을 선택적으로 수행	IF <조건> SQL 문 END IF;
FOR	counter 값이 범위 내에 있을 경우 FOR 문의 블록을 실행	FOR counter IN 범위 {SQL 문} END LOOP
WHILE	조건이 참일 경우 WHILE 문의 블록을 실행	WHILE 조건 SQL 문 END LOOP
RETURN	프로시저를 종료 상태값을 정수로 반환 가능	RETURN 정수;

동일한 도서가 있는지 점검한 후 삽입하는 프로시저

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

 

결과를 반환하는 프로시저

저장 프로시저는 계산된 결과를 반환할 수도 있다.

결과를 반환하는 방법은 인자의 타입을 OUT으로 설정한 후, 이 인자 변수에 값을 주면 된다.

Book 테이블에 저장된 도서의 평균 가격을 반환하는 프로시저

CREATE OR REPLACE PROCEDURE AveragePrice(AverageVal OUT NUMBER) AS BEGIN         SELECT AVG(price) INTO AverageVal FROM Book WHERE price IS NOT NULL; END;

 

커서를 사용하는 프로시저

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

커서와 관련된 키워드

키워드	역할
CUSOR <cursor 이름> IS <커서 정의>	커서를 생성
OPEN <cursor 이름>	커서 사용을 시작
FETCH <cursor 이름> INTO <변수>	행 데이터를 가져옴
CLOSE <cursor 이름>	커서 사용을 끝냄

Orders 테이블의 판매 도서에 대한 이익을 계산하는 프로시저

CREATE OR REPLACE PROCEDURE Interest AS         myInterest NUMERIC;         Price NUMERIC;         CURSOR InterestCursor IS SELECT saleprice FROM Orders; BEGIN         myInterest := 0.0;         OPEN InterestCursor;         LOOP                 FETCH InterestCursor INTO Price;                 EXIT WHEN InterestCursor%NOTFOUND;                 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;

 

트리거

데이터가 변경될 때 자동으로 같이 실행되는 프로시저를 말한다.

트리거는 데이터 변경의 실행 전, 대신해서, 실행 후 에 동작하며 오라클 DBMS에서는 실행 전후 트리거를 지원한다.

신규 도서를 삽입한 후 자동으로 Book_log 테이블에 삽입 내용을 기록하는 트리거

CREATE OR REPLACE TRIGGER AfterInsertBook AFTER INSERT ON Book FOR EACH ROW DECLARE BEGIN         INSERT INTO Book_log         VALUES(:new.bookid, :new.bookname, :new.publisher, :new.price); END;

 

 

사용자 정의 함수

사용자 정의 함수는 입력된 값을 가공하여 결과 값을 되돌려준다.

프로시저는 EXEC 명령에 의해 실행되는 독립된 프로그램이고, 사용자 정의 함수는 SQL 문이나 프로시저에 값을 제공하는 용도로 사용된다.

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

CREATE OR REPLACE FUNCTION fnc_Interest(price NUMBER) RETURN INT IS         myInterest NUMBER; BEGIN         IF Price >= 30000 THEN myInterest := Price * 0.1;         ELSE myInterest := Price * 0.05;         END IF;         RETURN myInterest; END;