박형근 wrote:

> 저는 델파이 초보입니다.

> stored proc 에 대해 꼭배우고 싶은데 서적에 자세히 쓰인게 없네요

> 자세한 예제 부탁드립니다.





일별합계내는 프로시져 소스 올려드리져.. 참고하세염.. ^^







CREATE PROCEDURE PS_DAY_TOTAL

(I_SDATE IN DATE, /* 시작일자 */

I_EDATE IN DATE) /* 종료일자 */

AS

-- I_SDATE DATE DEFAULT '2000/12/31'; /* 시작일자 */

-- I_EDATE DATE DEFAULT '2000/12/31'; /* 종료일자 */

ERROR_CODE VARCHAR2(1) DEFAULT 'N'; /*에러메세지*/

INWON_CNT1 NUMBER(3) DEFAULT 0; /* 일반인원 */

INWON_CNT2 NUMBER(3) DEFAULT 0; /* 후불인원 */

INWON_CNT3 NUMBER(3) DEFAULT 0; /* 학생인원 */

INWON_CNT4 NUMBER(3) DEFAULT 0; /* 원호1인원 */

INWON_CNT5 NUMBER(3) DEFAULT 0; /* 원호2인원 */

TEMP_EXP_DATE DATE; /* 등록일자 */

TEMP_SA_CODE VARCHAR2(4); /* 영업소코드 */

TEMP_WAY_CODE VARCHAR2(4); /* 노선코드 */

TEMP_BUS_GBN VARCHAR2(1); /* 고속버스구분 */

TEMP_PRICE1 NUMBER(5) DEFAULT 0; /* 승차운임 */

TEMP_PRICE2 NUMBER(5) DEFAULT 0; /* 승차운임 30% */

TEMP_PRICE3 NUMBER(5) DEFAULT 0; /* 승차운임 50% */

INWON_AMT1 NUMBER(8) DEFAULT 0; /* 일반인원금액 */

INWON_AMT2 NUMBER(8) DEFAULT 0; /* 후불인원금액 */

INWON_AMT3 NUMBER(8) DEFAULT 0; /* 학생인원금액 */

INWON_AMT4 NUMBER(8) DEFAULT 0; /* 원호1인원금액 */

INWON_AMT5 NUMBER(8) DEFAULT 0; /* 원호2인원금액 */

TEMP_WAY_CNT1 NUMBER(2) DEFAULT 0; /* 정규편도수 */

TEMP_WAY_CNT2 NUMBER(2) DEFAULT 0; /* 심야편도수 */

TEMP_WAY_CNT3 NUMBER(2) DEFAULT 0; /* 임시편도수 */

TEMP_WAY_CNT4 NUMBER(2) DEFAULT 0; /* 기타편도수 */

TEMP_AMOUNT NUMBER(8) DEFAULT 0; /* 현금승차액 */

TEMP_TOTAL NUMBER(9) DEFAULT 0; /* 합계금액 */

BEGIN

DECLARE

CURSOR T_CUS IS

SELECT EXP_DATE,

SA_CODE,

WAY_CODE,

BUS_GBN,

SUM(AMOUNT),

SUM(INWON1),

SUM(INWON2),

SUM(INWON3),

SUM(INWON4),

SUM(INWON5),

SUM(DECODE(BUS_GBN1, 1, 1, 0)),

SUM(DECODE(BUS_GBN1, 2, 1, 0)),

SUM(DECODE(BUS_GBN1, 3, 1, 0)),

SUM(DECODE(BUS_GBN1, 1, 0,

DECODE(BUS_GBN1, 2, 0,

DECODE(BUS_GBN1, 3, 0, 1))))

FROM SM05_EXP_MAST

WHERE EXP_DATE BETWEEN I_SDATE AND I_EDATE

GROUP BY EXP_DATE,

SA_CODE,

WAY_CODE,

BUS_GBN;

BEGIN

dbms_output.enable;

OPEN T_CUS;

LOOP

FETCH T_CUS INTO TEMP_EXP_DATE, TEMP_SA_CODE, TEMP_WAY_CODE, TEMP_BUS_GBN, TEMP_AMOUNT,

INWON_CNT1, INWON_CNT2, INWON_CNT3, INWON_CNT4, INWON_CNT5,

TEMP_WAY_CNT1, TEMP_WAY_CNT2, TEMP_WAY_CNT3, TEMP_WAY_CNT4;

EXIT WHEN T_CUS%NOTFOUND;

BEGIN

SELECT MAX(PRICE1), MAX(PRICE2), MAX(PRICE3)

INTO TEMP_PRICE1, TEMP_PRICE2, TEMP_PRICE3

FROM SM02_TIK_MAST

WHERE SA_CODE = TEMP_SA_CODE

AND BUS_CODE = TEMP_WAY_CODE

AND TIKET_TYPE = TEMP_BUS_GBN || '1';

EXCEPTION

WHEN NO_DATA_FOUND THEN

BEGIN

ERROR_CODE := 'Y';

EXIT;

END;

END;

INWON_AMT1 := NVL(INWON_CNT1, 0) * NVL(TEMP_PRICE1, 0);

INWON_AMT2 := NVL(INWON_CNT2, 0) * NVL(TEMP_PRICE1, 0);

INWON_AMT3 := NVL(INWON_CNT3, 0) * NVL(TEMP_PRICE3, 0);

INWON_AMT4 := NVL(INWON_CNT4, 0) * NVL(TEMP_PRICE2, 0);

INWON_AMT5 := NVL(INWON_CNT5, 0) * NVL(TEMP_PRICE3, 0);

TEMP_TOTAL := NVL(INWON_AMT1, 0) + NVL(INWON_AMT2, 0) + NVL(INWON_AMT3, 0) +

NVL(INWON_AMT4, 0) + NVL(INWON_AMT5, 0) + NVL(TEMP_AMOUNT, 0);

BEGIN

INSERT INTO SM06_DAY_EXP

(EXP_DATE, SA_CODE, WAY_CODE, BUS_GBN,

INWON1, AMOUNT1, INWON2, AMOUNT2, INWON3, AMOUNT3,

INWON4, AMOUNT4, INWON5, AMOUNT5,

WAY_CNT1, WAY_CNT2, WAY_CNT3, WAY_CNT4, AMOUNT6, AMT_HAP)

VALUES

(TEMP_EXP_DATE, TEMP_SA_CODE, TEMP_WAY_CODE, NVL(TEMP_BUS_GBN, '9'),

INWON_CNT1, INWON_AMT1, INWON_CNT2, INWON_AMT2, INWON_CNT3, INWON_AMT3,

INWON_CNT4, INWON_AMT4, INWON_CNT5, INWON_AMT5,

TEMP_WAY_CNT1, TEMP_WAY_CNT2, TEMP_WAY_CNT3, TEMP_WAY_CNT4, TEMP_AMOUNT, TEMP_TOTAL);

EXCEPTION

WHEN NO_DATA_FOUND THEN

BEGIN

ERROR_CODE := 'Y';

EXIT;

END;

END;

-- NULL;

-- EXIT;

END LOOP;

CLOSE T_CUS;

COMMIT WORK;

END;

END;