오라클 프로시저 처음부터 끝까지 예제

정리가 잘 되어 있어서 퍼왔습니다.

출처 : http://seobangnim.com/zbxe/?mid=DB&page=2&document_srl=14525

-- ###################
--  테스트 환경 구축
-- ###################

-- 테이블스페이스 생성
create tablespace test
datafile 'D:\oracle\product\10.0.2\oradata\kei\test.dbf' size 10M

-- 유저 생성
create user kei identified by kei
default tablespace test

-- 권한 부여
grant connect,resource to kei

-- 접속 
connect kei/kei

-- ###################
--   테이블 생성
-- ###################
-- 주어진 테이블 레이아웃에서 타입을 일부분 수정 했습니다.

-- 사원정보 테이블
create table emp(
no char(5) primary key,
name varchar2(20) not null,
sex char(3),
age number(3),
marriage char(3),
phone varchar2(30),
addr char(3),
enteryear char(4) not null,
status char(3) not null,
dept char(5) not null,
position varchar2(10))

-- 부서정보 테이블
create table dept(
dept char(5),
deptname varchar2(20),
depth number(1),
location char(3))

-- 연봉정보 테이블
-- 제약 조건 생성
-- NO 컬럼을 FK로 생성 했습니다.데이터가 안 들어 가더군요.
-- 또한 중복값 방지를 위해 복합키를 넣어 봤습니다.
-- 프로시져 실행시 결과값이 하나 이상 있으면 오류를 리턴해서 생성 했습니다.
create table salary(
no char(5),
year char(4) not null,
annual number,
cdate date,
constraint PK_Sal primary key(no,year),
constraint FK_Sal_No foreign key(no) references emp(no))

-- ###################
--    데이터 입력
-- ###################

-- 사원정보 테이블
insert into emp values(1,'문어바','남',33,'N','225-383-3939','321','2003','Y','10','사장');
insert into emp values(2,'꿀대지','남',32,'Y','125-343-5219','512','2004','N','20','과장');
insert into emp values(3,'꽃돼지','여',24,'N','775-354-7798','531','2005','Y','20','매니저');
insert into emp values(4,'복돼지','남',30,'N','626-332-3321','411','2006','Y','30','사원');
insert into emp values(5,'금돼지','남',28,'Y','425-933-4569','385','2007','Y','30','사원');

-- 부서정보 테이블
insert into dept values('10','','사장실','1','567');
insert into dept values('20','10','영양실','2','678');
insert into dept values('30','20','조리실','3','789');

-- 연봉정보 테이블
insert into salary values('1','2003','5000','2003-04-10');
insert into salary values('1','2004','5500','2004-04-10');
insert into salary values('1','2005','6000','2005-04-10');
insert into salary values('1','2006','6500','2006-04-10');
insert into salary values('1','2007','7000','2007-04-10');

insert into salary values('2','2004','4000','2004-02-10');
insert into salary values('2','2005','4500','2005-02-10');
insert into salary values('2','2006','5000','2006-02-10');
insert into salary values('2','2007','5500','2007-02-10');

insert into salary values('3','2005','3000','2005-08-11');
insert into salary values('3','2006','3500','2006-08-11');
insert into salary values('3','2007','4500','2007-08-11');

insert into salary values('4','2006','3500','2006-06-20');
insert into salary values('4','2007','4000','2007-06-20');

insert into salary values('5','2007','4000','2007-07-15');

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

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 salary.annual%TYPE;

sal_l salary.annual%TYPE;

sal_d salary.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