본문 바로가기
TechNical/Oracle

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

by 강멍멍이 2007. 12. 11.
반응형

뭐.. 이건 어느 강좌 들으면서 레포트로 나왔던 건데...
프로시져를 실행하기 위한 준비부터 차근차근 다 기술해 보았다.
왼팔에 기부쓰 하고.. 이게 뭐 하는 짓이여 ㅡㅡ;; 팔 아퍼 ㅠㅠ


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

-- 테이블스페이스 생성
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 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

반응형

댓글