Lunes, 08 Junio 2020 01:47

REF CURSOR de Oracle

Rate this item
(0 votes)
Objetivos:
• Introducción al tipo REF CURSOR.
--Weak REF Cursor
--Strong REF Cursor
• Introducción al tipo pre-definido SYS_REFCURSOR.


Los REF CURSOR
Como ya sabemos, un cursor es un puntero a un área privada de SQL que almacena información sobre el procesamiento de una sentencia SELECT o lenguaje de manipulación de datos (DML) (INSERTUPDATEDELETE MERGE).

 

De su lado, un REF CURSOR es una variable, definida como un tipo de cursor, que apuntará o hará referencia a un resultado de un cursor.

Ventajas de los REF CURSOR.

• A diferencia de un cursor normal, un REF CURSOR puede ser pasado como variable a un Procedimiento y/o una Función.

• El valor contenido en un REF CURSOR para ser asignado a otras variables REF CURSOR.
• Una variable REF CURSOR puede ser el valor de retorno de una función.

Nota: Una variable REF CURSOR no es un cursor, sino una variable que apunta a un cursor. Antes de asignar una variable cursor, debe definirse un tipo cursor.

Weak REF Cursor:

Clasificamos como Weak(débil) a una variable REF CURSOR en la cual no se defina el tipo de dato que la misma retornara.

Eje:

TYPE typ_ref_cur IS REF CURSOR;


Strong REF Cursor:

En estos se define lo que el cursor va a devolver. Hay que tener en cuenta que si se trata de devolver algo distinto que su tipo de retorno, se genera una excepción "ROWTYPE_MISMATCH".
Eje:
TYPE typ_ref_cur IS REF CURSOR
    RETURN hr.employees%ROWTYPE;
Nota: Los Strong REF Cursor resultan menos flexible pero a la vez menos propensos a errores de programación que los Weak REF Cursor. Esto porque con los Strong REF Cursor el compilador PL/SQL verifica(en tiempo de compilación) que la cláusula FETCH tenga una variable/record acorde con el tipo de retorno del cursor.

Ejemplos:
SET SERVEROUTPUT ON
DECLARE
    ---------------------TIPOS---------------------
    TYPE typ_ref_cur IS REF CURSOR;     --TIPO REF CURSOR
    TYPE typ_rec   IS RECORD
    (
      id      VARCHAR2(10),
      name    VARCHAR2(50),
      col_3   VARCHAR2(20),
      col_4   VARCHAR2(10)
    );
    ---------------------VARIABLES-Y-RECORDS---------------------
 
    v_rec   typ_rec;
 
    v_ref_cur   typ_ref_cur;    ---VARIABLE REF CURSOR;
 
    v_input     NUMBER(1);
    v_query     VARCHAR2(1000);
BEGIN
    v_input     :=  3;
 
    CASE
        WHEN v_input  > 2 THEN
          v_query :=  q'[SELECT
                              employee_id,
                              first_name||' '||last_name,
                              TRUNC(DBMS_RANDOM.value(20,50)),
                              'M'
                         FROM hr.employees]';
        WHEN v_input  < 2 THEN
          v_query :=  q'[SELECT
                              job_id,
                              job_title,
                              min_salary,
                              max_salary
                         FROM hr.jobs]';
        ELSE
          v_query :=  q'[SELECT
                              d.department_id,
                              d.department_name,
                              l.city,
                              (
                                SELECT COUNT(*)
                                FROM  hr.employees
                                WHERE department_id = d.department_id
                              )
                         FROM hr.departments d, locations l
                         WHERE  d.location_id = l.location_id]';
    END CASE;
 
    OPEN    v_ref_cur   FOR v_query;
        DBMS_OUTPUT.PUT_LINE
                            (
                              'Codigo||     Nombre     || Columna 3  ||  Columna 4 '
                            );
    LOOP
        FETCH v_ref_cur INTO  v_rec;
        EXIT  WHEN  v_ref_cur%NOTFOUND;
 
        DBMS_OUTPUT.PUT_LINE(RPAD(v_rec.id,10,' ')||'      '||RPAD(v_rec.name,15,' ')||'    '||RPAD(v_rec.col_3,15,' ')||'    '||v_rec.col_4);
    END LOOP;
    CLOSE    v_ref_cur;
END;
/*El SCRIPT anterior nos muestra como usar los REF CURSOR de Oracle; En este ejemplo primero declaramos el tipo REF CURSORtyp_ref_cur y posteriormente declaramos la variable v_ref_cur que sera del tipo antes creado; Notar que dependiendo del valor que contenga la variable v_input se le asigna a v_query una cadena de caracteres equivalente a un Query SQLQuery que mas adelante es usado por el CURSOR  v_ref_cur; Una vez abierto el REF CURSOR la forma de recuperar los datos que contiene es similar a la forma comúnmente usada con cualquier CURSOR, en este caso usamos un LOOP simple para hacer FETCH del CURSOR y asignar cada registro al RECORD v_rec declarado con 4 campos que podríamos considerar genéricos para así facilitar su manejo.*/
---OUTPUT:
DYNAMIC CURSOR
 
 
CREATE OR REPLACE FUNCTION  f_ref_cursor( p_table NUMBER DEFAULT 1)
    RETURN SYS_REFCURSOR
IS
    TYPE typ_ref_cur IS REF CURSOR;     --TIPO REF CURSOR
 
    v_ref_cur   typ_ref_cur;    ---VARIABLE REF CURSOR;
 
    v_query     VARCHAR2(1000);
BEGIN
    CASE
        WHEN p_table  > 2 THEN
          v_query :=  q'[SELECT
                              employee_id,
                              first_name||' '||last_name,
                              TRUNC(DBMS_RANDOM.value(20,50)),
                              'M'
                         FROM hr.employees]';
        WHEN p_table  < 2 THEN
          v_query :=  q'[SELECT
                              job_id,
                              job_title,
                              min_salary,
                              max_salary
                         FROM hr.jobs]';
        ELSE
          v_query :=  q'[SELECT
                              d.department_id,
                              d.department_name,
                              l.city,
                              (
                                SELECT COUNT(*)
                                FROM  hr.employees
                                WHERE department_id = d.department_id
                              )
                         FROM hr.departments d, locations l
                         WHERE  d.location_id = l.location_id]';
    END CASE;
 
    OPEN    v_ref_cur   FOR v_query;
 
    RETURN  v_ref_cur;
END f_ref_cursor;
/*Este ejemplo nos muestra como es posible crear una función que retorne una variable REF CURSOR o mas bien SYS_REFCURSORMas adelante vemos como podemos usar el valor retornado por dicha función.*/
 
 
 
DECLARE
    v_ref_cur   SYS_REFCURSOR;
    TYPE typ_rec   IS RECORD
    (
      id      VARCHAR2(10),
      name    VARCHAR2(50),
      col_3   VARCHAR2(20),
      col_4   VARCHAR2(10)
    );
 
    v_rec   typ_rec;
BEGIN
    v_ref_cur   :=  f_ref_cursor(3);
 
    DBMS_OUTPUT.PUT_LINE
                        (
                          'Codigo||     Nombre     || Columna 3  ||  Columna 4 '
                        );
    LOOP
        FETCH v_ref_cur INTO  v_rec;
        EXIT  WHEN  v_ref_cur%NOTFOUND;
 
        DBMS_OUTPUT.PUT_LINE(RPAD(v_rec.id,10,' ')||'      '||RPAD(v_rec.name,15,' ')||'    '||RPAD(v_rec.col_3,15,' ')||'    '||v_rec.col_4);
    END LOOP;
    CLOSE    v_ref_cur;
END;
/*E aquí la forma de usar una función que retorne un SYS_REFCURSOR.*/
---El OUTPUT seria el mismo del ejemplo anterior.
Read 3693 times Last modified on Domingo, 09 Agosto 2020 23:58

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.