gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 18:15:12 26 4
gpt4 key购买 nike

基本上,我在 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';

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

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

--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

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

最佳答案

这可能是问题所在:

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

关于mysql - PL/SQL 错误 ORA-01403 : No data found. 未收到预期结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47086685/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com