결론은 이런 에러가 뜹니다. ORA-01008: not all variables bound.
작성한 내용은 대충 다음과 같습니다.
<내용설명요약>
현재시점의 월별 미수현황을 구하고자하는 것이며,
예를 들어 생성월로 8월이 들어오면 해당년도의 1월부터 8월까지의
미수를 조회해서 각 달필드 m01amt....m12amt에 저장해야 합니다.
그래서 for..loop을 써서 각달에 대한 미수값을 가져왔는데
저장하는데 문제가 생겨서(저장하는 필드가 달라서) 그부분을 Dynamic SQL를 쓰게 되었습니다.
Dynamic SQL을 처음 작성한거라서 엉망이네요
고수님들께서 한수 가르쳐 주시길 진심으로 바랍니다.
CREATE OR REPLACE PROCEDURE Proc_TMP_act_no(iyymm IN VARCHAR2,
iopid IN VARCHAR2,
ostat OUT VARCHAR2)
AS
v_msid char(3);
v_main char(3);
v_crym char(6);
v_bfdt char(10) := SUBSTRB(iyymm,1,4)||'.01.01'; --전년이월의 기준일
v_post char(6);
v_item char(3);
v_pkcd char(3);
v_ramt number(12);
v_rvat number(12);
v_tamt number(12);
v_tvat number(12);
v_cnt number(12);
v_tcnt number(12);
v_icnt number(12);
v_opdt char(10);
v_opid varchar2(14);
v_fcdt v_opdt%TYPE;
v_tcdt v_opdt%TYPE;
v_frdt v_opdt%TYPE;
v_todt v_opdt%TYPE;
v_mamt varchar2(13);
v_mvat varchar2(13);
CURSOR C_MSID IS
SELECT compnycode, compnymain
FROM tcompny
WHERE compnygubn IN ('CA','MA')
ORDER BY 1;
BEGIN
SELECT TO_CHAR(sysdate,'YYYY.MM.DD') --작업일자
INTO v_opdt
FROM dual;
v_frdt := SUBSTR(v_opdt,1,7)||'.'||'01'; --이체시작일
v_todt := v_opdt; --이체종료일
v_crym := iyymm;
v_opid := iopid;
OPEN C_MSID;
LOOP
<< LOOP_START >>
FETCH C_MSID INTO v_msid, v_main;
EXIT WHEN C_MSID%NOTFOUND;
DECLARE
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
BEGIN
v_tcnt := TO_NUMBER(SUBSTR(v_crym,5,2));
FOR v_cnt IN 1..v_tcnt LOOP
IF (v_cnt < 10) THEN
v_fcdt := SUBSTRB(iyymm,1,4)||'.0'||TO_CHAR(v_cnt)||'.01';
v_tcdt := SUBSTRB(iyymm,1,4)||'.0'||TO_CHAR(v_cnt)||'.31';
v_mamt := 'actno_m0'||TO_CHAR(v_cnt)||'amt';
v_mvat := 'actno_m0'||TO_CHAR(v_cnt)||'vat';
ELSE
v_fcdt := SUBSTRB(iyymm,1,4)||'.'||TO_CHAR(v_cnt)||'.01';
v_tcdt := SUBSTRB(iyymm,1,4)||'.'||TO_CHAR(v_cnt)||'.31';
v_mamt := 'actno_m'||TO_CHAR(v_cnt)||'amt';
v_mvat := 'actno_m'||TO_CHAR(v_cnt)||'vat';
END IF;
DECLARE
CURSOR C_CSM IS --해당년도 사용료미수금
SELECT hosmstpost post,
a.item,
a.pkcd,
sum(a.ramt+a.iamt) ramt,
sum(a.rvat+a.ivat) rvat
FROM tpkggrp,
(select actcsmmsid msid, actcsmcust cust, actcsmitem item, actcsmpkcd pkcd,
actcsmramt ramt, actcsmrvat rvat, 0 iamt, 0 ivat
from tactcsm
where actcsmmsid = v_msid
and actcsmcrdt >= v_fcdt and actcsmcrdt <= v_tcdt
and (actcsmramt > 0 or actcsmrvat > 0)
union all
select actcinmsid msid, actcincust cust, actcinitem item, actcinpkcd pkcd,
0 ramt, 0 rvat, actciniamt iamt, actcinivat ivat
from tactcin,
(select actcsmmsid, actcsmcust, actcsmyymm from tactcsm
where actcsmmsid=v_msid
and actcsmcrdt >= v_fcdt and actcsmcrdt <= v_tcdt )
where actcinmsid = actcsmmsid
and actcsmcust = actcincust
and actcsmyymm = actcinyymm
and actcinmvdt >= v_frdt and actcinmvdt <= v_todt ) a,
thosmst, tcstmst
WHERE cstmstmsid = v_msid
AND cstmstmsid = hosmstmsid
AND cstmsthous = hosmsthous
AND cstmstmsid = a.msid
AND cstmstcust = a.cust
AND a.msid = pkggrpmsid
AND a.pkcd = pkggrpcode
GROUP BY hosmstpost, a.item, a.pkcd;
BEGIN
OPEN C_CSM;
LOOP
<< LOOP_C_CSM >>
FETCH C_CSM INTO v_post, v_item, v_pkcd,
v_ramt, v_rvat;
EXIT WHEN C_CSM%NOTFOUND;
SELECT COUNT(*)
INTO v_icnt
FROM t_tmp_act_no
WHERE actno_crym = v_crym
AND actno_msid = v_msid
AND actno_post = v_post
AND actno_item = v_item
AND actno_pkcd = v_pkcd;
IF (v_icnt > 0) THEN
==> 문제가 되고 있는 Dynamic SQL부분입니다.
DBMS_SQL.PARSE (cur,'UPDATE t_tmp_act_no '|| 'SET ' || v_mamt ||'= :v_ramt, '|| v_mvat || '= :v_rvat '|| 'WHERE actno_msid = :v_msid '||
'AND actno_crym = :v_crym '||
'AND actno_post = :v_post '||
'AND actno_item = :v_item '||
'AND actno_pkcd = :v_pkcd ', DBMS_SQL.NATIVE);
ELSE
DBMS_SQL.PARSE (cur,'INSERT INTO t_tmp_act_no (actno_crym, '||
'actno_msid, '||
'actno_post, '||
'actno_item, '||
'actno_pkcd, '||
v_mamt||', '||
v_mvat||', '||
'actno_opdt, '||
'actno_opid) '||
'VALUES ( :v_crym, '||
':v_msid, '||
':v_post, '||
':v_item, '||
':v_pkcd, '||
':v_ramt, '||
':v_rvat, '||
':v_opdt, '||
':v_opid) ', DBMS_SQL.NATIVE);
END IF;
==> 그래서 이부분을 추가해보았습니다. 그러면 컴파일에러가 생깁니다.
DBMS_SQL.BIND_VARIABLE(cur, 'v_ramt', v_ramt);
DBMS_SQL.BIND_VARIABLE(cur, 'v_rvat', v_rvat);
DBMS_SQL.BIND_VARIABLE(cur, 'v_msid', v_msid);
DBMS_SQL.BIND_VARIABLE(cur, 'v_crym', v_crym);
DBMS_SQL.BIND_VARIABLE(cur, 'v_post', v_post);
DBMS_SQL.BIND_VARIABLE(cur, 'v_item', v_item); DBMS_SQL.BIND_VARIABLE(cur, 'v_pkcd', v_pkcd); DBMS_SQL.BIND_VARIABLE(cur, 'v_opdt', v_opdt); DBMS_SQL.BIND_VARIABLE(cur, 'v_opid', v_opid);
==> 추가부분끝
fdbk := DBMS_SQL.EXECUTE (cur);
END LOOP;
CLOSE C_CSM;
COMMIT;
END;
END LOOP; --FOR
DBMS_SQL.CLOSE_CURSOR (cur);
END;
END LOOP; --<< LOOP_START >>
CLOSE C_MSID;
COMMIT;
ostat := 'S,Success.';
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
ostat := 'F,'||v_msid||v_crym||v_item||v_pkcd||v_post|| SQLCODE || ',' || SQLERRM || '.';
END;
/
spool off
exit;