Oracle 11g - Running PL/SQL Cursors

问题:

I'm trying to run this code on Oracle 11g and it's giving me the error below. I can't seem to get it right.

DECLARE
    CURSOR bookcursor IS
    SELECT btName, BookCopy.Isbn, pubName, dateDestroyed
      FROM booktitle bt
      JOIN publisher p
        ON bt.pubId = p.pubId 
      JOIN bookcopy bc
        ON bt.Isbn = bc.Isbn 
     WHERE datedestroyed IS NULL
    ;
    bookcursorrec bookcursor%ROWTYPE;
BEGIN
    OPEN bookcursor;
    LOOP
        FETCH bookcursor INTO bookcursorrer;
        EXIT WHEN bookcursor%NOTFOUND;
        dbms_output.put_line( 'ISBN: ' ||bookcursorrec.isbn
                              || ' - Book Name: ' || bookcursorrec.btname
                              || ' - Publisher: ' || bookcursorrec.pubname );
    END LOOP;
    CLOSE bookcursor;
END;


ERROR at line 3:
ORA-06550: line 3, column 20:
PL/SQL: ORA-00904: "BOOKCOPY"."ISBN": invalid identifier
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 12:
PLS-00341: declaration of cursor 'BOOKCURSOR' is incomplete or malformed
ORA-06550: line 11, column 19:
PL/SQL: Item ignored
ORA-06550: line 15, column 31:
PLS-00201: identifier 'BOOKCURSORRER' must be declared
ORA-06550: line 15, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 42:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 17, column 9:
PL/SQL: Statement ignored

Can you kindly point me to what is wrong? I can't seem to understand to what is wrong

Thanks, Brian

回答1:

There are a few problems:

• You need a semicolon after the cursor definition (i.e., after the query).
• You can't use bookCursor both as the name of the cursor and as the name of the record you fetch. (I notice that a bit of your code uses bookCursorRec for the latter, so I'll go with that.)
• The fetch needs to fetch into something, that is, into bookCursorRec.
• You need a semicolon after the call to dbms_output.put_line.
• Your query seems wrong; it looks like both joins are cross-joins.

Putting it together, and adjusting the formatting and structure a bit so it's slightly more "idiomatic" PL/SQL:

DECLARE
    CURSOR bookcursor IS
    SELECT btname, isbn, pubname, datedestroyed
      FROM booktitle bt
      JOIN publisher p
        ON bt.pid = p.id -- this is just a guess
      JOIN bookcopy bc
        ON bt.bcid = bc.id -- this is just a guess
     WHERE datedestroyed IS NULL
    ;
    bookcursorrec bookcursor%ROWTYPE;
BEGIN
    OPEN bookcursor;
    LOOP
        FETCH bookcursor INTO bookcursorrec;
        EXIT WHEN bookcursor%NOTFOUND;
        dbms_output.put_line( 'ISBN: ' ||bookcursorrec.isbn
                              || ' - Book Name: ' || bookcursorrec.btname
                              || ' - Publisher: ' || bookcursorrec.pubname );
    END LOOP;
    CLOSE bookcursor;
END;
/

By the way, Oracle identifiers are mostly case-sensitive (in that they're implicitly converted to uppercase unless you wrap them in double-quotes), so usually people will use identifiers like book_cursor_rec and date_destroyed rather than bookCursorRec (= bookcursorrec) and dateDestroyed (= datedestroyed).

回答2:

You need to change

 fetch  bookCursor

into

 fetch bookCursor into bookCursorRec;

above the while.

Also

cursor bookCursor is
       SELECT btName, ISBN, pubName, dateDestroyed
       FROM BookTitle bt, publisher p, BookCopy bc
       WHERE bt.bcId = bcId
       AND dateDestroyed is null
       bookCursor bookCursor%rowtype;

should read

cursor bookCursor is
       SELECT btName, ISBN, pubName, dateDestroyed
       FROM BookTitle bt, publisher p, BookCopy bc
       WHERE bt.bcId = bcId
       AND dateDestroyed is null;

       bookCursorRec bookCursor%rowtype;

回答3:

Set Serveroutput on
CREATE OR REPLACE A PROCEDURE TO GET_SENRYO_EMP
DECLARE
    CURSOR Senryocursor IS
    SELECT first_name, last_name, full_Name,Gender,DOB,Martialstatus,Nationality,Telephone,Email_id,job,Nationalidentifier,Hire_date
      FROM Senryo;
--Senryocursorrec senryocursor%rowtype;
BEGIN
 OPEN Senryocursor;
LOOP
        FETCH Senryocursor INTO Senryocursorrec;
        EXIT WHEN Senryocursor%NOTFOUND;
        dbms_output.put_line( 'Senryocursor ' ||Senryocursorrec
                              || '- Full_name :' || Senryocursorrec.full_name
                              || ' - Job: ' || Senryocursorrec.Job );
    END LOOP;
    CLOSE Senryocursor;
END;

回答4:

Also important to check user has select grant to the object or table in question. My issue got resolved by granting user select access to table used in cursor;

GRANT SELECT ON <table> TO <user> ;