Procedimiento almacenado en SQL - CIBERNINJAS

Los procedimientos almacenados son una parte fundamental de las bases de datos SQL. Consisten en código SQL preparado que se puede guardar y reutilizar.

Esta característica ayuda a evitar escribir las mismas consultas repetidamente. Puede llamar al procedimiento almacenado para ejecutar el código guardado.

Además, los procedimientos almacenados pueden aceptar parámetros, lo que los hace versátiles y dinámicos.

Este artículo cubrirá todo sobre los procedimientos almacenados en SQL, incluida su definición, implementación y casos de uso.

Descripción general

Comprenda qué son los procedimientos almacenados en SQL.
Conozca cómo y cuándo utilizar procedimientos almacenados en SQL.
Aprenda a utilizar parámetros en procedimientos almacenados.
Aprenda a implementar procedimientos almacenados en SQL Server, Oracle, MySQL y PostgreSQL.

👉 ¿Qué Encontrarás Aquí? ❤️ Índice del Contenido

Definición y beneficios de los procedimientos almacenados

Un procedimiento almacenado es un conjunto de instrucciones SQL que realizan una tarea específica.

Puede guardar estos procedimientos y ejecutarlos según sea necesario, lo que hace que sus operaciones SQL sean más eficientes y organizadas.

Los procedimientos almacenados funcionan como funciones en programación, lo que le permite ejecutar acciones predefinidas fácilmente.

Ofrecen muchos beneficios: puede reutilizar el código, mejorar el rendimiento ya que se ejecutan más rápido que las instrucciones SQL individuales, mejorar la seguridad al controlar el acceso de los usuarios y simplificar el mantenimiento al centralizar su código SQL para facilitar las actualizaciones y la depuración.

Creación de procedimientos almacenados

Crear un procedimiento almacenado es sencillo. La sintaxis básica implica el comando CREATE PROCEDURE seguido del nombre del procedimiento y las instrucciones SQL que contiene.

A continuación, se muestra un ejemplo sencillo:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Ejemplo en SQL Server

En SQL Server, la creación de un procedimiento almacenado podría verse así:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Este procedimiento selecciona todos los registros de la tabla Clientes.

Ejemplo en MySQL

En MySQL, la sintaxis es ligeramente distinta. A continuación, se muestra cómo crear un procedimiento similar:

DELIMITER //
CREATE PROCEDURE SelectAllCustomers()
BEGIN
SELECT * FROM Customers;
END //
DELIMITER ;

MySQL utiliza DELIMITER para definir el inicio y el final del procedimiento.

Ejemplo en PostgreSQL

En PostgreSQL, se utiliza la palabra clave LANGUAGE:

CREATE PROCEDURE SelectAllCustomers()
LANGUAGE SQL
AS $$
SELECT * FROM Customers;
$$;

Este comando crea un procedimiento para seleccionar todos los clientes.

Ejemplo en Oracle

Crear un procedimiento en Oracle implica un poco más de sintaxis:

CREATE PROCEDURE SelectAllCustomers
AS
BEGIN
OPEN res FOR SELECT * FROM Customers;
DBMS_SQL.RETURN_RESULT(res);
END;

Oracle utiliza cursores para manejar el conjunto de resultados.

Ejecución de procedimientos almacenados

La ejecución de un procedimiento EXEC almacenado es sencilla.

En SQL Server y Oracle, se utiliza el comando seguido del nombre del procedimiento. En MySQL y PostgreSQL, se utiliza el comando CALL.

Ejemplo en SQL Server

En SQL Server, ejecuta un procedimiento almacenado como este:

EXEC SelectAllCustomers;

Este comando ejecuta el procedimiento y recupera todos los clientes.

Ejemplo en MySQL

En MySQL, se utiliza el comando CALL:

CALL SelectAllCustomers();

Este comando realiza la misma tarea que el ejemplo de SQL Server.

Ejemplo en PostgreSQL

En PostgreSQL, la ejecución es similar a MySQL:

CALL SelectAllCustomers();

Este comando llama al procedimiento para seleccionar todos los clientes.

Ejemplo en Oracle

En Oracle, la ejecución implica el comando EXEC:

EXEC SelectAllCustomers;

Este comando ejecuta el procedimiento y devuelve los datos del cliente.

Uso de parámetros en procedimientos almacenados

Puede pasar parámetros a los procedimientos almacenados para hacerlos más dinámicos. Por ejemplo, puede que desee filtrar los resultados en función de un valor específico. Esto se puede hacer mediante parámetros.

Parámetro único

A continuación se explica cómo crear un procedimiento almacenado con un solo parámetro.

CREATE PROCEDURE SelectCustomersByCity
@City NVARCHAR(30)
AS
SELECT * FROM Customers WHERE City = @City;
GO;

En este ejemplo, el procedimiento selecciona clientes de una ciudad específica.

Parámetros múltiples

Los procedimientos almacenados también pueden aceptar múltiples parámetros, lo que permite realizar consultas más complejas. Simplemente, se enumera cada parámetro, separado por comas.

CREATE PROCEDURE SelectCustomersByCityAndPostalCode
@City NVARCHAR(30), @PostalCode NVARCHAR(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode;
GO;

Este procedimiento filtra a los clientes según la ciudad y el código postal.

Ejemplo con parámetros en SQL Server

En SQL Server, puede ejecutar un procedimiento con parámetros como este:

EXEC SelectCustomersByCity @City = 'London';

O bien, con múltiples parámetros:

EXEC SelectCustomersByCityAndPostalCode @City = 'London', @PostalCode = 'WA1 1DP';

Ejemplo con parámetros en MySQL

En MySQL, se definen y llaman procedimientos con parámetros como estos:

DELIMITER //
CREATE PROCEDURE SelectCustomersByCity(IN City NVARCHAR(30))
BEGIN
SELECT * FROM Customers WHERE City = City;
END //
DELIMITER ;
CALL SelectCustomersByCity('London');
With multiple parameters:
DELIMITER //
CREATE PROCEDURE SelectCustomersByCityAndPostalCode(IN City NVARCHAR(30), IN PostalCode NVARCHAR(10))
BEGIN
SELECT * FROM Customers WHERE City = City AND PostalCode = PostalCode;
END //
DELIMITER ;
CALL SelectCustomersByCityAndPostalCode('London', 'WA1 1DP');

Ejemplo con parámetros en PostgreSQL

En PostgreSQL, se utiliza un enfoque similar:

CREATE PROCEDURE SelectCustomersByCity(IN City NVARCHAR(30))
LANGUAGE SQL
AS $$
SELECT * FROM Customers WHERE City = City;
$$;
CALL SelectCustomersByCity('London');
With multiple parameters:
CREATE PROCEDURE SelectCustomersByCityAndPostalCode(IN City NVARCHAR(30), IN PostalCode
NVARCHAR(10))
LANGUAGE SQL
AS $$
SELECT * FROM Customers WHERE City = City AND PostalCode = PostalCode;
$$;
CALL SelectCustomersByCityAndPostalCode('London', 'WA1 1DP');

Ejemplo con parámetros en Oracle

En Oracle, los parámetros individuales se manejan de la siguiente manera:

CREATE PROCEDURE SelectCustomersByCity(City IN NVARCHAR2)
AS
BEGIN
OPEN res FOR SELECT * FROM Customers WHERE City = City;
DBMS_SQL.RETURN_RESULT(res);
END;
EXEC SelectCustomersByCity('London');

Con múltiples parámetros:

CREATE PROCEDURE SelectCustomersByCityAndPostalCode(City IN NVARCHAR2, PostalCode IN NVARCHAR2)
AS
BEGIN
OPEN res FOR SELECT * FROM Customers WHERE City = City AND PostalCode = PostalCode;
DBMS_SQL.RETURN_RESULT(res);
END;
EXEC SelectCustomersByCityAndPostalCode('London', 'WA1 1DP');

Gestión de procedimientos almacenados

Ahora aprendamos cómo modificar y administrar procedimientos almacenados en SQL.

Modificación de procedimientos almacenados

Puede modificar un procedimiento almacenado mediante el comando ALTER PROCEDURE.

Esto le permitirá cambiar el procedimiento sin tener que eliminarlo y volver a crearlo.

ALTER PROCEDURE SelectAllCustomers
AS
SELECT CustomerName, ContactName FROM Customers;
GO;

Este comando actualiza el procedimiento para seleccionar solo las columnas CustomerName y ContactName.

Eliminación de procedimientos almacenados

Si ya no necesita un procedimiento almacenado, puede eliminarlo mediante el comando DROP PROCEDURE.

DROP PROCEDURE SelectAllCustomers;

Este comando elimina el procedimiento de la base de datos.

Ventajas de los procedimientos almacenados

Existen muchas ventajas de utilizar procedimientos almacenados en SQL, algunas de las cuales son:

Reutilización: los procedimientos almacenados promueven la reutilización. Puede escribir el código SQL una vez y usarlo varias veces. Esto ahorra tiempo y reduce los errores.
Mejora del rendimiento: los procedimientos almacenados mejoran el rendimiento. Están precompilados, por lo que se ejecutan más rápido que las instrucciones SQL individuales.
Beneficios de seguridad: Los procedimientos almacenados mejoran la seguridad. Puede otorgar a los usuarios permiso para ejecutar procedimientos sin darles acceso directo a los datos.
Facilidad de mantenimiento y depuración: los procedimientos almacenados simplifican el mantenimiento y la depuración. Al centralizar el código SQL, se facilita la actualización y la depuración.

Mejores prácticas para la gestión de procedimientos almacenados

Al administrar procedimientos almacenados, siga estas prácticas recomendadas:

Utilice convenciones de nomenclatura: las convenciones de nomenclatura consistentes facilitan la gestión de procedimientos.
Documente sus procedimientos: incluya comentarios en su código para explicar qué hace cada procedimiento.
Optimice el rendimiento: revise y optimice periódicamente sus procedimientos para mejorar el rendimiento.
Control de versiones: utilice sistemas de control de versiones para realizar un seguimiento de los cambios en sus procedimientos.

Casos de uso común

A continuación se muestran algunos de los casos de uso más comunes de procedimientos almacenados en SQL.

Recuperación de datos

Los procedimientos almacenados son excelentes para obtener datos. Ejecutan consultas complejas para obtener resultados específicos.

Esto resulta útil para informes y análisis. Por ejemplo, puede obtener una lista de todos los clientes que compraron algo el mes pasado.

Modificación de datos

Puede utilizar procedimientos almacenados para cambiar datos en bases de datos SQL. Estos procedimientos ayudan a agregar, actualizar o eliminar registros.

Esto garantiza que los cambios sigan las reglas y mantengan los datos correctos. Por ejemplo, puede actualizar la dirección de un cliente mediante un procedimiento almacenado.

Implementación de lógica empresarial compleja

Los procedimientos almacenados manejan bien reglas empresariales complejas. Pueden realizar cálculos, verificar datos y aplicar reglas.

Esto mantiene todo organizado y fácil de administrar. Por ejemplo, un procedimiento almacenado puede calcular las ventas totales y aplicar descuentos en función de la cantidad vendida.

Conclusión

Los procedimientos almacenados son herramientas potentes en las bases de datos SQL. Ayudan a reutilizar el código, mejoran el rendimiento, mejoran la seguridad y facilitan el mantenimiento.

Utilice procedimientos almacenados para obtener, modificar y gestionar datos. Siga las prácticas recomendadas para mantenerlos eficientes y fáciles de gestionar.

Utilice procedimientos almacenados para simplificar el trabajo de su base de datos y hacer que su código SQL sea mejor y más fácil de mantener.