Oracle 11g - 运行PL / SQL游标 [英] Oracle 11g - Running PL/SQL Cursors

问题描述

我试图在Oracle 11g上运行此代码，它给我以下错误。我似乎无法做到。

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

感谢，
Brian

Thanks, Brian

推荐答案

有几个问题：

• 在游标定义后需要一个分号）。


• 您不能使用 bookCursor 作为游标的名称和您获取的记录的名称。 （我注意到你的代码的一些使用 bookCursorRec 为后者，所以我会去。）


• code> fetch 需要提取到中，即 bookCursorRec 。


• 在调用 dbms_output.put_line 后需要一个分号。


• 它看起来像两个连接是交叉连接。

• 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.

将它们放在一起，并调整格式和结构，惯用的PL / SQL：

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

顺便说一下，Oracle标识符大多是区分大小写的重新隐式转换为大写，除非你将它们包装在双引号中），所以通常人们会使用 book_cursor_rec 和 date_destroyed 而不是 bookCursorRec （= bookcursorrec ）和 dateDestroyed = dateredroyed ）。

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).

这篇关于Oracle 11g - 运行PL / SQL游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！