Procedimiento Almacenado SQL: Automatizar y Optimizar Consultas

Tutoriales
SQL

Los procedimientos almacenados de SQL son conjuntos de declaraciones SQL guardadas y almacenadas en una base de datos. Se pueden ejecutar bajo demanda para realizar tareas de manipulación y validación de datos, reduciendo la necesidad de escribir código SQL repetitivo para operaciones comunes. Los procedimientos almacenados son útiles en la gestión de bases de datos al promover la eficiencia y la reutilización. Además, admiten una seguridad y mantenibilidad mejoradas de la base de datos. En este artículo, discutiremos cómo crear y ejecutar procedimientos almacenados de SQL, casos de uso comunes y mejores prácticas.

Al comenzar, te recomiendo encarecidamente tomar los cursos de Introducción a SQL y Aprender SQL de DataCamp para aprender los conocimientos fundamentales de extracción y análisis de datos usando SQL. Además, la Hoja de referencia de conceptos básicos de SQL, que puedes descargar, es una referencia útil porque tiene todas las funciones SQL más comunes.

¿Qué es un Procedimiento Almacenado en SQL?

Un procedimiento almacenado en SQL es una colección de declaraciones SQL guardadas y almacenadas dentro de la base de datos. El propósito del procedimiento almacenado en SQL es realizar una secuencia de operaciones en una base de datos, como consultar, insertar, actualizar o eliminar datos.

A diferencia de las consultas SQL regulares, que se ejecutan como comandos separados, los procedimientos almacenados encapsulan un conjunto de declaraciones SQL, lo que facilita la reutilización del código sin tener que escribir comandos SQL repetidamente.

Los beneficios de los procedimientos almacenados de SQL incluyen lo siguiente:

  • Reutilización de código: Una vez que se crea un procedimiento almacenado, se puede llamar tantas veces como sea necesario, eliminando la redundancia en el código SQL.
  • Rendimiento mejorado: Los procedimientos almacenados a menudo se ejecutan más rápido porque están precompilados y almacenados en el servidor de la base de datos, lo que reduce la latencia de red y el tiempo de compilación.
  • Seguridad: Los procedimientos almacenados pueden mejorar la seguridad de los datos y el control sobre el acceso a datos sensibles al otorgar a los usuarios permiso para ejecutar un procedimiento almacenado sin acceso directo a las tablas.

Sintaxis y estructura básica

La sintaxis para crear un procedimiento almacenado puede variar ligeramente dependiendo del sistema de base de datos (por ejemplo, MySQL, SQL Server, Oracle). A continuación se muestra un ejemplo general utilizando la sintaxis de SQL Server:

-- Crear un procedimiento almacenado llamado NombreProcedimiento
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- Aquí van las sentencias SQL
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

En la sintaxis anterior;

  • CREAR PROCEDIMIENTO: Este comando se utiliza para definir un nuevo procedimiento almacenado.

  • NombreDelProcedimiento: El nombre dado al procedimiento almacenado. Debe ser único dentro de la base de datos.

  • @Parámetro1, @Parámetro2: Los parámetros son opcionales; permiten que el procedimiento reciba datos de entrada. Cada parámetro se define con un símbolo @ y un tipo de dato (por ejemplo, INT, VARCHAR(50)).

  • COMO BEGIN…END: Las declaraciones SQL dentro de COMENZAR y FIN forman el cuerpo del procedimiento, donde se ejecuta la lógica principal. El procedimiento recupera registros de una tabla basados en condiciones específicas en este ejemplo.

Parámetros de entrada y salida

Los parámetros de entrada y salida te permiten pasar valores hacia y desde un procedimiento almacenado.

Por ejemplo, si @Parameter1 se define como un parámetro de entrada, se le puede asignar cualquier valor cuando se llama al procedimiento, afectando la lógica SQL o la salida. En el ejemplo a continuación, el parámetro @UserID recupera datos específicos del UserID proporcionado.

-- Crear un procedimiento para recuperar datos de un usuario específico por UserID
CREATE PROCEDURE GetUserData 
 -- Parámetro de entrada: ID del usuario a recuperar
    @UserID INT 
AS
BEGIN
    -- Seleccionar todas las columnas de Usuarios donde UserID coincide con el parámetro de entrada
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Los parámetros de salida, definidos por la palabra clave OUTPUT, permiten que un procedimiento almacenado envíe un valor de regreso al entorno que lo llamó. Por ejemplo, si un procedimiento calcula un descuento, podría usar un parámetro de salida para pasarlo de regreso al programa que lo llama.

-- Crear un procedimiento para calcular el precio con descuento
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Salida: precio final después del descuento
AS
BEGIN
    -- Calcular el precio final aplicando la tasa de descuento al precio original
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Para llamar a este procedimiento, usarías:

-- Declarar una variable para almacenar el precio final después del descuento
DECLARE @FinalPrice DECIMAL(10, 2);
-- Ejecutar el procedimiento CalculateDiscount con un precio de 100 y un descuento del 10%
-- Almacenar la salida en la variable @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Seleccionar y mostrar el precio final con descuento
SELECT @FinalPrice AS FinalPrice;

Procedimientos almacenados en MySQL

Como mencioné, ejecutar un procedimiento almacenado en SQL puede hacerse de diferentes maneras dependiendo del sistema de base de datos y las herramientas utilizadas.

Creación de procedimientos almacenados en MySQL

Crear un procedimiento almacenado en MySQL implica definir el nombre del procedimiento, los parámetros y las instrucciones SQL que componen su cuerpo. El siguiente ejemplo crea un procedimiento llamado GetEmployeeDetails que toma EmployeeID como parámetro de entrada y recupera los detalles de ese empleado específico.

DELIMITER $
-- Crea un procedimiento para recuperar detalles de un empleado específico por EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Selecciona todas las columnas de Empleados donde EmployeeID coincida con el parámetro de entrada
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Ejecutando procedimientos almacenados en MySQL

La forma más común de ejecutar un procedimiento almacenado es utilizando comandos SQL. En MySQL, usamos el comando CALL para ejecutar el procedimiento almacenado.

CALL ProcedureName();

Usando el procedimiento definido GetEmployeeDetails, la consulta de ejecución se vería así:

-- Ejecuta el procedimiento almacenado para recuperar detalles del EmployeeID 101
CALL GetEmployeeDetails(101);

Procedimientos Almacenados en SQL Server

SQL Server proporciona una sintaxis y comandos específicos para crear, ejecutar y gestionar procedimientos almacenados. Este enfoque facilita la construcción de rutinas SQL eficientes y reutilizables que pueden manejar tareas complejas con una mínima repetición.

Creando procedimientos almacenados en SQL Server

Crear un procedimiento almacenado en SQL Server implica definir el nombre del procedimiento, los parámetros y las declaraciones SQL que conforman su cuerpo. El siguiente ejemplo crea un procedimiento llamado GetEmployeeDetails que toma @EmployeeID como parámetro de entrada y recupera detalles para ese empleado específico.

-- Crear un procedimiento para recuperar detalles de un empleado específico por EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Parámetro de entrada: ID del empleado a recuperar
AS
BEGIN
    -- Seleccionar todas las columnas de Employees donde EmployeeID coincida con el parámetro de entrada
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Ejecutar procedimientos almacenados en SQL Server

En SQL Server, el comando EXEC o EXECUTE llama a un procedimiento almacenado. El siguiente ejemplo muestra cómo ejecutar el procedimiento almacenado GetEmployeeDetails usando parámetros de entrada específicos.

-- Ejecutar el procedimiento GetEmployeeDetails con el EmployeeID establecido en 102
EXEC GetEmployeeDetails @EmployeeID = 102;

También puedes ejecutar los parámetros de salida declarando la variable en el comando. En el siguiente ejemplo, @TotalSales se declara como una variable para recibir la salida de CalculateTotalSales.

-- Declarar una variable para almacenar el monto total de ventas
DECLARE @TotalSales DECIMAL(10, 2);
-- Ejecutar CalculateTotalSales para SalespersonID 5, almacenar el resultado en @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Mostrar el monto total de ventas
SELECT @TotalSales AS TotalSales;

Te recomiendo realizar nuestro curso Introducción a SQL Server para comprender las diferentes funcionalidades de SQL Server para consultar datos. Además, considera nuestra trayectoria profesional completa de Desarrollador SQL Server, que no solo te equipará con las habilidades para crear, actualizar y ejecutar procedimientos almacenados, sino que también te ayudará con funciones de agregado, unión, inserción y eliminación de tablas, y mucho más.

Usos comunes de procedimientos almacenados

Los procedimientos almacenados en SQL son útiles en escenarios donde se requieren tareas complejas repetitivas. A continuación, se presentan aplicaciones del mundo real de los procedimientos almacenados en la gestión de datos y operaciones comerciales.

Validación de datos y aplicación de integridad

Los procedimientos almacenados se pueden utilizar para validar datos antes de una actualización o inserción. En el ejemplo a continuación, un procedimiento almacenado verifica que el correo electrónico de un cliente sea único antes de insertar un nuevo registro en la tabla Customers, asegurando la consistencia de los datos. Esto centraliza la lógica de validación dentro de la base de datos, reduciendo la redundancia y asegurando una aplicación uniforme en diferentes aplicaciones.

-- Crear un procedimiento para agregar un nuevo cliente, verificando si el correo electrónico ya existe
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Comprobar si el correo electrónico ya existe en la tabla Customers
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Lanzar un error si el correo electrónico ya está en uso
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insertar nuevos detalles del cliente si el correo electrónico es único
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Procesamiento y generación de informes de datos automatizados

También puedes utilizar procedimientos almacenados para generar informes periódicos o procesar grandes conjuntos de datos. Por ejemplo, un procedimiento almacenado podría agregar datos de ventas diarias de una plataforma de comercio electrónico y almacenarlos en una tabla de informes, facilitando así el acceso a información sobre ventas para los equipos sin necesidad de ejecutar consultas complejas.

-- Crear un procedimiento para generar un informe de ventas diario
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insertar la fecha de hoy y las ventas totales en la tabla SalesReport
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Seleccionar la fecha actual y la suma de las ventas de hoy de la tabla Sales
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Gestión de transacciones

Al usar procedimientos almacenados, puedes garantizar que múltiples operaciones se ejecuten como una sola transacción. Por ejemplo, en un sistema bancario, un procedimiento almacenado puede manejar tanto las acciones de débito como de crédito en una transferencia de fondos, asegurando que ambas acciones tengan éxito o fallo juntas.

-- Crea un procedimiento para transferir fondos entre cuentas
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Inicia una transacción para garantizar la atomicidad
    -- Deduce la cantidad especificada del saldo de la cuenta del remitente
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Agrega la cantidad especificada al saldo de la cuenta del destinatario
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Verifica errores y realiza un rollback si ocurrió alguno; de lo contrario, confirma la transacción
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Deshace todos los cambios si ocurrió un error
    ELSE
        COMMIT TRANSACTION;    -- Confirma los cambios si no hay errores
END;

Control de acceso y seguridad de datos

También puedes utilizar procedimientos almacenados de SQL para controlar el acceso a datos sensibles. Por ejemplo, un procedimiento almacenado puede limitar el acceso directo a tablas al permitir a los usuarios llamar a un procedimiento que solo recupera campos relevantes, como saldos de cuentas, sin detalles de transacciones.

-- Crea un procedimiento para recuperar el saldo de la cuenta, con verificación de autorización
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Verifica si la cuenta existe y pertenece al usuario especificado
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Si está autorizado, selecciona y devuelve el saldo de la cuenta
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Si no está autorizado, lanza un error
        THROW 50000, 'Unauthorized access.', 1;
END;

Migración de datos y procesos ETL

Los procedimientos almacenados también se utilizan para cargar, transformar y migrar datos entre sistemas. Un procedimiento almacenado puede automatizar la extracción de datos de una base de datos de origen, transformarlos según sea necesario e insertarlos en una tabla de destino, simplificando la integración de datos para informes o análisis.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extraer
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transformar
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Cargar
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Mejores Prácticas para Procedimientos Almacenados

La redacción de procedimientos almacenados eficientes y mantenibles garantiza el rendimiento óptimo de su base de datos. A continuación se presentan consejos para redactar procedimientos almacenados para sus bases de datos SQL.

  • Utiliza Convenciones de Nombres Consistentes: Para que los procedimientos almacenados sean fáciles de identificar y entender, utiliza un formato de nomenclatura consistente y descriptivo. Además, evita el prefijo sp_ en SQL Server reservado para procedimientos del sistema para prevenir posibles conflictos y problemas de rendimiento.

  • Implementación de control de errores: Envuelva las declaraciones SQL en bloques TRY...CATCH para capturar y manejar errores, y mantener la integridad de los datos.

  • Optimización para el rendimiento: Minimice el uso de cursores ya que pueden ser lentos y consumir muchos recursos. En su lugar, intente usar operaciones basadas en conjuntos, que generalmente son más eficientes. Además, indexe las columnas de uso frecuente y evite uniones complejas en tablas grandes para reducir la sobrecarga de memoria y mejorar la eficiencia.

  • Parametriza los procedimientos almacenados: Utiliza parámetros en lugar de valores codificados para permitirte pasar valores dinámicos a tu procedimiento, haciéndolo más flexible y reutilizable.

Consulte nuestro curso de SQL Intermedio para aprender más sobre el uso de funciones de agregado y joins para filtrar datos. También, pruebe nuestras trayectorias de habilidades de SQL Server Fundamentals y Fundamentos de SQL para mejorar sus habilidades de unir tablas y análisis de datos.

Conclusión

Los procedimientos almacenados de SQL mejoran la reutilización del código y la optimización del rendimiento en la gestión de bases de datos. Los procedimientos almacenados también mejoran la seguridad de la base de datos mediante el acceso controlado y garantizando la integridad de los datos. Como profesional de datos, le animo a practicar la creación y ejecución de procedimientos almacenados para dominar las mejores prácticas de gestión de bases de datos.

Si estás interesado en convertirte en un analista de datos competente, consulta nuestra Trayectoria Profesional de Analista de Datos Asociado en SQL para aprender las habilidades necesarias. El curso Informes en SQL también es apropiado si deseas aprender a crear paneles profesionales utilizando SQL. Finalmente, te recomiendo obtener la Certificación de Asociado en SQL para demostrar tu dominio en el uso de SQL para el análisis de datos y destacar entre otros profesionales de datos.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure