mysql - PL/SQL 错误 ORA-01403 : No data found. 未收到预期结果

Question

基本上，我在 oracle 中创建了一个业务报告(查询)，以从 FREQUENT_FLYER 表中提取有关里程余额 >10000 但小于 20000 且 MileageExpDate 大于 10 月的乘客的信息2017 年 1 日。

SELECT F.FPASSENGERID, P.First, P.Last, F.FREQFLYERNUM, F.FREQFLYERMILEAGE, 
F.MILEAGEBALANCE, F.MILEAGEEXPDATE
FROM FREQUENT_FLYER F
INNER JOIN PASSENGER P
ON F.FPassengerID = P.PassengerID
WHERE MileageBalance >= 10000 AND F.MILEAGEBALANCE <= 20000
AND MileageExpDate > '01/OCT/2017';

这里是该查询的结果。它应该只返回一条记录。

我正在尝试使用存储过程，并且决定将该查询转换为存储过程

--First Stored Procedure--
CREATE OR REPLACE PROCEDURE sp_GetMileageBalance 
IS  --Code declaration section--
    --variables to store column values returned from select into
     fPassengerID        VARCHAR2(10);
     pFirst              VARCHAR2(20);
     pLast               VARCHAR2(20);
     fFreqflyernum       NUMBER (10);
     fFreqflyerMileage   NUMBER (7);
     fMileagebalance     NUMBER (7);
     fMileageExpDate     DATE;
     MileageExpDate      DATE;
     MileageStart        NUMBER (7);
     MileageEnd          NUMBER (7);
     MileageBalance      NUMBER (7);

BEGIN --Code execution section
              --executing select into Query assign to variable
              MileageExpDate := '01/OCT/2017';
              MileageStart := 10000;
              MileageEnd :=  20000;
SELECT F.FPASSENGERID, P.First, P.Last, F.FREQFLYERNUM, F.FREQFLYERMILEAGE,  
F.MILEAGEBALANCE, F.MILEAGEEXPDATE
INTO  fPassengerID,  
pFirst,pLast,fFreqflyernum,fFreqflyerMileage,fMileagebalance,fMileageExpDate
FROM FREQUENT_FLYER F
INNER JOIN PASSENGER P
ON F.FPassengerID = P.PassengerID
WHERE MileageBalance >= MileageStart AND F.MILEAGEBALANCE <= MileageEnd
AND MileageExpDate > MileageExpDate;

--Displaying the results
DBMS_OUTPUT.PUT_LINE ('CUSTOMER INFORMATION: ');
DBMS_OUTPUT.PUT_LINE ('The Frequent Flyer PassengerID is:  ' ||fPassengerID);
DBMS_OUTPUT.PUT_LINE ('First Name of passenger is:  ' ||pFirst);
DBMS_OUTPUT.PUT_LINE ('Last Name of passenger is:  ' ||pLast);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer number of passenger is: ' ||fFreqflyernum);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer Mileage of Passenger is:  ' ||fFreqflyerMileage);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer Balance of passenger is:  ' ||MileageBalance );
DBMS_OUTPUT.PUT_LINE ('Mileage expiration date of passenger is:  ' ||MileageExpDate);

END sp_GetMileageBalance;

我期望存储过程返回基于该记录的信息。相反，这就是我得到的。

Procedure SP_GETMILEAGEBALANCE compiled


Error starting at line : 57 in command -
BEGIN sp_GetMileageBalance; END;
Error report -
ORA-01403: no data found
ORA-06512: at "SYSTEM.SP_GETMILEAGEBALANCE", line 29
ORA-06512: at line 1
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch

我不知道为什么会得到这个结果，而我应该得到与查询相同的信息。

Answer 1

这可能是问题所在:

drop table test_table;
create table test_table ( charcol varchar2(10), numcol integer);
insert into test_table select ' 15000',15000 from dual;

select * from test_table where charcol>'10000'; --no record
select * from test_table where charcol>10000; --1 record