2. Creación y Ejecución de Procedimientos Almacenados.pdf

5/25/2018 2. Creacin y Ejecucin de Procedimientos Almacenados.pdf

1/5

Creacin y ejecucin de procedimientos almacenados

La sintaxis completa de create procedure es:

cr eat e pr ocedur e [owner.]procedure_name[;number] [ [ (]@parameter_name datatype [= def aul t] [output][ , @parameter_name datatype [= def aul t] [output] ] . . . [ ) ] ] [wi th recompi l e]

as sql_statements

Slo se puede crear un procedimiento en la base de datos actual.

El permiso para emitir create procedure est asignado de forma predeterminada al propietario de la base de datos, que puede transferirlo a otrosusuarios.

A continuacin se muestra la instruccin de sintaxis completa de execute :

[ execut e] [ @r et urn_stat us = ][ [ [ server . ] database . ] owner . ] procedure_name [ ; number ]

[ [ @ parameter_name =] value |[ @ parameter_name =] @ variable [ out put ][ , [ @parameter_name =] value |[ @ parameter_name =] @ variable [output ] . . . ] ][ wi t h r ecompi l e]

Note: Las llamadas de procedimientos remotos no se consideran parte de una transaccin. Si ejecuta una llamada de procedimientosremotos despus de begin transaction y luego usa rollback transaction , los cambios realizados por la llamada en los datos remotosno se revierten. El diseador del procedimiento almacenado debe asegurarse de que se comprueban todas las condiciones que puedanoriginar una reversin antes de ejecutar una llamada de procedimientos remotos que altere los datos remotos.

Parmetros

Un parmetro es un argumento de un procedimiento almacenado. Es posible declarar uno o ms parmetros de forma opcional en una instruccincreate procedure . El usuario debe suministrar el valor de cada parmetro indicado en una instruccin create procedure al ejecutarse el

procedimiento.

Los nombres de los parmetros deben estar precedidos del smbolo "@" y ajustarse a las reglas para identificadores. Es necesario asignarles untipo de datos del sistema o uno definido por el usuario, y una longitud si es necesario para el tipo de datos. Los nombres de los parmetros sonlocales para el procedimiento que los crea; los mismos nombres de parmetros pueden utilizarse en otros procedimientos. Los nombres de

parmetro, incluido el smbolo "@", pueden tener una longitud mxima de 30 bytes.

A continuacin se muestra un procedimiento almacenado que resulta til en la base de datospubs2

. Dado el apellido y nombre de un autor, elprocedimiento muestra los nombres de los libros escritos por la persona en cuestin, as como el editor de cada libro.

cr eat e pr oc au_i nf o @l ast name varchar( 40) ,@f i r st name varchar( 20) as

sel ect au_l name, au_f name, t i t l e, pub_namef romauthors, t i t l es, publ i shers, t i t l eauthorwhere au_f name = @f i r st nameand au_l name = @l ast nameand aut hors. au_i d = t i tl eauthor. au_i dand t i t l es . t i t l e_ i d = t i t l eauthor . t i t l e_ i dand t i t l es. pub_i d = publ i shers. pub_i d

Ahora ejecute au_info :

au_i nf o Ri nger, Anne

au_l name au_f name t i t l e pub_name

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Ri nger Anne The Gourmet Mi cr owave Bi nnet

& Hardl eyRi nger Anne I s Anger t he Enemy? New Age

Books

( 2 rows af f ected, ret urn stat us = 0)

El siguiente procedimiento almacenado consulta las tablas del sistema. Dado un nombre de tabla como parmetro, el procedimiento muestra elnombre de la tabla, el nombre del ndice y la ID del ndice.

cr eat e pr oc showi nd @t abl e varchar( 30) assel ect t abl e_name = sysobj ects. name,i ndex_name = sysi ndexes. name, i ndex_i d = i ndi df r omsysi ndexes, sysobj ect swher e sysobj ect s. name = @t abl eand sysobj ects. i d = sysi ndexes. i d

Los encabezados de columna, por ejemplo, table_name , se aadieron para facilitar la lectura de los resultados. A continuacin se muestran losformatos de sintaxis aceptables para la ejecucin de este procedimiento almacenado:

execute showi nd ti t l es

Transact-SQL User's Guide (Spanish)

Page 1 of 5Transact-SQL User's Guide (Spanish)

18/04/2010http://manuals.sybase.com/onlinebooks/group-asarc/svs11001/tsqlsp/@Generic__Boo...


2/5

p y g p q p @

exec showi nd t i t l es

execute showi nd @t abl e = t i t l es

execute GATEWAY. pubs2. dbo. showi nd t i t l es

showi nd t i t l es

El ltimo formato de sintaxis, sin exec ni execute , es aceptable siempre que la instruccin sea la nica o la primera de un lote.

A continuacin se muestran los resultados de ejecutar showind en la base de datospubs2 con titles como parmetro:

t abl e_name i ndex_name i ndex_i d- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -t i t l es t i t l ei di nd 1t i t l es t i t l ei nd 2


Note: Si proporciona los parmetros con el formato "@parameter = value ", puede especificarlos en cualquier orden. En casocontrario, debe proporcionar los parmetros en el orden de su instruccin create procedure . Si suministra un valor con el formato"@parameter = value ", todos los parmetros subsiguientes han de suministrarse de este modo.

Parmetros predeterminados

Se puede asignar un valor predeterminado al parmetro de la instruccin create procedure . Este valor, que puede ser cualquier constante, setoma como el argumento del procedimiento si el usuario no proporciona ninguno.

A continuacin se muestra un procedimiento que muestra los nombres de todos los autores que han escrito un libro publicado por el editorintroducido como parmetro. Si no se proporciona ningn nombre de editor, el procedimiento muestra los autores publicados por AlgodataInfosystems.

cr eat e pr oc pub_i nfo@pubname var char( 40) = "Al godata I nf osyst ems" as

sel ect au_l name, au_f name, pub_namef romauthors a, publ i shers p, t i t l es t , t i t l eauthor t awher e @pubname = p. pub_nameand a. au_i d = t a. au_i dand t . t i t l e_ i d = ta. t i t l e_ i dand t . pub_i d = p. pub_i d

Tenga en cuenta que si el valor predeterminado es una cadena de caracteres que contiene espacios en blanco o signos de puntuacin incrustados,es necesario incluirlo entre comillas simples o dobles.

Cuando ejecutapub_info , puede proporcionar cualquier nombre de editor como valor del parmetro. Si no suministra ningn parmetro, SQLServer utiliza el predeterminado, Algodata Infosystems.

exec pub_i nfo

au_l name au_f name pub_name- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Gr een Marj ori e Al godat a I nf osyst emsBennet Abraham Al godata I nf osyst emsO' Lear y Mi chael Al godat a I nf osyst emsMacFeather Stearns Al godat a I nf osyst emsSt r ai ght Di ck Al godata I nfosyst emsCars on Cher yl Al godat a I nfosyst emsDul l Ann Al godat a I nf osyst emsHunt er Sheryl Al godat a I nf osyst emsLocksl ey Chasti t y Al godata I nfosyst ems


En este procedimiento, showind2 , se asigna "titles" como valor predeterminado del parmetro @ table :

cr eat e pr oc showi nd2 @t abl e varchar ( 30) = t i t l esassel ect t abl e_name = sysobj ects. name,

i ndex_name = sysi ndexes. name, i ndex_i d = i ndi df r omsysi ndexes, sysobj ect swher e sysobj ect s. name = @t abl eand sysobj ects. i d = sysi ndexes. i d

Los encabezados de columna, por ejemplo, table_name , clarifican la presentacin de los resultados. A continuacin se indica lo que elprocedimiento muestra para la tabla authors :

showi nd2 aut hors

t abl e_name i ndex_name i ndex_i d

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -aut hor s aui di nd 1authors aunmi nd 2





3/5

p y g p q p @

Si el usuario no proporciona ningn valor, SQL Server utiliza el valor predeterminado, titles .

showi nd2

t abl e_name i ndex_name i ndex_i d- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -t i t l es t i t l ei di nd 1t i t l es t i t l ei nd 2

( 2 r ows af f ected, r eturn stat us =0)

Si se espera un parmetro, pero no se suministra ninguno y no se proporciona ningn valor en la instruccin create procedure , SQL Servermuestra un mensaje de error con los parmetros que espera el procedimiento.

NULL como parmetro predeterminado

El valor predeterminado puede ser el valor NULL. En este caso, si el usuario no suministra ningn parmetro, SQL Server ejecuta elprocedimiento almacenado sin mostrar ningn mensaje de error.

La definicin del procedimiento puede especificar una accin para llevarse a cabo si el usuario no proporciona ningn parmetro verificando si elvalor del parmetro es nulo. A continuacin se muestra un ejemplo:

cr eat e procedure showi nd3 @t abl e varchar( 30) = nul lasi f @t abl e i s nul l

pri nt "Pl ease gi ve a t abl e name"el se

sel ect t abl e_name = sysobj ects. name,i ndex_name = sysi ndexes. name, i ndex_i d = i ndi d

f r omsysi ndexes, sysobj ect swher e sysobj ect s. name = @t abl eand sysobj ects. i d = sysi ndexes. i d

Si el usuario no proporciona ningn parmetro, SQL Server imprime el mensaje del procedimiento en la pantalla.

Para otros ejemplos de definicin del valor predeterminado como NULL, examine el texto de los procedimientos del sistema utilizandosp_helptext .

Caracteres comodn en el parmetro predeterminado

El valor predeterminado puede incluir los caracteres comodn (%, _, [] y [^]) si el procedimiento utiliza el parmetro con la palabra clave like .

Por ejemplo, showindpuede modificarse para mostrar informacin sobre las tablas del sistema si el usuario no proporciona ningn parmetro,

como se muestra a continuacin:

cr eate procedure showi nd4 @t abl e varchar ( 30) ="sys%"assel ect t abl e_name = sysobj ects. name,

i ndex_name = sysi ndexes. name, i ndex_i d = i ndi df r omsysi ndexes, sysobj ect swher e sysobj ects . name l i ke @t abl eand sysobj ects. i d = sysi ndexes. i d

Uso de ms de un parmetro

A continuacin se muestra una variante del procedimiento almacenado au_info que tiene valores predeterminados con caracteres comodn paraambos parmetros:

cr eat e pr oc au_i nf o2 @l ast name var char ( 30) = "D%",@f i r st name var char ( 18) = "%" as

sel ect au_l name, au_f name, t i t l e, pub_name

f romauthors, t i t l es, publ i shers, t i t l eauthorwher e au_f name l i ke @f i r st nameand au_l name l i ke @l ast nameand aut hors. au_i d = t i tl eauthor. au_i dand t i t l es . t i t l e_ i d = t i t l eauthor . t i t l e_ i dand t i t l es. pub_i d = publ i shers. pub_i d

Si au_info2 se ejecuta sin parmetros, se muestran todos los autores cuyos apellidos comienzan por "D":

au_i nfo2

au_l name au_f name t i t l e pub_name- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Dul l Ann Secrets of Si l i con Val l ey Al godata I nfosyst emsDeFr ance Mi chel The Gourmet Mi cr owave Bi nnet & Har dl ey

( 2 r ows aff ected)

Si hay valores predeterminados disponibles para parmetros, stos pueden omitirse en la ejecucin, comenzando por el ltimo parmetro. Nopuede saltarse un parmetro a menos que NULL sea su valor predeterminado suministrado.

Note: Si proporciona los parmetros en el formato " @parameter = value ", puede suministrarlos en cualquier orden. Tambin puede




4/5

p y g p q p @

omitir un parmetro para el que se ha suministrado un valor predeterminado.Si proporciona un valor en el formato " @parameter = value ", todos los parmetros subsiguientes tambin debern suministrarse deeste modo.

Como ejemplo de omisin del segundo parmetro cuando se han definido valores predeterminados para dos parmetros, puede buscar los libros yeditores de todos los autores cuyo apellido es "Ringer", de la siguiente manera:

au_i nfo2 Ri nger

au_l name au_f name t i t l e Pub_name

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Ri nger Anne The Gourmet Mi cr owave Bi nnet & Har dl eyRi nger Anne I s Anger t he Enemy? New Age BooksRi nger Al bert I s Anger t he Enemy? New Age BooksRi nger Al ber t Li f e Wi t hout Fear New Age Books

( 4 r ows aff ected)

Grupos de procedimientos

El punto y coma (;) y el nmero entero opcionales despus del nombre del procedimiento en las instrucciones create procedure y executepermiten agrupar los procedimientos que tienen el mismo nombre para que puedan omitirse juntos mediante un solo comando drop procedure .

Los procedimientos utilizados en la misma aplicacin suelen agruparse de este modo. Por ejemplo, podra crear una serie de procedimientosllamados orders;1 , orders;2 , etc.. La siguiente instruccin omitira el grupo completo:

drop pr oc or der s

Una vez agrupados los procedimientos mediante el anexo de un punto y coma (;) y un nmero a sus nombres, no es posible omitirlos de formaindividual. Por ejemplo, no se permite la siguiente instruccin:

drop proc or der s; 2

with recompile encreate procedure

En la instruccin create procedure , la clusula opcional with recompile aparece justo antes de las instrucciones SQL. Esto indica a SQL Serverque no guarde ningn plan para este procedimiento. Cada vez que se ejecuta el procedimiento se crea un plan nuevo.

Si no se usa with recompile , SQL Server almacena el plan de ejecucin que crea. Generalmente, este plan de ejecucin es correcto.

Sin embargo, es posible que un cambio en los datos o un cambio en los valores de parmetro suministrados para las ejecuciones subsiguienteshaga que SQL Server proponga un plan de ejecucin distinto del que cre la primera vez que se ejecut el procedimiento. En estas situaciones,SQL Server necesita un plan de ejecucin nuevo.

Use with recompile en una instruccin create procedure cuando crea que necesita un plan nuevo. Consulte elManual de Referencia de SQLServerpara obtener ms informacin.

with recompile en execute

En la instruccin execute , la clusula opcional with recompile aparece despus de cualquier parmetro. Esto indica a SQL Server que compile unplan nuevo. El plan nuevo se utiliza para ejecuciones subsiguientes.

Use with recompile cuando ejecute un procedimiento si los datos han sufrido un gran cambio o si el parmetro que proporciona es atpico, esdecir, si tiene alguna razn para creer que el plan almacenado con el procedimiento podra no ser ptimo para la ejecucin de ste.

Note: Si utiliza select * en la instruccin create procedure , el procedimiento, aunque use la opcin with recompile de execute , notoma ninguna columna nueva aadida a la tabla. Es necesario omitir el procedimiento y volver a crearlo.

Anidacin de procedimientos dentro de procedimientos

La anidacin tiene lugar cuando un procedimiento almacenado o un disparador llama a otro. El nivel de anidacin se incrementa cuando elprocedimiento o disparador llamado inicia la ejecucin y disminuye cuando el procedimiento o disparador llamado finaliza la ejecucin. Si sesupera el mximo de 16 niveles de anidacin, el procedimiento no se ejecuta correctamente. El nivel de anidacin actual se almacena en lavariable global @@nestlevel .

Uso de tablas temporales en procedimientos almacenados

Es posible crear y utilizar tablas temporales en un procedimiento almacenado, pero la tabla temporal slo existe mientras dura el procedimientoalmacenado que la crea. Cuando el procedimiento finaliza, SQL Server omite la tabla temporal de forma automtica. Un solo procedimiento

puede:

Crear una tabla temporal

Insertar, actualizar o eliminar datos

Ejecutar consultas en la tabla temporal

Llamar a otros procedimientos que hacen referencia a la tabla temporal




5/5

p y g p q p @

Dado que la tabla temporal tiene que existir para poder crear procedimientos que hagan referencia a ella, a continuacin se indican los pasos quedebe seguir:

1. Cree la tabla temporal que precisa con una instruccin create table o una select into . Por ejemplo:

cr eat e t abl e #t empstor es( st or_ i d char ( 4), amount money)

2. Cree los procedimientos que accedan a la tabla temporal (pero no el que la genera).

cr eate procedur e i nv_amount sassel ect st or_ i d, "Tot al Due" =sum( amount)f r om#t empstor esgroup by st or_i d

3. Omita la tabla temporal:

drop t abl e #t empst ores

4. Cree el procedimiento que genera la tabla y llama a los procedimientos creados en el paso 2:

cr eat e procedure i nv_procascr eat e t abl e #t empstor es( st or_ i d char ( 4), amount money)

i nser t #t empstor essel ect st or_i d, sum( qt y*(100- di scount ) / 100*pr i ce)f r omsal esdetai l , t i t l eswhere sal esdetai l . t i t l e_ i d = t i t l es . t i t l e_ i dgroup by stor_ i d, sal esdetai l . t i t l e_ i d

exec i nv_amount s

Tambin puede crear tablas temporales sin el prefijo #, utilizando create table tempdb..tablename... desde dentro de un procedimientoalmacenado. Estas tablas no desaparecen cuando finaliza el procedimiento, de modo que es posible hacer referencia a ellas mediante

procedimientos independientes. Siga los pasos descritos anteriormente para crear estas tablas.

Ejecucin de procedimientos de forma remota

Es posible ejecutar procedimientos en otro SQL Server desde el SQL Server local. Una vez configurados ambos servidores de forma adecuada,

puede ejecutar cualquier procedimiento en el SQL Server remoto con slo usar el nombre del servidor como parte del identificador. Por ejemplo,para ejecutar un procedimiento llamado remoteproc en un servidor denominado GATEWAY:

exec gateway. r emot edb. dbo. r emot epr oc

Consulte la Gua de Administracin del Sistemapara obtener informacin sobre cmo configurar los SQL Server local y remoto para la ejecucinremota de procedimientos. Es posible pasar uno o ms valores como parmetros a un procedimiento remoto desde el lote o el procedimiento quecontiene la instruccin executepara el procedimiento remoto. Los resultados del SQL Server remoto aparecen en el terminal local.

El estado de retorno de los procedimientos, descritos en las secciones siguientes, puede utilizarse para capturar y transmitir mensajes deinformacin sobre el estado de ejecucin de los procedimientos.

Warning! Las llamadas de procedimientos remotos no se consideran parte de una transaccin. En consecuencia, si ejecuta unallamada de procedimientos remotos como parte de una transaccin y luego revierte la transaccin, los cambios realizados por lallamada en un SQL Server remoto no se revierten.


18/04/2010http://manuals.sybase.com/onlinebooks/group-asarc/svs11001/tsqlsp/@Generic Boo...

2. Creación y Ejecución de Procedimientos Almacenados.pdf

Documents

Transcript of 2. Creación y Ejecución de Procedimientos Almacenados.pdf