PL/SQL 객체) STORED PROCEDURE

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

STORED PROCEDURE

- 일련의 작업 절차를 작성한 PL/SQL 객체 → 함수와 같이 호출해서 사용

- 함수와 달리 리턴 값이 없음

- 하지만, 호출부로의 값 전달은 out 모드의 매개변수를 사용할 수 있음

- 저장 프로시저는 일련의 작업을 미리 컴파일 해두고, 즉시 사용 가능한 상태로 보관하므로 DB서버의 처리 성능 향상 효과 있음

- 일반 DQL에서는 호출 불가 : 익명블럭/다른 프로시저에서 호출 가능

 

문법

** 프로시저의 매개변수는 생략 가능 ** 

create or replace procedure 프로시저명 [(
    매개변수명 [모드] 타입,
    매개변수명 [모드] 타입,
    ...
)]
is
	-- 지역변수 선언
begin
	-- 실행부
exception
	-- 예외처리부
end;
/

 

[모드]

① in (기본값) : 프로시저로 값을 전달할 때

② out : 프로시저에서 호출부로 값을 전달할 때 (호출 시 전달한 변수의 주소값)

③ inout : in / out 두 가지 겸용

 

---

매개변수 없는 프로시저

employee_ex 사원을 모두 삭제하는 프로시저

-- employee_ex 사원을 모두 삭제하는 프로시저
create or replace procedure proc_delete_all_employee_ex
is
begin
    delete from employee_ex;
    commit;
    dbms_output.put_line('employee_ex의 모든 사원 레코드가 삭제되었습니다.');
end;
/

-- 익명 블럭에서 실행
begin
    -- 매개변수 없으므로 () 생략!
    proc_delete_all_employee_ex;
end;
/

select * from employee_ex;

 

생성된 procedure 객체 확인

select 
	*
from 
	user_procedures
where 
	object_type = 'PROCEDURE';

 

매개변수 있는 프로시저

사원 1명 삭제 프로시저

-- 사원 1명 삭제 프로시저 proc_delete_employee_ex
create or replace procedure proc_delete_employee_ex (
    p_emp_id in employee_ex.emp_id%type
    -- in모드는 기본값이므로 생략 가능
)
is
begin
    delete from
        employee_ex
    where
        emp_id = p_emp_id;
    commit;
    dbms_output.put_line(p_emp_id || '번 사원데이터를 삭제했습니다.');
end;
/

-- 익명블럭에서 실행
begin
    proc_delete_employee_ex('&사번');
end;
/

select * from employee_ex where emp_id = '202';

사원 정보 조회용 프로시저 생성 (이름, 부서명)

create or replace procedure proc_get_employee_info (
    p_emp_id in employee.emp_id%type,   -- 값
    p_emp_name out employee.emp_name%type,  -- 공간
    p_dept_title out department.dept_title%type -- 공간
)
is
begin
    select
        emp_name,
        (select dept_title from department where dept_id = e.dept_code)
    into
        p_emp_name, p_dept_title
    from 
        employee e
    where
        emp_id = p_emp_id;  -- 값으로만 사용
        
    dbms_output.put_line(p_emp_id || '번 사원 조회 완료 : ' || p_emp_name || ', ' || p_dept_title);
end;
/

-- 익명블럭에서 실행
declare
    v_emp_id employee.emp_id%type := '&사번';
    v_emp_name employee.emp_name%type;
    v_dept_title department.dept_title%type;
begin
    -- v_emp_id : 값 / v_emp_name, v_dept_title : 공간 전달
    proc_get_employee_info(v_emp_id, v_emp_name, v_dept_title);
    dbms_output.put_line(v_emp_id || ' 조회 결과 : ' || v_emp_name || ', ' || v_dept_title);
end;
/

'202번 사원 조회 완료 : 노옹철, 총무부'는 프로시저에서 출력된 결과,

'202 조회 결과 : 노옹철, 총무부'는 익명블럭에서 출력된 결과 입니다.

 

익명블럭에서 v_emp_name, v_dept_title의 공간 주소값을 넘겨준 후, 주소값을 기억하고 프로시저에서 값을 out모드로 넘겨줄 때 저장한 주소값에 값을 넘겨주어 호출부로 넘어오게 된 것입니다.

 

upsert

레코드가 이미 존재한다면 update 처리, 레코드가 존재하지 않으면 insert 처리

J1의 부사장을 '대장'으로 변경, J8 / 인턴 데이터 추가

create table job_ex
as
select * from job;

-- 제약조건 다시 추가
alter table job_ex
add constraint pk_job_ex_job_code primary key(job_code)
modify job_code varchar2(5)
modify job_name not null;

-- 프로시저 proc_upsert_job_ex
begin
    proc_upsert_job_ex('&직급코드', '&직급명');
end;
/

create or replace procedure proc_upsert_job_ex (
    p_job_code job_ex.job_code%type,
    p_job_name job_ex.job_name%type
)
is
    v_cnt number;
begin
    -- 레코드 존재 여부 확인
    select count(*)
    into v_cnt
    from job_ex
    where job_code = p_job_code;
    
    -- 분기처리
    if v_cnt > 0 then
        -- update
        update job_ex
        set job_name = p_job_name
        where job_code = p_job_code;
        dbms_output.put_line(p_job_code || '의 직급명을 ' || p_job_name || '으로 변경했습니다.');
    else
        -- insert
        insert into job_ex values(p_job_code, p_job_name);
        dbms_output.put_line('직급코드 ' || p_job_code || ', 직급명 ' || p_job_name || ' 레코드를 추가하였습니다.');
    end if;
    commit;
end;
/

select * from job_ex;