Creating and calling a function in a pl/sql program

Question

I created a function to return a rental ID that is then used in an anonymous PL/SQL program that calculates the total cost of the rental given the price of the tool rented and the number of time units it was rented for. (ex. $5 tool rented for 4 hours = $20 rental total).

The code to create the function is:

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;

The code to call the function is:

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;

The program works properly if I use rental ID 1 as the parameter or any rental ID that doesn't exist; however, for all of the other valid rental ID's, I still get the same total is when passing rental ID 1.

Does anyone have an idea of what's causing this? I know that using a function to return the rental ID (returned_rentalID) is redundant and not necessary; however, this is an exercise to become comfortable creating and calling functions so it's necessary.

I really, truly appreciate any insight provided!

Below is some sample code for the tables I'm using:

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,1,7.00);
insert into tool_price values(2,2,40);
insert into tool_price values(2,3,160);

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

insert into tool_price values(4,1,7.00);
insert into tool_price values(4,2,40);
insert into tool_price values(4,3,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(tid) references tool,
foreign key(tuid) references time_unit
);
-- John rented a mower for 4 hours, 
insert into rental values(1,1,1,1,4,timestamp '2019-08-01 
10:00:00.00',null,null,'123456789',null);

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

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

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

Brian Leach · Accepted Answer · 2019-11-07 23:33:36Z

1

First of all, thanks for including sample data. I changed your second select statement into an ANSI join and received different values for 1,2,and3:

   SELECT tp.price
      INTO unit_price
      FROM tool_price  tp
           INNER JOIN rental r ON tp.tid = r.tid AND tp.tuid = r.tuid
     WHERE rid = returned_rentalid;

I could not fully duplicate your results as there is no return_count function included in your example.

answered Nov 7, 2019 at 23:33

Brian Leach

2,1111 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rash8151 Over a year ago

Thank you. Can you provide a little bit on insight on including a return_count function?

Gro Over a year ago

return_count(1) was a typo perhaps, it should be return_rentalID(1)

Rash8151 Over a year ago

Oh, yes! It was.

Gro · Accepted Answer · 2019-11-08 00:04:53Z

1

Writing a separate function to check existence of id may be an overkill, you may want to simplify your block and get rid of the function with following

declare
     v_total number(10,2);   
begin
     select tp.price * r.num_unit into v_total  
     from rental r
     join tool_price tp on (tp.tid = r.tid and tp.tuid = r.tuid)
     where r.rid = p_rental_id;  -- rental_id for total is to be calculated

     dbms_output.put_line('The Total Cost of this rental is $' || v_total);
exception 
    when NO_DATA_FOUND then
        dbms_output.put_line('No data found');
    when others then    
        raise;
end

In regards to data, check what you have in the tables, if required you can run the sql directly (by removing into clause) from any client.

Note: I don't have DB handy to run and check, there may be minor syntax error but the concept and construct is valid.

answered Nov 8, 2019 at 0:04

Gro

1,6931 gold badge14 silver badges22 bronze badges

1 Comment

Rash8151 Over a year ago

Thank you for providing this alternative. Is there any benefit to choosing one way over another? I assumed the simpler code is easier to interpret by others and check for errors?

Collectives™ on Stack Overflow

Creating and calling a function in a pl/sql program

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related