Download Procedimientos de Almacenado

Procedimientos de
Almacenado
Rocío Contreras Águila
Primer Semestre 2010
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Categorías de procedimientos
almacenados
• Existen cinco categorías :
1. procedimientos almacenados del
sistema,
2. procedimientos almacenados locales,
3. procedimientos almacenados
temporales,
4. procedimientos almacenados extendidos
y
5. 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.
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 Pubs
GO
EXECUTE sp_table_privileges Stores
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.
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.
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
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.
Procedimientos almacenados
extendidos
• Un procedimiento almacenado extendido
usa un programa externo, compilado
como una DLL, para expandir las
capacidades de un procedimiento
almacenado.
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.
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.
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.
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.
CREATE PROCEDURE
• Cuando se definen parámetros de entrada y
salida, estos siempre van precedidos por el
signo @, 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.
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.
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.
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
Proveer de contexto a un
procedimiento almacenado
• Ejemplo:
USE Pubs
GO
CREATE PROCEDURE [dbo].[ListAuthorNames]
AS
SELECT [au_fname], [aufname]
FROM [pubs].[dbo].[authors]
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
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