存储过程的游标问题

Question

在下面的 oracle sql 包主体中，我收到“SQL 语句被忽略”错误，并且无法确定原因。请参阅下面语句中PROCEDURE updateLoansInLdcTab中dborLoans_cursor的SELECT语句：

create or replace
PACKAGE body PK_DBORUFT_SYNC AS
PROCEDURE mainProg(v_beginDate DATE, v_endDate DATE) IS
BEGIN
    updateLoansInLdcTab(v_beginDate,v_endDate);
END mainProg;

FUNCTION searchForLdcToUpdate(v_beginDate DATE, v_endDate DATE) RETURN type_ref_cursor IS   
    v_ldcLoan type_ref_cursor;
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
     OPEN v_ldcLoan FOR
        SELECT loan_id
        FROM ldc.ldc_rel_tab
        WHERE loan_id NOT IN
                (SELECT loan_id
                 FROM dbor.vw_ldc_data dbor
                 WHERE dbor.closing_agent     IS NOT NULL
                             AND dbor.closing_agent_phone IS NOT NULL
                             AND dbor.lock_expiration_date > sysdate)
                     AND TO_CHAR(request_date, 'MM/DD/YYYY') >= v_beginDate
                     AND TO_CHAR(request_date, 'MM/DD/YYYY') <= v_endDate; 

    RETURN v_ldcLoan;   
END searchForLdcToUpdate;

PROCEDURE updateLoansInLdcTab(v_beginDate DATE, v_endDate DATE) is
TYPE dborLdcData IS TABLE OF dbor.vw_ldc_data%ROWTYPE;
v_ldcLoan_type_rec type_ref_cursor;
    v_ldcLoanCursor_type ldcLoanCursor_type;
dborReference           dborLdcData;
v_LDC_LOANID            VARCHAR2(10);
    v_LOAN_ID                           VARCHAR2(10);
v_BANKLINE                          VARCHAR2(20);
    v_CHANNEL                           VARCHAR2(20);
    v_PROPERTY_TYPE                 VARCHAR2(10);
    v_STATE                                 VARCHAR2(2);
    v_STREET_NAME                   VARCHAR2(64);
    v_FIRST_NAME                        VARCHAR2(64);
    v_LAST_NAME                         VARCHAR2(64);
    v_CLOSING_AGENT                 VARCHAR2(50);
    v_CLOSING_AGENT_PHONE   VARCHAR2(15);         
v_REGION_CODE           VARCHAR2(20);        
v_CLPP_FLAG             VARCHAR2(1);         
v_INSTRUMENT_NAME       VARCHAR2(30);        
v_BROKER_OFFICER        VARCHAR2(30);          
v_COST_CENTER           VARCHAR2(10);           
v_PREPARED_BY           VARCHAR2(30);          
v_BUYPRICE              NUMBER(9,4);           
v_SRP                   NUMBER(8,3);           
v_TOTAL_BUYPRICE        NUMBER(9,4);         
v_TOTAL_SRP             NUMBER(8,3);
v_BRANCH_NAME                   VARCHAR2(30); 
v_LOCK_EFFECTIVEDATE    DATE;
dbor_count              NUMBER;

    CURSOR dborLoans_cursor IS
        SELECT P.loan_id,
                 P.property_type,
                 P.state,
                 P.street_name,
                 P.close_date,
                 P.loan_purpose,
                 P.borrower_last_name,
                 P.borrower_first_name,
                 P.closing_agent,
               P.closing_agent_phone,
                 P.region_code,
                 P.clpp,
                 P.instrument_name,
                 P.broker_officer,
               P.lock_effective_date,
                 P.channel,
                 NVL(P.buyprice, 0) buyPrice
         FROM dbor.vw_ldc_data P
                    LEFT JOIN dbor.wlnprogram W
                             ON upper(P.instrument_name) = W.ln_pgm
                    LEFT JOIN
                         (SELECT A.loan_id FROM ldc.ldc_rel_tab A WHERE A.ldc_status='LDC_PENDING'
                         ) pend ON pend.loan_id = p.loan_id
                    LEFT JOIN
                        (SELECT DISTINCT A.loan_id
                         FROM ldc.ldc_rel_tab A, ldc.ldc_request_rel_tab B
                         WHERE A.ldc_status   IN ('LDC_PENDING', 'DISBURSED','COMPLETE','RECON_PENDING','SUBMITTED','DISBURSEPAYOFF','VOIDREQUEST','FUNDS_REQUESTED')
                                    AND A.ldc_id          = B.ldc_id
                                    AND (B.funding_reason = 'DL Payoff' OR B.funding_reason   ='Original Disbursement')
                        ) disbursed ON disbursed.loan_Id = p.loan_id
                    LEFT JOIN
                        (SELECT name, phone, agent_id, street1, city, zip, state FROM dbor.WCLOS_AGNT) wagnt
                            ON wagnt.agent_id=p.loan_id
                    LEFT JOIN
                        (SELECT loan,
                                        company_name,
                                        phone_phn,
                                        street,
                                        city,
                                        zip_code,
                                        state
                            FROM DBOR.WLOAN_PARTY
                            WHERE type='4'
                         ) wloan ON wloan.loan   =p.loan_id
            WHERE P.closing_agent     IS NOT NULL
                        AND p.loan_id not in (SELECT loan_id FROM ldc.ldc_rel_tab)
                        AND P.closing_agent_phone IS NOT NULL
                        AND P.lock_expiration_date > sysdate ;

  v_dborLdcData dbor.vw_ldc_data%ROWTYPE;

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    dborReference := dborLdcData();
    v_ldcLoanCursor_type := searchForLdcToUpdate(v_beginDate, v_endDate);
    dbor_count := 0;



WHILE dborLoans_cursor%FOUND LOOP
  FETCH dborLoans_cursor INTO v_dborLdcData;
  dbor_count := dbor_count + 1;
        v_LOAN_ID := v_dborLdcData.LOAN_ID;
        v_PROPERTY_TYPE := v_dborLdcData.property_type;
        v_STATE := v_dborLdcData.state;
        v_STREET_NAME := v_dborLdcData.street_name;

        v_LAST_NAME  := v_dborLdcData.borrower_last_name;
  v_FIRST_NAME := v_dborLdcData.borrower_first_name;
        v_CLOSING_AGENT  := v_dborLdcData.closing_agent;
        v_CLOSING_AGENT_PHONE := v_dborLdcData.closing_agent_phone;

        v_CLPP_FLAG := v_dborLdcData.clpp;
        v_INSTRUMENT_NAME := v_dborLdcData.INSTRUMENT_NAME;
        v_BROKER_OFFICER := v_dborLdcData.BROKER_OFFICER;
        v_CHANNEL := v_dborLdcData.CHANNEL;
        EXECUTE IMMEDIATE 'SELECT region_code FROM dbor.Branch WHERE branch_name = '||v_dborLdcData.CHANNEL INTO v_REGION_CODE;
        EXECUTE IMMEDIATE 'SELECT cost_center FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_COST_CENTER;
        EXECUTE IMMEDIATE 'SELECT bankline FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_BANKLINE;
        v_LOCK_EFFECTIVEDATE := v_dborLdcData.lock_effective_date;
        v_BUYPRICE := v_dborLdcData.buyPrice;


        LOOP 
    FETCH v_ldcLoan_type_rec INTO v_LDC_LOANID;
            EXECUTE IMMEDIATE
    'update ldc.ldc_rel_tabtest 
    set loan_id = ' ||''''|| v_LOAN_ID||''''||
        ',bankline = ' ||''''||  v_BANKLINE||''''||
            ',channel = ' ||''''||v_CHANNEL||''''||
            ',PROPERTY_TYPE= ' ||''''||v_PROPERTY_TYPE||''''||
            ',STATE = ' ||''''||v_STATE||''''||
            ',STREET_NAME = ' ||''''||v_STREET_NAME||''''||
            ',BORROWER_NAME = ' ||''''||v_LAST_NAME||','||''''||v_FIRST_NAME||''''||
            ',CLOSING_AGENT = ' ||''''||v_CLOSING_AGENT||''''||
            ',CLOSING_AGENT_PHONE = ' ||''''||v_CLOSING_AGENT_PHONE||''''||             
                ',REGION_CODE = ' ||''''||v_REGION_CODE||''''||                     
                ',CLPP_FLAG = ' ||''''||v_CLPP_FLAG||''''||                      
                ',INSTRUMENT_NAME = ' ||''''||v_INSTRUMENT_NAME||''''||               
                ',BROKER_OFFICER = ' ||''''||v_BROKER_OFFICER||''''||                  
                ',COST_CENTER = ' ||''''||v_COST_CENTER||''''||       
                ',BUYPRICE = ' ||v_BUYPRICE ||
    ' where loan_id = ' ||v_LDC_LOANID;
        END LOOP;
    END LOOP;
EXCEPTION
    WHEN OTHERS
      THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END updateLoansInLdcTab;
END PK_DBORUFT_SYNC;

Answer 1

快速浏览一下（没有运行它）后，我会发现下面的行有错误，因为您没有在连接的值周围加上引号：

EXECUTE IMMEDIATE 'SELECT region_code FROM dbor.Branch WHERE branch_name = '||v_dborLdcData.CHANNEL INTO v_REGION_CODE;
EXECUTE IMMEDIATE 'SELECT cost_center FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_COST_CENTER;
EXECUTE IMMEDIATE 'SELECT bankline FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_BANKLINE;

但是，从上面的评论来看，您似乎已经让它工作了。

这段代码中更大的问题是为什么要使用立即执行？您不需要这样做，更糟糕的是您创建了不可扩展的代码，因为上面的三个立即执行语句并且大更新不使用绑定变量。在 PLSQL 中，如果您避免使用execute_immediate，则根本不需要担心绑定，PLSQL 会自动为您完成这一切。

尝试将立即执行选择转换为类似的内容：

select region_code
into v_region_code
from dbor.branch
where branch_name = v_dborLdcData.CHANNEL;

这将有助于查找并修复绑定变量问题。然后对更新做同样的事情：

update ldc.ldc_rel_tabtest 
set loan_id = v_LOAN_ID
    bankline = v_bank_line
    ...
    ...
where loan_id = v_LDC_LOANID;

代码会更简单，更容易发现 SQL 中的错误并且更具可扩展性。

Answer 2

问题是由于缺少 DBOR.WLOAN_PARTY 上的授权造成的。