Oracle 中隐式游标和显式游标的区别

发布于 2024-09-24 09:56:08 字数 334 浏览 2 评论 0原文

我想知道这两种说法之间的区别。其中一个比另一个“更好”吗？

DECLARE
    myvar varchar2(50);
BEGIN
    SELECT fieldone into myvar FROM tbl_one WHERE id = 1;
END;

和

DECLARE
    CURSOR L1 IS
    SELECT fieldone FROM tbl_one WHERE id = 1;
BEGIN
    OPEN L1;
    FETCH L1 INTO myvar;
    CLOSE L1;
END;

原文

I want to know the difference between these two statements. Is one 'better' than the other ?

DECLARE
    myvar varchar2(50);
BEGIN
    SELECT fieldone into myvar FROM tbl_one WHERE id = 1;
END;

AND

DECLARE
    CURSOR L1 IS
    SELECT fieldone FROM tbl_one WHERE id = 1;
BEGIN
    OPEN L1;
    FETCH L1 INTO myvar;
    CLOSE L1;
END;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

洛阳烟雨空心柳 2024-10-01 09:56:08

如果没有返回行或者返回多行，第一个将引发异常。如果您不处理异常，则会将其扔回调用例程或客户端软件。这称为隐式游标。

第二个会默默地失败。如果没有返回行，则 myvar 将具有空值（尽管如果您假设它未定义，则更好）。如果返回多行，则仅存储第一行的值。如果没有 ORDER BY，则未定义哪个值是“first”。这称为显式游标。

所以问题实际上是，如果没有找到数据或行太多，您希望发生什么情况。如果您确定这种情况永远不会发生，或者不知道如何处理它，请使用选项 1。

如果您确实希望仅出现未找到数据的情况，则使用隐式游标，但添加异常处理程序。

如果您期望多行，则可以使用带有异常处理程序的隐式游标，或者如果您确实需要处理多行，则使用 BULK SELECT 或 CURSOR LOOP。

如果要选择多个字段，定义显式游标并使用 %TYPE 声明来声明所有必要的变量可能会很有用。

从性能的角度来看，没有什么区别。
从可维护性的角度来看，有些人喜欢将 SELECT 与代码“内联”（因此更喜欢隐式游标）。我更喜欢我的“不碍事”，特别是如果有一个大的列列表，所以我喜欢显式游标。

回复收藏 0 原文

不…忘初心 2024-10-01 09:56:08

我不知道你在问什么问题，但我就说吧。

你应该像这样使用 PL/SQL 吗？

declare
  myvar varchar2(50);
begin
  select fieldone 
    into myvar
    from tbl_one;
end;
/

好吧，当且仅当您知道 select 语句可以恰好返回一行时，您才可以；或者，您需要对 TOO_MANY_ROWS 和 NO_DATA_FOUND 异常进行错误处理，否则会引发这些异常。

当使用显式游标（即 CURSOR 关键字）时，有几个针对它的操作来控制其行为。

declare
  myvar varchar2(50);
  CURSOR L1 IS
  SELECT fieldone FROM tbl_one ;
begin
  OPEN L1;
  FETCH L1 into myvar;
  CLOSE L1;
end;
/

CURSOR L1... 是游标的声明。它只不过是绑定静态 SQL 语句，PL/SQL 引擎所做的就是检查 SQL 在语法和上下文上是否有效 - 是否缺少子句？该用户可以从此表中进行 SELECT 操作吗？

OPEN L1 打开光标，在系统历史记录中建立结果将反映的确切点。针对该游标的任何后续 FETCH 操作都将反映该精确点的数据。

FETCH L1... 实际上将该结果集的第一行/下一行返回到您指定的变量中，无论它是什么。它可以是声明的记录，也可以是变量列表。

CLOSE L1... 释放光标打开的所有资源；例如，影响记录的插入/更新/删除操作会生成您的用户会话已声明的读取兴趣的撤消，因此在您关闭游标之前，无法释放或重用撤消。

I don't know what question you're asking, but here goes.

Should you use PL/SQL like this?

declare
  myvar varchar2(50);
begin
  select fieldone 
    into myvar
    from tbl_one;
end;
/

Well, you can if and only if you know that the select statement can return exactly one row; alternatively, you need error handling for the TOO_MANY_ROWS and NO_DATA_FOUND exceptions which would be raised otherwise.

When using explicit cursors (i.e., the CURSOR keyword), there are several operations against it which control its behavior.

declare
  myvar varchar2(50);
  CURSOR L1 IS
  SELECT fieldone FROM tbl_one ;
begin
  OPEN L1;
  FETCH L1 into myvar;
  CLOSE L1;
end;
/

CURSOR L1... is the cursor's declaration. It's nothing more than binding the static SQL statement, and all the PL/SQL engine does is check that the SQL is syntactically and contextually valid - are there missing clauses? Can this user SELECT from this table?

OPEN L1 opens the cursor, establishing the exact point in the history of the system which the results will reflect. Any subsequent FETCHes against that cursor will reflect the data as of that precise point.

FETCH L1... actually returns the first/next row of that result set, whatever it is, into the variables you've specified. It could be a record declared, or it could be list of variables.

CLOSE L1... frees any resources your cursor has open; for example, insert/update/delete operations that affect the records generate undo that your user session has a declared read interest in, so that undo can't be freed or reused until you've closed your cursor.

回复收藏 0 原文

丿*梦醉红颜 2024-10-01 09:56:08

一般来说，您编写的代码越少，您的解决方案就越健壮。这就是为什么我们不再青睐汇编级语言。

Joe Celko 在他的一本书中对这种应用于游标的想法进行了雄辩的批评：

游标是一种将查询结果集转换为可以用主机 3GL 语言处理的流的方法。游标在不同的 RDBMS 供应商之间不兼容，并且通常比声明式 SQL 查询慢。那么，何苦呢？主要是对数据库基础的不了解和旧习惯。这里有详细的类比：


ALLOCATE   = turn tape recorder power on, assign  channel

DECLARE  CURSOR FOR ... = mount the tape and declare file

OPEN  = open the file

FETCH   INTO  = read in the program records one by one, move the head

CLOSE  = close file, dismount tape from the recorder

DEALLOCATE  = free tape recorder channel, power off

这句话在他的“SQL 编程风格”的俄语翻译版中读起来特别有趣，其中“录音机”听起来像“录音机”。在 iPod、iPhone 等设备泛滥的世界里，有人仍然在操作磁带机，这真是太搞笑了。

然后，Joe 讲述了一个新手程序员的轶事案例，他安排三个游标一起工作来迭代主从数据，这些数据选择了一致的记录并执行了更新。最终 250 行代码被丢弃，转而采用单个 SQL 更新语句。

Generally, the less code you write, the more robust your solution is. This is why we don't favor assembler level languages anymore.

This idea applied to cursors, has been eloquently criticized by Joe Celko in one of his books:

Cursor is a way to transform query result set into a stream that can be processed in a host 3GL language. Cursors are not compatible between different RDBMS vendors, and generally work slower than declarative SQL queries. Then, why bother? Mainly because of ignorance of database fundamentals and old habits. Here is detailed analogy:


ALLOCATE   = turn tape recorder power on, assign  channel

DECLARE  CURSOR FOR ... = mount the tape and declare file

OPEN  = open the file

FETCH   INTO  = read in the program records one by one, move the head

CLOSE  = close file, dismount tape from the recorder

DEALLOCATE  = free tape recorder channel, power off

This quote reads especially funny in Russian translation edition of his "SQL Programming Style" where the "tape recorder" sounds like "audio tape deck". And the suggestion that somebody still operates a tape deck in the world infested by iPods, iPhones and alike is hilarious.

Then, Joe goes through an anecdotal case of a newbie programmer who arranged three cursors to work together to iterate through master-detail data that have chosen conforming records and performed update. Eventually 250 lines of code were thrown away in favor of a single SQL update statement.

回复收藏 0 原文