5장 데이터베이스 프로그래밍 - 1

• DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정
• 일반 프로그래밍과는 데이터베이스 언어인 SQL을 포함한다는 점이 다름.

데이터 베이스 프로그래밍 방법

1. SQL 전용 언어를 사용하는 방법
   • SQL 자체의 기능을 확장하여 변수, 제어, 입출력 등의 기능을 추가한 새로운 언어를 사용하는 방법.
   • Oracle은 PL/SQL 언어를 사용하며, SQL Server는 T-SQL이라는 언어를 사용함.
2. 일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
   • 자바, C, C++ 등 일반 프로그래밍 언어에 SQL 삽입하여 사용하는 방법.
   • 일반 프로그래밍 언어로 작성된 응용 프로그램에서 데이터베이스에 저장된 데이터를 관리, 검색함.
   • 삽입된 SQL문은 DBMS의 컴파일러가 처리함.
3. 웹 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
   • 호스트 언어가 JSP, ASP, PHP 등 웹 스크립트 언어인 경우다.
4. 4GL(4th Generation Language)
   • 데이터베이스 관리 기능과 비주얼 프로그래밍 기능을 갖춘 ‘GUI 기반 소프트웨어 개발 도구’를 사용하여 프로그래밍하는 방법. Delphi, Power Builder, Visual Basic 등이 있음.

PL/SQL

• Procedural Languages / Structured Query Language의 줄일말로 데이터베이스 응요 프로그램을 작성하는데 사용된다.
• SQL 표준이 아니라 Oracle 에서만 제공한다.
• Procedure 는 Return X , Function 은 Return 0인 차이가 있다.
• 파이썬이나 자바에서 Database table을 가져다가 함수를 작성할 수 있으나 PL/SQL을 사용하는 것이 속도면에서 더 빠르다.
• Begin과 End 사이에 우리가 원하는 코드를 작성하면 된다.

삽입 작업을 하는 프로시저

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

EX) 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;
-- 프로시저 InsertBook을 테스트 하는 부분 
EXEC InsertBook(13,'스포츠과학', '마당과학서적',25000);
SELECT * FROM Book;

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

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

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

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; 
-- BookInsertOrUpdate 프로시저를 실행하여 테스트하는 부분 
   EXEC BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 25000);
   SELECT * FROM Book; /* 15번 튜플 삽입 결과 확인 */
   /* BookInsertOrUpdate 프로시저를 실행하여 테스트하는 부분 */
   EXEC BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 20000);
   SELECT * FROM Book; /* 15번 튜플 가격 변경 함 */

결과를 반환하는 프로시저

EX) 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);
  END;

커서를 사용하는 프로시저

• SELECT명령은 보통 여러 줄을 가져오기를 원한다. 하지만 원래는 tuple 하나에 대해서만 리턴해야 하는데그 경우 트래픽이 너무 심하게 발생하여 조건에 만족하는 여러 줄을 한번에 묶어서 보내게 된다.
• 묶어서 가져온 데이터를 한 줄씩 가져오기 위해 도입된 개념이 커서이다. 커서는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로 가리키는데 사용한다.
• 커서는 한번 지나가고 나면 다시 뒤로 돌아갈 수 없는 형태이다. 앞뒤로 구현이 가능은 했으나 자원낭비가 심하기 때문에 단방향 실행으로 만들었다.

커서 관련 키워드

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

트리거

• 데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 따라서 실행되는 프로시저를 말함.
• 데이터 변경의 경우 문제가 발생할 수 있기 때문에 보통 관리자에게 승인을받고 변경되도록 절차를 나눈다.

사용자 정의 함수

• 프로시저와 다르게 return값이 존재한다. 또한 호출시에 EXEC로 호출하지 않고, return 방식에 따라 바로 사용 가능하다.

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

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;

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