Capítulo 6 - Procedimientos y Procedimientos Almacenados

Los bloques de código anónimos BEGIN .. END, proveen un mecanismo básico para la programación en PL/SQL, pero uno de sus problemas principales es que impiden la reutilización de SCRIPTS, si se tuviera un algoritmo , como por ejemplo, para calcular algún monto según determinados parámetros tendríamos que repetirlo cuantas veces sea necesario.

El uso de procedimientos en PL/SQL supone un buen mecanismo para promover la reutilización de código, además de que permite dividir el código en partes funcionales  individuales, adicionalmente los procedimientos pueden ser declarados en bloques anónimos o almacenarnos en la misma base de datos.

DECLARACIÓN DE PROCEDIMIENTOS

La creación de un procedimiento en PL/SQL es similar a la creación de un bloque anónimo. Veamos la sintaxis de un procedimiento:

PROCEDURE <NOMBRE_PROCEDIMIENTO>(<PARAMETROS>)
IS
  <VARIABLES>
BEGIN
  <CÓDIGO PL/SQL>
END;

Dónde:

  • <NOMBRE_PROCEDIMIENTO>: Es el nombre del procedimiento, el cual se usará para identificarlo.
  • <PARÁMETROS>: Los parámetros son como variables, contienen datos que se pueden especificar al momento de llamar al procedimiento.
  • <VARIABLES>: Como en un bloque anónimo, en los procedimientos se pueden crear variables, pero éstas variables sólo pueden usadas en código dentro del procedimiento.
  • <CÓDIGO PL/SQL>: Es el código propio ejecutado al momento de llamar al procedimiento, se pueden hacer uso de las variables declaradas así como de los parámetros.

Para crear un procedimiento dentro de un bloque anónimo, éste se debe crear dentro de la sección DECLARE ... BEGIN.

La tabla HR.CLIENTE, se ha creado en el capítulo anterior, debes asegurarte de haber creado la tabla HR.CLIENTE antes de ejecutar los ejemplos de éste capítulo.
CREATE TABLE HR.CLIENTE (
        id NUMBER NOT NULL,
        nombres VARCHAR(200) NOT NULL,
        apellidos VARCHAR(200) NOT NULL,
        fecha_afiliacion DATE NOT NULL,
        PRIMARY KEY(id)
);
DECLARE
  -- El procedimiento debe ser declarado dentro de la sección DECLARE .. BEGIN
  PROCEDURE REGISTRAR_CLIENTE(P_ID        NUMBER,
                              P_NOMBRES   VARCHAR2,
                              P_APELLIDOS VARCHAR2,
                              P_FECHA     DATE)
  IS
  BEGIN
    INSERT INTO hr.cliente(id,nombres,apellidos,fecha_afiliacion)
       VALUES (P_ID,P_NOMBRES,P_APELLIDOS, P_FECHA);
    DBMS_OUTPUT.PUT_LINE('Insertado cliente: '|| P_ID || ' ' || P_NOMBRES || ' ' || P_APELLIDOS);
  END;
BEGIN
  -- Eliminamos los registros anteriores de la tabla CLIENTE para evitar conflictos con sus ID.
  DELETE FROM hr.cliente;

  -- Para utilizar el procedimiento creado debemos invocarlo por su nombre,
  -- adicionalmente, debemos especificar los valores para los parámetros de la función
  REGISTRAR_CLIENTE(1,'Juan', 'Rosales', SYSDATE);
  REGISTRAR_CLIENTE(2,'Luis', 'Cabrera', SYSDATE);
  REGISTRAR_CLIENTE(3,'Pedro', 'Morales', TO_DATE('13/09/80','DD/MM/YYYY'));
END;
Insertado cliente: 1 Juan Rosales
Insertado cliente: 2 Luis Cabrera
Insertado cliente: 3 Pedro Morales

Para comprobar los registros insertados en la tabla HR.CLIENTE, podemos utilizan una simple consulta SQL:

SELECT *
  FROM hr.cliente

PROCEDIMIENTOS ALMACENADOS

Los procedimientos almacenados permiten "almacenar" los procedimientos para ser utilizados desde cualquier bloque anónimo sin que haya la necesidad de declararlo, adicionalmente se puede utilizar en otros procedimientos. Para crear un procedimiento almacenado debemos anteponer la palabra reservada CREATE y ejecutar el código como si se tratase de un bloque PL/SQL.

El procedimiento almacenado es compilado previamente por el motor PL/SQL, si la compilación es satisfactoria podremos llamar al procedimiento almacenado como cualquier otro. Es recomendable agregar el nombre del esquema previamente al nombre de procedimiento, por este motivo, el nombre del procedimiento de ejemplo se llama HR.REGISTRAR_CLIENTE.
-- Agregando la palabra reservada CREATE,
-- podemos almacenar el procedimiento en la base de datos
-- para poderla reutilizar desde cualquier bloque anónimo
-- o procedimiento
CREATE PROCEDURE HR.REGISTRAR_CLIENTE(P_ID        NUMBER,
                                      P_NOMBRES   VARCHAR2,
                                      P_APELLIDOS VARCHAR2,
                                      P_FECHA     DATE)
IS
BEGIN
  INSERT INTO hr.cliente(id,nombres,apellidos,fecha_afiliacion)
     VALUES (P_ID,P_NOMBRES,P_APELLIDOS, P_FECHA);
  DBMS_OUTPUT.PUT_LINE('Insertado cliente: '|| P_ID || ' ' || P_NOMBRES || ' ' || P_APELLIDOS);
END;
Adicionalmente, puedes agregar las palabras reservadas OR REPLACE, lo cual te evitará errores cuando intentes volver a compilar un procedimiento que ya ha sido compilado. La declaración quedaría como CREATE OR REPLACE PROCEDURE HR.REGISTRAR_CLIENTE.

Si un procedimiento se compila correctamente,la pestaña Salida de Script del SQL Developer mostrará un mensaje indicando que el procedimiento se ha compilado .

Finalmente, podremos usar el procedimiento desde cualquier bloque anónimo sin necesidad de de declarar dicho procedimiento.

BEGIN
  DELETE FROM hr.cliente;
  HR.REGISTRAR_CLIENTE(1,'Miguel', 'Rosales', SYSDATE);
  HR.REGISTRAR_CLIENTE(2,'Pedro', 'Rodriguez', SYSDATE);
  HR.REGISTRAR_CLIENTE(3,'Manuel', 'Licierns', TO_DATE('13/09/80','DD/MM/YYYY'));
END;
Toma en cuenta que ya no es necesario declarar el procedimiento dentro de la sección DECLARE .. BEGIN del bloque anónimo, simplemente llamamos al procedimiento que previamente hemos tenido que compilar.
Tipo de Recurso: 
Cursos