SQLP 2-2-5 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 생성 ```sql 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 생성
```sql
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;
/
  • 데이터 입력 및 결과 확인 ```sql 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; ```

이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)