SQL查询中的游标 [英] Cursor inside SQL query

问题描述

在Oracle中，可以使用cursor关键字在SQL查询中返回cursor，如下所示:

In Oracle, it's possible to return a cursor inside a SQL query, using the cursor keyword, like this:

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

问题是:

1. 有人知道我在哪里可以找到相关文档吗?
2. PortgreSQL(或任何其他开源DBMS)是否具有类似的功能?

推荐答案

它被称为CURSOR EXPRESSION，并且在明显的位置(《 Oracle SQL参考》)中进行了记录. 在此处查找.

It's called a CURSOR EXPRESSION, and it is documented in the obvious place, the Oracle SQL Reference. Find it here.

关于第二个问题，PostgreSQL提供的与此功能最接近的东西是标量子查询".但是，正如@tbrugz所指出的那样，它们仅返回一行和一列，因此它们与游标表达式不太相似. 在此处的文档中了解有关它们的信息. MySQL还具有标量子查询，再次限制为一列和一行. 此处的文档.同样，SQL Server和DB2(不是开放源代码，但出于完整性考虑).

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

这排除了所有明显的竞争者.因此，似乎其他任何DBMS都不可能提供从Oracle的游标表达式中获得的锯齿状的结果集.

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.

这篇关于SQL查询中的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！