|
아무리 봐도 문법은 맞는거 같은데 자꾸 에러가 납니다.
아래 소스 검토 부탁드립니다.
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE EAMR4007HIGHMONTH_INSERT
(
IN_METERNUM IN VARCHAR2,
IN_METERDATE IN CHAR,
IN_BRANCHCD IN VARCHAR2,
IN_METERMGRNUM IN NUMBER
)
IS
ST_VALID NUMBER(25,12);
ST_NULLITYLAG NUMBER(25,12);
ST_NULLITYLEAD NUMBER(25,12);
ST_APPARENT NUMBER(25,12);
ST_FACTOR NUMBER(25,12);
ST_LPVALUE NUMBER(25,12);
ST_TOTALLPVALUE NUMBER(25,12);
ST_METERDATE CHAR(14);
ST_CNT INTEGER;
ST_LASTDAY CHAR(2);
ST_QUERY VARCHAR2(200);
ST_COLUM VARCHAR2(20);
ST_COLUM2 VARCHAR2(20);
ST_TEMP VARCHAR2(1000);
BEGIN
/**********************************************************************
- 시간 사용량 구하기
**********************************************************************/
SELECT
SUM(VALID) INTO ST_VALID
FROM EAMR4001HIGHSOURCE
WHERE METERMGRNUM = IN_METERMGRNUM
AND YYYY = SUBSTR(IN_METERDATE,0,4)
AND MM = SUBSTR(IN_METERDATE,5,2);
SELECT
SUM(NULLITYLAG) INTO ST_NULLITYLAG
FROM EAMR4001HIGHSOURCE
WHERE METERMGRNUM = IN_METERMGRNUM
AND YYYY = SUBSTR(IN_METERDATE,0,4)
AND MM = SUBSTR(IN_METERDATE,5,2);
SELECT
SUM(NULLITYLEAD) INTO ST_NULLITYLEAD
FROM EAMR4001HIGHSOURCE
WHERE METERMGRNUM = IN_METERMGRNUM
AND YYYY = SUBSTR(IN_METERDATE,0,4)
AND MM = SUBSTR(IN_METERDATE,5,2);
SELECT
SUM(APPARENT) INTO ST_APPARENT
FROM EAMR4001HIGHSOURCE
WHERE METERMGRNUM = IN_METERMGRNUM
AND YYYY = SUBSTR(IN_METERDATE,0,4)
AND MM = SUBSTR(IN_METERDATE,5,2);
SELECT
MAX(METERDATE) INTO ST_METERDATE
FROM EAMR4001HIGHSOURCE
WHERE METERMGRNUM = IN_METERMGRNUM
AND YYYY = SUBSTR(IN_METERDATE,0,4)
AND MM = SUBSTR(IN_METERDATE,5,2);
SELECT
MAX(LPVALUE) INTO ST_LPVALUE
FROM EAMR4003HIGHHOUR
WHERE METERMGRNUM = IN_METERMGRNUM
AND YYYY = SUBSTR(IN_METERDATE,0,4)
AND MM = SUBSTR(IN_METERDATE,5,2);
-- 역율 구하기
ST_FACTOR := ST_VALID / ST_APPARENT;
MERGE INTO EAMR4007HIGHMONTH
USING DUAL
ON ( METERNUM = IN_METERNUM AND YYYYMM = SUBSTR(IN_METERDATE,0,6))
WHEN MATCHED THEN
UPDATE SET
VALID = ST_VALID,
NULLITYLAG = ST_NULLITYLAG,
NULLITYLEAD = ST_NULLITYLEAD,
APPARENT = ST_APPARENT,
FACTOR = ST_FACTOR,
LASTCHECKDATE = ST_METERDATE,
EDITDATE = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
WHEN NOT MATCHED THEN
INSERT (
METERNUM,
YYYY,
MM,
YYYYMM,
VALID,
NULLITYLAG,
NULLITYLEAD,
APPARENT,
FACTOR,
INDATE,
METERMGRNUM,
MONTHDATANUM,
LPVALUE,
BRANCHCD
) VALUES (
IN_METERNUM,
SUBSTR(IN_METERDATE,0,4),
SUBSTR(IN_METERDATE,5,2),
SUBSTR(IN_METERDATE,0,6),
ST_VALID,
ST_NULLITYLAG,
ST_NULLITYLEAD,
ST_APPARENT,
ST_FACTOR,
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
IN_METERMGRNUM,
MONTHDATANUM.NEXTVAL,
ST_LPVALUE,
IN_BRANCHCD
);
-- 일별월통계 등록 하기
SELECT SUM(VALID) INTO ST_TOTALLPVALUE FROM EAMR4007HIGHMONTH WHERE METERNUM = IN_METERNUM AND YYYY = SUBSTR(IN_METERDATE,0,4) AND MM = SUBSTR(IN_METERDATE,5,2);
ST_COLUM := 'M' || SUBSTR(IN_METERDATE,8,2);
ST_COLUM2 := ST_COLUM || 'VALUE';
MERGE INTO EAMR4103HIGHMONTHYEAR
USING DUAL
ON ( METERMGRNUM = IN_METERMGRNUM AND YYYY = SUBSTR(IN_METERDATE,0,4))
WHEN MATCHED THEN
ST_TEMP := 'UPDATE EAMR4103HIGHMONTHYEAR SET '
|| ST_COLUM ||' = '|| TO_CHAR(ST_VALID,'9999999999999999.999999')
||', '|| ST_COLUM2 ||'='|| TO_CHAR(ST_LPVALUE,'9999999999999999999999999.999999999999')
||', TOTAL = '|| TO_CHAR(ST_TOTALLPVALUE,'9999999999999999999999999.999999999999')
||' WHERE '
|| 'METERMGRNUM = '|| '''' || TO_CHAR(IN_METERMGRNUM,'9999999999') || ''''
|| ' AND YYYY = '|| '''' || SUBSTR(IN_METERDATE,0,4) || ''''
WHEN NOT MATCHED THEN
ST_TEMP := 'INSERT INTO EAMR4103HIGHMONTHYEAR '
|| '(METERMGRNUM,YYYY,'|| ST_COLUM || ', ' || ST_COLUM2 || ',TOTAL,BRANCHCD,INDATE) '
|| 'VALUE ('
|| TO_CHAR(IN_METERMGRNUM,'9999999999')
||','''
|| SUBSTR(IN_METERDATE,0,4)
||''','
|| TO_CHAR(ST_VALID,'9999999999999999.999999')
||','
|| TO_CHAR(ST_VALUE,'9999999999999999999999999.999999999999')
||','
|| TO_CHAR(ST_VALID,'9999999999999999999999999.999999999999')
||','''
|| IN_BRANCHCD
||''','''
|| TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
||''')';
EXECUTE IMMEDIATE ST_TEMP;
END EAMR4007HIGHMONTH_INSERT;
-------------------------------------------------------------------------- -----------------------------------------
빨강색 부분에서 문법이 오류가 났다고 합니다.
뭐가 문제 인가요 ㅜㅜ
|
