5.2. Procedimientos almacenados

Un procedimiento almacenado es un subprograma que ejecuta una acción específica y que no devuelve ningún valor. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.

Construyendo bloques de programas PL/SQL

PL/SQL es un lenguaje estructurado con bloques. Un bloque PL/SQL es definido por las palabras clave DECLARE, BEGIN, EXCEPTION, y END, que dividen el bloque en tres secciones.

  1. Declarativa: sentencias que declaran variables, constantes y otros elementos de código, que después pueden ser usados dentro del bloque
  2. Ejecutable: sentencias que se ejecutan cuando se ejecuta el bloque
  3. Manejo de excepciones: una sección especialmente estructurada para atrapar y manejar cualquier excepción que se produzca durante la ejecución de la sección ejecutable

Sólo la sección ejecutable es obligatoria. No es necesario que usted declare nada en un bloque, ni que maneje las excepciones que se puedan lanzar.

Un bloque es en sí mismo una sentencia ejecutable, por lo que se pueden anidar los bloques unos dentro de otros.

Ejemplo: El clásico '¡Hola Mundo!' es un bloque con una sección ejecutable que llama al procedimiento DBMS_OUTPUT.PUT_LINE para mostrar texto en pantalla:

BEGIN
  DBMS_OUTPUT.put_line('¡Hola Mundo!');
END;

Ejemplo: El siguiente bloque declara una variable de tipo VARCHAR2 (un string) con un largo máximo de 100 bytes para contener el string '¡Hola Mundo!'. Después, el procedimiento DBMS_OUTPUT.PUT_LINE acepta la variable, en lugar del literal, para desplegarlo:

DECLARE
  l_mensaje VARCHAR2(100) := '¡Hola Mundo!';
BEGIN
  DBMS_OUTPUT.put_line(l_mensaje);
END;

Note que he llamado a la variable l_mensaje. Normalmente uso el prefijo l_ para variables locales —variables definidas dentro del código de un bloque— y el prefijo g_ para variables globales definidas en un paquete.

Ejemplo: se demuestra la habilidad de PL/SQL de anidar bloques dentro de bloques así como el uso del operador de concatenación (||) para unir múltiples strings. Además se agrega una sección de manejo de excepciones que atrapa cualquier excepción (WHEN OTHERS) que pueda ser lanzada y muestra el mensaje de error, que es retornado por la función SQLERRM (provista por Oracle).

DECLARE
  l_mensaje VARCHAR2(100) := '¡Hola';
BEGIN
  DECLARE
    l_mensaje2 VARCHAR2(100) := l_mensaje || ' Mundo!'; 
  BEGIN
    DBMS_OUTPUT.put_line(l_mensaje2);
  END;
EXCEPTION
  WHEN OTHERS
    THEN
      DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
/

Ponle nombre a todo

Todos los bloques que hemos visto hasta el momento son 'anónimos', no tienen nombres. Si los bloques anónimos fueran la única manera de organizar el código, sería muy difícil usar PL/SQL para crear una aplicación grande y compleja. Por esto, PL/SQL soporta la definición de bloques nombrados (named blocks), también conocidos como subprogramas. Los subprogramas pueden ser procedimientos o funciones. Generalmente, un procedimiento se utiliza para realizar una acción y una función se utiliza para calcular y devolver un valor.

La sintaxis Oracle de un procedimiento almacenado es la siguiente:

CREATE [OR REPLACE] 
PROCEDURE <nombre_procedure> [(<param1> [IN | OUT | IN OUT] <type>,
                               <param2> [IN | OUT | IN OUT] <type>, ...)] IS
-- Declaración de variables locales
BEGIN
-- Sentencias
   [EXCEPTION]
-- Sentencias control de excepción
END [<nombre_procedure>];

Ejemplo: Precio máximo del dólar historico

CREATE OR REPLACE PROCEDURE precioDolar IS caro NUMBER(8,4);
BEGIN
  SELECT max(precio) INTO caro FROM dolar;
  DBMS_OUTPUT.PUT_LINE('Precio máximo dólar ' || caro);
END;
/

Ejecutar

Parámetros

Los parámetros son un mecanismo muy importante cuando hablamos de funciones o procedimientos en un lenguaje de programación. Adicionalmente en un lenguaje como PL/SQL se pueden definir los parámetros de entrada(por defecto), de salida o ambos.

Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo.

Los parámetros pueden ser de entrada (IN), de salida (OUT) o de entrada salida (IN OUT). El valor por defecto es IN, y se toma ese valor en caso de que no especifiquemos nada.

Tipos y variables en PL/SQL

  1. Para tipos numéricos se usa comúnmente el tipo NUMBER, que engloba enteros como números reales.
  2. PL/SQL permite variables de tipo BOOLEAN, sin embargo, Oracle no soporta dicho tipo para las columnas de la base de datos.
  3. Podemos usar la siguiente sentencia para hacer que la variable 'miCarro' sea declarada con el mismo tipo que tiene la columna 'modelo' en la relación 'Automovil':
    miCarro Automovil.modelo%TYPE;
  4. La siguiente sentencia hace que la variable 'miCarro' sea un registro con los campos 'modelo' y 'year', asumiendo la relación que tiene en el schema automovil(modelo, year):
    miCarro Automovil%ROWTYPE;
  5. El valor inicial de cualquier variable, sin importar su tipo, es NULL.
  6. Para hacer asignaciones usamos el operador ':='. Ejemplo:
    DECLARE 
     miCarro Automovil%ROWTYPE;
    BEGIN
     miCarro.modelo = 'Jetta';
    END;
    /

Para declarar una variable de cadena , debe seleccionar uno de los muchos tipos de datos de cadena Oracle, incluyendo CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB y NCLOB. Los tipos de datos que tienen el prefijo 'N se utilizan para almacenar datos de caracteres Unicode.

Así que , ¿cómo determinar qué tipo de datos a utilizar en sus programas ? Aquí hay algunas pautas:

Si la cadena puede contener más de 32.767 caracteres , utilice tipos CLOB (o NCLOB )

Si el valor asignado a una cadena siempre tiene una longitud fija ( tal como el Registo Federal de Causantes (RFC), que siempre tiene el mismo formato y longitud , AAAA-NNNNNN-XXX), utilice CHAR (o NCHAR).

De lo contrario (y , por lo tanto , la mayoría de las veces ), utilice el tipo de datos VARCHAR2 (o NVACHAR2, cuando se trabaje con datos Unicode).

Utilizando el tipo de datos CHAR para toda cadena que siempre tienen un número fijo de caracteres puede conducir a resultados inesperados e indeseables . Considere el siguiente bloque , que mezcla cadenas de variables y de longitud fija:

Ejemplo: Manejo de cadenas. El objetivo de este código es demostrar lo sutil del manejo de parámetros

DECLARE
  l_variable VARCHAR2(10) := 'Veracruz';
  l_fixed    CHAR (10) := 'Veracruz';
BEGIN
  IF l_variable = l_fixed
  THEN
   DBMS_OUTPUT.put_line ('Igual');
  ELSE
   DBMS_OUTPUT.put_line ('Diferente');
  END IF;
END;
/

Si usted esperaba que el resultado fuera igual te equivocas. La razón l_fixed contiene 10 caracteres y l_variable almacena una cadena con 8 caracteres

Diferentes tamaños máximos.

Hay una serie de diferencias entre SQL y PL/SQL para los tamaños máximos para los tipos de datos de cadena. En PL/SQL, el tamaño máximo para VARCHAR2 es 32.767 bytes, mientras que en SQL el máximo es de 4.000 bytes. En PL/SQL, el tamaño máximo para CHAR es 32.767 bytes , mientras que en SQL el máximo es de 2.000 bytes.

Por lo tanto, si necesitas guardar un valor de una variable VARCHAR2 en la columna de una tabla, puede encontrarse con el error ORA- 12899. Si esto sucede, tenemos dos opciones:

Usar SUBSTR para extraer no más de 4.000 bytes de la cadena, y salvar la subcadena en la tabla. Esta opción tiene claramente un inconveniente: se pierde parte de sus datos.

Cambiar el tipo de datos de la columna de VARCHAR2 a CLOB. De esta manera , puede guardar todos sus datos.

En PL/SQL , el tamaño máximo de CLOB es de 128 terabytes , mientras que en SQL el máximo es simplemente ( 4 GB - 1 )*DB_BLOCK_SIZE

Paso de parámetros

CREATE OR REPLACE PROCEDURE DBA_TASK (dia VARCHAR2) AS 
  BEGIN     
    IF UPPER(dia) = 'LUNES' THEN 
      DBMS_OUTPUT.PUT_LINE('Checar archivos log.');       
    ELSIF UPPER(dia) = 'VIERNES' THEN             
      DBMS_OUTPUT.PUT_LINE('Reconstruir índices.');          
    ELSE    
      DBMS_OUTPUT.PUT_LINE('Leer algunos artículos.');      
    END IF; 
 END;      
/

Ejemplo: Mostrar el número de municipios que pertenecen a un estado en la República Mexicana.

CREATE OR REPLACE PROCEDURE municipiosEstado(nEstado VARCHAR2) AS 
  n NUMBER;
  l_estado VARCHAR2(50);
BEGIN  
   l_estado:= nEstado || '%';
   SELECT COUNT(*) INTO n
   FROM estados E, municipios M
   WHERE E.idEstado = M.idEstado AND E.estado LIKE l_estado;
   DBMS_OUTPUT.PUT_LINE(n  || ' municipios del estado ' || nEstado);
END;
/

Manejo de excepciones

RAISE_APPLICATION_ERROR. La sentencia raise lanza una excepción, deteniendo la ejecución del bloque actual. También establece el mensaje de código de error y el error actual. Mensajes de error, tales como 'ORA-06502: PL / SQL: error numérico o valor' son suministrados por Oracle Database y suelen ser genéricos.

Este tipo de mensaje de error puede ser suficiente para informar de los errores de base de datos, pero lo que si un error de este tipo, como 'empleados es demasiado joven' o 'salario no puede ser mayor de $1,000', aportados por nuestra aplicación. Un mensaje de 'error numérico o valor' no va a ayudar a los usuarios a entender lo que hicieron mal y cómo solucionarlo.

Si usted necesita para pasar un mensaje específico de la aplicación de nuevo a sus usuarios cuando se produce un error, debe ejecutar procedimiento incorporado RAISE_APPLICATION_ERROR. Este procedimiento acepta un número entero (el código de error), cuyo valor debe estar comprendido entre -20.999 y -20.000, y un mensaje de error de tipo cadena.

Cuando se ejecuta este procedimiento, la ejecución del bloque PL / SQL actual se detiene inmediatamente y se produce una excepción (cuyo error de código y el mensaje se establecen a partir de los valores pasados ​​a RAISE_APPLICATION_ERROR). Las llamadas subsiguientes a SQLCODE y SQLERRM aportarán estos valores.

Ejemplo: Validar edad

CREATE OR REPLACE PROCEDURE  validarEdad ( fechaNacimiento_in IN DATE) IS
BEGIN
   IF fechaNacimiento_in > ADD_MONTHS (SYSDATE, -12 * 18)
     THEN RAISE_APPLICATION_ERROR (-20500, 'Eres menor de 18 años');
     ELSE DBMS_OUTPUT.PUT_LINE('Eres mayor de edad');
   END IF;
END;
/

Ejemplo: Consultas sin registros de retorno

  CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)     NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE           NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 );

INSERT INTO DBUSER VALUES(1,'RUBI','EPACHECO',SYSDATE);
INSERT INTO DBUSER VALUES(2,'MERANI','EPACHECO',SYSDATE);
INSERT INTO DBUSER VALUES(3,'EZEQUIEL','EPACHECO',SYSDATE);

 CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
	   p_userid IN DBUSER.USER_ID%TYPE)
IS	   
   usuario DBUSER%ROWTYPE;
BEGIN
  SELECT *  INTO usuario
    FROM  DBUSER WHERE USER_ID = p_userid;
	DBMS_OUTPUT.PUT_LINE(usuario.userName);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('NO existe el usuario con id =' || p_userid);
 END;
/
SET SERVEROUT ON;
EXECUTE getDBUSERByUserId(2);
EXECUTE getDBUSERByUserId(32);

Considere el Caso Prático 1 y su correspondiente diagrama entidad relación

A continuación se presenta un procedimiento almacenado que asigna una calificación a un alumno en una materia X.

El procedimiento posee instrucciones para atrapar, capturar y gestionar esta excepción "ORA-01403: no data found" que sucedera si los nombres de la materia o del alumno no se encontraran en las tablas correspondientes.

EXECUTE inscripcion('Compiladores e Interpretes ','LILIANA LIZBET', 'ENEJUN09',90);

El siguiente procedimiento almacenado registra la renta de una película, checando que el número de películas rentadas no exceda el límite permitido.

execute rentaPelicula('Sergio Avila Pacheco', 39, 3);