PL/SQL STORED PROCEDURE output problem [message #427730] |
Sat, 24 October 2009 10:11  |
|
I'm trying to write my first stored procedure. I want the procedure to select a number of books orders and customer info from the joined tables and output the total with a coupon of 12.5% applied to the order results.
CREATE OR REPLACE PROCEDURE Discount
AS
CURSOR c_group_discount
IS
SELECT sum(books.cost),customers.firstname,customers.lastname
FROM orders, orderitems,customers, books
WHERE
orders.order# = orderitems.order#
AND
orders.customer# = customers.customer#
AND orderitems.isbn = books.isbn
GROUP BY
customers.firstname, customers.lastname;
BEGIN
FOR r_group_discount IN c_group_discount
LOOP
FETCH
c_group_discount.FIRSTNAME,c_group_discount.LASTNAME,sum(c_group_discount.cost) as SPENT, sum(c_group_discount.cost * 12.5) as DISCOUNT
INTO r_group_discount;
EXIT WHEN c_group_discount.FIRSTNAME%NOTFOUND;
DBMS_OUTPUT.PUTLINE(r_group_discount.DISCOUNT); //<--- does not like this line but I need to output the results of the calculation through each loop iteration
END LOOP;
CLOSE c_group_discount;
END;
|
|
|
|
Re: PL/SQL STORED PROCEDURE output problem [message #427734 is a reply to message #427733] |
Sat, 24 October 2009 10:43   |
|
This is my error:
ERROR at line 20: PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * @ % & = - + < / > at in is mod remainder not rem then
<> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
1. CREATE OR REPLACE PROCEDURE Discount
2. AS
3. CURSOR c_group_discount
4. IS
I don't really have an example of multiple lines being manipulated in a cursor and copied over( moved from the c_group_discount to r_group_discount while appying discount to the field) inside the loop.
What I want it to do is print the name of the customer, each items original cost, and then apply the discount and then print them row by row.
I guess oracle is seeing a ; when what I want is for it to do this operation. I don't know why.
|
|
|
|
Re: PL/SQL STORED PROCEDURE output problem [message #427736 is a reply to message #427734] |
Sat, 24 October 2009 10:52   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
below is what I get when trying the posted code
1 CREATE OR REPLACE PROCEDURE Discount
2 AS
3 CURSOR c_group_discount
4 IS
5 SELECT sum(books.cost),customers.firstname,customers.lastname
6 FROM orders, orderitems,customers, books
7 WHERE
8 orders.order# = orderitems.order#
9 AND
10 orders.customer# = customers.customer#
11 AND orderitems.isbn = books.isbn
12 GROUP BY
13 customers.firstname, customers.lastname;
14 BEGIN
15 FOR r_group_discount IN c_group_discount
16 LOOP
17 FETCH
18 c_group_discount.FIRSTNAME,c_group_discount.LASTNAME,sum(c_group_discount.cost) as SPENT, sum(c_group_discount.cost * 12.5) as DISCOUNT
19 INTO r_group_discount;
20 EXIT WHEN c_group_discount.FIRSTNAME%NOTFOUND;
21 DBMS_OUTPUT.PUTLINE(r_group_discount.DISCOUNT); //<--- does not like this line but I need to output the results of the calculation through each loop iteration
22 END LOOP;
23 CLOSE c_group_discount;
24* END;
25 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE DISCOUNT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
18/27 PLS-00103: Encountered the symbol "," when expecting one of the
following:
. into bulk
18/81 PLS-00103: Encountered the symbol "AS" when expecting one of the
following:
) , * & | = - + < / > at in is mod remainder not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
20/1 PLS-00103: Encountered the symbol "EXIT"
LINE/COL ERROR
-------- -----------------------------------------------------------------
20/46 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
. ( * @ % & = - + < / > at in is mod remainder not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
21/49 PLS-00103: Encountered the symbol "/" when expecting one of the
following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
LINE/COL ERROR
-------- -----------------------------------------------------------------
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
PL/SQL Reference Manual can be found at http://tahiti.oracle.com
many find coding examples can be found at http://asktom.oracle.com
First you need to find & fix syntax errors
|
|
|
Re: PL/SQL STORED PROCEDURE output problem [message #427767 is a reply to message #427730] |
Sun, 25 October 2009 04:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You use a cursor, which defines what fields to select and in what order to return them.
This means that the FETCH should not name the columns, only the fields/record to fetch the columns (as listed in the cursor) into.
So, a typical example would look like this:
create or replace procedure my_test
as
cursor c_emp
is
select last_name
, count(*) total
from emp
group by last_name
;
r_emp c_emp%rowtype;
begin
open c_emp;
fetch c_emp into r_emp;
dbms_output.put_line('Number of people with last_name = '
||r_emp.last_name
||' is: '
||r_emp.total);
close c_emp;
end;
(Note: untested code)
|
|
|
|
|
Re: PL/SQL STORED PROCEDURE output problem [message #427926 is a reply to message #427730] |
Mon, 26 October 2009 07:44   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
jtaylorok wrote on Sat, 24 October 2009 11:11
DBMS_OUTPUT.PUTLINE(r_group_discount.DISCOUNT); //<--- does not
It's put_line, not putline.
But...
Since this is a stored procedure, output has no meaning. It runs on the server. No one is going to see any output from it.
Sure, you will see output from it if you run it in a SQL*Plus session yourself, but that's nor the purpose of a stored procedure; it's for an application to execute and no one will ever see output from it.
[Updated on: Mon, 26 October 2009 07:48] Report message to a moderator
|
|
|
Re: PL/SQL STORED PROCEDURE output problem [message #427934 is a reply to message #427910] |
Mon, 26 October 2009 08:17   |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
21/49 PLS-00103: Encountered the symbol "/" when expecting one of the
following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
I don't think so
|
|
|
Re: PL/SQL STORED PROCEDURE output problem [message #427936 is a reply to message #427934] |
Mon, 26 October 2009 08:25  |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
michael_bialik wrote on Mon, 26 October 2009 13:1721/49 PLS-00103: Encountered the symbol "/" when expecting one of the
following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
I don't think so
That was from blackswan running not the OP
|
|
|