Anuncios en tutorial de programación PLSQL

martes, 21 de febrero de 2023

La cláusula PIPELINED en las funciones PL/SQL y la excepción NO_DATA_NEEDED

En esta entrada explicaré para que sirve la cláusula PIPELINED en las funciones PL/SQL, y como se debe utilizar la excepción NO_DATA_NEEDED, que nada tiene que ver con la excepción NO_DATA_FOUND, para controlar las funciones PLSQL que incluyen dicha cláusula y que en inglés se denominan pipelined functions. Primero quiero remarcar que la funcionalidad pipelined fue introducida por primera vez en la versión 9i de las bases de datos Oracle. Básicamente, el uso de la cláusula PIPELINED resulta de gran utilidad y es prácticamente imprescindible cuando necesitamos que en lugar de una tabla sea una rutina PL/SQL la que nos sirva como fuente de datos.

Pipelined functions o funciones tubería en PL/SQL

La mejor forma de explicar el funcionamiento de esta cláusula es con un ejemplo.

SQL> CREATE OR REPLACE FUNCTION
2    generador_numeros (n IN NUMBER DEFAULT NULL)
3    RETURN sys.odciNumberList
4    PIPELINED
5    AS
6      BEGIN
7        FOR i IN 1 .. NVL(n,100)
8        LOOP
9          PIPE ROW(i);
10        END LOOP;
11        DBMS_OUTPUT.PUT_LINE
12          ('Ejecutando return');
13        RETURN;
14      END;
15  /
 
Function created

En el ejemplo la cláusula PIPELINED permite que la función generador_numeros funcione exactamente como una tabla. Veamos como.

SQL> SELECT * FROM TABLE(generador_numeros(4));
 
COLUMN_VALUE
------------
1
2
3
4
 
Ejecutando return

Una vez explicado para que sirve la cláusula PIPELINED, ya es posible explicar como se debe utilizar la excepción NO_DATA_NEEDED dentro de este tipo de funciones PLSQL, de hecho se trata de una excepción muy importante cuando estamos escribiendo el código de una pipelined function. Si alguna vez habéis utilizado la cláusula PIPELINED y dentro de la función PL/SQL asociada no habéis utilizado la mencionada excepción, es más que probable que en vuestro código tengáis un bug en estado latente. Ahondando un poco más en el tema, os diré que la excepción NO_DATA_NEEDED aparece cuando una función que utiliza la cláusula PIPELINED puede devolver más datos pero la sentencia SQL que invoca dicha función no los ha solicitado.

Como anteriormente, la mejor forma de explicar cuando aparece y como debemos manejar la excepción NO_DATA_NEEDED es con un ejemplo.

SQL> SELECT * FROM TABLE(generador_numeros(4))
2  WHERE rownum < 3;
 
COLUMN_VALUE
------------
1
2

Aparentemente la función PL/SQL generador_numeros ha funcionado correctamente, pero si nos fijamos bien, la salida por pantalla que yo había incluido al final de la función no se ha ejecutado, es decir, en la pantalla no aparece por ningún lado el esperado "Ejecutando Return". Simplemente esa parte del código no se ha ejecutado porque la sentencia SQL con la que hemos invocado la función generador_numeros no lo necesitaba.

Ciertamente todo parece haber funcionado correctamente, sin embargo, aunque haya permanecido oculto a nuestros ojos, se ha generado una excepción NO_DATA_NEEDED. En este sentido, la excepción NO_DATA_NEEDED se trata de una excepción totalmente diferente a todas las demás, ya que si ésta no se trata dentro del código, simplemente es ignorada (por contra, el resto de excepciones, en caso de no ser tratadas, generan un error).

A continuación cambiaré someramente el código de nuestra función PLSQL pipelined para demostrar lo que estoy diciendo.

SQL> CREATE OR REPLACE FUNCTION
2    generador_numeros (n IN NUMBER DEFAULT NULL)
3    RETURN sys.odciNumberList
4    PIPELINED
5    AS
6      BEGIN
7        FOR i IN 1 .. NVL(n,100)
8        LOOP
9          PIPE ROW(i);
10        END LOOP;
11        DBMS_OUTPUT.PUT_LINE
12          ('Ejecutando return');
13        RETURN;
14      EXCEPTION
15        WHEN NO_DATA_NEEDED
16        THEN
17          DBMS_OUTPUT.PUT_LINE
18            ('Ejecutando excepcion');
19          RETURN;
20      END;
21  /
 
Function created

Ahora si ejecutamos nuestra sentencia SQL obtendremos lo siguiente:

SQL> SELECT * FROM TABLE(generador_numeros(4))
2  WHERE rownum < 3;
 
COLUMN_VALUE
------------
1
2
 
Ejecutando excepcion

Es decir, vemos que la salida por pantalla muestra el texto "Ejecutando excepcion", quedando así demostrado que el bloque EXCEPTION se ha ejecutado.

Por último, sólo me queda mencionar que utilizando la sentencia WHEN OTHERS en lugar de WHEN NO_DATA_NEEDED, habríamos conseguido el mismo resultado, sin embargo no sería la manera correcta de hacerlo ya que estaríamos enmascarando otros errores que podrían generarse por otros motivos. La excepción NO_DATA_NEEDED está especialmente diseñada para tratar este tipo de situaciones y debe ser utilizada de forma apropiada cuando estemos escribiendo una función PL/SQL que utilice la cláusula PIPELINED.

7 comentarios:

Unknown dijo...

Hola, buenas noches.-
Quisiera saber los beneficios que esta cláusula otorga en relación a performance. Es decir, me gustaría tener una noción aproximada de cuanto más beneficioso es ejecutar una consulta a un PIPELINE que a una tabla convencional.-
Desde ya muchas gracias.-
(aprox. no hace falta correr estadísticas en alguna base)

Slds.,

JLPM dijo...

Gastón, las funciones PIPELINED funcionan como tablas pero no tienen nada que ver con ellas, por lo que tampoco tiene nada que ver el rendimiento de unas y otras. El acceso a una tabla puede ser más rápido que utilizar una función PIPELINED, pero también puede ocurrir lo contrario, hay muchos aspectos que pueden influir como la existencia de índices sobre la tabla o la complejidad de los cálculos que se realizan en la función PIPELINED.

luis dijo...

En este caso se retorna el tipo sys.odciNumberList.
Que son estos tipos?
cuales son las opciones?
que tipos no sirven?

JLPM dijo...

Hola Luis, sys.odciNumberList no es nada más que un tipo de dato que viene definido por defecto en lás últimas releases de las bases de datos Oracle.

Es equivalente a definir un tipo de la siguiente manera:

CREATE OR REPLACE TYPE ListaNumeros AS TABLE OF NUMBER;

En este caso el tipo ListaNumeros y el tipo sys.odciNumberList son equivalentes.

Te pongo otro ejemplo de utilización del "AS TABLE OF" para que quede más claro:

DECLARE
  TYPE ListaUsuarios IS TABLE OF VARCHAR2(16);
  mis_usuarios ListaUsuarios;
BEGIN
  mis_usuarios := ListaUsuarios ('Pepe', 'Juan', 'Paco');
  FOR i IN mis_usuarios.FIRST .. mis_usuarios.LAST
  LOOP
    IF mis_usuarios(i) = 'Juan' THEN
      DBMS_OUTPUT.PUT_LINE(i);
    END IF;
  END LOOP;
END;

Valo dijo...

Muchas gracias por la ayuda Pepelu, tengo varios años trabajando con Oracle y hasta ahora voy conociendo para qué sirven las funciones PIPELINED.

Unknown dijo...

Buenas.

Aunque un poco tarde, me ha parecido una entrada bastante interesante que me ha aclarado ciertas dudas acerca de la clausula PIPELINED. De todas formas tengo un par de dudas:

1. Entiendo que se usan para crear tablas a partir de valores que retornan de una función.

2.La estructura seria.. PIPELINED en la cabecera de la función, y después pipe row() para almacenar los registros.. ¿no?.

Saludos!

JLPM dijo...

Hola Fernando, lo que indicas es correcto.