Facultativa Triiger Funcionesd y Desencadenadore

7/25/2019 Facultativa Triiger Funcionesd y Desencadenadore

1/24

Facultativa I

Clase Practica de Transac -SQL

Ing.Juan Ramon Osabas s.


2/24

Cursores en Transact SQL

Un cursor es una variable que nos permite recorrer con unconjunto de resultados obtenido a travs de una

sentencia SELECT fla a fla.

Cuando trabajemos con cursores debemos seguir los

siguientes pasos.

eclarar el cursor! utili"ando DECLARE

#brir el cursor! utili"ando OPE

Leer los datos del cursor! utili"ando !ETC"... ITO

Cerrar el cursor! utili"ando CLOSE

Liberar el cursor! utili"ando DEALLOCATE

$


3/24

La creacin y utilizacin de un cursor estar compuesta, como es de

esperarse, por una serie de instrucciones T-SQL, las cuales podrn

separarse en grupos bien diferenciados, los cuales son: Declaracin,

pertura, cceso a datos, !ierre y Desalo"o, a continuacin detallaremoscada grupo de instrucciones#

$ecorrido del cursor y acceso a los datos

En Este paso constar% de recorrer los resultados del cursor! la

instrucci&n 'ETC(permitir% e)ectuar dic*a operaci&n. Las flas le+das podr%n copiarse a variables utili"ando la sentencia

,-Ten combinaci&n con la sentencia 'ETC(! por ejemplo lasentencia

#lmacenes / 0iner+a de atos 1


4/24

Sinta2is de un Curso 33 eclaraci&n del cursor

DECLARE4nombre5cursor6C#RSOR

!OR

4sentencia5sql6

33 apertura del cursor

OPE4nombre5cursor6

33 Lectura de la primera fla del cursor

!ETC"4nombre5cursor6ITO4lista5variables6

$"ILE788'ETC(5ST#TUS9:;

%E&I

33 Lectura de la siguiente fla de un cursor

!ETC"4nombre5cursor6ITO4lista5variables6

ED33 'in del bucle


5/24

'ETC( -E>T '?0 @rod,n)o ,-T 8escriptionTomar% la siguiente fla de resultados del cursor / lo alojar% enla variable 8escription.88'ETC(5ST#TUS 7Transact3SAL;evuelve el estado de la Bltima instrucci&n 'ETC( de cursoremitida para cualquier cursor abierto en ese momento por la

cone2i&n.Los valores que devuelve son los siguientes: 9 La instrucci&n 'ETC( se ejecut& correctamente.3D 9 La instrucci&n 'ETC( no se ejecut& correctamente.3$ 9 'alta la fla recuperada.

#lmacenes / 0iner+a de atos


6/24

Ejemplo de cursor

# continuaci&n juntaremos todos los pasos descriptospreviamente / crearemos el ejemplo m%s simple de un cursor elcual recorrer% la tabla @roduction.@roductescription de la basede datos #dventure


7/24

LOCALEspecifca que el %mbito del cursor es local para elproceso por lotes! procedimiento almacenado o

desencadenador en que se cre& el cursor.

ra el proceso por lotes! procedimientoalmacenado o desencadenador en

que se cre& el cursor.&LO%ALEspecifca que el %mbito del cursor es global para la cone2i&n.@uede *acerse re)erencia al nombre del cursor en cualquierprocedimiento almacenado o proceso por lotes que se ejecute

eDECLAREcClientes C#RSORJLI#L!ORSELECT ,d! -ombre! #pellidoD!

#pellido$! -i)Ci)! '2-acimiento

!RO'CL,E-TES n la cone2i&n.

DECLAREcClientes C#RSORLC#L !OR

SELECT ,d! -ombre! #pellidoD!

#pellido$! -i)Ci)! '2-acimiento

!RO'CL,E-TES

#lmacenes / 0iner+a de atos


8/24

'uncionesSAL Server proporciona al usuario la posibilidad de defnir sus propias )unciones!conocidad como U' 7user defned )unctions;

Sinta(is

CREATE!#CTIO4Scalar5'unction5-ame! s)sname! 'unction-ame6

7

33 Lista de par%metros

48@aramD! s)sname! 8pD6 4ata5T/pe5'or5@aramD! ! int6! ...

;33 Tipo de datos que devuelve la )unci&n.

RET#RS4'unction5ata5T/pe! !int6

AS

%E&I

...

ED

#lmacenes / 0iner+a de atos M


9/24

Ejemplo

CREATE!#CTIO)n50ultiplicaSaldo

7

8-umCuenta *ARC"AR7$:;! 80ultiplicador DECI'AL7D:!$;

;

RET#RSDECI'AL7D:!$;

AS

%E&I DECLARE8Saldo DECI'AL7D:!$;!

8?eturn DECI'AL7D:!$;

SELECT8Saldo 9 S#L

!RO'CUE-T#S

$"ERE-U0CUE-T# 9 8-umCuenta

SET8?eturn 9 8Saldo N 80ultiplicador

RET#R8?eturn

ED#lmacenes / 0iner+a de atos O


10/24

Ejecuci&n de la )unci&n

dbo.)n50ultiplicaSaldo7 -U0CUE-T#! ,CUE-T#; AS?ESULT#

!RO'CUE-T#S

#lmacenes / 0iner+a de atos D:

PN


11/24

PN Esta )uncion busca la tres cuentas con ma/or saldo / obtiene los tresBltimos movimientos de cada una de estas cuentas

CREATE!#CTIO)n5Cuenta0ovimietos7; RET#RS8datos TA%LE

7 33 Estructura de la tabla que devuelve la )uncion.

-umCuenta +arc,ar7$:;! Saldo decimal7D:!$;!

Saldo5anterior decimal7D:!$;!

Saldo5posterior decimal7D:!$;!

,mporte50ovimiento decimal7D:!$;!

'20ovimiento datetime

; AS

%E&I

33 Qariables necesarias para la l&gica de la )uncion.

DECLARE8idcuenta int!

8numcuenta +arc,ar7$:;!

8saldo decimal7D:!$; 33 Cursor con las 1 cuentas de ma/or saldo

DECLAREC#TS C#RSOR!OR

SELECTTOP1 ,CUE-T#! -U0CUE-T#! S#L !RO'CUE-T#S ORDER%S#L DESC

OPEC#TS

!ETC"C#TS ITO8idcuenta! 8numcuenta! 8saldo DD


12/24

33 ?ecorremos el cursor

$"ILE788'ETC(5ST#TUS 9 :;

%E&I

33 ,nsertamos la cuenta en la variable de salida

ISERTITO8datos7-umCuenta! Saldo;*AL#ES78numcuenta! 8saldo;33 ,nsertamos los tres Bltimos movimientos de la cuenta

ISERTITO8datos7Saldo5anterior! Saldo5posterior! ,mporte50ovimiento!'20ovimiento ;

SELECTTOP1 S#L5#-TE?,?! S#L5@STE?,?! ,0@?TE!'>0Q,0,E-T

!RO'0Q,0,E-TS$"ERE,CUE-T# 9 8idcuenta

ORDER%'>0Q,0,E-T DESC

33 Qamos a la siguiente cuenta

!ETC"C#TS ITO8idcuenta! 8numcuenta! 8saldo

ED

CLOSEC#TSR

DEALLOCATEC#TSR

RET#R

ED

D$


13/24

resultado

#lmacenes / 0iner+a de atos D1


14/24

Ejemplo C?E#? U-# 'U-C,- AUE ?ET?-E S, U- CL,E-TE ES 0?S

C?E#TE 'U-CT,- 0?S78'EC(#@#J#TET,0E;

?ETU?-SQ#?C(#?7$:;

#S

IEJ,-

ECL#?E 8'EC(#CL,E-TE#TET,0E

ECL#?E 80E-S#EQ#?C(#?7$:;

SELECT 8'EC(#CL,E-TE9'EC(# '?0 0Q,0,E-T


15/24

Ejemplo ,ncrementar Sueldo de los Empleados con mas de 1 #os de Servicio aun$:V mas

C?E#TE @?CEU?EUS@5E0@LE#S5#U0E-T#?SUEL

#S

ECL#?E 8'EC(# #TET,0E ECL#?E 8,' ,-T

SELECT 8'EC(#9 'EC(#,-J?ES '?0 E0@LE#S

SET 8,'9 WE#?7JET#TE7;; 3WE#?78'EC(#; di)erencia en aos

,' 0-T(7JET#TE7;; 40-T(78'EC(#; di)erencia de mese

SET 8, '9 8,'3D

,' 0-T(7JET#TE7;; 9 0-T(78'EC(#; #- #W7JET#TE7;; 4#W78'EC(#;

SET 8,'98,'3D

,' 78,'6 91;

U@#TEE0@LE#SSETSUEL9 7SUELX 7SUELN:.$:;;

J

D


16/24

33 Creamos la Iase de atos

Create atabase 0/CursorI

33 Seleccionamos la Iase de atos

use 0/CursorI

33 Creamos la Tabla Empreado

create table Empleado7,d int identit/7D!D; primar/ Fe/

!-ombre varc*ar7$:; not null

!#pellido varc*ar7$:; not null

!-ombreCompleto as #pellidoX! X-ombre

!e0ail varc*ar7=:; null

!'ec*a?eg date de)ault Jetdate7;

;

33 ,nsertamos los ?egistros a la tabla Empleado

insert into Empleado7-ombre! #pellido; values 7ose!Lope";

insert into Empleado7-ombre! #pellido; values 7#ndres!0artine";

insert into Empleado7-ombre! #pellido; values 7oel!#lon"o;

insert into Empleado7-ombre! #pellido; values 7Lemuel!#lon"o;

insert into Empleado7-ombre! #pellido; values 7Jabriel!-ina;

insert into Empleado7-ombre! #pellido; values 7@riscila!?ui";

select N )rom Empleadompleado

#lmacenes / 0iner+a de atos DK


17/24

#lmacenes / 0iner+a de atos D

Declare@IDintdeclare@Nomvarchar(20)declare@Apevarchar(20)

DeclareMyCursorscrollcursorfor

selectId,Nomre,Apellidofrom!mpleado

"penMyCursor#etchne$tfromMyCursorinto@ID,@Nom,@Ape%&I'!@@#!C&*A+*0-!.INupdate!mpleadoseteMail'"%!/(*+-*/IN.(@Nom,,)1@Ape1@midominio3com)4hereid @Id

#etchne$tfromMyCursorinto@ID,@Nom

,@Ape!ND

CloseMyCursorDeallocateMyCursor56


18/24

Ejemplo @r%cticos

#lmacenes / 0iner+a de atos DM

Ejemplo


19/24

Creando la bases de datos

use mastergo

i) 7e2ists 7select -#0E )rom s/s.databases Y*ere name 9labCursores;;

drop database labCursores

Jo

create database labCursores

go

use labCursores

go

DO


20/24

Creando la tablas

createtablecategoria(

idCategoria intprimarykeynotnull,nombreCategoria varchar(50) notnull,)goinsertintocategoria values(1, 'Computadoras')insertintocategoria values(2, 'Accesorios')o

createtableproductos(codigo intprimarykeynotnull,producto varchar(50) notnull,precio !oney notnull,idCategoria intnotnull"oreignkey(idCategoria) re"erencescategoria,)

o

insertintoproductos values(1, '#aptop', 2500$00, 1)insertintoproductos values(2, '!ouse', 50$00, 2)insertintoproductos values(%, '&arlantes', 0$00, 2)insertintoproductos values(, 'Audi"onos', 20$00, 2)go

$:


21/24

!reando el respaldo

%!&'( DT%S) labCursoresT* D+S& ):.labCursores #ba/0+T1 2*$3T4

5*

#lmacenes / 0iner+a de atos $D


22/24

-- Crear un cursor que permita listar los productos

declarecursor& cursor"orselectcodigo, producto, precio "romproductosopencursor&declarecod int, pro varchar(50), pre money

"etchne*t"romcursor&intocod, pro, pre+hile "etchstatus-0beginprint 'Codigo .'/ cast(cod asvarchar(10))print 'producto .'/ proprint '&recio .'/ cast(pre asvarchar(10))"etchne*t"romcursor& intocod,pro,prend

closecursor&deallocatecursor&

$$


23/24

-- Crear un cursor ue /ermita mostrar los /roductos decada categor0a.

declare cursorC@cursor

)or select idCategoria! nombreCategoria )rom categoria

pen cursorC@

declare 8cat int! 8nom varc*ar7:;

)etc* ne2t )rom cursorC@ into 8cat!8nom

Y*ile 88)etc*5status9:

begin

print Categoria X 8nom

print 9999999999999999999999999999999999

declare cursorC@D cursor

)or select codigo! producto! precio )rom productos Y*ere idCategoria98cat

open cursorC@Ddeclare 8codpro int!8prod varc*ar7:; !8prec mone/

)etc* ne2t )rom cursorC@D into 8codpro! 8prod! 8prec

Y*ile 88)etc*5status9:

begin

print Codigo X cast78codpro as varc*ar7D:;;

print @roducto X 8prod

print @recio X cast78prec as varc*ar7D:;;

)etc* ne2t )rom cursorC@D into 8codpro! 8prod! 8prec

end

close cursorC@D

deallocate cursorC@D

)etc* ne2t )rom cursorC@ into 8cat!8nom

end

close cursorC@

deallocate cursorC@go

#lmacenes / 0iner+a de atos $1


24/24

#lmacenes / 0iner+a de atos $=

Documents

Facultativa Triiger Funcionesd y Desencadenadore