这个 DB2 游标是一个循环吗?

2022-01-14 00:00:00 db2 tsql sql-server cursor

由于命运或运气，我正在努力将 DB2 存储过程转换为 SQL Server 存储过程.

Through fate or luck, I am working on converting DB2 stored procedures to SQL Server stored procedures.

在 DB2 中我无法完全理解的一件事是游标1.通过查看它并阅读一些文档，它似乎只是一个选择语句.

One thing I could not completely understand in DB2 is cursors1. By looking at it and reading some documentation, it appears that it is only a select statement.

-- DB2 stored procedure code
declare entity_cursor cursor with return for
select *
from TableName;

...
--a lot more cursors like above, and some other code
...

open entity_cursor;

问题

  1. 我的假设是否正确2 这只是一个 select 语句实际上 在打开游标时返回结果集?
  2. 如果在光标打开之前发生错误3，SP是否会为entity_cursor返回空白结果集?
  1. Am I correct in my assumption2 that this is only a select statement actually returning a result set when the cursor is opened?
  2. If an error has occurred before the cursor is opened3, will the SP return a blank result set for entity_cursor?

<小时>

1:我知道 SQL Server 它们通常用于逐行执行操作.
2:基于阅读 DB2 文档和一点我的知识和才智.
3:很遗憾，我无法完全测试 DB2 中的存储过程.

1: I know that SQL Server they are commonly used for performing operations on row-by-row.
2: Based on reading DB2 documentation and a little on my knowledge and intellect.
3: Unfortunately I cannot fully test the stored procedure in DB2.

推荐答案

关于你的第一个问题:正如评论中提到的，打开游标不会产生结果集，而只是一个(指向a的)结构，允许您访问使用 FETCH 语句或等效语句的结果集.此外，当您开始获取记录时，结果集甚至可能没有完全实现——这取决于实际查询和各种游标选项.

To your first question: as mentioned in comments, opening a cursor does not produce a result set, but merely a (pointer to a) structure allowing you to access the result set using the FETCH statement or equivalent. Moreover, the result set may not even be fully materialized when you start fetching records -- this depends on the actual query and various cursor options.

关于你的第二个问题:如果你的存储过程在打开游标之前正常或以其他方式返回，它的调用者将获得一个未初始化的游标结构(当你尝试访问它时会导致异常)而不是一个空的结果集.

To your second question: if your stored procedure returns, normally or otherwise, before opening the cursor, its caller will get an uninitialized cursor structure (which will cause an exception when you try to access it) and not an empty result set.

相关文章