SQL 활용 - 절차형 SQL

절차형 SQL

일반 개발 언어처럼 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 제공함.

(PL/SQL , T-SQL등)

1. 프로시져
CREATE OR REPLACE PROCEDURE p_DEPT_insert
( v_DEPTNO in number,
  v_dname in varchar2,
  v_loc in varchar2,
  v_result out varchar2)
IS
cnt number := 0;
BEGIN
    SELECT COUNT(*) INTO CNT
    FROM DEPT
    WHERE DEPTNO = v_DEPTNO
        AND ROWNUM = 1;
    if cnt > 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 := 'ERROR';
END;
/
// 새로운 부서를 등록하는 프로시져
1. DEPT 테이블에 들어갈 컬럼 값을 입력받고
2. 입력받은 부서가 이미 있는지 확인
3. 부서코드가 존재하면 이미등록된 부서번호라는 메시지 출력
4. 부서코드가 존재하지 않으면 필드 값으로 새로운 부서 레코드 입력
5. 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션 종료
6. 에러가 발생하면 모든 트랜잭션 취소 하고 ERROR 출력
 
사용
variable rslt varchar2(30);
EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt);
// 프로시져 정상 처리
print rslt;
// 이미등록된 부서입니다.
 
2. 유저 정의 함수
프로시져처럼 절차형 SQL을 로직과 함께 테이터베이스 내에 저장해놓은 명령문의 집합
SUM, SUBSTR같은 함수는 미리 만든 내장함수고 유저정의함수는 사용자가 별도로 제작한 함수다.
* 프로시져와 차이점은 유저정의함수는 반드시 return을 사용해서 하나의 값을 돌려줘야한다는 것.
 
CREATE OR REPLACE Function UTIL_ABS
(v_input in number)
    return NUMBER
IS
    v_return number := 0;
BEGIN
    if v_input < 0 then
        v_return := v_input * -1;
    else
        v_return := v_input;
    end if;
    return v_return;
END;
/
// 사용자 정의 함수를 통해 abs함수를 다시 만들어 보았음.
 
3. 트리거
트리거는 insert update delete 같은 DML문이 수행되었을 때 데이터베이스에서
자동으로동작하도록 작성된 프로그램.
CREATE OR REPLACE Trigger SUMMARY_SALES
    AFTER INSERT
    ON ORDER_LIST
    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;
/
 
 

Point

프로시저와 트리거의 차이점.

프로시저는 EXECUTE 명령어로 실행하지만

트리거는 생성 후에 자동으로 실행된다.

또한 프로시저는 BEGIN ~ END절에 commit, rollback같은 트랜잭션 실행이 가능하지만

트리거는 실행이 안된다.