[Oracle] 오라클 Procedure (프로시저) 활용 및 예제

※ 예제(Example) . 부서이름을 입력 받아 사원 ID, 사원의 이름을 출력하는 프로시저 생성.

 

계정 : HR

사용 Table 정보 : 

departments 테이블

 

employees

1. 먼저 프로시저 생성 전에 부서이름을 넣어 사원ID와 사원 이름을 출력하는 쿼리문을 생성해서 테스트 해본다.

select 
		employee_id, last_name
from 
		employees emp
left outer join departments dept
on emp.department_id = dept.department_id
where
       dept.department_name = '부서명';

 

2. 프로시저 생성

create or replace procedure example_user_get(
        p_department departmentS.department_name%type --1
)
is
    
    i_emp employees%rowtype; --2
    
    
     Cursor emp_get is --3
        select employee_id, last_name
        from employees emp
        left outer join departments dept
        on emp.department_id = dept.department_id
        where
            dept.department_name = p_department;
            
begin
    open emp_get;
    
    LOOP
    fetch emp_get into i_emp.employee_id, i_emp.last_name; --4
    exit when emp_get%NOTFOUND; --5
    DBMS_OUTPUT.PUT_LINE(i_emp.employee_id || ' ' || i_emp.last_name);
    END LOOP;
END;

--1. 부서값을 받아올 매개변수를 선언한다. 'departmentS.department_name%type' 구문은 매개변수 Type를 해당컬럼과 같게 지정해주는 문으로 해당열의 변수타입을 알고있으면 nvarchar, num 이런식으로 받아와도 된다.

 

--2. i_emp employees%rowtype; 구문은 fetch로 값을 받아올때 변수를 지정해주는 구문이다. %rowtype 은 변수타입을 employees 의 행의 타입으로 선언한것이다.

 

--3. 프로시저를 사용해보면 알겠지만 오라클 Procedure 는 멀티 Row를 출력하기 위해선 Cursor를 사용해야한다.

 

--4. 커서값을 fetch로 가져와 변수에 하나씩 대입해준다.

 

--5. %NOTFOUND 는 마지막으로 얻은 커서의 결과 SET에 레코드가 없다면 참을 반환해준다.

(결과가 없으면 LOOP을 나오기위해 선언해줬다. LOOP를 쓰지않으면 1행만 출력된다.)

 

3. 프로시저 실행

EXEC example_user_get('Marketing');

 

실행결과