Procedimientos almacenados definidos por el usuario en SQL

Utilizarás Microsoft SQL Server como base de datos. El concepto que se trata en este tutorial es el mismo en la mayoría de los sistemas de gestión de bases de datos relacionales (RDBMS), aunque la sintaxis puede variar. Aun así, la idea que verás aquí te servirá para cualquier base de datos.

Un procedimiento almacenado es un bloque de sentencias SQL en un sistema RDBMS. Suele escribirlo una persona programadora, un administrador de bases de datos o un analista de datos, y se guarda para reutilizarlo en varios programas. Puede haber distintos tipos según el RDBMS. No obstante, los dos procedimientos almacenados más importantes en cualquier RDBMS son:

• Procedimiento almacenado definido por el usuario
• Procedimiento almacenado del sistema

Un procedimiento almacenado es código precompilado que se guarda o se mantiene en caché para su reutilización. Tener código en caché facilita mucho el mantenimiento, ya que no tienes que cambiarlo en varios sitios, y además ayuda a reforzar la seguridad.

Crear una tabla

Ahora vas a crear una tabla llamada table_Employees como sigue.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)
Con la tecnología de

La tabla contiene información sobre el personal de una empresa concreta y tiene las siguientes columnas:

1. EmployeeId: clave primaria única de tipo entero; su valor puede autoincrementarse.
2. EmployeeFirstName: nombre de la persona empleada, de tipo carácter y obligatorio.
3. EmployeeLast Name: apellido de la persona empleada, de tipo carácter y obligatorio.
4. EmployeeGender: género de la persona empleada, también de tipo carácter.
5. EmployeeDepartmentId: identificador del departamento de la persona empleada, de tipo entero.

Puedes escribir una consulta para insertar valores en la tabla. El patrón para añadir valores es:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)
Con la tecnología de

Después de insertar los valores en la tabla, ya tendrás creada table_Employees.

Crear un procedimiento almacenado definido por el usuario

Un procedimiento almacenado te permite escribir una consulta una vez, asignarle un nombre y guardarla para ejecutarla tantas veces como necesites. Al ejecutar las consultas, verás que se crea una carpeta en Programmability -> Stored Procedure, y dentro aparecerá el archivo dbo.uprocGetEmployees.

La sintaxis para crear un procedimiento almacenado es:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END
Con la tecnología de

Puedes crear un procedimiento almacenado usando una de estas instrucciones SQL:

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Además, conviene evitar la convención de nombres que empiezan por "sp_". En el ejemplo, el procedimiento almacenado se crea con el nombre: uprocGetEmployees.

Ejecutar el procedimiento almacenado

Para ejecutar un procedimiento almacenado, puedes usar cualquiera de estas tres formas y ejecutarlo así:

• EXEC <>
• EXECUTE <>
• <>

En el programa anterior, puedes usar el nombre del procedimiento uprocGetEmployees y seleccionarlo para ejecutar la consulta.

Procedimiento almacenado con parámetros

Un procedimiento almacenado puede aceptar uno o varios parámetros. Si entiendes cómo funcionan varios parámetros, te resultará sencillo entender el caso de un único parámetro.

La sintaxis para crear un procedimiento almacenado con múltiples parámetros es:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END
Con la tecnología de

En el ejemplo, se observa un cambio en la carpeta:
EMPLOYEE -> Programmability -> Stored Procedures -> dbo.uprocGetEmployessGenderAndDepartment

La idea es muy similar a la de una función en lenguajes de programación como Python o Java. El programa anterior define la función o procedimiento llamado "uprocGetEmployeesGenderAndDepartment", que recibe los parámetros @EmployeeGender y @EmployeeDepartmentId. Estos se "invocan" pasando los valores necesarios a nuestro procedimiento, lo cual puede hacerse de dos formas, como se explica a continuación.

Los parámetros en este programa son @EmployeeGender y @EmployeeDepartmentId, a los que se les pasa un valor al ejecutar el procedimiento.

El valor puede especificarse de dos maneras. Puedes usar cualquiera de ellas para ejecutar el procedimiento almacenado con múltiples parámetros.

• Especificando los nombres de los parámetros en la consulta y pasando los valores correspondientes:

• Por posición, haciendo coincidir el orden de los parámetros del procedimiento almacenado con tu consulta y pasando los valores necesarios:

Modificar el procedimiento almacenado

También puedes modificar un procedimiento almacenado usando el comando ALTER PROCEDURE.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END
Con la tecnología de

Eliminar un procedimiento almacenado

Los procedimientos almacenados se pueden eliminar rápidamente con los siguientes comandos:

DROP PROC <> DROP PROCEDURE <>

Añadir seguridad mediante cifrado

El icono de candado que ves a la izquierda en la imagen siguiente indica que el procedimiento almacenado está cifrado, por lo que solo las personas autorizadas pueden acceder a él.

Conclusión

Acabas de aprender los aspectos básicos de los procedimientos almacenados definidos por el usuario. Los conceptos tratados son aplicables a cualquier RDBMS. Puedes seguir aprendiendo en DataCamp con el curso Intermediate SQL Server. Además, este tutorial de Stored Procedures te será de gran ayuda.