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) (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.
Sus Ventajas
- 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 puede ser asignado a otras variables REF CURSOR.
- Una variable REF CURSOR puede ser el valor de retorno de una función.
Nota: Un 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 define 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
El siguiente bloque nos muestra como usar los REF CURSOR de Oracle. Primero declaramos el tipo REF CURSOR typ_ref_cur y posteriormente declaramos la variable v_ref_cur 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 más 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.
SET SERVEROUTPUT ON
DECLARE
TYPE typ_ref_cur IS REF CURSOR;
TYPE typ_rec IS RECORD
(
id VARCHAR2(10),
name VARCHAR2(50),
col_3 VARCHAR2(20),
col_4 VARCHAR2(10)
);
v_rec typ_rec;
v_ref_cur typ_ref_cur;
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 siguiebte ejemplo nos muestra como es posible crear una función que retorne una variable REF CURSOR o más bien SYS_REFCURSOR.
CREATE OR REPLACE FUNCTION f_ref_cursor( p_table NUMBER DEFAULT 1)
RETURN SYS_REFCURSOR
IS
TYPE typ_ref_cur IS REF CURSOR;
v_ref_cur typ_ref_cur;
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;Ahora ejecutamos el siguiente bloque que muestra el uso de la función f_ref_cursor.
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;
Conclusión
Los REF CURSOR representan una herramienta flexible y poderosa dentro de PL/SQL, ya que permiten devolver conjuntos de resultados dinámicos y adaptarse a distintos escenarios de programación. Su uso facilita la interacción entre procedimientos y aplicaciones externas, ofreciendo mayor versatilidad frente a los cursores tradicionales. Dominar esta técnica es clave para optimizar el manejo de datos y construir soluciones más escalables en Oracle.