SQL - de Cero a Profesional

Procedimientos Almacenados y Funciones

Código  de ejemplo

				
					-- Crear una base de datos y tablas para los ejemplos
CREATE DATABASE Tienda;

-- Tabla Clientes
CREATE TABLE Clientes (
    ClienteID INT PRIMARY KEY,
    Nombre VARCHAR(50),
    Email VARCHAR(100),
    Edad INT
);

-- Tabla Pedidos
CREATE TABLE Pedidos (
    PedidoID INT PRIMARY KEY,
    ClienteID INT,
    Fecha DATE,
    Monto DECIMAL(10, 2),
    FOREIGN KEY (ClienteID) REFERENCES Clientes(ClienteID)
);

-- Insertar datos en la tabla Clientes
INSERT INTO Clientes (ClienteID, Nombre, Email, Edad)
VALUES
(1, 'Juan', 'juan@example.com', 30),
(2, 'Ana', 'ana@example.com', 25),
(3, 'Pedro', 'pedro@example.com', 20);

-- Insertar datos en la tabla Pedidos
INSERT INTO Pedidos (PedidoID, ClienteID, Fecha, Monto)
VALUES
(1, 1, '2023-01-15', 100.00),
(2, 2, '2023-01-16', 150.00),
(3, 1, '2023-01-17', 200.00),
(4, 3, '2023-01-18', 250.00),
(5, 2, '2023-01-19', 300.00);

-- Creación de un procedimiento almacenado
DELIMITER //
CREATE PROCEDURE ObtenerPedidosPorCliente(IN cliente_id INT)
BEGIN
    SELECT * FROM Pedidos WHERE ClienteID = cliente_id;
END //
DELIMITER ;

-- Llamar al procedimiento almacenado
CALL ObtenerPedidosPorCliente(1);

-- Creación de una función definida por el usuario
DELIMITER //
CREATE FUNCTION CalcularDescuento(monto DECIMAL(10, 2)) RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE descuento DECIMAL(10, 2);
    SET descuento = monto * 0.1; -- 10% de descuento
    RETURN descuento;
END //
DELIMITER ;

-- Usar la función definida por el usuario
SELECT PedidoID, Monto, CalcularDescuento(Monto) AS Descuento FROM Pedidos;

-- Creación de un trigger
DELIMITER //
CREATE TRIGGER ActualizarEdadAntesDeInsertar
BEFORE INSERT ON Clientes
FOR EACH ROW
BEGIN
    IF NEW.Edad < 18 THEN
        SET NEW.Edad = 18; -- Establecer la edad mínima a 18
    END IF;
END //
DELIMITER ;

-- Probar el trigger insertando un nuevo cliente con edad menor a 18
INSERT INTO Clientes (ClienteID, Nombre, Email, Edad)
VALUES (4, 'Laura', 'laura@example.com', 16);

-- Verificar el resultado del trigger
SELECT * FROM Clientes;

Explicación

  1. Creación de Procedimientos Almacenados:

    • CREATE PROCEDURE ObtenerPedidosPorCliente(IN cliente_id INT) ...: Este comando crea un procedimiento almacenado llamado ObtenerPedidosPorCliente, que toma un parámetro de entrada cliente_id y selecciona todos los pedidos del cliente especificado.
    • CALL ObtenerPedidosPorCliente(1);: Este comando llama al procedimiento almacenado ObtenerPedidosPorCliente con el parámetro cliente_id igual a 1, obteniendo todos los pedidos realizados por el cliente con ID 1.

  2. Creación de Funciones Definidas por el Usuario:

    • CREATE FUNCTION CalcularDescuento(monto DECIMAL(10, 2)) RETURNS DECIMAL(10, 2) ...: Este comando crea una función definida por el usuario llamada CalcularDescuento, que toma un parámetro monto y devuelve el 10% de ese monto como descuento.
    • SELECT PedidoID, Monto, CalcularDescuento(Monto) AS Descuento FROM Pedidos;: Este comando usa la función CalcularDescuento para calcular el descuento de cada pedido y selecciona el ID del pedido, el monto original y el descuento calculado.

  3. Creación de Triggers:

    • CREATE TRIGGER ActualizarEdadAntesDeInsertar BEFORE INSERT ON Clientes ...: Este comando crea un trigger llamado ActualizarEdadAntesDeInsertar, que se ejecuta antes de insertar un registro en la tabla Clientes. Si la edad del nuevo cliente es menor de 18, el trigger establece la edad a 18.
    • INSERT INTO Clientes (ClienteID, Nombre, Email, Edad) VALUES (4, 'Laura', 'laura@example.com', 16);: Este comando inserta un nuevo cliente con edad 16. El trigger ajusta la edad a 18 antes de la inserción.
    • SELECT * FROM Clientes;: Este comando verifica que la edad del nuevo cliente se ha ajustado a 18.

Estos conceptos avanzados permiten automatizar tareas, realizar cálculos personalizados y garantizar la integridad de los datos a través de procedimientos almacenados, funciones y triggers.

Recursos