- no $uede incluir las cl+usulas Eco&$uteE ni Eco&$ute byE ni la $alabra clave EintoED - no se $ueden crear vistas te&$orales ni crear vistas sobre tablas te&$orales. - no se $ueden asociar re)las ni valores $or defecto a las vistas. - no $uede co&binarse con otras instrucciones en un &is&o lote. Se $ueden construir vistas sobre otras vistas.
1# - istas (informaci?n) Las vistas son objetos as6 que $ara obtener infor&aci:n de ellos $ueden usarse los si)uientes $rocedi&ientos al&acenados del siste&aC Es$%0el$E sin $ar+&etros nos &uestra todos los objetos de la base de datos seleccionada incluidas las vistas. ?n la colu&na EObject%ty$eE a$arece Evie/E si es una vista. Si le envia&os co&o ar)u&ento el no&bre de una vista obtene&os la fec0a de creaci:n $ro$ietario los ca&$os y de&+s infor&aci:n. Es$%0el$te8tE se)uido del no&bre de una vista nos &uestra el te8to que la define e8ce$to si 0a sido encri$tado. ?jecutando Es$%de$endsE se)uido del no&bre de un objeto obtene&os ! resultadosC - no&bre ti$o ca&$os etc. de los objetos de los cuales de$ende el objeto no&brado y
- no&bre y ti$o de los objetos que de$enden del objeto no&brado. Si ejecuta&os el $rocedi&iento Es$%de$endsE se)uido del no&bre de una vistaC sp_depends )sta_epleados;
a$arecen las tablas #y de&+s objetos' de las cuales de$ende la vista es decir las tablas referenciadas en la &is&a. Si ejecuta&os el $rocedi&iento se)uido del no&bre de una tablaC sp_depends epleados;
a$arecen los objetos que de$enden de la tabla vistas restricciones etc. Ta&bi9n se $uede consultar la tabla del siste&a EsysobjectsEC select 51ro s4sob2ects;
@os &uestra no&bre y varios datos de todos los objetos de la base de datos actual. La colu&na E8ty$eE indica el ti$o de objeto si es una vista a$arece H5H. Si quere&os ver todas las vistas creadas $or nosotros $ode&os ti$earC select 51ro s4sob2ects w*ere Ft4pe='3' and>> tpo )sta nae lDe ')staQ';>>bYs&eda con coodn
1' - vistas (encriptar) Bode&os ver el te8to que define una vista ejecutando el $rocedi&iento al&acenado del siste&a Es$%0el$te8tE se)uido del no&bre de la vistaC sp_*elpteFt NOMBRE3!TA;
Bode&os ocultar el te8to que define una vista e&$leando la si)uiente sinta8is al crearlaC create )ew NOMBRE3!TA wt* encr4pton as ENTENC!AELECT 1ro TABLA;
E/it0 encry$tionE indica a S7L Server que codifique las sentencias que definen la vista. "rea&os una vista con su definici:n ocultaC create )ew )sta_epleados wt* encr4pton as
select (apelldo' 'e$nobre% as nobre#seFo# s$nobre as seccon# cantdad*2os 1ro epleados as e 2on seccones as s on cod7o=seccon
Si ejecuta&os el $rocedi&iento al&acenado del siste&a Es$%0el$te8tE se)uido del no&bre de una vista encri$tada a$arece un &ensaje indicando tal situaci:n y el te8to no se &uestra.
1 - istas (eliminar) Bara quitar una vista se e&$lea Edro$ vie/EC drop )ew NOMBRE3!TA;
Si se eli&ina una tabla a la que 0ace referencia una vista la vista no se eli&ina 0ay que eli&inarla e8$l6cita&ente. Solo el $ro$ietario $uede eli&inar una vista. Antes de eli&inar un objeto se reco&ienda ejecutar el $rocedi&iento al&acenado de siste&a Es$%de$endsE $ara averi)uar si 0ay objetos que 0a)an referencia a 9l. ?li&ina&os la vista deno&inada Evista%e&$leadosEC drop )ew )sta_epleados;
1+ - istas (*it& c&ecA option) ?s $osible obli)ar a todas las instrucciones de &odificaci:n de datos que se ejecutan en una vista a cu&$lir ciertos criterios. Bor eje&$lo crea&os la si)uiente vistaC create )ew )sta_epleados as select apelldo# e$nobre# seFo# s$nobre as seccon 1ro epleados as e 2on seccones as s on seccon=cod7o w*ere s$nobre='Adnstracon' wt* c*ecD opton;
La vista definida anterior&ente &uestra sola&ente al)unos de los datos de los e&$leados de la secci:n EAd&inistracionE. Ade&+s sola&ente se $er&iten &odificaciones a los e&$leados de esa secci:n. Bode&os actuali3ar el no&bre a$ellido y se8o a trav9s de la vista $ero no el ca&$o EseccionE $orque est+ restrin)uido.
1, - istas (modificar datos de una tabla a trav>s de vistas) Si se &odifican los datos de una vista se &odifica la tabla base.
Se $uede insertar actuali3ar o eli&inar datos de una tabla a trav9s de una vista teniendo en cuenta lo si)uiente las &odificaciones que se reali3an a las vistasC - no $ueden afectar a &+s de una tabla consultada. Bueden &odificarse datos de una vista que co&bina varias tablas $ero la &odificaci:n sola&ente debe afectar a una sola tabla. - no se $ueden ca&biar los ca&$os resultado de un c+lculo. - $ueden )enerar errores si afectan a ca&$os a las que la vista no 0ace referencia. Bor eje&$lo si se in)resa un re)istro en una vista que consulta una tabla que tiene ca&$os not null que no est+n incluidos en la vista. - la o$ci:n E/it0 c0ec= o$tionE obli)a a todas las instrucciones de &odificaci:n que se ejecutan en la vista a cu&$lir ciertos criterios que se es$ecifican al definir la vista. - $ara eli&inar datos de una vista sola&ente U@A tabla $uede ser listada en el Efro&E de la definicion de la &is&a.
1. - istas modificar (alter vie*) Bara &odificar una vista $uede eli&inarla y volver a crearla o e&$lear Ealter vie/E. "on Ealter vie/E se &odifica la definici:n de una vista sin afectar los $rocedi&ientos al&acenados y los $er&isos. Si eli&ina una vista y vuelve a crearla debe reasi)nar los $er&isos asociados a ella. Sinta8is b+sica $ara alterar una vistaC alter )ew NOMBRE3!TA wt* encr4pton>>opconal as ELECT
?n el eje&$lo si)uiente se altera vista%e&$leados $ara a)re)ar el ca&$o Edo&icilioEC alter )ew )sta_epleados wt* encr4pton as select (apelldo' 'e$nobre% as nobre#seFo# s$nobre as seccon# cantdad*2os#doclo 1ro epleados as e 2on seccones as s on cod7o=seccon
Si cre: la vista con E/it0 encry$tionE y quiere &odificarla &anteniendo la encri$taci:n debe colocarla nueva&ente en caso de no 0acerlo desa$arece.
Si crea una vista con Eselect E y lue)o a)re)a ca&$os a la estructura de las tablas involucradas los nuevos ca&$os no a$arecer+n en la vistaD esto es $orque los ca&$os se seleccionan al ejecutar Ecreate vie/ED debe alterar la vista.
11 - 5en"uaje de control de flujo (case) La funci:n EcaseE co&$ara ! o &+s valores y devuelve un resultado. La sinta8is es la si)uienteC case 3ALORACOMPARAR w*en 3ALOR t*en RE-LTA"O w*en 3ALOR t*en RE-LTA"O $$$ else RE-LTA"O+ end
Bor cada valor 0ay un E/0enE y un Et0enED si encuentra un valor coincidente en al)
Un $rofesor )uarda las notas de sus alu&nos de un curso en una tabla lla&ada Ealu&nosE que consta de los si)uientes ca&$osC > nobre (+, caracteres%# > nota ()alor entero entre , 4 ,# p&ede ser n&lo%$
7uere&os &ostrar los no&bres notas de los alu&nos y en una colu&na e8tra lla&ada EresultadoE e&$lea&os un case que testee la nota y &uestre un &ensaje diferente si en dic0o ca&$o 0ay un valorC > ,# # +J 'lbre'; > # J 're7&lar'; > I# G# K ,J 'prooconado';
?sta es la sentenciaC select nobre#nota# res<ado= case nota w*en , t*en 'lbre' w*en t*en 'lbre' w*en t*en 'lbre' w*en + t*en 'lbre' w*en t*en 're7&lar' w*en t*en 're7&lar' w*en t*en 're7&lar' w*en I t*en 'prooconado' w*en G t*en 'prooconado' w*en K t*en 'prooconado' w*en , t*en 'prooconado' end 1ro al&nos;
@ote que cada E/0ereE co&$ara un valor $untual $or ello los valores devueltos son i)uales $ara al)unos casos. @ote que co&o o&iti&os la $arte EelseE en caso que el valor no encuentre coincidencia con nin)uno valor E/0enE retorna EnullE. Bode&os reali3ar co&$araciones en cada E/0ereE. La sinta8is es la si)uienteC case w*en 3ALORACOMPARAR OPERA"OR 3ALOR t*en RE-LTA"O w*en 3ALORACOMPARAR OPERA"OR 3ALOR t*en RE-LTA"O $$$ else RE-LTA"O+ end
Mostra&os los no&bres de los alu&nos y en una colu&na e8tra lla&ada EresultadoE e&$lea&os un case que teste si la nota es &enor a est+ entre y , o su$era el ,C select nobre# nota# condcon= case w*en nota9 t*en 'lbre' w*en nota := and nota9I t*en 're7&lar' w*en nota:=I t*en 'prooconado' else 'sn nota' end 1ro al&nos;
Buede utili3ar una e8$resi:n EcaseE en cualquier lu)ar en el que $ueda utili3ar una e8$resi:n. Ta&bi9n se $uede e&$lear con E)rou$ byE y funciones de a)ru$a&iento.
111 - 5en"uaje de control de flujo (if) ?8isten $alabras es$eciales que $ertenecen al len)uaje de control de flujo que controlan la ejecuci:n de las sentencias los bloques de sentencias y $rocedi&ientos al&acenados. Tales $alabras sonC be)in... end )oto if... else return /aitfor /0ile brea= y continue. - Ebe)in... endE encierran un bloque de sentencias $ara que sean tratados co&o unidad. - Eif... elseEC testean una condici:nD se e&$lean cuando un bloque de sentencias debe ser ejecutado si una condici:n se cu&$le y si no se cu&$le se debe ejecutar otro bloque de sentencias diferente. - E/0ileEC ejecuta re$etida&ente una instrucci:n sie&$re que la condici:n sea verdadera. - Ebrea=E y EcontinueEC controlan la o$eraci:n de las instrucciones incluidas en el bucle E/0ileE. 5ea&os un eje&$lo. Tene&os nuestra tabla ElibrosED quere&os &ostrar todos los t6tulos de los cuales no 0ay libros dis$onibles #cantidadG4' si no 0ay &ostrar un &ensaje indicando tal situaci:nC 1 eFsts (select 51ro lbros w*ere cantdad=,% (select tt&lo 1ro lbros w*ere cantdad=,% else select 'No *a4 lbros sn stocD';
S7L Server ejecuta la sentencia #en este caso una subconsulta' lue)o del EifE si la condici:n es verdaderaD si es falsa ejecuta la sentencia del EelseE #si e8iste'. Bode&os e&$lear Eif...elseE en actuali3aciones. Bor eje&$lo quere&os 0acer un descuento en el $recio del 14 a todos los libros de una deter&inada editorialD si no 0ay &ostrar un &ensajeC 1 eFsts (select 51ro lbros w*ere edtoral='Eece'% be7n &pdate lbros set preco=preco>(preco5,$% w*ere edtoral='Eece' select 'lbros act&alados' end else select 'no *a4 re7stros act&alados';
@ote que si la condici:n es verdadera se deben ejecutar ! sentencias. Bor lo tanto se deben encerrar en un bloque Ebe)in...endE. ?n el si)uiente eje&$lo eli&ina&os los libros cuya cantidad es ceroD si no 0ay &ostra&os un &ensajeC 1 eFsts (select 51ro lbros w*ere cantdad=,% delete 1ro lbros w*ere cantdad=, else select 'No *a4 re7stros elnados;
112 - ariables de usuario Las variables nos $er&iten al&acenar un valor y recu$erarlo &+s adelante $ara e&$learlos en otras sentencias. Las variables de usuario son es$ec6ficas de cada cone8i:n y son liberadas auto&+tica&ente al abandonar la cone8i:n. Las variables de usuario co&ien3an con EFE #arroba' se)uido del no&bre #sin es$acios' dic0o no&bre $uede contener cualquier caracter. Una variable debe ser declarada antes de usarse. Una variable local se declara as6C declare @NOMBRE3AR!ABLE T!PO
colocando EdeclareE el no&bre de la variable que co&ien3a con el s6&bolo arroba #F' y el ti$o de dato. ?je&$loC declare @nobre )arc*ar(,%
Buede declarar varias variables en una &is&a sentenciaC declare @nobre )arc*ar(,%# @edad nt
@o e8isten variables )lobales en S7L Server. Una variable declarada e8iste dentro del entorno en que se declaraD debe&os declarar y e&$lear la variable en el &is&o lote de sentencias $orque si declara&os una variable y lue)o en otro bloque de sentencias $retende&os e&$learla dic0a variable ya no e8iste. Bor eje&$lo si ejecuta&os estas sentencias en diferentes lotesC declare Fvariable varc0ar#14'D select FvariableD
a$arece un &ensaje indicando que la variable EFvariableE debe ser declarada. >ebe&os ti$earC declare @)arable )arc*ar(,% select @)arable;
>is$one&os $unto y co&a solo al final de la
Bara al&acenar un valor en una variable se coloca el si)no i)ual #G' entre la variable y el valor a asi)nar. Si le asi)na&os un valor resultado de una consulta la sinta8is esC select @nobre = a&tor 1ro lbros w*ere tt&lo='-no'
Bode&os ver el contenido de una variable conC select @nobre;
Una variable $uede tener co&odinesC declare @patron )arc*ar(+,% set @patron='BQ' select a&tor 1ro lbros w*ere a&tor lDe @patron;
La utilidad de las variables consiste en que al&acenan valores $ara utili3arlos en otras consultas. Bor eje&$lo quere&os saber todos los datos del libro con &ayor $recio de la tabla ElibrosE de una librer6a. Bara ello $ode&os e&$lear una variable $ara al&acenar el $recio &+s altoC declare @a4orpreco select @a4orprecoJ=aF(preco% 1ro lbros
y lue)o &ostrar todos los datos de dic0o libro e&$leando la variable anteriorC select 51ro lbros w*ere preco=@a4orpreco;
?s decir declara&os la variable y )uarda&os en ella el $recio &+s alto y lue)o en otra sentencia &ostra&os los datos de todos los libros cuyo $recio es i)ual al valor de la variable. Una variable $uede ser definida con cualquier ti$o de dato e8ce$to te8t nte8t e i&a)eD incluso de un ti$o de dato definido $or el usuario.
11 - $ipos de datos te=t nte=t y ima"e Los ti$os de datos Ente8tE Ete8tE e Ei&a)eE re$resentan ti$os de datos de lon)itud fija y variable en los que se $ueden )uardar )ran cantidad de infor&aci:n caracteres unicode y no unicode y datos binarios. Ente8tE al&acena datos unicode de lon)itud variable y el &+8i&o es de a$ro8i&ada&ente 1444444444 caracteres en bytes el ta&ao es el doble de los caracteres in)resados #! 2'. Ete8tE al&acena datos binarios no unicode de lon)itud variable el &+8i&o es de !444444444 caracteres a$ro8. #! 2'. @o $uede e&$learse en $ar+&etros de $rocedi&ientos al&acenados. Ei&a)eE es un ti$o de dato de lon)itud variable que $uede contener de 4 a !444444444 bytes #! 2' a$ro8. de datos binarios. Se e&$lea $ara al&acenar )ran cantidad de infor&aci:n o )r+ficos. Se e&$lean estos ti$os de datos $ara al&acenar valores su$eriores a 444 caracteres. @in)uno de estos ti$os de datos ad&iten ar)u&ento $ara es$ecificar su lon)itud co&o en el caso de los ti$os Ec0arE o Evarc0arE. "o&o estos ti$os de datos tiene )ran ta&ao S7L Server los al&acena fuera de los re)istros en su lu)ar )uarda un $untero #de 1* bytes' que a$unta a otro sitio que contiene los datos. Bara declarar un ca&$o de al)uno de estos ti$os de datos coloca&os el no&bre del ca&$o se)uido del ti$o de datoC $$$ NOMBRECAMPO teFt $$$$
Otras consideraciones i&$ortantesC - @o $ueden definirse variables de estos ti$os de datos. - Los ca&$os de estos ti$os de datos no $ueden e&$learse $ara 6ndices. - La
11# - $ipo de dato te=t - nte=t e ima"e (punteros) ?8$lica&os anterior&ente que co&o estos ti$os de datos tiene )ran ta&ao S7L Server al&acena los datos fuera de los re)istrosD en el re)istro )uarda un $untero #de 1* bytes' que a$unta a otro sitio que contiene la direcci:n en la cual se )uardan los datos $ro$ia&ente dic0os. La funci:n Ete8t$trE devuelve el valor del $untero a te8to que corres$onde al ca&$o te8t nte8t o i&a)eD tal valor $uede e&$learse $ara &ani$ular los datos de este ti$o con las funciones $ara leer escribir y actuali3ar. Sinta8isC teFtptr(CAMPO%;
?l ca&$o debe ser ti$o te8t nte8t o i&a)e. ?n el ca&$o de ti$o Ete8tE no se al&acenan los datos sino la direcci:n en la cual se encuentran los datos. Bode&os ver esa direcci:n ti$eando la si)uiente sentenciaC select tt&lo# teFtptr(snopss% 1ro lbros;
La funci:n Ete8t$trE retorna un $untero a te8to #valor binario de 1*'. Si el ca&$o no tiene te8to retorna un $untero a nullD $or ello se debe usar la funci:n Ete8tvalidE $ara confir&ar si el $untero a te8to e8iste. Si la consulta retorna &+s de un re)istro Ete8t$trE retorna un $untero a te8to del
Los ar)u&entos sonC el no&bre de la tabla y ca&$o y el no&bre del $untero a te8to que se va a controlar. Retorna 1 si el $untero es v+lido y 4 si no lo es. @o se $uede e&$lear Eu$datete8tE E/ritete8tE y Ereadte8tE si el $untero no es v+lido. La si)uiente consulta &uestra si los $unteros son v+lidos en cada re)istro del ca&$o Esino$sisE de la tabla ElibrosEC select tt&lo#
teFt)ald('lbros$snopss'# teFtptr(snopss%% as 'P&ntero )aldo' 1ro lbros;
?n el si)uiente eje&$lo declara&os una variable de ti$o EvarbinaryE a la cual le asi)na&os el valor del $untero a te8to de un re)istro y lue)o ve&os si dic0o $untero es v+lido e&$leando la variableC declare @p&ntero )arbnar4(% select @p&ntero = teFtptr(snopss% 1ro lbros w*ere tt&lo= '!l&sones' select teFt)ald('lbros$snopss'# @p&ntero%;
Solo dis$one&os $unto y co&a al final $ara que S7L Server ejecute todas las instrucciones en un solo lote y e8ista la variable F$untero. Si al insertar re)istros se in)resa un valor EnullE en un ca&$o Ete8tE Ente8tE o Ei&a)eE o no se in)resa valor no se crea un $untero v+lido. Bara crear un $untero a te8to v+lido ejecute un EinsertE o Eu$dateE con datos que no sean nulos $ara el ca&$o te8t nte8t o i&a)e.
11' - $ipo de dato te=t - nte=t e ima"e (leer) La funci:n Ereadte8tE lee valores de un ca&$o te8t nte8t o i&a)e co&en3ando desde una $osici:n y leyendo un es$ec6fico n<&ero de bytes. Sinta8isC readteFt TABLA$CAMPO P-NTEROATETO "EPLAWAM!ENTO CANT!"A";
Analice&os la sinta8isC - BU@T?ROAT?PTOC $untero a te8to v+lido binary#1*'. - >?SBLA]AMI?@TOC n<&ero de bytes #$ara te8t o i&a)e' o caracteres #nte8t' que se &ueve el $untero antes de co&en3ar a leer. - "A@TI>A>C n<&ero de bytes o caracteres a leer desde la $osici:n indicada $or >?SBLA]AMI?@TO. Si es 4 se leen Z2 bytes o 0asta el final. Lee&os la infor&aci:n al&acenada en el ca&$o Esino$sisE de ElibrosE del re)istro con c:di)o ! desde la $osici:n 4 caracteresC declare @p&ntero )arbnar4(%
select @p&ntero=teFtptr(snopss% 1ro lbros w*ere cod7o= readteFt lbros$snopss @p&ntero K ,;
Si al insertar re)istros se in)resa un valor EnullE en un ca&$o Ete8tE Ente8tE o Ei&a)eE o no se in)resan datos no se crea un $untero v+lido y al intentar leer dic0o ca&$o ocurre un error $orque la funci:n Ereadte8tE requiere un $untero v+lido. Bara evitarlo $ode&os c0equear el $untero antes de $as+rselo a la funci:n de lecturaC declare @p&ntero )arbnar4(% select @p&ntero=teFtptr(snopss% 1ro lbros w*ere cod7o= 1 (teFt)ald('lbros$snopss'# @p&ntero%=% readteFt lbros$snopss @p&ntero K , else select 'p&ntero n)aldo';
11 - $ipo de dato te=t - nte=t e ima"e (escribir) La funci:n E/ritete8tE sobreescribe #ree&$la3a' el te8to de un ca&$o Ete8tE Ente8tE o Ei&a)eE. @o $uede e&$learse en vistas. Sinta8isC wrteteFt TABLA$CAMPO P-NTEROATETO "ATO;
Lue)o de E/ritete8tE se coloca el no&bre de la tabla y el ca&$o #te8t nte8t o i&a)e' a actuali3ar. EBU@T?ROAT?PTOE es el valor que al&acena el $untero a te8to del dato de ti$o Ete8tE Ente8tE o Ei&a)eE tal $untero debe ser v+lido. E>ATOE es el te8to que al&acena $uede ser una variable o un literal. ?ste eje&$lo coloca el $untero a te8to en una variable EF$unteroE y lue)o E/ritete8tE al&acena el nuevo te8to en el re)istro a$untado $or EF$unteroEC declare @p&ntero bnar4(%
select @p&ntero = teFtptr (snopss% 1ro lbros w*ere cod7o= wrteteFt lbros$snopss @p&ntero 'Este es &n n&e)o lbro acerca de P0P escrto por el pro1esor Molna &e aborda todos los teas necesaros para el aprenda2e desde cero de este len7&a2e$';
Recuerde que si al insertar re)istros se in)resa un valor EnullE en un ca&$o Ete8tE Ente8tE o Ei&a)eE o no se in)resan datos no se crea un $untero v+lido y al intentar escribir dic0o ca&$o ocurre un error $orque la funci:n E/ritete8tE requiere un $untero v+lido. Bara evitarlo $ode&os c0equer el $untero antes de $as+rselo a la funci:n de escrituraC declare @p&ntero )arbnar4(% select @p&ntero=teFtptr(snopss% 1ro lbros w*ere cod7o= 1 (teFt)ald('lbros$snopss'# @p&ntero%=% wrteteFt lbros$snopss @p&ntero 'Trata de &na 7a)ota &e )&ela Vs alto &e las deas$' else select 'p&ntero n)aldo# no se act&al el re7stro';
11+ - $ipo de dato te=t - nte=t e ima"e (actuali0ar) A$rendi&os que la funci:n E/ritete8tE sobreescribe ree&$la3a el contenido co&$leto de un ca&$o de ti$o Ete8tE Ente8tE o Ei&a)eE. Bara actuali3ar ca&$os de estos ti$os ta&bi9n e&$lea&os Eu$datete8tE que $er&ite ca&biar una $orci:n del ca&$o #o todo el ca&$o'. La sinta8is b+sica es la si)uienteC &pdateteFt TABLA$CAMPO P-NTEROATETO "EPLAWAM!ENTO"ELP-NTERO LON/!T-""EBORRA"O "ATOA!NERTAR;
Anali3a&os la sinta8isC - TA2LA."AMBOC ca&$o y tabla que se va a actuali3ar. - BU@T?ROAT?PTOC valor del $untero retornado $or la funci:n Ete8t$trE que a$unta al dato te8t nte8t o i&a)e que se quiere actuali3ar. - >?SBLA]AMI?@TO>?LBU@T?ROC indica la $osici:n en que inserta el nuevo dato. ?s$ecifica la cantidad de bytes #$ara ca&$os te8t e i&a)e' o caracteres #$ara ca&$os nte8t' que debe &overse el $untero $ara insertar el dato. Los valores $ueden serC 4 #el nuevo dato se inserta al co&ien3o' EnullE #coloca el $untero al final' un valor &ayor a cero y &enor o i)ual a la lon)itud total del te8to #inserta el nuevo dato en la $osici:n indicada' y un valor &ayor a la lon)itud total del ca&$o #)enera un &ensaje de error'. ?s i&$ortante recordar que cada caracter nte8t ocu$a ! bytes. - LO@ITU>>?2ORRA>OC indica la cantidad de bytes #$ara te8t e i&a)e' o caracteres #$ara nte8t' a borrar co&en3ando de la $osici:n indicada $or el $ar+&etro >?SBLA]AMI?@TO>?LBU@T?RO. Si coloca&os el valor 4 #no se borra nin)
?SBLA]AMI?@TO>?LBU@T?RO 0asta el final' un valor &ayor que cero y &enor o i)ual a la lon)itud del te8to #borra tal cantidad' y un valor inv+lido es decir &ayor a la lon)itud del te8to #)enera un &ensaje de error'. ?s i&$ortante recordar que cada caracter Ente8tE ocu$a ! bytes. - >ATOAI@S?RTARC el dato que va a ser insertado en el ca&$o. Buede ser c0ar nc0ar varc0ar nvarc0ar binary varbinary te8t nte8t i&a)e un literal o una variable. Si el dato es un ca&$o te8t nte8t o i&a)e de otra tabla se debe indicar el no&bre de la tabla junto con el ca&$o y el valor del $untero que a$unta al ti$o de dato te8t nte8t o i&a)e #retornado $or la funci:n Ete8t$trE' de esta for&aC
TABLA$CAMPO P-NTERO;
Tene&os la tabla libros con un ca&$o de ti$o te8t lla&ado Esino$sisED 0ay un re)istro car)ado con el si)uiente te8toC EBara a$render BJB a $aso.E @ecesita&os a)re)ar antes de Ea $asoE el te8to E$aso E $ara que el te8to co&$leto sea EBara a$render BJB $aso a $asoE ti$ea&osC declare @p&ntero bnar4(% select @p&ntero = teFtptr(snopss% 1ro lbros w*ere tt&lo='Aprenda P0P' &pdateteFt lbros$snopss @p&ntero G , 'paso ';
?ntonces declara&os una variable lla&ada EF$unteroED )uarda&os en la variable el valor del $untero obtenido con la funci:n Ete8t$tr#sino$sis'E tal $untero a$unta al ca&$o Esino$sisE del libro EA$renda BJBE. Lue)o actuali3a&os el ca&$o colocando el $untero en la $osici:n 1 no borra&os nin)
?ntonces se e&$lea Eu$datete8tE $ara &odificar datos de ca&$os de ti$o te8t nte8t e i&a)e $udiendo ca&biar una $orci:n del te8to.
11, - $ipo de dato te=t - nte=t e ima"e (funciones) Las si)uientes son otras funciones que $ueden e&$learse con estos ti$os de datosC - datalen)0t#"AMBO'C devuelve el n<&ero de bytes de un deter&inado ca&$o. Retorna EnullE si el ca&$o es nulo. ?je&$loC select tt&lo# datalen7t*(snopss% as lon7t&d 1ro lbros order b4 tt&lo;
- $atinde8 #HBATRO@H"AMBO'C retorna el co&ien3o de la $ri&era ocurrencia de un $atr:n de la e8$resi:n es$ecificada si el $atr:n no se encuentra retorna cero. ?l $atr:n es una cadena que $uede incluir co&odines. ?je&$loC select patndeF('QP0PQ'# snopss% 1ro lbros;
"on este ti$o de datos ta&bi9n $uede utili3arse Eli=eE $ero Eli=eE sola&ente $uede incluirse en la cl+usula E/0ereE. - substrin) #T?PTOI@I"IOLO@ITU>'C devuelve una $arte del te8to es$ecificado co&o $ri&er ar)u&ento e&$e3ando desde la $osici:n es$ecificada $or el se)undo ar)u&ento y de tantos caracteres de lon)itud co&o indica el tercer ar)u&ento. ?je&$loC select tt&lo#s&bstrn7(snopss##,% 1ro lbros;
11. - Drocedimientos almacenados 5i&os que S7L Server ofrece dos alternativas $ara ase)urar la inte)ridad de datos la inte)ridadC 1' >?"LARATI5A &ediante el uso de restricciones #constraints' valores $redeter&inados #defaults' y re)las #rules' y !' BRO"?>IM?@TAL &ediante la i&$le&entaci:n de $rocedi&ientos al&acenados y desencadenadores #tri))ers'. @os detendre&os a0ora en $rocedi&ientos al&acenados. Un $rocedi&iento al&acenado es un conjunto de instrucciones a las que se les da un no&bre que se al&acena en el servidor. Ber&iten enca$sular tareas re$etitivas. S7L Server $er&ite los si)uientes ti$os de $rocedi&ientos al&acenadosC 1' del siste&aC est+n al&acenados en la base de datos E&asterE y llevan el $refijo Es$%ED $er&iten recu$erar infor&aci:n de las tablas del siste&a y $ueden ejecutarse en cualquier base de datos. !' localesC los crea el usuario #$r:8i&o te&a'. (' te&$oralesC $ueden ser locales cuyos no&bres co&ien3an con un si)no nu&eral #^' o )lobales cuyos no&bres co&ien3an con ! si)nos nu&eral #^^'. Los $rocedi&ientos al&acenados te&$orales locales est+n dis$onibles en la sesi:n de un solo usuario y se eli&inan auto&+tica&ente al finali3ar la sesi:nD los )lobales est+n dis$onibles en las sesiones de todos los usuarios. ' e8tendidosC se i&$le&entan co&o bibliotecas de v6nculos din+&icos #>LL >yna&icLin= Libraries' se ejecutan fuera del entorno de S7L Server. eneral&ente llevan el $refijo E8$%E. @o los estudiare&os. Al crear un $rocedi&iento al&acenado las instrucciones que contiene se anali3an $ara verificar si son correctas sint+ctica&ente. Si no se detectan errores S7L Server )uarda el no&bre del $rocedi&iento al&acenado en la tabla del siste&a EsysobjectsE y su contenido en la tabla del siste&a Esysco&&entsE en la base de datos activa. Si se encuentra al)
Un $rocedi&iento al&acenados $uede 0acer referencia a objetos que no e8isten al &o&ento de crearlo. Los objetos deben e8istir cuando se ejecute el $rocedi&iento al&acenado. 5entajasC - co&$arten la l:)ica de la a$licaci:n con las otras a$licaciones con lo cual el acceso y las &odificaciones de los datos se 0acen en un solo sitio. - $er&iten reali3ar todas las o$eraciones que los usuarios necesitan evitando que ten)an acceso directo a las tablas. - reducen el tr+fico de redD en ve3 de enviar &uc0as instrucciones los usuarios reali3an o$eraciones enviando una
12 - Drocedimientos almacenados (crear - ejecutar) Los $rocedi&ientos al&acenados se crean en la base de datos seleccionada e8ce$to los $rocedi&ientos al&acenados te&$orales que se crean en la base de datos Ete&$dbE. ?n $ri&er lu)ar se deben ti$ear y $robar las instrucciones que se incluyen en el $rocedi&iento al&acenado lue)o si se obtiene el resultado es$erado se crea el $rocedi&iento. Los $rocedi&ientos al&acenados $ueden 0acer referencia a tablas vistas a funciones definidas $or el usuario a otros $rocedi&ientos al&acenados y a tablas te&$orales. Un $rocedi&iento al&acenado $ueden incluir cualquier cantidad y ti$o de instrucciones e8ce$toC create default create $rocedure create rule create tri))er y create vie/. Se $ueden crear otros objetos #$or eje&$lo 6ndices tablas' en tal caso deben es$ecificar el no&bre del $ro$ietarioD se $ueden reali3ar inserciones actuali3aciones eli&inaciones etc. Si un $rocedi&iento al&acenado crea una tabla te&$oral dic0a tabla s:lo e8iste dentro del $rocedi&iento y desa$arece al finali3ar el &is&o. Lo &is&o sucede con las variables. Je&os e&$leado varias veces $rocedi&ientos al&acenados del siste&a #Es$%0el$E Es$%0el$constraintE etc.' a0ora a$rendere&os a crear nuestros $ro$ios $rocedi&ientos al&acenados. Bara crear un $rocedi&iento al&acenado e&$lea&os la instrucci:n Ecreate $rocedureE. La sinta8is b+sica $arcial esC create proced&re NOMBREPROCE"!M!ENTO as !NTR-CC!ONE;
Bara diferenciar los $rocedi&ientos al&acenados del siste&a de los $rocedi&ientos al&acenados locales use un $refijo diferente a Es$%E cuando les de el no&bre. "on las si)uientes instrucciones crea&os un $rocedi&iento al&acenado lla&ado E$a%libros%li&ite%stoc=E que &uestra todos los libros de los cuales 0ay &enos de 14 dis$oniblesC create proc pa_lbros_lte_stocD
as select 51ro lbros w*ere cantdad 9=,;
?ntonces crea&os un $rocedi&iento al&acenado colocando Ecreate $rocedureE #o Ecreate $rocE que es la for&a abreviada' lue)o el no&bre del $rocedi&iento y se)uido de EasE las sentencias que definen el $rocedi&iento. Ecreate $rocedureE debe ser la $ri&era sentencia de un lote. Bara ejecutar el $rocedi&iento al&acenado creado anterior&ente ti$ea&osC eFec pa_lbros_lte_stocD;
?ntonces $ara ejecutar un $rocedi&iento al&acenado coloca&os Ee8ecuteE #o Ee8ecE' se)uido del no&bre del $rocedi&iento. "uando reali3a&os un ejercicio nuevo sie&$re reali3a&os las &is&as tareasC eli&ina&os la tabla si e8iste la crea&os y lue)o in)resa&os al)unos re)istros. Bode&os crear un $rocedi&iento al&acenado que conten)a todas estas instruccionesC create proced&re pa_crear_lbros as 1 ob2ect_d('lbros'%s not n&ll drop table lbros; create table lbros( cod7o nt dentt4# tt&lo )arc*ar(,%# a&tor )arc*ar(+,%# edtoral )arc*ar(,%# preco decal(#%# prar4 De4(cod7o% %; nsert nto lbros )al&es('-no'#'Rc*ard Bac*'#'Planeta'#%; nsert nto lbros )al&es('!l&sones'#'Rc*ard Bac*'#'Planeta'#G%; nsert nto lbros )al&es('El alep*'#'Bor7es'#'Eece'#%; nsert nto lbros )al&es('Aprenda P0P'#'Maro Molna'#'N&e)o s7lo'#%; nsert nto lbros )al&es('Mateatca estas a*'#'Paena'#'N&e)o s7lo'#%; nsert nto lbros )al&es('6a)a en , n&tos'#'Maro Molna'#'Pados'#+%;
lue)o lo ejecuta&os cada ve3 que co&en3a&os un nuevo ejercicio y as6 evita&os ti$ear tantas sentenciasC eFec pa_crear_lbros;
121 - Drocedimientos almacenados (eliminar) Los $rocedi&ientos al&acenados se eli&inan con Edro$ $rocedureE. Sinta8isC drop proced&re NOMBREPROCE"!M!ENTO;
?li&ina&os el $rocedi&iento al&acenado lla&ado E$a%libros%autorEC drop proced&re pa_lbros_a&tor;
Si el $rocedi&iento que quere&os eli&inar no e8iste a$arece un &ensaje de error $ara evitarlo $ode&os e&$lear esta sinta8isC 1 ob2ect_d('NOMBREPROCE"!M!ENTO'% s not n&ll
drop proced&re NOMBREPROCE"!M!ENTO;
?li&ina&os si e8iste el $rocedi&iento E$a%libros%autorE si no e8iste &ostra&os un &ensajeC 1 ob2ect_d('pa_lbros_a&tor'% s not n&ll drop proced&re pa_lbros_a&tor else select 'No eFste el procedento 8pa_lbros_a&tor8';
Edro$ $rocedureE $uede abreviarse con Edro$ $rocE. Se reco&ienda ejecutar el $rocedi&iento al&acenado del siste&a Es$%de$endsE $ara ver si al)
122 - Drocedimientos almacenados (par%metros de entrada) Los $rocedi&ientos al&acenados $ueden recibir y devolver infor&aci:nD $ara ello se e&$lean $ar+&etros de entrada y salida res$ectiva&ente.
5ea&os los $ri&eros. Los $ar+&etros de entrada $osibilitan $asar infor&aci:n a un $rocedi&iento. Bara que un $rocedi&iento al&acenado ad&ita $ar+&etros de entrada se deben declarar variables co&o $ar+&etros al crearlo. La sinta8is esC create proc NOMBREPROCE"!M!ENTO @NOMBREPARAMETRO T!PO =3ALORPOR"E
Los $ar+&etros se definen lue)o del no&bre del $rocedi&iento co&en3ando el no&bre con un si)no arroba #F'. Los $ar+&etros son locales al $rocedi&iento es decir e8isten sola&ente dentro del &is&o. Bueden declararse varios $ar+&etros $or $rocedi&iento se se$aran $or co&as. "uando el $rocedi&iento es ejecutado deben e8$licitarse valores $ara cada uno de los $ar+&etros #en el orden que fueron definidos' a &enos que se 0aya definido un valor $or defecto en tal caso $ueden o&itirse. Bueden ser de cualquier ti$o de dato #e8ce$to cursor'. Lue)o de definir un $ar+&etro y su ti$o o$cional&ente se $uede es$ecificar un valor $or defectoD tal valor es el que asu&e el $rocedi&iento al ser ejecutado si no recibe $ar+&etros. Si no se coloca valor $or defecto un $rocedi&iento definido con $ar+&etros no $uede ejecutarse sin valores $ara ellos. ?l valor $or defecto $uede ser EnullE o una constante ta&bi9n $uede incluir co&odines si el $rocedi&iento e&$lea Eli=eE. "rea&os un $rocedi&iento que recibe el no&bre de un autor co&o $ar+&etro $ara &ostrar todos los libros del autor solicitadoC create proced&re pa_lbros_a&tor @a&tor )arc*ar(+,% as select tt&lo# edtoral#preco 1ro lbros w*ere a&tor= @a&tor;
?l $rocedi&iento se ejecuta colocando Ee8ecuteE #o Ee8ecE' se)uido del no&bre del $rocedi&iento y un valor $ara el $ar+&etroC eFec pa_lbros_a&tor 'Bor7es';
"rea&os un $rocedi&iento que recibe ! $ar+&etros el no&bre de un autor y el de una editorialC create proced&re pa_lbros_a&tor_edtoral @a&tor )arc*ar(+,%# @edtoral )arc*ar(,% as select tt&lo# preco 1ro lbros w*ere a&tor= @a&tor and edtoral=@edtoral;
?l $rocedi&iento se ejecuta colocando Ee8ecuteE #o Ee8ecE' se)uido del no&bre del $rocedi&iento y los valores $ara los $ar+&etros se$arados $or co&asC eFec pa_lbros_a&tor_edtoral 'Rc*ard Bac*'#'Planeta';
Los valores de un $ar+&etro $ueden $asarse al $rocedi&iento &ediante el no&bre del $ar+&etro o $or su $osici:n. La sinta8is anterior ejecuta el $rocedi&iento $asando valores a los $ar+&etros $or $osici:n. Ta&bi9n $ode&os e&$lear la otra sinta8is en la cual $asa&os valores a los $ar+&etros $or su no&breC eFec pa_lbros_a&tor_edtoral @edtoral='Planeta'# @a&tor='Rc*ard Bac*';
"uando $asa&os valores con el no&bre del $ar+&etro el orden en que se colocan $uede alterarse.
@o $odr6a&os ejecutar el $rocedi&iento anterior sin valores $ara los $ar+&etros. Si quere&os ejecutar un $rocedi&iento que $er&ita o&itir los valores $ara los $ar+&etros debe&os al crear el $rocedi&iento definir valores $or defecto $ara cada $ar+&etroC create proced&re pa_lbros_a&tor_edtoral @a&tor )arc*ar(+,%='Rc*ard Bac*'# @edtoral )arc*ar(,%='Planeta' as select tt&lo# a&tor#edtoral#preco 1ro lbros w*ere a&tor= @a&tor and edtoral=@edtoral;
Bode&os ejecutar el $rocedi&iento anterior sin enviarle valores usar+ los $redeter&inados. Si envia&os un solo $ar+&etro a un $rocedi&iento que tiene definido &+s de un $ar+&etro sin es$ecificar a qu9 $ar+&etro corres$onde #valor $or $osici:n' asu&e que es el $ri&ero. ?s decir S7L Server asu&e que los valores se dan en el orden que fueron definidos no se $uede interru&$ir la secuencia. Si quere&os es$ecificar sola&ente el se)undo $ar+&etro debe&os e&$lear la sinta8is de $aso de valores a $ar+&etros $or no&breC eFec pa_lbros_a&tor_edtoral @edtoral='Pados';
Bode&os e&$lear $atrones de b
La sentencia si)uiente ejecuta el $rocedi&iento al&acenado E$a%libros%autor%editorial(E enviando un valor $or $osici:n se asu&e que es el $ri&ero. eFec pa_lbros_a&tor_edtoral+ 'PQ';
La sentencia si)uiente ejecuta el $rocedi&iento al&acenado E$a%libros%autor%editorial(E enviando un valor $ara el se)undo $ar+&etro $ara el $ri&er $ar+&etro to&a el valor $or defectoC eFec pa_lbros_a&tor_edtoral+ @edtoral='PQ';
Ta&bi9n $odr6a&os 0aber ti$eadoC eFec pa_lbros_a&tor_edtoral+ de1a<# 'PQ';
12 - Drocedimientos almacenados (par%metros de salida) >iji&os que los $rocedi&ientos al&acenados $ueden devolver infor&aci:nD $ara ello se e&$lean $ar+&etros de salida. ?l valor se retorna a quien reali3: la lla&ada con $ar+&etros de salida. Bara que un $rocedi&iento al&acenado devuelva un valor se debe declarar una variable con la $alabra clave Eout$utE al crear el $rocedi&ientoC create proced&re NOMBREPROCE"!M!ENTO @PARAMETROENTRA"A T!PO =3ALORPOR"E
Los $ar+&etros de salida $ueden ser de cualquier ti$o de datos e8ce$to te8t nte8t e i&a)e. "rea&os un $rocedi&iento al&acenado al cual le envia&os ! n<&eros y retorna el $ro&edioC create proced&re pa_proedo @n decal(#%# @n decal(#%# @res<ado decal(#% o&tp&t as select @res<ado=(@n@n%?;
Al ejecutarlo ta&bi9n debe e&$learse Eout$utEC declare @)arable decal(#% eFec&te pa_proedo ## @)arable o&tp&t select @)arable;
>eclara&os una variable $ara )uardar el valor devuelto $or el $rocedi&ientoD ejecuta&os el $rocedi&iento envi+ndole ! valores y &ostra&os el resultado. La instrucci:n que reali3a la lla&ada al $rocedi&iento debe contener un no&bre de variable $ara al&acenar el valor retornado. "rea&os un $rocedi&iento al&acenado que &uestre los t6tulos editorial y $recio de los libros de un deter&inado autor #enviado co&o $ar+&etro de entrada' y nos retorne la su&a y el $ro&edio de los $recios de todos los libros del autor enviadoC create proced&re pa_a&tor_s&a4proedo @a&tor )arc*ar(+,%='Q'# @s&a decal(#% o&tp&t# @proedo decal(#% o&tp&t as select tt&lo#edtoral#preco 1ro lbros w*ere a&tor lDe @a&tor select @s&a=s&(preco% 1ro lbros w*ere a&tor lDe @a&tor select @proedo=a)7(preco% 1ro lbros w*ere a&tor lDe @a&tor;
?jecuta&os el $rocedi&iento y ve&os el contenido de las variables en las que al&acena&os los $ar+&etros de salida del $rocedi&ientoC declare @s decal(#%# @p decal(#% eFec&te pa_a&tor_s&a4proedo 'Rc*ard Bac*'# @s o&tp&t# @p o&tp&t select @s as total# @p as proedo;
12# - Drocedimientos almacenados (return) La instrucci:n EreturnE sale de una consulta o $rocedi&iento y todas las instrucciones $osteriores no son ejecutadas. "rea&os un $rocedi&iento que &uestre todos los libros de un autor deter&inado que se in)resa co&o $ar+&etro. Si no se in)resa un valor o se in)resa EnullE se &uestra un &ensaje y se sale del $rocedi&ientoC create proced&re pa_lbros_a&tor @a&tor )arc*ar(+,%=n&ll as 1 @a&tor s n&ll be7n select '"ebe ndcar &n a&tor' ret&rn end; select tt&lo 1ro lbros w*ere a&tor = @a&tor;
Si al ejecutar el $rocedi&iento envia&os el valor EnullE o no $asa&os valor con lo cual to&a el valor $or defecto EnullE se &uestra un &ensaje y se saleD en caso contrario ejecuta la consulta lue)o del EelseE. EreturnE $uede retornar un valor entero. Un $rocedi&iento $uede retornar un valor de estado $ara indicar si se 0a ejecutado correcta&ente o no. "rea&os un $rocedi&iento al&acenado que in)resa re)istros en la tabla ElibrosE. Los $ar+&etros corres$ondientes al t6tulo y autor >?2?@ in)resarse con un valor distinto de EnullE los de&+s son o$cionales. ?l $rocedi&iento retorna E1E si la inserci:n se reali3a es decir si se in)resan valores $ara t6tulo y autor y E4E en caso que t6tulo o autor sean nulosC create proced&re pa_lbros_n7reso @tt&lo )arc*ar(,%=n&ll# @a&tor )arc*ar(+,%=n&ll# @edtoral )arc*ar(,%=n&ll# @preco decal(#%=n&ll as 1 (@tt&lo s n&ll% or (@a&tor s n&ll% ret&rn , else be7n nsert nto lbros )al&es (@tt&lo#@a&tor#@edtoral#@preco% ret&rn end;
Bara ver el resultado debe&os declarar una variable en la cual se al&acene el valor devuelto $or el $rocedi&ientoD lue)o ejecutar el $rocedi&iento asi)n+ndole el valor devuelto a la variable final&ente &ostra&os el contenido de la variableC declare @retorno nt eFec @retorno=pa_lbros_n7reso 'Alca en el pas$$$'#'Lews Carroll' select '!n7reso realado=' = @retorno eFec @retorno=pa_lbros_n7reso select '!n7reso realado=' = @retorno;
Ta&bi9n $odr6a&os e&$lear un EifE $ara controlar el valor de la variable de retornoC declare @retorno nt; eFec @retorno=pa_lbros_n7reso 'El 7ato con botas'#'Anno' 1 @retorno= prnt 'Re7stro n7resado' else select 'Re7stro no n7resado por&e 1altan datos';
12' - Drocedimientos almacenados (informaci?n) Los $rocedi&ientos al&acenados son objetos as6 que $ara obtener infor&aci:n de ellos $ueden usarse los si)uientes $rocedi&ientos al&acenados del siste&a y las si)uientes tablasC - Es$%0el$EC sin $ar+&etros nos &uestra todos los objetos de la base de datos seleccionada incluidos los $rocedi&ientos. ?n la colu&na EObject%ty$eE a$arece Estored $rocedureE si es un $rocedi&iento al&acenado. Si le envia&os co&o ar)u&ento el no&bre de un $rocedi&iento obtene&os la fec0a de creaci:n e infor&aci:n sobre sus $ar+&etros.
- Es$%0el$te8tEC se)uido del no&bre de un $rocedi&iento al&acenado nos &uestra el te8to que define el $rocedi&iento e8ce$to si 0a sido encri$tado. - Es$%stored%$roceduresEC &uestra todos los $rocedi&ientos al&acenados los $ro$ietarios etc. ?ste $rocedi&iento al&acenado $uede recibir ( $ar+&etrosC Fs$%na&e #no&bre nvarc0ar ad&ite co&odines $ara b
- Es$%de$endsEC se)uido del no&bre de un objeto nos devuelve ! resultadosC 1' no&bre ti$o ca&$os etc. de los objetos de los cuales de$ende el objeto enviado y !' no&bre y ti$o de los objetos que de$enden del objeto no&brado. Bor eje&$lo ejecuta&os Es$%de$endsE se)uido del no&bre de un $rocedi&ientoC sp_depends pa_a&tor_proedo;
a$arecen las tablas #y de&+s objetos' de las cuales de$ende el $rocedi&iento es decir las tablas referenciadas en el &is&o. Bode&os ejecutar el $rocedi&iento se)uido del no&bre de una tablaC sp_depends lbros;
a$arecen los $rocedi&ientos #y de&+s objetos' que de$enden de ella. - La tabla del siste&a EsysobjectsEC &uestra no&bre y varios datos de todos los objetos de la base de datos actual. La colu&na E8ty$eE indica el ti$o de objeto. Si es un $rocedi&iento al&acenado &uestra EBE. ?je&$loC select 51ro s4sob2ects;
Si quere&os ver todos los $rocedi&ientos al&acenados creados $or nosotros $ode&os ti$earC select 51ro s4sob2ects w*ere Ft4pe='P' and>> tpo procedento nae lDe 'paQ';>>bYs&eda con coodn
12 - Drocedimientos almacenados (encriptado) >iji&os que S7L Server )uarda el no&bre del $rocedi&iento al&acenado en la tabla del siste&a EsysobjectsE y su contenido en la tabla Esysco&&entsE. Si no quiere que los usuarios $uedan leer el contenido del $rocedi&iento $ode&os indicarle a S7L Server que codifique la entrada a la tabla Esysco&&entsE que contiene el te8to. Bara ello debe&os colocar la o$ci:n E/it0 encry$tionE al crear el $rocedi&ientoC create proced&re NOMBREPROCE"!M!ENTO PARAMETRO wt* encr4pton
as !NTR-CC!ONE;
?sta o$ci:n es o$cional. "rea&os el $rocedi&iento al&acenado E$a%libros%autorE con la o$ci:n de encri$tadoC create proced&re pa_lbros_a&tor @a&tor )arc*ar(+,%=n&ll wt* encr4pton as select 51ro lbros w*ere a&tor=@a&tor;
Si ejecuta&os el $rocedi&iento al&acenado del siste&a Es$%0el$te8tE $ara ver su contenido no a$arece.
12+ - Drocedimientos almacenados (modificar) Los $rocedi&ientos al&acenados $ueden &odificarse $or necesidad de los usuarios o $or ca&bios en la estructura de las tablas que referencia. Un $rocedi&iento al&acenado e8istente $uede &odificarse con Ealter $rocedureE. Sinta8isC alter proced&re NOMBREPROCE"!M!ENTO @PARAMETRO T!PO = 3ALORPRE"ETERM!NA"O as ENTENC!A;
Modifica&os el $rocedi&iento al&acenado E$a%libros%autorE $ara que &uestre ade&+s del t6tulo la editorial y $recioC alter proced&re pa_lbros_a&tor @a&tor )arc*ar(+,%=n&ll as 1 @a&tor s n&ll be7n select '"ebe ndcar &n a&tor' ret&rn end else select tt&lo#edtoral#preco 1ro lbros w*ere a&tor = @a&tor;
Si quiere &odificar un $rocedi&iento que se cre: con la o$ci:n E/it0 encry$tionE y quiere conservarla debe incluirla al alterarlo.
12, - Drocedimientos almacenados (insertar) Bode&os in)resar datos en una tabla con el resultado devuelto $or un $rocedi&iento al&acenado. La instrucci:n si)uiente crea el $rocedi&iento E$a%ofertasE que in)resa libros en la tabla EofertasEC
create proc pa_o1ertas as select tt&lo#a&tor#edtoral#preco 1ro lbros w*ere preco9,;
La si)uiente instrucci:n in)resa en la tabla EofertasE el resultado del $rocedi&iento E$a%ofertasEC nsert nto o1ertas eFec pa_o1ertas;
Las tablas deben e8istir y los ti$os de datos deben coincidir.
12. - Drocedimientos almacenados (anidados) Un $rocedi&iento al&acenado $uede lla&ar a otro $rocedi&iento al&acenado. ?l $rocedi&iento que es invocado $or otro debe e8istir cuando crea&os el $rocedi&iento que lo lla&a. ?s decir si un $rocedi&iento A lla&a a otro $rocedi&iento 2 2 debe e8istir al crear A. Los $rocedi&ientos al&acenados $ueden anidarse 0asta (! niveles.
"rea&os un $rocedi&iento al&acenado que reciba ! n<&eros enteros y nos retorne el $roducto de los &is&osC create proced&re pa_<plcar @n&ero nt# @n&ero nt# @prod&cto nt o&tp&t as select @prod&cto=@n&ero5@n&ero;
"rea&os otro $rocedi&iento que nos retorne el factorial de un n<&ero tal $rocedi&iento lla&ar+ al $rocedi&iento E$a%&ulti$licarEC create proced&re pa_1actoral @n&ero nt as declare @res<ado nt declare @n& nt set @res<ado= set @n&=@n&ero w*le (@n&:% be7n eFec pa_<plcar @res<ado#@n&# @res<ado o&tp&t set @n&=@n&> end select rtr(con)ert(c*ar#@n&ero%%'Z='con)ert(c*ar#@res<ado%;
C&ando &n procedento (A% llaa a otro (B%# el se7&ndo (B% tene acceso a todos los ob2etos &e cree el prero (A%$
1 - Drocedimientos /lmacenados (recompilar) La co&$ilaci:n es un $roceso que consiste en anali3ar el $rocedi&iento al&acenado y crear un $lan de ejecuci:n. Se reali3a la $ri&era ve3 que se ejecuta un $rocedi&iento al&acenado o si el $rocedi&iento al&acenado se debe volver a co&$ilar #reco&$ilaci:n'. S7L Server reco&$ila auto&+tica&ente un $rocedi&iento al&acenado si se reali3a al)
Un $rocedi&iento al&acenado $uede reco&$ilarse e8$l6cita&ente. ?n )eneral se reco&ienda no 0acerlo e8ce$to si se a)re)a un 6ndice a una tabla referenciada $or el $rocedi&iento o si los datos 0an variado &uc0o desde la
!' Bode&os es$ecificar E/it0 reco&$ileE al &o&ento de ejecutarloC eFec NOMBREPROCE"!M!ENTO wt* recople;
(' Bode&os ejecutar el $rocedi&iento al&acenado del siste&a Es$%reco&$ileE. ?ste $rocedi&iento vuelve a co&$ilar el $rocedi&iento al&acenado #o desencadenador' que se es$ecifica. La sinta8is esC eFec sp_recople NOMBREOB6ETO;
?l $ar+&etro enviado debe ser el no&bre de un $rocedi&iento de un desencadenador de una tabla o de una vista. Si es el no&bre de una tabla o vista todos los $rocedi&ientos al&acenados que usan tal tabla #o vista' se vuelven a co&$ilar.
11 - Drocedimientos /lmacenados (con join) Jasta a0ora 0e&os creado $rocedi&ientos que incluyen una sola tabla o $ocas instrucciones $ara a$render la sinta8is $ero la funcionalidad de un $rocedi&iento consiste b+sica&ente en que conten)an &uc0as instrucciones o instrucciones co&$lejas y as6 evitar ti$ear re$etida&ente dic0as instruccionesD ade&+s si no quere&os que el usuario cono3ca la estructura de las tablas involucradas los $rocedi&ientos $er&iten el acceso a ellas. Bode&os crear $rocedi&ientos que incluyan co&binaciones #join' subconsultas varias instrucciones y lla&adas a otros $rocedi&ientos. Bode&os crear todos los $rocedi&ientos que necesite&os $ara que realicen todas las o$eraciones y consultas.
12 - $ablas temporales Las tablas te&$orales son visibles sola&ente en la sesi:n actual. Las tablas te&$orales se eli&inan auto&+tica&ente al acabar la sesi:n o la funci:n o $rocedi&iento al&acenado en el cual fueron definidas. Se $ueden eli&inar con Edro$ tableE. Bueden ser locales #son visibles s:lo en la sesi:n actual' o )lobales #visibles $or todas las sesiones'. Bara crear tablas te&$orales locales se e&$lea la &is&a sinta8is que $ara crear cualquier tabla e8ce$to que se coloca un si)no nu&eral #^' $recediendo el no&bre. create table [NOMBRE( CAMPO "E
Bara referenciarla en otras consultas se debe incluir el nu&eral#^' que es $arte del no&bre. Bor eje&$loC nsert nto [lbros de1a< )al&es; select 51ro [lbros;
Una tabla te&$oral no $uede tener una restricci:n Eforei)n =eyE ni ser inde8ada ta&$oco $uede ser referenciada $or una vista. Bara crear tablas te&$orales )lobales se e&$lea la &is&a sinta8is que $ara crear cualquier tabla e8ce$to que se coloca un si)no nu&eral doble #^^' $recediendo el no&bre. create table [[NOMBRE( CAMPO "E
?l #o los' nu&erales son $arte del no&bre. As6 que $uede crearse una tabla $er&anente lla&ada ElibrosE otra tabla te&$oral local lla&ada E^librosE y una tercera tabla te&$oral )lobal deno&inada E^^librosE. @o $ode&os consultar la tabla EsysobjectsE $ara ver las tablas te&$orales debe&os ti$earC select 51ro tepdb$$s4sob2ects; para elnar &na tabla teporal create table [pepe (caca nt% 1 eFsts (select 5 1ro tepdb$dbo$s4sob2ects w*ere nae lDe'[pepeQ'% select '!' else select 'NO' drop table [pepe
1 - ;unciones S7L Server ofrece varios ti$os de funciones $ara reali3ar distintas o$eraciones. Je&os visto y e&$leado varias de ellas. Se $ueden e&$lear las funciones del siste&a en cualquier lu)ar en el que se $er&ita una e8$resi:n en una sentencia EselectE.
Las funciones $ueden clasificarse enC - deter&inisticasC sie&$re retornan el &is&o resultado si se las invoca enviando el &is&o valor de entrada. Todas las funciones de a)re)ado y strin) son deter&inisticas e8ce$to Ec0arinde8E y E$atinde8E. - no deter&inisticasC $ueden retornar distintos resultados cada ve3 que se invocan con el &is&o valor de entrada. Las si)uientes son al)unas de las funciones no deter&inisticasC )etdate datena&e te8t$tr te8tvalid rand. Todas las funciones de confi)uraci:n cursor &eta data se)uridad y estad6sticas del siste&a son no deter&inisticas. S7L Server $rovee &uc0as funciones y ade&+s $er&ite que el usuario $ueda definir sus $ro$ias funciones. Sabe&os que una funci:n es un conjunto de sentencias que o$eran co&o una unidad l:)ica una rutina que retorna un valor. Una funci:n tiene un no&bre ace$ta $ar+&etros de entrada y retorna un valor escalar o una tabla. Los $ar+&etros de entrada $ueden ser de cualquier ti$o e8ce$to ti&esta&$ cursor y table. Las funciones definidas $or el usuario no $er&iten $ar+&etros de salida. @o todas las sentencias S7L son v+lidas dentro de una funci:n. @O es $osible e&$lear en ellas funciones no deter&inadas #co&o )etdate#'' ni sentencias de &odificaci:n o actuali3aci:n de tablas o vistas. Si $ode&os e&$lear sentencias de asi)naci:n de control de flujo #if' de &odificaci:n y eli&inaci:n de variables locales. S7L Server ad&ite ( ti$os de funciones definidas $or el usuario clasificadas se)
1# - ;unciones (drop) Las funciones definidas $or el usuario se eli&inan con la instrucci:n Edro$ functionEC Sinta8isC drop 1&ncton NOMBREPPROP!ETAR!O$NOMBRE<-NC!ON;
Se coloca el no&bre del $ro$ietario se)uido del no&bre de la funci:n. Si la funci:n que se intenta eli&inar no e8iste a$arece un &ensaje indic+ndolo $ara evitarlo $ode&os verificar su e8istencia antes de solicitar su eli&inaci:n #co&o con cualquier otro objeto'C 1 ob2ect_d('NOMBREPROP!ETAR!O$NOMBRE<-NC!ON'% s not n&ll drop 1&ncton NOMBREPROP!ETAR!O$NOMBRE<-NC!ON;
?li&ina&os si e8iste la funci:n deno&inada Ef%fec0acadenaEC 1 ob2ect_d('dbo$1_1ec*acadena'% s not n&ll
drop 1&ncton dbo$1_1ec*acadena;
1' - ;unciones escalares (crear y llamar) Una funci:n escalar retorna un
Lue)o del no&bre se colocan #o$cional&ente' los $ar+&etros de entrada con su ti$o. La cl+usula EreturnsE indica el ti$o de dato retornado. ?l cuer$o de la funci:n se define en un bloque Ebe)in...endE que contiene las instrucciones que retornan el valor. ?l ti$o del valor retornado $uede ser de cualquier ti$o e8ce$to te8t nte8t i&a)e cursor o ti&esta&$. "rea&os una si&$le funci:n deno&inada Ef%$ro&edioE que recibe ! valores y retorna el $ro&edioC create 1&ncton 1_proedo (@)alor decal(#%# @)alor decal(#% % ret&rns decal (#% as be7n declare @res<ado decal(#% set @res<ado=(@)alor@)alor%? ret&rn @res<ado end;
?ntonces lue)o de Ecreate functionE y el no&bre de la funci:n se deben es$ecificar los $ar+&etros de entrada con sus ti$os de datos #entre $ar9ntesis' el ti$o de dato que retorna lue)o de EreturnsE lue)o de EasE co&ien3a el bloque Ebe)in...endE dentro del cual se encuentran las instrucciones de $rocesa&iento y el valor retornado lue)o de EreturnE. ?n el eje&$lo anterior se declara una variable local a la funci:n #desa$arece al salir de la funci:n' que calcula el resultado que se retornar+. Al 0acer referencia a una funci:n escalar se debe es$ecificar el $ro$ietario y el no&bre de la funci:nC select dbo$1_proedo($#G$%;
"uando lla&a&os a funciones que tienen definidos $ar+&etros de entrada >?2?MOS su&inistrar SI?MBR? un valor $ara 9l. Si lla&a&os a la funci:n anterior sin enviarle los valores $ara los $ar+&etrosC select dbo$1_proedo(%;
S7L Server &uestra un &ensaje de error indicando que necesita ar)u&entos. "rea&os una funci:n a la cual le envia&os una fec0a y nos retorna el no&bre del &es en es$aolC
create 1&ncton 1_nobreMes (@1ec*a datete=',,I?,?,'% ret&rns )arc*ar(,% as be7n declare @nobre )arc*ar(,% set @nobre= case datenae(ont*#@1ec*a% w*en '6an&ar4' t*en 'Enero' w*en '>case ret&rn @nobre end;
Analice&osC lue)o de Ecreate functionE y el no&bre de la funci:n es$ecifica&os los $ar+&etros de entrada con sus ti$os de datos #entre $ar9ntesis'. ?l $ar+&etro de entrada tiene definido un valor $or defecto. Lue)o de los $ar+&etros de entrada se indica el ti$o de dato que retorna lue)o de EreturnsED lue)o de EasE co&ien3a el bloque Ebe)in...endE dentro del cual se encuentran las instrucciones de $rocesa&iento y el valor retornado lue)o de EreturnE. Las funciones que retornan un valor escalar $ueden e&$learse en cualquier consulta donde se coloca un ca&$o. Recuerde que al invocar una funci:n escalar se debe es$ecificar el $ro$ietario y el no&bre de la funci:nC select nobre# dbo$1_nobreMes(1ec*an7reso% as 'es de n7reso' 1ro epleados;
@o olvide que cuando invoca&os funciones que tienen definidos $ar+&etros de entrada >?2?MOS su&inistrar SI?MBR? un valor $ara 9l. Bode&os colocar un valor $or defecto al $ar+&etro $ero al invocar la funci:n $ara que to&e el valor $or defecto >?2?MOS es$ecificar EdefaultE. Bor eje&$lo si lla&a&os a la funci:n anterior sin enviarle un valorC select dbo$1_nobreMes(%;
S7L Server &uestra un &ensaje de error indicando que necesita ar)u&ento. Bara que to&e el valor $or defecto debe&os enviar EdefaultE co&o ar)u&entoC select dbo$1_nobreMes(de1a<%;
La instrucci:n Ecreate functionE debe ser la $ri&era sentencia de un lote.
1 - ;unciones de tabla de varias instrucciones Je&os visto el $ri&er ti$o de funciones definidas $or el usuario que retornan un valor escalar. A0ora vere&os las funciones con varias instrucciones que retornan una tabla. Las funciones que retornan una tabla $ueden e&$learse en lu)ar de un Efro&E de una consulta. ?ste ti$o de funci:n es si&ilar a un $rocedi&iento al&acenadoD la diferencia es que la tabla retornada $or la funci:n $uede ser referenciada en el Efro&E de una consulta $ero el resultado de un $rocedi&iento al&acenado no. Ta&bi9n es si&ilar a una vistaD $ero en las vistas sola&ente $ode&os e&$lear EselectE &ientras que en funciones definidas $or el usuario $ode&os incluir sentencias co&o EifE lla&adas a funciones $rocedi&ientos etc. Sinta8isC create 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO% ret&rns @NOMBRETABLARETORNO table>> nobre de la tabla >>1orato de la tabla (CAMPO T!PO# CAMPO T!PO# CAMPO+ T!PO % as be7n nsert @NOMBRETABLARETORNO select CAMPO 1ro TABLA w*ere capo OPERA"OR @PARAMETRO RET-RN end
"o&o cualquier otra funci:n se crea con Ecreate functionE se)uida del no&bre de la funci:nD lue)o #o$cional&ente' los $ar+&etros de entrada con su ti$o de dato.
La cl+usula EreturnsE define un no&bre de variable local $ara la tabla que retornar+ el ti$o de datos a retornar #que es EtableE' y el for&ato de la &is&a #ca&$os y ti$os'. ?l cuer$o de la funci:n se define ta&bi9n en un bloque Ebe)in... endE el cual contiene las instrucciones que insertan filas en la variable #tabla que ser+ retornada' definida en EreturnsE. EreturnE indica que las filas insertadas en la variable son retornadasD no $uede ser un ar)u&ento. ?l si)uiente eje&$lo crea una funci:n deno&inada Ef%ofertasE que recibe un $ar+&etro. La funci:n retorna una tabla con el codi)o t6tulo autor y $recio de todos los libros cuyo $recio sea inferior al $ar+&etroC create 1&ncton 1_o1ertas (@no decal(#%% ret&rns @o1ertas table>> nobre de la tabla >>1orato de la tabla (cod7o nt# tt&lo )arc*ar(,%# a&tor )arc*ar(+,%# preco decal(#% % as be7n nsert @o1ertas select cod7o#tt&lo#a&tor#preco 1ro lbros w*ere preco 9 @no ret&rn end;
Las funciones que retornan una tabla $ueden lla&arse sin es$ecificar $ro$ietarioC select 51ro 1_o1ertas(+,%; select 51ro dbo$1_o1ertas(+,%;
>iji&os que este ti$o de funci:n $uede ser referenciada en el Efro&E de una consultaD la si)uiente consulta reali3a un join entre la tabla ElibrosE y la tabla retornada $or la funci:n Ef%ofertasEC select 51ro lbros as l 2on dbo$1_o1ertas(% as o on l$cod7o=o$cod7o;
Se $uede lla&ar a la funci:n co&o si fuese una tabla o vista listando al)unos ca&$osC select tt&lo#preco 1ro dbo$1_o1ertas(,%;
1+ - ;unciones con valores de tabla en l3nea Una funci:n con valores de tabla en l6nea retorna una tabla que es el resultado de una
?s si&ilar a una vista $ero &+s fle8ible en el e&$leo de $ar+&etros. ?n una vista no se $uede incluir un $ar+&etro lo que 0ace&os es e s a)re)ar una cl+usula E/0ereE al ejecutar ejecu tar la vista. Las funciones con valores de tabla en l6nea funcionan co&o una vista con $ar+&etros. Sinta8isC create 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO=3ALORPOR"E
"o&o todas las funciones definidas $or el usuario se crea con Ecreate functionE se)uido del no&bre que le da&os a la funci:nD lue)o declara&os los $ar+&etros de entrada con su ti$o de dato entre $ar9ntesis. ?l valor $or defecto es o$cional. EreturnsE es$ecifica EtableE co&o el ti$o de datos a retornar. @o se define el for&ato de la tabla a retornar $orque queda establecido en el EselectE. ?l cuer$o de la funci:n no contiene un bloque Ebe)in...endE co&o las otras funciones. La cl+usula EreturnE contiene una sola instrucci:n EselectE entre $ar9ntesis. ?l resultado del EselectE es la tabla que se retorna. ?l EselectE est+ sujeto a las &is&as re)las que los EselectE de las vistas. "rea&os una funci:n con valores de tabla en l6nea que recibe un valor de autor co&o $ar+&etroC create 1&ncton 1_lbros (@a&tor )arc*ar(+,%='Bor7es'% ret&rns table as ret&rn ( select tt&lo#edtoral 1ro lbros w*ere a&tor lDe 'Q'@a&tor'Q' %;
?stas funciones retornan una tabla y se 0ace referencia a ellas en la cl+usula Efro&E co&o una vistaC select 51ro 1_lbros('Bac*'%;
Recuerde a que todas las funciones que tienen definidos $ar+&etros se les >?2? su&inistrar valores $ara ellos al invocarse. Recuerde que $ara que el $ar+&etro to&e el valor $or defecto #si lo tiene' >?2? enviarse EdefaultE al lla&ar a la funci:nD si no le envia&os $ar+&etros S7L Server &uestra un &ensaje de error. >>ncorrectoJ select 51ro 1_lbros(%; select 51ro 1_lbros(de1a<%;>>correcto
1, - ;unciones (modificar) Las funciones de S7L Server no $ueden ser &odificadas las funciones definidas $or el usuario si. Las funciones definidas $or el usuario $ueden &odificarse con la instrucci:n Ealter functionE. Sinta8is )eneralC alter 1&ncton PROP!ETAR!O$NOMBRE<-NC!ON N-E3A"E
Sinta8is $ara &odificar funciones escalaresC alter 1&nton PROP!ETAR!O$NOMBRE<-NC!ON (@PARAMETRO T!PO=3ALORPOR"E
Sinta8is $ara &odificar una funci:n de varias instrucciones que retorna una tablaC alter 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO=3ALORPOR"E
Sinta8is $ara &odificar una funci:n con valores de tabla en l6nea alter 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO% ret&rns TABLE as ret&rn (ENTENC!A ELECT%
5ea&os 5e a&os un eje&$lo. "rea&os una funci:n que retorna una tabla en l6neaC
create 1&ncton 1_lbros (@a&tor )arc*ar(+,%='Bor7es'% ret&rns table as ret&rn ( select tt&lo#edtoral 1ro lbros w*ere a&tor lDe 'Q'@a&tor'Q' %;
La &odifica&os a)re)ando otro ca&$o en el EselectEC alter table 1_lbros (@a&tor )arc*ar(+,%='Bor7es'% ret&rns table as ret&rn ( select cod7o#tt&lo#edtoral 1ro lbros w*ere a&tor lDe 'Q'@a&tor'Q' %;
1. - ;unciones (encriptado) Las funciones definidas $or el usuario $ueden encri$tarse $ara evitar que sean le6das con Es$%0el$te8tE. Bara ello debe&os a)re)ar al crearlas la o$ci:n E/it0 encry$tionE antes de EasE. ?n funciones escalaresC create 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO% ret&rns T!PO wt* encr4pton as be7n C-ERPO ret&rn EPRE!ON
end
?n funciones de tabla de varias sentencias se coloca lue)o del for&ato de la tabla a retornarC create 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO% ret&rns @NOMBRETABLARETORNO table>> nobre de la tabla >>1orato de la tabla (CAMPO T!PO# CAMPO T!PO# CAMPO+ T!PO % wt* encr4pton as be7n nsert @NOMBRETABLARETORNO select CAMPO 1ro TABLA w*ere capo OPERA"OR @PARAMETRO RET-RN end
?n funciones con valores de tabla en l6neaC create 1&ncton NOMBRE<-NC!ON (@PARAMETRO T!PO=3ALORPOR"E
5ea&os un eje&$loC create 1&ncton 1_lbros (@a&tor )arc*ar(+,%='Bor7es'% ret&rns table wt* encr4pton as ret&rn ( select tt&lo#edtoral 1ro lbros w*ere a&tor lDe 'Q'@a&tor'Q' %;
Si ejecuta&os el $rocedi&iento al&acenado del siste&a Es$%0el$te8tE se)uido del no&bre de la funci:n creada anterior&ente S7L Server &ostrar+ un &ensaje indicando que tal funci:n est+ encri$tada.
1# - ;unciones (informaci?n) Las funciones son objetos as6 que $ara obtener infor&aci:n de ellos $ueden usarse los si)uientes $rocedi&ientos al&acenados del siste&a y las si)uientes tablasC - Es$%0el$EC sin $ar+&etros nos &uestra todos los objetos de la base de datos seleccionada incluidas las funciones definidas $or el usuario. ?n la colu&na EObject%ty$eE a$arece Escalar functionE si es una funci:n escalar Etable functionE si es una funci:n de tabla de varias sentencias y Einline functionE si es una funci:n de tabla en l6nea. Si le envia&os co&o ar)u&ento el no&bre de una funci:n definida $or el usuario obtene&os el $ro$ietario el ti$o de funci:n y la fec0a de creaci:nD si es una funci:n de tabla los ca&$os de la tabla retornada.
- Es$%0el$te8tEC se)uido del no&bre de una funci:n definida $or el usuario nos &uestra el te8to que define la funci:n e8ce$to si 0a sido encri$tado. - Es$%stored%$roceduresEC &uestra todos los $rocedi&ientos al&acenados y funciones definidas $or el usuario. - Es$%de$endsEC se)uido del no&bre de un objeto nos devuelve ! resultadosC 1' no&bre ti$o ca&$os etc. de los objetos de los cuales de$ende el objeto enviado #referenciados $or el objeto' y !' no&bre y ti$o de los objetos que de$enden del objeto no&brado #que lo referencian'. Bor eje&$lo ejecuta&os Es$%de$endsE se)uido del no&bre de una funci:n definida $or el usuarioC sp_depends pa_lbroslstado;
a$arecen las tablas #y de&+s objetos' de las cuales de$ende el $rocedi&iento es decir las tablas #y ca&$os' referenciadas en la &is&a. @o a$arecen objetos que de$endan de la funci:n $orque no e8iste nin)
a$arecen las funciones #y de&+s objetos' que de$enden de ella #que la referencian'. @o a$arecen objetos de los cuales de$ende $orque la tabla no los tiene. - La tabla del siste&a EsysobjectsEC &uestra no&bre y varios datos de todos los objetos de la base de datos actual. La colu&na E8ty$eE indica el ti$o de objeto. Si es una funci:n definida $or el usuario escalar &uestra E;@E si es una funci:n de tabla de varias sentencias &uestra ET;E y si es una funci:n de tabla en linea &uestra EI;E. Si quere&os ver el no&bre ti$o y fec0a de creaci:n de todas las funciones definidas $or el usuario $ode&os ti$earC select nae#Ft4pe as tpo#crdate as 1ec*a 1ro s4sob2ects w*ere Ft4pe n ('
1#1 - Bisparadores (tri""ers) Un Etri))erE #dis$arador o desencadenador' es un ti$o de $rocedi&iento al&acenado que se ejecuta cuando se intenta &odificar los datos de una tabla #o vista'. Se definen $ara una tabla #o vista' es$ec6fica. Se crean $ara conservar la inte)ridad referencial y la co0erencia entre los datos entre distintas tablas. Si se intenta &odificar #a)re)ar actuali3ar o eli&inar' datos de una tabla en la que se defini: un dis$arador $ara al)una de estas acciones #inserci:n actuali3aci:n y eli&inaci:n' el dis$arador se ejecuta #se dis$ara' en for&a auto&+tica. Un tri))er se asocia a un evento #inserci:n actuali3aci:n o borrado' sobre una tabla. La diferencia con los $rocedi&ientos al&acenados del siste&a es que los tri))ersC - no $ueden ser invocados directa&enteD al intentar &odificar los datos de una tabla $ara la que se 0a definido un dis$arador el dis$arador se ejecuta auto&+tica&ente. - no reciben y retornan $ar+&etros.
- son a$ro$iados $ara &antener la inte)ridad de los datos no $ara obtener resultados de consultas. Los dis$aradores a diferencia de las restricciones Ec0ec=E $ueden 0acer referencia a ca&$os de otras tablas. Bor eje&$lo $uede crearse un tri))er de inserci:n en la tabla EventasE que co&$ruebe el ca&$o Estoc=E de un art6culo en la tabla EarticulosED el dis$arador controlar6a que cuando el valor de Estoc=E sea &enor a la cantidad que se intenta vender la inserci:n del nuevo re)istro en EventasE no se realice. Los dis$aradores se ejecutan >?SBU?S de la ejecuci:n de una instrucci:n EinsertE Eu$dateE o EdeleteE en la tabla en la que fueron definidos. Las restricciones se co&$rueban A@T?S de la ejecuci:n de una instrucci:n EinsertE Eu$dateE o EdeleteE. Bor lo tanto las restricciones se co&$rueban $ri&ero si se infrin)e al)una restricci:n el desencadenador no lle)a a ejecutarse. Los tri))ers se crean con la instrucci:n Ecreate tri))erE. ?sta instrucci:n es$ecifica la tabla en la que se define el dis$arador los eventos $ara los que se ejecuta y las instrucciones que contiene. Sinta8is b+sicaC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA 1or E3ENTO> nsert# &pdate o delete as ENTENC!A
Anali3a&os la sinta8isC - Ecreate tri))erE junto al no&bre del dis$arador. - EonE se)uido del no&bre de la tabla o vista $ara la cual se establece el tri))er. - lue)o de EforE se indica la acci:n #evento el ti$o de &odificaci:n' sobre la tabla o vista que activar+ el tri))er. Buede ser EinsertE Eu$dateE o EdeleteE. >ebe colocarse al &enos U@A acci:n si se coloca &+s de una deben se$ararse con co&as. - lue)o de EasE viene el cuer$o del tri))er se es$ecifican las condiciones y acciones del dis$aradorD es decir las condiciones que deter&inan cuando un intento de inserci:n actuali3aci:n o borrado $rovoca las acciones que el tri))er reali3ar+. "onsideraciones )eneralesC - Ecreate tri))erE debe ser la $ri&era sentencia de un bloque y s:lo se $uede a$licar a una tabla. - un dis$arador se crea sola&ente en la base de datos actual $ero $uede 0acer referencia a objetos de otra base de datos. - Las si)uientes instrucciones no est+n $er&itidas en un desencadenadorC create database alter database dro$ database load database restore database load lo) reconfi)ure restore lo) dis= init dis= resi3e. - Se $ueden crear varios tri))ers $ara cada evento es decir $ara cada ti$o de &odificaci:n #inserci:n actuali3aci:n o borrado' $ara una &is&a tabla. Bor eje&$lo se $uede crear un Einsert tri))erE $ara una tabla que ya tiene otro Einsert tri))erE. A continuaci:n vere&os la creaci:n de un dis$arador $ara el suceso de inserci:nC Einsert tri)erE.
1#2 - Bisparador de inserci?n (insert tri""er) Bode&os crear un dis$arador $ara que se ejecute sie&$re que una instrucci:n EinsertE in)rese datos en una tabla. Sinta8is b+sicaC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA 1or nsert as ENTENC!A
Anali3a&os la sinta8isC Ecreate tri))erE junto al no&bre del dis$aradorD EonE se)uido del no&bre de la tabla $ara la cual se establece el tri))er. Lue)o de EforE se coloca el evento #en este caso EinsertE' lo que indica que las inserciones sobre la tabla activar+n el tri))er. Lue)o de EasE se es$ecifican las condiciones y acciones es decir las condiciones que deter&inan cuando un intento de inserci:n $rovoca las acciones que el tri))er reali3ar+. "rea&os un tri))er sobre la tabla EventasE $ara el evento se inserci:n. "ada ve3 que se reali3a un EinsertE sobre EventasE el dis$arador se ejecuta. ?l dis$arador controla que la cantidad que se intenta vender sea &enor o i)ual al stoc= del libro y actuali3a el ca&$o Estoc=E de ElibrosE restando al valor anterior la cantidad vendidaC create tr77er "!_)entas_nsertar on )entas 1or nsert as declare @stocD nt select @stocD= stocD 1ro lbros 2on nserted on nserted$cod7olbro=lbros$cod7o w*ere lbros$cod7o=nserted$cod7olbro 1 (@stocD:=(select cantdad 1ro nserted%% &pdate lbros set stocD=stocD>nserted$cantdad 1ro lbros 2on nserted on nserted$cod7olbro=lbros$cod7o w*ere cod7o=nserted$cod7olbro else be7n raserror ('0a4 enos lbros en stocD de los solctados para la )enta'# # % rollbacD transacton end
?ntonces crea&os el dis$arador #Ecreate tri))erE' d+ndole un no&bre #E>I%ventas%insertarE' sobre #EonE' una tabla es$ec6fica #EventasE' $ara #EforE' el suceso de inserci:n #EinsertE'. Lue)o se EasE coloca&os las sentencias las acciones que el tri))er reali3ar+ cuando se in)rese un re)istro en EventasE #en este caso controlar que 0aya stoc= y dis&inuir el stoc= de ElibrosE'. "uando se activa un dis$arador EinsertE los re)istros se a)re)an a la tabla del dis$arador y a una tabla deno&inada EinsertedE. La tabla EinsertedE es una tabla virtual que contiene una co$ia de los re)istros insertadosD tiene una estructura si&ilar a la tabla en que se define el dis$arador es decir la tabla en que se intenta la acci:n. La tabla EinsertedE )uarda los valores nuevos de los re)istros.
>entro del tri))er se $uede acceder a esta tabla virtual EinsertedE que contiene todos los re)istros insertados es lo que 0ici&os en el dis$arador creado anterior&ente lo que solicita&os es que se le reste al Estoc=E de ElibrosE la cantidad in)resada en el nuevo re)istro de EventasE valor que recu$era&os de la tabla EinsertedE. Erollbac= transactionE es la sentencia que des0ace la transacci:n es decir borra todas las &odificaciones que se $rodujeron en la
1# - Bisparador de borrado (delete tri""er) Bode&os crear un dis$arador $ara que se ejecute sie&$re que una instrucci:n EdeleteE eli&ine datos en una tabla. Sinta8is b+sicaC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA 1or delete as ENTENC!A
Anali3a&os la sinta8isC
Ecreate tri))erE junto al no&bre del dis$aradorD EonE se)uido del no&bre de la tabla $ara la cual se establece el tri))er. Lue)o de EforE se coloca el evento #en este caso EdeleteE' lo que indica que las eli&inaciones sobre la tabla activar+n el tri))er. Lue)o de EasE se es$ecifican las condiciones que deter&inan cuando un intento de eli&inaci:n causa las acciones que el tri))er reali3ar+. ?l dis$arador del si)uiente eje&$lo se crea $ara la tabla EventasE $ara que cada ve3 que se eli&ine un re)istro de EventasE se actualice el ca&$o Estoc=E de la tabla ElibrosE #$or eje&$lo si el co&$rador devuelve los libros co&$rados'C create tr77er "!_)entas_borrar on )entas 1or delete as &pdate lbros set stocD= lbros$stocDdeleted$cantdad 1ro lbros 2on deleted on deleted$cod7olbro=lbros$cod7o;
?ntonces crea&os el dis$arador #Ecreate tri))erE' d+ndole un no&bre #E>I%ventas%borrarE' sobre #EonE' una tabla es$ec6fica #EventasE' $ara #EforE' el evento de borrado #EdeleteE'. Lue)o de EasE coloca&os las sentencias las acciones que el tri))er reali3ar+ cuando se eli&ine un re)istro en EventasE #en este caso au&entar el stoc= de ElibrosE'. "uando se activa un dis$arador EdeleteE los re)istros eli&inados en la tabla del dis$arador se a)re)an a una tabla lla&ada EdeletedE. La tabla EdeletedE es una tabla virtual que conserva una co$ia de los re)istros eli&inadosD tiene una estructura si&ilar a la tabla en que se define el dis$arador es decir la tabla en que se intenta la acci:n. >entro del tri))er se $uede acceder a esta tabla virtual EdeletedE. ?l si)uiente dis$arador se crea $ara controlar que no se eli&ine &+s de un re)istro de la tabla ElibrosE. ?l dis$arador se activa cada ve3 que se eli&ina un re)istro o varios controlando la cantidad de re)istros que se est+n eli&inandoD si se est+ eli&inando &+s de un re)istro el dis$arador retorna un &ensaje de error y des0ace la transacci:nC create tr77er "!_lbros_borrar on lbros 1or delete as 1 (select co&nt(5% 1ro deleted% : be7n raserror('No p&ede borrar Vs de &n lbro'##% rollbacD transacton end;
Si se ejecuta un EdeleteE sobre ElibrosE que afecte a varios re)istros se activa el dis$arador y evita la transacci:n. Si se ejecuta el si)uiente EdeleteE que afecta a un solo re)istro se activa el dis$arador y $er&ite la transacci:nC delete 1ro lbros w*ere cod7o=;
La sentencia Etruncate tableE no $uede incluirse en un dis$arador de borrado #delete tri))er'.
1## - Bisparador de actuali0aci?n (update tri""er) Bode&os crear un dis$arador $ara que se ejecute sie&$re que una instrucci:n Eu$dateE actualice los datos de una tabla. Sinta8is b+sicaC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA 1or &pdate as ENTENC!A
Anali3a&os la sinta8isC Ecreate tri))erE junto al no&bre del dis$aradorD EonE se)uido del no&bre de la tabla $ara la cual se establece el tri))er. Lue)o de EforE se coloca el evento #en este caso Eu$dateE' lo que indica que las actuali3aciones sobre la tabla activar+n el tri))er. Lue)o de EasE se es$ecifican las condiciones y acciones es decir las condiciones que deter&inan cuando un intento de &odificaci:n $rovoca las acciones que el tri))er reali3ar+.
?l si)uiente dis$arador de actuali3aci:n se crea $ara evitar que se &odifiquen los datos de la tabla ElibrosEC create tr77er "!_lbros_act&alar on lbros 1or &pdate as raserror('Los datos de la tabla 8lbros8 no p&eden od1carse'# ,# % rollbacD transacton
?ntonces crea&os el dis$arador #Ecreate tri))erE' d+ndole un no&bre #E>I%libros%actuali3arE' sobre una tabla es$ec6fica #ElibrosE' $ara #EforE' el suceso de actuali3aci:n #Eu$dateE'. Lue)o de EasE coloca&os las sentencias las acciones que el tri))er reali3ar+ cuando se intente actuali3ar uno o varios re)istros en ElibrosE #en este caso i&$edir las &odificaciones'. "uando se ejecuta una instrucci:n Eu$dateE en una tabla que tiene definido un dis$arador los re)istros ori)inales #antes de ser actuali3ados' se &ueven a la tabla virtual EdeletedE y los re)istros actuali3ados #con los nuevos valores' se co$ian a la tabla virtual EinsertedE. >entro del tri))er se $uede acceder a estas tablas. ?n el cuer$o de un tri))er se $uede e&$lear la funci:n Eu$date#ca&$o'E que recibe un ca&$o y retorna verdadero si el evento involucra actuali3aciones #o inserciones' en ese ca&$oD en caso contrario retorna EfalseE. "rea&os un dis$arador que evite que se actualice el ca&$o E$recioE de la tabla ElibrosEC create tr77er "!_lbros_act&alar_preco on lbros 1or &pdate as 1 &pdate(preco% be7n raserror('El preco de &n lbro no p&ede od1carse$'# ,# % rollbacD transacton end;
?&$lea&os Eif u$date#'E $ara que el tri))er controle la actuali3aci:n del ca&$o E$recioED as6 cuando el dis$arador detecte una actuali3aci:n en tal ca&$o reali3ar+ las acciones a$ro$iadas #&ostrar un &ensaje y des0acer la actuali3aci:n'D en caso que se actualice otro ca&$o el dis$arador se activa $ero $er&ite la transacci:n. "rea&os un dis$arador de actuali3aci:n que &uestra el valor anterior y nuevo valor de los re)istros actuali3adosC create tr77er "!_lbros_act&alar on lbros 1or &pdate as 1 (&pdate(tt&lo% or &pdate(a&tor% or &pdate(edtoral%% and not (&pdate(preco% or &pdate(stocD%% be7n select d$cod7o# (d$tt&lo'>' d$a&tor'>'d$edtoral% as 're7stro anteror'# ($tt&lo'>' $a&tor'>'$edtoral% as 're7stro act&alado' 1ro deleted as d 2on nserted as on d$cod7o=$cod7o end else be7n raserror('El preco 4 stocD no p&eden od1carse$ La act&alacn no se real$'# ,# % rollbacD transacton end;
?&$lea&os Eif u$dateE $ara que el tri))er controle si la actuali3aci:n se reali3a en ciertos ca&$os $er&itidos #titulo autor y editorial' y no en los ca&$os $ro0ibidos #$recio y
stoc=''D si se &odifican los ca&$os $er&itidos y nin)uno de los no $er&itidos &ostrar+ los anti)uos y nuevos valores consultando las tablas EdeletedE e EinsertedE en caso que se actualice un ca&$o no $er&itido el dis$arador &uestra un &ensaje y des0ace la transacci:n. @ote que el dis$arador no controla los intentos de actuali3aci:n sobre el ca&$o Ecodi)oE esto es $orque tal ca&$o no $uede &odificarse $orque est+ definido EidentityE si intenta&os &odificarlo S7L Server &uestra un &ensaje de error y el tri))er no lle)a a dis$ararse.
1#' - Bisparadores (varios eventos) Je&os a$rendido a crear dis$aradores $ara diferentes eventos #insert u$date y delete'. >iji&os que un dis$arador $uede definirse $ara &+s de una acci:nD en tal caso deben se$ararse con co&as. "rea&os un tri))er $ara evitar que se inscriban socios que deben &atr6culas y no $er&itir que se eli&inen las inscri$ciones de socios deudores. ?l tri))er se define $ara a&bos eventos en la &is&a sentencia de creaci:n. create tr77er ds_nscrptos_nsert_delete on nscrptos 1or nsert#delete as 1 eFsts (select 51ro nserted 2on orosos on orosos$doc&ento=nserted$doc&ento% be7n raserror('El soco es oroso# no p&ede nscrbrse en otro c&rso'# # % rollbacD transacton end else 1 eFsts (select 51ro deleted 2on orosos on orosos$doc&ento=deleted$doc&ento% be7n raserror('El soco debe atrc&las# no p&ede borrarse s& nscrpcon'# # % rollbacD transacton end else 1 (select atrc&la 1ro nserted%='n' nsert nto orosos select doc&ento 1ro nserted;
?l tri))er controlaC - si se intenta in)resar una inscri$ci:n de un socio &oroso se des0ace la transacci:nD - si se intenta eli&inar una inscri$ci:n de un socio que est+ en E&orososE se des0ace la transacci:nD - si se in)resa una nueva inscri$ci:n y no se $a)a la &atr6cula dic0o socio se in)resa a la tabla E&orososE.
1# - Bisparador (!nstead Off y after) Jasta el &o&ento 0e&os a$rendido que un tri))er se crea sobre una tabla es$ec6fica $ara un evento #inserci:n eli&inaci:n o actuali3aci:n'. Ta&bi9n $ode&os es$ecificar el &o&ento de dis$aro del tri))er. ?l &o&ento de dis$aro indica que las acciones #sentencias' del tri))er se ejecuten lue)o de la acci:n #insert delete o u$date' que dis$ara el tri))er o en lu)ar de la acci:n. La sinta8is $ara ello esC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA o 3!TA MOMENTO"E"!PARO>> a1ter o nstead o1 ACC!ON>> nsert# &pdate o delete as ENTENC!A
?ntonces el &o&ento de dis$aro es$ecifica cuando deben ejecutarse las acciones #sentencias' que reali3a el tri))er. Buede ser Edes$u9sE #after' o Een lu)arE #instead of' del evento que lo dis$ara. Si no es$ecifica&os el &o&ento de dis$aro en la creaci:n del tri))er $or defecto se establece co&o EafterE es decir las acciones que el dis$arador reali3a se ejecutan lue)o del suceso dis$arador. Jasta el &o&ento todos los dis$aradores que crea&os 0an sido EafterE. Los dis$aradores Einstead ofE se ejecutan en lu)ar de la acci:n desencadenante es decir cancelan la acci:n desencadenante #suceso que dis$ar: el tri))er' ree&$la3+ndola $or otras acciones.
5ea&os un eje&$lo. Una e&$resa al&acena los datos de sus e&$leados en una tabla Ee&$leadosE y en otra tabla EclientesE los datos de sus clientes. Se crea una vista que &uestra los datos de a&bas tablasC create )ew )sta_epleados_clentes as select doc&ento#nobre# doclo# 'epleado' as condcon 1ro epleados &non select doc&ento#nobre# doclo#'clente' 1ro clentes;
"rea&os un dis$arador sobre la vista Evista%e&$leados%clientesE $ara inserci:n que redirija las inserciones a la tabla corres$ondienteC create tr77er "!_epleadosclentes_nsertar on )sta_epleados_clentes nstead o1 nsert as nsert nto epleados select doc&ento#nobre#doclo 1ro nserted w*ere condcon='epleado' nsert nto clentes select doc&ento#nobre#doclo 1ro nserted w*ere condcon='clente';
?l dis$arador anterior es$ecifica que cada ve3 que se in)resen re)istros en la vista Evista%e&$leados%clientesE en ve3 de #instead of' reali3ar la acci:n #insertar en la vista' se ejecuten las sentencias del tri))er es decir se in)resen los re)istros en las tablas corres$ondientes. ?ntonces las o$ciones de dis$aro $ueden serC a' EafterEC el tri))er se dis$ara cuando las acciones es$ecificadas #insert delete yo u$date' son ejecutadasD todas las acciones en cascada de una restricci:n Eforei)n =eyE y las co&$robaciones de restricciones Ec0ec=E deben reali3arse con 98ito antes de ejecutarse el tri))er. ?s la o$ci:n $or defecto si sola&ente coloca&os EforE #equivalente a EafterE'. La sinta8is esC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA a1ter \ 1or>> son e&)alentes ACC!ON>> nsert# &pdate o delete as ENTENC!A
b' Einstead ofEC sobreescribe la acci:n desencadenadora del tri))er. Se $uede definir sola&ente un dis$arador de este ti$o $ara cada acci:n #insert delete o u$date' sobre una tabla o vista. Sinta8isC create tr77re NOMBRE"!PARA"OR on NOMBRETABLA o 3!TA nstead o1 ACC!ON>> nsert# &pdate o delete as ENTENC!A
"onsideracionesC - Se $ueden crear dis$aradores Einstead ofE en vistas y tablas. - @o se $uede crear un dis$arador Einstead ofE en vistas definidas E/it0 c0ec= o$tionE.
- @o se $uede crear un dis$arador Einstead of deleteE y Einstead of u$dateE sobre tablas que ten)an una Eforei)n =eyE que es$ecifique una acci:n Eon delete cascadeE y Eon u$date cascadeE res$ectiva&ente. - Los dis$aradores EafterE no $ueden definirse sobre vistas. - @o $ueden crearse dis$aradores EafterE en vistas ni en tablas te&$oralesD $ero $ueden referenciar vistas y tablas te&$orales. - Si e8isten restricciones en la tabla del dis$arador se co&$rueban >?SBU?S de la ejecuci:n del dis$arador Einstead ofE y A@T?S del dis$arador EafterE. Si se infrin)en las restricciones se revierten las acciones del dis$arador Einstead ofED en el caso del dis$arador EafterE no se ejecuta.
1#+ - Bisparador (eliminar) Los tri))ers se eli&inan con la instrucci:n Edro$ tri))erEC drop tr77er NOMBRE"!PARA"OR;
Si el dis$arador que se intenta eli&inar no e8iste a$arece un &ensaje indic+ndolo $ara evitarlo $ode&os verificar su e8istencia antes de solicitar su eli&inaci:n #co&o con cualquier otro objeto'C 1 ob2ect_d('NOMBRE"!PARA"OR'% s not n&ll drop tr77er NOMBRE"!PARA"OR;
?li&ina&os si e8iste el tri))er Edis%libros%insertarEC 1 ob2ect_d('ds_lbros_nsertar'% s not n&ll drop tr77er ds_lbros_nsertar;
"uando se eli&ina una tabla o vista que tiene asociados tri))ers todos los tri))ers asociados se eli&inan auto&+tica&ente.
1#, - Bisparador (informaci?n) Los tri))ers #dis$aradores' son objetos as6 que $ara obtener infor&aci:n de ellos $ueden usarse los si)uientes $rocedi&ientos al&acenados del siste&a y las si)uientes tablasC - Es$%0el$EC sin $ar+&etros nos &uestra todos los objetos de la base de datos seleccionada incluidos los tri))ers. ?n la colu&na EObject%ty$eE a$arece Etri))erE si es un dis$arador. Si le envia&os co&o ar)u&ento el no&bre de un dis$arador obtene&os el $ro$ietario el ti$o de objeto y la fec0a de creaci:n. - Es$%0el$te8tEC se)uido del no&bre de un dis$arador nos &uestra el te8to que define el tri))er e8ce$to si 0a sido encri$tado. - Es$%de$endsEC retorna ! resultadosC 1' el no&bre ti$o ca&$os etc. de los objetos de los cuales de$ende el objeto enviado #referenciados $or el objeto' y !' no&bre y ti$o de los objetos que de$enden del objeto no&brado #que lo referencian'. Bor eje&$lo ejecuta&os Es$%de$endsE se)uido del no&bre de un dis$aradorC sp_depends ds_nscrptos_nsertar;
A$arece una tabla si&ilar a la si)uienteC nae t4pe &pdated col&n >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> dbo$condconales &ser table 4es cod7oc&rso dbo$condconales &ser table 4es 1ec*a dbo$nscrptos &ser table 4es n&eroc&rso dbo$nscrptos &ser table 4es 1ec*a dbo$condconales &ser table 4es doc&ento dbo$c&rsos &ser table no n&ero dbo$c&rsos &ser table no cantdadaFa dbo$nscrptos &ser table 4es doc&ento
?n la colu&na Ena&eE nos &uestra las tablas #y de&+s objetos si 0ubiese' de las cuales de$ende el tri))er es decir las tablas referenciadas en el &is&oD el ti$o de objeto en la colu&na Ety$eE #en este caso todas tablas'D la colu&na Eu$dateE indica si el objeto es actuali3ado o no #note que la tabla EcursosE no se actuali3a sola&ente se consulta'D la colu&na Ecolu&nE &uestra el no&bre del ca&$o que se referencia. @o a$arecen objetos que de$enden del tri))er $orque no e8iste nin)
a$arecen los objetos que de$enden de ella #que la referencian'. ?n este eje&$loC 1 solo objeto su no&bre y ti$o #tri))er'. @o a$arecen objetos de los cuales de$ende $orque la tabla no los tiene. - Bara conocer los dis$aradores que 0ay en una tabla es$ec6fica y sus acciones res$ectivas $ode&os ejecutar el $rocedi&iento del siste&a Es$%0el$tri))erE se)uido del no&bre de la tabla o vista. Bor eje&$loC sp_*elptr77er nscrptos;
@os &uestra la si)uiente infor&aci:nC
tr77er_nae tr77er_owner s&pdate sdelete snsert sa1ter snsteado1 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ds_nscrptos_nsertar dbo , , ,
?l no&bre del tri))er su $ro$ietarioD en las ( colu&nas si)uientes indica $ara qu9 evento se 0a definido #un valor 1 indica que est+ definido $ara tal evento'D las !
1#. - Bisparador (modificar) Los tri))ers $ueden &odificarse y eli&inarse. Al &odificar la definici:n de un dis$arador se ree&$la3a la definici:n e8istente del dis$arador $or la nueva definici:n. La sinta8is )eneral es la si)uienteC alter tr77er NOMBRE"!PARA"OR N-E3A"E
Asu&iendo que 0e&os creado un dis$arador lla&ado Edis%e&$leados%borrarE que no $er&it6a eli&inar &+s de 1 re)istro de la tabla e&$leadosD altera&os el dis$arador $ara que ca&bia la cantidad de eli&inaciones $er&itidas de 1 a (C alter tr77er ds_epleados_borrar on epleados 1or delete as 1 (select co&nt(5% 1ro deleted%:+>>antes era be7n raserror('No p&ede borrar as de + epleados'## % rollbacD transacton end;
Se $uede ca&biar el evento del dis$arador. Bor eje&$lo si cre: un dis$arador $ara EinsertE y lue)o se &odifica el evento $or Eu$dateE el dis$arador se ejecutar+ cada ve3 que se actualice la tabla.
1' - disparador (des&abilitar y &abilitar) Se $uede des0abilitar o 0abilitar un dis$arador es$ec6fico de una tabla o vista o todos los dis$aradores que ten)a definidos. Si se des0abilita un dis$arador 9ste si)ue e8istiendo $ero al ejecutar una instrucci:n EinsertE Eu$dateE o EdeleteE en la tabla no se activa. Sinta8is $ara des0abilitar o 0abilitar un dis$aradorC alter table NOMBRETABLA ENABLE \ "!ABLE tr77er NOMBRE"!PARA"OR;
?l si)uiente eje&$lo des0abilita un tri))erC alter table epleados dsable tr77er ds_epleados_borrar;
Se $ueden des0abilitar #o 0abilitar' varios dis$aradores en una sola sentencia se$arando sus no&bres con co&as. ?l si)uiente eje&$lo des0abilita&os dos tri))ers definidos sobre la tabla e&$leadosC alter table epleados
dsable tr77er ds_epleados_act&alar# ds_epleados_nsertar;
Sinta8is $ara 0abilitar #o des0abilitar' todos los dis$aradores de una tabla es$ec6ficaC alter table NOMBRETABLA ENABLE \ "!ABLE TR!//ER all;
La si)uiente sentencia 0abilita todos los tri))ers de la tabla Ee&$leadosEC alter table epleados enable tr77er all;
1'1 - Bisparador (*it& encryption) Jasta el &o&ento 0e&os a$rendido que un tri))er se crea sobre una tabla #o vista' es$ecificando el &o&ento de ejecuci:n #after o instead of' $ara un evento #inserci:n eli&inaci:n o actuali3aci:n'. Bode&os encri$tar los tri))ers $ara evitar que sean le6dos con Es$%0el$te8tE. Bara ello debe&os a)re)ar al crearlos la o$ci:n E/it0 encry$tionE lue)o del no&bre de la tabla o vistaC create tr77re NOMBRE"!PARA"OR on NOMBRETABLAo3!TA wt* encr4pton MOMENTO"E"!PARO>>a1ter o nstead o1 ACC!ON>> nsert# &pdate# delete as ENTENC!A
?l si)uiente dis$arador se crea encri$tadoC create tr77er "!_epleados_nsertar on epleados wt* encr4pton a1ter nsert as
1 (select seccon 1ro nserted%='/erenca' be7n raserror('No p&ede n7resar epleados en la seccn 8/erenca8$'# # % rollbacD transacton end;
Si ejecuta&os el $rocedi&iento al&acenado del siste&a Es$%0el$te8tE se)uido del no&bre del tri))er creado anterior&ente S7L Server &ostrar+ un &ensaje indicando que tal dis$arador 0a sido encri$tado.
1'2 - Bisparador (condicionales) Una instrucci:n EinsertE Eu$dateE o EdeleteE que invoque a un dis$arador $uede afectar a varios re)istros. ?n tales casos un tri))er rec0a3a o ace$ta cada transacci:n de &odificaci:n co&o una totalidad. Bode&os o$tar $orC 1' $rocesar todos los re)istrosC todos los re)istros afectados deber+n cu&$lir los criterios del dis$arador $ara que se $rodu3ca la acci:n o !' $er&itir acciones condicionalesC $uede definir un dis$arador que controle si cada re)istro afectado cu&$le con la condici:nD si al)
5ea&os un eje&$lo. Tene&os la tabla ElibrosE. "rea&os un dis$arador de actuali3aci:n sobre la tabla ElibrosE. Se $er&ite actuali3ar el stoc= de varios libros a la ve3D $ero nin)
@o $ode&os revertir la transacci:n con Erollbac= transactionE $orque en ese caso TO>OS los re)istros &odificados volver6an a los valores anteriores y lo que necesita&os es que sola&ente aquellos que quedaron con valor ne)ativo vuelvan a su valor ori)inal. Ta&$oco $ode&os evitar que se actualicen todos los re)istros $orque se actuali3an antes que las acciones del tri))er se ejecuten. Lo que 0ace&os es en el cuer$o
CO55/$68
Una cl+usula que se $uede a$licar a una definici:n de base de datos o una definici:n de colu&na $ara definir el cotejo o una e8$resi:n de cadena de caracteres $ara a$licar una conversi:n de intercalaci:n. Syntax Sintaxis COLLATE ]9 collaton_nae : \ database_default ^ 9 collaton_nae : JJ = ] Windows_collation_name ^ \ ] SQL_collation_name ^
Argumentos
?s el no&bre de la intercalaci:n que se a$lica a la e8$resi:n la definici:n de la colu&na o la definici:n de base de datos. ?s ?l @o&bre de la intercalaci:n 7ue se ABLI"A al estilo de ?8$resi:n la >efinici:n de la "olu&na o la >efinici:n de la 2ase de >atos. s:lo $uede ser un _indo/s%collation%na&e es$ecificado o S7L%collation%na&e uno. c0arset S`LO ou can servicios sin _indo/s%collation%na&e es$ecificado o S7L%collation%na&e O@U. Eindo*s_collation_name
?s el no&bre de intercalaci:n de la intercalaci:n de _indo/s. ?s ?l @o&bre de intercalaci:n de la intercalaci:n de _indo/s. 5er los no&bres de intercalaci:n de _indo/s. 5er los @o&bres de intercalaci:n de _indo/s. S45_collation_name
?s el no&bre de intercalaci:n de una intercalaci:n de S7L. ?s ?l @o&bre de intercalaci:n de Una intercalaci:n de S7L. 5er los no&bres de intercalaci:n de S7L. 5er los @o&bres de intercalaci:n de S7L. database_default
Jace que la cl+usula "OLLAT? 0erede la intercalaci:n de la base de datos actual. Jace 7ue la clausula "OLLAT? 0erede la intercalaci:n de la 2ase de >atos actual. Comentarios Comentarios
La cl+usula "OLLAT? se $uede es$ecificar en varios niveles incluyendo los si)uientesC 1F Crear o modificar una base de datosF
Buede utili3ar la cl+usula "OLLAT? de la instrucci:n "R?AT? >ATA2AS? o ALT?R >ATA2AS? $ara es$ecificar la intercalaci:n $redeter&inada de la base de datos. ou can utili3ar la clausula "OLLAT? de la Instrucci:n "R?AT? >ATA2AS? o ALT?R >ATA2AS? $+rrafo ?s$ecificar la intercalaci:n $redeter&inada de la 2ase de >atos. Ta&bi9n se $uede es$ecificar una intercalaci:n al crear una base de datos utili3ando S7L Server ?nter$rise Mana)er. Also Se Buede ?s$ecificar Una intercalaci:n al "rear Una 2ase de >atos utili3ando S7L Server ?nter$rise Mana)er. Si no es$ecifica nin)una intercalaci:n la base de datos se le asi)na la intercalaci:n $redeter&inada de la instancia de S7L Server. Si no ?s$ec6fica @in)una intercalaci:n la base de >atos S? L? ASI@A la intercalaci:n $redeter&inada de la Instancia de S7L Server. 2F Crear o modificar una columna de la tablaF
Usted $uede es$ecificar intercalaciones $ara cada colu&na de cadena de caracteres &ediante la cl+usula "OLLAT? de la instrucci:n "R?AT? TA2L? o ALT?R TA2L?. Usted ou can ?s$ecificar intercalaciones $+rrafo "ada "olu&na de la "adena de "ARA"T?R?S M?>IA@T? la clausula "OLLAT? de la Instrucci:n "R?AT? TA2L? o ALT?R TA2L?. Ta&bi9n se $uede es$ecificar una intercalaci:n al crear una tabla utili3ando S7L Server ?nter$rise Mana)er. Also Se Buede ?s$ecificar Una intercalaci:n al "rear Una tabla utili3ando S7L Server ?nter$rise Mana)er. Si no es$ecifica nin)una intercalaci:n la colu&na se le asi)na la intercalaci:n $redeter&inada de la base de datos. Si no 0ay @in)una ?s$ec6fica intercalaci:n La "olu&na S? L? ASI@A la intercalaci:n $redeter&inada de la 2ase de >atos. Ta&bi9n $uede utili3ar la o$ci:n database%default de la cl+usula "OLLAT? $ara es$ecificar que una colu&na en una tabla te&$oral utilice la intercalaci:n $redeter&inada de la base de datos del usuario actual $ara la cone8i:n en lu)ar de te&$db. ou can also utili3ar la O$ci:n database%default de la clausula "OLLAT? $+rrafo ?s$ecificar 7ue Una "olu&na en Una tabla te&$oral utilice la intercalaci:n $redeter&inada de la 2ase de >atos del Usuario actual $+rrafo de La "one8i:n en Lu)ar de te&$db. F Convertir la intercalaci?n de una e=presi?nF
Buede utili3ar la cl+usula "OLLAT? $ara convertir una e8$resi:n de caracteres en una intercalaci:n concreta. ou can utili3ar la clausula "OLLAT? $+rrafo convertir Una ?8$resi:n de "ARA"T?R?S en Una intercalaci:n "O@"R?TA. "aracteres literales y las variables se les asi)na la intercalaci:n $redeter&inada de la base de datos actual. "aracteres literales y variables las S? L?S ASI@A la intercalaci:n $redeter&inada de la 2ase de >atos actual. Referencias a las colu&nas se les asi)na la intercalaci:n de definici:n de la colu&na. Referencias uno las "olu&nas S? L?S ASI@A la intercalaci:n de >efinici:n de la "olu&na. Bara la reco$ilaci:n de una e8$resi:n vea Brioridad de intercalaci:n. Bara la Reco$ilaci:n de Una ?8$resi:n vea Brioridad de intercalaci:n.
La cl+usula "OLLAT? s:lo se $uede a$licar $ara el c&ar varc&ar te=t nc&ar nvarc&ar y nte=t ti$os de datos. Las intercalaciones se identifican )eneral&ente $or un no&bre de intercalaci:n. T0e e8ce$tion is in Setu$ /0ere you do not s$ecify a collation na&e for _indo/s collations but instead s$ecify t0e collation desi)nator and t0en select c0ec= bo8es to s$ecify binary sortin) or dictionary sortin) t0at is eit0er sensitive or insensitive to eit0er case or accents. La e8ce$ci:n es en el $ro)ra&a de instalaci:n en la que no se es$ecifica un no&bre de intercalaci:n de las intercalaciones de _indo/s sino que es$ecifique el desi)nador de intercalaci:n y a continuaci:n seleccione las casillas de verificaci:n $ara es$ecificar la ordenaci:n binaria o de un orden de diccionario que se sea sensible o insensible a cualquiera de los casos o los acentos. Buede ejecutar la funci:n del siste&a fn_&elpcollations $ara recu$erar una lista de todos los no&bres de intercalaci:n v+lidos $ara intercalaciones de _indo/s y S7LC ELECT 5
Broble&aC on es$codcar COLLATE "ATABAE_"E
Conflicto de intercalaci?n
5er+ este &ensaje cuando se ejecuta una consulta en S7L Server tate K# Lne Cannot resol)e t*e collaton con1lct between 8XL_Latn_/eneral_CP_C!_A8 and 8XL_Latn_/eneral_Pre1_CP_C!_A8 n t*e e&al to operaton$
La ra3:n es que se est+ consultando dos bases de datos diferentes cada una de ellas tiene un conjunto de intercalaci:n diferente. ou &i)0t be co&$arin) colu&ns in a _J?R? clause KOI@in) t/o tables usin) results fro& functions etc. ?s $osible que se co&$aran las colu&nas de una cl+usula _J?R? unir dos tablas utili3ando los resultados de las funciones etc La soluci:n es aadir un indicador de colaci:n a su consulta. LetHs say youHre tryin) to u$date a table A in >atabase 1 based on t0e values of a colu&n in table 2 in >atabase !C >i)a&os que usted est+ tratando de actuali3ar una tabla de una base de datos en una base en los valores de una colu&na en el cuadro 2 de la base de datos !C -P"ATE A ET col&n = B$col&n
/967O 1 - $//GO CO9 ;6C
Estilos de fecha y hora
"uando e8$ression es un ti$o de datos de fec0a u 0ora style $uede ser uno de los valores que se &uestran en la tabla si)uiente. Otros valores se $rocesan co&o 4. S7L Server ad&ite el for&ato de fec0a en estilo +rabe utili3ando el al)orit&o Zu/aiti.
;o&as de convertir datos en S7L S?R5?R "ASTC -E p&bs ELECT CAT(ttle A c*ar(,%%# 4td_sales
"O@5?RTC -E p&bs ELECT CON3ERT(c*ar(,%# ttle%# 4td_sales
?n este eje&$lo se &uestra "O@5?RT con el $ar+&etro style. ELECT CON3ERT(c*ar(%# /ET"ATE(%# +% ,,I>,>, ,,,,J,,,, >>>
,?,?,I
s&bstrn7(con)ert(c*ar(%# rr**$ant7&edad$1ec_nco# %#,#% ,,I>,>, ,,,,J,,,, >>>
,,I,,
"OLLAT? Una cl+usula que se $uede a$licar a una definici:n de base de datos o a una definici:n de colu&na $ara definir la intercalaci:n o una e8$resi:n de cadena de caracteres $ara a$licar a una conversi:n de intercalaci:n. Sinta8is "OLLAT? N collation%na&e database%default N collation%na&e CC G _indo/s%collation%na&e S7L%collation%na&e Ar)u&entos collation%na&e ?s el no&bre de la intercalaci:n que se va a a$licar a la e8$resi:n la definici:n de colu&na o la definici:n de base de datos. La intercalaci:n es$ecificada en collation%na&e s:lo $uede ser una _indo/s%collation%na&e o una S7L%collation%na&e es$ecificada. _indo/s%collation%na&e ?s el no&bre de intercalaci:n de la intercalaci:n de _indo/s. "onsulte @o&bres de intercalaci:n de _indo/s. S7L%collation%na&e ?s el no&bre de intercalaci:n de la intercalaci:n S7L. "onsulte @o&bres de intercalaci:n S7L. database%default Jace que la cl+usula "OLLAT? 0erede la intercalaci:n de la base de datos actual. Observaciones La cl+usula "OLLAT? se $uede es$ecificar en varios niveles incluidos los si)uientesC "rear o alterar una base de datos. Buede utili3ar la cl+usula "OLLAT? de la instrucci:n "R?AT? >ATA2AS? o
ALT?R >ATA2AS? $ara es$ecificar la intercalaci:n $redeter&inada de la base de datos. Ta&bi9n $uede es$ecificar una intercalaci:n al crear una base de datos &ediante el Ad&inistrador cor$orativo de S7L Server. Si no es$ecifica nin)una intercalaci:n se asi)na a la base de datos la intercalaci:n $redeter&inada de la instancia de S7L Server. "rear o alterar una colu&na de una tabla. Buede es$ecificar intercalaciones $ara cada colu&na de cadena de caracteres &ediante la cl+usula "OLLAT? de la instrucci:n "R?AT? TA2L? o ALT?R TA2L?. Ta&bi9n $uede es$ecificar una intercalaci:n al crear una tabla &ediante el Ad&inistrador cor$orativo de S7L Server. Si no es$ecifica nin)una intercalaci:n se asi)na a la colu&na la intercalaci:n $redeter&inada de la base de datos. Ta&bi9n $uede utili3ar la o$ci:n database%default de la cl+usula "OLLAT? $ara es$ecificar que una colu&na de una tabla te&$oral utili3a la intercalaci:n $redeter&inada de la base de datos del usuario actual $ara la cone8i:n en ve3 de usar te&$db. "onvertir la intercalaci:n de una e8$resi:n. Buede utili3ar la cl+usula "OLLAT? $ara convertir una e8$resi:n de caracteres en una intercalaci:n concreta. La intercalaci:n $redeter&inada de la base de datos actual se asi)na a los literales y las variables de car+cter. La intercalaci:n de definici:n de la colu&na se asi)na a las referencias de colu&nas. Bara intercalar una e8$resi:n consulte Brecedencia de intercalaci:n. La intercalaci:n de un identificador de$ende del nivel en que est+ definido. Se asi)na a los identificadores de objetos de instancia co&o los inicios de sesi:n y los no&bres de base de datos la intercalaci:n $redeter&inada de la instancia. Se asi)na a los identificadores de objetos de una base de datos co&o no&bres de tablas vistas y colu&nas la intercalaci:n $redeter&inada de la base de datos. Bor eje&$lo es $osible crear dos tablas con no&bres que s:lo se diferencian en las &ay
de c:di)os co&$atible con el siste&a o$erativo del equi$o. >ic0as acciones $ueden incluirC ?s$ecificar una intercalaci:n $redeter&inada $ara una base de datos durante su creaci:n. ?s$ecificar una intercalaci:n $ara una colu&na durante la creaci:n o la alteraci:n de una tabla. "uando se restaura o ane8a una base de datos la intercalaci:n $redeter&inada de la base de datos y la intercalaci:n de las colu&nas c0ar varc0ar y te8t o los $ar+&etros de la base de datos deben ser co&$atibles con el siste&a o$erativo. Las traducciones de $+)inas de c:di)os se ad&iten $ara los ti$os de datos c0ar y varc0ar $ero no $ara el ti$o de datos te8t. >ata loss durin) code $a)e translations is not re$orted. Si la intercalaci:n es$ecificada o la intercalaci:n que utili3a el objeto de referencia usa una $+)ina de c:di)os que no es co&$atible con los siste&as o$erativos _indo/s S7L Server devuelve un error. Bara obtener &+s infor&aci:n consulte la secci:n Intercalaciones en el ca$6tulo Arquitectura de S7L Server de los Libros en $antalla de S7L Server.
Se tiene que tener cuidado con el &anejo de esta $ro$iedad ya que si se requiere 0acer una consulta entre dos tablas y utili3ar un dter&inado ca&$o y este no contiene el &is&o ti$o la consulta &arcara un error ?je&$loC Muestra todas las base de datos con su collate. S?L?"T na&e collation%na&e ;ROM sys.databases ?l collation se es$ecifica a nivel de colu&na al crear la tabla $ara colu&nas de ti$o caracter. ?n caso de que no indiques el collation el collation asi)nado $or defecto es el es$ecificado al crear la base de datos. Bor lo tanto a
Bara ca&biar el collation de una base de datos utili3a alter databaseC ":di)oC ALTER "ATABAE Base_de_"atos COLLATE l_Latn_/eneral_CP_C!_A
"onsidera que objetos de$endientes del esque&a de la base de datos #co&o constraints' deben ser eli&inado antes y recreados des$ues de ejecutar la sentencia anterior. ?l efecto de esta sentencia es que nuevos objetos creados usar+n $or defecto el collation es$ecificado. Los objetos e8istentes no ca&bian su collation.