[DB] 18. 프로시저(PROCEDURE) & 트리거(TRIGGER)

프로시저 (PROCEDURE)

프로시저는 일련의 작업들을 하나로 묶어서 저장해 두었다가 호출하여 간편하게 작업을 실행할 수 있게 해준다.

저장 프로시저 - 하나의 함수처럼 실행하기 위한 쿼리의 집합.
만드는 과정과, 실행하는 구문이 나누어 작성합니다.

 

 

➰ 기본 사용 틀

--프로시저 생성

create or replace procedure new_job_proc -- 매개변수
is --변수의 선언 영역
begin --실행영역
    dbms_output.put_line('hello world!');
end;


--프로시저 실행
execute new_job_proc;

➰  프로시저 매개변수 in

in 매개 변수를 사용하면 execute할 때 값을 받아오는 변수로 사용한다는 뜻이다.

--프로시저 매게변수 in
create or replace procedure 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
    values(p_job_id, p_job_title, p_min_sal, p_max_sal);
end;

execute new_job_proc('sm_man1', 'sample test', 1000, 5000);

select * from jobs;

➰ 프로시저 응용

--프로시저 응용
create or replace procedure 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_count number := 0; --지역변수
begin
    
    select count(*)
    into v_count
    from jobs
    where job_id = p_job_id; -- v count의 데이터개수 할당
    -- v count가 없다면 insert 있다면 update
    if v_count = 0 then 
        insert into jobs
        values(p_job_id, p_job_title, p_min_sal, p_max_sal);
    else
        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;
end;

execute new_job_proc('sm_man1', 'sample test', 1000, 5000);

➰ 프로시저의 디폴트 매게 변수

--프로시저의 디폴트 매게 변수
execute new_job_proc('sm_man1', 'sample test'); -- 매개변수의 개수가 일치하지 않기 떄문에 에러가 난다.

create or replace procedure 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_count number := 0; --지역변수
begin
    
    select count(*)
    into v_count
    from jobs
    where job_id = p_job_id; -- v count의 데이터개수 할당
    -- v count가 없다면 insert 있다면 update
    if v_count = 0 then 
        insert into jobs
        values(p_job_id, p_job_title, p_min_sal, p_max_sal);
    else
        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;
end;

execute new_job_proc('sm_man2', 'sample test22');

execute new_job_proc('sm_man2', 'sample test22',1000, 5000);

➰ 프로시저 매개변수 OUT

-- 매개변수 OUT 
create or replace procedure 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 --out
    )
is
    v_count number := 0; --지역변수
begin
    
    select count(*)
    into v_count
    from jobs
    where job_id = p_job_id; -- v count의 데이터개수 할당
    -- v count가 없다면 insert 있다면 update
    if v_count = 0 then 
        insert into jobs
        values(p_job_id, p_job_title, p_min_sal, p_max_sal);
        
        --out 변수에 성공 할당
        p_result := '성공';
    else
        update jobs
        set job_title = p_job_title,
            min_salary = p_min_sal,
            max_salary = p_max_sal
        where job_id = p_job_id;
        
        --out 변수에 업데이트 할당
        p_result := '업데이트';
    end if;
end;

-- out변수를 활용하려면 익명블록에서 실행
declare
    str varchar2(20); -- out을 돌려받을 변수
begin
    new_job_proc('sa_man4', 'sample', 0, 1000, str);
    dbms_output.put_line(str); -- 결과
end;

➰ 프로시저 예외처리

-- 예외처리
    EXECUTE WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다');
END;

-- OUT변수를 활용하면 익명 블록에서 실행

➰ 실습

--------------------------------------------------------------------실습
/*
employee_id를 받아서 employees에 존재하면, 근속년수를 출력
없다면 ID는 없습니다를 출력하는 프로시저
*/
create or replace procedure find_emp_id
    (find_emp_id employees.employee_id%type,
      str_result out varchar2)
is
    id_count number := 0;
begin
    select count(*)
    into id_count
    from employees
    where employee_id = find_emp_id;
    
    if id_count = 0 then 
        str_result := 'ID는 없습니다.';
    else
        select hire_date 
        into str_result
        from employees 
        where employee_id = find_emp_id;
    end if;
end;


declare
    str varchar2(100); -- out을 돌려받을 변수
begin
    find_emp_id(2000, str);
    dbms_output.put_line(str); -- 결과
end;

select * from employees;


--------------------------------------------------------------------강사님 답안

-- 프로시저 생성
create or replace procedure find_emp_id 
    (find_emp_id in employees.employee_id%type)
is
    id_count number := 0;
    emp_year number := 0;
begin
    select count(*)
    into id_count
    from employees
    where employee_id = find_emp_id;
    
    if id_count = 0 then 
        dbms_output.put_line('ID가 없습니다.');
    else
        select trunc((sysdate - hire_date) /365)
        into emp_year
        from employees 
        where employee_id = find_emp_id;
        
        dbms_output.put_line('근속년수는:'||emp_year);
    end if;
end;

execute find_emp_id(200);

 

 

트리거 (TRIGGER)

• trigger는 테이블에 부착한 현태로써, insert, update, delete 작업이 수행될 때
• 특정 코드가 작동되도록 하는 구문이다.
• 트리거는 실행할 때 범위를 지정하고 f5버튼으로 부분 실행해야한다. 그렇지 않으면 하나의 구문으로 인식되어 정상동작하지 않는다.

🔸 사전 작업

-- 1. 테스트 테이블 생성
create table tb1_test(
    id number(10),
    text varchar2(20)
);

-- 서버아웃풋 온...
set serveroutput on;

🔸 트리거 생성 후 table에 insert/update/delete

-- 트리거 생성

create or replace trigger trg_test
    after update or delete -- 업데이트나 삭제이후에 실행되는 트리거
    on tb1_test -- 트리거를 부착할 테이블
    for each row -- 각행에 적용
declare
begin
    DBMS_OUTPUT.PUT_LINE('트리거가 동작함');
end;

insert into tb1_test values(1, '홍길동');
insert into tb1_test values(2, '이순신');
update tb1_test set text = '홍길동' where id = 1;
delete from tb1_test where id = 2;

select * from tb1_test;

🔹 before vs after

• after 트리거 - insert, update, delete 작업 이후에 동작하는 트리거
• before 트리거 - insert, update, delete 작업 이전에 동작하는 트리거
• :OLD - 참조  전  열의  값  (INSERT : 입력  전  자료, UPDATE : 수정  전  자료, DELETE : 삭제할  자료)
• :NEW - 참조  후  열의  값  (INSERT : 입력  할  자료, UPDATE : 수정  된  자료)
  
  

🔸 사전 작업

-- update나 delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관
-- 업데이트/삭제가 발생하면 user에 있던 회원 정보를 backup에 저장하는 트리거 만들기.

create table tb1_user_backup(
    id varchar2(20),
    name varchar2(20),
    address varchar2(30),
    updatedate date default sysdate,
    m_type char(10), --변경타입
    m_user varchar2(20) --변경한 사용자
);

create table tb1_user(
    id varchar2(20) primary key,
    name varchar2(20),
    address varchar2(30)
);

 

🔸 After 트리거 생성 후 table에 insert/update/delete 

create or replace trigger trg_user_backup
    after update or delete -- 업데이트나 삭제이후에 실행되는 트리거
    on tb1_user -- 부착은 user테이블에 적용해야 된다.
    for each row -- 각행에 적용
declare -- 변수 선언
    vn_type varchar2(10);
begin
    if updating then --업데이트 or 딜리트 시에 동작함.
        vn_type := '수정';
    elsif deleting then
        vn_type := '삭제';
    end if;

   --user_backup에 insert
    insert into tb1_user_backup values(:old.id, :old.name, :old.address, sysdate, vn_type, user());
end;

insert into tb1_user values('test01', 'admin', '서울');
insert into tb1_user values('test02', 'admin', '경기');
insert into tb1_user values('test03', 'admin', '부산');
update tb1_user set address = '부산' where id = 'test01';
delete from tb1_user where id = 'test02';

select * from tb1_user_backup;

 
🔸 Before 트리거 생성 후 table에 insert/update/delete 

--before
create or replace trigger trg_user_insert
    before insert 
    on tb1_user
    for each row
declare

begin
    :new.name := substr(:new.name, 1,1) || '**'; -- 입력된 데이터 **을 붙임
end;

insert into tb1_user values('test04', 'admin', '부산');
insert into tb1_user values('test05', 'admin', '경기');

select * from tb1_user;