PLS-00394:fetch 语句的 INTO 列表中的值数量错误 [英] PLS-00394: Wrong number of values in the INTO list of a fetch statement

问题描述

这是我在存储过程中创建游标的尝试

This is my attempt in creating a cursor in a stored procedure

   --Second Stored Procedure--
 CREATE OR REPLACE PROCEDURE sp_GetDiscountedRate (DiscountCode IN    
VARCHAR2,Percentage IN NUMBER, ReserveDate IN DATE)
 IS  --Code declaration section--
--variables to store column values returned from select into
 pPassengerID            VARCHAR2(10);
 pFirst                  VARCHAR2(20);
 pMiddle                 VARCHAR2(20);
 pLast                   VARCHAR2(20);
 pPassengerType          CHAR (1);
 uUSMilitaryID           VARCHAR (8);
 uMilitaryBranch         VARCHAR2 (20);
 uMilitaryDiscountCode   VARCHAR2(8);
 rFlightNumber           VARCHAR2(6);
 rReservationCost        NUMBER(6);
 rReservationDate        DATE;
 --Declare Cursor
            CURSOR cur_USMilitary IS
            --Query cursor will point to results 
  SELECT P.PassengerID, P.First, P.Middle, P.Last, P.PassengerType,    
  U.USMilitaryID, U.MilitaryBranch,   
  U.MilitaryDiscountCode, R.FlightNumber, R.ReservationCost,   
  R.ReservationDate,
  CASE U.MilitaryDiscountCode WHEN DiscountCode THEN   
  Percentage*R.ReservationCost 
  ELSE R.ReservationCost END "REVISED_RESERVATION_COST"
  FROM PASSENGER P, US_Military U, RESERVATION R
  WHERE P.PassengerID = U.MPassengerID
  AND P.PassengerID = R.PassengerID
  AND U.MilitaryDiscountCode = DiscountCode
  AND R.ReservationDate = ReserveDate;

       --Start Execution section--
BEGIN 
      --Open Cursor
     OPEN cur_USMilitary; --  open cursor for use   
     --loop to display each record returned by cursor 
     --Use PL/SQL language control or loop to display each record pointed by cursor 
    LOOP
        --Fetch cursor data
        FETCH cur_USMilitary INTO pPassengerID,  
       pFirst,pMiddle,pLast,pPassengerType,

uUSMilitaryID,uMilitaryBranch,uMilitaryDiscountCode,rFlightNumber,
rReservationCost,rReservationDate;
        EXIT WHEN cur_USMilitary%NOTFOUND;
        --Display each record
        --Displaying the results
DBMS_OUTPUT.PUT_LINE ('CUSTOMER INFORMATION: ');
DBMS_OUTPUT.PUT_LINE ('The PassengerID is:  ' ||pPassengerID);
DBMS_OUTPUT.PUT_LINE ('First Name of passenger is:  ' ||pFirst);
DBMS_OUTPUT.PUT_LINE ('Middle Name of passenger is:  ' ||pMiddle);
DBMS_OUTPUT.PUT_LINE ('Last Name of passenger is:  ' ||pLast);
DBMS_OUTPUT.PUT_LINE ('Passenger Type of customer is: ' ||pPassengerType);
DBMS_OUTPUT.PUT_LINE ('US Military ID of Passenger is:  ' ||uUSMilitaryID);
DBMS_OUTPUT.PUT_LINE ('Military Branch of passenger is:  ' ||uMilitaryBranch);
DBMS_OUTPUT.PUT_LINE ('Military Discount code of passenger is:  ' ||uMilitaryDiscountCode);
DBMS_OUTPUT.PUT_LINE ('Flight number of passenger is:  ' ||rFlightNumber);
DBMS_OUTPUT.PUT_LINE ('Reservation Cost of passenger is:  ' ||rReservationCost);
DBMS_OUTPUT.PUT_LINE ('Reservation Date of passenger is:  ' ||rReservationDate);

END LOOP; 



    CLOSE cur_USMilitary; --close cursor

 END sp_GetDiscountedRate;

我收到此错误:

Error(36,9): PL/SQL: SQL Statement ignored
Error(36,9): PLS-00394: wrong number of values in the INTO list of a FETCH statement

我完全理解错误.我检查了列数，在我看来它们与查询中的列数相匹配.我还检查了数据类型以确保它是正确的.

I fully understand the error. I checked the number of columns and it looks like to me that they match the number of columns within the query. I've also checked the datatypes to make sure it was correct.

推荐答案

这就是当你把事情复杂化时会发生的情况，恐怕.我认为不需要所有这些变量和相应的 fetch into 很难保持同步.为什么不只是:

This is what happens when you overcomplicate things, I'm afraid. I don't see any need for all those variables and the corresponding fetch into that is hard to keep in sync. Why not just:

create or replace procedure sp_getdiscountedrate
    ( discountcode in varchar2
    , percentage   in number
    , reservedate  in date )
is
begin
    for r in (
        select p.passengerid
             , p.first
             , p.middle
             , p.last
             , p.passengertype
             , u.usmilitaryid
             , u.militarybranch
             , u.militarydiscountcode
             , r.flightnumber
             , r.reservationcost
             , r.reservationdate
             , case u.militarydiscountcode
                   when discountcode then percentage * r.reservationcost
                   else r.reservationcost
               end as revised_reservation_cost
        from   passenger p
               join us_military u
                    on   u.mpassengerid = p.passengerid
               join reservation r
                    on   r.passengerid = p.passengerid
                    and  r.discountcode = u.militarydiscountcode
                    and  r.reservedate = r.reservationdate
    )
    loop
        dbms_output.put_line('CUSTOMER INFORMATION:');
        dbms_output.put_line('The PassengerID is:  ' || r.passengerid);
        dbms_output.put_line('First Name of passenger is:  ' || r.first);
        dbms_output.put_line('Middle Name of passenger is:  ' || r.middle);
        dbms_output.put_line('Last Name of passenger is:  ' || r.last);
        dbms_output.put_line('Passenger Type of customer is: ' || r.passengertype);
        dbms_output.put_line('US Military ID of Passenger is:  ' || r.usmilitaryid);
        dbms_output.put_line('Military Branch of passenger is:  ' || r.militarybranch);
        dbms_output.put_line('Military Discount code of passenger is:  ' || r.militarydiscountcode);
        dbms_output.put_line('Flight number of passenger is:  ' || r.flightnumber);
        dbms_output.put_line('Reservation Cost of passenger is:  ' || r.reservationcost);
        dbms_output.put_line('Reservation Date of passenger is:  ' || r.reservationdate);
        dbms_output.put_line('Revised reservation cost is:  ' || r.revised_reservation_cost);
    end loop;

end sp_getdiscountedrate;

(未经测试)

这篇关于PLS-00394:fetch 语句的 INTO 列表中的值数量错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！