时间戳

首页 > 解决方案 > PLS-00428 PLSQL 过程中的 INTO 子句预期错误

database - PLS-00428 PLSQL 过程中的 INTO 子句预期错误

问题描述

我在 PLSQL 中有一个过程。我在编译它们时遇到了这些错误：

PROCEDURE FCBSADM.EXTRACT_MONTHLY_DATA 的编译错误

错误：PLS-00428：此 SELECT 语句中应有 INTO 子句行：11 文本：SELECT DISTINCT intl_prod_id,bill_date

错误：提示：参数 'pid_billdate' 已声明但从未在 'extract_monthly_data' 中使用 行：1 文本：创建或替换过程 extract_monthly_data(pid_billdate DATE) 是

此外，如何在 pid_billdate 和 ab.bill_date 之间建立连接？我愿意接受建议。从现在开始谢谢你。

    create or replace procedure extract_monthly_data(pid_billdate DATE) is
v_cnt      NUMBER;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER; -- whatever the table is 
l_prod_ids   prod_ids_t;  
begin

   execute immediate 'truncate table gpu';
                  
   v_cnt := 0;
   
   SELECT DISTINCT intl_prod_id,bill_date

      FROM apld_bill_rt abr,
           acct_bill ab
      WHERE     abr.cdate > SYSDATE - 1000 
          AND abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          ORDER BY bill_date;
    
    FOR indx IN 1 .. l_prod_ids.COUNT
    loop  
      INSERT INTO GPU
         SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba2.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id 
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1, 
                bill_acct ba2, 
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id 
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID 
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id  
                AND bap.intl_prod_id = abr.intl_prod_id 
                AND ABR.CHRG_TP = 'INSTALLMENT' 
                AND abr.cdate > SYSDATE - 5
                AND ABR.intl_prod_id = l_prod_ids(indx)
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id(+);                    

      v_cnt := v_cnt + 1;

      IF MOD (v_cnt, 1000) = 0
      THEN
         COMMIT;
      END IF;
  
    end loop;
    COMMIT;

end;

这是它的最后一个版本

    create or replace procedure extract_monthly_data(pid_billdate DATE) is
v_cnt      NUMBER;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER; -- whatever the table is 
l_prod_ids   prod_ids_t;  
begin

   execute immediate 'truncate table gpu';
                  
   v_cnt := 0;
   
   SELECT DISTINCT abr.intl_prod_id, ab.bill_date BULK COLLECT INTO l_prod_ids
      FROM apld_bill_rt abr,
           acct_bill ab
      WHERE     abr.cdate > SYSDATE - 1000 
          AND abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          ORDER BY bill_date;
    
    FOR indx IN 1 .. l_prod_ids.COUNT
    loop  
      INSERT INTO GPU
      SELECT AB.ACCT_BILL_ID,
            AB.BILL_NO,
            AB.INV_ID,
            AB.BILL_DATE,
            ba2.bill_acct_id,
            ba1.bill_acct_id parent_bill_acct_id,
            AB.DUE_DATE,
            PG.CMPG_ID,
            ABR.NET_AMT,
            AB.DUE_AMT,
            P.PROD_NUM,
            pds.DST_ID,
            ABR.DESCR,
            p.intl_prod_id 
       FROM apld_bill_rt abr
            JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID 
            JOIN FCBSADM.PROD_DST pds ON 
            JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id 

                AND bap.intl_prod_id = pds.intl_prod_id 
                AND bap.intl_prod_id = p.intl_prod_id
            JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
            JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id 
            JOIN bill_acct ba2 ON ba1.intl_bill_acct_id = ba2.parent_bill_acct_id 
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id  
            LEFT OUTER JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
            WHERE ABR.CHRG_TP = 'INSTALLMENT' 
            AND abr.cdate > SYSDATE - 5
            AND ABR.intl_prod_id =ANY (
              SELECT intl_prod_id -- no DISTINCT, no ORDER BY
              FROM apld_bill_rt abr
                JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
              WHERE abr.cdate > SYSDATE - 1000 
                AND abr.CHRG_TP = 'INSTALLMENT'
                AND abr.TAX_CATG_ID = 'NOTAX'
              );                    

      v_cnt := v_cnt + 1;

      IF MOD (v_cnt, 1000) = 0
      THEN
         COMMIT;
      END IF;
  
    end loop;
    COMMIT;

end;

这是插入表

    create table GPU
(
  acct_bill_id        NUMBER(16),
  bill_no             VARCHAR2(30 CHAR),
  inv_id              VARCHAR2(20 CHAR),
  bill_date           DATE,
  bill_acct_id        NUMBER(38),
  parent_bill_acct_id NUMBER(38),
  due_date            DATE,
  cmpg_id             NUMBER,
  net_amt             NUMBER(16,2),
  due_amt             NUMBER(16,2),
  prod_num            VARCHAR2(32 CHAR),
  dst_id              NUMBER(22) not null,
  descr               VARCHAR2(100 CHAR),
  intl_prod_id        NUMBER(14) not null
);

标签： databaseoracleplsqlrdbms

解决方案

必须是这样的：

SELECT DISTINCT intl_prod_id
BULK COLLECT INTO l_prod_ids
FROM ...

甚至更好的是，使用现代 ANSI 连接语法并且不使用任何循环。将类似于：

create or replace procedure extract_monthly_data AS
BEGIN
   execute immediate 'truncate table gpu';

   INSERT INTO GPU
     SELECT AB.ACCT_BILL_ID,
            AB.BILL_NO,
            AB.INV_ID,
            AB.BILL_DATE,
            ba2.bill_acct_id,
            ba1.bill_acct_id parent_bill_acct_id,
            AB.DUE_DATE,
            PG.CMPG_ID,
            ABR.NET_AMT,
            AB.DUE_AMT,
            P.PROD_NUM,
            pds.DST_ID,
            ABR.DESCR,
            p.intl_prod_id 
       FROM apld_bill_rt abr
            JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID 
            JOIN FCBSADM.PROD_DST pds ON 
            JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id 
                AND bap.intl_prod_id = pds.intl_prod_id 
                AND bap.intl_prod_id = p.intl_prod_id
            JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
            JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id 
            JOIN bill_acct ba2 ON ba1.intl_bill_acct_id = ba2.parent_bill_acct_id 
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id  
            LEFT OUTER JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
      WHERE ABR.CHRG_TP = 'INSTALLMENT' 
            AND abr.cdate > SYSDATE - 5
            AND ABR.intl_prod_id =ANY (
              SELECT intl_prod_id -- no DISTINCT, no ORDER BY
              FROM apld_bill_rt abr
                JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
              WHERE abr.cdate > SYSDATE - 1000 
                AND abr.CHRG_TP = 'INSTALLMENT'
                AND abr.TAX_CATG_ID = 'NOTAX'
              );   
    COMMIT;
END;

不明白你的第二个问题，也许你在找

       AND (ABR.intl_prod_id, pid_billdate) =ANY (
              SELECT intl_prod_id, bill_date
              FROM apld_bill_rt abr
              ...
              )

推荐阅读