Início / dba / Perguntas / 577
Accepted
bernd_k
Asked: 2011-01-13 06:10:05 +0800 CST

Como retornar um CTE como REFCURSOR de um procedimento armazenado Oracle?

  • 772

Tentei modificar um procedimento armazenado do Oracle para usar um CTE em vez de uma simples instrução Select.

Meu procedimento ficou assim:

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


end;
/

Eu reescrevi esta consulta:

   Select * from ...something ...;

Como este CTE:

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

Abrir um cursor dessa maneira não funciona:

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

Nem isso:

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

2 respostas

  1. Best Answer

    Como a CTE faz parte da mesma instrução SQL, ela não deve conter ponto e vírgula.

    Portanto, não deve haver ponto e vírgula na segunda linha do terceiro bloco ou na terceira linha do quarto bloco. O quinto bloco não possui uma instrução SQL contígua.

    • 4

  2. Um exemplo muito básico completo usando um procedimento armazenado sem parâmetro de entrada:

    Alguns dados de demonstração:

    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;

    Agora, um procedimento armazenado simples usando um CTE trivial:

    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;
/

    E finalmente uma chamada de teste para sqlplus ou TOAD

    VAR R REFCURSOR
EXEC return_from_simple_cte (:R)
PRINT r

    Contexto para esta pergunta:

    Fiz a transformação da instrução Select para CTE no SQL-Server e queria adicionar números de linha ao resultado. Eu simplesmente esqueço que adicione um alias ao *

    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;
/

    os CTEs não reursivos parecem ajudar a migrar do SQL-Server para o Oracle, mas existem algumas pequenas diferenças de sintaxe.

    • 0

relate perguntas