主页 / dba / 问题 / 577
Accepted
bernd_k
Asked: 2011-01-13 06:10:05 +0800 CST

如何从 Oracle 存储过程中将 CTE 作为 REFCURSOR 返回？

  • 772

我尝试修改 Oracle 存储过程以使用 CTE 而不是简单的 Select 语句。

我的程序如下所示：

Create or replace Myproc ( MyRefCursor  IN OUT SYS_REFCURSOR)
as
begin
    Open MyRefCursor for
        Select * from ...something ...;


end;
/

我重写了这个查询：

   Select * from ...something ...;

作为这个 CTE：

with MyCTE As (
    ... ;
)
Select * from MyCTE;

以这种方式打开游标不起作用：

Open MyRefCursor for
with MyCTE As (
    ... ;
)
Select * from MyCTE;

这也不是：

with MyCTE As (
    ... ;
)
Open MyRefCursor for
Select * from MyCTE;
oracle stored-procedures

2 个回答

  1. Best Answer

    由于 CTE 是同一 SQL 语句的一部分，因此不应包含分号。

    所以，第三块的第二行或第四块的第三行不应该有分号。第五个块没有连续的 SQL 语句。

    • 4

  2. 一个使用没有输入参数的存储过程的完整非常基本的示例：

    一些演示数据：

    CREATE TABLE DEMO(
    id NUMBER(10),
    type NUMBER(10),
    code varchar2(20)
);

Insert INTO DEMO values (1, 0, 'a');
Insert INTO DEMO values (2, 0, 'b');
Insert INTO DEMO values (3, 0, 'c');
Insert INTO DEMO values (4, 1, 'd');
Insert INTO DEMO values (5, 1, 'e');
Insert INTO DEMO values (6, 1, 'f');

SELECT * from DEMO;

    现在是一个使用普通 CTE 的简单存储过程：

    create or replace procedure return_from_simple_cte
(
    MyRefCursor IN OUT SYS_REFCURSOR
)
AS
BEGIN
    Open MyRefCursor for
    WITH My_CTE AS(
        SELECT * FROM DEMO WHERE Type = 1
    )
    Select * FROM My_CTE;


END;
/

    最后是 sqlplus 或 TOAD 的测试调用

    VAR R REFCURSOR
EXEC return_from_simple_cte (:R)
PRINT r

    这个问题的背景：

    我在 SQL-Server 上将 Select-statement 转换为 CTE，我想将行号添加到结果中。我只是忘记给 * 添加一个别名

    create or replace  PROCEDURE TEChange
(
    MyRefCursor    IN OUT SYS_REFCURSOR
    ,p_TERefnr    NUMBER
)
AS
BEGIN
    Open MyRefCursor for 
    WITH TE_CTE AS (
        SELECT TeRefnr
        FROM vSYSTE
        WHERE TERefnr <> p_TERefnr
    )
    Select
         ROW_NUMBER() over(order by TERefnr)  Nr, 
        t.* 
    from TE_CTE t;
END;
/

    非递归 CTE 似乎有助于从 SQL-Server 迁移到 Oracle，但存在一些细微的语法差异。

    • 0

相关问题