Download - 7090112 Clase Transact Sql Server
Transact SQL ServerTransact SQL Server
Ingeniería Informática IPPIngeniería Informática IPP
Identificadores de objetosIdentificadores de objetosde base de datosde base de datos
[[[ server[[[ server..][ database]][ database]..][ owner]][ owner]..] database_object] database_object] ] Server:Server:
– El nombre del servidorEl nombre del servidorE E Database:Database:
– Nombre de la base de datos que gestiona el servidorNombre de la base de datos que gestiona el servidor Owner:Owner:
– Es el propietario de ese objetoEs el propietario de ese objeto Database_object:Database_object:
– El nombre del objeto de la base de datosEl nombre del objeto de la base de datos
Tipos de datosTipos de datos
Los tipos de datos definidos por los usuarios Los tipos de datos definidos por los usuarios se definen comose definen como
siguensiguens s Exec sp_addtype Phone, varchar(20), Exec sp_addtype Phone, varchar(20), 'NOT NULL''NOT NULL'' ' Exec sp_addtype typPostalCode, Exec sp_addtype typPostalCode, varchar(7), 'NULL' -- in Canadavarchar(7), 'NULL' -- in Canadav v Exec sp_addtype Exec sp_addtype
Es el nombre del procedimiento almacenado Es el nombre del procedimiento almacenado que nos permite definir un tipo de datos que nos permite definir un tipo de datos Phone ó typPostalCode es el nombre del tipo Phone ó typPostalCode es el nombre del tipo de datos que estamos creando. de datos que estamos creando.
Variables localesVariables locales
El ámbito de las VL es el procedimiento El ámbito de las VL es el procedimiento almacenado donde se declaranalmacenado donde se declaran
a a Se definen así:Se definen así:– Declare @LastName varchar(50)Declare @LastName varchar(50)– Declare @LastName varchar(50),Declare @LastName varchar(50),@FirstName varchar(30),@FirstName varchar(30),@BirthDate smalldatetime@BirthDate smalldatetime
@ @ Variables definidas según tipo de datos Variables definidas según tipo de datos construido por el usuarioconstruido por el usuario– Declare @OfficePhone phoneDeclare @OfficePhone phone
Variables localesVariables locales
La asignación de valores se realiza con La asignación de valores se realiza con la sentenciala sentencia
SELECTSELECTS S Select @LastName = 'Smith'Select @LastName = 'Smith'S S Se pueden realizar varias asignaciones a Se pueden realizar varias asignaciones a
la vezla vezl l Select @LastName = 'Smith', Select @LastName = 'Smith', @FirstName = 'David',@FirstName = 'David', @BirthDate = '2/21/1965'@BirthDate = '2/21/1965'
Variables localesVariables locales
V V Declare @make varchar(50), @model varchar(50), Declare @make varchar(50), @model varchar(50), @EqType varchar(50)@EqType varchar(50)@ @ Select @Make = 'ACME', @Model = 'Turbo', Select @Make = 'ACME', @Model = 'Turbo', @EqType = 'cabadaster'@EqType = 'cabadaster'@ @ Select @Make = make, @Model = Model, Select @Make = make, @Model = Model, @EqType = EqType.EqType@EqType = EqType.EqType@ @ From EqType INNER JOIN EquipmentFrom EqType INNER JOIN EquipmentF F ON EqType.EqTypeId = Equipment.EqTypeIdON EqType.EqTypeId = Equipment.EqTypeIdO O Where EquipmentId = -1Where EquipmentId = -1W W Select @make make, @model model, @EqType Select @make make, @model model, @EqType
EqTypeEqType
Variables localesVariables locales
V V SQL Server permite asignar valores SQL Server permite asignar valores a las variablesa las variables
dentro de una sentencia de dentro de una sentencia de actualizaciónactualización
aa Update InventoryUpdate Inventory
Set @mnsCost = Cost = Cost * @fltTaxRateSet @mnsCost = Cost = Cost * @fltTaxRate Where InventoryId = @intInventoryIdWhere InventoryId = @intInventoryId
Variables GlobalesVariables Globales
No las definimosNo las definimosN N El servidor las mantiene por nosotrosEl servidor las mantiene por nosotrosE E @@identity@@identity– – Almacena el código interno de identificación de un Almacena el código interno de identificación de un
registroregistro– – Almacena el último código de la última inserciónAlmacena el último código de la última inserciónA A @@error@@error– – 0 = No existe error0 = No existe error– – Otro valor = Indica el código del errorOtro valor = Indica el código del errorO O @@rowcount@@rowcount– – Número de registros afectados en la última Número de registros afectados en la última
sentencia.sentencia.
Variables de tipo tablaVariables de tipo tabla
Declare @MyTableVar table (Id int primary Declare @MyTableVar table (Id int primary key, Lookupkey, Lookup
varchar(15))varchar(15))v v Insert @MyTableVar values (1, '1Q2000')Insert @MyTableVar values (1, '1Q2000')I I Insert @MyTableVar values (2, '2Q2000')Insert @MyTableVar values (2, '2Q2000')I I Insert @MyTableVar values (3, '3Q2000')Insert @MyTableVar values (3, '3Q2000')I I Select * from @MyTableVarSelect * from @MyTableVar
Variables de tipo tablaVariables de tipo tabla
Las VT solo pueden aparecer como parte de las sentencias Select, Las VT solo pueden aparecer como parte de las sentencias Select, Update,Update,
U U Delete, Insert, y Cursores.Delete, Insert, y Cursores.D D No pueden aparecer en sentencias SELECT de inserción No pueden aparecer en sentencias SELECT de inserción
Select...Into:Select...Into:Select LookupId, LookupSelect LookupId, LookupInto @TableVariable -- wrongInto @TableVariable -- wrongFrom LookupFrom Lookup
F F No puede aparecer en sentencias de inserción que utilicen No puede aparecer en sentencias de inserción que utilicen procedimientosprocedimientos
alamcenados:alamcenados:Insert into @TableVariable -- wrongInsert into @TableVariable -- wrongExec prMyProcedureExec prMyProcedure
E E A diferencia de las tablas temporales, las VT tienen un alcance A diferencia de las tablas temporales, las VT tienen un alcance local. Sololocal. Solo
pueden ser utilizados en el ámbito donde fueron creadas.pueden ser utilizados en el ámbito donde fueron creadas.p p Los cursores basados en VT tienen un alcance localLos cursores basados en VT tienen un alcance localL L Las VT no son objetos persistentes y no se pueden deshacer sus Las VT no son objetos persistentes y no se pueden deshacer sus
cambioscambioscon una sentencia Rollbackcon una sentencia Rollback
Procedimientos de AlmacenadoProcedimientos de Almacenado
Son procedimientos que se encuentran compilados y almacenados en el DBMS.Pueden realizar operaciones de mantenimiento, actualización,inserción, eliminación y consulta.Pueden recibir par´ametros para realizar sus tareas.Son excelentes para mantener seguridad, encapsulamiento y para aumentar la eficiencia de las aplicaciones que funcionan coninteracción con DBMS.
Procedimientos de AlmacenadoProcedimientos de Almacenado
Para crear un procedimiento almacenado usamos create.almacenados en el DBMS.
create procedure miprocedimiento @parametro1 tipo1, ...
asbegin...end
Un procedimiento no tiene un valor de retorno, pero puede producir resultado en la forma de una o varias consultas.
Restricciones de Integridad
Hasta ahora los tipos de restricciones de integridad que hemosvisto usan un modelo estático, dependen de valores constantes.Son controladas cada vez que el elemento asociado cambia (en cualquier forma).Hemos usado:
• Restricciones de unicidad – llaves primarias.• Restricciones referenciales – llaves for´aneas.• Restricciones de dominio – formatos de strings, rangos devalores, etc.
Las restricciones que hemos visto, son impuestas por el diseñador de la BD y ejecutadas por el DBMS.
Sentencias de control de flujoSentencias de control de flujo
S S IfIfI I WhileWhileW W BreakBreakB B ContinueContinueC C GoToGoToG G WaitForWaitFor
Sentencias de control de flujoSentencias de control de flujoIFIF
I I Esta sentencia cambia el flujo de ejecuciónEsta sentencia cambia el flujo de ejecuciónE E If boolean_expressionIf boolean_expressionI I { Transact-SQL_statement | statement_block}{ Transact-SQL_statement | statement_block}{ { [else[else[ [ { Transact-SQL_statement | statement_block}]{ Transact-SQL_statement | statement_block}]{ { El valor True=Cierto=Verdadero=1El valor True=Cierto=Verdadero=1E E El valor False=Falso=0El valor False=Falso=0E E Si el valor es cierto ejecuta el primer bloque de Si el valor es cierto ejecuta el primer bloque de
sentencias, sino el segundosentencias, sino el segundo
EjemploEjemplo WHILE @@FETCH_STATUS = 0WHILE @@FETCH_STATUS = 0 BEGINBEGIN
UPDATE MON_MONITOREOUPDATE MON_MONITOREO SET CANTIDAD = isnull(CANTIDAD,0) + 1 SET CANTIDAD = isnull(CANTIDAD,0) + 1 WHERE CAMP_NOMBRE = @CAMPANAWHERE CAMP_NOMBRE = @CAMPANA
------ AND AND CODEJECUTIVO = @CODEJEC CODEJECUTIVO = @CODEJEC AND INDICE AND INDICE = @INDICE = @INDICE
IF @CORTIPOVTA = 'TIT' IF @CORTIPOVTA = 'TIT' BEGIN BEGIN
UPDATE MON_MONITOREOUPDATE MON_MONITOREO SET TITULAR = isnull(TITULAR,0) + 1 SET TITULAR = isnull(TITULAR,0) + 1 WHERE CAMP_NOMBRE = @CAMPANAWHERE CAMP_NOMBRE = @CAMPANA
--- --- AND AND CODEJECUTIVO = @CODEJEC CODEJECUTIVO = @CODEJEC AND INDICE AND INDICE = @INDICE = @INDICE END END
IF @CORTIPOVTA = 'ADI' IF @CORTIPOVTA = 'ADI' BEGIN BEGIN
UPDATE MON_MONITOREOUPDATE MON_MONITOREO SET ADICIONAL = isnull(ADICIONAL,0) + 1 SET ADICIONAL = isnull(ADICIONAL,0) + 1 WHERE CAMP_NOMBRE = @CAMPANAWHERE CAMP_NOMBRE = @CAMPANA
------ AND AND CODEJECUTIVO = @CODEJEC CODEJECUTIVO = @CODEJEC AND INDICE AND INDICE = @INDICE = @INDICE END END
FETCH NEXT FROM ventas FETCH NEXT FROM ventas INTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTAINTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTA
END END CLOSE ventas CLOSE ventas DEALLOCATE ventasDEALLOCATE ventas
Sentencias de control de flujoSentencias de control de flujoIF con subconsultasIF con subconsultas
I I Otra forma de utilizar las consultasOtra forma de utilizar las consultasO O If [NOT] Exists(subquery)If [NOT] Exists(subquery)II {Transact-SQL_statement | {Transact-SQL_statement |
statement_block}statement_block}s s [else[else[ [ {Transact-SQL_statement | {Transact-SQL_statement |
statement_block}]statement_block}]
Sentencias de control de flujoSentencias de control de flujoWhileWhile
While Boolean_expressionWhile Boolean_expression{sql_statement | statement_block}{sql_statement | statement_block}[Break][Break]{sql_statement | statement_block}{sql_statement | statement_block}[Continue][Continue]{sql_statement | statement_block}{sql_statement | statement_block}{Resto de instrucciones}{Resto de instrucciones}
{ { True = 1 False = 0True = 1 False = 0T T Break. El servidor interrumpe el bucle y salta a la siguienteBreak. El servidor interrumpe el bucle y salta a la siguienteinstrucción después del bucle. {Resto de instrucciones}instrucción después del bucle. {Resto de instrucciones}i i Continue. El servidor vuelve inmediatamente a la guarda Continue. El servidor vuelve inmediatamente a la guarda
del bucle,del bucle,ignorando el resto de instrucciones por ejecutar después de laignorando el resto de instrucciones por ejecutar después de lasentencia continue. Boolean_expressionsentencia continue. Boolean_expression
Sentencias de control de flujoSentencias de control de flujoEjemplo de la instrucción WhileEjemplo de la instrucción While
Create Procedure prCalcFactorialCreate Procedure prCalcFactorial-- calculate factorial-- calculate factorial-- 1! = 1-- 1! = 1-- 3! = 3 * 2 * 1-- 3! = 3 * 2 * 1-- n! = n * (n-1)* . . . 5 * 4 * 3 * 2 * 1-- n! = n * (n-1)* . . . 5 * 4 * 3 * 2 * 1
@N tinyint,@N tinyint,@F int OUTPUT@F int OUTPUTAsAsSet @F = 1Set @F = 1while @N > 1while @N > 1beginbegin
set @F = @F * @Nset @F = @F * @NSet @N = @N - 1Set @N = @N - 1
endendreturn 0return 0
CursoresCursores
conjuntos de datosconjuntos de datosLas aplicaciones de usuario estan Las aplicaciones de usuario estan
deseñadas para mostrar conjuntosdeseñadas para mostrar conjuntosde datos por registrosde datos por registrosLos cursores unen estos dos enfoquesLos cursores unen estos dos enfoquesTenemos tres tipos de cursores en SQL Tenemos tres tipos de cursores en SQL
ServerServer– – Client cursorsClient cursors– – API Server cursorsAPI Server cursors– – Transact-SQL cursorsTransact-SQL cursors
CursoresCursoresTransact-SQL vs Resto de cursoresTransact-SQL vs Resto de cursores
La finalidad de los cursores es su La finalidad de los cursores es su principal diferenciaprincipal diferencia
p p Transact-SQL se utiliza en:Transact-SQL se utiliza en:– – Los procedimientos almacenadosLos procedimientos almacenados– – Los procesos por lotesLos procesos por lotes– – Las funcionesLas funciones– – Los disparadoresLos disparadoresL L El resto de cursores se utilizan desde El resto de cursores se utilizan desde
las aplicaciones de loslas aplicaciones de losusuarios (normalmente en forma usuarios (normalmente en forma
embebida)embebida)
CursoresCursoresTransact-SQLTransact-SQL
T T Pasos a seguir en la definición de un cursor en un Pasos a seguir en la definición de un cursor en un procedimientoprocedimiento
almacenadoalmacenadoa a 1. Utilizar la sentencia “Declare Cursor” para crear el cursor1. Utilizar la sentencia “Declare Cursor” para crear el cursor1 1 2. Llamar a la sentencia “Open” para activar el cursor2. Llamar a la sentencia “Open” para activar el cursor2 2 3. Utilizar la sentencia “Fetch” para:3. Utilizar la sentencia “Fetch” para:
– – recuperar los valores a los que apunta el cursorrecuperar los valores a los que apunta el cursor– – cambiar la posición del puntero del cursorcambiar la posición del puntero del cursor
c c 4. Ahora utilizamos los valores leidos para nuestros fines4. Ahora utilizamos los valores leidos para nuestros fines4 4 5. Si es necesario, se repiten los pasos 3 y 45. Si es necesario, se repiten los pasos 3 y 45 5 6. Cerramos el cursor para6. Cerramos el cursor para
– – desbloquear los datosdesbloquear los datos– – liberar memorialiberar memoria– – etcetce e 7. Eliminamos definitivamente el cursor7. Eliminamos definitivamente el cursor
Cursores. EjemploCursores. EjemploDefinimos los parámetros y las variablesDefinimos los parámetros y las variables
CREATE PROCEDURE dbo.SP_SUMAVENTASEASI (@FECHAINI AS VARCHAR(10), @FECFIN AS VARCHAR(10))CREATE PROCEDURE dbo.SP_SUMAVENTASEASI (@FECHAINI AS VARCHAR(10), @FECFIN AS VARCHAR(10))AS AS BEGIN BEGIN
SET NOCOUNT OFF SET NOCOUNT OFF
DECLARE @CAMPANA VARCHAR(20)DECLARE @CAMPANA VARCHAR(20)DECLARE @CODEJEC VARCHAR(10)DECLARE @CODEJEC VARCHAR(10)DECLARE @INDICE INTEGERDECLARE @INDICE INTEGERDECLARE @CORTIPOVTA CHAR(10)DECLARE @CORTIPOVTA CHAR(10)
DECLARE ventas CURSOR FOR DECLARE ventas CURSOR FOR SELECT camp_tmk, cod_agente, indice2, cor_tipopersonaSELECT camp_tmk, cod_agente, indice2, cor_tipopersona FROM MON_VENTASEASIFROM MON_VENTASEASI WHERE fec_coti between @FECHAINI and @FECFINWHERE fec_coti between @FECHAINI and @FECFIN
AND cod_estado in ('000','PND','PRP')AND cod_estado in ('000','PND','PRP')
OPEN ventasOPEN ventas
FETCH NEXT FROM ventas FETCH NEXT FROM ventas INTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTAINTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTA WHILE @@FETCH_STATUS = 0WHILE @@FETCH_STATUS = 0 BEGINBEGIN
UPDATE MON_MONITOREOUPDATE MON_MONITOREO SET CANTIDAD = isnull(CANTIDAD,0) + 1 SET CANTIDAD = isnull(CANTIDAD,0) + 1 WHERE CAMP_NOMBRE = @CAMPANAWHERE CAMP_NOMBRE = @CAMPANA
------ AND AND CODEJECUTIVO = @CODEJEC CODEJECUTIVO = @CODEJEC AND INDICE AND INDICE = @INDICE = @INDICE
IF @CORTIPOVTA = 'TIT' IF @CORTIPOVTA = 'TIT' BEGIN BEGIN
UPDATE MON_MONITOREOUPDATE MON_MONITOREO SET TITULAR = isnull(TITULAR,0) + 1 SET TITULAR = isnull(TITULAR,0) + 1 WHERE CAMP_NOMBRE = @CAMPANAWHERE CAMP_NOMBRE = @CAMPANA
--- --- AND AND CODEJECUTIVO = @CODEJEC CODEJECUTIVO = @CODEJEC AND INDICE AND INDICE = @INDICE = @INDICE END END
IF @CORTIPOVTA = 'ADI' IF @CORTIPOVTA = 'ADI' BEGIN BEGIN
UPDATE MON_MONITOREOUPDATE MON_MONITOREO SET ADICIONAL = isnull(ADICIONAL,0) + 1 SET ADICIONAL = isnull(ADICIONAL,0) + 1 WHERE CAMP_NOMBRE = @CAMPANAWHERE CAMP_NOMBRE = @CAMPANA
------ AND AND CODEJECUTIVO = @CODEJEC CODEJECUTIVO = @CODEJEC AND INDICE AND INDICE = @INDICE = @INDICE END END
FETCH NEXT FROM ventas FETCH NEXT FROM ventas INTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTAINTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTA
END END CLOSE ventas CLOSE ventas DEALLOCATE ventasDEALLOCATE ventas
ENDENDGOGO
Otro EjemploOtro Ejemplo
declare @nombre varchar(50)declare @direccion varchar(100)declare departamento_cursor cursor for
select nombre, direccion from departamentoopen departamento_cursorfetch next from departamento_cursor into @nombre, @direccionwhile @@fetch_status = 0begin
print(’Departamento de ’ + @nombre + ’, Direcci´on: ’ + @direccion)fetch next from departamento_cursor into @nombre, @direccion
endclose departamento_cursordeallocate departamento_cursor
TriggersTriggers
No son mas que un caso particular deNo son mas que un caso particular deprocedimiento almacenadoprocedimiento almacenadop p No se pueden ejecutar directamenteNo se pueden ejecutar directamenteN N Se ejecutan al realizarse alguna de Se ejecutan al realizarse alguna de
estasestasoperacionesoperaciones– – Inserción InsertInserción Insert– – Modificación UpdateModificación Update– – Borrado DeleteBorrado Delete
TriggersTriggers
Create Trigger Create Trigger trigger_nametrigger_nameOn { table | view} [With Encryption]On { table | view} [With Encryption]{{{{{For | After | Instead Of} { [Delete] [,] [Insert] [,] [Update] }{For | After | Instead Of} { [Delete] [,] [Insert] [,] [Update] }[With Append][With Append][Not For Replication][Not For Replication]AsAssql_statement [...n]sql_statement [...n]} |} |{(For | After | Instead Of) { [Insert] [,] [Update] }{(For | After | Instead Of) { [Insert] [,] [Update] }[With Append][With Append][Not For Replication][Not For Replication]AsAs{{If Update ( Column)If Update ( Column)[{And | Or} Update ( Column)][{And | Or} Update ( Column)][...n] | If ( Columns_Updated() { bitwise_operator} updated_bitmask)[...n] | If ( Columns_Updated() { bitwise_operator} updated_bitmask){ comparison_operator} column_bitmask [...n] }{ comparison_operator} column_bitmask [...n] }sql_statement [...n]sql_statement [...n]}}}}
Trigger: Create triggerTrigger: Create trigger
Indica al SQL Server que vamos a Indica al SQL Server que vamos a crear uncrear un
objeto en la B.D. del tipo TRIGGERobjeto en la B.D. del tipo TRIGGERo o La B.D. maneja objetos: triggers, La B.D. maneja objetos: triggers,
storestoreprocedures, tables , viewsprocedures, tables , views
Ejemplo de TriggerEjemplo de Triggercreate trigger minimo_salario_supervisoron empleadoafter updateas
if update(salario)begin
update empleadoset salario = 500000where rut in (select rutfrom inserted as i join supervisa_departamento as son s.empleado_rut = i.rutwhere i.salario < 500000 )
end
FuncionesFunciones
Usando bloques de instrucciones se pueden crear funciones de
usuario que pueden usarse en consultas simples.Se usa el comando create function para crearlas y
returnpara el valor de retorno:create function func(@parametro1 tipo1, ...)returns moneyas
begin...return @valor_retorno
end
Funciones ya Construidas
SQLServer al igual que la mayor´ıa de los DBMS comerciales,
provee funciones ya construidas.Funciones para manipular fechas:day, month, year, getdate, dateadd,...Funciones matem´aticas: abs, cos, sin, floor,
ceiling,...Funciones de strings: substring, lower, upper,
trim,...Pueden crearse funciones m´as complejas que usen
estas funcionespara calcular su resultado.