SQLP 요점정리 #08 : 2과목 SQL - 2장-5 SQL활용 : PL/SQL

2과목 SQL 기본 및 활용

• 20문제
• 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
• 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.

2장 SQL 활용 (#5)

PL/SQL (Procedural Language/SQL)

특징

• Block 구조로 되어 있어 각 기능별로 모듈화가 가능
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
• IF, LOOP 등의 절차형 언어를 사용하여 프로그램이 가능
• DBMS 정의 에러/사용자 정의 에러를 사용
• Oracle에 내장되어 있으므로 다른 Oracle Server로 옮길수 있음
• 여러 SQL문장을 Block으로 묶어서 한번에 서버로 보내기 때문에 통신량을 줄일수 있음
• Server에서 수행되므로 Application의 성능을 향상

구조

• DECLARE : BEGIN ~ END 사이에 변수, 인수에 대한 정의 및 데이터 타입 선언
• BEGIN : PL/SQL 시작
  • EXCEPTION : BEGIN ~ END 에서 실행되는 SQL문에서 발생한 에러를 처리 (선택항목)
• END : PL/SQL 종료

문법 (Syntax)

• 생성

CREATE [OR REPLACE] PROCEDURE 명칭
{
  argument1 [mode] data_type1,
  argument2 [mode] data_type2,
  ...
}
IS [AS]
...
BEGIN
...
EXCEPTION
...
END;
/

• mode : IN (입력) , OUT (출력) , INOUT(입출력)
• 삭제

DROP PROCEDURE 명칭;

예제

DEPT table에 새로운 부서를 입력하는데,부서번호가 이미 존재하지 않을때만 입력하고 존재할 경우에는 그냥 종료되는 PROCEDURE를 작성해보자.

CREATE OR REPLACE PROCEDURE INPUT_DEPT
(
  v_deptno      in    NUMBER,
  v_dname       in    VARCHAR2,
  v_loc         in    VARCHAR2,
  v_result      out   VARCHAR2
)
IS
is_exist    NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO is_exist FROM DEPT WHERE DEPTNO = v_deptno AND ROWNUM = 1;
    IF is_exist > 0 THEN
        v_result := '이미 등록된 부서번호';
    ELSE
        INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (v_deptno, v_dname, v_loc);
        COMMIT;
        v_result := '입력 완료';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
    v_result := '에러 발생';
END;
/

실행을 시켜 보겠다.

EXECUTE INPUT_DEPT(10,'DEV','SEOUL',:result)

EXECUTE INPUT_DEPT(60,'NEW_DEV','SANGAM',:result)

User Defined Function

• Function는 Procedure와는 다르게 반드시 1개의 값을 RETURN해야 한다. (SUM, NVL 같은 내장 함수를 생각하면 이해가 쉽다.)
• ABS 함수 구현 : 절대값을 RETURN

CREATE OR REPLACE FUNCTION ABS_NUM
(v_input IN NUMBER)
    RETURN NUMBER
IS
    v_return NUMBER := 0;
BEGIN
    if v_input >= 0 THEN
        v_return := v_input;
    ELSE
        v_return := v_input * -1;
    END IF;
    RETURN v_return;
END;
/

SELECT ABS_NUM(-20), ABS_NUM(3) FROM DUAL;

Trigger

• 특정 Table에 DML (INSERT, UPDATE, DELETE)문이 수행했을 때 자동으로 동작 (사용자 실행이 아닌 DBMS가 실행)
• DBMS에서 실행을 하는 것이므로 DB에 바로 적용됨. (COMMIT, ROLLBACK이 불가)
• Syntax (문법)

CREATE [OR REPLACE] TRIGGER trigger_name  -- TRIGGER 명칭
BEFORE | AFTER                            -- 아래 작업이 일어나기 전(BEFORE) 또는 후(AFTER)에 실행
INSERT | UPDATE | DELETE ON table_name    -- 해당 table에 해당 작업이 일어났을 경우
[ FOR EACH ROW ]                          -- 행 트리거 (행 데이터 제어가능), 없으면 문장 트리거 (각 행 데이터 제어 불가)
[ WHEN (condition) ]                      -- 해당 조건에 맞을때에만 실행
PL/SQL block...

• 행의 데이터는 :NEW.column_name , :OLD.column_name 으로 제어가 가능하다.
  • INSERT : :NEW (입력되는 ROW)
  • UPDATE : :NEW (변경된 새로운 값), :OLD (변경 전의 값)
  • DELTE : :OLD (삭제된 값)
• 예

쇼핑몰의 경우

• 하루 수만건의 주문이 들어옴
• 직원들은 일자별, 상품별 총 판매수량 및 주문실적으로 온라인으로 조회함

이럴 경우 매번 수만건의 데이터로 집계를 낼려면 서버 부하가 크다.
Trigger를 이용하여 주문이 들어올 때마다 집계를 계산하여 별도 Table에 보관하면 된다.

• Table 생성

CREATE TABLE ORDER_LIST
(
    ORDER_DATE  CHAR(8)         NOT NULL,
    PRODUCT     VARCHAR2(10)    NOT NULL,
    QTY         NUMBER          NOT NULL,
    AMOUNT      NUMBER          NOT NULL
);

CREATE TABLE SALES_PER_DATE
(
    SALE_DATE   CHAR(8)         NOT NULL,
    PRODUCT     VARCHAR2(10)    NOT NULL,
    QTY         NUMBER          NOT NULL,
    AMOUNT      NUMBER          NOT NULL
);

• Trigger 생성

CREATE OR REPLACE TRIGGER SUM_SALES
    AFTER   INSERT                  
    ON      ORDER_LIST              -- ORDER_LIST table에 INSERT 작업 후에
    FOR     EACH ROW                -- 행 트리거로 실행
DECLARE
    o_date  ORDER_LIST.ORDER_DATE%TYPE;
    o_prod  ORDER_LIST.PRODUCT%TYPE;
BEGIN
    o_date := :NEW.ORDER_DATE;
    o_prod := :NEW.PRODUCT;

    UPDATE SALES_PER_DATE
       SET QTY    = QTY    + :NEW.QTY,
           AMOUNT = AMOUNT + :NEW.AMOUNT
     WHERE SALE_DATE = o_date
       AND PRODUCT    = o_prod;

    IF SQL%NOTFOUND THEN
        INSERT INTO SALES_PER_DATE
        VALUES (o_date, o_prod, :NEW.QTY, :NEW.AMOUNT);
    END IF;
END;
/

• 데이터 입력 및 결과 확인

INSERT INTO ORDER_LIST VALUES ('20160127','Orange', 2, 10000);
INSERT INTO ORDER_LIST VALUES ('20160127','Orange', 5, 20000);
INSERT INTO ORDER_LIST VALUES ('20160127','PetaSQL', 1, 100);
INSERT INTO ORDER_LIST VALUES ('20160127','ChakraMAX', 1, 1000);

SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;