oracle SQL 查询中的游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8637050/
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
Cursor inside SQL query
提问by tbrugz
In Oracle, it's possible to return a cursorinside a SQL query, using the cursorkeyword, like this:
在 Oracle 中，可以cursor使用cursor关键字返回内部 SQL 查询，如下所示：
select owner, table_name,
cursor (select column_name
from all_tab_columns
where owner = allt.owner
and table_name = allt.table_name) as columns
from all_tables allt
The questions are:
问题是：
- Does anyone know where can I find documentation for this?
- Does PortgreSQL (or any other open source DBMS) have a similar feature?
- 有谁知道我在哪里可以找到这方面的文档？
- PortgreSQL（或任何其他开源 DBMS）有类似的功能吗？
采纳答案by APC
It's called a CURSOR EXPRESSION, and it is documented in the obvious place, the Oracle SQL Reference. Find it here.
它被称为 CURSOR EXPRESSION，它记录在明显的地方，Oracle SQL 参考。 在这里找到它。
As for your second question, the closest thing PostgreSQL offers to match this functionality is "scalar sub-queries". However, as @tbrugz points out, these only return one row and one column, so they aren't much like Cursor Expressions. Read about them in the documentation here. MySQL also has Scalar Sub-queries, again limited to one column and one row. Docs here. Likewise SQL Server and DB2 (not open source but for completeness).
至于你的第二个问题，PostgreSQL 提供的最接近这个功能的东西是“标量子查询”。但是，正如@tbrugz 指出的那样，它们只返回一行和一列，因此它们不太像光标表达式。 在此处的文档中阅读有关它们的信息。MySQL 也有标量子查询，同样限于一列和一行。文档在这里。同样的 SQL Server 和 DB2（不是开源的，但为了完整性）。
That rules out all the obvious contenders. So, it seems unlikely any other DBMS offers the jagged result set we get from Oracle's cursor expression.
这排除了所有明显的竞争者。因此，其他 DBMS 似乎不太可能提供我们从 Oracle 的游标表达式中获得的锯齿状结果集。
回答by dvv
Postgres provides cursor expressions but the syntax is a bit less handy than Oracle's.
Postgres 提供了游标表达式，但语法不如 Oracle 方便。
First you need to create function for array to refcursor conversion:
首先，您需要为数组到 refcursor 转换创建函数：
create or replace function arr2crs(arr anyarray) returns refcursor as $$
declare crs refcursor;
begin
open crs for select * from unnest(arr);
return crs;
end;
$$ language plpgsql volatile;
Now let's create some test data
现在让我们创建一些测试数据
create table dep as
select 1 depid, 'Sales' depname
union all
select 2 depid, 'IT' depname;
create table emp as
select 1 empid, 1 depid, 'John' empname union all
select 2 empid, 1 depid, 'James' empname union all
select 3 empid, 2 depid, 'Rob';
You can query it like this
你可以这样查询
select
dep.*,
arr2crs(array(
select row(emp.*)::emp from emp
where emp.depid = dep.depid
)) emps
from dep
And process in on client side like this (Java)
并像这样在客户端处理（Java）
public static List Rs2List(ResultSet rs) throws SQLException{
List result = new ArrayList();
ResultSetMetaData meta = rs.getMetaData();
while(rs.next()){
Map row = new HashMap();
for (int i = 1; i <= meta.getColumnCount(); i++){
Object o = rs.getObject(i);
row.put(
meta.getColumnName(i),
(o instanceof ResultSet)?Rs2List((ResultSet)o):o);
}
result.add(row);
}
return result;
}
Note that you must explicitly cast row to particular type. You can use CREATE TYPE to create necessary types.
请注意，您必须将行显式转换为特定类型。您可以使用 CREATE TYPE 创建必要的类型。
