Clase Transact Sql Server

Transact SQL Server Ingeniería Informática IPP

Identificadores de objetos de base de datos

[[[ server.][ database].][ owner].] database_object Server: – El nombre del servidor Database: – Nombre de la base de datos que gestiona el servidor Owner: – Es el propietario de ese objeto Database_object: – El nombre del objeto de la base de datos

Tipos de datos Los tipos de datos definidos por los usuarios se definen como siguen Exec sp_addtype Phone, varchar(20), 'NOT NULL' Exec sp_addtype typPostalCode, varchar(7), 'NULL' -- in Canada Exec sp_addtype Es el nombre del procedimiento almacenado que nos permite definir un tipo de datos Phone ó typPostalCode es el nombre del tipo de datos que estamos creando.

Variables locales El ámbito de las VL es el procedimiento almacenado donde se declaran Se definen así: – Declare @LastName varchar(50) – Declare @LastName varchar(50), @FirstName varchar(30), @BirthDate smalldatetime

Variables definidas según tipo de datos construido por el usuario – Declare @OfficePhone phone

Variables locales

La asignación de valores se realiza con la sentencia SELECT Select @LastName = 'Smith' Se pueden realizar varias asignaciones a la vez Select @LastName = 'Smith', @FirstName = 'David', @BirthDate = '2/21/1965'

Variables locales Declare @make varchar(50), @model varchar(50), @EqType varchar(50) Select @Make = 'ACME', @Model = 'Turbo', @EqType = 'cabadaster' Select @Make = make, @Model = Model, @EqType = EqType.EqType From EqType INNER JOIN Equipment ON EqType.EqTypeId = Equipment.EqTypeId Where EquipmentId = -1 Select @make make, @model model, @EqType EqType

Variables locales

SQL Server permite asignar valores a las variables dentro de una sentencia de actualización Update Inventory Set @mnsCost = Cost = Cost * @fltTaxRate Where InventoryId = @intInventoryId

Variables Globales No las definimos El servidor las mantiene por nosotros @@identity – Almacena el código interno de identificación de un registro – Almacena el último código de la última inserción @@error – 0 = No existe error – Otro valor = Indica el código del error @@rowcount – Número de registros afectados en la última sentencia.

Variables de tipo tabla Declare @MyTableVar table (Id int primary key, Lookup varchar(15)) Insert @MyTableVar values (1, '1Q2000') Insert @MyTableVar values (2, '2Q2000') Insert @MyTableVar values (3, '3Q2000') Select * from @MyTableVar

Variables de tipo tabla Las VT solo pueden aparecer como parte de las sentencias Select, Update, Delete, Insert, y Cursores. No pueden aparecer en sentencias SELECT de inserción Select...Into: Select LookupId, Lookup Into @TableVariable -- wrong From Lookup

No puede aparecer en sentencias de inserción que utilicen procedimientos alamcenados: Insert into @TableVariable -- wrong Exec prMyProcedure

A diferencia de las tablas temporales, las VT tienen un alcance local. Solo pueden ser utilizados en el ámbito donde fueron creadas. Los cursores basados en VT tienen un alcance local Las VT no son objetos persistentes y no se pueden deshacer sus cambios con una sentencia Rollback

Procedimientos 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 con interacción con DBMS.

Procedimientos de Almacenado

Para crear un procedimiento almacenado usamos create. almacenados en el DBMS. create procedure miprocedimiento @parametro1 tipo1, ... as begin ... 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 hemos visto 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 de valores, etc.

Las restricciones que hemos visto, son impuestas por el diseñador de la BD y ejecutadas por el DBMS.

Sentencias de control de flujo

If While Break Continue GoTo WaitFor

Sentencias de control de flujo IF Esta sentencia cambia el flujo de ejecución If boolean_expression { Transact-SQL_statement | statement_block} [else { Transact-SQL_statement | statement_block}] El valor True=Cierto=Verdadero=1 El valor False=Falso=0 Si el valor es cierto ejecuta el primer bloque de sentencias, sino el segundo

Ejemplo

WHILE @@FETCH_STATUS = 0 BEGIN UPDATE MON_MONITOREO SET CANTIDAD = isnull(CANTIDAD,0) + 1 WHERE CAMP_NOMBRE = @CAMPANA --AND CODEJECUTIVO = @CODEJEC AND INDICE = @INDICE

IF @CORTIPOVTA = 'TIT' BEGIN UPDATE MON_MONITOREO SET TITULAR = isnull(TITULAR,0) + 1 WHERE CAMP_NOMBRE = @CAMPANA --AND CODEJECUTIVO = @CODEJEC AND INDICE = @INDICE END

---

IF @CORTIPOVTA = 'ADI' BEGIN UPDATE MON_MONITOREO SET ADICIONAL = isnull(ADICIONAL,0) + 1 WHERE CAMP_NOMBRE = @CAMPANA AND CODEJECUTIVO = @CODEJEC AND INDICE = @INDICE END FETCH NEXT FROM ventas INTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTA

END CLOSE ventas DEALLOCATE ventas

Sentencias de control de flujo IF con subconsultas

Otra forma de utilizar las consultas If [NOT] Exists(subquery) {Transact-SQL_statement | statement_block} [else {Transact-SQL_statement | statement_block}]

Sentencias de control de flujo While While Boolean_expression

{sql_statement | statement_block} [Break] {sql_statement | statement_block} [Continue] {sql_statement | statement_block} {Resto de instrucciones}

True = 1 False = 0 Break. El servidor interrumpe el bucle y salta a la siguiente instrucción después del bucle. {Resto de instrucciones} Continue. El servidor vuelve inmediatamente a la guarda del bucle, ignorando el resto de instrucciones por ejecutar después de la sentencia continue. Boolean_expression

Sentencias de control de flujo Ejemplo de la instrucción While

Create Procedure prCalcFactorial -- calculate factorial -- 1! = 1 -- 3! = 3 * 2 * 1 -- n! = n * (n-1)* . . . 5 * 4 * 3 * 2 * 1 @N tinyint, @F int OUTPUT As Set @F = 1 while @N > 1 begin set @F = @F * @N Set @N = @N - 1

end return 0

Cursores conjuntos de datos Las aplicaciones de usuario estan deseñadas para mostrar conjuntos de datos por registros Los cursores unen estos dos enfoques Tenemos tres tipos de cursores en SQL Server – – –

Client cursors API Server cursors Transact-SQL cursors

Cursores Transact-SQL vs Resto de cursores

La finalidad de los cursores es su principal diferencia Transact-SQL se utiliza en: – Los procedimientos almacenados – Los procesos por lotes – Las funciones – Los disparadores El resto de cursores se utilizan desde las aplicaciones de los usuarios (normalmente en forma embebida)

Cursores Transact-SQL

Pasos a seguir en la definición de un cursor en un procedimiento almacenado

1. Utilizar la sentencia “Declare Cursor” para crear el cursor 2. Llamar a la sentencia “Open” para activar el cursor 3. Utilizar la sentencia “Fetch” para:

– recuperar los valores a los que apunta el cursor – cambiar la posición del puntero del cursor

– – –

4. Ahora utilizamos los valores leidos para nuestros fines 5. Si es necesario, se repiten los pasos 3 y 4 6. Cerramos el cursor para

desbloquear los datos liberar memoria etc 7. Eliminamos definitivamente el cursor

Cursores. Ejemplo Definimos los parámetros y las variables CREATE PROCEDURE dbo.SP_SUMAVENTASEASI (@FECHAINI AS VARCHAR(10), @FECFIN AS VARCHAR(10)) AS BEGIN SET NOCOUNT OFF DECLARE DECLARE DECLARE DECLARE

@CAMPANA VARCHAR(20) @CODEJEC VARCHAR(10) @INDICE INTEGER @CORTIPOVTA CHAR(10)

DECLARE ventas CURSOR FOR SELECT camp_tmk, cod_agente, indice2, cor_tipopersona FROM MON_VENTASEASI WHERE fec_coti between @FECHAINI and @FECFIN AND cod_estado in ('000','PND','PRP') OPEN ventas FETCH NEXT FROM ventas INTO @CAMPANA, @CODEJEC, @INDICE, @CORTIPOVTA WHILE @@FETCH_STATUS = 0 BEGIN UPDATE MON_MONITOREO SET CANTIDAD = isnull(CANTIDAD,0) + 1 WHERE CAMP_NOMBRE = @CAMPANA --AND CODEJECUTIVO = @CODEJEC AND INDICE = @INDICE IF @CORTIPOVTA = 'TIT' BEGIN UPDATE MON_MONITOREO SET TITULAR = isnull(TITULAR,0) + 1 WHERE CAMP_NOMBRE = @CAMPANA --AND CODEJECUTIVO = @CODEJEC AND INDICE = @INDICE END

---

IF @CORTIPOVTA = 'ADI' BEGIN UPDATE MON_MONITOREO SET ADICIONAL = isnull(ADICIONAL,0) + 1 WHERE CAMP_NOMBRE = @CAMPANA AND CODEJECUTIVO = @CODEJEC AND INDICE = @INDICE END

Otro Ejemplo declare @nombre varchar(50) declare @direccion varchar(100) declare departamento_cursor cursor for

select nombre, direccion from departamento open departamento_cursor fetch next from departamento_cursor into @nombre, @direccion while @@fetch_status = 0 begin print(’Departamento de ’ + @nombre + ’, Direcci´on: ’ + @direccion) fetch next from departamento_cursor into @nombre, @direccion end close departamento_cursor deallocate departamento_cursor

Triggers

No son mas que un caso particular de procedimiento almacenado No se pueden ejecutar directamente Se ejecutan al realizarse alguna de estas operaciones – Inserción Insert – Modificación Update – Borrado Delete

Triggers Create Trigger trigger_name On { table | view} [With Encryption] { { {For | After | Instead Of} { [Delete] [,] [Insert] [,] [Update] } [With Append] [Not For Replication] As sql_statement [...n] }| {(For | After | Instead Of) { [Insert] [,] [Update] } [With Append] [Not For Replication] As { If Update ( Column) [{And | Or} Update ( Column)] [...n] | If ( Columns_Updated() { bitwise_operator} updated_bitmask) { comparison_operator} column_bitmask [...n] } sql_statement [...n] } }

Trigger: Create trigger

Indica al SQL Server que vamos a crear un objeto en la B.D. del tipo TRIGGER La B.D. maneja objetos: triggers, store procedures, tables , views

Ejemplo de Trigger create trigger minimo_salario_supervisor on empleado after update as if update(salario) begin

end

update empleado set salario = 500000 where rut in ( select rut from inserted as i join supervisa_departamento as s on s.empleado_rut = i.rut where i.salario < 500000 )

Funciones

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 return para el valor de retorno: create function func(@parametro1 tipo1, ...) returns money as 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 funciones para calcular su resultado.