TEMA 15: Introducción a los procedimientos almacenados

Hasta ahora hemos utilizado el Query Analizer (o la herramienta on-line) para ejecutar comandos o grupos de comandos en el lenguaje Transact SQL. A los grupos de comandos los llamaremos scripts (guiones).

Cuando se ejecuta un script, los comandos son ejecutados por SQL Server para mostrar un conjunto de resultados, configurar un comportamiento y administrar al servidor o para manipular datos contenidos en una base de datos.

A estos scripts se les puede dar un nombre y guardar como procedimientos almacenados en el SQL Server. Posteriormente se los puede invocar de diversas maneras, tal como desde el Query Analizer, para que realicen el procesamiento de los comandos Transact SQL incorporados.

El SQL Server provee una serie de procedimientos almacenados del sistema, algunos de los cuales ya hemos utilizado en este Kit. Estos procedimientos son guardados en la base de datos Master y contienen comando Transact SQL que facilitan muchas tareas.

Propósito y ventajas de los procedimientos almacenados

Los procedimientos almacenados proporcionan ventajas de performance, un marco de trabajo, y mayores capacidades de seguridad. La mejora en el rendimiento se logra a través de un almacenamiento local (en la base de datos), código precompilado, y manejo de cachés  (almacenamientos temporarios). El marco de programación se logra a través de construcciones comunes de programación tales como parámetros de entrada/salida y reutilización de los procedimientos. Las capacidades de seguridad incluye encriptación y limitaciones de privilegios que permiten mantener a los usuarios fuera de la vista de la estructura de la base de datos subyacente, mientras se los habilita a ejecutar procedimientos almacenados  que actúan sobre la base de datos.

Rendimiento

Cada vez que un comando Transact-SQL, o conjunto de comandos, es enviado el servidor para su procesamiento, el servidor debe determinar si el remitente tiene suficientes privilegios para ejecutar esos comandos y si los comandos son válidos. Una vez que los permisos y la sintaxis de los comandos se han verificado, SQL Server construye un plan de ejecución para procesar el pedido.

Los procedimientos almacenados son más eficientes en parte porque el procedimiento es almacenado en el SQL Server cuando se crea. La sintaxis de los comandos contenidos en un procedimiento almacenado se comprueba que este libre de errores antes de ser guardado. El nombre del procedimiento almacenado se almacena en la tabla SysObjects, mientras que el texto del procedimiento se guarda en la tabla SysComments. Por otro lado, invocar al procedimiento almacenado implica ejecutar un solo comando en vez de cientos de comandos que un procedimiento almacenado podría contener.

La primera vez que se ejecuta el procedimiento, se crea un plan de ejecución y se compila al procedimiento almacenado. Los procesamientos subsecuentes del procedimiento almacenado son mucho más rápidos ya que el SQL Server no vuelve a controlar la sintaxis, ni recrea un plan de ejecución, ni se recompila el procedimiento. Por último se verifica el caché por si ya existe un plan de ejecución para ese procedimiento antes de generar un nuevo plan de ejecución.

La relativa pérdida de rendimiento producida por ubicar los planes de ejecución de los procedimientos almacenados en el caché de procedimiento se reduce ya que los planes de ejecución para todos los comandos SQL se guardan ahora en el caché de procedimientos. Por lo que un comando Transact-SQL tratará de utilizar un plan de ejecución ya existente en todos casos posibles.

Marco de programación

Una vez que se crea un procedimiento almacenado, puede ser llamado todas las veces que sea necesario. Esta capacidad provee modulación y habilita la reutilización del código. La reutilización del código mejora el mantenimiento de la base de datos al aislar la base de datos de los cambios en las prácticas del negocio. Si las reglas de negocios cambian en una organización, se puede modificar a los procedimientos almacenados para cumplir con las nuevas reglas de negocio. Todas las aplicaciones que llaman a esos procedimientos almacenados cumplirán con la nuevas reglas, sin tener que ser directamente modificados.

Tal y como otros lenguajes de programación, los procedimientos almacenados pueden aceptar parámetros de ingreso, retornar parámetros de salida, producir información de retroalimentación de la ejecución en la forma de códigos de estatus y texto descriptivo, y llamar a otros procedimientos. Por ejemplo, un procedimiento almacenado puede retornar un código de estatus a un procedimiento que lo llamó para que este procedimiento realice una operación según el código recibido.

Los desarrolladores de software pueden escribir sofisticados en un lenguaje como el C++; luego, se puede utilizar un tipo especial de procedimiento almacenado, denominado procedimiento almacenado extendido, para invocar al programa desde dentro del SQL Server.

Ante cualquier tarea simple, se debería escribir un procedimiento almacenado. Mientras más genérico sea el procedimiento más útil será para muchas bases de datos. Por ejemplo; el procedimiento almacenado sp_rename cambia el nombre de un objeto creado por el usuario, tal como una tabla, una columna o un tipo de datos definido por el usuario en la base de datos actual, pudiéndose aplicar a cualquier base de datos.

Seguridad

Otro capacidad importante de los procedimientos almacenados es que mejoran la seguridad a través de la encriptación y el aislamiento. Los usuarios de las bases de datos pueden tener permisos de ejecutar un procedimiento almacenado sin tenerlos para acceder directamente a los objetos de la bases de datos sobre las que opera el procedimiento almacenado. Además un procedimiento almacenado puede ser encriptado cuando se lo crea o modifica inhabilitando a los usuarios a leer los comandos Transact-SQL contenidos en el procedimiento almacenado. Estas capacidad de seguridad permiten aislar la estructura de la base de datos del usuario de la base de datos, con la consiguiente ganancia en seguridad.

Categorías de procedimientos almacenados

Existen cinco categorías de procedimientos almacenados: procedimientos almacenados del sistema, procedimientos almacenados locales, procedimientos almacenados temporarios, procedimientos almacenados extendidos y procedimientos almacenados remotos.

Procedimientos almacenados del sistema

Los procedimientos almacenados del sistema 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 administración de las bases de datos, y funciones de administración de seguridad. Por ejemplo, se pueden ver los privilegios de una tabla usando el procedimiento almacenado de catálogo sp_table_privileges. El comando siguiente utiliza este procedimiento almacenado para mostrar los privilegios de la tabla stores en la base de datos Pubs:

USE PubsGO
EXECUTE sp_table_privileges Stores

Una tarea común de administración de bases de datos  es consultar la información acerca de los usuarios y procesos que se están ejecutando en una base de datos. Este paso es importante cuando se debe dar de baja la base de datos. El siguiente comando usa el procedimiento sp_who para mostrar todos los procesos en uso por el usuario LAB1\Administrator:

EXECUTE sp_who @loginame=’LAB1\administrator’

La seguridad de base de datos es crítica para la mayoría de las organizaciones que almacenan datos privados en su base de datos. El comando siguiente usa el procedimiento almacenado del sistema sp_validatelogins para mostrar cualquier usuario y grupo de usuarios Windows NT o Windows 2000 huérfanos que no existan más y que aún tengan entradas en las tablas del sistema del SQL Server:

EXECUTE sp_validatelogins

Hay cientos de procedimientos almacenados del sistema incluidos con el SQL Server. Para una lista completa de los procedimientos almacenados, consultar “System Store Procedures” en SQL Server Books Online.

Procedimientos almacenados locales

Los procedimientos almacenados locales son usualmente almacenados en una base de datos y están típicamente diseñados para completar tareas en la base de datos donde residen. Un procedimiento almacenado local se podría crear también para personalizar código de los procedimientos almacenados del sistema. Para crear una tarea personalizada basada sobre un procedimiento almacenado del sistema, primero copie el contenido del procedimiento almacenado del sistema y guarde el nuevo procedimiento almacenado y guarde el nuevo procedimiento almacenado como un procedimiento almacenado local.

Procedimientos almacenados temporarios

Un procedimiento almacenado temporario es similar a un procedimiento almacenado local, pero existe sólo hasta que se cierre la conexión que lo creó o se dé de baja el SQL Server, dependiendo del tipo de procedimiento almacenado. Estos procedimientos tienen una existencia volátil debido a que son creados son almacenados en la base de datos TempDB. TempDB se recrea cuando se reinicia el servidor; por lo tanto, todos los objetos dentro de la base de datos desaparecen después de la base de datos. Los procedimientos almacenados temporarios son muy útiles si Ud. está accediendo a versiones anteriores del SQL Server que no soportan la reutilización de los planes de ejecución y si no se quiere almacenar las tareas que serán ejecutadas con distintos parámetros.

Hay tres tipos de procedimiento almacenado temporarios: locales (también llamados privados), globales, y procedimientos almacenados en TempDB. Un procedimiento almacenado temporario local siempre comienza con #, un procedimiento almacenado temporario global siempre comienza con ##. Mas adelante se explicará como crear cada tipo de procedimiento almacenado temporario. El alcance de ejecución de un procedimiento almacenado local esta limitado a la conexión que lo creó. Todos los usuarios que tienen conexión a la base de datos, sin embargo, pueden ver el procedimiento almacenado en la ventana del Object Browser del Query Analizer. Dado que se encuentra limitado en su alcance, no existe la posibilidad de colisión de nombres con otras conexiones que crean procedimientos almacenados temporarios. Para asegurar unicidad, SQL Server  agrega al nombre de un procedimiento almacenado con una serie de caracteres _ y un número único de conexión. Los privilegios no pueden ser garantizado a otros usuarios para el procedimiento almacenado temporario. Cuando la conexión que creó el procedimiento almacenado temporario se cerró, el procediemiento se elimina de la TempDB.

Cualquier conexión para la base de datos puede ejecutar un procedimiento almacenado temporario global. Este tipo de procedimientos debe tener un nombre único , dado todas las conexiones pueden ejecutarlo y, cómo todos los procedimientos almacenados temporarios, este es creado en TempDB. El permiso para ejecutar procedimientos almacenados es automáticamente garantizado al rol público y no puede ser cambiado. Un procedimiento almacenado temporario global es casi tan volátil como los locales. Este tipo de procedimiento es removido cuando la conexión usada para crear el procedimiento se cierra y cualquier conexión que este ejecutando el procedimiento almacenado es completada.

Los procedimientos almacenados temporarios creados directamente en la TempDB son diferentes a los procedimientos almacenados locales y globales en lo siguiente:

·        Se pueden configurar permisos para ellos.

·        Existen aún después que la conexión que los creó se terminan

·        No son removidos hasta que el SQL Server  no sea apagado.

Ya que este tipo de procedimiento almacenado se crea directamente en la TempDB, es importante calificar completamente los objetos de base de datos referenciados por comandos Transact-SQL en el código. Por ejemplo, se debe referenciar la tabla Authors, la cual es propiedad de dbo en la base de datos Pubs, como pubs.dbo.authors.

Procedimientos almacenados extendidos

Un procedimiento almacenado extendido usa un programa externo, compilado como una DLL, librería de vínculos dinámicos (dynamic link library) para expandir las capacidades de un procedimiento almacenado. Un número de procedimiento almacenado del sistema son también calificados como procedimientos almacenados extendidos. Por ejemplo, el programa xp_sendmail, el cual envía un mensaje y un archivo de conjunto de resultados de una consulta adjunto a una dirección específica de email, es tanto un procedimiento almacenado de sistema como un procedimiento almacenado extendido. La mayoría de los procedimientos almacenados extendidos usan el prefijo xp_ como un convención de nombre. Sin embargo, hay algunos procedimientos almacenados extendidos que comienzan con el prefijo sp_, y hay algunos procedimientos almacenados del sistema que no son procedimientos extendidos y usan el prefijo xp_. Por lo tanto, no se puede depender sobre convención de nombres para identificar procedimientos almacenados del sistema y procedimientos almacenados extendidos.

Se puede usar la función OBJECTPROPERTY para determinar si un procedimiento almacenado es extendido o no. OBJECTPROPERTY retorna un valor de 1 si es un procedimiento extendido (IsExtendedProc), indicando un procedimiento almacenado extendido, o retorna un valor de 0, indicando que no es un procedimiento extendido. El siguiente ejemplo demuestra que el sp_prepare es un procedimiento almacenado extendido y que xp_logininfo no es un procedimiento almacenado extendido:

USE Master

·        un procedimiento almacenado extendido que usa el prefijo sp_ SELECT OBJECTPROPERTY(object_id(‘sp_prepare’), ‘IsExtendedProc’)

 Este ejemplo retorna un valor de 1

USE Master

·        un procedimiento almacenado que no es extendido y usa el prefijo xp_ SELECT OBJECTPROPERTY(object_id(‘xp_logininfo’), ‘IsExtendedProc’)

 Este ejemplo retorna un valor de 0.