1 Bases de Datos RelacionalesPROCEDIMIENTOS ALMACENADOS Preparó: Ismael Castañeda Fuentes Fuentes: Manuales Sybase Manuales SQL Server Manuales Oracle

2 Procedimientos almacenadosUn procedimiento almacenado es un conjunto de sentencias SQL y de control de flujo Beneficios de los procedimientos almacenados: Simplifican la ejecución de tareas repetitivas Corren más rápido que las mismas instrucciones ejecutadas en forma interactiva Reducen el tráfico a través de la red Pueden capturar errores antes que ellos puedan entrar a la base de datos Establece consistencia porque ejecuta las tareas de la misma forma Permite el desarrollo modular de aplicaciones Ayuda a proveer seguridad Puede forzar reglas y defaults complejos de los negocios

3 Procedimientos - TiposProcedimientos almacenados definidos por el usuario Son procedimientos definidos por el usuario que se deben llamar explícitamente Triggers Son procedimientos definidos por el usuario que se ejecutan automáticamente cuando se modifica un dato en una tabla Procedimientos del sistema Procedimientos suministrados por el sistema Procedimientos extendidos Procedimientos que hacen llamadas al sistema operativo y ejecutan tareas a ese nivel

4 Interactive Execution

5 Procedimientos - Creación

6 Procedimientos - Ejecución

7 Procedimientos - Ventajas en el rendimientoUn procedimiento almacenado se ejecuta más rápido que un batch porque: El procedimiento almacenado ya ha sido analizado Ya se han resuelto las referencias a los objetos referenciados en el procedimiento almacenado No se necesita construir el árbol de búsqueda, él usa el que se hace en el momento de compilarlo No se necesita crear un plan de búsqueda, porque ya el procedimiento tiene uno

8 Procedimientos - Crear y borrarSintaxis simplificada para create: create proc procedure_name as statements return Sintaxis simplificada para drop: drop proc procedure_name

9 Procedimientos - EjecuciónSintaxis simplificada: [exec | execute] procedure_name

10 Procedimientos - VariablesLos procedimientos almacenados pueden crear y usar variables locales Las variables sólo existen mientras exista el procedimiento Las variables no las puede usar otro proceso

11 Procedimientos - Sentencias válidas e inválidasUn procedimiento almacenado puede: Seleccionar y modificar datos Crear tablas temporales y permanentes Llamar otros procedimientos almacenados Referenciar objetos de bases de datos Un procedimiento almacenado no puede ejecutar: use database create view create default create rule create procedure create trigger

12 Procedimientos – PermisosPara permitir que otros usen un procedimiento almacenado, el propietario debe dar los respectivos permisos Sintaxis simplificada: grant execute on procedure_name to user_list

13 Procedimientos – Parámetros de entradaUn Parámetro de entrada es una variable local del procedimiento almacenado que puede recibir un valor de una sentencia exec procedure lista_de_parámetros

14 Procedimientos – Parámetros de entradaSintaxis simplificada: create procedure procedure_name (parameter_name datatype default_value [, parameter_name datatype default_value...] ) as statements return

15 Procedimientos – Paso de parámetrosDos métodos para pasar valores a parámetros: Paso de parámetros por posición Paso de parámetros por nombre

16 Procedimientos – Paso de parámetros por posiciónSintaxis para paso por posición: [exec | execute] procedure_name value [, value...] Los parámetros se deben pasar en el mismo orden en que ellos aparecen en la sentencia create procedure Como este método es más propenso a errores, se aconseja el paso por nombre

17 Procedimientos – Paso de parámetros por nombreSintaxis para paso por nombre: [exec | execute] procedure procedure_name parameter_name = value [, parameter_name = value ] Los nombres de los parámetros en la sentencia exec deben concordar con los nombres de los parámetros usados en la sentencia create procedure Los parámetros pueden pasar en cualquier orden

18 Procedimientos – Valores por defaultSe puede asignar un valor por default a un parámetro cuando él no se indica en la sentencia exec ...

19 Procedimientos – Errores típicos en parámetros de entradaLos valores que se pasan no tienen el mismo tipo de datos que los parámetros definidos En la misma sentencia, se pasa un parámetro por posición después de haber pasado un parámetro por nombre Aunque no es recomendado, es posible mezclar los dos métodos para pasar valores, sin embargo, después de pasar un valor a un parámetro por nombre, todos los restantes de deben pasar por nombre Olvido de uno o más parámetros El olvido de uno o más valores para los parámetros, hace que se usen los valores por default Los valores para los parámetros se pasan en un orden errado

20 Procedimientos – Retorno de valoresUn parámetro output es una variable local en un procedimiento almacenado que se puede enviar a una Sentencia exec procedure

21 Procedimientos – Crear parámetros que retornan valoresSintaxis simplificada: create procedure procedure_name (parameter_name datatype output [, parameter_name datatype output...] ) as statements return

22 Procedimientos – Usar parámetros que retornan valoresSintaxis simplificada: [exec | execute] procedure_name variable output Los valores que retornan los parámetros se pasan automáticamente al conjunto respuesta El retorno de valores se pueden pasar por nombre o por posición Se recomienda el paso por nombre

23 Legibilidad Para hacer un código más legible:Utilizar comentarios Utilizar indentación Usar espacios en blanco y dejar el código organizado en columnas Declarar e iniciar las variables en un bloque Establecer un conjunto de buenas prácticas para legibilidad

24 Procedimientos – Chequeo de valoresEl servidor chequea que los parámetros concuerden en tipo de datos Los procedimientos almacenados verifican que los valores pasados se encuentren dentro del dominio establecido

25 Procedimientos – mensajes de errorSi un procedimiento almacenado requiere valores para los parámetros, el usuario debe incluir un tratamiento de esos errores y un manejo de mensajes de error Ejemplo: create proc proc_cutoff char(6) = NULL, @max_price money = NULL) as is NULL is NULL begin raiserror 20001 "Execution for this procedure is: exec proc_cutoff title_id, max_price" return end ... return

26 Procedimientos – Rótulos de modo de transacciónLos procedimientos almacenados se rotulan con el modo de transacción con el cual fueron creados No se puede ejecutar una transacción en un modo diferente al del rótulo

27 Procedimientos – sp_procxmodesp_procxmode permite ver y cambiar el modo de transacción de un procedimiento Sintaxis: sp_procxmode [ procedure_name [ , {chained | unchained | anymode} ] ] Ejemplo: sp_procxmode proc_update_titles, unchained

28 Procedimientos – Retorno de valores de estadoA return status es un valor que indica cuándo o no un procedimiento se ha ejecutado totalmente

29 Procedimientos – Retorno de valores de estado

30 Procedimientos – Retorno de valores de estadoSintaxis simplificada: create proc procedure_name as statements return [return_status]

31 Procedimientos – Captura del valor del estado de retornoLos valores de retorno se deben capturar en variables Sintaxis simplificada: [exec | execute] variable = procedure_name parameter_list

32 Procedimientos – Rollbacks no intencionalesUn rollback no intencional es un rollback anidado que sin intensión deshace el trabajo en transacciones externas Proc1(Outermost tran) begin tran if begin rollback tran return end exec proc2 commit tran return Proc2(Nested proc) begin tran if begin rollback tran return end commit tran return

33 Procedimientos – Savepoints y Rollbacks anidadosUsar savepoints para evitar rollbacks no intencionales batch (Outermost tran) begin tran (1) exec proc1 if rollback tran (6) else commit tran (1) Con begin tran inicia la transacción. Nivel anidamiento: 1. (6) rollback deshace todas las sentencias en proc1, proc2, y batch -or- commit hace commit a todo. proc 1 save tran abc (2) exec proc2 if rollback tran abc (5) else ... (2) save tran no incrementa el nivel de anidamiento. (5) rollback regresa al punto abc. Entonces se ejecutan las subsiguientes sentencias en proc1. proc 2 save tran xyz (3) if rollback tran xyz(4) else ... (3) save tran no incrementa el nivel de anidamiento. (4) rollback regresa al punto xyz. Entonces se ejecutan las subsiguientes sentencias en proc2.

34 Procedimientos – Límite de anidamientoLos procedimientos almacenados pueden llamar otros procedimientos almacenados El máximo nivel de anidamiento es 16 La variable contiene el nivel de anidamiento actual Si se excede el nivel máximo: Se abortan los procedimientos pendientes El servidor retorna un error

35 Planes de búsqueda Un plan de búsqueda es un conjunto ordenado de estapas que se requieren para acceder los datos, incluyendo información sobre: Si usar o no un índice El índice a usar El orden en el cual las tablas se deben encadenar Los planes de búsqueda son creados por el optimizador de búsquedas El optimizador de búsquedas usa información acerca de los objetos de base de datos para producir el plan Los planes de búsqueda creados para los procedimientos, se reutilizan Cuando se ejecuta un procedimiento almacenado, el servidor chequea el caché del procedimiento para un plan no usado Si hay un plan de búsqueda no utilizado, el servidor lo usa Si no hay un plan de búsqueda no utilizado, el servidor genera uno nuevo del árbol de búsqueda en sysprocedures

36 Planes de búsqueda sub-óptimosEl plan de búsqueda creado para la una ejecución de un procedimiento almacenado puede que no sea el plan de búsqueda óptimo para la siguiente ejecución del procedimiento almacenado Las dos ejecuciones pueden usar parámetros de entrada muy diferentes Se pueden haber añadido nuevos índices entre las dos ejecuciones El tamaño de las tablas accesadas pueden haber cambiado significativamente entre las dos ejecuciones Hay tres formas para forzar al servidor a generar un nuevo plan de búsqueda Usar with recompile en el procedimiento Usar with recompile cuando se ejecute el procedimiento Usar sp_recompile

37 Procedimientos con recompileEn un procedimiento, usar la opción with recompile para forzar al servidor a crear un nuevo plan de búsqueda cada vez que se ejecute el procedimiento Sintaxis simplificada: create proc procedure_name with recompile as statements return

38 Procedimientos con recompileCuando se ejecute un procedimiento almacenado, usar la opción with recompile para forzar al servidor a crear un nuevo plan de búsqueda para esa ejecución del procedimiento Esta opción se puede usar cuando se ejecuta cualquier procedimiento almacenado Sintaxis simplificada: [exec | execute] procedure_name with recompile

39 sp_recompile sp_recompile hace que cada procedimiento almacenado (y trigger) que utilice la tabla indicada se recompile la siguiente vez que él se ejecute Sintaxis: sp_recompile table_name