Oracle 11g - 运行 PL/SQL 游标

Oracle 11g - Running PL/SQL Cursors

提问by Brian

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.

我正在尝试在 Oracle 11g 上运行此代码，但出现以下错误。我似乎无法正确理解。

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

谢谢，布赖恩

回答by ruakh

There are a few problems:

有几个问题：

• You need a semicolon after the cursor definition (i.e., after the query).
• You can't use bookCursorboth as the name of the cursor and as the name of the record you fetch. (I notice that a bit of your code uses bookCursorRecfor the latter, so I'll go with that.)
• The fetchneeds to fetch intosomething, 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.

• 在游标定义之后（即在查询之后）需要一个分号。
• 不能bookCursor同时用作游标名称和获取的记录名称。（我注意到你的一些代码bookCursorRec用于后者，所以我会这样做。）
• 在fetch需求获取到的东西，这是进入bookCursorRec。
• 调用后需要一个分号dbms_output.put_line。
• 您的查询似乎有误；看起来两个连接都是交叉连接。

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

将它们放在一起，并稍微调整格式和结构，使其更“惯用”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_recand date_destroyedrather than bookCursorRec(= bookcursorrec) and dateDestroyed(= datedestroyed).

顺便说一下，Oracle 标识符大多区分大小写（因为它们会被隐式转换为大写，除非您将它们用双引号括起来），因此通常人们会使用book_cursor_rec和date_destroyed而不是bookCursorRec(= bookcursorrec) 和dateDestroyed(= datedestroyed) 之类的标识符。

回答by Aravind Rajasekaran

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;

回答by René Nyffenegger

You need to change

你需要改变

 fetch  bookCursor

into

进入

 fetch bookCursor into bookCursorRec;

above the while.

上面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;

回答by anil

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> ;