SQL PostgreSQL 游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8624036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL cursors
提问by Karl
I am trying to learn basic cursors using PostgreSQL. This is my script:
我正在尝试使用 PostgreSQL 学习基本的游标。这是我的脚本：
DECLARE cur_employees CURSOR FOR
SELECT *
FROM employee
CLOSE cur_employees
I want to traverse the list and output those which are active. Where should I start?
我想遍历列表并输出那些处于活动状态的列表。我应该从哪里开始？
回答by araqnid
You very rarely want to explicitly use cursors at all in PostgreSQL, even when processing query results in plpgsql. This is a marked contrast from many other SQL databases where they are used almost all the time.
您很少想在 PostgreSQL 中显式使用游标，即使在 plpgsql 中处理查询结果时也是如此。这与几乎一直使用的许多其他 SQL 数据库形成鲜明对比。
In plpgsql you could simply write something like:
在 plpgsql 中，您可以简单地编写如下内容：
DECLARE
emp employee%rowtype;
BEGIN
FOR emp IN SELECT * FROM employee LOOP
IF emp.active THEN
RAISE INFO 'Active: %', emp.employee_id
END IF;
END LOOP;
END
In the above, the plpgsql language handler deals with opening, binding, fetching and closing itself (more on declarations, and control structures).
在上面，plpgsql 语言处理程序处理打开、绑定、获取和关闭自身（更多关于声明和控制结构）。
With PostgreSQL from 9.0, you can simply drop into executing plpgsql using a "DO" block. For prior versions, you need to create a function and select it. If you're looking for the PostgreSQL equivalent of, for example, iterating through a result with a cursor on SQL Server, that's what it is. Note that iteration etc. are notpart of the SQL dialect, only part of plpgsql (or any of the other emebedded languages).
使用 PostgreSQL 9.0，您可以简单地使用“DO”块执行 plpgsql。对于以前的版本，您需要创建一个函数并选择它。如果您正在寻找 PostgreSQL 等价物，例如，在 SQL Server 上使用游标迭代结果，那就是它。请注意，迭代等不是SQL 方言的一部分，只是 plpgsql（或任何其他嵌入式语言）的一部分。
The "DECLARE CURSOR xxx" syntax at the SQL level canbe used like this:
SQL 级别的“DECLARE CURSOR xxx”语法可以这样使用：
DECLARE cur_employees CURSOR FOR SELECT * FROM employee;
FETCH NEXT FROM cur_employees;
// etc..
CLOSE cur_employees;
This can be used to carefully get just part of the query result set. However, it's unusual to use these, since usually your client driver will provide some sort of functionality to do this (e.g. scrollable result sets in JDBC). You can also return cursors from functions similar to Oracle, although again that's a comparatively rare use case.
这可用于仔细获取查询结果集的一部分。但是，使用这些并不常见，因为通常您的客户端驱动程序会提供某种功能来执行此操作（例如 JDBC 中的可滚动结果集）。您还可以从类似于 Oracle 的函数中返回游标，尽管这也是一个相对罕见的用例。
回答by cschneid
Generally, one has a DECLARE for the cursor, then an OPEN of the cursor (which materializes the result set), multiple FETCH operations to retrieve the rows from the result set individually, and then one does a CLOSE of the cursor.
通常，一个是针对游标的 DECLARE，然后是游标的 OPEN（具体化结果集），多个 FETCH 操作以分别从结果集中检索行，然后对游标进行 CLOSE。
You appear to have a DECLARE followed by a CLOSE. Thus your syntax error, as you never did an OPEN.
您似乎有一个 DECLARE 后跟一个 CLOSE。因此您的语法错误，因为您从未执行过 OPEN。
