CURSORES, PROCEDIMIENTOS Y FUNCIONES

Los cursores se utilizan en PL/SQL para manejar las sentencias SELECT. Un cursor esta formado por un conjunto de registros devueltos por una instrucción SQL del tipoSELECT. Desde un punto de visto interno a la base de datos Oracle, los cursores son segmentos de memoria utilizados para realizar operaciones con los registros devueltos tras ejecutar una sentencia SELECT.

Se pueden distinguir dos tipos de cursores:

- Cursores implícitos Se utilizan cuando la sentencia SELECT devuelve un solo registro y su formato es como sigue:

     DECLARE
       lsalario empleados.salario%TYPE;
       ldni empleados.dni%TYPE;
     BEGIN
       SELECT salario, dni
       INTO lsalario, ldni
       FROM empleados
       WHERE nombre = 'Juan'
       AND apellidos = 'Rodrigo Comas';
       /* Resto de sentencias del bloque */
     END;
Nota: Mucha gente considera que las sentencias UPDATE, dentro de un bloque PLSQL, son también cursores implícitos, no obstante, yo prefiero no incluirlas dentro de este concepto.

- Cursores explícitos 


DECLARE Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código PL/SQL.

Un cursor explícito tiene que ser definido previamente como cualquier otra variable PLSQL y debe serle asignado un nombre. Veamos un ejemplo que muestra el DNI y el salario de los trabajadores incluidos en la tabla empleados:

DECLARE
Cursor cursor1 IS
SELECT ename from emp;
v_ename varchar2 (30);
Begin 
OPEN cursor1;
LOOP
FETCH cursor1 INTO v_ename;
EXIT WHEN cursor1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename);
END LOOP;
CLOSE cursor1;
END;
/

SQL> start cursor1
KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
SCOTT
ADAMS
MILLER


Procedimiento PL/SQL terminado con éxito.

Los cursores explícitos admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor. Por ejemplo: 


     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS 
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE; 
       vnombre VARCHAR2(20); 
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       FOR cemp_rec IN cemp(vnombre)
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;
      /


Los cursores se han controlado con la sentencia FOR pero también pueden controlarse mediante el uso de las sentencias OPEN, FETCH y CLOSE. La sentencia OPEN identifica el cursor que se tiene que utilizar. La sentencia FETCH pone, registro a registro, los valores devueltos por el cursor en las variables correspondientes, variables que pueden estar constituidas por una lista de variables o un registro PLSQL (este es el caso de los ejemplos que incluyo en este artículo). Por último, la sentencia CLOSE cierra el cursor y libera la memoria reservada. Veamos como quedaría nuestro ejemplo utilizando este tipo de sentencias en lugar de utilizar la sentencia FOR:


     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS 
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE; 
       vnombre VARCHAR2(20); 
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       OPEN cemp(vnombre);
       LOOP
         FETCH cemp INTO cemp_rec;
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
         EXIT WHEN cemp%NOTFOUND; -- Último registro.
       END LOOP;
       DBMS_OUTPUT.PUT_LINE
         ('Número de empleados procesados ' || cemp%ROWCOUNT);
       CLOSE cemp;
     END;




Finalmente sólo mencionar que existen cuatro tipos de atributos que nos permiten controlar la ejecución de un cursor:


- %ISOPEN: Devuelve "true" si el cursor está abierto.
- %FOUND: Devuelve "true" si el registro fue satisfactoriamente procesado.
- %NOTFOUND: Devuelve "true" si el registro no pudo ser procesado. Normalmente esto ocurre cuando ya se han procesado todos los registros devueltos por el cursor.
- %ROWCOUNT: Devuelve el número de registros que han sido procesados hasta ese momento.

PROCEDIMIENTOS y FUNCIONES

Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.

Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.

Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.

Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:
- Verificar si el usuario tiene permiso de ejecución.
- Verificar la validez del procedimiento o función.
- Y finalmente ejecutarlo.

Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:
- Facilidad para gestionar la seguridad.
- Mejor rendimiento al estar compilados y almacenados en la base de datos.
- Mejor gestión de la memoria.
- Mayor productividad e integridad.

La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).

Sintaxis de un procedimiento PLSQL:

   CREATE OR REPLACE PROCEDURE [esquema].nombre-procedim
      (nombre-parámetro {IN, OUT, IN OUT} tipo de dato, ..) {IS, AS}
         Declaración de variables;
         Declaración de constantes;
         Declaración de cursores;
         BEGIN
            Cuerpo del subprograma PL/SQL;
            EXCEPTION
            Bloque de excepciones PL/SQL;
         END;

Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.

IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.

OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.

IN OUT: Son parámetros de entrada y salida a la vez.

Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

Ejemplo:

CREATE OR REPLACE procedure ver_nombre

(v_deptno number)

AS

v_nombre emp.ename%TYPE; 

BEGIN

select ename into v_nombre

from emp

where sal=(select Max(sal)

from emp

where deptno=v_deptno);

DBMS_OUTPUT.PUT_line(v_nombre);

END;

/