Formateo de valores y utilización de la nueva función CURSOR en SQL

Existe en ambientes UNIX y Linux, algunos utilitarios, que nos permite manipular la información de un conjunto de parámetros, pasados a un script para permitir relacionarlos como parte de una sóla salida de datos.

Muchos lenguajes de secuencias de comandos, tales como Perl y Python, proporcionan funciones que hacen posible, manipular dentro del mismo idioma, lista de valores específicos; por lo que es sorprendente que, aún, esta funcionalidad no forme parte del estándar de programación, como una función nativa en SQL.

En las siguientes líneas, podrás observar como jugar con esta simple función llamada "join" y algunas aplicaciones en las cuáles podemos sacar provecho.

Para empezar, vamos a conectarnos a nuestra base de datos Oracle Database 11gR2 y vamos a crear un usuario de pruebas para el ejercicio.

Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys@lab2 as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create user PR identified by pr;
User created.
SQL> grant create session to pr;
Grant succeeded.
SQL> grant dba to pr;
Grant succeeded.

Conectados con el usuario de pruebas, vamos a crear una tabla simple con algunos campos y la vamos a llenar de información, que sirva de ejemplo, en la salida de las consultas.

Enter value for nombre: RONALD
Enter value for apellido: VARGAS
Enter value for id_empleado: 1
Enter value for fecha: 01-JUN-10

old 1: insert into empleados values ('&nombre', '&apellido',&id_empleado, '&fecha')
new 1: insert into empleados values ('RONALD', 'VARGAS',1, '01-JUN-10')
1 row created.

SQL> /
Enter value for nombre: MAURICIO
Enter value for apellido: MENESES
Enter value for id_empleado: 2
Enter value for fecha: 02-JUN-10

old 1: insert into empleados values ('&nombre', '&apellido',&id_empleado, '&fecha')
new 1: insert into empleados values ('MAURICIO', 'MENESES',2, '02-JUN-10')
1 row created.

SQL> /
Enter value for nombre: ROBERTO
Enter value for apellido: ESPINOZA
Enter value for id_empleado: 3
Enter value for fecha: 03-JUN-10

old 1: insert into empleados values ('&nombre', '&apellido',&id_empleado, '&fecha')

new 1: insert into empleados values ('ROBERTO', 'ESPINOZA',3, '03-JUN-10')

1 row created.

SQL> /

Enter value for nombre: MANUEL
Enter value for apellido: OBREGON
Enter value for id_empleado: 4
Enter value for fecha: 04-JUN-10

old 1: insert into empleados values ('&nombre', '&apellido',&id_empleado, '&fecha')


new 1: insert into empleados values ('MANUEL', 'OBREGON',4, '04-JUN-10')
1 row created.
SQL> COMMIT;
Commit complete.

A continuación, vamos a crear la función "join", la que nos permitirá manipular una lista de parámetros o valores, en la ejecución de una consulta.

SQL>

create or replace function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_valor varchar2(32767);
l_resultado varchar2(32767);

begin

loop
fetch p_cursor into l_valor;
exit when p_cursor%notfound;
if l_resultado is not null then
l_resultado := l_resultado
p_del;
end if;
l_resultado := l_resultado
l_valor;
end loop;
return l_resultado;
end join;
/

SQL> /
Function created.

Podemos utilizar la funcionalidad de la función "join" para especificar una consulta que devuelva una sola columna y un delimitador, como una cadena simple, que contiene una lista de los valores separados por mi delimitador.

La sub-consulta se puede pasar a la función como un CURSOR de referencia, utilizando la nueva función de CURSOR de SQL. El delimitador predeterminado es una coma, ya que es el delimitador más comúnmente utilizado.

SQL> SELECT JOIN(CURSOR(SELECT NOMBRE FROM EMPLEADOS)) FROM DUAL;
JOIN(CURSOR(SELECTNOMBREFROMEMPLEADOS))
--------------------------------------------------------------------------------
RONALD,MAURICIO,ROBERTO,MANUEL

SQL>
SQL> select join(cursor(select trunc(fecha_inclusion,'MONTH') from empleados),'|') from dual;

JOIN(CURSOR(SELECTTRUNC(FECHA_INCLUSION,'MONTH')FROMEMPLEADOS),'
')
---------------------------------------
01-JUN-10|01-JUN-10|01-JUN-10|01-JUN-10

SQL> select join(cursor(select fecha_inclusion from empleados),'|') from dual;
JOIN(CURSOR(SELECTFECHA_INCLUSIONFROMEMPLEADOS),'
')
----------------------------------------
01-JUN-10|02-JUN-10|03-JUN-10|04-JUN-10

Utilicemos la función de "combinación" -join- para comparar dos conjuntos de datos ordenados. Por ejemplo, la siguiente consulta comprobará que se ha creado un índice en una clave externa (foreign key), que ayuda a evitar el bloqueo de la tabla y ayuda en las consultas principales sobre la table detalle.

SQL>
select decode(indexes.table_name,null,'missing','ok') status,
constraints.table_name,
constraints.columns
from
(select table_name,
constraint_name,
join(cursor
(
select column_name
from user_cons_columns
where constraint_name = user_constraints.constraint_name
)) columns
from user_constraints
where constraint_type = 'R'
) constraints,
(select table_name, index_name,
join(cursor
(
select column_name
from user_ind_columns
where index_name = user_indexes.index_name
)) columns
from user_indexes) indexes
where constraints.table_name = indexes.table_name (+)
and constraints.columns = indexes.columns (+)
/