DATABASE PROCEDURE & TRIGGER

Let's Just Go·2022년 5월 14일
Databaseoracle
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
Let's Just Go
안녕하세요! 공부한 내용을 기록하는 공간입니다.
이전 포스트

DATABASE PL/SQL

0개의 댓글