- Procedimientos Almacenados Parte I
- Procedimientos Almacenados Parte II. Definidos por el Usuario
- Procedimientos Almacenados Parte III. Con parámetros
Procedimientos almacenados
Un procedimiento almacenado es un conjunto de instrucciones de Transact-SQL que puede aceptar y devolver parámetros proporcionados por el usuario.
Los procedimientos se pueden crear para uso permanente o para uso temporal en una sesión, un procedimiento local temporal, o para su uso temporal en todas las sesiones, un procedimiento temporal global.
El motor de base de datos considera al procedimiento almacenado como un objeto que contiene un conjunto de instrucciones SQL que se ejecutan en conjunto con un mismo nombre y una sola llamada.
Consideraciones generales sobre los procedimientos almacenados
- Todo procedimiento almacenado se registra en el servidor actual
- Pueden incluir atributos de seguridad como permisos y cadenas de propiedad; además se les pueden asociar certificados.
- Los procedimientos almacenados mejoran la seguridad de una aplicación
- Los procedimientos almacenados con parámetros pueden ayudar a proteger la aplicación ante ataques por inyecciones de códifo SQL
- Permiten una programación modular
- Puede crear el procedimiento una vez y llamarlo desde el programa tantas veces lo desee. Así puede mejorar el mantenimiento de la aplicación y permitir que las aplicaciones tengan acceso a la base de datos de manera uniforme
- Pueden reducir el tráfico de red.
- Una operación que necesite centenares de líneas de código puede realizarse mediante una sola instrucción que ejecute el código en un procedimiento, en vez de enviar cientos de líneas por código a la red.
Tipos de procedimientos almacenados
Se describen particularmente tres tipos de procedimientos almacenados:
- Procedimientos almacenados del sistema.
- Procedimientos almacenados definidos por el usuario.
- Procedimientos almacenados Extendidos
Procedimientos Almacenados del Sistema
Los procedimientos almacenados del sistema, generalmente son guardados en la base de datos MASTER y son típicamente identificados por el prefijo SP_. Ellos realizan una amplia variedad de tareas para soportar las funciones del SQL Server soportando:
- Llamadas de aplicaciones externas para datos de las tablas del sistema
- Procedimientos generales para la administración de la base de datos.
- Funciones de administración de seguridad.
Nombraremos algunos procedimientos almacenados del sistema y veremos algunos ejemplos relevantes de estos procedimientos:
- sp_catalogos
- sp_column_provileges
- sp_column_privileges_ex
- sp_columns
- sp_columns_ex
- sp_databases
- sp_executeSQL
- sp_datatype_info
- sp_fkeys
- sp_foreignkeys
- sp_indexes
- sp_pkeys
- sp_primarykeys
- sp_server_info
- sp_special_columns
- sp_sproc_columns
- sp_statistics
- sp_table_privileges
- sp_table_privileges_ex
- sp_tables
- sp_tables_ex.
Ahora veremos algunos ejemplos con algunos procedimientos almacenados del sistema
Ejemplo 1:
Procedimiento almacenado del sistema que muestra los
privilegios de las columnas involucradas en la tabla PASAJERO
SP_COLUMN_PRIVILEGES
PASAJERO
GO
La imagen siguiente muestra el resultado de la ejecución el procedimiento almacenado.
Como notarán queridos lectores, se muestran todas las columnas de la tabla PASAJEROen COLUMN_NAME, además de los privilegios que cada columna especificado en PRIVILEGE.
Ejemplo 2:
Procedimiento almacenado
del sistema que muestre las columnas de la tabla PASAJERO
SP_COLUMNS
PASAJERO
GO
En el script se ejecuta el procedimiento almacenado del sistema que permitirá mostrar las columnas de la tabla PASAJERO.
Ejemplo 3:
Procedimiento almacenado
del sistema que muestre las bases de datos del servidor activo.
SP_DATABASES
GO
Este procedimiento tiene la capacidad de mostrar todas las bases de datos del servidor, además del tamaño asignado a cada una.
Ejemplo 4:
Procedimiento almacenado
del sistema, que muestre el pasajero de código IDPASAJERO=P0001 de la tabla
PASAJERO
EXECUTE SP_EXECUTESQL
N'SELECT * FROM PASAJERO
WHERE
IDPASAJERO=@COD',
N'@COD CHAR(5)',@COD='P0001'
GO
En el script se ejecuta el procedimiento almacenado EXECUTESQL que tiene como tipo de dato de devolución a nTEXT, es por eso que la consulta debe estar encerrada entre "N".
La siguiente imagen muestra el resultado de la ejecución del procedimiento almcenado:
Ejemplo 5:
Procedimiento almacenado
que devuelva información sobre el servidor de SQL SERVER
SP_SERVER_INFO
GO
El resultado de la ejecución se muestra en la siguiente imagen:
Ejemplo 6:
Procedimiento almacenado
del sistema que devuelve una lista de todos los índices y estadísticas de la
tabla PASAJERO
SP_STATISTICS
PASAJERO
GO
En la siguiente imagen se muestra el resultado de la ejecución:
Ejemplo 7:
Procedimiento almacenado
del sistema que devuelva la lista de permisos de tabla (como INSERT, DELETE,
UPDATE, SELECT o REFERENCES) para la tabla AEROPUERTO
EXECUTE SP_TABLE_PRIVILEGES 'AEROPUERTO'
GO
En el script se permiten mostrar los privilegios asignados a la tabla AEROPUERTO como lo muestra la siguiente imagen:
Ejemplo 8:
Procedimiento almacenado
del sistema que devuelva las tablas de la base de datos AGENCIA.
USE AGENCIA
GO
SP_TABLES
GO
En el script se permiten mostrar los privilegios asignados a la tabla AEROPUERTO como lo muestra la siguiente imagen:
Como notará, este procedimiento almacenado del sistema, no solo muestra las tablas definidas por el usuario sino también las vistas y las tablas nativas de la base de datos.
Instrucción EXECUTE y SP_EXECUTE
La instrucción EXECUTE indica que SqlClient, ODBC, OLE DB o DB-Library debe ejecutar una o varias instrucciones Transact-SQL preparadas, claro está que dichas instrucciones deben cumplir con la sintaxis evaluada desde el motor de base de datos de SQL
El SP_EXECUTE es un procedimiento almacenado, que ejecuta una cadena Unicode en la cual admite la sustitución de parámetros. Considere que el valor deberá ser mínimamente de tipo NVARCHAR, este procedimiento deberá ejecutarse con la instrucción EXEC obligatoriamente.
Veamos dos ejemplos que explicarán el uso de la instrucción EXEC y el procedimiento almacenado SP_EXECUTESQL
DECLARE @X VARCHAR(MAX)
SET @X='SELECT * FROM PASAJERO'
EXEC (@X)
GO
En el sript se usa la variable @X como contenedor de una consulta simple sobre la tabla PASAJERO, luego la instrucción SET asigna esa consulta a la variable y finalmente se muestra el resultado de la consulta por medio de la instrucción EXEC que tiene como parámetro la variable @X.
DECLARE @X NVARCHAR(MAX)
SET @X='SELECT * FROM PASAJERO'
EXEC SP_EXECUTESQL @X
GO
En el sript se cambia de tipo de dato de la variable @N por NVARCHAR y se le asigna el mismo valor de la consulta para que lo ejecute el procedimiento almacenado SP_EXECUTESQL con el parámetro @X.
En la imagen siguiente se muestra el resultado de ambos scripts
Hasta aquí el post de la primera parte de los procedimientos almacenados, mejor conocidos como SP, en el próximo post hablaré de los procedimientos almacenados creados por el usurio.
Espero que les haya sido de utilidad, también que compartan, comenten, le den +1 o algun clic en algún anuncio que les interese.
No hay comentarios.:
Publicar un comentario