[오라클] 프로시저 처음부터 끝까지 예제
페이지 정보
작성자 서방님 댓글 1건 조회 90회 작성일 09-01-02 11:39본문
-- ###################
-- 테스트 환경 구축
-- ###################
-- 테이블스페이스 생성
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
댓글목록
서방님님의 댓글
서방님 작성일
<P>CREATE OR REPLACE PROCEDURE SEIZE.USP_TEST_BATCH1 (<BR>I_CO_CD IN VARCHAR2,<BR>I_USER_CD IN VARCHAR2,<BR>I_TEAM_CD IN VARCHAR2,<BR>I_STNDYRMN IN VARCHAR2,<BR>I_EDU_KEY IN VARCHAR2,<BR>I_FASTIVAL IN VARCHAR2<BR>)<BR>IS<BR>--i_COST NUMBER DEFAULT 0;<BR>i_R_COST NUMBER DEFAULT 0;</P>
<P>i_PAYSUNG NUMBER DEFAULT 0;<BR>i_SILJUK NUMBER DEFAULT 0;<BR>i_AVGSILJUK NUMBER DEFAULT 0;<BR>i_DALSUNG NUMBER DEFAULT 0;<BR>i_BONUS NUMBER DEFAULT 0;<BR>i_BONUSUNG NUMBER DEFAULT 0;<BR>i_CMONTH NUMBER DEFAULT 0;<BR>i_DMONTH NUMBER DEFAULT 0;</P>
<P>i_GUNSOK NUMBER DEFAULT 0;<BR>i_JANGGUN NUMBER DEFAULT 0;<BR>i_HAPPYSAD NUMBER DEFAULT 0;<BR>i_FESTIVAL NUMBER DEFAULT 0;<BR>i_HOLIDAY NUMBER DEFAULT 0;<BR>i_EDUCNT NUMBER DEFAULT 0;<BR>i_EDUAWN NUMBER DEFAULT 0;<BR>i_TOTAL NUMBER DEFAULT 0;<BR>v_ATA NUMBER DEFAULT 0;<BR>v_DAY NUMBER DEFAULT 0;<BR>v_LAST_DT VARCHAR(8);<BR>v_LSTEND_DT VARCHAR(8);<BR>v_FSTSTT_DT VARCHAR(8);</P>
<P>v_MONTH VARCHAR(8);<BR>v_MONTHLST VARCHAR(8);<BR>v_30DAY VARCHAR(1);</P>
<P>--v_YR VARCHAR(2);<BR>--v_DR NUMBER DEFAULT 0;<BR>--v_USER_CD VARCHAR2;</P>
<P>BEGIN<BR> v_30DAY := 'N';<BR> -- v_30DAY := 'N';<BR> --v_MONTH := ;<BR> --i_COST := 800000;<BR> --=========================<BR> --===== 마지막 날짜 ======<BR> --=========================<BR> BEGIN<BR> SELECT TO_CHAR(LAST_DAY(TO_DATE(I_STNDYRMN || '01','YYYYMMDD')),'YYYYMMDD')<BR> INTO v_LAST_DT<BR> FROM DUAL;<BR> END;<BR> --===========================<BR> --====== 지우기 =======<BR> --===========================<BR> BEGIN<BR> DELETE FROM TBL_USER_SAL<BR> WHERE STNDYRMN = I_STNDYRMN;<BR> COMMIT;<BR> END;</P>
<P> --=========================<BR> --===== 근무 일수 ======<BR> --=========================<BR> /*BEGIN<BR> SELECT COUNT(*)<BR> INTO v_DAY<BR> FROM TBL_BIZ_DT_INF<BR> WHERE CO_CD = I_CO_CD<BR> AND BIZ_DT BETWEEN I_STNDYRMN ||'01' AND v_LAST_DT<BR> AND HODY_DV_CD = '0';</P>
<P> END; */</P>
<P><BR> --========================<BR> --===== FOR 문 시작 =====<BR> --========================<BR> BEGIN<BR> FOR Rec1 IN<BR> (<BR> SELECT<BR> USER_CD AS UCD<BR> ,TEAM_CD AS TEAM<BR> ,QUA_AQ_DT AS QSDT --자격취득일<BR> ,QUA_DPRV_DT AS QEDT --자격상실일<BR> ,JNCO_DT AS CSDT --입사일자<BR> ,LVCO_DT AS CEDT --퇴사일자<BR> --INTO v_USER_CD<BR> FROM TBL_USER_INF TUI<BR> WHERE CO_CD = I_CO_CD<BR> AND SUBSTR(JNCO_DT,1,4) <= I_STNDYRMN<BR> AND ((LVCO_DT IS NULL) OR (LVCO_DT = '') OR SUBSTR(LVCO_DT,1,4) >= SUBSTR(v_LAST_DT,1,4))<BR> AND TEAM_CD LIKE I_TEAM_CD||'%'<BR> AND USER_CD LIKE I_USER_CD||'%'<BR> )</P>
<P> LOOP<BR> BEGIN<BR> v_30DAY := 'N';<BR> IF Rec1.CEDT < v_LAST_DT THEN</P>
<P> i_CMONTH := TRUNC(MONTHS_BETWEEN(TO_DATE(Rec1.CEDT) ,TO_DATE(Rec1.CSDT)));<BR> v_LSTEND_DT := Rec1.CEDT;<BR> v_30DAY := 'O';<BR> ELSE</P>
<P> i_CMONTH := TRUNC(MONTHS_BETWEEN(TO_DATE(v_LAST_DT) ,TO_DATE(Rec1.CSDT)));<BR> v_LSTEND_DT := v_LAST_DT;</P>
<P> END IF;</P>
<P><BR> IF Rec1.CSDT > I_STNDYRMN || '01' THEN<BR> v_FSTSTT_DT := Rec1.CSDT;<BR> v_30DAY := 'I';<BR> ELSE<BR> v_FSTSTT_DT := I_STNDYRMN || '01';<BR> END IF;</P>
<P> BEGIN<BR> --=================================<BR> --========== 실적 건수 =========<BR> --=================================</P>
<P> SELECT<BR> COUNT(CTRMNGNO) AS JUK<BR> --16 AS JUK<BR> INTO i_SILJUK<BR> FROM<BR> TBL_SUBS TSUB<BR> WHERE<BR> SUBSDT BETWEEN v_FSTSTT_DT AND v_LSTEND_DT<BR> AND USERCD = Rec1.UCD<BR> AND TEAMCD = Rec1.TEAM<BR> AND CTRSTACD IN ('01', '00');</P>
<P> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_SILJUK := 0;<BR> END;</P>
<P> BEGIN<BR> SELECT<BR> --SUM(DECODE(ATACD,'05',0,'10',0,1)),<BR> SUM(DECODE(ATACD,'05',1,'10',1,0))<BR> INTO<BR> --v_ATA,<BR> v_DAY<BR> FROM<BR> TBL_AGENT_ATA<BR> WHERE<BR> DT BETWEEN v_FSTSTT_DT AND v_LSTEND_DT<BR> AND USERCD = Rec1.UCD;<BR> --AND ATACD NOT IN ('05','10');<BR> EXCEPTION<BR> WHEN OTHERS THEN<BR> v_ATA := 0;<BR> v_DAY := 0;<BR> END;</P>
<P> BEGIN<BR> --==========================<BR> --====== 성과수당 ======<BR> --==========================<BR> SELECT<BR> NVL(TO_NUMBER(PAYRT),0) AS PAYSUNG<BR> INTO i_PAYSUNG<BR> FROM<BR> TBL_ACH_AWN_STNDINF KWA<BR> WHERE<BR> USEYN = 'Y'<BR> AND CTRRSTSCNT_MIN <= i_SILJUK<BR> AND CTRRSTSCNT_MAX >= i_SILJUK;<BR> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_PAYSUNG := 0;<BR> END;<BR> --==========================<BR> --====== 달성 수당 ======<BR> --==========================<BR> BEGIN<BR> SELECT<BR> NVL(TO_NUMBER(ATNMAWN),0)<BR> INTO i_DALSUNG<BR> FROM<BR> TBL_ATNMAWN_STNDINF<BR> WHERE<BR> USEYN = 'Y'<BR> AND CTRRSTSCNT_MIN <= i_SILJUK<BR> AND CTRRSTSCNT_MAX >= i_SILJUK;<BR> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_DALSUNG := 0;<BR> END;</P>
<P> --DBMS_OUTPUT.PUT_LINE(TO_NUMBER(SUBSTR(I_STNDYRMN, 5, 2)) MOD 3);<BR> --AND i_CMONTH > 3<BR> BEGIN<BR> IF TO_NUMBER(SUBSTR(I_STNDYRMN, 5, 2)) MOD 3 = 1 THEN</P>
<P> BEGIN<BR> v_MONTHLST := TO_CHAR(ADD_MONTHS(TO_DATE(I_STNDYRMN || '01') , -3), 'YYYYMMDD');<BR> v_MONTH := TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(I_STNDYRMN || '01') , -1)), 'YYYYMMDD');<BR> --DBMS_OUTPUT.PUT_LINE('sdf : ' || i_CMONTH || ' ' || v_MONTH);<BR> BEGIN<BR> SELECT<BR> TRUNC(SUM(CTRRSTSCNT) / 3),<BR> SUM(CTRRSTSAMT)<BR> INTO<BR> i_AVGSILJUK,<BR> i_BONUSUNG<BR> FROM<BR> TBL_USER_SAL<BR> WHERE<BR> USERCD = Rec1.UCD AND<BR> STNDYRMN between v_MONTHLST AND v_MONTH;</P>
<P> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_AVGSILJUK := 0;<BR> i_BONUSUNG := 0;<BR> END;</P>
<P> BEGIN<BR> SELECT<BR> PAYRT<BR> INTO<BR> i_BONUS<BR> FROM<BR> TBL_BNUSAWN_STNDINF<BR> WHERE<BR> USEYN = 'Y' AND<BR> i_AVGSILJUK BETWEEN AVG_CTRRSTSCNT_MIN AND AVG_CTRRSTSCNT_MAX;<BR> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_BONUS := 0;<BR> END;<BR> DBMS_OUTPUT.PUT_LINE(i_AVGSILJUK || ': ㅋㅋ : ' || i_BONUSUNG || ' ' || i_BONUS);<BR> END;</P>
<P> END IF;<BR> END;<BR>--===================================================================<BR>--== 최대 차월수 부분 / 비교 대상 차월수 보다 크면 크기를 제한한다 ==<BR>--===================================================================</P>
<P> BEGIN<BR> SELECT MAX(SRVC_PRD)<BR> INTO i_DMONTH<BR> FROM<BR> TBL_SRVC_STNDINF SRV,<BR> TBL_DRG_CD DRG</P>
<P> WHERE<BR> SRV.STNDYR = DRG.YR<BR> AND SRV.DG = DRG.DR<BR> AND I_STNDYRMN || '01' BETWEEN DRG.STRDT AND DRG.ENDDT;<BR> END;</P>
<P> IF i_CMONTH <= i_DMONTH THEN<BR> i_DMONTH := i_CMONTH;<BR> END IF;</P>
<P> BEGIN</P>
<P> SELECT<BR> SRV.SRVCAWN,<BR> SRV.LGTM_SRVCAWN,<BR> SRV.FESTIVAL_BNUS,<BR> SRV.HOLIDAY_CNT</P>
<P> INTO<BR> i_GUNSOK,<BR> i_JANGGUN,<BR> i_FESTIVAL,<BR> i_HOLIDAY<BR> FROM<BR> TBL_SRVC_STNDINF SRV,<BR> TBL_DRG_CD DRG</P>
<P> WHERE<BR> SRV.STNDYR = DRG.YR<BR> AND SRV.DG = DRG.DR<BR> AND I_STNDYRMN || '01' BETWEEN DRG.STRDT AND DRG.ENDDT<BR> AND i_DMONTH = SRV.SRVC_PRD;</P>
<P> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_GUNSOK := 0;<BR> i_JANGGUN := 0;<BR> i_FESTIVAL := 0;<BR> i_HOLIDAY := 0;<BR> END;<BR>--=============================================================================<BR>--=============================================================================<BR> BEGIN<BR> SELECT<BR> COUNT(ATACD)<BR> INTO<BR> i_EDUCNT<BR> FROM<BR> TBL_AGENT_ATA<BR> WHERE<BR> USERCD = Rec1.UCD<BR> AND ATACD = '11'<BR> AND DT BETWEEN v_MONTH AND v_MONTHLST;<BR> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_EDUCNT := 0;<BR> END;<BR> --DBMS_OUTPUT.PUT_LINE('근속: ' || i_JANGGUN);</P>
<P> IF I_EDU_KEY = 'Y' THEN<BR> i_EDUAWN := 200000;<BR> ELSE<BR> i_EDUAWN := 0;<BR> END IF;</P>
<P> BEGIN<BR> SELECT<BR> NVL(SUM(HAPPY_SAD_EVT_AMT),0)<BR> INTO<BR> i_HAPPYSAD<BR> FROM<BR> TBL_USER_HAPPY_SAD_EVT_INF<BR> WHERE<BR> USERCD = Rec1.UCD<BR> AND STNDYRMN = I_STNDYRMN;<BR> EXCEPTION<BR> WHEN OTHERS THEN<BR> i_HAPPYSAD := 0;<BR> END;<BR> --<BR> --DBMS_OUTPUT.PUT_LINE(' ' || v_30DAY);</P>
<P> IF v_DAY = 0 THEN<BR> i_R_COST := 800000;<BR> ELSE<BR> i_R_COST := ROUND((800000 / 30 * (30 - v_DAY - 1)), 0);<BR> END IF;<BR> --<BR> IF v_30DAY = 'I' THEN<BR> i_R_COST := ROUND((800000 / 30 * (30 - SUBSTR(Rec1.CSDT,7,2) + 1 - v_DAY)), 0);<BR> ELSIF v_30DAY = 'O' THEN<BR> i_R_COST := ROUND((800000 / 30 * (30 - (30 - SUBSTR(Rec1.CEDT,7,2)) + 1 - v_DAY)), 0);<BR> END IF;</P>
<P> IF I_FASTIVAL != 'Y' THEN<BR> i_FESTIVAL := 0;<BR> END IF;</P>
<P> i_TOTAL := i_R_COST + (i_R_COST * (i_PAYSUNG / 100)) + i_DALSUNG + i_BONUSUNG * (i_BONUS / 100) + i_GUNSOK + i_JANGGUN + i_GUNSOK + i_FESTIVAL + i_HAPPYSAD + i_EDUAWN;<BR> BEGIN<BR> INSERT INTO TBL_USER_SAL(<BR> STNDYRMN, --1<BR> USERCD, --2<BR> WORK_ACT_AWN, --3<BR> CTRRSTSCNT, --4<BR> CTRRSTSAMT, --5<BR> ACH_AWN, --6<BR> ATNMAWN, --7<BR> BNUSAWN, --8<BR> SRVCAWN, --9<BR> LGTM_SRVCAWN, --10<BR> FESTIVAL_BNUS, --11<BR> HAPPY_SAD_EVT_AMT, --12<BR> EDUAWN, --13<BR> ETCAWN1, --14<BR> ETCAWN2, --15<BR> ETCAWN3, --16<BR> ETCAWN4, --17<BR> ETCAWN5, --18<BR> ETCAWN6, --19<BR> SAL, --20<BR> CREDT, --21<BR> LASTUPDT) --22<BR> VALUES(<BR> I_STNDYRMN,<BR> Rec1.UCD,<BR> i_R_COST,<BR> i_SILJUK,<BR> 777,<BR> i_R_COST * (i_PAYSUNG / 100), --5<BR> i_DALSUNG,<BR> i_BONUSUNG * (i_BONUS / 100),<BR> i_GUNSOK,<BR> i_JANGGUN,<BR> i_FESTIVAL, --10<BR> i_HAPPYSAD,<BR> i_EDUAWN,<BR> 8,<BR> 8,<BR> i_CMONTH,<BR> DECODE(v_30DAY,'N',0,1),<BR> v_ATA,<BR> v_DAY,<BR> i_TOTAL,<BR> '07/04/01',<BR> SYSDATE);<BR> COMMIT;<BR> END;<BR> END;<BR> END LOOP;<BR> END;</P>
<P>END;</P>
