Funciones y Procedimientos

Clase 02

1 Material Clase

  • Github TI3V22: LINK

1.1 Palabras Claves

  • Funciones:
    • funciones escalares
    • funciones con valores de tabla
      • en línea
      • de tipo de tabla
  • Procedimientos
  • SQL Server:
    • FUNCTION: declara funciones.
    • PROCEDURE: declara procedimientos.
    • DECLARE: declara variables dentro de funciones.
    • SCOPE_IDENTITY(): devuelve el ultimo valor generado por IDENTITY.
    • EXEC: ejecuta el procedimiento almacenado.

2 Funciones

  • Se utiliza para crear una función, definida por el usuario.
  • Existe 2 tipos de funciones: Escalares y con valores de tabla.
  • En SQL Server se utiliza el comando FUNCTION.

2.1 Funciones escalares

  • Devuelven un valor único (número, texto, fecha).
  • Requieren el uso de de BEGIN...END para la lógica de la función.

Ejemplo: Crear una función que devuelva el cuadrado de un número

CREATE FUNCTION Cuadrado(@Numero INT)
RETURNS INT
AS
BEGIN
    RETURN @Numero * @Numero;
END;
  1. Se crea una función llamada Cuadrado.
  2. La función acepta un parámetro de nombre @Numero de tipo INT.
  3. La función devuelve el tipo de dato INT. Para esto se utiliza la instrucción RETURNS.
  4. Se utiliza un alias AS para definir la lógica de la función Cuadrado (qué hace).
  • RETURNS especifíca el tipo de dato que la función devolverá.
  • RETURN especifica el valor que la función devolverá.

2.2 Funciones con valores de tabla

  • Devuelven un conjunto de datos (una tabla).
  • Se pueden consultar como si fueran tablas normales.
  • Existen 2 categorías.

2.3 Funciones con Valor de Tabla en Línea

  • Inline Table-Valued Functions.
  • Son muy eficientes porque son casi como una consulta directa.

Características

  • Simplicidad: El cuerpo de la función contiene una sola expresión SELECT.
  • No requiere BEGIN...END: Como solo hay una expresión SELECT, no se necesita el bloque BEGIN...SELECT.

Ejemplo: Crear una función que devuelva una tabla con todos los empleados que pertenecen a un departemento específico.

CREATE FUNCTION ObtenerEmpleadosPorDepartamento(@DepartamentoID INT)
RETURNS TABLE
AS
RETURN 
(
    SELECT Nombre, Cargo
    FROM Empleados
    WHERE Departamento_ID = @DepartamentoID
);

2.4 Funciones con Valor de Tabla de Tipo de Tabla

  • Multi-Statement Table-Valued Functions.
  • Son más complejas y flexibles que las funciones en línea.
  • Permiten múltiples operaciones antesd de devolver el conjunto de resultados.
  • Se utilizan cuando es necesario realizar cálculos complejos, insertar una tabla temporal, o cualquier otro tipo de procesamiento de datos.

Características

  • Flexibilidad: Permiten múltiples sentencias dentro de la función.
  • Uso de Variables de Tabla: Se utiliza una tabla como variable temporal, para almacenar y manipular datos antes de devolverlos.
  • Requiere BEGIN...END: Al tener múltiples sentencias SQL, el cuerpo de la función debe estar dentro de un bloque BEGIN...END.
  • Menor eficiencia: Pueden ser menos eficientes en términos de rendimiento.

Ejemplo: Crear una función que devuelva una tabla con todos los empleados que pertenecen a un departemento específico.

CREATE FUNCTION ObtenerEmpleadosPorDepartamento(@DepartamentoID INT)
RETURNS @Resultado TABLE
(
    Nombre NVARCHAR(100),
    Cargo NVARCHAR(100)
)
AS
BEGIN
    INSERT INTO @Resultado
    SELECT Nombre, Cargo
    FROM Empleados
    WHERE DepartamentoID = @DepartamentoID;
    
    RETURN;
END;
  • Es el mismo ejemplo, pero ahora implementada en una ‘funcion de tipo de tabla’.

3 Procedimientos

  • Son un conjuntos de instrucciones que se pueden almacenar para ejecutar reiteradas veces posteriores.
  • Esto permite reutilizar código, mejorar rendimiento y la seguridad en la base de datos.
  • En SQL Server se utiliza el comando PROCEDURE.

Ejemplo1:

  • Sea la tabla Empleado con los atributos Nombre, Cargo, Departamento_ID, FechaContratacion.
  • Crear un procedimiento que permita ingresar un nuevo registro y devuelva el ID del empreado recién registrado.
CREATE PROCEDURE InsertarEmpleado
    @Nombre NVARCHAR(100),
    @Cargo NVARCHAR(50),
    @DepartamentoID INT,
    @FechaContratacion DATE
AS
BEGIN
    -- Insertar un nuevo registro
    INSERT INTO Empleados (Nombre, Cargo, DepartamentoID, FechaContratacion)
    VALUES (@Nombre, @Cargo, @DepartamentoID, @FechaContratacion);

    -- Devolver el ID del nuevo empleado
    DECLARE @NuevoEmpleadoID INT;
    SET @NuevoEmpleadoID = SCOPE_IDENTITY();

    SELECT @NuevoEmpleadoID AS EmpleadoID;
END;

Ejemplo2: Ejecutar el procedimiento creado para ingresar al nuevo empleado: - Juan Perez, Desarrollador, Departamento ID 3, 2024-08-16.

DECLARE @NuevoID INT;

EXEC dbo.InsertarEmpleado 
    @Nombre = 'Juan Pérez', 
    @Cargo = 'Desarrollador',
    @DepartamentoID = 3, 
    @FechaContratacion = '2024-08-16';

SELECT @NuevoID;

3.1 Características

  • Reutilización de código:
  • Uso de parámetros: Similar a las funciones, aceptan parámetros de entrada y salida. Esto permite recibir valores al ejecutarse y devolver resultados.
  • Seguridad: Es posible controlar los permisos para ejecutar un procedimiento, limitando el acceso a las tablas. Solo el código pre-aprobado será ejecutado.
  • Mejora del rendimiento:

4 Ejercicios

Se Crea la base de datos Clase02_Ejercicios utilizando el siguiente script:

script02.sql
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Clase02_Ejercicios')
BEGIN
    CREATE DATABASE Clase02_Ejercicios;
END;

USE Clase02_Ejercicios;

CREATE TABLE Estudiantes (
    ID INT IDENTITY PRIMARY KEY,
    Nombre VARCHAR(100) NOT NULL,
    Edad INT,
    FechaIngreso DATE
);

INSERT INTO Estudiantes (Nombre, Edad, FechaIngreso) VALUES
    ('Juan Pérez', 20, '2022-03-15'),
    ('Ana López', 22, '2022-05-21'),
    ('Luis Martínez', 17, '2022-01-11'),
    ('Carmen Ríos', 19, '2022-02-07'),
    ('Carlos Sánchez', 23, '2022-08-25'),
    ('Lucía Fernández', 21, '2022-09-30'),
    ('Diego Alonso', 18, '2022-04-14'),
    ('Isabel Mora', 25, '2022-07-19');
python code 
from sqlalchemy import create_engine, text
import pandas as pd

# connection string
server = r'.\SQLEXPRESS'
username = r'sauk'
password = r'123'
database = r'master' 
driver = r'SQL+Server'
connection_string = f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver}'

# SQLAlchemy engine
engine = create_engine(connection_string)

# queries
queries = []

queries.append(text('''
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Clase02_Ejercicios')
BEGIN
    CREATE DATABASE Clase02_Ejercicios;
END;
'''))
queries.append(text('''
USE Clase02_Ejercicios;
'''))
queries.append(text('''
CREATE TABLE Estudiantes (
    ID INT IDENTITY PRIMARY KEY,
    Nombre VARCHAR(100) NOT NULL,
    Edad INT,
    FechaIngreso DATE
);
'''))
queries.append(text('''
INSERT INTO Estudiantes (Nombre, Edad, FechaIngreso) VALUES
    ('Juan Pérez', 20, '2022-03-15'),
    ('Ana López', 22, '2022-05-21'),
    ('Luis Martínez', 17, '2022-01-11'),
    ('Carmen Ríos', 19, '2022-02-07'),
    ('Carlos Sánchez', 23, '2022-08-25'),
    ('Lucía Fernández', 21, '2022-09-30'),
    ('Diego Alonso', 18, '2022-04-14'),
    ('Isabel Mora', 25, '2022-07-19');
'''))

# execute queries
with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

4.1 Ejercicio 01

Creación de una función: Crea una función fn_CantidadEstudiantes que devuelva el número total de estudiantes.

Solución

CREATE FUNCTION fn_CantidadEstudiantes()
RETURNS INT
AS
BEGIN
    DECLARE @numEstudiantes INT;
    SELECT @numEstudiantes = COUNT(*) FROM Estudiantes;
    RETURN @numEstudiantes;
END;
python code 
queries.append(text('''
CREATE FUNCTION fn_CantidadEstudiantes()
RETURNS INT
AS
BEGIN
    DECLARE @numEstudiantes INT;
    SELECT @numEstudiantes = COUNT(*) FROM Estudiantes;
    RETURN @numEstudiantes;
END;
'''))

4.2 Ejercicio 02

Uso de la función: Utiliza la función creada para mostrar el total de estudiantes.

Solución

SELECT dbo.fn_CantidadEstudiantes() AS TotalEstudiantes;
python code 
query_select = text('''
SELECT dbo.fn_CantidadEstudiantes() AS TotalEstudiantes;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
TotalEstudiantes
8

4.3 Ejercicio 03

Creación de un procedimiento almacenado para actualizar edad: Crea un procedimiento almacenado sp_ActualizarEdad que permita actualizar la edad de un estudiante dado su ID.

Solución

CREATE PROCEDURE sp_ActualizarEdad
    @EstudianteID INT, 
    @NuevaEdad INT
AS
BEGIN
    UPDATE Estudiantes 
    SET Edad = @NuevaEdad 
    WHERE ID = @EstudianteID;
END;
python code 
queries.append(text('''
CREATE PROCEDURE sp_ActualizarEdad
    @EstudianteID INT, 
    @NuevaEdad INT
AS
BEGIN
    UPDATE Estudiantes 
    SET Edad = @NuevaEdad 
    WHERE ID = @EstudianteID;
END;
'''))

4.4 Ejercicio 04

Uso del procedicimiento almacenado para actualizar edad: Actualiza la edad del estudiante con ID 1, a 22 años usando el procedimiento sp_ActualizarEdad.

EXEC sp_ActualizarEdad 
    @EstudianteID = 1,
    @NuevaEdad = 22;

SELECT *
FROM Estudiantes
WHERE ID = 1;
python code 
queries.append(text('''
EXEC sp_ActualizarEdad 
    @EstudianteID = 1,
    @NuevaEdad = 22;
'''))
query_select = text('''
SELECT *
FROM Estudiantes
WHERE ID = 1;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Edad FechaIngreso
1 Juan Pérez 22 2022-03-15

4.5 Ejercicio 05

Creación de índices sobre múltiples columnas: Crea un índice compuesto en las columnas Nombre y FechaIngreso para optimizar las consultas que usan estos campos.

Solución

CREATE NONCLUSTERED INDEX ix_NombreFechaIngreso
ON Estudiantes(Nombre, FechaIngreso);

-- consulta opcional: para verificar la creación de índice
SELECT ind.name AS IndexName, ind.type_desc AS IndexType, col.name AS ColumnName
FROM sys.indexes ind
JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE ind.object_id = OBJECT_ID('Estudiantes')
ORDER BY ind.name;
python code 
queries.append(text('''
CREATE NONCLUSTERED INDEX ix_NombreFechaIngreso
ON Estudiantes(Nombre, FechaIngreso);
'''))
query_select = text('''
SELECT ind.name AS IndexName, ind.type_desc AS IndexType, col.name AS ColumnName
FROM sys.indexes ind
JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE ind.object_id = OBJECT_ID('Estudiantes')
ORDER BY ind.name;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
IndexName IndexType ColumnName
ix_NombreFechaIngreso NONCLUSTERED Nombre
ix_NombreFechaIngreso NONCLUSTERED FechaIngreso
PK__Estudian__3214EC2756C578C3 CLUSTERED ID

4.6 Ejercicio 06

Consulta avanzada de estudiantes: Escribe una consulta SQL que liste todos los estudiantes menores de 25 años, ordenados por fecha de ingreso de manera ascendente.

Solución

SELECT *
FROM Estudiantes
WHERE Edad < 25
ORDER BY FechaIngreso ASC;
python code 
query_select = text('''
SELECT *
FROM Estudiantes
WHERE Edad < 25
ORDER BY FechaIngreso ASC;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Edad FechaIngreso
3 Luis Martínez 17 2022-01-11
4 Carmen Ríos 19 2022-02-07
1 Juan Pérez 22 2022-03-15
7 Diego Alonso 18 2022-04-14
2 Ana López 22 2022-05-21
5 Carlos Sánchez 23 2022-08-25
6 Lucía Fernández 21 2022-09-30

4.7 Ejercicio 07

Actualización de información: Actualiza el registro de los estudiantes para incrementar su edad en un año si han ingresado antes del año 2023.

Solución

UPDATE Estudiantes
SET Edad = Edad + 1
WHERE YEAR(FechaIngreso) < 2023;

SELECT *
FROM Estudiantes
WHERE YEAR(FechaIngreso) < 2023;
python code 
queries.append(text('''
UPDATE Estudiantes
SET Edad = Edad + 1
WHERE YEAR(FechaIngreso) < 2023;
'''))
query_select = text('''
SELECT *
FROM Estudiantes
WHERE YEAR(FechaIngreso) < 2023;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Edad FechaIngreso
1 Juan Pérez 23 2022-03-15
2 Ana López 23 2022-05-21
3 Luis Martínez 18 2022-01-11
4 Carmen Ríos 20 2022-02-07
5 Carlos Sánchez 24 2022-08-25
6 Lucía Fernández 22 2022-09-30
7 Diego Alonso 19 2022-04-14
8 Isabel Mora 26 2022-07-19

4.8 Ejercicio 08

Eliminar estudiantes: Elimina de la tabla Estudiantes aquellos que tienen más de 30 años.

Solución

DELETE FROM Estudiantes
WHERE Edad > 30;

SELECT *
FROM Estudiantes;
python code 
queries.append(text('''
DELETE FROM Estudiantes
WHERE Edad > 30;
'''))
query_select = text('''
SELECT *
FROM Estudiantes;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Edad FechaIngreso
1 Juan Pérez 23 2022-03-15
2 Ana López 23 2022-05-21
3 Luis Martínez 18 2022-01-11
4 Carmen Ríos 20 2022-02-07
5 Carlos Sánchez 24 2022-08-25
6 Lucía Fernández 22 2022-09-30
7 Diego Alonso 19 2022-04-14
8 Isabel Mora 26 2022-07-19

4.9 Ejercicio 09

Creación de vistas complejas: Crea una vista llamada VistaEstudiantesActivos que muestre solamente los estudiantes que tienen menos de 30 años y ordena los resultados por edad de manera descendente.

Solución

CREATE VIEW VistaEstudiantesActivos AS
    SELECT *
    FROM Estudiantes
    WHERE Edad < 30;

SELECT *
FROM VistaEstudiantesActivos
ORDER BY Edad DESC;
python code 
queries.append(text('''
CREATE VIEW VistaEstudiantesActivos AS
    SELECT *
    FROM Estudiantes
    WHERE Edad < 30;
'''))
query_select = text('''
SELECT *
FROM VistaEstudiantesActivos
ORDER BY Edad DESC;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
ID Nombre Edad FechaIngreso
8 Isabel Mora 26 2022-07-19
5 Carlos Sánchez 24 2022-08-25
1 Juan Pérez 23 2022-03-15
2 Ana López 23 2022-05-21
6 Lucía Fernández 22 2022-09-30
4 Carmen Ríos 20 2022-02-07
7 Diego Alonso 19 2022-04-14
3 Luis Martínez 18 2022-01-11

4.10 Ejercicio 10

Función para calcular la edad mínima: Desarrolla una función llamada fn_EdadMinimaEstudiantes que devuelva la edad mínima de los estudiantes.

Solución

CREATE FUNCTION fn_EdadMinimaEstudiantes()
RETURNS INT
AS
BEGIN
    DECLARE @minEdad
    SELECT @minEdad = MIN(Edad) FROM Estudiantes
    RETURN @minEdad;
END;

SELECT dbo.fn_EdadMinimaEstudiantes() AS EdadMinima;
python code 
queries.append(text('''
CREATE FUNCTION fn_EdadMinimaEstudiantes()
RETURNS INT
AS
BEGIN
    DECLARE @minEdad INT
    SELECT @minEdad = MIN(Edad) FROM Estudiantes
    RETURN @minEdad;
END;
'''))
query_select = text('''
SELECT dbo.fn_EdadMinimaEstudiantes() AS EdadMinima;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    columns = result.keys()
    data = result.fetchall()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output
EdadMinima
18