데이터베이스 프로그래밍(PL/SQL)

1. PL/SQL

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

2. 프로시저

리턴 값이 없음, 리턴 값이 있으면 함수

2.(1) 삽입 작업 프로시저

• 프로시저로 데이터를 삽입 작업을 하면 좀 더 복잡한 조건의 삽입 작업을 인자 값만 바꾸어 수행할 수도 있고, 저장해두었다가 필요할 때마다 호출하여 사용할 수도 있음
• 유효성 검사도 됨

-- 프로시저

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;

-- 프로시저(실행부)
EXEC INSERTBOOK(13, '스포츠과학', '마당과학서적', 25000);

-- 확인작업
SELECT * FROM BOOK;

2.(2) 제어문을 사용하는 프로시저

• PL/SQL의 제어문은 어떤 조건에서 어떤 코드가 실행되어야 하는지를 제어하기 위한 문법으로, 절차적 언어의 구성요소를 포함함

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

• 예제

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;

• IN, OUT, INOUT 매개변수
  • IN 변수는 프로시저 내에서 값을 읽기만 할 수 있으며, 프로시저를 호출할 때 전달된 값을 읽어들입니다. 이 변수는 프로시저 내부에서 변경될 수 없습니다. 자바의 final처럼 수정할 수 없는 변수입니다.
  • OUT 변수는 프로시저 내에서 값을 쓰기만 할 수 있으며, 프로시저를 호출할 때 전달된 변수에 값을 할당합니다. 이 변수는 프로시저 내부에서 읽을 수 없습니다.
  • IN OUT 변수는 프로시저 내에서 값을 읽고 쓸 수 있으며, 프로시저를 호출할 때 전달된 변수에 값을 할당하고 읽어들입니다.

2.(3) 결과를 반환하는 프로시저

• 예제

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

 

SET SERVEROUTPUT ON;  
DECLARE  
AverageVal NUMBER;  
BEGIN  
AveragePrice(AverageVal);  
DBMS\_OUTPUT.PUT\_LINE('책값 평균: '|| AverageVal);  
END;

2.(4) 커서를 사용하는 프로시저

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

• 예제

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;

트리거

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

사용자 정의함수

• 사용자 정의 함수는 수학의 함수와 마찬가지로 입력된 값을 가공하여 결과 값을 되돌려줌.
• 판매된 도서에 대한 이익을 계산하는 함수(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;

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