T-SQL

(1)

T-SQL

(2)

Transact - SQL



Hand On LAB de Sql Server Managment Studio



Introducción a Transact SQL



Programación con Transact SQL



Fundamentos de Transact SQL

o Primeros pasos con Transact SQL o Scripts y lotes.



Tipos de datos en Transact SQL

o Tipos de datos numéricos.

o Tipos de datos de caracter. o Tipos de datos de fecha. o Tipos de datos binarios. o Tipo de datos XML. o Otros tipos de datos.

o Tipos de datos personalizados.



Variables en Transact SQL

o Declarar variables es Transact SQL o Asignar variables en Transact SQL



Equivalencia de datos de SQL Server y .NET



Operadores en Transact SQL



Estructuras de control en Transact SQL

o Estructura IF

o Estructura CASE o Bucle WHILE o Estructura GOTO



Control de errores en Transact SQL

o Uso de TRY CATCH

o Funciones especiales de Error o La variable de sistema @@ERROR o Generar un error con RAISERROR



Consultar datos en Transact SQL

o La sentencia SELECT o La cláusula WHERE o La cláusula ORDER BY



Consultas agregadas

o La cláusula GROUP BY o La cláusula HAVING o AVG o Count o Max, Min o Sum

o Uso de Select TOP con consultas agregadas



Select FOR XML

o Clausula FOR XML. o Campos y variables XML.

(3)

o UNION o EXCEPT o INTERSECT



Insertar datos en Transact SQL

o Inserción individual de filas.

o Insertción múltiple de filas. o Inserción de valores por defecto. o Clausula OUTPUT



Actualizar datos en Transact SQL

o Update

o Update INNER JOIN o Clausula OUTPUT



Borrar datos en Transact SQL

o Delete

o Clausula OUTPUT o Truncate Table



Transacciones en Transact SQL

o Concepto de transaccion

o Transacciones implicitas y explicitas o Transacciones anidadas.

o Puntos de recuperacion



Procedimientos almacenados en Transact SQL



Funciones en Transact SQL

o Funciones escalares o Funciones en linea

o Funciones en línea de multiples sentencias



Funciones integradas de Transact SQL (I)

o Cast y Convert o Isnull o COALESCE o GetDate y GetUTCDate



Triggers en Transact SQL

o Trigger DML o Trigger DDL



Cursores en Transact SQL



SQL dinámico en Transact SQL

o La instrucción comando EXECUTE

(4)

Hands on LAB de SQL Server Managment Studio

2005

 Inicar SSMS (Sql Server Managment Studio)

(5)

 Interface Gráfica de SQL Server Management Studio

1. Seleccionar la Base de Datos 2. Abrir el Servidor conectado

3. Desplegar la base de Datos NORTHWIND 4. Desplegar los Objetos Ejm. Tablas

5. Desplegar las características de Tabla (COLUMNAS) 6. Escribir el Script (“Conjunto de Ordenes T-SQL”) 7. Ventana de resultado de la consulta

(6)

Modelo de Datos de la Base de Datos NorthWind

Orders = Cabecera de Ordenes Orders Details = Detalle de Ordenes Customers = Clientes

Employees = Empleados Products = Productos Categories = Categorias

Observar las relaciones que apunta en dirección de (PK=Primary Key) y (FK=Foreign Key) esta son de gran utilidad para nuestro caso, ya que en el cursos empleamos las uniones JOIN no servirán como referencia

(7)

Ejercicios corridos con las estructuras de la base de datos northwind

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.COMPANYNAME, C.CONTACTNAME,

E.LASTNAME+' '+E.FIRSTNAME AS EMPLEADO, P.PRODUCTNAME,

X.CATEGORYNAME, OD.QUANTITY, OD.UNITPRICE,

(OD.QUANTITY * OD.UNITPRICE) AS TOTAL FROM ORDERS O

INNER JOIN [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID INNER JOIN CUSTOMERS C

ON O.CUSTOMERID=C.CUSTOMERID INNER JOIN EMPLOYEES E

ON O.EMPLOYEEID=E.EMPLOYEEID INNER JOIN PRODUCTS P

ON OD.PRODUCTID=P.PRODUCTID INNER JOIN CATEGORIES X

(8)

Vista de los resultados

DECLARE @TABLA TABLE(CODIGO VARCHAR(5),EMPRESA VARCHAR(50),MES INT, MONTO MONEY)

INSERT INTO @TABLA

SELECT C.CUSTOMERID AS CODIGO,

C.COMPANYNAME AS EMPRESA, MONTH(ORDERDATE) AS MES, SUM(O.FREIGHT) AS MONTO FROM ORDERS O INNER JOIN CUSTOMERS C

ON O.CUSTOMERID = C.CUSTOMERID WHERE YEAR(ORDERDATE)=1997

GROUP BY C.CUSTOMERID,C.COMPANYNAME,MONTH(ORDERDATE); WITH RES AS (

SELECT * FROM @TABLA

PIVOT(SUM(MONTO) FOR MES IN( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT) SELECT CODIGO,EMPRESA, ISNULL([1],0.0) AS [ENE], ISNULL([2],0.0) AS [FEB], ISNULL([3],0.0) AS [MAR], ISNULL([4],0.0) AS [ABR], ISNULL([5],0.0) AS [MAY], ISNULL([6],0.0) AS [JUN], ISNULL([7],0.0) AS [JUL], ISNULL([8],0.0) AS [AGO], ISNULL([9],0.0) AS [SET], ISNULL([10],0.0) AS [OCT], ISNULL([11],0.0) AS [NOV], ISNULL([12],0.0) AS [DIC] FROM RES ORDER BY EMPRESA

(9)

Transact SQL 2005

Este documento describe las nuevas características de T-SQL para SQL Server 2005.

Introducción

Microsoft Sql Server 2005 (Code Name = YUKON) es un producto con muchas novedades comparándolo con su antecesor SQL 2000. Sin duda que uno de sus mayores cambios ha sido la inclusión del CLR dentro del motor de base de datos.

Transact SQL (TSQL) es el lenguaje que usamos para escribir: Store Procedures – Triggers – Querys – Etc.

SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación.

Transact SQL es el lenguaje de programación que proporciona SQL Server para ampliar SQL con los elementos característicos de los lenguajes de programación: variables, sentencias de control de flujo, bucles ...

Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. Transact SQL es el lenguaje de programación que proporciona SQL Server para extender el SQL estándar con otro tipo de instrucciones.

Transact SQL

existe desde las primeras versiones de SQL Server, si bien a lo largo de este tutorial nos centraremos en la versión

SQL Server 2005

.

¿Qué vamos a necesitar?

Para poder seguir este tutorial correctamente necesitaremos tener los siguientes elementos:  Un servidor SQL Server 2005. Podemos descargar gratuitamente la versión SQL Server

Express desde el siguiente enlace.

http://www.microsoft.com/downloads/details.aspx?familyid=220549B5-0B07-4448-8848-DCC397514B41&displaylang=es

 Herramientas cliente de SQL Server. Recomendamos:

http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=es

(10)

Programación con Transact SQL

Introducción

SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación. No permite el uso de variables, estructuras de control de flujo, bucles ... y demás elementos característicos de la programación. No es de

extrañar, SQL es un lenguaje de consulta, no un lenguaje de programación.

Sin embargo, SQL es la herramienta ideal para trabajar con bases de datos. Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. Transact SQL es el lenguaje de programación que proporciona Microsoft SQL Server para extender el SQL estándar con otro tipo de instrucciones y elementos propios de los lenguajes de programación.

Con Transact SQL vamos a poder programar las unidades de programa de la base de datos

SQL Server, están son:

 Procedimientos almacenados  Funciones

 Triggers  Scripts

Pero además Transact SQL nos permite realizar programas sobre las siguientes herramientas de SQL Server:

(11)

Fundamentos de Transact SQL

Primeros pasos con Transact SQL

Para programar en Transact SQL es necesario conocer sus fundamentos.

Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje.

 Transact SQL no es CASE-SENSITIVE, es decir, no diferencia mayúsculas de minúsculas como otros

lenguajes de programación como C o Java.

 Un comentario es una aclaración que el programador incluye en el código. Son soportados 2 estilos de comentarios, el de línea simple y de multilínea, para lo cual son empleados ciertos caracteres especiales como son:

o -- Para un comentario de línea simple o /* ... */ Para un comentario de varias lineas

 Un literal es un valor fijo de tipo numérico, carácter, cadena o lógico no representado por un identificador (es un valor explícito).

 Una variable es un valor identificado por un nombre (identificador) sobre el que podemos realizar modificaciones. En

Transact SQL

los identificadores de variables deben comenzar por el carácter @, es decir, el nombre de una variable debe comenzar por @.Para declarar variables en

Transact SQL

debemos utilizar la palabra clave

declare

, seguido del identificador y tipo de datos de la variable

.

Veamos algunos ejemplos:

-- Esto es un comentario de línea simple

/*

Este es un comentario con varias líneas. Conjunto de Líneas.

*/

declare @nombre varchar(50)-- declare declara una variable

-- @nombre es el identificador de la -- variable de tipo varchar

set @nombre = 'www.Microsoft.com' -- El signo = es un operador

-- www.Microsoft.com es un literal

print @Nombre -- Imprime por pantalla el valor de @nombre.

(12)

Scripts y lotes.

Un script de

Transact SQL

es un conjunto de sentencias de

Transact SQL

en formato de texto plano que se ejecutan en un servidor de

SQL Server

.

Un script está compuesto por uno o varios lotes. Un lote delimita el alcance de las variables y sentencias del script. Dentro de un mismo script se diferencian los diferentes lotes a través de las instrucción GO.

-- Este es el primer lote del script

SELECT * FROM COMENTARIOS

GO

-- GO es el separador de lotes -- Este es el segundo lote del script

SELECT getdate() -- getdate() es una función integrada que devuelve

-- la fecha

En ocasiones es necesario separar las sentencias en varios lotes, porque

Transact SQL

no permite la ejecución de ciertos comandos en el mismo lote, si bien normalmente también se utilizan los lotes para realizar separaciones lógicas dentro del script.

(13)

Tipos de datos en Transact SQL

Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato que indica los

posibles valores. El tipo de datos define el formato de almacenamiento, espacio que de disco-memoria que va a ocupar un campo o variable, restricciones y rango de valores validos.

Transact SQL proporciona una variedad predefinida de tipos de datos . Casi todos los tipos de datos manejados por Transact SQL son similares a los soportados por SQL.

Tipos de datos numéricos.

SQL Server dispone de varios tipos de datos numéricos. Cuanto mayor sea el número que

puedan almacenar mayor será en consecuencia el espacio utilizado para almacenarlo. Como regla general se recomienda usar el tipo de dato mínimo posible. Todos los dato numéricos admiten el valor NULL.

Bit. Una columna o variable de tipo bit puede almacenar el rango de valores de 1 a 0.

Tinyint. Una columna o variable de tipo tinyint puede almacenar el rango de valores de 0 a 255. SmallInt. Una columna o variable de tipo smallint puede almacenar el rango de valores -32768 a

32767.

Int. Una columna o variable de tipo int puede almacenar el rango de valores -231_{a 2}31_{-1 .}

BigInt. Una columna o variable de tipo bigint puede almacenar el rango de valores -263_{a 2}63_{-1 .}

Decimal(p,s). Una columna de tipo decimal puede almacenar datos numéricos decimales sin

redondear. Donde p es la precisión (número total del dígitos) y s la escala (número de valores decimales)

Float. Una columna de datos float puede almacenar el rango de valores -1,79x-10308_a

1,79x-10308,_{, si la definimos con el valor máximo de precisión. La precisión puede variar entre 1 y 53.}

Real. Sinónimo de float(24). Puede almacenar el rango de valores -3,4x-1038_{a 3,4x-10}38,

Money. Almacena valores numéricos monetarios de -263 _{a 2}63_{-1, con una precisión de hasta diez}

milésimas de la unidad monetaria.

SmallMoney. Almacena valores numéricos monetarios de -214.748,3647 a 214.748,3647, con una

precisión de hasta diez milésimas de la unidad monetaria.

Todos los tipos de datos enteros pueden marcarse con la propiedad identity para hacerlos auto numéricos.

(14)

DECLARE @bit bit,

@tinyint tinyint, @smallint smallint, @int int,

@bigint bigint,

@decimal decimal(10,3), -- 10 digitos, 7 enteros y -- 3 decimales @real real, @double float(53), @money money set @bit = 1 print @bit set @tinyint = 255 print @tinyint set @smallint = 32767 print @smallint set @int = 642325 print @int

set @decimal = 56565.234 -- Punto como separador decimal print @decimal

set @money = 12.34 print @money

Tipos de datos de caracter.

Char(n). Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamos

datos en el tipo char, siempre se utilizan los n caracteres indicados, incluso si la entrada de datos es inferior. Por ejemplo, si en un char(5), guardamos el valor 'A', se almacena 'A ', ocupando los cinco bytes.

Varchar(n).Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando

almacenamos datos en el tipo varchar, únicamente se utilizan los caracteres necesarios, Por ejemplo, si en un varchar(255), guardamos el valor 'A', se almacena 'A', ocupando solo un byte bytes.

Varchar(max). Igual que varchar, pero al declararse como max puede almacenar 231_{-1 bytes.}

Nchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable

utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferentes idiomas.

Nvarchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es

recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferentes idiomas.

Nvarchar(max).Igual que varchar, pero al declararse como max puede almacenar 231_{-1 bytes.}

Tipos de datos de fecha.

Datetime. Almacena fechas con una precisión de milisegundo. Debe usarse para fechas muy

(15)

SmallDatetime. Almacena fechas con una precisión de minuto, por lo que ocupa la mitad de

espacio de que el tipo datetime, para tablas que puedan llegar a tener muchos datos es un factor a tener muy en cuenta.

TimeStamp.Se utiliza para marcar un registro con la fecha de inserción - actualización. El tipo

timestamp se actualiza automáticamente cada vez que insertamos o modificamos los datos.

Tipos de datos binarios.

Binary. Se utiliza para almacenar datos binarios de longitud fija, con una longitud máxima de 8000

bytes.

Varbinary. Se utiliza para almacenar datos binarios de longitud variable, con una longitud máxima

de 8000 bytes..Es muy similar a binary, salvo que varbinary utiliza menos espacio en disco.

Varbinary(max).Igual que varbinary, pero puede almacenar 231_{-1 bytes}

Tipo de datos XML.

XML.Una de las grandes mejoras que incorpora SQL Server 2005 es el soporte nativo para XML.

Como podemos deducir, este tipo de datos se utiliza para almacenar XML.

DECLARE @myxml XML

set @myxml = (SELECT @@SERVERNAME NOMBRE FOR XML RAW, TYPE)

print cast(@myxml as varchar(max))

Obtendremos la siguiente salida: <row nombre="SVR01"/>

Otros tipos de datos.

UniqueIdentifier. Se utiliza para identificadores únicos. Para generar identificadores únicos

debemos utilizar la función NEWID().

DECLARE @myuniqueid UNIQUEIDENTIFIER

set @myuniqueid = NEWID()

print cast(@myuniqueid as varchar(36))

Obtendremos la siguiente salida: 46141D79-102C-4C29-A620-792EA0208637

Sql_Variant.Permite almacenar valores de diferentes tipos de datos. No puede almacenar

varchar(max), xml, timestamp y tipos de datos definidos por el usuario.

(16)

Transact SQL permite la creación de tipos de datos personalizados, a través de la instrucción CREATE TYPE. Personalmente, desaconsejo el uso de tipos de datos personalizados.

CREATE TYPE MD5 FROM CHAR(32) NULL

GO

DECLARE @miMD5 MD5

set @miMD5 = '0000000000000000000000000000000A' print @miMD5

(17)

Variables en Transact SQL

A. Declarar variables es Transact SQL

Una variable es un valor identificado por un nombre (identificador) sobre el que podemos realizar modificaciones.

En Transact SQL los identificadores de variables deben comenzar por el carácter @, es decir, el nombre de una variable debe comenzar por @. Para declarar variables en Transact SQL debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable.

-- Esto es un comentario de linea simple

/*

Este es un comentario con varias líneas. Conjunto de Líneas.

*/

declare @nombre varchar(50)-- declare declara una variable

-- @nombre es el identificador de la -- variable de tipo varchar

set @nombre = 'www.Microsoft.com' -- El signo = es un operador

-- www.Microsoft.com es un literal

print @Nombre -- Imprime por pantalla el valor de @nombre.

-- No diferencia mayúsculas ni minúsculas

B. Asignar variables en Transact SQL

En Transact SQL podemos asignar valores a una variable de varias formas:  A través de la instruncción set.

 Utilizando una sentencia SELECT.

 Realizando un FETCH de un cursor.

El siguiente ejemplo muestra como asignar una variable utilizando la instrucción SET.

DECLARE @nombre VARCHAR(100)

-- La consulta debe devolver un único registro

SET @nombre = (SELECT nombre FROM CLIENTES WHERE ID = 1) PRINT @nombre

(18)

El siguiente ejemplo muestra como asignar variables utilizando una sentencia SELECT.

DECLARE @nombre VARCHAR(100),

@apellido1 VARCHAR(100), @apellido2 VARCHAR(100) SELECT @nombre=nombre , @apellido1=Apellido1, @apellido2=Apellido2 FROM CLIENTES WHERE ID = 1 PRINT @nombre PRINT @apellido1 PRINT @apellido2

Un punto a tener en cuenta cuando asignamos variables de este modo, es que si la consulta SELECT devuelve más de un registro, las variables quedarán asignadas con los valores de la última fila devuelta.

(19)

Por último veamos como asignar variables a través de un cursor.

DECLARE @nombre VARCHAR(100),

@apellido1 VARCHAR(100), @apellido2 VARCHAR(100)

DECLARE CDATOS CURSOR FOR

SELECT nombre , Apellido1, Apellido2 FROM CLIENTES

OPEN CDATOS

FETCH CDATOS INTO @nombre, @apellido1, @apellido2

WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @nombre PRINT @apellido1 PRINT @apellido2

FETCH CDATOS INTO @nombre, @apellido1, @apellido2 END

CLOSE CDATOS

DEALLOCATE CDATOS

(20)

Equivalencia de datos de SQL Server y .NET

La siguiente lista muestra los tipos de datos de SQL Server 2005 y sus equivalentes con CRL, para el namespace System.Data.SqlTypes y los tipos nativos de CRL .NET FrameWork

SQL Server CLR data type (SQL Server) CLR data type (.NET _Framework)

varbinary SqlBytes, SqlBinary Byte[] binary SqlBytes, SqlBinary Byte[] varbinary(1),

binary(1) SqlBytes, SqlBinary byte, Byte[]

image ninguno ninguno

varchar ninguno ninguno

char ninguno ninguno

nvarchar(1),

nchar(1) SqlChars, SqlString Char, String, Char[]

nvarchar

SqlChars, SqlString

SQLChars es mejor para la transferencia de datos y SQLString obtiene mejor rendimiento para

operaciones con Strings.

String, Char[]

nchar SqlChars, SqlString String, Char[]

text ninguno ninguno

ntext ninguno ninguno

uniqueidentifier SqlGuid Guid

rowversion ninguno Byte[]

bit SqlBoolean Boolean

tinyint SqlByte Byte

smallint SqlInt16 Int16

int SqlInt32 Int32

bigint SqlInt64 Int64

smallmoney SqlMoney Decimal

money SqlMoney Decimal

numeric SqlDecimal Decimal

decimal SqlDecimal Decimal

real SqlSingle Single

float SqlDouble Double

smalldatetime SqlDateTime DateTime

datetime SqlDateTime DateTime

sql_variant ninguno Object

User-defined

type(UDT) ninguno Misma clase que la definida en el assemblie.

table ninguno ninguno

(21)

timestamp ninguno ninguno

(22)

Operadores en Transact SQL

La siguiente tabla ilustra los operadores de Transact SQL .

Tipo de operador Operadores

Operador de asignación =

Operadores aritméticos + (suma)

- (resta) * (multiplicación) / (división) ** (exponente) % (modulo) Operadores relacionales o de comparación = (igual a) <> (distinto de) != (distinto de) < (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a) !> (no mayor a) !< (no menor a)

Operadores lógicos AND (y lógico) NOT (negacion) OR (o lógico)

& (AND a nivel de bit) | (OR a nivel de bit)

^ (OR exclusivo a nivel de bit)

Operador de

concatenación +

Otros

ALL (Devuelve TRUE si el conjunto completo de comparaciones es TRUE)

ANY(Devuelve TRUE si cualquier elemento del conjunto de comparaciones es TRUE)

BETWEEN (Devuelve TRUE si el operando está dentro del intervalo)

EXISTS (TRUE si una subconsulta contiene filas)

IN (TRUE si el operando está en la lista)

LIKE (TRUE si el operando coincide con un patron)

NOT (Invierte el valor de cualquier operador booleano)

(23)

Estructuras de control en Transact SQL

Estructura condicional IF

La estuctura condicional IF permite evaluar una expresion booleana (resultado SI - NO), y ejecutar las operaciones contenidas en el bloque formado por BEGIN END.

IF (<expresion>) BEGIN ... END ELSE IF (<expresion>) BEGIN ... END ELSE BEGIN ... END

Ejemplo de la estructura condicional IF.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' IF @diminutivo = 'DJK' BEGIN PRINT 'www.microsoft.com' END ELSE BEGIN

PRINT 'Otra Web (peor!)' END

(24)

La estructura IF admite el uso de subconsultas:

DECLARE @coPais int,

@descripcion varchar(255)

set @coPais = 5

set @descripcion = 'España' IF EXISTS(SELECT * FROM PAISES

WHERE CO_PAIS = @coPais) BEGIN

UPDATE PAISES

SET DESCRIPCION = @descripcion WHERE CO_PAIS = @coPais

END

ELSE

BEGIN

INSERT INTO PAISES

(CO_PAIS, DESCRIPCION) VALUES (@coPais, @descripcion)

END

Estructura condicional CASE

La estructura condicional CASE permite evaluar una expresion y devolver un valor u otro. La sintaxis general de case es:

CASE <expresion>

WHEN <valor_expresion> THEN <valor_devuelto> WHEN <valor_expresion> THEN <valor_devuelto> ELSE <valor_devuelto> -- Valor por defecto END

Ejemplo de CASE.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK'

SET @Web = (CASE @diminutivo

WHEN 'DJK' THEN 'www.Microsoft.com' WHEN 'ALM' THEN 'www.aleamedia.com' ELSE 'www.Microsoft.com'

END) PRINT @Web

(25)

Otra sintaxis de CASE nos permite evaluar diferentes expresiones:

CASE

WHEN <expresion> = <valor_expresion> THEN <valor_devuelto> WHEN <expresion> = <valor_expresion> THEN <valor_devuelto> ELSE <valor_devuelto> -- Valor por defecto

END

El mismo ejemplo aplicando esta sintaxis:

SET @Web = (CASE

WHEN @diminutivo = 'DJK' THEN 'www.Microsoft.com' WHEN @diminutivo = 'ALM' THEN 'www.aleamedia.com' ELSE 'www.Microsoft.com'

END) PRINT @Web

Otro aspecto muy interesante de CASE es que permite el uso de subconsultas.

SET @Web = (CASE

WHEN @diminutivo = 'DJK' THEN (SELECT web

FROM WEBS WHERE id=1)

WHEN @diminutivo = 'ALM' THEN (SELECT web

FROM WEBS WHERE id=2)

ELSE 'www.Microsoft.com' END)

(26)

Bucle WHILE

El bucle WHILE se repite mientras expresion se evalue como verdadero. Es el único tipo de bucle del que dispone Transact SQL.

WHILE <expresion> BEGIN

... END

Un ejemplo del bucle WHILE.

DECLARE @contador int SET @contador = 0

WHILE (@contador < 100) BEGIN

SET @contador = @contador + 1

PRINT 'Iteracion del bucle ' + cast(@contador AS varchar)

END

Podemos pasar a la siguiente iteración del bucle utilizando CONTINUE.

DECLARE @contador int SET @contador = 0

WHILE (@contador < 100) BEGIN

IF (@contador % 2 = 0)

CONTINUE

END

El bucle se dejará de repetir con la instrucción BREAK.

DECLARE @contador int SET @contador = 0 WHILE (1 = 1) BEGIN

IF (@contador % 50 = 0)

BREAK

END

(27)

DECLARE @coRecibo int WHILE EXISTS (SELECT * FROM RECIBOS

WHERE PENDIENTE = 'S')-- Ojo, la subconsulta se ejecuta -- una vez por cada iteracion

-- del bucle! BEGIN

SET @coRecibo = (SELECT TOP 1 CO_RECIBO

FROM RECIBOS WHERE PENDIENTE = 'S') UPDATE RECIBOS

SET PENDIENTE = 'N'

WHERE CO_RECIBO = @coRecibo END

Estructura GOTO

La sentencia goto nos permite desviar el flujo de ejecución hacia una etiqueta. Fué muy utilizada en versiones anteriores de SQL Server conjuntamente con la variable de sistema @@ERROR para el control de errores.

Actualmente, se desaconseja el uso GOTO, recomendandose el uso de TRY - CATCH para la gestion de errores.

DECLARE @divisor int, @dividendo int, @resultado int SET @dividendo = 100 SET @divisor = 0

SET @resultado = @dividendo/@divisor

IF @@ERROR > 0 GOTO error

PRINT 'No hay error' RETURN

error:

PRINT 'Se ha producido una division por cero'

(28)

Control de errores en Transact SQL

Uso de TRY CATCH

A partIr de la versión 2005, SQL Server proporciona el control de errores a través de las instrucciónes TRY y CATCH.

Estas nuevas instrucciones suponen un gran paso adelante en el control de errores en SQL

Server, un tanto precario en las versiones anteriores.

La sintaxis de TRY CATCH es la siguiente: BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH

El siguiente ejemplo ilustra el uso de TRY - CATCH.

BEGIN TRY

DECLARE @divisor int , @dividendo int, @resultado int

SET @dividendo = 100 SET @divisor = 0

-- Esta línea provoca un error de división por 0 SET @resultado = @dividendo/@divisor

PRINT 'No hay error' END TRY

BEGIN CATCH

PRINT 'Se ha producido un error' END CATCH

(29)

Funciones especiales de Error

Las funciones especiales de error, están disponibles únicamente en el bloque CATCH para la obtención de información detallada del error.

Son:

 ERROR_NUMBER(), devuelve el número de error.

 ERROR_SEVERITY(), devuelve la severidad del error.

 ERROR_STATE(), devuelve el estado del error.

 ERROR_PROCEDURE(), devuelve el nombre del procedimiento almacenado que ha

provocado el error.

 ERROR_LINE(), devuelve el número de línea en el que se ha producido el error.

 ERROR_MESSAGE(), devuelve el mensaje de error.

Son extremadamente útiles para realizar una auditoría de errores.

BEGIN TRY

DECLARE @divisor int , @dividendo int, @resultado int

-- Esta linea provoca un error de division por 0 SET @resultado = @dividendo/@divisor

PRINT 'No hay error' END TRY BEGIN CATCH PRINT ERROR_NUMBER() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() PRINT ERROR_PROCEDURE() PRINT ERROR_LINE() PRINT ERROR_MESSAGE() END CATCH

Lógicamente, podemos utilizar estas funciones para almacenar esta información en una tabla de la base de datos y registrar todos los errores que se produzcan.

(30)

La variable de sistema @@ERROR

En versiones anteriores a SQL Server 2005, no estaban disponibles las instrucciones TRY

CATCH. En estas versiones se controlaban los errores utilizando la variable global de sistema

@@ERROR, que almacena el número de error producido por la última sentencia Transact SQL ejecutada.

DECLARE @divisor int ,

@dividendo int ,

@resultado int

-- Esta linea provoca un error de division por 0 SET @resultado = @dividendo/@divisor

IF @@ERROR = 0 BEGIN

PRINT 'No hay error' END

ELSE

BEGIN

PRINT 'Hay error' END

El uso de @@ERROR para controlar errores puede provocar multitud de problemas. Uno de los más habituales es sin duda, incluir una nueva sentencia Transact SQL entre la línea que provoco el error y la que lo controla. Esa nueva instrucción restaura el valor de @@ERROR y

no controlaremos el error.

El siguiente ejemplo ilustra esta situación:

DECLARE @divisor int ,

@dividendo int ,

@resultado int

-- Esta línea provoca un error de división por 0 SET @resultado = @dividendo/@divisor

PRINT 'Controlando el error ...' -- Esta línea estable @@ERROR a cero IF @@ERROR = 0

BEGIN

-- Se ejecuta esta parte!

PRINT 'No hay error' END

ELSE

BEGIN

(31)

END

Generar un error con RAISERROR

En ocasiones es necesario provocar voluntariamente un error, por ejemplo nos puede interesas que se genere un error cuando los datos incumplen una regla de negocio.

Podemos provocar un error en tiempo de ejecución a través de la función RAISERROR.

DECLARE @tipo int,

@clasificacion int

SET @tipo = 1

SET @clasificacion = 3

IF (@tipo = 1 AND @clasificacion = 3) BEGIN

RAISERROR ('El tipo no puede valer uno y la clasificacion 3', 16, -- Severidad

1 -- Estado )

END

La función RAISERROR recibe tres parámetros, el mensaje del error (o código de error predefinido), la severidad y el estado.

La severidad indica el grado de criticidad del error. Admite valores de 0 al 25, pero solo podemos asignar valores del 0 al 18. Los errores el 20 al 25 son considerados fatales por el sistema, y cerraran la conexión que ejecuta el comando RAISERROR. Para asignar valores del 19 al 25 necesitares ser miembros de la función de SQL Server sysadmin.

El estado es un valor para permitir que el programador identifique el mismo error desde diferentes partes del código. Admite valores entre 1 y 127, permite tratar

(32)

Consultar datos en Transact SQL

La sentencia SELECT

La sentencia SELECT nos permite consultar los datos almacenados en una tabla de la base de datos.

El formato de la sentencia select es:

SELECT [ALL | DISTINCT ][ TOP expression [ PERCENT ] [ WITH TIES ] ]

<nombre_campos>

FROM <nombre_tabla>

[ INNER | LEFT [OUTER]| RIGHT [OUTER] | CROSS]

[JOIN ] <nombre_tabla> ON <condicion_join>[ AND|OR <condicion>] [WHERE <condicion> [ AND|OR <condicion>]]

[GROUP BY <nombre_campos>]

[HAVING <condicion>[ AND|OR <condicion>]] [ORDER BY <nombre_campo> [ASC | DESC]

El siguiente ejemplo muestra una consulta sencilla que obtiene el código y la "familia" de una tabla llamada familias (representaría familias de productos por ejemplo).

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS

El uso del asterisco indica que queremos que la consulta devuelva todos los campos que existen

en la tabla.

SELECT * FROM FAMILIAS

(33)

Ahora vamos a realizar una consulta obteniendo además de los datos de familias, los datos de

las categorías y los productos.

INNER JOIN CATEGORIAS

ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA INNER JOIN PRODUCTOS

ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA

La combinación se realiza a través de la cláusula INNER JOIN, que es una clausula exclusiva,

es decir las familias que no tengan categorías y productos asociados no se devolverán.

Si queremos realizar la consulta para que no sea exclusiva, tenemos que utilizar LEFT JOIN. El

uso de la palabra reservada OUTER es opcional.

LEFT OUTER JOIN CATEGORIAS

ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA LEFT OUTER JOIN PRODUCTOS

ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA

Los registros que no tengan datos relacionados en una consulta LEFT JOIN devolverán en valor

null en los campos que correspondan a las tablas en las que no tienen dato.

También podemos forzar un producto cartesiano (todos con todos) a través de CROSS JOIN.

SELECT * FROM FAMILIAS CROSS JOIN CATEGORIAS

(34)

La cláusula WHERE

La cláusula WHERE es la instrucción que nos permite filtrar el resultado de una sentencia SELECT.

WHERE CO_FAMILIA = 1

Por supuesto, podemos especificar varias condiciones para el WHERE:

WHERE CO_FAMILIA = 1

OR CO_FAMILIA = 2

Podemos agrupar varios valores para una condición en la cláusula IN:

WHERE CO_FAMILIA IN ( 1 , 2)

La cláusula WHERE se puede utilizar conjuntamente con INNER JOIN, LEFT JOIN ...

SELECT FAMILIAS.CO_FAMILIA,

FAMILIAS.FAMILIA

FROM FAMILIAS

INNER JOIN CATEGORIAS

ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA WHERE FAMILIAS.CO_FAMILIA > 1

(35)

Siempre que incluyamos un valor alfanumérico para un campo en la condición WHERE este debe ir entre comillas simples:

WHERE FAMILIA = 'FAMILIA 1'

Para consultar campos alfanuméricos, es decir, campos de texto podemos utilizar el operador

LIKE conjuntamente con comodines.

WHERE FAMILIA LIKE 'FAM%'

Los comodines que podemos utilizar en son los siguientes:

 % , representa cualquier cadena de texto de cero o más caracteres de cualquier longitud.

 _ , representa un carácter.

 [a-d], representa cualquier carácter del intervalo a-d.

 [abcd], representa cualquier carácter del grupo abcd.

 [^a-d], representa cualquier carácter diferente del intervalo a-d.

 [^abcd], representa cualquier carácter distinto del grupo abcd.

También podemos obtener los valores distintos utilizando DISTINCT.

SELECT DISTINCT FAMILIA -- Devuelve los distintos valores de FAMILIA FROM FAMILIAS

Podemos limitar el número de registros que devuelve la consulta a través de la cláusula TOP. La cláusula TOP admite como parámetros un valor numérico entero o un porcentaje (sólo a partir de la versión 2005)

SELECT TOP 10 * -- Devuelve 10 registros FROM FAMILIAS

SELECT TOP 50 PERCENT * -- Devuelve el 50% de los registros FROM FAMILIAS

(36)

La cláusula TOP se puede combinar con WITH TIESen consultas agregadas.

La cláusula ORDER BY

Podemos especificar el orden en el que serán devueltos los datos a través de la cláusula ORDER BY.

ORDER BY FAMILIA DESC

También podemos indicar el índice del campo en la lista de selección en lugar de su nombre :

(37)

Consultas agregadas

La cláusula GROUP BY

La cláusula GROUP BY combina los registros devueltos por una consulta SELECT obteniendo uno o varios valores agregados(suma, valor mínimo y máximo ...).

Para cada registro se puede crear un valor agregado si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción SELECT. Su sintaxis es:

SELECT [ALL | DISTINCT ] [

TOP

<n> [

WITH TIES

]] <nombre_campo> [{,<nombre_campo>}] [{,<funcion_agregado>}]

FROM <nombre_tabla>|<nombre_vista>

[{,<nombre_tabla>|<nombre_vista>}] [WHERE <condicion> [{ AND|OR <condicion>}]] [GROUP BY <nombre_campo> [{,<nombre_campo >}]] [HAVING <condicion>[{ AND|OR <condicion>}]]

[ORDER BY <nombre_campo>|<indice_campo> [ASC | DESC] [{,<nombre_campo>|<indice_campo> [ASC | DESC ]}]]

Si se utiliza GROUP BY pero no existe una función SQL agregada en la instrucción SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de las funciones SQL agregadas.

Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como argumentos de una función SQL agregada.

El siguiente ejemplo realiza una "cuenta" de los datos que hay en la tabla PRODUCTOS.

SELECT COUNT(*) FROM PRODUCTOS

(38)

Este otro ejemplo, muestra la suma del PRECIO de cada uno de los productos que componen un pedido, para calcular el total del pedido agrupado por los datos del cliente.

SELECT CLIENTES.NOMBRE,

CLIENTES.APELLIDO1, CLIENTES.APELLIDO2,

SUM(PRECIO) -- Total del pedido

FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE,

CLIENTES.APELLIDO1, CLIENTES.APELLIDO2

(39)

Siempre que incluyamos una clausula WHERE en una consulta agregada esta se aplica antes de calcular el valor agregado. Es decir, si sumamos el valor de las ventas por producto, la suma se calcula después de haber aplicado el filtro impuesto por la cláusula WHERE.

INNER JOIN PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

-- La cláusula WHERE se aplica antes de realizar el calculo

WHERE CLIENTES.NOMBRE != 'UN NOMBRE' GROUP BY CLIENTES.NOMBRE,

La cláusula HAVING

Es posible que necesitemos calcular un agregado, pero que no necesitemos obtener todos los datos, solo los que cumplan una condición del agregado. Por ejemplo, podemos calcular el valor de las ventas por producto, pero que solo queramos ver los datos de los productos que hayan vendido más o menos de una determinada cantidad. En estos casos debemos utilizar la cláusula HAVING.

Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. Se utiliza la cláusula

WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros

(40)

HAVING es similar a WHERE, determina qué registros se seleccionan pero después de calcular el

agregado. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuáles de ellos se van a mostrar. HAVING permite el uso de funciones agregadas.

INNER JOIN PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

-- La cláusula WHERE se aplica antes de realizar el calculo

WHERE CLIENTES.NOMBRE != 'UN NOMBRE' GROUP BY CLIENTES.NOMBRE,

(41)

Funciones agregadas.

Transact SQL pone a nuestra disposición múltiples funciones agregadas, las más comunes son:  MAX  MIN  COUNT  SUM  AVG AVG

Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente

AVG(<expr>)

En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.

AVG(PRECIO) -- Promedio del pedido

INNER JOIN PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE,

(42)

Count

Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente:

COUNT(<expr>)

En donde expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto.

Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null. Count(*) es considerablemente más rápida que Count(Campo).

SELECT COUNT(*) FROM PEDIDOS

SELECT CLIENTES.NOMBRE, COUNT(*) FROM PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE

(43)

Max, Min

Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es:

MIN(<expr>) MAX(<expr>)

En donde expr es el campo sobre el que se desea realizar el cálculo. Expr puede incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).

SELECT CLIENTES.NOMBRE, MIN(PEDIDOS.FX_ALTA), MAX(PEDIDOS.FX_ALTA) FROM PEDIDOS

INNER JOIN CLIENTES

Sum

Devuelve la suma del conjunto de valores contenido en un campo especifico de una consulta. Su sintaxis es:

SUM(<expr>)

En donde expr representa el nombre del campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).

(44)

SUM(PEDIDOS.TOTAL_PEDIDO) FROM PEDIDOS

INNER JOIN CLIENTES

Uso de Select TOP con consultas agregadas.

Podemos utilizar SELECT TOP con consultas agregadas como con cualquier otra instrucción Transact

SQL.

En estos casos, la cláusula TOP se aplica después de calcular el agregado, devolviendo las N filas indicadas.

En este escenario es posible que queramos obtener los N valores que satisfagan una condición. Por ejemplo, queremos si queremos obtener los tres primeros clientes con mayores pedidos, usaríamos una consulta parecida a esta:

SELECT TOP 3

CLIENTES.NOMBRE,

SUM(DETALLE_PEDIDO.PRECIO)

FROM DETALLE_PEDIDO INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES

ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)

Sin embargo, puede darse el caso, de que el cuarto cliente devuelto por la consulta tenga un valor agregado idéntico al tercero, (es decir, están empatados). El uso de TOP 3 discriminaría el cuarto registro. Para evitar este comportamiento, y que la consulta devuelva también al cuarto cliente utilizamos la

(45)

cláusula WITH TIES.

SELECT TOP 3 WITH TIES

CLIENTES.NOMBRE,

SUM(DETALLE_PEDIDO.PRECIO)

INNER JOIN PEDIDOS

INNER JOIN CLIENTES

(46)

Select FOR XML

Cláusula FOR XML.

A partir de la versión 2000 SQL Server incluye la cláusula FOR XML para la consultas. Sin embargo, es a partir de la versión 2005 cuando se integra XML como tipo de dato nativo. Cuando especificamos la cláusula FOR XML el resultado de la consulta es devuelto en formato XML.

La cláusula FOR XML admite los siguientes modos que representan el formato en el que el XML es devuelto:

 XML AUTO, el modo AUTO emplea los campos en la declaración SELECT para formar una

jerarquía simple XML.

 XML RAW, el modo RAW genera elementos únicos, los cuales se denominan row, por

cada fila retornada.

 EXPLICIT, el modo EXPLICIT requiere un formato específico que puede ser mapeado en

casi cualquier forma XML, y al mismo tiempo ser formulado por una sola consulta SQL. Adicionalmente, disponemos de dos opciones más TYPE y ELEMENTS que determinan el formato del XML resultante. Los vemos con ejemplos.

Un ejemplo de XML AUTO.

ORDER BY FAMILIA FOR XML AUTO, TYPE

Obtendremos el siguiente resultado:

(47)

FOR XML AUTO, ELEMENTS

Obtendremos el siguiente resultado:

<FAMILIAS> <CO_FAMILIA>1</CO_FAMILIA> <FAMILIA>FAMILIA 1</FAMILIA> </FAMILIAS> <FAMILIAS> <CO_FAMILIA>2</CO_FAMILIA> <FAMILIA>FAMILIA 2</FAMILIA> </FAMILIAS> <FAMILIAS> <CO_FAMILIA>3</CO_FAMILIA> <FAMILIA>FAMILIA 3</FAMILIA> </FAMILIAS> <FAMILIAS> <CO_FAMILIA>4</CO_FAMILIA> <FAMILIA>FAMILIA 4</FAMILIA> </FAMILIAS>

Ahora un ejemplo de XML RAW:

(48)

ORDER BY FAMILIA FOR XML RAW , TYPE

Obtenemos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

<row> <CO_FAMILIA>1</CO_FAMILIA> <FAMILIA>FAMILIA 1</FAMILIA> </row> <row> <CO_FAMILIA>2</CO_FAMILIA> <FAMILIA>FAMILIA 2</FAMILIA> </row> <row> <CO_FAMILIA>3</CO_FAMILIA> <FAMILIA>FAMILIA 3</FAMILIA> </row> <row> <CO_FAMILIA>4</CO_FAMILIA> <FAMILIA>FAMILIA 4</FAMILIA> </row>

(49)

También es posible especificar el nodo que queremos que muestre:

ORDER BY FAMILIA

FOR XML RAW ('FamiliasDeProductos') , TYPE

Devuelve el siguiente resultado:

Del mismo modo con la opción ELEMENTS:

ORDER BY FAMILIA

FOR XML RAW ('FamiliasDeProductos') , ELEMENTS

Obtendremos el siguiente resultado:

<FamiliasDeProductos> <CO_FAMILIA>1</CO_FAMILIA> <FAMILIA>FAMILIA 1</FAMILIA> </FamiliasDeProductos> <FamiliasDeProductos> <CO_FAMILIA>2</CO_FAMILIA>

(50)

<FAMILIA>FAMILIA 2</FAMILIA> </FamiliasDeProductos> <FamiliasDeProductos> <CO_FAMILIA>3</CO_FAMILIA> <FAMILIA>FAMILIA 3</FAMILIA> </FamiliasDeProductos> <FamiliasDeProductos> <CO_FAMILIA>4</CO_FAMILIA> <FAMILIA>FAMILIA 4</FAMILIA> </FamiliasDeProductos>

Ahora un ejemplo con el formato XML EXPLICIT.

SELECT

1 AS TAG, -- La primera columna debe tener el alias TAG NULL AS PARENT, -- La segunda columna debe tener el alias PARENT -- El resto de columnas deben tener el alias en el formato:

-- <NombreNodo>!<nodo>!<atributo> CO_FAMILIA as "FamiliaDeProductos!1!CODIGO_FAMILIA", FAMILIA as "FamiliaDeProductos!1!DESCRIPCION" FROM FAMILIAS ORDER BY FAMILIA FOR XML EXPLICIT

Obtenemos el siguiente resultado:

(51)

Campos y variables XML.

Dado que XML es un tipo nativo de XML podemos definir tablas con campos de tipo XML, variables...

El siguiente ejemplo muestra cómo trabajar con campos y variables XML.

-- Primero creamos una tabla con un campo XML

CREATE TABLE tablaXML

(

ID int not null identity, DOC xml null,

constraint PK_tablaXML PRIMARY KEY (ID)

) GO

DECLARE @xml xml -- Variable de tipo XML

-- Leemos los datos de la tabla FAMILIAS

SET @xml = (SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS FOR XML AUTO)

-- y los guardamos en nuestra tabla

INSERT INTO tablaXML

(DOC) VALUES (@xml)

(52)

-- Hacemos lo mismo con los productos

SET @xml = (SELECT *

FROM PRODUCTOS FOR XML AUTO)

INSERT INTO tablaXML

(DOC) VALUES (@xml)

-- Consultamos la tabla y vemos el resultado

SELECT * FROM tablaXML

Cuando consultemos la tabla tendremos la siguiente información (en mi caso claro!):

(53)

Operaciones con conjuntos.

SQL Server 2005 permite tres tipos de operaciones con conjuntos:  UNION, disponible en todas las versiones de SQL Server.  EXCEPT, nuevo en SQL Server 2005.

 INTERSECT, nuevo en SQL Server 2005.

Para utilizar operaciones de conjuntos debemos cumplir una serie de normas.

 Las consultas a unir deben tener el mismo número campos, y además los campos deben ser del mismo tipo.

 Sólo puede haber una única clausula ORDER BY al final de la sentencia SELECT.

UNION

UNION devuelve la suma de dos o más conjuntos de resultados. El conjunto obtenido como resultado de UNION tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM EMPLEADOS

UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM CLIENTES

Cuando realizamos una consulta con UNION internamente se realiza una operación DISTINCT sobre el conjunto de resultados final. Si queremos obtener todos los valores debemos utiliza

UNION ALL.

UNION ALL

(54)

EXCEPT

EXCEPT devuelve la diferencia (resta) de dos o más conjuntos de resultados. El conjunto obtenido como resultado de EXCEPT tiene la misma estructura que los conjuntos originales. El siguiente ejemplo muestra el uso de EXCEPT

EXCEPT

El uso de EXCEPT, como norma general, es mucho más rápido que utilizar condiciones NOT IN o EXISTS en la cláusula WHERE.

INTERSECT

Devuelve la intersección entre dos o más conjuntos de resultados en uno. El conjunto obtenido como resultado de INTERSECT tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de INTERSECT

INTERSECT

(55)

Insertar datos en Transact SQL

Inserción individual de filas.

Para realizar la inserción individual de filas SQL posee la instrucción INSERT INTO.La inserción individual de filas es la que más comúnmente utilizaremos. Su sintaxis es la siguiente:

INSERT INTO <nombre_tabla>

[(<campo1>[,<campo2>,...])]

values

(<valor1>,<valor2>,...);

El siguiente ejemplo muestra la inserción de un registro en la tabla PRECIOS.

INSERT INTO PRECIOS

(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO)

VALUES

(10, getdate(),getdate()+30, 1)

Inserción múltiple de filas.

También es posible insertar en una tabla el resultado de una consulta SELECT. De este modo se insertarán tantas filas como haya devuelto la consulta SELECT.

El siguiente ejemplo muestra la inserción múltiple de filas.

(56)

getdate(),

getdate() + 30, CO_PRODUCTO

FROM DETALLE_PEDIDO Inserción de valores por defecto.

También podemos forzar a que la insercción se realice con los datos por defecto establecidos para la

tabla (o null si no tienen valores por defecto).

INSERT INTO PRECIOS DEFAULT VALUES

En SQL Sever podemos marcar un campo de una tabla como auto numérico (identity), cuando insertamos un registro en dicha tabla el valor del campo se genera automáticamente. Para recuperar el valor generado disponemos de varios métodos:

 Utilizar la función @@identity, que devuelve el último valor identidad insertado por la transacción:

DECLARE @Codigo int

VALUES

set @Codigo = @@Identity

PRINT @Codigo

El uso de @@Identity no siempre es válido, ya que al devolver el último valor identidad insertado por la transacción, no nos garantiza que el valor haya sido insertado en la tabla que nos interesa (por ejemplo la tabla podría tener un trigger que insertara datos en otra tabla con campos identidad).

(57)

 En este tipo de escenarios debemos utilizar la función, SCOPE_IDENTITY.

DECLARE @Codigo int

VALUES

SET @Codigo = SCOPE_IDENTITY() PRINT @Codigo

Clausula OUTPUT

A partir de la versión de SQL Server 2005 disponemos de la cláusula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y

DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o

DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción

UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los

datos "después" del cambio.

INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.

DECLARE @FILAS_INSERTADAS TABLE ( CO_PRECIO int,

PRECIO decimal, FX_INICIO datetime, FX_FIN datetime,

(58)

CO_PRODUCTO int )

(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) OUTPUT INSERTED.* INTO @FILAS_INSERTADAS VALUES

(10, getdate(),getdate()+30, 1)

(59)

II. Actualizar datos en Transact SQL

A. Update

Para la actualización de datos Transact SQL dispone de la sentencia UPDATE. La sentencia UPDATE permite la actualización de uno o varios registros de una única tabla. La sintaxis de la sentencia UPDATE es la siguiente

UPDATE

<nombre_tabla>

SET

{[,<campo2> = <valor2>,...,<campoN> = <valorN>]} [

WHERE

<condicion>];

El siguiente ejemplo muestra el uso de UPDATE.

UPDATE CLIENTES SET NOMBRE = 'Microsoft', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' WHERE CO_CLIENTE = 10

Un aspecto a tener en cuenta, sobre todo si has trabajado con ORACLE, es que SQL graba los cambios inmediatamente sin necesidad de hacer COMMIT. Por supuesto podemos gestionar nosotros las transacciones pero es algo que hay que hacer de forma explícita con la instrucción

BEGIN TRAN y que se verá en capítulos posteriores de este tutorial. B. C. D. E. F. G.

H. Update INNER JOIN

En ocasiones queremos actualizar los datos de una tabla con los datos de otra (muy común para des normalizar un modelo de datos).

Habitualmente, usamos subconsultas para este propósito, pero Transact SQL permite la utilización de la sentencia UPDATE INNER JOIN.

(60)

UPDATE CLIENTES SET NOMBRE = FICHERO_CLIENTES.NOMBRE, APELLIDO1 = FICHERO_CLIENTES.APELLIDO1, APELLIDO2 = FICHERO_CLIENTES.APELLIDO2 FROM CLIENTES

INNER JOIN FICHERO_CLIENTES

ON FICHERO_CLIENTES.CO_CLIENTE = CLIENTES.CO_CLIENTE I. Clausula OUTPUT

A partir de la versión de SQL Server 2005 disponemos de la cláusula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas

INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción

UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

DECLARE @FILAS_ACTUALIZADAS TABLE

( CO_CLIENTE int , NOMBRE varchar(100), APELLIDO1 varchar(100), APELLIDO2 varchar(100) ) UPDATE CLIENTES SET NOMBRE = 'Microsoft', APELLIDO1 = 'Herrarte',