example - ¿Cómo devolver un conjunto de resultados/cursor desde un bloque anónimo Oracle PL/SQL que ejecuta SQL dinámico?

pl sql oracle pdf

Question

Tengo esta tabla:

ALLITEMS
---------------
ItemId  | Areas
---------------
1       | EAST
2       | EAST
3       | SOUTH
4       | WEST

El DDL:

drop table allitems;

Create Table Allitems(ItemId Int,areas Varchar2(20));
Insert Into Allitems(Itemid,Areas) Values(1,''east'');
Insert Into Allitems(ItemId,areas) Values(2,''east'');
insert into allitems(ItemId,areas) values(3,''south'');
insert into allitems(ItemId,areas) values(4,''east'');

En MSSQL, para obtener un cursor de un SQL dinámico, puedo hacer lo siguiente:

DECLARE @v_sqlStatement VARCHAR(2000);
SET @v_Sqlstatement = ''SELECT * FROM ALLITEMS'';
EXEC (@v_sqlStatement); --returns a resultset/cursor, just like calling SELECT 

En Oracle, necesito usar un bloque PL / SQL:

SET AUTOPRINT ON;
DECLARE
 V_Sqlstatement Varchar2(2000);
 outputData SYS_REFCURSOR;
BEGIN
 V_Sqlstatement := ''SELECT * FROM ALLITEMS'';
 OPEN outputData for v_Sqlstatement; 
End;
--result is : anonymous block completed

Pero todo lo que obtengo es "bloque anónimo completado".
¿Cómo logro devolver el cursor?
(Sé que si realizo AUTOPRINT, se imprimirá la información en el REFCURSOR (no está imprimiendo en el código anterior, pero ese es otro problema))

Voy a llamar a este SQL dinámico del código (ODBC, C ++), y necesito devolver un cursor.
¿Cómo hago esto? Estoy perplejo.

Answer 1

Debería poder declarar que un cursor es una variable de vinculación (llamados parámetros en otro DBMS '')

como Vincent escribió, puedes hacer algo como esto:

begin
  open :yourCursor
    for ''SELECT "''|| :someField ||''" from yourTable where x = :y''
      using :someFilterValue;
end;

Tendría que vincular 3 vars a esa secuencia de comandos. Una cadena de entrada para "someField", un valor para "someFilterValue" y un cursor para "yourCursor" que se debe declarar como var de salida.

Desafortunadamente, no tengo idea de cómo harías eso desde C ++. (Aunque podría decirse afortunadamente para mí ;-))

Dependiendo de la biblioteca de acceso que use, puede ser un dolor real o directo.

Answer 2

Esta configuración debe establecerse:

SET SERVEROUTPUT ON 

Answer 3

Puede escribir una función PL / SQL para devolver ese cursor (o puede poner esa función en un paquete si tiene más código relacionado con esto):

CREATE OR REPLACE FUNCTION get_allitems
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT * FROM allitems;
  RETURN my_cursor;
END get_allitems;

Esto devolverá el cursor.

Asegúrese de no poner su cadena SELECT en comillas en PL / SQL cuando sea posible. Ponerlo en cadenas significa que no se puede verificar en tiempo de compilación, y que se debe analizar cada vez que lo use.

Si realmente necesita usar SQL dinámico, puede poner su consulta entre comillas simples:

  OPEN my_cursor FOR ''SELECT * FROM allitems'';

Esta cadena debe analizarse siempre que se llame a la función, que generalmente será más lenta y ocultará errores en su consulta hasta el tiempo de ejecución.

Asegúrese de usar bind-variables donde sea posible para evitar los análisis duros :

  OPEN my_cursor FOR ''SELECT * FROM allitems WHERE id = :id'' USING my_id;

Answer 4

en SQL * Plus también podría usar una variable REFCURSOR :

SQL> VARIABLE x REFCURSOR
SQL> DECLARE
  2   V_Sqlstatement Varchar2(2000);
  3  BEGIN
  4   V_Sqlstatement := ''SELECT * FROM DUAL'';
  5   OPEN :x for v_Sqlstatement;
  6  End;
  7  /

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> print x;

D
-
X