Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure cursor and loop (ORACLE EXPRESS 10G, WIN XP)
stored procedure cursor and loop [message #430060] Sat, 07 November 2009 21:19 Go to next message
jtaylorok
Messages: 4
Registered: October 2009
Location: USA
Junior Member

I am trying to display a sum from a query in a cursor. My book does not have any examples of how to spit out the cursor's results.

I am reading in a publishers name and grabbing the data, discount it and putting it into a cursor. I just want to display the result of my calculation to the screen.

The code compiles without errors but it just hangs there when I try to EXEC the stored procedure.

SET SERVEROUTPUT ON

CREATE OR REPLACE
PROCEDURE PUBDOG
AS

BEGIN

DECLARE

V_PNAME VARCHAR2(60);
V_TOTAL NUMBER;

CURSOR C_PUBLISHER IS

select sum( orderitems.quantity * books.cost) INTO v_TOTAL from
books, publisher, orderitems, orders
where books.pubid = publisher.pubid
and
books.isbn = orderitems.isbn
and
orderitems.order# = orders.order#
and
publisher.name=V_PNAME;


BEGIN
OPEN C_PUBLISHER;


DBMS_OUTPUT.PUT_LINE
('THE TOTAL AMOUNT FOR '||
V_PNAME||' IS '||V_TOTAL);

IF NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO RESULT AVAILABLE');
EXIT;
END IF;

CLOSE C_PUBLISHER;
END;
END PUBDOG;
/
Re: stored procedure cursor and loop [message #430061 is a reply to message #430060] Sat, 07 November 2009 21:35 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
CREATE OR REPLACE PROCEDURE Pubdog
AS
BEGIN
  DECLARE
    v_pname  VARCHAR2(60);
    v_total  NUMBER;
    CURSOR c_publisher IS
      SELECT Sum(orderitems.quantity * books.cost)
      INTO   v_total
      FROM   books,
             publisher,
             orderitems,
             orders
      WHERE  books.pubid = publisher.pubid
             AND books.isbn = orderitems.isbn
             AND orderitems.order# = orders.order#
             AND publisher.NAME = v_pname;
  BEGIN
    OPEN c_publisher;
    
    dbms_output.Put_line('THE TOTAL AMOUNT FOR '
                         ||v_pname
                         ||' IS '
                         ||v_total);
    
    IF no_data_found THEN
      dbms_output.Put_line('NO RESULT AVAILABLE');
      
      EXIT;
    END IF;
    
    CLOSE c_publisher;
  END;
END pubdog;
/ 

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Close all sessions from client to DB and try again
Re: stored procedure cursor and loop [message #430063 is a reply to message #430061] Sat, 07 November 2009 21:53 Go to previous messageGo to next message
jtaylorok
Messages: 4
Registered: October 2009
Location: USA
Junior Member

I have tried this and it is giving me a warning but the line number does not make sense that it is reporting.


SQL> show errors;
Errors for PROCEDURE PUBDOG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
32/1 PL/SQL: Statement ignored
32/4 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

Re: stored procedure cursor and loop [message #430064 is a reply to message #430063] Sat, 07 November 2009 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
Please follow Posting Guidelines!

  1  CREATE OR REPLACE PROCEDURE Pubdog
  2  AS
  3  BEGIN
  4    DECLARE
  5	 v_pname  VARCHAR2(60);
  6	 v_total  NUMBER;
  7	 CURSOR c_publisher IS
  8	   SELECT Sum(orderitems.quantity * books.cost)
  9	   INTO   v_total
 10	   FROM   books,
 11		  publisher,
 12		  orderitems,
 13		  orders
 14	   WHERE  books.pubid = publisher.pubid
 15		  AND books.isbn = orderitems.isbn
 16		  AND orderitems.order# = orders.order#
 17		  AND publisher.NAME = v_pname;
 18    BEGIN
 19	 OPEN c_publisher;
 20	 dbms_output.Put_line('THE TOTAL AMOUNT FOR '
 21			      ||v_pname
 22			      ||' IS '
 23			      ||v_total);
 24	 IF no_data_found THEN
 25	   dbms_output.Put_line('NO RESULT AVAILABLE');
 26	   EXIT;
 27	 END IF;
 28	 CLOSE c_publisher;
 29    END;
 30* END pubdog;
 31  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE PUBDOG:

LINE/COL
---------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------------------------------------------------------------------------
8/7
PL/SQL: SQL Statement ignored
13/14
PL/SQL: ORA-00942: table or view does not exist
24/5
PL/SQL: Statement ignored
24/8
PLS-00320: the declaration of the type of this expression is incomplete or malformed
SQL> 

[Updated on: Sat, 07 November 2009 22:08]

Report message to a moderator

Re: stored procedure cursor and loop [message #430078 is a reply to message #430060] Sun, 08 November 2009 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should read the following:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Opening a cursor does not return any result, you get the result on fetch.

no_data_found is an exception that should be tested in an exception block not in an IF.

Before posting 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Sun, 08 November 2009 01:04]

Report message to a moderator

Re: stored procedure cursor and loop [message #430148 is a reply to message #430060] Mon, 09 November 2009 03:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
The code compiles without errors
No, it really
doesn't.

In addition to @Michel's points:

1) You cannot have an INTO statement in a cursor definition - the FETCH statement determines which variables the cursor values are fetched into.

2) You don't populate the v_pname variable at any point

3) You don't need the DECLARE / BEGIN / END block in the procedure - you can put the declarations above the BEGIN at the start of the procedure, and put all the rest of the code after the BEGIN.
Re: stored procedure cursor and loop [message #430150 is a reply to message #430060] Mon, 09 November 2009 04:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You mention that you got this from a book. If this is a book on the basics of PL/SQL, you should not skip to the examples and expect to understand what is going on.
If this is a book for some other database then Oracle, you might want to either get an Oracle-focused book or first use that other database until you get a grasp on the basics.
Take this as I mean it to be: just an advice. Learning to code is a tedious job and there is no shortcut to it that will really last.
Previous Topic: Query regarding subqueries & execution plan
Next Topic: Help
Goto Forum:
  


Current Time: Tue Sep 27 12:50:15 CDT 2016

Total time taken to generate the page: 0.07048 seconds