본문 바로가기
Database/Oracle

[국비] SQL 내용정리 Day07

by tpleehan 2021. 12. 14.

PL/SQL

  • 오라클에서 제공하는 SQL 프로그래밍 기능이다.
  • 일반적으로 프로그래밍과 차이가 있지만, 오라클 내부에서 적절한 처리를 위해서 적용해 줄 수 있는 절차지향적 코드 작성 방식이다.
  • 쿼리문의 집합으로 어떠한 동작을 일괄처리 하기 위한 용도로 사용한다.
SET SERVEROUTPUT ON; -- 출력문 활성화
DECLARE -- 변수를 선언하는 구간(선언부)
    emp_num NUMBER;
BEGIN -- 대입/출력문/기타등등 코드를 실행하는 시작구간(실행부)
    emp_num := 10;
    dbms_output.put_line(emp_num); -- 출력문
    dbms_output.put_line('hello PL/SQL');
-- [EXCEPTION] 옵션
END; -- PL/SQL이 끝나는 구간(종료)

-- 연산자
-- 일반 SQL문의 모든 연산자의 사용이 가능하고
-- **는 제곱을 의미한다.
DECLARE
  A NUMBER := 2**2*3**2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(A));
END;

DML문

DDL문은 사용이 불가능하다.

SELECT절 아래에 INTO절을 사용해 변수에 할당할 수 있다.

DECLARE
    v_emp_name VARCHAR2(50); -- 사원명 변수 (문자열 변수는 길이제약이 필수 / 기본값이 없기 때문이다.) 
    v_dep_name VARCHAR2(50); -- 부서명 변수
BEGIN   
    SELECT
        e.first_name,
        d.department_name
    INTO
        v_emp_name, v_dep_name -- 변수에 대입을 의미
    FROM employees e 
    LEFT OUTER JOIN departments D
    ON e.department_id = d.department_id
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_name || '-' || v_dep_name);
END;

PL/SQL Quiz01

문제1.
구구단 중 3단을 출력하는 익명 블록 만들기 (출력문 9개를 복사해서 사용)
BEGIN
    DBMS_OUTPUT.PUT_LINE('3 * 1 = ' || 3*1);
    DBMS_OUTPUT.PUT_LINE('3 * 2 = ' || 3*2);
    DBMS_OUTPUT.PUT_LINE('3 * 3 = ' || 3*3);
    DBMS_OUTPUT.PUT_LINE('3 * 4 = ' || 3*4);
    DBMS_OUTPUT.PUT_LINE('3 * 5 = ' || 3*5);
    DBMS_OUTPUT.PUT_LINE('3 * 6 = ' || 3*6);
    DBMS_OUTPUT.PUT_LINE('3 * 7 = ' || 3*7);
    DBMS_OUTPUT.PUT_LINE('3 * 8 = ' || 3*8);
    DBMS_OUTPUT.PUT_LINE('3 * 9 = ' || 3*9);
END;​
문제2.
사원 테이블에서 201번 사원의 이름과 이메일주소를 출력하는 익명 블록 만들기
DECLARE
    v_emp_name employees.first_name%TYPE;
    v_emp_email employees.email%TYPE;
BEGIN
    SELECT
        first_name, email
    INTO 
        v_emp_name, v_emp_email
    FROM employees
    WHERE employee_id = 201;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ': ' || v_emp_email);
END;​
문제3.
사원 테이블에서 사원번호가 제일 큰 사원을 찾아낸 뒤,
해당 번호 + 1번으로 아래의 사원을 emps에 
employee_id, last_name, email, hire_date, job_id를 신규 입력하는 익명 블록 만들기
SELECT절 이후에 INSERT문 사용이 가능하다.
/*
<사원명>: steven
<이메일>: stevenjobs
<입사일자>: 오늘날짜
<JOB_ID>: CEO
*/
DROP TABLE emps;
CREATE TABLE emps AS (SELECT * FROM employees WHERE 1 = 2);

DECLARE
    v_max_empno employees.employee_id%TYPE;
BEGIN
    SELECT MAX(employee_id)
    INTO v_max_empno
    FROM employees;
    
    INSERT INTO emps
        (employee_id, last_name, email, hire_date, job_id)
    VALUES
        (v_max_empno + 1, 'steven', 'stevenjobs', sysdate, 'CEO');
END;

SELECT * FROM emps;​

IF문

DECLARE
    v_num1 NUMBER := 10;
    v_num2 NUMBER := 5;
BEGIN
    IF
        v_num1 >= v_num2
    THEN
        DBMS_OUTPUT.PUT_LINE(v_num1 || '이(가) 큰 수');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_num2 || '이(가) 큰 수');
    END IF;
END;

ELSIF문

DECLARE
    v_salary NUMBER := 0;
    v_department_id NUMBER := 0;
BEGIN
    v_department_id := ROUND(DBMS_RANDOM.VALUE(10, 120), -1);
    
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE department_id = v_department_id
    AND ROWNUM = 1; -- 첫째 값만 구해서 변수에 저장
    
    DBMS_OUTPUT.PUT_LINE(v_salary);
    
    IF v_salary <= 5000 THEN
        DBMS_OUTPUT.PUT_LINE('낮음');
    ELSIF v_salary <= 9000 THEN
        DBMS_OUTPUT.PUT_LINE('중간');
    ELSE
        DBMS_OUTPUT.PUT_LINE('높음');
    END IF;
END;

CASE 문

DECLARE
    v_salary NUMBER := 0;
    v_department_id NUMBER := 0;
BEGIN
    v_department_id := ROUND(DBMS_RANDOM.VALUE(10, 120), -1);
    
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE department_id = v_department_id
    AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(v_salary);
    
    CASE
        WHEN v_salary <= 5000 THEN
            DBMS_OUTPUT.PUT_LINE('낮음');
        WHEN v_salary <= 9000 THEN
            DBMS_OUTPUT.PUT_LINE('중간');
        ELSE
            DBMS_OUTPUT.PUT_LINE('높음');
    END CASE;
END;

중첩 IF문

DECLARE
    v_salary NUMBER := 0;
    v_department_id NUMBER := 0;
    v_commission NUMBER := 0;
BEGIN
    v_department_id := ROUND(DBMS_RANDOM.VALUE(10, 120), -1);
    
    SELECT salary, commission_pct
    INTO v_salary, v_commission
    FROM employees
    WHERE department_id = v_department_id
    AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(v_salary);
    
    IF v_commission > 0 THEN
        IF v_commission > 0.15 THEN
            DBMS_OUTPUT.PUT_LINE(v_salary * v_commission);
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_salary);
    END IF;    
END;

WHILE 문

DECLARE
    v_num NUMBER := 3;
    v_count NUMBER := 1;
BEGIN
    WHILE v_count <= 10
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_num);
        v_count := v_count + 1;
    END LOOP;
END;

탈출문

DECLARE
    v_num NUMBER := 3;
    v_count NUMBER := 1;
BEGIN
    WHILE v_count <= 10
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_num);
        EXIT WHEN v_count = 5;
        v_count := v_count + 1;
    END LOOP;
END;

FOR 문

DECLARE
    v_num NUMBER := 3;
BEGIN    
    FOR i IN 1..10 -- .을 두개 작성해서 범위를 표현
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_num || ' x ' || i || ' = ' || v_num * i);
    END LOOP;
END;

CONTINUE문

DECLARE
    v_num NUMBER := 3;
BEGIN    
    FOR i IN 1..10
    LOOP
        CONTINUE WHEN i = 5;
        DBMS_OUTPUT.PUT_LINE(v_num || ' x ' || i || ' = ' || v_num * i);
    END LOOP;
END;

PL/SQL Quiz02

문제1.
모든 구구단을 출력하는 익명 블록 만들기 (2 ~ 9단)
BEGIN    
    FOR i IN 2..9
    LOOP
        DBMS_OUTPUT.PUT_LINE('구구단: ' || i || '단');
        FOR j in 1..9
        LOOP 
            DBMS_OUTPUT.PUT_LINE(i || ' x ' || j || ' = ' || i * j);
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('----------------------');
    END LOOP;
END;​

문제2.
INSERT를 300번 실행하는 익명 블록 처리하기
board라는 이름의 테이블을 만들기 (bno writer title 컬럼 존재)
bno는 SEQUENCE로 올리고 writer와 title에 번호를 붙여서 INSERT 진행
ex) 1, test1, title1 -> 2, test2, title2 -> 3, test3, title3 ...
CREATE TABLE board(
    bno NUMBER PRIMARY KEY,
    writer VARCHAR2(30),
    title VARCHAR2(30)
);

CREATE SEQUENCE b_seq 
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 1000
    NOCYCLE
    NOCACHE;
    
DECLARE
    v_num NUMBER := 1;
BEGIN
    WHILE v_num <= 300
    LOOP
        INSERT INTO board
        VALUES (b_seq.NEXTVAL, 'test' || v_num, '제목제목' || v_num);
        v_num := v_num + 1;
    END LOOP;
    COMMIT;
END;

SELECT * FROM board
ORDER BY bno desc;​

Procedure

void 메서드와 유사하다.

  • 특정한 로직을 처리하고 결과값을 반환하지 않는 코드 덩어리 (쿼리)
  • PL/SQL에도 값을 전달 받아서 코드를 실행 후 리턴하는 함수
  • 프로시저를 통해서 값을 리턴하는 방법이 있다.
-- 매개값(인수) 없는 프로시저
CREATE PROCEDURE p_test
IS -- 선언부
    v_msg VARCHAR2(30) := 'hello procedure';    
BEGIN -- 실행부
    DBMS_OUTPUT.PUT_LINE(v_msg);
END; -- 끝

EXEC p_test; -- 프로시저 호출문

-- IN 입력값을 받는 파라미터
CREATE PROCEDURE my_new_job_proc
    (p_job_id IN jobs.job_id%TYPE,
     p_job_title IN jobs.job_title%TYPE,
     p_min_sal IN jobs.min_salary%TYPE,
     p_max_sal IN jobs.max_salary%TYPE
    )
IS
    
BEGIN
    INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
    VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);
    COMMIT;
END;

EXEC my_new_job_proc('JOB1', 'test job1', 1000, 5000);

  • job_id가 primary key인데 unique 제약조건을 위반하기 때문에 에러가 발생한다.
-- 이미 존재한다면 ALTER(수정), 없다면 CREATE(생성)
CREATE OR REPLACE PROCEDURE my_new_job_proc
    (p_job_id IN jobs.job_id%TYPE,
     p_job_title IN jobs.job_title%TYPE,
     p_min_sal IN jobs.min_salary%TYPE,
     p_max_sal IN jobs.max_salary%TYPE
    )
IS
    v_cnt NUMBER := 0;
BEGIN

    -- 동일한 job_id가 있는지 체크
    -- 이미 존재한다면 1, 존재하지 않는다면 0 -> v_cnt에 들어있다.
    SELECT COUNT(*)
    INTO v_cnt 
    FROM jobs
    WHERE job_id = p_job_id;
    
    IF v_cnt = 0 THEN  -- 없다면 INSERT
        INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
        VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);
    ELSE -- 있다면 UPDATE
        UPDATE jobs
        SET job_title = p_job_title,
            min_salary = p_min_sal,
            max_salary = p_max_sal
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;

EXEC my_new_job_proc('JOB1', 'test job1', 2000, 8000);

EXEC my_new_job_proc('JOB2', 'test job2'); -- 에러
-- 매개변수(인수)의 디폴트 값(기본값) 설정
CREATE OR REPLACE PROCEDURE my_new_job_proc
    (p_job_id IN jobs.job_id%TYPE,
     p_job_title IN jobs.job_title%TYPE,
     p_min_sal IN jobs.min_salary%TYPE := 0,
     p_max_sal IN jobs.max_salary%TYPE := 1000
    )
IS
    v_cnt NUMBER := 0;
BEGIN

    -- 동일한 job_id가 있는지 체크
    -- 이미 존재한다면 1, 존재하지 않는다면 0 -> v_cnt에 들어있다.
    SELECT COUNT(*)
    INTO v_cnt 
    FROM jobs
    WHERE job_id = p_job_id;
    
    IF v_cnt = 0 THEN  -- 없다면 INSERT
        INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
        VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);
    ELSE -- 있다면 UPDATE
        UPDATE jobs
        SET job_title = p_job_title,
            min_salary = p_min_sal,
            max_salary = p_max_sal
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;

EXEC my_new_job_proc('JOB2', 'test job2');

SELECT * FROM jobs
WHERE job_id = 'JOB2';

OUT, IN OUT 매개변수(인수) 사용

  • OUT 변수를 사용하면 프로시저 바깥으로 값을 보낸다.
  • OUT을 이용해서 보낸 값은 바깥 익명블록에서 실행해야 한다.
CREATE OR REPLACE PROCEDURE my_new_job_proc
    (p_job_id IN jobs.job_id%TYPE,
     p_job_title IN jobs.job_title%TYPE,
     p_min_sal IN jobs.min_salary%TYPE := 0,
     p_max_sal IN jobs.max_salary%TYPE := 1000,
     p_result OUT VARCHAR2 -- 바깥쪽에서 출력하기 위한 변수
    )
IS
    v_cnt NUMBER := 0;
    v_result VARCHAR(100) := '값이 없어서 insert 처리 되었습니다.';
BEGIN
    SELECT COUNT(*)
    INTO v_cnt 
    FROM jobs
    WHERE job_id = p_job_id;
    
    IF v_cnt = 0 THEN  -- 없다면 INSERT
        INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
        VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);
    ELSE -- 있다면 결과를 추출
        SELECT p_job_id || '의 최대 연봉: ' || max_salary || ', ' || '최소연봉: ' || min_salary
        INTO v_result -- 조회 결과를 대입
        FROM jobs
        WHERE job_id = p_job_id;
    END IF;
    
    -- OUT 매개변수에 결과를 할당
    p_result := v_result;
    
END;
DECLARE 
    str VARCHAR2(100);
BEGIN
    -- 프로시저를 부를 경우 out되는 값을 받기 위해 변수를 하나 더 전달해야 한다.
    my_new_job_proc('JOB1', 'test_job1', 2000, 8000, str);
    DBMS_OUTPUT.put_line(str);
    
    my_new_job_proc('CEO', 'test_CEO', 10000, 100000, str);
    DBMS_OUTPUT.put_line(str);
END;

-- IN, OUT을 동시에 처리
CREATE OR REPLACE PROCEDURE my_parameter_test_proc
    (
        -- 반환 불가
        p_var1 IN VARCHAR2,
        -- 반환 가능 하지만 OUT변수는 프로시저가 끝나기 전까지 값의 할당이 안되고 프로시저가 끝나야만 OUT 가능
        p_var2 OUT VARCHAR2,
        -- IN, OUT이 둘 다 가능하다.
        p_var3 IN OUT VARCHAR2
    )
IS

BEGIN
    DBMS_OUTPUT.PUT_LINE('p_var1: ' || p_var1);
    DBMS_OUTPUT.PUT_LINE('p_var2: ' || p_var2); -- 값이 전달이 안된다. (공백)
    DBMS_OUTPUT.PUT_LINE('p_var3: ' || p_var3); -- IN의 성질을 가지고 있다.

    -- p_var1 := '결과1'; 할당 불가 (프로시저 안에서 새로운 값을 할당 할 수 없다.)
    p_var2 := '결과2';
    p_var3 := '결과3';

    DBMS_OUTPUT.PUT_LINE('-------------------------------');
END;
DECLARE
    v_var1 VARCHAR2(10) := 'value1';
    v_var2 VARCHAR2(10) := 'value2';
    v_var3 VARCHAR2(10) := 'value3';
BEGIN
    my_parameter_test_proc(v_var1, v_var2, v_var3);
    
    DBMS_OUTPUT.PUT_LINE('v_var2: ' || v_var2);
    DBMS_OUTPUT.PUT_LINE('v_var3: ' || v_var3);
END;

--RETURN
CREATE OR REPLACE PROCEDURE my_new_job_proc
    (p_job_id IN jobs.job_id%TYPE,
     p_result OUT VARCHAR2)
IS
    v_cnt NUMBER := 0;
    v_result VARCHAR(100) := '값이 없어서 insert 처리 되었습니다.';
BEGIN
    SELECT COUNT(*)
    INTO v_cnt 
    FROM jobs
    WHERE job_id = p_job_id;
    
    IF v_cnt = 0 THEN 
        DBMS_OUTPUT.PUT_LINE(p_job_id || '는 테이블에 존재하지 않습니다.');
        RETURN; -- 프로시저 강제종료
    END IF;
    
    SELECT p_job_id || '의 최대 연봉: ' || max_salary || ', ' || '최소연봉: ' || min_salary
    INTO v_result -- 조회 결과를 대입
    FROM jobs
    WHERE job_id = p_job_id;  
    
    -- OUT 매개변수에 결과를 할당
    p_result := v_result;
END;
DECLARE
    str VARCHAR2(100);
BEGIN
    my_new_job_proc('CEO', str);
    DBMS_OUTPUT.PUT_LINE(str);
END;

-- 예외처리
DECLARE
    v_num NUMBER := 0;
BEGIN
    v_num := 10 / 0;

EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('0으로 나눌 수 없습니다.');
    DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('ERROR MSG: ' || SQLERRM);
END;

PL/SQL Quiz03

문제1.
프로시저명 guguProc
구구단을 입력받아 해당 단수를 출력하는 procedure을 생성
CREATE OR REPLACE PROCEDURE guguProc (
    p_dan IN NUMBER
)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p_dan || '단');

    FOR i IN 1..9
    LOOP
        DBMS_OUTPUT.PUT_LINE(p_dan || 'x' || i || ' = ' || p_dan * i);
        
    END LOOP;
END;

EXEC guguProc(7);​

문제2.
부서번호, 부서명, 직업 flag(I: insert, U:update, D:delete)을 매개변수로 받아
depts 테이블에 각각 INSERT, UPDATE, DELETE 하는 DEPTS_PROC란 이름의 프로시저를 만든다.
그리고 정상종료라면 commit, 예외라면 롤백 처리하도록 처리한다.
CREATE TABLE depts AS SELECT department_id, department_name FROM departments;
ALTER TABLE depts ADD CONSTRAINTS depts_pk PRIMARY KEY(department_id);

CREATE OR REPLACE PROCEDURE depts_proc (    
    p_department_id IN depts.department_id%TYPE,
    p_department_name IN depts.department_name%TYPE,
    p_flag IN VARCHAR2
)
IS
   
BEGIN
    
    IF p_flag = 'I' THEN
        INSERT INTO depts VALUES (p_department_id, p_department_name);
    ELSIF p_flag = 'U' THEN
        UPDATE depts
        SET department_name = p_department_name
        WHERE department_id = p_department_id;
    ELSIF p_flag = 'D' THEN
        DELETE depts
        WHERE department_id = p_department_id;
    END IF;
    COMMIT;
    
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다.');
    DBMS_OUTPUT.PUT_LINE('ERROR MSG: ' || SQLERRM);
    ROLLBACK;
END;

EXEC depts_proc(11, '마케팅부', 'D');

SELECT * FROM depts;​

문제3.
employee_id를 입력받아 employees에 존재하면, 근속년수를 out하는 프로시저를 작성, 없다면 exception처리
CREATE OR REPLACE PROCEDURE emp_hire_proc (    
    p_employee_id IN employees.employee_id%TYPE,
    p_year OUT NUMBER
)
IS
    v_hire_date employees.hire_date%TYPE;
BEGIN
    SELECT hire_date
    INTO v_hire_date
    FROM employees
    WHERE employee_id = p_employee_id;
    
    p_year := TRUNC((sysdate - v_hire_date) / 365);
    
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(p_employee_id || '은(는) 없는 데이터입니다.');
    DBMS_OUTPUT.PUT_LINE('ERROR MSG: ' || SQLERRM);
END;

DECLARE
    v_year NUMBER;
BEGIN
    emp_hire_proc(100, v_year);
    DBMS_OUTPUT.PUT_LINE(v_year);
END;​

문제4.
프로시저명 - new_emp_proc
employees 테이블의 복사 테이블 emps를 생성
employee_id, last_name, email, hire_date, job_id를 입력받아 존재하면 
이름, 이메일, 입사일, 직업을 update, 없다면 insert하는 merge문을 작성
CREATE OR REPLACE PROCEDURE new_emp_proc (
    p_employee_id IN emps.employee_id%TYPE,
    p_last_name IN emps.last_name%TYPE,
    p_email IN emps.email%TYPE,
    p_hire_date IN emps.hire_date%TYPE,
    p_job_id IN emps.job_id%TYPE
)

IS

BEGIN
    MERGE INTO emps a
    USING (SELECT p_employee_id AS employee_id FROM dual) b
    ON (a.employee_id = b.employee_id)
    WHEN MATCHED THEN
        UPDATE SET
            a.last_name = p_last_name,
            a.email = p_email,
            a.hire_date = p_hire_date,
            a.job_id = p_job_id
    WHEN NOT MATCHED THEN
        INSERT (a.employee_id, a.last_name, a.email, a.hire_date, a.job_id)
        VALUES (p_employee_id, p_last_name, p_email, p_hire_date, p_job_id);
END;

EXEC new_emp_proc(100, 'lee', 'abc', sysdate, 'test2');​

Trigger

  • 테이블에 부착한 형태로 INSERT, UPDATE, DELETE 작업이 수행될 때 특정 코드가 작동되도록 하는 구문이다.
  • trigger는 실행할 때 범위를 지정하고 F5버튼으로 부분 실행해야 하며, 그렇지 않으면 하나의 구문으로 인식되어 정상 동작하지 않는다.
CREATE TABLE tbl_test (
    id NUMBER(10),
    text VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER trg_test
    AFTER DELETE OR UPDATE -- 삭제 혹은 수정 이후에 동작
    ON tbl_test -- 부착할 테이블 선언
    FOR EACH ROW -- 각 행에 적용
BEGIN
    DBMS_OUTPUT.PUT_LINE('트리거 동작'); -- 실행되는 코드를 begin ~ end에 넣는다.
END;

INSERT INTO tbl_test VALUES(1, '홍길동');
UPDATE tbl_test SET text = '홍길동2' WHERE id = 1;
DELETE FROM tbl_test  WHERE id = 1;

trigger After & before

  • AFTER trigger - INSERT, UPDATE, DELETE 작업 이후에 동작하는 트리거를 의미한다.
  • BEFORE trigger - INSERT, UPDATE, DELETE 작업 이전에 동작하는 트리거를 의미한다.
  • INSTEAD OF trigger - INSERT, UPDATE, DELETE 작업 이전에 발생하는 트리거 이지만 VIEW만 부착할 수 있다.
  • :OLD = 참조 전 열의 값 (INSERT: 입력 전 자료, UPDATE: 수정 전 자료, DELETE: 삭제할 값)
  • :NEW = 참조 후 열의 값 (INSERT: 입력 할 자료, UPDATE: 수정 된 자료) :NEW는 DELETE가 없다.
  • 테이블에 UPDATE나 DELETE를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관해 놓는 형식으로 트리거를 사용할 수 있다.
CREATE TABLE tbl_user (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR2(20),
    address VARCHAR2(30)
);

CREATE TABLE tbl_user_backup (
    id VARCHAR2(20),
    name VARCHAR2(20),
    address VARCHAR2(30),
    update_date DATE DEFAULT sysdate, -- 변경 시간
    m_type VARCHAR2(10), -- 변경 타입
    m_user VARCHAR2(20) -- 변경한 사용자
);

AFTER trigger

CREATE OR REPLACE TRIGGER trg_user_backup
    AFTER UPDATE OR DELETE 
    ON tbl_user
    FOR EACH ROW
DECLARE -- 사용할 변수를 선언
    v_type VARCHAR2(10);
BEGIN
    IF UPDATING THEN -- UPDATING은 시스템 자체에서 상태에 대한 내용을 지원하는 빌트인 구문
        v_type := '수정';
    ELSIF DELETING THEN -- DELETING은 시스템 자체에서 상태에 대한 내용을 지원하는 빌트인 구문
        v_type := '삭제';
    END IF;
    
    -- 실행 구문 시작 (:OLD는 테이블 DELETE, UPDATE가 적용되기 전 기존데이터, 즉 변경 전 데이터)
    INSERT INTO tbl_user_backup
    VALUES (:OLD.id, :OLD.name, :OLD.address, sysdate, v_type, USER());
END;

UPDATE tbl_user SET address = '서울' WHERE id = 'test01';

SELECT * FROM tbl_user;
SELECT * FROM tbl_user_backup;

BEFORE trigger

CREATE OR REPLACE TRIGGER trg_user_insert
    BEFORE INSERT
    ON tbl_user
    FOR EACH ROW
BEGIN
    :NEW.name := SUBSTR(:NEW.name, 1, 1) || '**';
END;

INSERT INTO tbl_user VALUES('test04', '홍길동', '대구');

SELECT * FROM tbl_user;

트리거의 활용

INSERT -> 주문테이블 -> 주문테이블 INSERT 트리거 실행 (물품 테이블 update)

-- 주문 히스토리
CREATE TABLE order_history (
    history_no NUMBER(5) PRIMARY KEY,
    order_no NUMBER (5),
    product_no NUMBER(5),
    total NUMBER(10),
    price NUMBER(10)
);

-- 상품
CREATE TABLE product (
    product_no NUMBER(5) PRIMARY KEY,
    product_name VARCHAR2(20),
    total NUMBER(5),
    price NUMBER(5)
);

CREATE SEQUENCE order_history_seq NOCACHE;
CREATE SEQUENCE product_seq NOCACHE;

INSERT INTO product VALUES(product_seq.NEXTVAL, '피자', 100, 10000);
INSERT INTO product VALUES(product_seq.NEXTVAL, '치킨', 100, 15000);
INSERT INTO product VALUES(product_seq.NEXTVAL, '햄버거', 100, 5000);
-- 주문 히스토리에 데이터가 들어오면 실행
CREATE OR REPLACE TRIGGER trg_order_history
    AFTER INSERT
    ON order_history
    FOR EACH ROW 
DECLARE
    v_total NUMBER;
    v_product_no NUMBER;
BEGIN 
    DBMS_OUTPUT.PUT_LINE('트리거 실행');
    SELECT :NEW.total
    INTO v_total
    FROM dual;
    
    v_product_no := :NEW.product_no;
    
    UPDATE product SET total = total - v_total
    WHERE product_no = v_product_no;
    
END;

INSERT INTO order_history VALUES(order_history_seq.NEXTVAL, 200, 1, 5, 50000);
INSERT INTO order_history VALUES(order_history_seq.NEXTVAL, 200, 2, 1, 15000);
INSERT INTO order_history VALUES(order_history_seq.NEXTVAL, 200, 3, 10, 50000);

SELECT * FROM order_history;
SELECT * FROM product;

저작자표시

'Database > Oracle' 카테고리의 다른 글

[국비] SQL 내용정리 Day06  (0) 2021.12.13
[국비] SQL 내용정리 Day05  (0) 2021.12.11
[국비] SQL 내용정리 Day04  (0) 2021.12.09
[국비] SQL 내용정리 Day03  (0) 2021.12.08
[국비] SQL 내용정리 Day02  (0) 2021.12.07

관련글

댓글

티스토리툴바