SQL 프로시저, 트리거 설명 및 예제

※ 프로시저

 

- 정의 : CREATE PROCEDURE문 사용

- 선언부와 실행부(BEGIN-END)로 구성됨

- 선언부 : 변수와 매개변수 선언, 실행부 : 프로그램 로직 구현

- 매개변수 : 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값

- 변수 : 저장 프로시저나 트리거 내에서 사용하는 값

- 특정 작업을 수행하는 이름이 있는 PL/SQL BLOCK

 

- 매개변수를 받을 수 있고 반복적으로 사용할 수 있는 블록

-  보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL BLOCK을

데이터베이스에 저장하기 위해 생성 한다.

(출처 : 꿈꾸는 개발자, DBA 커뮤니티 구루비 (gurubee.net))

 

 

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

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

 

1) BEGIN-END : PL/SQL 블록화, 중첩가능

 

BEGIN

{ SQL문 }

END

 

2) IF-ELSE : 조건의 검사 결과에 따라 문장을 선택적 수행

 

IF <조건> SQL 문

[ ELSE SQL문 ]

END IF;

 

3) FOR : counter값이 범위 내에 있을 경우 FOR 문의 블록으로 실행

 

FOR counter IN <범위>

{SQL문}

END LOOP

 

4) WHILE : 조건이 참일 경우 WHILE문으로 블록 실행

 

WHILE <조건>

{SQL 문 | BREAK | CONTINUE}

END LOOP

 

5) RETURN : 프로시저를 종료, 상태값을 정수로 반환 가능

 

RETURN [ <정수> ]

 

[문제 1] - 프로시저 : 모든 결과 출력

 

[결과값]

EMPNO :1ENAME :한여진JOB :경찰DEPTNO :24
EMPNO :2ENAME :황시목JOB :검사DEPTNO :37
EMPNO :3ENAME :강원철JOB :검사DEPTNO :50
EMPNO :4ENAME :장건JOB :경찰DEPTNO :22
EMPNO :5ENAME :이연재JOB :CEODEPTNO :80

 

 

[문제 1 답]

CREATE TABLE JOB (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
DEPTNO NUMBER(2)
);
 
INSERT INTO JOB (EMPNO, ENAME, JOB, DEPTNO)
VALUES(001, '한여진', '경찰', 24);
 
INSERT INTO JOB (EMPNO, ENAME, JOB, DEPTNO)
VALUES(002, '황시목', '검사', 37);
 
INSERT INTO JOB (EMPNO, ENAME, JOB, DEPTNO)
VALUES(003, '강원철', '검사', 50);
 
INSERT INTO JOB (EMPNO, ENAME, JOB, DEPTNO)
VALUES(004, '장건', '경찰', 22);
 
INSERT INTO JOB (EMPNO, ENAME, JOB, DEPTNO)
VALUES(005, '이연재', 'CEO', 80);
 
SELECT * FROM JOB;
 
-- 익명 프로시저
DECLARE
   CURSOR CUR_JOB IS SELECT * FROM JOB; -- 커서 선언
   V_JOB JOB%ROWTYPE; --변수 선언, 모든 열의 데이터를 갖고 옴(행참조 : %ROWTYPE)
BEGIN
    OPEN CUR_JOB; --커서 열기
    LOOP --반복
    FETCH CUR_JOB INTO V_JOB; --CUR_JOB의 한 행을 V_JOB에 넣음
    IF CUR_JOB%NOTFOUND THEN
    EXIT;
    END IF; --조건문을 통해 루프 탈출
    DBMS_OUTPUT.PUT_LINE('EMPNO :' || V_JOB.EMPNO ||
                         'ENAME :' || V_JOB.ENAME ||
                         'JOB :' || V_JOB.JOB ||
                         'DEPTNO :' || V_JOB.DEPTNO);
    END LOOP;
    CLOSE CUR_JOB;
END;
 
SELECT * FROM JOB;

 

[문제 2] 프로시저 - 사용자 인증 프로시저

 

[결과값]

EXECUTE VALIDATE_JOB(1, 37); -> 부서번호 불일치

EXECUTE VALIDATE_JOB(4, 22); -> 인증 성공

 

 

[문제 2 답]

CREATE OR REPLACE PROCEDURE VALIDATE_JOB
(
    V_EMPNO JOB.EMPNO%TYPE,
    V_DEPTNO JOB.DEPTNO%TYPE
)
AS
    V_JOB JOB%ROWTYPE;
    CURSOR CUR_JOB IS SELECT * FROM JOB WHERE EMPNO = V_EMPNO;
BEGIN
    OPEN CUR_JOB;
    FETCH CUR_JOB INTO V_JOB;
    IF CUR_JOB%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('회원번호 불일치');
    ELSIF V_DEPTNO <> V_JOB.DEPTNO THEN
        DBMS_OUTPUT.PUT_LINE('부서번호 불일치');
    ELSE
        DBMS_OUTPUT.PUT_LINE('인증 성공');
    END IF;
CLOSE CUR_JOB;
END;
 
EXECUTE VALIDATE_JOB(1, 37);
EXECUTE VALIDATE_JOB(4, 22);

 

[문제 3] 프로시저 - 삽입 작업 : 복잡한 조건의 삽입 작업을 인자 값만 바꾸어 수행

(필요할 때마다 호출하여 재사용 가능)

 

[결과값]

 

 

[문제 3 답]

--삽입 작업을 하는 프로시저
CREATE TABLE CAFE(
CAFEID NUMBER(20), 
CAFENAME VARCHAR2(20), 
CORPORATION VARCHAR2(20),
PRICE NUMBER(10)
);
 
CREATE OR REPLACE PROCEDURE INSERTCAFE(
    mCAFEID IN NUMBER,
    mCAFENAME IN VARCHAR2,
    mCORPORATION IN VARCHAR2,
    mPRICE IN NUMBER)
AS 
BEGIN 
    INSERT INTO CAFE(CAFEID, CAFENAME, CORPORATION, PRICE)
    VALUES(mCAFEID, mCAFENAME, mCORPORATION, mPRICE);
END;
 
EXEC INSERTCAFE(14, '스타벅스', '신세계', 7000);
EXEC INSERTCAFE(20, '이디야', '이디야', 4500);
EXEC INSERTCAFE(03, '빽다방', '백종원', 3000);
SELECT * FROM CAFE;

 

[문제 4] 프로지서 - 제어문 사용 프로시저

[결과값]

싼커피 가격 1500 -> 1000원으로 변경

 

 

[문제 4 답] 

-- 동일한 카페명이 있는지 점검 후 삽입하는 프로시저
CREATE OR REPLACE PROCEDURE CAFEINSERTORUPDATE(
mCAFEID IN NUMBER,
    mCAFENAME IN VARCHAR2,
    mCORPORATION IN VARCHAR2,
    mPRICE IN NUMBER)
AS 
    mycount NUMBER;
BEGIN
    SELECT COUNT(*) INTO mycount FROM CAFE
    WHERE CAFENAME LIKE mCAFENAME;
    IF mycount!=0 THEN
        UPDATE CAFE SET PRICE = mPRICE
        WHERE CAFENAME LIKE mCAFENAME;
    ELSE 
        INSERT INTO CAFE(CAFEID, CAFENAME, CORPORATION, PRICE)
        VALUES(mCAFEID, mCAFENAME, mCORPORATION, mPRICE);
    END IF;
END;
 
EXEC CAFEINSERTORUPDATE(50, '폴바셋', '매일유업', 8000);
EXEC CAFEINSERTORUPDATE(14, '싼커피', '편의점', 1500);
EXEC CAFEINSERTORUPDATE(14, '싼커피', '편의점', 1000);
SELECT * FROM CAFE;

 

[문제 5] 프로시저 - 평균을 계산하는 프로시저

결과값 

카페값 평균 :4700

 

 

[문제 5 답]

--결과를 반환하는 프로시저
CREATE OR REPLACE PROCEDURE AVERAGEPRICE(AVERAGEVAL OUT NUMBER)
AS
BEGIN
    SELECT AVG(PRICE) INTO AVERAGEVAL
    FROM CAFE
    WHERE PRICE IS NOT NULL;
END;
 
DECLARE
    AVERAGEVAL NUMBER;
BEGIN
    AVERAGEPRICE (AVERAGEVAL);
    DBMS_OUTPUT.PUT_LINE('카페값 평균 :' || ROUND(AVERAGEVAL));
END;

 

 

[문제 6] - 트리거

-- 트리거
CREATE TABLE MUSIC (
MUSICID NUMBER,
MUSICNAME VARCHAR2(40),
SINGER VARCHAR2(40),
PRICE NUMBER
);
 
INSERT INTO MUSIC (MUSICID, MUSICNAME, SINGER, PRICE)
VALUES(050, '화', '여자아이들', 4000);
 
INSERT INTO MUSIC (MUSICID, MUSICNAME, SINGER, PRICE)
VALUES(001, '좋은 날', '아이유', 5000);
 
SELECT * FROM MUSIC;
 
CREATE TABLE MUSIC_LOG(
MUSICID_L NUMBER,
MUSICNAME_L VARCHAR2(40),
SINGER_L VARCHAR2(40),
PRICE_L NUMBER
);
 
DESC MUSIC_LOG;
CREATE OR REPLACE TRIGGER AFTERINSERTMUSINC
AFTER INSERT ON MUSIC FOR EACH ROW
BEGIN
    INSERT INTO MUSIC_LOG
    VALUES(:NEW.MUSICID, :NEW.MUSICNAME, :NEW.SINGER, :NEW.PRICE);
    DBMS_OUTPUT.PUT_LINE('삽입 튜플은 MUSIC_LOG 테이블에서 백업');
END;