SQL Procedures: Ejecución y Ejemplos en Oracle PL/SQL

En este tutorial vamos a explorar cómo crear, compilar y ejecutar un procedimiento PL/SQL desde el entorno de desarrollo Oracle SQL Developer. Los procedimientos PL/SQL son unidades reutilizables que encapsulan la lógica de negocio específica de una aplicación. Al utilizar procedimientos, podemos simplificar y organizar nuestro código, al tiempo que aumentamos la eficiencia y la legibilidad del mismo. En este artículo, aprenderemos la sintaxis básica para la creación de procedimientos PL/SQL, los diferentes tipos de parámetros y las tres partes principales que los componen: declarativa, ejecutable y de manejo de excepciones.

Creación de un procedimiento PL/SQL

El primer paso para crear un procedimiento PL/SQL en Oracle SQL Developer es abrir una conexión a la base de datos en la que queremos trabajar. Una vez conectados, podemos abrir una nueva ventana de «SQL Worksheet» para comenzar a escribir nuestro código.

Para crear un procedimiento, utilizaremos la siguiente sintaxis:


CREATE OR REPLACE PROCEDURE nombre_procedimiento (parametro1 tipo, parametro2 tipo, ...)
IS
-- Declaraciones
BEGIN
-- Lógica de negocio
EXCEPTION
-- Manejo de excepciones
END;
/


En esta sintaxis, «nombre_procedimiento» es el nombre que queremos darle al procedimiento y «tipo» es el tipo de dato que esperamos recibir como parámetro. Podemos tener uno o varios parámetros, y cada uno puede ser de tipo «IN», «OUT» o «IN OUT», dependiendo de si queremos que el parámetro sea solo de entrada, solo de salida o ambos.

La sección de «Declaraciones» es donde declaramos variables, constantes, cursores u otros objetos necesarios para nuestro procedimiento.

La sección de «Lógica de negocio» es donde escribiremos las sentencias que implementan la lógica específica de nuestra aplicación.

En la sección de «Manejo de excepciones» podemos incluir código para manejar y controlar cualquier excepción que ocurra durante la ejecución del procedimiento.

Una vez que hayamos completado el código del procedimiento, podemos compilarlo utilizando el comando «CREATE OR REPLACE PROCEDURE«. Si todo sale bien, recibiremos un mensaje indicando que el procedimiento ha sido creado correctamente.

Especificación y parámetros de un procedimiento

Al crear un procedimiento PL/SQL, es importante especificar la lista de parámetros que el procedimiento espera recibir. Los parámetros nos permiten pasar valores al procedimiento y utilizarlos en nuestra lógica de negocio. Además, la especificación de los parámetros nos ayuda a comunicar claramente qué tipo de datos espera y cómo se deben utilizar.

En la especificación del procedimiento, cada parámetro se define con un nombre y un tipo de dato. Además, se debe indicar si el parámetro es de tipo «IN», «OUT» o «IN OUT». Aquí hay algunos ejemplos de cómo se pueden declarar los parámetros en la especificación de un procedimiento:


CREATE OR REPLACE PROCEDURE calcular_promedio (IN notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
-- Declaraciones
BEGIN
-- Lógica de negocio
END;
/


En este ejemplo, tenemos dos parámetros: «notas_arr» y «promedio». El parámetro «notas_arr» es de tipo «sys.odcinumberlist» y se declara como «IN». Esto significa que es un parámetro de entrada, lo que indica que solo puede ser leído en el procedimiento. El parámetro «promedio» es de tipo «NUMBER» y se declara como «OUT». Esto indica que es un parámetro de salida, lo que significa que el procedimiento puede modificar su valor y devolverlo al código que lo llamó.

También es posible declarar parámetros de tipo «IN OUT», lo que significa que pueden ser leídos y modificados en el procedimiento. Aquí hay otro ejemplo:


CREATE OR REPLACE PROCEDURE incrementar_valor (IN OUT numero INT)
IS
-- Declaraciones
BEGIN
-- Lógica de negocio
END;
/


En este caso, el parámetro «numero» se declara como «IN OUT», lo que indica que puede ser leído y modificado en el procedimiento.

La especificación y el uso correcto de los parámetros nos permiten crear procedimientos flexibles y reutilizables, que pueden adaptarse a diferentes situaciones y requerimientos.

Cuerpo del procedimiento

El cuerpo de un procedimiento PL/SQL consta de tres partes: la sección declarativa, la sección ejecutable y la sección de manejo de excepciones. Estas partes nos permiten definir y organizar la lógica de negocio específica de nuestro procedimiento.

En la sección declarativa, declaramos las variables, constantes, cursores y otros objetos necesarios para el funcionamiento del procedimiento. Aquí es donde definimos nuestros objetos y les asignamos un tipo y un valor inicial si es necesario. Por ejemplo:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
-- Lógica de negocio
END;
/


En este ejemplo, declaramos dos variables: «total» y «contador». En este caso, «total» y «contador» son de tipo «NUMBER» y se inicializan con un valor de 0. Estas variables se pueden utilizar para realizar cálculos dentro del procedimiento.

La sección ejecutable es donde escribimos las sentencias que implementan la lógica de negocio específica de nuestro procedimiento. Aquí es donde realizamos operaciones, llamamos a funciones, manipulamos datos, realizamos bucles y estructuras de control, entre otras acciones. Por ejemplo:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
FOR i IN 1..notas_arr.COUNT LOOP
total := total + notas_arr(i);
contador := contador + 1;
END LOOP;

promedio := total / contador;
END;
/


En este ejemplo, utilizamos un bucle «FOR» para recorrer los elementos del arreglo «notas_arr». En cada iteración, sumamos el valor del elemento a la variable «total» y aumentamos el contador en 1. Al final del bucle, calculamos el promedio dividiendo el total entre el contador y asignamos el resultado a la variable «promedio».

Por último, está la sección de manejo de excepciones. Aquí es donde podemos manejar y controlar cualquier excepción que pueda ocurrir durante la ejecución del procedimiento. Podemos capturar errores específicos, mostrar mensajes de error personalizados y tomar acciones correctivas según sea necesario. Por ejemplo:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
FOR i IN 1..notas_arr.COUNT LOOP
total := total + notas_arr(i);
contador := contador + 1;
END LOOP;

promedio := total / contador;

IF contador = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Error: No hay notas para calcular el promedio.');
END IF;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/


En este ejemplo, hemos agregado una estructura condicional «IF» para verificar si el contador es igual a cero. Si es así, utilizamos la función «RAISE_APPLICATION_ERROR» para generar un error personalizado y mostrar un mensaje de error. La cláusula «WHEN OTHERS» captura cualquier otra excepción que pueda ocurrir y la reenvía para su manejo.

Combinando estas tres secciones, podemos crear procedimientos PL/SQL poderosos y flexibles que implementan la lógica de negocio específica de nuestra aplicación.

Parte declarativa

La parte declarativa de un procedimiento PL/SQL es donde declaramos variables, constantes, cursores y otros objetos necesarios para el funcionamiento del procedimiento. Estas declaraciones nos permiten definir y reservar espacio en la memoria para almacenar datos que necesitaremos utilizar en el cuerpo del procedimiento.

En la sección declarativa, las declaraciones se colocan después de la palabra clave «IS» y antes de la sección ejecutable del procedimiento. Aquí hay algunos ejemplos de declaraciones que se pueden incluir:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
constante VARCHAR2(50) := 'Hola, mundo!';
cursor_cur sys_refcursor;
END;
PROCEDURE;
/


En este ejemplo, hemos declarado varias variables y una constante. La variable «total» es de tipo «NUMBER» y se ha inicializado con un valor de 0. La variable «contador» también es de tipo «NUMBER» y se ha inicializado con un valor de 0. La constante «constante» es de tipo «VARCHAR2» y tiene una longitud máxima de 50 caracteres. Esta constante se puede utilizar para almacenar un valor fijo que no cambiará durante la ejecución del procedimiento.

También hemos declarado un cursor llamado «cursor_cur». Los cursores son objetos que nos permiten recuperar y manipular conjuntos de datos en Oracle.

Además de las variables, constantes y cursores, también podemos declarar otros objetos, como tipos definidos por el usuario, tablas temporales, excepciones personalizadas, entre otros.

Las declaraciones nos permiten definir y organizar todos los objetos necesarios para nuestro procedimiento, lo que facilita la escritura y la comprensión del código. Además, al declarar variables y constantes, podemos asegurarnos de que los datos se utilicen correctamente y se reserven suficiente espacio en la memoria para ellos.

Parte ejecutable

La parte ejecutable de un procedimiento PL/SQL es donde escribimos las sentencias que implementan la lógica de negocio específica de nuestro procedimiento. Esta sección es donde realizamos operaciones, llamamos a funciones, manipulamos datos y ejecutamos cualquier otra acción necesaria para lograr el objetivo del procedimiento.

En la sección ejecutable, las sentencias se colocan entre las palabras clave «BEGIN» y «END». Aquí hay algunos ejemplos de lo que se puede hacer en esta sección:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
FOR i IN 1..notas_arr.COUNT LOOP
total := total + notas_arr(i);
contador := contador + 1;
END LOOP;

promedio := total / contador;

IF promedio > 90 THEN
dbms_output.put_line('El promedio es excelente');
ELSIF promedio > 70 THEN
dbms_output.put_line('El promedio es bueno');
ELSE
dbms_output.put_line('El promedio es regular');
END IF;

DBMS_OUTPUT.PUT_LINE('La suma total de las notas es: ' || total);
END;
/


En este ejemplo, utilizamos un bucle «FOR» para recorrer los elementos del arreglo «notas_arr». En cada iteración, sumamos el valor del elemento a la variable «total» y aumentamos el contador en 1. Al final del bucle, calculamos el promedio dividiendo el total entre el contador y asignamos el resultado a la variable «promedio».

Luego, utilizamos una estructura condicional «IF» para imprimir un mensaje según el valor del promedio. Si el promedio es mayor a 90, se imprimirá «El promedio es excelente». Si el promedio es mayor a 70 pero menor o igual a 90, se imprimirá «El promedio es bueno». De lo contrario, se imprimirá «El promedio es regular».

Finalmente, utilizamos la función DBMS_OUTPUT.PUT_LINE para imprimir en la consola el mensaje «La suma total de las notas es: » concatenado con el valor de la variable «total». Esto nos permitirá ver el resultado de la suma total de las notas al ejecutar el procedimiento.

En la sección ejecutable, podemos utilizar sentencias SQL, PL/SQL y llamadas a funciones o procedimientos almacenados para implementar nuestra lógica de negocio de manera efectiva.

Parte de manejo de excepciones

La parte de manejo de excepciones es una parte crucial en un procedimiento PL/SQL. En esta sección, podemos manejar y controlar cualquier excepción inesperada que pueda ocurrir durante la ejecución del procedimiento. El manejo adecuado de las excepciones nos permite identificar y solucionar posibles problemas, así como proporcionar mensajes de error claros y descriptivos.

En la sección de manejo de excepciones, podemos utilizar la palabra clave «EXCEPTION» seguida de una o más cláusulas «WHEN». Cada cláusula «WHEN» se utiliza para capturar un tipo específico de excepción y proporcionar el código para manejarla.

Veamos un ejemplo de cómo se puede implementar esta sección en un procedimiento:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
FOR i IN 1..notas_arr.COUNT LOOP
total := total + notas_arr(i);
contador := contador + 1;
END LOOP;

promedio := total / contador;

IF contador = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Error: No hay notas para calcular el promedio.');
END IF;

EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Error: División por cero.');
WHEN OTHERS THEN
dbms_output.put_line('Error inesperado: ' || SQLERRM);
END;
/


En este ejemplo, hemos agregado una cláusula «WHEN» para capturar la excepción «ZERO_DIVIDE», que ocurre cuando se intenta dividir entre cero. En este caso, imprimimos un mensaje indicando que se produjo un error de división por cero.

También hemos agregado una cláusula «WHEN OTHERS» para capturar cualquier otra excepción que no se haya mencionado específicamente. En este caso, utilizamos la función SQLERRM para obtener información detallada sobre el error y lo imprimimos en la consola.

En algunos casos, podemos querer reenviar la excepción después de manejarla en nuestra sección de manejo de excepciones utilizando la sentencia «RAISE». Esto permitirá que la excepción sea manejada por un bloque de código externo.

El manejo adecuado de las excepciones nos permite tener un control más preciso sobre los errores que puedan ocurrir durante la ejecución del procedimiento y proporcionar una respuesta adecuada y descriptiva en caso de que algo salga mal.

Compilación de un procedimiento

La compilación de un procedimiento PL/SQL en Oracle SQL Developer es un paso crucial antes de poder ejecutarlo. La compilación del procedimiento verifica la sintaxis y la integridad del código para asegurarse de que no se produzcan errores durante la ejecución.

Para compilar un procedimiento, podemos utilizar el comando «CREATE OR REPLACE PROCEDURE» seguido del nombre del procedimiento y la lista de parámetros. Por ejemplo:


CREATE OR REPLACE PROCEDURE calcular_promedio (notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
-- Lógica de negocio
END;
/


Una vez que hayamos escrito el código del procedimiento, podemos ejecutar el comando anterior en una ventana de «SQL Worksheet» en Oracle SQL Developer.

Si el procedimiento no contiene errores de sintaxis o referencia a objetos inexistentes, recibiremos un mensaje de éxito indicando que se ha creado o reemplazado el procedimiento correctamente.

Si el procedimiento contiene errores, recibiremos mensajes de error específicos que nos indicarán la naturaleza y ubicación de los problemas. Estos mensajes nos ayudarán a corregir los errores antes de volver a intentar compilar el procedimiento.

Es importante destacar que la compilación del procedimiento no ejecuta el código del mismo. Solo verifica su validez sintáctica. Para ejecutar el procedimiento después de haberlo compilado correctamente, hay que llamarlo utilizando una instrucción de ejecución adecuada, como «EXECUTE» o «CALL«.

Compilar el procedimiento antes de ejecutarlo es una mejor práctica de programación, ya que nos ayuda a identificar y resolver errores temprano, evitando problemas durante la ejecución del código.

Ejecución de un procedimiento

Para ejecutar un procedimiento PL/SQL en Oracle SQL Developer, podemos utilizar el comando «EXECUTE» o «CALL» seguido del nombre del procedimiento y los valores de los parámetros requeridos. A continuación, se muestra un ejemplo:


EXECUTE calcular_promedio(notas_arr => notas, promedio => resultado);


En este ejemplo, estamos ejecutando el procedimiento «calcular_promedio» y pasando los valores de los parámetros requeridos, «notas_arr» y «promedio». Los nombres de los parámetros se especifican utilizando la sintaxis «nombre_parametro => valor», asegurándonos de proporcionar valores adecuados para los parámetros definidos en la especificación del procedimiento.

Cuando ejecutamos el procedimiento, el código dentro de la sección ejecutable del procedimiento se ejecutará, realizando las operaciones y lógica de negocio especificadas. En este caso, se calculará el promedio de las notas proporcionadas y se almacenará en la variable «resultado».

Después de ejecutar el procedimiento, podemos verificar el resultado interactuando con las variables o proporcionando mensajes de salida utilizando la función «DBMS_OUTPUT.PUT_LINE», que imprimirá información en la consola.

Es importante tener en cuenta que algunos procedimientos pueden requerir parámetros de entrada, mientras que otros pueden no requerir ningún parámetro. Asegúrate de conocer los requisitos específicos de los procedimientos que deseas ejecutar antes de intentar ejecutarlos.

La ejecución de un procedimiento permite poner en funcionamiento la lógica de negocio encapsulada en el procedimiento, permitiendo realizar acciones específicas o cálculos necesarios para el flujo de trabajo de una aplicación o sistema.

Edición de un procedimiento

En Oracle SQL Developer, podemos realizar ediciones en un procedimiento PL/SQL de manera sencilla. Si queremos modificar el código de un procedimiento existente, podemos utilizar la sentencia «CREATE OR REPLACE PROCEDURE» seguida del nombre del procedimiento y la lista de parámetros actualizados.

A continuación, se muestra un ejemplo de cómo podemos editar un procedimiento:


CREATE OR REPLACE PROCEDURE calcular_promedio (IN notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
FOR i IN 1..notas_arr.COUNT LOOP
total := total + notas_arr(i);
contador := contador + 1;
END LOOP;

promedio := total / contador;

IF contador = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Error: No hay notas para calcular el promedio.');
END IF;
END;
/


Supongamos que deseamos agregar una verificación adicional para manejar el caso en el que el arreglo de notas esté vacío. Podemos realizar la edición de la siguiente manera:


CREATE OR REPLACE PROCEDURE calcular_promedio (IN notas_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
total NUMBER := 0;
contador NUMBER := 0;
BEGIN
IF notas_arr IS NULL OR notas_arr.COUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'Error: No hay notas para calcular el promedio.');
END IF;

FOR i IN 1..notas_arr.COUNT LOOP
total := total + notas_arr(i);
contador := contador + 1;
END LOOP;

promedio := total / contador;
END;
/


En este ejemplo, hemos agregado una verificación adicional al inicio del procedimiento para comprobar si el arreglo de notas es nulo o si su tamaño es igual a cero. Si se cumple esta condición, utilizamos la función «RAISE_APPLICATION_ERROR» para generar un error personalizado y mostrar un mensaje de error.

Después de realizar la edición, podemos ejecutar el nuevo código del procedimiento utilizando el comando «EXECUTE» o «CALL». Las ediciones realizadas se aplicarán y se ejecutarán en la próxima ejecución del procedimiento. How to execute a procedure in oracle

La capacidad de editar un procedimiento fácilmente nos permite realizar mejoras y correcciones en el código existente, manteniendo la flexibilidad y la adaptabilidad de nuestra lógica de negocio.

Eliminación de un procedimiento

Para eliminar un procedimiento PL/SQL en Oracle SQL Developer, podemos utilizar el comando «DROP PROCEDURE» seguido del nombre del procedimiento que deseamos eliminar. A continuación, se muestra un ejemplo:


DROP PROCEDURE calcular_promedio;


En este ejemplo, el procedimiento llamado «calcular_promedio» será eliminado de la base de datos. Es importante tener en cuenta que una vez eliminado, el procedimiento y todo su código asociado no estarán disponibles para su ejecución.

Antes de eliminar un procedimiento, es importante tener en cuenta que esta acción es irreversible y que se perderá todo el código y la funcionalidad asociada a dicho procedimiento. Por lo tanto, debemos asegurarnos de que realmente deseamos eliminar el procedimiento antes de ejecutar el comando.

Además, es importante destacar que solo los usuarios con los privilegios adecuados pueden eliminar procedimientos. Si no tienes los permisos necesarios, no podrás eliminar el procedimiento.

La eliminación de procedimientos es útil cuando queremos eliminar procedimientos antiguos, redundantes o innecesarios de nuestras bases de datos, lo que ayuda a mantener un entorno de desarrollo limpio y organizado.

Ejemplos de procedimientos PL/SQL

A continuación, te presento algunos ejemplos de procedimientos PL/SQL que muestran diferentes casos de uso y funcionalidades:

Ejemplo 1: Procedimiento para obtener el promedio de un conjunto de números:
sql
CREATE OR REPLACE PROCEDURE calcular_promedio(nums_arr sys.odcinumberlist, OUT promedio NUMBER)
IS
suma NUMBER := 0;
contador NUMBER := 0;
BEGIN
FOR i IN 1..nums_arr.COUNT LOOP
suma := suma + nums_arr(i);
contador := contador + 1;
END LOOP;

IF contador = 0 THEN
RAISE_APPLICATION_ERROR(-20001, ‘Error: No hay números para calcular el promedio.’);
END IF;

promedio := suma / contador;
END;
/

Este procedimiento toma un arreglo de números y calcula su promedio. Si el arreglo está vacío, se generará un error personalizado.

Ejemplo 2: Procedimiento para realizar una transferencia de fondos entre cuentas:
sql
CREATE OR REPLACE PROCEDURE transferir_fondos(cuenta_origen NUMBER, cuenta_destino NUMBER, monto NUMBER)
IS
saldo_origen NUMBER;
saldo_destino NUMBER;
BEGIN
SELECT saldo INTO saldo_origen FROM cuentas WHERE numero = cuenta_origen;
SELECT saldo INTO saldo_destino FROM cuentas WHERE numero = cuenta_destino;

IF saldo_origen < monto THEN RAISE_APPLICATION_ERROR(-20002, 'Error: Fondos insuficientes en la cuenta origen.'); END IF; UPDATE cuentas SET saldo = saldo - monto WHERE numero = cuenta_origen; UPDATE cuentas SET saldo = saldo + monto WHERE numero = cuenta_destino; COMMIT; END; /Este procedimiento realiza una transferencia de fondos entre dos cuentas bancarias. Verifica que la cuenta origen tenga suficientes fondos antes de realizar la transferencia y actualiza los saldos de las cuentas involucradas.Ejemplo 3: Procedimiento para insertar un nuevo empleado en una tabla:
sql
CREATE OR REPLACE PROCEDURE insertar_empleado(nombre VARCHAR2, salario NUMBER, cargo VARCHAR2)
IS
BEGIN
INSERT INTO empleados (nombre, salario, cargo) VALUES (nombre, salario, cargo);
COMMIT;
END;
/

Este procedimiento recibe los detalles de un nuevo empleado y los inserta en una tabla de empleados.

Estos son solo algunos ejemplos de cómo se pueden usar los procedimientos PL/SQL para implementar lógica de negocio en Oracle SQL Developer. Los procedimientos ofrecen una forma poderosa de encapsular la funcionalidad y reutilizarla en diferentes partes de una aplicación.