DATABASE PROCEDURE & TRIGGER

sig6774·2022년 5월 14일
0

DATABASE

목록 보기
11/11

DATABASE

DATABASE PROCEDURE

PROCEDURE

  • PROCEDURE
    • void 메소드와 유사하고 특정 로직을 처리하고 결과값을 반환하지 않는 것

    • PL/SQL에도 값을 전달받아서 코드를 실행 후 리턴하는 함수가 존재

    • 매개값이 없는 프로시저

    • CREATE PROCEDURE ~ IS ~ BEGIN ~ END;

      CREATE PROCEDURE p_test IS -- 선언부 
          v_msg VARCHAR2(30) := 'HI 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
      ) -- 매개값 작성 (IN 키워드 사용)
       IS
      BEGIN
          INSERT INTO jobs VALUES (
              p_job_id,
              p_job_title,
              p_min_sal,
              p_max_sal
          );
      
          COMMIT;
      -- IN키워드를 사용해서 매개값으로 받은 것을 넣어줌 
      END;
      
      EXEC my_new_job_proc('job1', 'test', 1000, 5000);
      -- 진짜 함수처럼 프로시저 이름과 매개값들을 넣어주네 (이렇게 함수를 하나 만들어서 인수만 넣어주면 쉽겠다)

CREATE OR REPLACE

  • CREATE OR REPLACE
    • 프로시저가 있으면 수정해주고 없으면 만들어 줌

      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
      ) -- 매개값 작성 (IN 키워드 사용)
       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;
          -- 중복이 존재하면 1이 나옴 
      
          IF v_cnt = 0 THEN -- 없다면 INSERT 
              INSERT INTO jobs 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;
      -- IN키워드를 사용해서 매개값으로 받은 것을 넣어줌 
      END;
      
      EXEC my_new_job_proc('job2', 'test job2', 5000, 10000);
      EXEC my_new_job_proc('job1', 'test job3', 5000, 10);
      -- 값을 새로 만들어서 넣을 수 있고 이미 있다면 값을 갱신할 수도 있음
      
      SELECT
          *
      FROM
          jobs;
          
      -- 매개변수(인수)의 디폴트 값(기본값) 설정  매개변수로 값을 입력 받지 않아도 min,max_sal은 기본값으로 대입 
      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
      ) -- 매개값 작성 (IN 키워드 사용)
       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;
          -- 중복이 존재하면 1이 나옴 
      
          IF v_cnt = 0 THEN -- 없다면 INSERT 
              INSERT INTO jobs 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;
      -- IN키워드를 사용해서 매개값으로 받은 것을 넣어줌 
      END;
      
      EXEC my_new_job_proc('job2', 'test_job2');
      -- 기본값을 지정한 것이 있기 때문에 기본값으로 지정하고 싶으면 매개변수를 넣지 않아도 됨
      
      SELECT
          *
      FROM
          jobs;

IN, OUT, IN OUT

  • IN, OUT, IN OUT
    • IN

      • 프로시저 내부에서 값을 수정할 수 있지만 프로시저가 반환되고 나서 수정할 수 없음
    • OUT

      • 프로시저 값을 호출자에게 전달하며 프로시저가 반환될 때 새로운 값이 리턴
      • OUT을 통해 보낸 값은 바깥 익명블록에서 실행
    • INOUT

      • 호출자에 의해 하나의 변수가 초기화 되고 프로시저에 의해 수정
      • IN + 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 VARCHAR 
          -- 블록 바깥쪽에서 출력을 하기 위한 변수 
      ) -- 매개값 작성 (IN, OUT 키워드 사용)
      IS
          v_cnt NUMBER := 0;
          v_result VARCHAR2(100) := '값이 존재하지 않아서 INSERT로 처리 되었습니다.';
      BEGIN
          -- 동일한 job_id가 있는지부터 체크
          -- 이미 존재한다면 1, 존재하지 않는다면 0을 주고 그것을 v_cnt에 대입할 예정
          SELECT
              COUNT(*)
          INTO v_cnt
          FROM
              jobs
          WHERE
              job_id = p_job_id;
          -- 중복이 존재하면 1이 나옴 
      
          IF v_cnt = 0 THEN -- 없다면 INSERT 
              INSERT INTO jobs 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
                  -- 매개변수로 입력받은 id가 기존에 같은 값이 있으면 그사람의 최소,최대 연봉의 내용을 v_result에 저장
                  -- v_result는 조회 결과가 있으면 블록을 나갈 수 있고 조회 결과가 없으면 블록을 나갈 수 없음
              FROM jobs
              WHERE job_id = p_job_id;
      
          END IF;
      
          -- OUT 매개변수에 결과를 할당 
          p_result := v_result;
      
          COMMIT;
      -- IN키워드를 사용해서 매개값으로 받은 것을 넣어줌 
      END;
      
      -- 프로시저에 out이 있으면 새로운 PLSQL을 작성해서 출력할 수 있음
      DECLARE
          str VARCHAR2(100);
      BEGIN
          my_new_job_proc('job1', 'test_job1', 2000, 7000, str);
          -- out변수가 존재하는 프로시저를 부를 때 out되는 값을 받을 변수를 하나 더 전달해야함
          dbms_output.put_line(str);
          -- 동일한 id가 있으므로 salary정보를 출력 
      
          my_new_job_proc('CEO', 'test_CEO', 10000, 70000, str);
          dbms_output.put_line(str);
          -- 동일한 id가 없으므로 insert 
      END;
      
      -- IN OUT 동시 처리 
      CREATE OR REPLACE PROCEDURE my_parameter_test_proc (
          p_var1 IN VARCHAR2,
          -- IN : 반환 불가이며 한번 값이 할당되면 바뀌지 않음
          p_var2 OUT VARCHAR2,
          -- OUT변수는 프로시저가 끝나기 전까지 값의 할당이 안됨 
          -- 프로시저가 끝나야 OUT 가능 
          p_var3 IN OUT VARCHAR2
          -- IN과 OUT이 둘 다 가능
      ) 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';
          -- IN으로 작성된 변수는 값을 할당할 수 없음
          p_var2 := '결과2';
          p_var3 := '결과3';
          -- IN OUT은 IN의 성질과 OUT의 기능을 모두 사용가능 
          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_var1 : ' || v_var1);
          dbms_output.put_line('v_var2 : ' || v_var2);
          dbms_output.put_line('v_var3 : ' || v_var3);
      END;

RETURN

  • RETURN
    • 메소드를 강제 종료시킬 때 사용

      CREATE OR REPLACE PROCEDURE my_new_job_proc (
          p_job_id IN jobs.job_id%TYPE,
          p_result OUT VARCHAR 
          -- 블록 바깥쪽에서 출력을 하기 위한 변수 
      ) -- 매개값 작성 (IN, OUT 키워드 사용)
       IS
          v_cnt    NUMBER := 0;
          v_result VARCHAR2(100) := '값이 존재하지 않아서 INSERT로 처리 되었습니다.';
      BEGIN
          SELECT
              COUNT(*)
          INTO v_cnt
          FROM
              jobs
          WHERE
              job_id = p_job_id;
          -- 중복이 존재하면 1이 나옴 
      
          IF v_cnt = 0 THEN -- 없다면 RETURN
              dbms_output.put_line(p_job_id || '는 테이블에 존재하지 않습니다.');
              RETURN;
              -- 프로시저 강제 종료
          END IF;    
      --    job_id와 같은게 존재하지 않으면 위의 코드를 작성하며 프로시저를 강제 종료
      --    job_id와 같은게 있으면 밑의 코드를 작성
      
          SELECT
              p_job_id
              || '의 최대 연봉 : '
              || max_salary
              || ', 최소 연봉: '
              || min_salary
          INTO v_result
              -- 매개변수로 입력받은 id가 기존에 같은 값이 있으면 그사람의 최소,최대 연봉의 내용을 v_result에 저장
              -- v_result는 조회 결과가 있으면 블록을 나갈 수 있고 조회 결과가 없으면 블록을 나갈 수 없음
          FROM
              jobs
          WHERE
              job_id = p_job_id;
      
          -- OUT 매개변수에 결과를 할당 
          p_result := v_result;
          COMMIT;
      -- IN키워드를 사용해서 매개값으로 받은 것을 넣어줌 
      END;
      
      DECLARE
          str VARCHAR2(100);
      BEGIN
          my_new_job_proc('CEO', str);
          dbms_output.put_line(str);
          my_new_job_proc('student', str);
          dbms_output.put_line(str);
      -- 데이터가 존재하지 않으면 거기서 RETURN을 통해 프로세스를 종료
      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;

Practice

  • PRACTICE
    • PROCEDURE를 이해하기 위한 연습

      /*
      프로시저명 guguProc
      구구단 단수를 전달받아 해당 단수를 출력하는 procedure을 생성하세요. 
      */
      
      CREATE OR REPLACE PROCEDURE guguproc (
          p_num IN NUMBER
      ) IS
      BEGIN
          FOR i IN 1..9 LOOP
              dbms_output.put_line(p_num
                                   || '*'
                                   || i
                                   || '='
                                   || p_num * i);
          END LOOP;
      END;
      
      DECLARE BEGIN
          guguproc(3);
      END;
      
      /*
      부서번호, 부서명, 작업 flag(I: insert, U:update, D:delete)을 매개변수로 받아 
      depts 테이블에 
      각각 INSERT, UPDATE, DELETE 하는 depts_proc 란 이름의 프로시저를 만들어보자.
      그리고 정상종료라면 commit, 예외라면 롤백 처리하도록 처리하세요.
      */
      DROP TABLE depts;
      CREATE TABLE depts AS SELECT department_id, department_name FROM departments;
      ALTER TABLE depts ADD CONSTRAINTS depts_pk PRIMARY KEY(department_id);
      SELECT * FROM depts;
      
      CREATE OR REPLACE PROCEDURE depts_proc (
          d_num  IN depts.department_id%TYPE,
          d_name IN depts.department_name%TYPE,
          flag   IN VARCHAR2
      ) IS
      BEGIN
          IF flag = 'I' THEN
              INSERT INTO depts VALUES (
                  d_num,
                  d_name
              );
      
          ELSIF flag = 'U' THEN
              UPDATE depts
              SET
                  department_name = d_name
              WHERE
                  department_id = d_num;
      
          ELSIF flag = 'D' THEN
              DELETE FROM depts
              WHERE
                  department_id = d_num;
      
          ELSE
              dbms_output.put_line('친구야 다른 flag를 넣었어');
          END IF;
      
          COMMIT;
      EXCEPTION
          WHEN OTHERS THEN
              dbms_output.put_line('예외가 발생했어!');
              dbms_output.put_line('예외 메세지 : ' || sqlerrm);
              ROLLBACK;
      END;
      
      EXEC depts_proc(0, 'OK~', 'I');
      EXEC depts_proc(10, '아아아아아아', 'U');
      
      SELECT
          *
      FROM
          depts;
          
      /*
      employee_id를 입력받아 employees에 존재하면,
      근속년수를 out하는 프로시저를 작성하세요. (익명블록에서 프로시저를 실행)
      없다면 exception처리하세요
      */
      
      CREATE OR REPLACE PROCEDURE emp_hire_proc (
          e_id   IN employees.employee_id%TYPE,
          e_year OUT NUMBER
      ) IS
          v_cnt       NUMBER := 0;
          v_hire_date employees.hire_date%TYPE;
      BEGIN
          SELECT
              hire_date
          INTO v_hire_date
          FROM
              employees
          WHERE
              employee_id = e_id;
      
          e_year := trunc((sysdate - v_hire_date) / 365);
      EXCEPTION
          WHEN OTHERS THEN
              dbms_output.put_line(e_id || '(은) 는 없는 데이터 입니다.');
              COMMIT;
      END;
      
      DECLARE
          e_year NUMBER;
      BEGIN
          emp_hire_proc(136, e_year);
          dbms_output.put_line(e_year || '년');
      END;
      
      /*
      프로시저명 - 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, 'MOON', 'ABC', sysdate, 'test');
      EXEC new_emp_proc(100, 'MOO', 'AB', sysdate, 'test234');
      SELECT * FROM EMPS;

DATABASE TRIGGER

TRIGGER

  • TRIGGER
    • 테이블에 부착한 형태로 INSERT, UPDATE, DELTE 작업이 수행될 때 자동으로 진행

    • 특정 코드가 자동으로 작동하는 형식

      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;
      
      CREATE TABLE tbl_user (
          id      VARCHAR2(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) -- 변경한 사용자 
      );
    • :OLD

      • 참조 전 열의 값 (INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 값)
    • :NEW

      • 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정 된 자료)
    • AFTER

      • INSERT, UPDATE, DELETE 작업 이후에 동작하는 트리거를 의미

        CREATE OR REPLACE TRIGGER trg_user_backup AFTER
            UPDATE OR DELETE ON tbl_user
            FOR EACH ROW 
        -- update나 delete 이후 동작 
        DECLARE -- 사용할 변수를 선언 
            v_type VARCHAR2(10);
        BEGIN
            IF updating THEN -- UPDATING은 시스템 자체에서 상태에 대한 내용을 지원하는 빌트인 구문 
        -- UPDATE 상황이라면 
                v_type := '수정';
            ELSIF deleting THEN
                v_type := '삭제';
            END IF;
        
        -- 실행 구문 시작. (:OLD는 테이블 DELETE, UPDATE가 적용되기 전 기존 데이터이며 변경 전 데이터를 뜻함)
        -- UPDATE와 DELETE가 실행되기 전 변경 전의 데이터를 BACKUP테이블에 넣어주겠다는 뜻
            INSERT INTO tbl_user_backup VALUES (
                :OLD.id,
                :OLD.name,
                :OLD.address,
                
                sysdate,
                v_type,
                user()
            );
            -- tbl_user_backup에 데이터 변경 전의 데이터가 입력
        END;
        -- tbl_user가 UPDATE와 DELETE가 진행된 후 TRIGGER 진행하는데 trg_user_backup 테이블에 결과를 넣어줌
        
        -- 트리거 동작 확인 
        INSERT INTO tbl_user VALUES (
            'test01',
            'admin',
            '서울'
        );
        
        INSERT INTO tbl_user VALUES (
            'test02',
            '강아지',
            '서울'
        );
        
        INSERT INTO tbl_user VALUES (
            'test03',
            '고양이',
            '서울'
        );
        
        SELECT
            *
        FROM
            tbl_user;
        
        SELECT
            *
        FROM
            tbl_user_backup;
        
        UPDATE tbl_user
        SET
            address = '인천'
        WHERE
            id = 'test01';
        
        DELETE FROM tbl_user
        WHERE
            id = 'test01';
            
        SELECT
            *
        FROM
            tbl_user_backup;

BEFORE

  • BEFORE
    • INSERT, UPDATE, DELETE 작업 이전에 동작하는 트리거를 의미

      CREATE OR REPLACE TRIGGER trg_user_insert
          BEFORE INSERT
          ON tbl_user
          FOR EACH ROW
      BEGIN
          :NEW.name := SUBSTR(:NEW.name, 1, 1) || '**';
      END;
      --    새로 입력받은 name을 넣어줌
          -- name의 첫글자만 추출하고 이후 **을 붙임 
      
      INSERT INTO tbl_user VALUES('test05', '김메롱', '부산');
      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,
          '치킨',
          10,
          10500
      );
      
      INSERT INTO product VALUES (
          product_seq.NEXTVAL,
          '햄버거',
          100,
          5000
      );
      
      SELECT
          *
      FROM
          product;
      
      -- 주문 히스토리에 데이터가 들어오면 실행
      CREATE OR REPLACE TRIGGER trg_order_history
          AFTER INSERT ON order_history FOR EACH ROW 
      --    order_history에 insert가 들어간 후 trigger 발동
          DECLARE 
              v_total NUMBER;
              v_product_no NUMBER;
      --        프로시저에서 사용할 변수 생성
          BEGIN 
              dbms_output.put_line('트리거 실행');
              SELECT :NEW.total
      --        입력할 자료 중 total이라는 변수를 select
          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,
          5000
      );
      
      INSERT INTO order_history VALUES (
          order_history_seq.NEXTVAL,
          200,
          1,
          1,
          15000
      );
      
      SELECT
          *
      FROM
          product;
profile
안녕하세요! 공부한 내용을 기록하는 공간입니다.

0개의 댓글