Home » SQL & PL/SQL » SQL & PL/SQL » calculate order function (merged for the 5th time!)
calculate order function (merged for the 5th time!) [message #414535] Wed, 22 July 2009 08:39 Go to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Hello i am very new to oracle sql i have only been doing it for 2 weeks. i am trying to create a function that can calculate the total of an order. I have no idea how to do this. this is the database i have so far
create table Customer_order( order# NUMBER primary key ,
date_order DATE,
date_required DATE,
address VARCHAR2(30)
);

CREATE TABLE Food_order (
food_order# VARCHAR2(7) primary key,
order# number references Customer_order (order#) ,
dish# VARCHAR(5) references Dish(Dish#),
n_portions NUMBER);

CREATE TABLE Drink_order (
drink_order# VARCHAR(5)primary key,
order# number references Customer_order (order#),
drink# VARCHAR(6)references drink (drink#),
n_units NUMBER);

CREATE TABLE Dish (
dish# VARCHAR(5) primary key,
dish_name CHAR(15),
vegetarian CHAR(3),
price NUMBER);

CREATE TABLE Drink (
drink# VARCHAR(6) primary key,
drink_name CHAR(6),
drink_type CHAR(9),
price NUMBER);

Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St');
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake', 'yes', '6.00');
Insert into Dish values ('D0002', 'Fish pie', 'no', '9.00');
Insert into Dish values ('D0003', 'Steak and chips', 'no', '14.00');
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');
Insert into Dish values ('D0005', 'Ham and rice' , 'no', '7.25');
Insert into Dish values ('D0006', 'Lamb curry' , 'no', '8.50');

Insert into Drink values ('DR0001', 'Water', 'soft', '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot', '1.70');
Insert into Drink values ('DR0003', 'Wine' , 'alcoholic', '3.00');
Insert into Drink values ('DR0004', 'Beer' , 'alcoholic', '2.30');
Insert into Drink values ('DR0005', 'Tea' , 'hot' , '1.50');

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23');
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');

can someone help me please.
Re: calculate order function [message #414538 is a reply to message #414535] Wed, 22 July 2009 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well thanks for giving us create tables and inserts - always helpfull, but you haven't actually explained what it is you're totalling.
Have a look at the sum funciton.
Also can you use code tags in future please - see the orafaq forum guide if you're not sure how.

Re: calculate order function [message #414539 is a reply to message #414535] Wed, 22 July 2009 09:01 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Am trying to create a function that can get the total cost of an order then how to use it in an anonymous block when order# is input by a user

[Updated on: Wed, 22 July 2009 09:04]

Report message to a moderator

Re: calculate order function [message #414541 is a reply to message #414539] Wed, 22 July 2009 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need PL/SQL, you can do it with a SQL statement.

Regards
Michel
Re: calculate order function (merged) [message #414542 is a reply to message #414535] Wed, 22 July 2009 09:11 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
this has to be done as a function for this task then has able be called by an anonymous block when a user inputs an order#
Re: calculate order function (merged) [message #414543 is a reply to message #414535] Wed, 22 July 2009 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can still do the calculation itself in a single select statement, even if you put the select in a function.
Re: calculate order function (merged) [message #414548 is a reply to message #414542] Wed, 22 July 2009 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this is a homework you have to FIRST post what you tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: calculate order function (merged) [message #414568 is a reply to message #414535] Wed, 22 July 2009 12:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
VARCHAR should be VARCHAR2 (not that it helps with your answer, but that's the way it is defined these days).
Re: calculate order function (merged) [message #414702 is a reply to message #414568] Thu, 23 July 2009 04:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Wed, 22 July 2009 19:45
VARCHAR should be VARCHAR2 (not that it helps with your answer, but that's the way it is defined these days).

And also use VARCHAR2 instead of all the CHARs. CHAR are for compliance to ANSI standards and should not be used.
Re: calculate order function (merged) [message #414749 is a reply to message #414535] Thu, 23 July 2009 06:45 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Ths is what i have tryed but i am getting no results.

CREATE OR REPLACE FUNCTION Total_order_price 
     (p_order#  IN NUMBER) 
RETURN NUMBER 
IS 
  l_total_order_amount  NUMBER := 0; 
BEGIN 
  SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price 
  INTO   l_total_order_amount 
  FROM   customer_order co, 
         food_order fo, 
         drink_order do, 
         dish di, 
         drink dr 
  WHERE  co.order# = fo.order# 
         AND fo.dish# = di.dish# 
         AND co.order# = do.order# 
         AND do.drink# = dr.drink# 
         AND co.order# = p_order#; 
   
  RETURN l_total_order_amount; 
END; 
Re: calculate order function (merged) [message #414760 is a reply to message #414749] Thu, 23 July 2009 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but i am getting no results.

What does this mean?
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: calculate order function (merged) [message #414763 is a reply to message #414749] Thu, 23 July 2009 07:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean by "getting no results" ?
How did you call this function?
What results do you get if you execute the query in sqlplus? (without the INTO and with a fixed p_order#)
Re: calculate order function (merged) [message #414773 is a reply to message #414760] Thu, 23 July 2009 07:46 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Michel Cadot wrote on Thu, 23 July 2009 13:28
Quote:
but i am getting no results.

What does this mean?
Use SQL*Plus and copy and paste your session.

Regards
Michel


By results i am expecting how much the order cost to show when i run it and when i run the function all i get is FUNCTION total_order_price Compiled.
Re: calculate order function (merged) [message #414776 is a reply to message #414773] Thu, 23 July 2009 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
vodlink wrote on Thu, 23 July 2009 13:46
Michel Cadot wrote on Thu, 23 July 2009 13:28
Quote:
but i am getting no results.

What does this mean?
Use SQL*Plus and copy and paste your session.

Regards
Michel


By results i am expecting how much the order cost to show when i run it and when i run the function all i get is FUNCTION total_order_price Compiled.


Then presumably you are (re)creating the function rather than calling it.
Re: calculate order function (merged) [message #414778 is a reply to message #414776] Thu, 23 July 2009 07:53 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
cookiemonster wrote on Thu, 23 July 2009 13:50
vodlink wrote on Thu, 23 July 2009 13:46
Michel Cadot wrote on Thu, 23 July 2009 13:28
Quote:
but i am getting no results.

What does this mean?
Use SQL*Plus and copy and paste your session.

Regards
Michel


By results i am expecting how much the order cost to show when i run it and when i run the function all i get is FUNCTION total_order_price Compiled.


Then presumably you are (re)creating the function rather than calling it.


yes i am creating it.
Re: calculate order function (merged) [message #414780 is a reply to message #414749] Thu, 23 July 2009 07:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless it's a requirement that all orders have both food and drink, you probably want some outer joins in there.
Other than that,the only problems were the table creation scripts were in the wrong order, and some of your data had letter O instead of number 0.

It works fine when I run this:
drop table food_order;
drop table drink_order;
drop table dish;
drop table drink;
drop table customer_order;

create table Customer_order( order# NUMBER primary key , 
date_order DATE,
date_required DATE,
address VARCHAR2(30)
);

CREATE TABLE Dish (
dish# VARCHAR2(5) primary key,
dish_name CHAR(15),
vegetarian CHAR(3),
price NUMBER);

CREATE TABLE Drink (
drink# VARCHAR2(6) primary key,
drink_name CHAR(6),
drink_type CHAR(9),
price NUMBER);


CREATE TABLE Food_order (
food_order# VARCHAR2(10) primary key,
order# number references Customer_order (order#) ,
dish# VARCHAR2(5) references Dish(Dish#),
n_portions NUMBER);

CREATE TABLE Drink_order (
drink_order# VARCHAR(10) primary key,
order# number references Customer_order (order#),
drink# VARCHAR2(6)references drink (drink#),
n_units NUMBER);

Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake', 'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie', 'no', '9.00'); 
Insert into Dish values ('D0003', 'Steak and chips', 'no', '14.00'); 
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50'); 
Insert into Dish values ('D0005', 'Ham and rice' , 'no', '7.25'); 
Insert into Dish values ('D0006', 'Lamb curry' , 'no', '8.50'); 

Insert into Drink values ('DR0001', 'Water', 'soft', '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot', '1.70');
Insert into Drink values ('DR0003', 'Wine' , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer' , 'alcoholic', '2.30'); 
Insert into Drink values ('DR0005', 'Tea' , 'hot' , '1.50'); 

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DR0001', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');
       
CREATE OR REPLACE FUNCTION Total_order_price 
     (p_order#  IN NUMBER) 
RETURN NUMBER 
IS 
  l_total_order_amount  NUMBER := 0; 
BEGIN 
  SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price 
  INTO   l_total_order_amount 
  FROM   customer_order co, 
         food_order fo, 
         drink_order do, 
         dish di, 
         drink dr 
  WHERE  co.order# = fo.order# 
         AND fo.dish# = di.dish# 
         AND co.order# = do.order# 
         AND do.drink# = dr.drink# 
         AND co.order# = p_order#; 
   
  RETURN l_total_order_amount; 
END; 
/

select order#,total_order_price(order#) from customer_order;

Results:
    ORDER# TOTAL_ORDER_PRICE(ORDER#)
---------- -------------------------
         1                     622.5
         2                       679
         3                     374.5
         4                     723.5
Re: calculate order function (merged) [message #414783 is a reply to message #414780] Thu, 23 July 2009 08:02 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
JRowbottom wrote on Thu, 23 July 2009 13:58
[Edit MC: Remove useless big quoting]

Thank you very much this has help me loads

[Updated on: Thu, 23 July 2009 08:20] by Moderator

Report message to a moderator

complacated function [message #415027 is a reply to message #414535] Fri, 24 July 2009 07:45 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Hi i have a function that calculates the total of an order.
I only want it to calculate one order but it is calculating more than one. Here is the code
 CREATE OR REPLACE FUNCTION Total_order_price 
     (p_order#  IN NUMBER) 
RETURN NUMBER 
IS 
  l_total_order_amount  NUMBER := 0; 
BEGIN 
  SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price 
  INTO   l_total_order_amount 
  FROM   customer_order co, 
         food_order fo, 
         drink_order do, 
         dish di, 
         drink dr 
  WHERE  co.order# = fo.order# 
         AND fo.dish# = di.dish# 
         AND co.order# = do.order# 
         AND do.drink# = dr.drink# 
         AND co.order# = p_order#; 
   
  RETURN l_total_order_amount; 
END; 
/

select order#,total_order_price(order#) from customer_order;

Here is the data for the orders
Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');

can anyone help please?
Re: complacated function [message #415028 is a reply to message #415027] Fri, 24 July 2009 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I can tell that function will only calculate one order at a time, why do you think otherwise?
Re: complacated function [message #415031 is a reply to message #415028] Fri, 24 July 2009 07:55 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
cookiemonster wrote on Fri, 24 July 2009 13:49
As far as I can tell that function will only calculate one order at a time, why do you think otherwise?

the reason is when i run it this my result

ORDER#                 TOTAL_ORDER_PRICE(ORDER#) 
---------------------- ------------------------- 
1                      622.5                     
2                      489                       
3                      374.5                     
4                      482                       

4 rows selected

so if i only wanted total amount for orde# 000001it should only come up with one result not multipule
Re: complacated function [message #415035 is a reply to message #415031] Fri, 24 July 2009 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you still refuse to post what you do to get the result you get?
If this is the statement JRowbottom gave you then just add a WHERE clause.

Regards
Michel
Re: calculate order function (merged again) [message #415037 is a reply to message #414535] Fri, 24 July 2009 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's nothing wrong with the function, you're just calling it multiple times.
select order#,total_order_price(order#) from customer_order;


If you put a function in the select part of a query then that function will be run once for each row returned by the query.
Has to work that way.
If you want results for a single row add a where clause to your query to specify that row.
Re: calculate order function (merged again) [message #415051 is a reply to message #415037] Fri, 24 July 2009 08:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or even better, don't call it from a query at all.
Just call it from pl/Sql:
SQL> set serveroutput on size 100000
SQL> declare
  2    v_order_total  number;
  3  begin
  4    v_order_total := total_order_price(1);
  5    dbms_output.put_line('Order price: '||v_order_total);
  6  end;
  7  /
Order price: 622.5

PL/SQL procedure successfully completed.
Re: calculate order function (merged again) [message #415054 is a reply to message #414535] Fri, 24 July 2009 09:05 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
or do use SQL and dual
SELECT total_order_price(1) FROM dual;
Re: calculate order function (merged again) [message #415060 is a reply to message #415054] Fri, 24 July 2009 10:14 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
cookiemonster wrote on Fri, 24 July 2009 15:05
or do use SQL and dual
SELECT total_order_price(1) FROM dual;


thank you

This is what my final code looked like when it worked
CREATE OR REPLACE FUNCTION Total_order_price 
     (p_order#  IN NUMBER) 
RETURN NUMBER 
IS 
  l_total_order_amount  NUMBER := 0; 
BEGIN 
  SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price 
  INTO   l_total_order_amount 
  FROM   customer_order co, 
         food_order fo, 
         drink_order do, 
         dish di, 
         drink dr 
  WHERE  co.order# = fo.order# 
         AND fo.dish# = di.dish# 
         AND co.order# = do.order# 
         AND do.drink# = dr.drink# 
         AND co.order# = p_order#; 
   
  RETURN l_total_order_amount; 
END; 
/
select total_order_price(1) from dual
Re: calculate order function (merged again) [message #415235 is a reply to message #415054] Sun, 26 July 2009 10:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
cookiemonster wrote on Fri, 24 July 2009 16:05
or do use SQL and dual
SELECT total_order_price(1) FROM dual;


If the value is needed in a piece of PL/SQL, selecting it from dual, rather than assigning it to a variable directly, means there needs to be a PL/SQL -> SQL context switch.
This is a relative 'expensive' thing.

I must admit though that I did not read the entire thread, so I cannot tell for sure where the outcome is used.
calling a function in a anonymous block [message #415635 is a reply to message #414535] Tue, 28 July 2009 08:19 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Hello i have a function
CREATE OR REPLACE FUNCTION Total_order_price 
     (p_order#  IN NUMBER) 
RETURN NUMBER 
IS 
  l_total_order_amount  NUMBER := 0; 
BEGIN 
  SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price 
  INTO   l_total_order_amount 
  FROM   customer_order co, 
         food_order fo, 
         drink_order do, 
         dish di, 
         drink dr 
  WHERE  co.order# = fo.order# 
         AND fo.dish# = di.dish# 
         AND co.order# = do.order# 
         AND do.drink# = dr.drink# 
         AND co.order# = p_order#; 
   
  RETURN l_total_order_amount; 
END; 
/
select total_order_price(1) from dual;


and am now having to get the function to work in an anonymous block when user inputs a orde#. The output i am expecting is soming like ' this is the total cost of your order 622.5' heres what i got so far for the
anonymous block
SET SERVEROUTPUT ON

begin
if Total_order_price = 1 then
DBMS_OUTPUT.PUT_LINE ('The total price for your order is 622.50 ');
elsif 
Total_order_price = 2 then
DBMS_OUTPUT.PUT_LINE ('The total price for your order is 489.00 ');
elsif 
Total_order_price = 3 then
DBMS_OUTPUT.PUT_LINE ('The total price for your order is 375.50 ');
elsif
Total_order_price = 4 then
DBMS_OUTPUT.PUT_LINE ('The total price for your order is 482.00 ');
elsif
(Total_order_price IS NULL) then
DBMS_OUTPUT.PUT_LINE  ('please enter order number');
END IF;
END ; 


here is database data if it helps
create table Customer_order( order# NUMBER primary key , 
date_order    DATE,
  date_required DATE,
  address       VARCHAR2(30)
  );

CREATE  TABLE Food_order (
  food_order# VARCHAR2(7) primary key,
  order#      number references Customer_order (order#) ,
  dish#       VARCHAR2(5) references Dish(Dish#),
  n_portions  NUMBER);

CREATE  TABLE Drink_order (
  drink_order# VARCHAR2(5)primary key,
  order#       number references Customer_order (order#),
  drink#       VARCHAR2(6)references drink (drink#),
  n_units      NUMBER);

CREATE  TABLE Dish (
  dish#      VARCHAR2(5) primary key,
  dish_name  CHAR(15),
  vegetarian CHAR(3),
  price      NUMBER);

CREATE  TABLE Drink (
  drink#     VARCHAR2(6) primary key,
  drink_name CHAR(6),
  drink_type CHAR(9),
  price      NUMBER);

Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');
Re: calling a function in a anonymous block [message #415638 is a reply to message #415635] Tue, 28 July 2009 08:37 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
try studying this example, it may help
http://www.oracle-training.cc/t_easyoracle_pl_sql_calling_functions.htm

**** just ignore this post, for some reason, i did not get the updated post when the following was posted****

[Updated on: Tue, 28 July 2009 08:47]

Report message to a moderator

Re: calculate order function (merged for the 5th time!) [message #415642 is a reply to message #414535] Tue, 28 July 2009 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Gotta love Burleson - the first example in that link has an unused variable and calls the same function twice for no good reason.
And the valid_numb function does not do what it says on the tin.
Cool
Re: calculate order function (merged for the 5th time!) [message #415643 is a reply to message #415642] Tue, 28 July 2009 08:48 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
haha, looks like i have added a new problem instead of helping OP.

** maybe burleson was having some beer when he wrote this example

[Updated on: Tue, 28 July 2009 08:51]

Report message to a moderator

icon10.gif  Re: calculate order function (merged for the 5th time!) [message #415665 is a reply to message #415643] Tue, 28 July 2009 09:55 Go to previous message
vodlink
Messages: 24
Registered: July 2009
Junior Member
ajitpal.s wrote on Tue, 28 July 2009 14:48
haha, looks like i have added a new problem instead of helping OP.

** maybe burleson was having some beer when he wrote this example


I got the thing working this was my final code
DECLARE
  p_order  NUMBER := ℴ
  total_order  number ;
BEGIN
  total_order := Total_order_price(p_order) ; 
  IF ( total_order >= 0  ) THEN
    dbms_output.Put_line('the total cost of your order is : ' ||
         total_order );
  ELSE
    dbms_output.Put_line('Invalid');
  END IF;
END;
/ 
Previous Topic: How to schedule a job depending on any condition
Next Topic: bulk collect
Goto Forum:
  


Current Time: Mon Dec 05 23:52:55 CST 2016

Total time taken to generate the page: 0.09210 seconds