Q&A

  • PL/SQL로 Dynamic SQL을 써서 프로시저를 작성했는데...
결론은 이런 에러가 뜹니다. 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;
0  COMMENTS