procedure (프로시저) 생성 및 사용 예제

 

-- ###################
-- 프로시져 생성
-- ###################

create or replace procedure pr_personal_annual
(
in_year in salary.year%TYPE,
in_no in emp.no%TYPE
)

is

v_emp_no emp.no%TYPE;
v_emp_name emp.name%TYPE;
v_dept_deptname dept.deptname%TYPE;
v_emp_position emp.position%TYPE;
v_sal_year salary.year%TYPE;
sal_n salay.annual%TYPE;
sal_l salay.annual%TYPE;
sal_d salay.annual%TYPE;

begin

select n.no,n.name,n.deptname,n.position,n.year,
n.annual,l.annual,n.annual-l.annual
into v_emp_no,v_emp_name,v_dept_deptname,v_emp_position,v_sal_year,
sal_n,sal_l,sal_d
from
(select emp.no,emp.name,annual,deptname,position,year
from emp,dept,salary sn
where emp.no=in_no and sn.no=in_no and emp.dept=dept.dept and year=in_year) n,
(select emp.no,emp.name,annual,deptname,position,year
from emp,dept,salary sn
where emp.no=in_no and sn.no=in_no and emp.dept=dept.dept and year=in_year-1) l;

DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('emp no : ' || v_emp_no);
DBMS_OUTPUT.PUT_LINE('emp name : ' || v_emp_name);
DBMS_OUTPUT.PUT_LINE('dept : ' || v_dept_deptname);
DBMS_OUTPUT.PUT_LINE('position : ' || v_emp_position);
DBMS_OUTPUT.PUT_LINE('year : ' || v_sal_year);
DBMS_OUTPUT.PUT_LINE('cur annual : ' || sal_n);
DBMS_OUTPUT.PUT_LINE('last annual : ' || sal_l);
DBMS_OUTPUT.PUT_LINE('annual gap : ' || sal_d);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('no name dept posi year c_yr l_yr gap');
DBMS_OUTPUT.PUT_LINE('v_emp_no || ',' || v_emp_name || ',' || v_dept_deptname || ',' ||
v_emp_position || ',' || v_sal_year || ',' || sal_n || ',' ||
sal_l || ',' || sal_d);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('해당 데이터가 없습니다!');


END;
/


-- 프로시져 실행
SQL> set serveroutput on
SQL> execute pr_personal_annual('2006','1')
SQL> set serveroutput off

 

* 출처 : http://www.redjava.co.kr