pls-00341:游标“cur2”的声明不完整或格式不正确| | pl/sql:ora-00918:列定义不明确

djmepvbi  于 2021-07-24  发布在  Java
关注(0)|答案(0)|浏览(344)

我写了一个程序,试图将值从源表插入到目标表,为了执行大量数据,我使用了批量收集。
在执行下面的过程时,它的showing cursor cus2声明不完整,并且它的showing cursor中的某些列定义不明确。

create or replace PROCEDURE TEST2 (
    p_array_size IN NUMBER
) IS

CURSOR cur1 IS SELECT DISTINCT
        *
                        FROM
        test

        CURSOR cur3 IS SELECT * FROM test;

        CURSOR cur2(BND_TYPE cr_loan_prima_rate_orig.bndng_typ%TYPE, BND_VAL cr_loan_prima_rate_orig.bndng_val%TYPE, FINANCIAL_INST_ID cr_loan_prima_rate_orig.financial_institution_id%TYPE, 
          PRDCT_ID cr_loan_prima_rate_orig.prdct_id%TYPE,PRDCT_SUB_ID cr_loan_prima_rate_orig.prdct_sub_id%TYPE, INSTRUMENT_ID cr_loan_prima_rate_orig.instrmnt_id%TYPE) IS SELECT
                                d.*
                                FROM
                                test1 d,
                                (
                                    SELECT
                                    b.prdct_id,

                                    FROM
                                        test2 a,
                                        test1 b
                                    WHERE
                                        a.ir_id = b.ir_id
                                        AND   a.price_component_id = b.price_component_id
                                        AND   a.financial_institution_id = b.financial_institution_id
                                    GROUP BY
                                        b.prdct_id,

                                ) e
                                WHERE
                                d.prdct_id = e.prdct_id
                                AND   d.bndng_typ = BND_TYPE
                                AND   d.bndng_val = BND_VAL
                                AND   d.financial_institution_id = FINANCIAL_INST_ID
                                AND   d.prdct_id = PRDCT_ID
                                AND   d.prdct_sub_id = PRDCT_SUB_ID
                                AND   d.instrmnt_id = INSTRUMENT_ID  ;

        TYPE loan_data_tbl IS TABLE OF cur1%rowtype INDEX BY PLS_INTEGER;
        loan_data loan_data_tbl;

        TYPE loanrate_tbl IS TABLE OF cur2%rowtype INDEX BY BINARY_INTEGER;
        loan_rate loanrate_tbl;

        BEGIN 

            DECLARE
                v_noofDays NUMBER:=0;
                currentDt DATE;
            BEGIN 
                    SELECT * INTO  currentDt FROM dt;

                    BEGIN

                        IF cur1%Isopen Then
                            Close cur1;
                        End IF;

                        IF cur2%Isopen Then
                            Close cur2;
                        End IF;

                        OPEN cur1;
                        LOOP
                                FETCH cur1 BULK COLLECT INTO loan_data LIMIT p_array_size;
                                EXIT WHEN loan_data.COUNT = 0;

                                FOR i IN 1..loan_data.COUNT         
                                LOOP

                                             OPEN cur3;

                                    OPEN cur2(loan_data(i).bndng_typ, loan_data(i).bndng_val,loan_data(i).financial_institution_id,
                                    loan_data(i).prdct_id, loan_data(i).prdct_sub_id, loan_data(i).instrmnt_id);
                                             loop

                                    FETCH cur2 BULK COLLECT INTO loan_rate LIMIT p_array_size;
                                    EXIT WHEN loan_rate.COUNT = 0;

                                    FOR j IN 1..loan_rate.COUNT
                                    LOOP 

                                        IF(cur3.POS_NUM = loan_data(i).POS_NUM AND cur3.POS_TYPE = loan_data(i).POS_TYPE    
                                            AND cur3.PRICE_COMPONENT_ID = loan_rate(j).PRICE_COMPONENT_ID
                                            AND cur3.RPRTD_TILL_DT = loan_data(i).RPRTD_TILL_DT) THEN 

                                            update test SET SEQ_NUM=1,
                                                WHERE SEQ_NUM=2;

                                        ELSE 

                                            INSERT INTO test VALUES (
                                            ....
                                            ....

                                        );

                                             END IF;
                                        COMMIT;                     
                                    END LOOP;
                                END LOOP;

                            CLOSE cur2;
                        CLOSE cur1;

                    END LOOP;
                         END LOOP;

            CLOSE cur1;
        END;
    END;    
End ;
/

对我来说一切都很好,找不到确切的错误。有人能帮我解决这个问题吗。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题