[PL/SQL] Procedure practice

5

다음은 이번 장에서 학습했던 my_new_job_proc 프로시저다. 이 프로시저는 JOBS 테이블에 기존 데이터가 없으면 INSERT, 있으면 UPDATE를 수행하는데 IF문을 사용해 구현하였다. IF문을 제거하고 동일한 로직을 처리하도록 MERGE문으로 my_new_job_proc2란 프로시저를 생성해 보자.

    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
    vn_cnt NUMBER := 0;
    BEGIN
    -- 동일한 job_id가 있는지 체크
    SELECT COUNT(*)
      INTO vn_cnt
      FROM JOBS
     WHERE job_id = p_job_id;

    -- 없으면 INSERT
    IF vn_cnt = 0 THEN
       INSERT INTO JOBS ( job_id, job_title, min_salary, max_salary, create_date, update_date)
                 VALUES ( p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
    UPDATE JOBS
        SET job_title   = p_job_title,
            min_salary  = p_min_sal,
            max_salary  = p_max_sal,
            update_date = SYSDATE
       WHERE job_id = p_job_id;
    END IF;
    COMMIT;
    END ;

답

create or replace procedure my_new_job_proc2
(   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
    merge into jobs
    using(
        select count(*) cnt
        from jobs
        where job_id = p_job_id
    ) b
    on (b.cnt > 0)
    when matched then
        UPDATE 
        SET job_title   = p_job_title,
            min_salary  = p_min_sal,
            max_salary  = p_max_sal,
            update_date = SYSDATE
    when not matched then
        INSERT ( jobs.job_id, jobs.job_title, jobs.min_salary, jobs.max_salary, jobs.create_date, jobs.update_date)
        VALUES ( p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
end;
/
begin
my_new_job_proc2('HP_DOC', 'Doctor', 1121, 11211);
end;

select * from jobs;

6

부서 테이블의 복사본 테이블을 다음과 같이 만들어 보자.

입력

CREATE TABLE ch09_departments AS
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, PARENT_ID
  FROM DEPARTMENTS;

위 테이블을 대상으로 다음과 같은 처리를 하는 프로시저를 my_dept_manage_proc라는 이름으로 만들어보자.
❶ 매개변수: 부서번호, 부서명, 상위부서번호, 동작 flag
❷ 동작 flag 매개변수 값은 ‘upsert’ → 데이터가 있으면 UPDATE, 아니면 INSERT
‘delete’ → 해당 부서 삭제
❸ 삭제 시, 만약 해당 부서에 속한 사원이 존재하는지 사원 테이블을 체크해 존재하면 경고 메시지와 함께 delete를 하지 않는다.

--CREATE TABLE ch09_departments AS
--    SELECT DEPARTMENT_ID, DEPARTMENT_NAME, PARENT_ID
--      FROM DEPARTMENTS;
--      
create or replace procedure my_dept_manage_proc(
    p_department_id in ch09_departments.department_id%type,
    p_department_name in ch09_departments.department_name%type,
    p_parent_id in ch09_departments.parent_id%type,
    p_flag in varchar
    )
is
vn_cnt number := 0;
vn_emp_cnt number := 0;
begin
    select count(*)
    into vn_cnt
    from ch09_departments
    where department_id = p_department_id;
    
    select count(*)
    into vn_emp_cnt
    from employees 
    where department_id = p_department_id;
    
    if p_flag like 'upsert' then
        if vn_cnt = 0 then
            insert into ch09_departments (department_id, department_name, parent_id)
            values(p_department_id, p_department_name, p_parent_id);
        else 
            update ch09_departments
                set department_name = p_department_name,
                    parent_id = p_parent_id
            where department_id = p_department_id;
        end if;
    elsif p_flag like 'delete' then
        if vn_emp_cnt = 0 then
            delete from ch09_departments
            where department_id = p_department_id;
        else
            dbms_output.put_line('사원이 존재합니다.');
        end if;
    end if;        
    commit;
end;

/

exec my_dept_manage_proc(300, 'leehyun', 10, 'upsert');

select * from ch09_departments;

exec my_dept_manage_proc(300, 'leehyun', 10, 'delete');
select * from ch09_departments;