我创建了一个返回租用ID的函数，然后将其用于匿名PL / SQL程序，该程序在给定租用工具的价格和租用时间单位的情况下计算租用的总成本。 （例如，租用了4个小时的工具$ 5 =总共需要支付$ 20）。

创建函数的代码是：

create or replace function return_rentalID (rental_id rental.rid%type)
     return integer is
     returned_rentalID integer;

begin
     select rid
     into returned_rentalID
     from rental
     where rental_id = rental.rid;
     return returned_rentalID;
exception
     when NO_DATA_FOUND
         then dbms_output.put_line('Rental ID Not Found');
         return -1;
end;

调用该函数的代码是：

declare
     returned_rentalID integer;
     units rental.num_unit%type;
     unit_price tool_price.price%type;
     rental_id rental.rid%type;

begin
     returned_rentalID := return_count(1);

     if returned_rentalID > 0 then

         select num_unit
         into units
         from rental
         where rid = returned_rentalID;

         select tp.price
         into unit_price
         from tool_price tp,rental r
         where tp.tid = r.tid
         and tp.tuid = r.tuid
         and rid = returned_rentalID;

         dbms_output.put_line('The Total Cost of this rental is $' || unit_price * units);

     else dbms_output.put_line('Rental ID Not Found');
     end if;
end;

如果我使用出租ID 1作为参数或任何不存在的出租ID，则程序可以正常工作；但是，对于所有其他有效的租金ID，通过租金ID 1时，我仍然得到相同的总数。

有人知道这是什么原因吗？我知道使用函数来返回租金ID（returned_rentalID）是多余的，没有必要；但是，这是使您习惯于创建和调用函数的练习，因此这是必需的。

我真的非常感谢您提供的任何见解！

以下是我正在使用的表的一些示例代码：

create table tool_price
(tid int,--- too id 
tuid int,--- time unit id
price number,primary key(tid,tuid),foreign key(tid) references tool,foreign key(tuid) references time_unit
);

--- mower,$20 per 4 hours. $30 per day 
insert into tool_price values(1,1,5.00);
insert into tool_price values(1,2,30);
insert into tool_price values(1,3,120);

insert into tool_price values(2,7.00);
insert into tool_price values(2,40);
insert into tool_price values(2,160);

insert into tool_price values(3,6.00);
insert into tool_price values(3,32);
insert into tool_price values(3,125);

insert into tool_price values(4,7.00);
insert into tool_price values(4,40);
insert into tool_price values(4,160);

create table rental 
(
rid int,--- rental id 
cid int,--- customer id 
tid int,--- tool id
tuid int,--- time unit id
num_unit int,--- number of units,if unit = 1 hour,num_unit = 5 means 5 hours. 
start_time timestamp,-- rental start time 
end_time timestamp,--- suppose rental end_time 
return_time timestamp,--- actual return time 
credit_card varchar(20),total number,--- total charge 
primary key (rid),foreign key(cid) references cust,foreign key(tuid) references time_unit
);
-- John rented a mower for 4 hours,insert into rental values(1,4,timestamp '2019-08-01 
10:00:00.00',null,'123456789',null);

-- susan rented a small carpet cleaner for one day 
insert into rental values(2,timestamp '2019-08-11 
10:00:00.00',null);

--susan also rented a small mower for 5 hours,before 8 am case  
insert into rental values(3,5,timestamp '2019-08-12 
21:00:00.00',null);

--david also rented a small carpet cleaner for 4 hours,after 10 pm case
insert into rental values(4,timestamp '2019-08-13 
19:00:00.00','12222828828',null);