Objetivos:---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) (INSERT, UPDATE, DELETE o 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.
• 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.
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:
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 CURSOR: typ_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 SQL, Query 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:
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;
/
SHOW ERROR
/*Este ejemplo nos muestra como es posible crear una función que retorne una variable REF CURSOR o mas bien SYS_REFCURSOR.
Mas adelante vemos como podemos usar el valor retornado por dicha función.*/
Mas adelante vemos como podemos usar el valor retornado por dicha función.*/
----
SET SERVEROUTPUT ON
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.
________________________________________________________________________________
Fuentes: https://docs.oracle.com/cd/E17781_01/appdev.112/e18751/procedures_plsql.htm#TDPNG60000
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
https://community.oracle.com/docs/DOC-915270
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
https://community.oracle.com/docs/DOC-915270
