Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Review My 'Get_Total' Function - PL/SQL

Posted on 2009-12-26
6
Medium Priority
?
284 Views
Last Modified: 2013-12-18
This code will be applied to a hotel database. II got a short pl/sql code that calculates the total price a customer has to pay.

Total price is calculated based on how many nights the client has booked the room for.

v_nights represents number of nights the client's has booked.

Don't worry about exception, i'm more concerned about the body.

Regards,
F-J-K
CREATE OR REPLACE FUNCTION get_total(customer_num IN customer.CustomerID%TYPE)
RETURN NUMBER IS
	   v_price	   MotelRoom.PRICE%TYPE;
	   v_nights	   INTEGER;
	   total_price NUMBER;
BEGIN
	   SELECT mr.price, TO_DATE(checkout, 'dd') - TO_DATE(checkin, 'dd') INTO v_price, v_nights
	   FROM MotelRoom mr JOIN BookedRoom b
	   ON	mr.roomID = b.roomID
	   WHERE b.customerID = customer_num;

	   total_price := v_price * v_nights;
	   
	   RETURN total_price;
	   
EXCEPTION
	   WHEN OTHERS THEN
	   		RAISE_APPLICATION_ERROR(-20001, 'There was an error when get_total function was run. ' || SQLCODE || ', ' || SUBSTR(SQLERRM, 1, 200)); 	  
END;
/

Open in new window

0
Comment
Question by:F-J-K
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26125604
what is the problem you run into?
0
 
LVL 1

Author Comment

by:F-J-K
ID: 26125609
I just would like you to confirm whether my PL/SQL is written correctly or not. Unfortunately, i do not have the access to test the code currently...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26125643
TO_DATE(checkout, 'dd') - TO_DATE(checkin, 'dd')

seems incorrect ...
http://www.techonthenet.com/oracle/functions/to_date.php

day of month substracted will give you bad results when checkin is 30th and checkout on 4th... you will own some money to the customer.

instead, presuming that both fields are datetime, simply:
( TRUNC(checkout) - TRUNC(checkin))
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:F-J-K
ID: 26125837
Hm, ok. I will check it out when i get access. By the way, checkin and checkout most probably is of type
TIMESTAMP
0
 
LVL 14

Accepted Solution

by:
shru_0409 earned 2000 total points
ID: 26129666
CREATE OR REPLACE FUNCTION get_total(
   customer_num IN customer.customerid%TYPE
)
   RETURN NUMBER
IS
   v_price motelroom.price%TYPE;
   v_nights INTEGER;
   total_price NUMBER;
BEGIN
   SELECT mr.price, to_number(to_char(checkout,'J'))- to_number(to_char(checkin,'J'))
     INTO v_price, v_nights
     FROM motelroom mr JOIN bookedroom b ON mr.roomid = b.roomid
    WHERE b.customerid = customer_num;

   total_price := v_price * v_nights;
   
   RETURN total_price;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error
                   (-20001,
                       'There was an error when get_total function was run. '
                    || SQLCODE
                    || ', '
                    || SUBSTR(SQLERRM, 1, 200));
END;
/

try this
0
 
LVL 1

Author Closing Comment

by:F-J-K
ID: 31670099
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question