Procedimientos de Almacenado

1
Procedimientos de Almacenado

• Rocío Contreras Águila
• Primer Semestre 2010

2
Que es un PA?

• Un Procedimiento Almacenado es un programa
  autocontrolado escrito en lenguaje del DBMS, son
  almacenados como parte de la Base de Datos y sus
  metadatos.

3
Que es un PA?

• Una vez creado un procedimiento almacenado, se
  puede invocar directamente desde una aplicación,
  o sustituir el nombre de una tabla o vista, por
  el nombre de procedimiento en cláusulas SELECT.
• Los procedimientos almacenados pueden recibir
  parámetros de entrada y retornar valores a la
  aplicación.

4
Ventajas

• Diseño modular.
• Aplicaciones que acceden la misma Base de Datos
  pueden compartir los procedimientos almacenados,
  eliminando el código doble y reduciendo el tamaño
  de las aplicaciones.
• El fácil mantenimiento.

5
Ventajas

• Cuando un procedimiento se actualiza, los cambios
  se reflejan automáticamente en todas las
  aplicaciones, sin la necesidad de recompilar y re
  linkear. Las aplicaciones son compiladas sólo una
  vez para cada cliente.

6
Ventajas

• Los procedimientos almacenados son ejecutados por
  el servidor, no por el cliente lo que reduce el
  tráfico en la red y mejora el performance o
  desempeño, especialmente para el acceso del
  cliente remoto.

7
Ventajas

• Están almacenados en los servidores y asegurados
  por las medidas tomadas en la instalación, lo que
  impide que los usuarios normales puedan
  modificarlos e incluso desconocen su existencia.
  Este es un elemento de gran valor en lo que a
  seguridad respecta

8
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.

9
Rendimiento

• 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.

10
Rendimiento

• El nombre del procedimiento almacenado se
  almacena en la tabla SysObjects, mientras que el
  texto del procedimiento se guarda en la tabla
  SysComments.
• Invocar al procedimiento almacenado implica
  ejecutar un solo comando en vez de cientos de
  comandos que un procedimiento almacenado podría
  contener.

11
Rendimiento

• 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.

12
Rendimiento

• 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.

13
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.

14
Marco de programación

• 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.

15
Marco de programación

• Todas las aplicaciones que llaman a esos
  procedimientos almacenados cumplirán con la
  nuevas reglas, sin tener que ser directamente
  modificados.

16
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.

17
Seguridad

• 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.
• Esta capacidad de seguridad permite aislar la
  estructura de la base de datos del usuario de la
  base de datos, con la consiguiente ganancia en
  seguridad.

18
Categorías de procedimientos almacenados

• Existen cinco categorías
• procedimientos almacenados del sistema,
• procedimientos almacenados locales,
• procedimientos almacenados temporales,
• procedimientos almacenados extendidos y
• procedimientos almacenados remotos.

19
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.

20
Procedimientos almacenados del sistema

• 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 PubsGOEXECUTE sp_table_privileges Stores

21
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.

22
Procedimientos almacenados locales

• 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.

23
Procedimientos almacenados temporales

• 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

24
Procedimientos almacenados temporales

• 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.

25
Procedimientos almacenados extendidos

• Un procedimiento almacenado extendido usa un
  programa externo, compilado como una DLL, para
  expandir las capacidades de un procedimiento
  almacenado.

26
Como se guarda un procedimiento

• Cuando se crea un procedimiento, SQL Server
  chequea la sintaxis de los comandos Transact-SQL
  que incluye. Si la sintaxis es incorrecta, SQL
  Server generará un mensaje  de error sintax
  incorrect (sintaxis incorrecta), y el
  procedimiento no será creado.

27
Como se guarda un procedimiento

• Si el procedimiento pasa el chequeo de sintaxis,
  el procedimiento se guarda, escribiéndose su
  nombre y otras informaciones en la tabla
  SysObject.
• El texto usado para crear el procedimiento se
  escribe en la tabla SysComments de la base de
  datos actual.

28
CREATE PROCEDURE

• Se puede usar el comando CREATE PROCEDURE, o su
  versión abreviada, CREATE PROC, para crear un
  procedimiento almacenado en el Query Analyzer.

29
CREATE PROCEDURE

• Cuando utiliza CRETE PROC, se pueden realizar las
  siguientes tareas
• Especificar agrupamientos de procedimientos
  almacenados
• Definir parámetros de entrada-salida, sus tipos
  de datos, y sus valores por defecto.

30
CREATE PROCEDURE

• Cuando se definen parámetros de entrada y salida,
  estos siempre van precedidos por el signo _at_,
  seguido del nombre del parámetro y luego una
  designación del tipo de dato.
• Los parámetros de salida deben incluir la palabra
  clave OUTPUT para diferenciarlos de los de
  entrada.       
• Usar códigos de retorno para mostrar información
  acerca del éxito o falla de una tarea.

31
CREATE PROCEDURE

• Controlar si un plan de ejecución debería ser
  guardado temporalmente para un procedimiento.
• Encriptar el contenido del procedimiento
  almacenado por razones de seguridad.
• Especificar las acciones que deberá tomar el
  procedimiento almacenado cuando se ejecute.

32
Proveer de contexto a un procedimiento almacenado

• Con la excepción de los procedimiento almacenado
  temporarios, un procedimiento almacenado se crea
  siempre en la base de datos actual.

33
Proveer de contexto a un procedimiento almacenado

• Siempre se debe especificar la base de datos
  actual usando el comando USE nombre_base seguido
  por el por el comando GO antes de crear un
  procedimiento almacenado

34
Proveer de contexto a un procedimiento almacenado

• Ejemplo
• USE Pubs
• GO
• CREATE PROCEDURE dbo.ListAuthorNames
• AS
• SELECT au_fname, aufname
• FROM pubs.dbo.authors

35
Crear procedimientos almacenados temporarios

• Para crear un procedimiento almacenado temporal
  local, se agrega delante del nombre del
  procedimiento el símbolo .
• Este signo numeral instruye al SQL Server para
  que cree el procedimiento en la TempDB

36
Crear procedimientos almacenados temporarios

• SQL Server ignora la base de datos actual cuando
  crea un procedimiento temporal.
• Crear un procedimiento temporal local
• CREATE PROCEDURE localtemp
• AS
• SELECT from pubs.dbo.authors
• GO