¿Qué es un procedimiento almacenado?
¿Qué es un "procedimiento almacenado" y cómo funciona?
¿Cuál es la composición de un procedimiento almacenado (cosas que cada uno debe tener para ser un procedimiento almacenado)?
Los procedimientos almacenados son un lote de declaraciones SQL que se pueden ejecutar de varias maneras. La mayoría de los DBM principales admiten procedimientos almacenados; sin embargo, no todos lo hacen. Deberá verificar con la documentación de ayuda de su DBMS particular para obtener detalles específicos. Como estoy más familiarizado con SQL Server, lo usaré como muestra.
Para crear un procedimiento almacenado la sintaxis es bastante simple:
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
Así por ejemplo:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
Un beneficio de los procedimientos almacenados es que puede centralizar la lógica de acceso a los datos en un solo lugar que luego es fácil de optimizar para los DBA. Los procedimientos almacenados también tienen un beneficio de seguridad ya que puede otorgar derechos de ejecución a un procedimiento almacenado pero el usuario no necesitará tener permisos de lectura/escritura en las tablas subyacentes. Este es un buen primer paso contra la inyección SQL.
Los procedimientos almacenados tienen desventajas, básicamente el mantenimiento asociado con la operación CRUD básica . Digamos que para cada tabla tiene Insertar, Actualizar, Eliminar y al menos una selección basada en la clave principal, eso significa que cada tabla tendrá 4 procedimientos. ¡Ahora tome una base de datos de tamaño decente de 400 tablas y tendrá 1600 procedimientos! Y eso suponiendo que no tenga duplicados, lo cual probablemente tendrá.
Aquí es donde usar un ORM o algún otro método para generar automáticamente sus operaciones CRUD básicas tiene mucho mérito.
Un procedimiento almacenado es un conjunto de sentencias SQL precompiladas que se utilizan para realizar una tarea especial.
Ejemplo: si tengo una Employeemesa
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Primero estoy recuperando la Employeetabla:
Create Procedure Employee details
As
Begin
Select * from Employee
End
Para ejecutar el procedimiento en SQL Server:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Luego, en segundo lugar, estoy insertando el valor en la tabla de empleados.
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Para ejecutar el procedimiento parametrizado en SQL Server:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Ejemplo:@Name Varchar(30)
En la Employeetabla el Nametamaño de la columna debe ser varchar(30).
Un procedimiento almacenado es un grupo de declaraciones SQL que se ha creado y almacenado en la base de datos. Un procedimiento almacenado aceptará parámetros de entrada para que varios clientes puedan utilizar un único procedimiento a través de la red utilizando diferentes datos de entrada. Los procedimientos almacenados reducirán el tráfico de la red y aumentarán el rendimiento. Si modificamos un procedimiento almacenado, todos los clientes obtendrán el procedimiento almacenado actualizado.
Ejemplo de creación de un procedimiento almacenado
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
Ventajas de utilizar procedimientos almacenados
Un procedimiento almacenado permite la programación modular.
Puede crear el procedimiento una vez, almacenarlo en la base de datos y llamarlo tantas veces como desee en su programa.
Un procedimiento almacenado permite una ejecución más rápida.
Si la operación requiere una gran cantidad de código SQL que se realiza de forma repetitiva, los procedimientos almacenados pueden ser más rápidos. Se analizan y optimizan cuando se ejecutan por primera vez, y una versión compilada del procedimiento almacenado permanece en una memoria caché para su uso posterior. Esto significa que no es necesario volver a analizar y optimizar el procedimiento almacenado con cada uso, lo que da como resultado tiempos de ejecución mucho más rápidos.
Un procedimiento almacenado puede reducir el tráfico de la red.
Una operación que requiere cientos de líneas de código Transact-SQL se puede realizar mediante una única instrucción que ejecuta el código en un procedimiento, en lugar de enviar cientos de líneas de código a través de la red.
Los procedimientos almacenados brindan mayor seguridad a sus datos
A los usuarios se les puede otorgar permiso para ejecutar un procedimiento almacenado incluso si no tienen permiso para ejecutar las declaraciones del procedimiento directamente.
En SQL Server tenemos diferentes tipos de procedimientos almacenados:
- Procedimientos almacenados del sistema
- Procedimientos almacenados definidos por el usuario
- Procedimientos almacenados extendidos
Los procedimientos almacenados por el sistema se almacenan en la base de datos maestra y comienzan con un
sp_prefijo. Estos procedimientos se pueden utilizar para realizar una variedad de tareas para admitir funciones de SQL Server para llamadas de aplicaciones externas en las tablas del sistema.Ejemplo: sp_helptext [Nombre_procedimientoalmacenado]
Los procedimientos almacenados definidos por el usuario generalmente se almacenan en una base de datos de usuario y generalmente están diseñados para completar las tareas en la base de datos de usuario. Mientras codifica estos procedimientos, no use el
sp_prefijo porque si usamos elsp_prefijo primero, verificará la base de datos maestra y luego llegará a la base de datos definida por el usuario.Los procedimientos almacenados extendidos son los procedimientos que llaman a funciones desde archivos DLL. Hoy en día, los procedimientos almacenados extendidos están en desuso porque sería mejor evitar el uso de procedimientos almacenados extendidos.
