Un procedimiento almacenado es un conjunto de sentencias SQL y de control cont rol de flujo Beneficios de los procedimientos almacenados: almacenados: ◦ ◦
◦ ◦
◦ ◦ ◦
Simplifican la ejecución de tareas repetitivas Corren más rápido rápido que las l as mismas instrucciones ejecutadas en forma interactiva Reducen el tráfico a través de la red Pueden capturar capturar errores antes que ellos puedan entrar a la base de datos Establece consistencia porque ejecuta las tareas de la misma forma f orma Permite el desarrollo desarrollo modular de aplicaciones Ayuda a proveer seguridad
Procedimientos almacenados definidos por el usuario ◦
Triggers ◦
Son procedimientos definidos por el usuario que se ejecutan automáticamente cuando se modifica un dato en una tabla
Procedimientos del sistema ◦
Son procedimientos definidos por el usuario que se deben llamar explícitamente
Procedimientos suministrados por el sistema
Procedimientos extendidos ◦
Procedimientos que hacen llamadas al sistema operativo y ejecutan tareas a ese nivel
Un 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 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
Sintaxis Sintaxis simplifica simplificada da para para create: create: create procedure Nombre_Procedimiento AS sentencias return
Ejemplo: create procedure ActualizarTitulos AS UPDATE titles SET price = price * $0.95 where wher e total_s total_sales ales < 3000 return
Sintaxis simplificada para drop: drop procedure NombreProcedimiento
Ejemplo:
drop procedure proc_update_titles
Sintaxis simplificada:
[exec | execute] NombreProcedimiento
Ejemplo: execute proc_update_titles
Los procedimientos almacenados pueden pueden crear y usar variables locales ◦ ◦
Las variables sólo existen mientras exista el procedimiento Las variables no las puede usar otro proceso
Ejemplo: create procedure ActualizarPromedioTitulos AS declare decl are @MaxV @MaxVent entas as int int,, @Pr @PromM omMax ax rea reall select sel ect @ Ma MaxV xVen entas tas = max(total max(total_sa _sales) les) from titles select sel ect @Pr @PromM omMax ax = @MaxV @MaxVent entas as / 2 select title, total_sale total_sales s from titles titles where wher e total_sales total_sales < @PromMax @PromMax update titles set price = price * $0.95 where wher e total_sales total_sales < @PromMax @PromMax return
Un procedimiento almacenado puede: ◦
◦
◦
◦
Seleccionar y modificar datos Crear tablas temporales y permanentes Llamar a otros procedimientos almacenados Referenciar objetos de bases de datos
Un procedimiento almacenado almacenado no puede ejecutar: ejecutar : ◦
◦
◦
◦
◦
◦
use database create view create default create rule create procedure create trigger
Para permitir que otros usen un procedimiento almacenado, el propietario debe dar los respectivos permisos
Sintaxis simplificada: grant execute on NombreProcedimiento to ListaUsuarios
Ejemplo: grant execute on ActualizarPromedi ActualizarPromedioTitulos oTitulos to lboy, lboy, tjovanna, vturbo
Crear un procedimiento almacenado sencillo: sencillo: create procedure SP_Hola AS print Hola Hola,, bre>"" return “
Ver el código fuente fuen te de un procedimiento: sp_helpt sp_h elptext ext SP_ SP_Hol Hola a
Ejecutar el procedimiento: execute SP_Hola
Borrar los objetos de bases de datos: drop procedure SP_Hola
Un parámetro de entrada es una variable local para el procedimiento almacenado que puede recibir un valor desde la sentencia:
Sintaxis simplificada: simplificada: create procedure NombreProcedimiento (parameter_name (param eter_name datatype default_value parameter_name datatype default_value ...] [, parameter_name ...] ) AS Sentencias return
Ejemplo: create procedure InfoAu InfoAutor tor (@lname (@lnam e var varchar( char(40), 40), @fname var varchar char(20)) (20)) AS -- Li List sta a de los los Lib Librros de dell Au Auto torr select au_lname, au_fname, title from authors, titles, titleauthor where wher e au_fn au_fname ame = @fname @fname and an d au_lna au_lname me = @lname @lname and authors.au_ authors.au_id id = titleau titleauthor thor.au_id .au_id and titles.title_id titles.title_id = titleau titleauthor thor.title_id .title_id return
Dos métodos para pasar valores a parámetros: ◦
◦
Paso de parámetros por posición Paso de parámetros por nombre
Sintaxis para paso por posición: [exec | execute] procedure_name value [, value ...] ...] Ejemplo: exec exe c AutorIn utorInfo fo "Ring "Ringer", er", "Albert" "Albert" au_lname --------------Ringer Ringer
au_fname --------------Albert Albert
title ----Is Anger the Enemy? Life Without Fear
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
Sintaxis para paso por nombre: [exec | execute] procedure procedure_name parameter_name = value [, parameter_name = value ]
Ejemplo: exec AuthorInfo @lnam @ln ame e = "Ringer", "Ringer", @fnam @fname e = "Albert "Albert"" au_lname --------------Ringer Ringer
au_fname title ------------------Albert Is Anger the Enemy? Albert Life Without Fear
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
Se puede asignar un valor por default a un parámetro cuando él no se indica en la sentencia execute Ejemplo:
create procedure EstadoAutores (@state char(2) = "CA") AS select au_lname, au_fname, state from authors where state = @state return execute EstadoAutores au_lname ----------White Green ...
au_fname -----------Johnson Marjorie
state ----CA CA
Los valores que se pasan no tienen el mismo tipo t ipo 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 ◦
Olvido de uno o más parámetros ◦
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, nombre, todos los restantes deben pasar por nombre 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
create procedure InsertarVenta (@stor_idchar(4)= NULL, @ord_n @or d_num um var varcha char( r(20) 20)= = NU NULL, LL, @date datetime= NULL) AS /* Si no se pas pasa a la fe fech cha, a, us use e la la Fe Fech cha a Ac Actu tual al */ if (@date is NULL) begin select @date = getdate() end begin transacti transaction on /* Insertar Ventas */ insert sales (stor_id, ord_num, date) values (@stor_id, @ord_num, @date) if @@error <> 0 begin rollback transaction rais ra iser erro ror r 24 2400 001 1 "Tr Tran ansa sacc cció ión n fa fal lla lada da …" return end commit transaction return
Crear un procedimiento almacenado que tenga un parámetro de entrada:
create crea te pro proce cedu dure re SP_ SP_Ho Hola la (@ (@No Nomb mbre re va varc rcha har( r(30 30)) )) AS print “Ho Hola la %1 %1! !", @N @No omb mbre re return
Ejecutar el procedimiento con y sin un valor para el parámetro de entrada. Una sentencia fallará: exec SP_Hola exec SP SP_Hol ola a ""
Crear un procedimiento almacenado que tiene un valor por default para un parámetro de entrada:
create procedure Hola (@No (@ Nomb mbre re va varc rcha har( r(30 30) ) = “Quien quiera que seas") AS print “Ho Hola la %1 %1! !", @N @No omb mbre re return
•
Ejecutar el procedimiento con y sin un valor para el parámetro de entrada: exec Hola exec ex ec Ho Hola la "" >"
•
¿Cuál procedimiento almacenado parece ser más amigable?
•
Borrar los objetos de bases de datos creados: drop procedure SP_Hola, Hola
El reto retorn rno o de un un Paráme rámetr tro o es una una vari varia able loc local a un pro proc cedimi dimien ento to alma lmacena enado que que puede uede enviar enviar un valor valor a la sent sentenc encia ia
Sintaxis simplificada: simplificada: create procedure NombreProcedimiento (parameter_name datatype output [,[, parameter_name datatype output...] ) AS Sentencias return Ejemplo:
create SP_NuevoPrecio (@ti (@ titl tle_ e_id id ch char ar(6 (6), ), @Nuevo @Nu evoPre Precio cio mon money ey out outpu put) t) AS select sel ect @Nu @Nuevo evoPre Preci cio o = pri price ce from titles wher wh ere e titl title_ e_id id = @titl @title_ e_i id sele se lect ct @N @Nu uev evoP oPre reci cio o = @Nu @Nuev evoP oPre rec cio * $1. $1.15 15 return
Sintaxis simplificada: [ ] NombreProcedimiento variable
Ejemplo:
declar decl are e @m @my_ y_ti titl tle e ch char ar(6 (6), ), @m @my_ y_pr pric ice e mo mone ney y select sel ect @m @my_t y_titl itle e = "P "PC88 C8888" 88" exec proc_new_price @my_t @my _titl itle, e, @my @my_pr _price ice out output put ---------23.00
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
Los procedimientos almacenados pueden llamar otros procedimientos almacenados ◦
◦
El máximo nivel de anidamiento es 16 @@nestlevel contiene el nivel de anidamiento actual La variable @@nestlevel contiene
Si se excede el nivel máximo: ◦
◦
Se abortan los procedimientos pendientes El servidor retorna un error
Un plan de búsqueda búsqued a es un conjunto ordenado de etapas que se requieren para acceder los datos, incluyendo información sobre: ◦
◦
◦
Los planes de búsqueda son creados por el optimizador de búsquedas búsqued as ◦
Si usar o no un índice El índice a usar El orden en el cual las tablas se deben encadenar El optimizador de búsquedas usa información acerca de los objetos o bjetos de base de datos para producir el plan
Los planes de búsqueda búsqued a creados para los procedimientos, se reutilizan ◦
◦
◦
Cuando se ejecuta un procedimiento p rocedimiento almacenado, el servidor chequea cheq uea 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 búsqued a en sysprocedures
El plan de búsqueda creado para la ejecución de un procedimiento almacenado puede que no sea el plan de búsqueda búsqued a óptimo para la siguiente ejecución del procedimiento almacenado ◦
◦
◦
Las dos ejecuciones ejecucione s 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 accedidas accedida s pueden haber cambiado significativamente entre las dos ejecuciones
Hay tres formas para forzar al servidor servidor a generar ge nerar un nuevo plan de búsqueda ◦
◦
◦
Usar with Usar with recompile en el procedimiento Usar with Usar with recompile cuando se ejecute el procedimiento Usar sp_recompile Usar sp_recompile
En un procedimiento, usar la opción with recompile para forzar al servidor a crear un u n nuevo plan de búsqueda cada vez que se ejecute el procedimiento Sintaxis simplificada: create procedure NombreProcedimiento with recompile AS Sentencias
return
•
Ejemplo: create procedure SP_lista_AutoresCalifornia (@No (@ Nomb mbre re va varc rcha har( r(80 80), ), @Ap @Apel elli lido dos s va varc rcha har( r(80 80)) )) with recompile AS select au_id, au_lname, au_fname from authors wher wh ere e au_ au_ln lnam ame e be betw twee een n @No @Nomb mbre re an and d @Ap @Apel elli lido dos s and state = "CA" order by au_lname return
Cuando se ejecute un procedimiento almacenado, 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: [exe [e xec c | exe execu cute te]] NombreProcedimiento with wi th re reco comp mpil ile e Ejemplo: execute ActualizarTitulos with 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 NombreTabla Ejemplo: sp_r sp _rec ecom ompi pile le au auth thor ors s
