stored - return cursor oracle

cómo declarar% ROWTYPE de una variable que es SYS_REFCURSOR de tipo débil?

Question

Código Wrt a continuación No puedo declarar el tipo de búsqueda en variable como% ROWTYPE de la tabla subyacente porque SYS_REFCURSOR está en una selección que une dos tablas y también selecciona algunas funciones llamadas en los atributos de las dos tablas subyacentes; es decir, no puedo declarar como L_RECORD T% ROWTYPE

---
DECLARE
  P_RS SYS_REFCURSOR;
  L_RECORD P_RS%ROWTYPE;
BEGIN
  CAPITALEXTRACT(
    P_RS => P_RS
  );
    OPEN P_RS;
    LOOP
      BEGIN
        FETCH P_RS INTO L_RECORD;
        EXIT WHEN P_RS%NOTFOUND;
        ...
      EXCEPTION
        WHEN OTHERS THEN
        ...
      END;
    END LOOP;
    CLOSE P_RS;
END;
--------
CREATE or REPLACE PROCEDURE CAPITALEXTRACT
(
    p_rs OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_rs for 
     select t.*,tminusone.*, f(t.cash), g(t.cash) FROM T t, TMINUSONE tminusone
    where t.ticket=tminusone.ticket;
END CAPITALEXTRACT;

Por supuesto, no quiero definir una tabla estática R con columnas como se devuelve en SYS_REFCURSOR y luego declarar como L_RECORD R% ROWTYPE.

Y de ahí la pregunta: ¿cómo declarar% ROWTYPE de una variable que es SYS_REFCURSOR de tipo débil?

Answer 1

La respuesta corta es que no puedes. Debería definir una variable para cada columna que se devolverá.

DECLARE
    P_RS SYS_REFCURSOR;
    L_T_COL1 T.COL1%TYPE;
    L_T_COL1 T.COL2%TYPE;
    ...

Y luego ir a la lista de columnas:

FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ;

Esto es doloroso pero manejable siempre que sepa lo que espera en el cursor de referencia. Sin embargo, el uso de T.* en su procedimiento lo hace frágil, ya que agregar una columna a la tabla podría romper el código que cree que sabe qué columnas hay y en qué orden se encuentran. (También puede dividirlo entre entornos si las tablas no están construidos de manera consistente: he visto lugares donde el orden de las columnas es diferente en diferentes entornos). Probablemente quiera asegurarse de que solo está seleccionando las columnas que realmente le interesan, para evitar tener que definir variables para cosas que nunca leerá.

Desde 11g puede usar el paquete DBMS_SQL para convertir su sys_refcursor en un cursor DBMS_SQL , y puede interrogarlo para determinar las columnas. Solo como un ejemplo de lo que puede hacer, se imprimirá el valor de cada columna en cada fila, con el nombre de la columna:

DECLARE
    P_RS SYS_REFCURSOR;
    L_COLS NUMBER;
    L_DESC DBMS_SQL.DESC_TAB;
    L_CURS INTEGER;
    L_VARCHAR VARCHAR2(4000);
BEGIN
    CAPITALEXTRACT(P_RS => P_RS);
    L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(P_RS);
    DBMS_SQL.DESCRIBE_COLUMNS(C => L_CURS, COL_CNT => L_COLS,
        DESC_T => L_DESC);

    FOR i IN 1..L_COLS LOOP
        DBMS_SQL.DEFINE_COLUMN(L_CURS, i, L_VARCHAR, 4000);
    END LOOP;

    WHILE DBMS_SQL.FETCH_ROWS(L_CURS) > 0 LOOP
        FOR i IN 1..L_COLS LOOP
            DBMS_SQL.COLUMN_VALUE(L_CURS, i, L_VARCHAR);
            DBMS_OUTPUT.PUT_LINE(''Row '' || DBMS_SQL.LAST_ROW_COUNT
                || '': '' || l_desc(i).col_name
                || '' = '' || L_VARCHAR);
        END LOOP;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(L_CURS);
END;
/

Eso no es de mucho uso práctico, y para abreviar estoy tratando cada valor como una cadena ya que solo quiero imprimirlo de todos modos. Mire los documentos y busque ejemplos para aplicaciones más prácticas.

Si solo quiere algunas columnas de su cursor de referencia, podría, supongo, recorrer loop alrededor de l_desc y registrar la posición donde column_name es lo que le interesa, como una variable numérica; luego puede referirse a la columna por esa variable más adelante, donde normalmente usaría el nombre en un bucle de cursor. Depende de lo que estés haciendo con los datos.

Pero a menos que espere no saber el orden de las columnas, regresará, lo que es poco probable, ya que parece que controla el procedimiento, y suponiendo que se deshace de los .* S, es probable que sea mucho mejor que reduzca los retornos. columnas al mínimo que necesita y simplemente declararlas todas individualmente.