Vistas, funciones y procedimientos almacenados

---

¿Para qué utilizar vistas, funciones y procedimientos almacenados?, ¿solamente para que se vea bonita la base de datos con muchos objetos de todos los tipos posibles?

Tenemos dos objetivos principales cuando ocupamos estos objetos en nuestra base de datos:

1. Encapsular la forma en que se obtienen los datos de la base de datos.
2. Implementar una capa de seguridad por encima del acceso a los datos.

Encapsulamiento

Para atacar este tema lo voy a dividir en dos partes, en "Vistas y funciones" y en "Procedimientos almacenados".

Funciones y vistas

No sé si te haya tocado trabajar con la base de datos de algún ERP o con la base de datos de un sistema a cuyo programador le resultó divertido ofuscar los nombres de los objetos.

La irremediable consecuencia de lo anterior es que la extracción de datos se vuelve un dolor de cabeza debido a que los nombres de los objetos no es descriptivo por sí mismo.

Supongamos que tenemos una tabla de este estilo:

CREATE TABLE XT56(
    A INT IDENTITY(1, 1),
    B VARCHAR(80) NOT NULL,
    C DECIMAL(8, 2) NOT NULL,
    D BIT NOT NULL DEFAULT 1,
    CONSTRAINT pkXT56 PRIMARY KEY (Id)
)

Resulta muy complejo establecer para qué es la tabla XT56 y el objetivo funcional de la información que está guardando.    Si después de un trabajo de revisión del sistema y de la base de datos determinamos que es la tabla de productos, bien podríamos hacer lo siguiente para clarificar la extracción de los productos:

CREATE VIEW vProductos
AS
    SELECT A AS Id, B AS Nombre, C AS Precio, D AS Activo
    FROM XT56

Al utilizar esta vista podremos obtener la información de una forma mucho más clara y entendible.

¿Qué pasaría si frecuentemente estamos obteniendo la lista de productos activos cuyo precio sea mayor o igual a un valor X?    Podemos encapsular el código para clarificar la forma en que devuelve la información y también para reutilizar un algoritmo que ya resolvió un problema en específico:

CREATE FUNCTION udfDameProductosPorPrecio(
    @Precio DECIMAL(8, 2)
)
RETURNS TABLE
AS
    RETURN
    SELECT A AS Id, B AS Nombre, C AS Precio
    FROM XT56
    WHERE C = @Precio
    AND D = 1


A través del encapsulamiento podemos proyectar la información de la forma en la que lo necesitamos y también crear objetos que nos permitan reutilizar algo que ya fue resuelto previamente.

Procedimientos almacenados

En un grupo de programación en T-SQL siempre habrá alguien que tenga una forma óptima de codificar y que resuelva los problemas utilizando la menor cantidad de recursos posibles.

Una excelente idea es que esta persona se encargue de resolver los problemas operativos (en la base datos) más complejos y encapsule el algoritmo dentro de un procedimiento almacenado, de manera tal que éste pueda ser utilizado por otros sin tener que estarse quebrando la cabeza en cómo resolver un problema.

Planes de ejecución

Otra gran ventaja de los objetos parametrizados (funciones y procedimientos almacenados) es que permiten que se almacene el plan de ejecución de éstos y así no tenga que recalcularse la siguiente ocasión que se requiera su utilización.

El caché de planes de ejecución es una cosa muy valiosa de SQL Server debido a que reduce el trabajo de uno de los procesos más caros, el cálculo del plan de ejecución.

Capa de seguridad

Habitualmente no es buena idea que se tenga acceso directo de lectura y/o escritura a las tablas de una base de datos.   En este momento se me ocurren por lo menos las siguientes X razones:

1. El usuario debe poder leer datos de una tabla, pero ella contiene información confidencial a la que no debería tener acceso el usuario.    Es decir, solamente necesita acceso a un subconjunto de columnas.
2. El usuario debe poder insertar datos pero necesitamos estar absolutamente seguros que la información que ingrese sea consistente con la especificación de requerimientos.
3. Si el usuario tiene permiso de escritura sobre la tabla, entonces podrá eliminar información y hacerlo de forma equivocada.

Seguramente habrá más de uno que inmediatamente pensó "pero si le puedo dar permisos muy granulares a un usuario de manera tal que solamente pueda leer o escribir ciertas columnas".    Coincido totalmente en que se podría, pero la administración de la seguridad se haría un dolor de cabeza y el dar esos permisos granulares no nos asegura que la información insertada sea adecuada.

Supongamos que tenemos una tabla como la siguiente:

CREATE TABLE Productos(
    Id INT IDENTITY(1, 1),
    Nombre VARCHAR(80) NOT NULL,
    Precio DECIMAL(8, 2) NOT NULL,
    Activo BIT NOT NULL DEFAULT 1,
    CONSTRAINT pkProductos PRIMARY KEY (Id)
)

Si un usuario únicamente tuviera permitido actualizar precios, podríamos crearle un procedimiento como el siguiente:

CREATE PROC pActualizaPrecioProducto(
    @Id INT,
    @Precio DECIMAL(8, 2)
)
AS
BEGIN
    UPDATE Productos
    SET Precio = @Precio
    WHERE Id = @Id
END

Vamos a mejorar este tema, vamos a crear una tabla de histórico de precios guardando auditoría de quién lo ejecutó:

CREATE TABLE ProductosHistoricoPrecio(
    Id INT IDENTITY(1, 1)
    Id_Producto INT NOT NULL,
    PrecioAnterior DECIMAL(8, 2),
    PrecioNuevo DECIMAL(8, 2),
    Usuario SYSNAME,
    Fecha DATETIME2(0) NOT NULL DEFAULT GETDATE(),
    CONSTRAINT pkProductosHistoricoPrecio PRIMARY KEY (Id),
    CONSTRAINT fkProductosHistoricoPrecio_Productos FOREIGN KEY (Id_Producto) REFERENCES Productos (Id)
)

Y nuestro procedimiento almacenado quedaría de la siguiente manera:

CREATE PROC pActualizaPrecioProducto(
    @Id INT,
    @Precio DECIMAL(8, 2)
)
AS
BEGIN
    BEGIN TRAN
    BEGIN TRY
        INSERT INTO HistoricoProductosPrecio (Id_Producto, PrecioAnterior, PrecioNuevo, Usuario)
        SELECT Id, Precio, @Precio, SUSER_NAME()
        FROM Productos
        WHERE Id = @Id

        UPDATE Productos
        SET Precio = @Precio
        WHERE Id = @Id

        COMMIT
    END TRY
    BEGIN CATCH
        ROLLBACK

        DECLARE @Error VARCHAR(4000) = ERROR_MESSAGE()
        THROW 50001, @Error, 1
    END CATCH
END

Si a nuestro usuario le damos permiso de ejecución sobre el procedimiento almacenado:

1. No será necesario que tenga permiso de escritura sobre HistoricoProductosPrecio ni sobre Productos.
2. No es necesario que sepa que se está guardando un histórico de precios, tarea que podría olvidar realizar si él actualizara directamente el precio del producto.

Espero te haya resultado de utilidad esta entrada, ¡saludos!

---