oracle - 用于解决使用的多值循环但出现错误

Question

ORA-06550：第 10 行，第 89 列：
PLS-00103：遇到符号“；” 当期望以下之一时：

* & - + / at mod remaining rem .. || 多组年日

    CREATE OR REPLACE PROCEDURE SALE
    (OUTLETID  IN number,itemCod IN number,START_Date IN DATE, END_DATE IN DATE,OUTLETID1  out number,itemCod1 out number,AMOUNT OUT NUMBER,Quantity OUT NUMBER,Entry_Date out date )IS 
    BEGIN 
      select l.OUTLET_ID,itemCode,
             Sum(Amount)Amount,sum(quantity)Quantity,
             i.Entry_Date  
    INTO OUTLETID1,itemCod1,
         AMOUNT,Quantity,
         Entry_Date 
    from IDSTRANSACTION i 
    join lup_outlet l on l.OUTLET_ID=i.outlet_id 
    JOIN LUP Z ON Z.ZONE_ID=L.ZONE_ID 
    join prod p on p.serial =itemCode 
    join lup_master m on  m.sup_id = p.Supplier_ID 
    where l.OUTLET_ID in (OUTLETID ) 
    and  itemCode in (itemCod) 
    and to_date(i.Entry_Date) between START_Date and END_DATE 
    group by l.OUTLET_ID,itemCode,i.Entry_Date
     END;
    ----------
    declare 
       var number; 
       var1 number; 
       var2 number;
       var3 number;
       var4 date;
    begin 
      for c in (SALE( OUTLETID  => 809,itemCod  => 128169, START_Date=>DATE '2018-01-01',end_Date=>DATE '2019-01-01',  AMOUNT => var,Quantity => var1,OUTLETID1 => var2,itemCod1 => var3,Entry_Date => var4));
     loop 
       var:=c.AMOUNT; 
       var1:=c.Quantity; 
       var2:=c.OUTLETID1; 
       var3:=c.itemCod1; 
       var4:=c.Entry_Date; 
       dbms_output.enable; 
       dbms_output.put_line(var); 
       dbms_output.enable;  
       dbms_output.put_line(var1);  
       dbms_output.enable;  
       dbms_output.put_line(var2);  
       dbms_output.enable;  
       dbms_output.put_line(var3); 
       dbms_output.enable;  
       dbms_output.put_line(var4);   
    end loop;
  End;

标签： oracleplsql

Answer 1

您缺少分号，并且在第二个过程中还有一个分号，这就是代码格式化很重要的原因：

      CREATE OR REPLACE PROCEDURE sale (
        outletid     IN           NUMBER,
        itemcod      IN           NUMBER,
        start_date   IN           DATE,
        end_date     IN           DATE,
        outletid1    OUT          NUMBER,
        itemcod1     OUT          NUMBER,
        amount       OUT          NUMBER,
        quantity     OUT          NUMBER,
        entry_date   OUT          DATE
      ) IS
      BEGIN
        SELECT
          l.outlet_id,
          itemcode,
          SUM(amount) amount,
          SUM(quantity) quantity,
          i.entry_date
        INTO
          outletid1,
          itemcod1,
          amount,
          quantity,
          entry_date
        FROM
          idstransaction   i
          JOIN lup_outlet       l ON l.outlet_id = i.outlet_id
          JOIN lup              z ON z.zone_id = l.zone_id
          JOIN prod             p ON p.serial = itemcode
          JOIN lup_master       m ON m.sup_id = p.supplier_id
        WHERE
          l.outlet_id IN (
            outletid
          )
          AND itemcode IN (
            itemcod
          )
          AND TO_DATE(i.entry_date) BETWEEN start_date AND end_date
        GROUP BY
          l.outlet_id,
          itemcode,
          i.entry_date; -- THE SEMICOLON MISSING HERE

      END;
        ----------

      DECLARE
        var    NUMBER;
        var1   NUMBER;
        var2   NUMBER;
        var3   NUMBER;
        var4   DATE;
      begin for c
      in(sale(outletid => 809, itemcod => 128169, start_date => DATE '2018-01-01', end_date => DATE '2019-01-01', amount => var, quantity
      => var1, outletid1 => var2, itemcod1 => var3, entry_date => var4)) -- ; REMOVE THE SEMICOLON HERE

      LOOP
        var := c.amount;
        var1 := c.quantity;
        var2 := c.outletid1;
        var3 := c.itemcod1;
        var4 := c.entry_date;
        dbms_output.enable;
        dbms_output.put_line(var);
        dbms_output.enable;
        dbms_output.put_line(var1);
        dbms_output.enable;
        dbms_output.put_line(var2);
        dbms_output.enable;
        dbms_output.put_line(var3);
        dbms_output.enable;
        dbms_output.put_line(var4);
      END LOOP;

      end;