Oracle 11g-运行PL / SQL游标

admin

Oracle 11g-运行PL / SQL游标

sql

我试图在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

您能指出我出什么问题吗？我似乎无法理解什么地方出了问题

谢谢，布莱恩

阅读 197

2021-07-01

共1个答案

admin

有几个问题：

您需要在游标定义之后（即在查询之后）使用分号。
您不能同时使用bookCursor游标的名称和获取的记录的名称。（我注意到您的代码中有一部分bookCursorRec用于后者，因此我将继续讨论。）
在fetch需求获取到的东西，这是进入bookCursorRec。
呼叫到后，您需要使用分号dbms_output.put_line。
您的查询似乎有误；看起来两个联接都是交叉联接。

放在一起，并稍微调整格式和结构，使它更像“惯用的” 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;
/

顺便说一句，甲骨文标识符大多是区分大小写（因为他们是隐式转换为大写，除非你在双引号包起来），所以通常人们会用类似的标识符book_cursor_rec和date_destroyed，而不是bookCursorRec（=
bookcursorrec）和dateDestroyed（= datedestroyed）。

2021-07-01