anonymous block to print a procedure

Question

i have this procedure and i have to create an anonymous block to print all the orders at same time. here is the procedure

create or replace procedure Item_bill
is
  sSQLstr VARCHAR2(1000);
  type refcur is ref cursor;
  rcur refcur;
  stmt1 VARCHAR2(300);
BEGIN 
  sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
              t.n_portions*p. price
              from Customer_order s,food_order t, Dish p
              where s.order#=t.order#
              and t.dish#=p.dish#'; 

  OPEN rcur FOR sSQLstr;
  LOOP
    FETCH rcur INTO stmt1;
    EXIT WHEN rcur%NOTFOUND;
    dbms_output.put_line(stmt1);
  END LOOP;
END;

begin
  -- Call the procedure
  item_bill();
end;

If it helps here is table creation and data for table

--table creation
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(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);


-- data insert
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');

So how do I get an anonymous block to print an itemised bill for each order? I am using Oracle PL/SQL and working in SQL Developer. Here is an example of what I mean:

6 portions of steak and chips at 14 pounds: 84 pounds
4 portions of pasta bake at 6 pounds: 24 pounds
3 portions of stuffed peppers at 11.50 pounds: 34.5 pounds

total food cost for order 000001: 142.50 pounds

13 units of water at 1 pound: 13 pounds
13 units of coffee at 1.70 pounds: 22.1 pounds
13 units of beer at 2.30 pounds: 29.90 pounds

total drink cost for order 000001: 65 pounds

total cost for order 000001: 207.50 pounds

I need something like that to appear for each order.

Answer 1

OK, first, I wouldn't be using the cursor method you are using. I'd declare the cursor explicitly, not using an 'open for'. I'd also include the name of the table in the cursor.

Second, one you have that, you can loop through the cursor like so for i in mycursor LOOP portions=i.portions; dish_name = i.dish_name;

Using this method, you can build the string you want that you are using in your cursor already. Actually, you can explicitly declare the cursor to have that text as well.

So, I now have a cursor in which I can refer to each and every bit of data individually.

From there, I can also have a total_for_this_type variable that tracks the totals for food orders, for example, and then drink orders.

so, much simplified:

declare 
total number;
cursor mycursor is
select 'FOOD' ordertype, price from food
UNION
select 'DRINK' ordertype, price from drinks
order by 1;
BEGIN
   for i in mycursor
   LOOP
      if i.ordertype <> currtype then
--          output the total for whatever type of order using the variable 'total'
         currtype=i.ordertype;
      end if;
      -- build your output string of portions etc.
      total = total + i.price;
      currtype = i.ordertype;
   END LOOP;
END;

Answer 2

This SQL will get you started:

SQL> var customer_order# varchar2(5)
SQL> exec :customer_order# := '00001'

PL/SQL-procedure is geslaagd.

SQL> with orderlines as
  2  ( select to_char(fo.n_portions) || ' portions of ' ||
  3           lower(d.dish_name) || ' at ' ||
  4           to_char(d.price) || ' pounds' text
  5         , 'F' food_or_drink
  6         , co.order#
  7         , fo.n_portions * d.price total_price
  8      from food_order fo
  9         , customer_order co
 10         , dish d
 11     where fo.order# = co.order#
 12       and fo.dish#  = d.dish#
 13     union all
 14    select to_char(do.n_units) || ' units of ' ||
 15           lower(d.drink_name) || ' at ' ||
 16           to_char(d.price) || ' pounds'
 17         , 'D'
 18         , co.order#
 19         , do.n_units * d.price
 20      from drink_order do
 21         , customer_order co
 22         , drink d
 23     where do.order# = co.order#
 24       and do.drink# = d.drink#
 25       and co.order# = :customer_order#
 26  )
 27  select case grouping_id(food_or_drink,text)
 28         when 0 then text
 29         when 1 then 'total ' || decode(food_or_drink,'D','drink','food') || ' cost for order ' || order#
 30         when 3 then 'total cost for order ' || order#
 31         end text
 32       , to_char(sum(total_price),'fm990D00') price
 33    from orderlines
 34   where order# = :customer_order#
 35   group by rollup((order#,food_or_drink),text)
 36  /

TEXT                                               PRICE
-------------------------------------------------- -------
13 units of water  at 1 pounds                     13.00
13 units of beer   at 2.3 pounds                   29.90
13 units of coffee at 1.7 pounds                   22.10
total drink cost for order 1                       65.00
4 portions of pasta bake      at 6 pounds          24.00
6 portions of steak and chips at 14 pounds         84.00
3 portions of stuffed peppers at 11.5 pounds       34.50
total food cost for order 1                        142.50
total cost for order                               207.50

9 rijen zijn geselecteerd.

Regards, Rob.

Answer 3

In PL/SQL:

SQL> declare
  2    l_previous_group pls_integer := 0;
  3  begin
  4    for r in
  5    ( with orderlines as
  6      ( select to_char(fo.n_portions) || ' portions of ' ||
  7               lower(d.dish_name) text
  8             , d.price unit_price
  9             , 'F' food_or_drink
 10             , co.order#
 11             , fo.n_portions * d.price total_price
 12          from food_order fo
 13             , customer_order co
 14             , dish d
 15         where fo.order# = co.order#
 16           and fo.dish#  = d.dish#
 17         union all
 18        select to_char(do.n_units) || ' units of ' ||
 19               lower(d.drink_name)
 20             , d.price
 21             , 'D'
 22             , co.order#
 23             , do.n_units * d.price
 24          from drink_order do
 25             , customer_order co
 26             , drink d
 27         where do.order# = co.order#
 28           and do.drink# = d.drink#
 29           and co.order# = :customer_order#
 30      )
 31      select case grouping_id(order#,food_or_drink,text)
 32             when 0 then rpad(text,32) || ' at ' || to_char(unit_price,'90D00') || ' pounds'
 33             when 1 then 'total ' || decode(food_or_drink,'D','drink','food') || ' cost for order ' || order#
 34             when 3 then 'total cost for order ' || order#
 35             when 7 then 'total cost for all orders'
 36             end text
 37           , to_char(sum(total_price),'990D00') price
 38           , grouping_id(order#,food_or_drink,text) grp
 39        from orderlines
 40       group by rollup(order#,food_or_drink,(text,unit_price))
 41       order by order# nulls last
 42           , food_or_drink desc nulls last
 43           , grp
 44    )
 45    loop
 46      if r.grp != l_previous_group
 47      then
 48        dbms_output.new_line;
 49      end if;
 50      dbms_output.put_line(rpad(r.text,50) || '  ' || r.price);
 51      l_previous_group := r.grp;
 52    end loop;
 53  end;
 54  /
3 portions of stuffed peppers    at  11.50 pounds     34.50
6 portions of steak and chips    at  14.00 pounds     84.00
4 portions of pasta bake         at   6.00 pounds     24.00

total food cost for order 1                          142.50

13 units of water                at   1.00 pounds     13.00
13 units of coffee               at   1.70 pounds     22.10
13 units of beer                 at   2.30 pounds     29.90

total drink cost for order 1                          65.00

total cost for order 1                               207.50

10 portions of fish pie          at   9.00 pounds     90.00
10 portions of pasta bake        at   6.00 pounds     60.00

total food cost for order 2                          150.00

total cost for order 2                               150.00

35 portions of fish pie          at   9.00 pounds    315.00

total food cost for order 3                          315.00

total cost for order 3                               315.00

23 portions of fish pie          at   9.00 pounds    207.00

total food cost for order 4                          207.00

total cost for order 4                               207.00

total cost for all orders                            879.50

PL/SQL-procedure is geslaagd.

Regards, Rob.