Programación con Transact SQL

(1)

Programación con Transact SQL

Introducción

SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, 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 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

de variables, estructuras de control de flujo, bucles ... y demás elementos caracteristicos de la programación. No es de extrañar,

caracteristicos de la programación. No es de extrañar, SQL es un lenguaje deSQL es un lenguaje de consulta, no un lenguaje

consulta, no un lenguaje de programación.de programación.

Sin embargo, SQL es la herramienta ideal para trabajar con bases de datos. Cuando 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

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 relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. 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 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 para extender el SQL estándar con otro tipo de instrucciones y elementos propios de los lenguajes de programación .

los lenguajes de programación . Con

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

de datos SQL ServerSQL Server, están son:, están son:

•

• Procedimientos almacenadosProcedimientos almacenados • • FuncionesFunciones • • Triggers Triggers • • ScriptsScripts

Fundamentos de Transact SQL

Primeros pasos con Transact SQL

Para programar en

Para programar enTransact SQLTransact SQL es necesario conocer sus fundamentos.es necesario conocer sus fundamentos. Como introducción vamos a ver algunos elementos y conceptos básicos del Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje.

lenguaje.

•

• Transact SQLTransact SQL no es CASE-SENSITIVE, es decir, no difereno es CASE-SENSITIVE, es decir, no diferencia mayúsculas dencia mayúsculas de

minúsculas como otros lenguajes de programación como C o Java. 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.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, Son soportados 2 estilos de comentarios, el de línea simple y de multilínea, para lo cual son empleados ciertos caracters especiales como son:

para lo cual son empleados ciertos caracters especiales como son: o

o -- Para un comentario de linea simple-- Para un comentario de linea simple o

o /* ... */ Para un comentario de varias lineas/* ... */ Para un comentario de varias lineas

•

• Un literal es un valor fijo de tipo numérico, caracter, cadena o lógico noUn literal es un valor fijo de tipo numérico, caracter, cadena o lógico no

representado por un identificador (es un valor explícito). representado por un identificador (es un valor explícito).

•

• Una variable es un valor identificado por un nombre (identificador) sobre elUna variable es un valor identificado por un nombre (identificador) sobre el

que podemos realizar modificaciones. En

que podemos realizar modificaciones. En Transact SQLTransact SQL los identificadoreslos identificadores de variables deben comenzar por el caracter @, es decir, el nombre de una de variables deben comenzar por el caracter @, es decir, el nombre de una variable debe comenzar por @.Para declarar variables en

(2)

SQL

SQLdebemos utilizar la palabra clavedebemos utilizar la palabra clave declaredeclare, seguido del identificador y, seguido del identificador y tipo de datos de la variable

tipo de datos de la variable

..

Veamos algunos ejemplos:

-- Esto es un comentario de linea simple -- Esto es un comentario de linea simple

/* /*

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

Conjunto de Lineas. */

*/

declare

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

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

-- variable de tipo varchar set

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

-- www.devjoker.com es un literal -- www.devjoker.com es un literal print

print @Nombre@Nombre -- Imprime por pantalla el valor de @nombre.-- Imprime por pantalla el valor de @nombre. -- No diferencia mayúsculas ni minúsculas -- No diferencia mayúsculas ni minúsculas Scripts y lotes.

Scripts y lotes.

Un script de

Un script de Transact SQLTransact SQL es un conjunto de sentencias dees un conjunto de sentencias de TransactTransact SQL

SQL en formato de texto plano que se ejecutan en un servidor deen formato de texto plano que se ejecutan en un servidor de SQL ServerSQL Server.. Un script está compuesto por uno o varios lotes. Un lote delimita el alcance de las 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

variables y sentencias del script. Dentro de un mismo script se diferencian los diferentes lotes a través de las instrucción

diferentes lotes a través de las instrucción GO.GO.

-- Este es el primer lote del script -- Este es el primer lote del script SELECT

SELECT ** FROM FROM COMENTARIOSCOMENTARIOS GO

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

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

-- la fecha

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

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

lógicas dentro del script.

Tipos de datos en Transact SQL

Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato 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

(3)

SQL

SQLdebemos utilizar la palabra clavedebemos utilizar la palabra clave declaredeclare, seguido del identificador y, seguido del identificador y tipo de datos de la variable

tipo de datos de la variable

..

Veamos algunos ejemplos:

-- Esto es un comentario de linea simple -- Esto es un comentario de linea simple

/* /*

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

Conjunto de Lineas. */

*/

declare

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

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

-- variable de tipo varchar set

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

-- www.devjoker.com es un literal -- www.devjoker.com es un literal print

print @Nombre@Nombre -- Imprime por pantalla el valor de @nombre.-- Imprime por pantalla el valor de @nombre. -- No diferencia mayúsculas ni minúsculas -- No diferencia mayúsculas ni minúsculas Scripts y lotes.

Scripts y lotes.

Un script de

Un script de Transact SQLTransact SQL es un conjunto de sentencias dees un conjunto de sentencias de TransactTransact SQL

SQL en formato de texto plano que se ejecutan en un servidor deen formato de texto plano que se ejecutan en un servidor de SQL ServerSQL Server.. Un script está compuesto por uno o varios lotes. Un lote delimita el alcance de las 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

variables y sentencias del script. Dentro de un mismo script se diferencian los diferentes lotes a través de las instrucción

diferentes lotes a través de las instrucción GO.GO.

-- Este es el primer lote del script -- Este es el primer lote del script SELECT

SELECT ** FROM FROM COMENTARIOSCOMENTARIOS GO

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

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

-- la fecha

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

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

lógicas dentro del script.

Tipos de datos en Transact SQL

Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato 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

(4)

almacenamiento, espacio que de disco-memoria que va a ocupar un campo o almacenamiento, espacio que de disco-memoria que va a ocupar un campo o variable, restricciones y rango de valores validos.

variable, restricciones y rango de valores validos.

T

Transact ransact SQLSQL proporciona una variedad predefinida de tipos de datos . Casi todosproporciona una variedad predefinida de tipos de datos . Casi todos

los tipos de datos manejados por

los tipos de datos manejados por Transact SQLTransact SQL son similares a los soportadosson similares a los soportados por

por SQLSQL..

Tipos de datos numéricos.

SQL Server

SQL Server dispone de varios tipos de datos númericos. Cuanto mayor sea eldispone de varios tipos de datos númericos. Cuanto mayor sea el número que puedan almacenar mayor será en consecuencia el espacio utilizado para 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. almacenarlo. Como regla general se recomienda usar el tipo de dato mínimo posible. T

Todos los dato odos los dato numéricos admiten el valor numéricos admiten el valor NULL.NULL.

Bit

Bit. Una columna o variable de tipo. Una columna o variable de tipo bitbit puede almacenar puede almacenar el rango el rango de valores de valores de de 11 a 0.

a 0.

Tinyint

Tinyint. Una columna o variable de tipo. Una columna o variable de tipo tinyinttinyintpuede almacenar el rango depuede almacenar el rango de valores de 0 a 255.

valores de 0 a 255.

SmallInt

SmallInt. Una columna o variable de tipo. Una columna o variable de tipo smallintsmallint puede almacenar el rango depuede almacenar el rango de

valores -32768 a 32767. valores -32768 a 32767.

Int

Int. Una columna o variable de tipo. Una columna o variable de tipo intint puede almacenar el rango de valores -2puede almacenar el rango de valores -23131_a_a

2 23131_{-1 .}_{-1 .}

BigInt.

BigInt. Una columna o variable de tipoUna columna o variable de tipo bigintbigint puede almacenar el rango de valorespuede almacenar el rango de valores

-2

-26363_{a 2}_{a 2}6363_{-1 .}_{-1 .}

Decimal(p,s)

Decimal(p,s). Una columna de tipo decimal puede almacenar datos númericos. Una columna de tipo decimal puede almacenar datos númericos decimales sin redondear. Donde p es la precision (número total del dígitos) y s la decimales sin redondear. Donde p es la precision (número total del dígitos) y s la escala (número de valores decimales)

escala (número de valores decimales)

Float.

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

10308308 _{a 1,79x-10}_{a 1,79x-10}308,308, _{, si la definimos con el valor máxmo de precisión. La precisión}_{, si la definimos con el valor máxmo de precisión. La precisión}

puede variar entre 1 y 53. puede variar entre 1 y 53.

Real

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

3,4x-10 1038,38,

Money.

Money. Almacena valores númericos monetarios de -2Almacena valores númericos monetarios de -26363 _{a 2}_{a 2}6363_{-1, con una precisión}_{-1, con una precisión}

de hasta diexz milesimas de la unidad monetaria. de hasta diexz milesimas de la unidad monetaria.

SmallMoney

SmallMoney. Almacena valores númericos monetarios de -214.748,3647 a. Almacena valores númericos monetarios de -214.748,3647 a

214.748,3647, con una precisión de hasta diez milesimas de la unidad monetaria. 214.748,3647, con una precisión de hasta diez milesimas de la unidad monetaria.

T

Todos los tipos odos los tipos de datos enteros pueden de datos enteros pueden marcarse con la propiedadmarcarse con la propiedad identityidentityparapara

hacerlos autonuméricos. hacerlos autonuméricos.

DECLARE

(5)

@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, unicamente 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 diferente idomas.

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 diferente idomas.

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 precision de milisegundo. Debe usarse para fechas muy especificas.

(6)

SmallDatetime. Almacena fechas con una precision 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

(7)

Tipos de datos personalizados.

Transact SQL permite la creación de tipos de datos personalizados, a trevé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

Variables en Transact SQL

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 caracter

@, es decir, el nombre de una variable debe comenzar por @. Para declarar variables en Transact SQL debemos utilizar la palabra clavedeclare, 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 Lineas.

*/

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

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

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

-- www.devjoker.com es un literal print @Nombre -- Imprime por pantalla el valor de @nombre.

-- No diferencia mayúsculas ni minúsculas 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 sentenciaSELECT. • Realizando un FETCH de un cursor.

(8)

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 WHEREID = 1) PRINT@nombre

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

DECLARE @nombre VARCHAR(100),

@apellido1VARCHAR(100), @apellido2VARCHAR(100) SELECT @nombre=nombre , @apellido1=Apellido1, @apellido2=Apellido2 FROMCLIENTES WHEREID = 1 PRINT@nombre PRINT@apellido1 PRINT@apellido2

(9)

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.

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

DECLARE @nombre VARCHAR(100),

@apellido1VARCHAR(100), @apellido2VARCHAR(100)

DECLARE CDATOS CURSOR FOR

SELECT nombre , Apellido1, Apellido2 FROMCLIENTES

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

CLOSECDATOS

(10)

Veremos los cursores con más detalle más adelante en este tutorial.

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.SqlTypesy 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 ySQLStringobtiene 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.

(11)

cursor ninguno ninguno

timestamp ninguno ninguno

xml SqlXml ninguno

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)

SOME(Devuelve TRUE si alguna de las comparaciones de

(12)

Estructuras de control en Transact SQL

Estructura condicional IF

La estuctura condicional IFpermite 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.devjoker.com' END ELSE BEGIN

PRINT 'Otra Web (peor!)' END

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

(13)

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.devjoker.com' WHEN 'ALM' THEN 'www.aleamedia.com'

ELSE 'www.devjoker.com' END)

PRINT @Web

Otra sintaxis deCASE 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:

(14)

@diminutivo varchar(3) SET @diminutivo = 'DJK'

SET @Web = (CASE

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

ELSE 'www.devjoker.com' END)

PRINT @Web

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

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

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.devjoker.com' END) PRINT @Web 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

(15)

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

DECLARE @contador int SET @contador = 0

WHILE (@contador < 100) BEGIN

SET @contador = @contador + 1 IF (@contador % 2 = 0)

CONTINUE

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

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

BEGIN

SET @contador = @contador + 1 IF (@contador % 50 = 0)

BREAK

También podemos utilizar el bucle WHILE conuntamente con subconsultas.

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.

(16)

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'

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

(17)

SET @divisor = 0

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

PRINT 'No hay error' END TRY

BEGIN CATCH

PRINT 'Se ha producido un error' END CATCH

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

SET @dividendo = 100 SET @divisor = 0

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

(18)

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. 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 SET @dividendo = 100 SET @divisor = 0

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 SET @dividendo = 100 SET @divisor = 0

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

(19)

-- Se ejecuta esta parte! PRINT 'No hay error'

END ELSE

BEGIN

PRINT 'Hay error' 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 conexion 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 .

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.

(20)

El formato de la sentencia select es:

SELECT [ALL | DISTINCT ][ TOPexpression [ 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

Ahora vamos a realizar una consulta obteniendo además de los datos de familias, los datos de las categorias y los productos.

SELECT * FROM FAMILIAS INNER JOIN CATEGORIAS ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA INNER JOIN PRODUCTOS ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA

(21)

La combinación se realiza a través de la clausula INNER JOIN, que es una clasula exclusiva, es decir las familias que no tengan categorias y productos asociados no se devolveran.

Si queremos realizar la consulta para que no sea exclusiva, tenemos que utilizar LEFT JOIN. El uso de la palabra reservada OUTER es opcional.

SELECT *

FROM FAMILIAS

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 devolveran 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 * FROMFAMILIAS CROSS JOIN CATEGORIAS

La cláusula WHERE

La cláusula WHEREes la instrucción que nos permite filtrar el resultado de una

sentencia SELECT.

SELECT CO_FAMILIA, FAMILIA FROMFAMILIAS

WHERECO_FAMILIA = 1

(22)

SELECT *

FROMFAMILIAS

WHERECO_FAMILIA = 1 OR CO_FAMILIA = 2

Podemos agrupar varias valores para una condicion en la clausula IN: SELECT *

FROMFAMILIAS

WHERECO_FAMILIA IN ( 1 , 2)

La clausula WHEREse puede utilizar conjuntamente con INNER JOIN, LEFT JOIN ... SELECT FAMILIAS.CO_FAMILIA, FAMILIAS.FAMILIA FROMFAMILIAS INNER JOIN CATEGORIAS ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA WHEREFAMILIAS.CO_FAMILIA > 1

Siempre que incluyamos un valor alfanumerico para un campo en la condición WHEREeste debe ir entre comillas simples:

SELECT *

FROMFAMILIAS

WHEREFAMILIA = 'FAMILIA 1'

Para consultar campos alfanumericos, es decir, campos de texto podemos utilizar el operador LIKEconjuntamente con comodines.

(23)

SELECT *

FROMFAMILIAS

WHEREFAMILIA 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 caracter.

• [a-d], representa cualquier caracter del intervalo a-d. • [abcd], representa cualquier caracter del grupo abcd.

• [^a-d], representa cualquier caracter diferente del intervalo a-d. • [^abcd], representa cualquier caracter distinto del grupo abcd.

También podemos obtener los valores distintos utilizandoDISTINCT.

SELECT DISTINCT FAMILIA -- Devuelve los distintos valores de FAMILIA

FROMFAMILIAS

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

SELECT TOP10 * -- Devuelve 10 registros FROMFAMILIAS

SELECT TOP50 PERCENT* -- Devuelve el 50% de los registros FROMFAMILIAS

La clausula 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.

(24)

FROMFAMILIAS

ORDER BY FAMILIADESC

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

FROMFAMILIAS

ORDER BY 2DESC -- Ordena por FAMILIA

Consultas agregadas

La cláusula GROUP BY

La clausula 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

(25)

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

SELECT COUNT(*)

FROMPRODUCTOS

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

SELECT CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2,

SUM(PRECIO) -- Total del pedido

FROMDETALLE_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

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 despues de haber aplicado el filtro impuesto por la clausula WHERE.

SELECT CLIENTES.NOMBRE,

CLIENTES.APELLIDO1, CLIENTES.APELLIDO2,

(26)

SUM(PRECIO) -- Total del pedido FROMDETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE -- La clausula WHERE se aplica antes de realizar el calculo

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

CLIENTES.APELLIDO1, CLIENTES.APELLIDO2 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 producto que hayan vendido más o menos de una determinada cantidad. En estos casos debemos utilizar la clausula 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 WHEREpara excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados.

HAVING es similar a WHERE, determina qué registros se seleccionan pero despues de calcular el agregado. Una vez que los registros se han agrupado utilizando GROUP BY , HAVING determina cuales de ellos se van a

mostrar. HAVING permite el uso de funciones agregadas.

CLIENTES.APELLIDO1, CLIENTES.APELLIDO2,

(27)

SUM

SUM((PRECIOPRECIO)) -- Total del pedido-- Total del pedido FROM

FROMDETALLE_PEDIDODETALLE_PEDIDO INNER

INNER JOIN JOIN PEDIDOSPEDIDOS

ON

ONDETALLE_PEDIDO.DETALLE_PEDIDO.CO_PEDIDOCO_PEDIDO == PEDIDOSPEDIDOS..CO_PEDIDOCO_PEDIDO INNER

INNER JOIN JOIN CLIENTESCLIENTES

ON

ON PEDIDOSPEDIDOS..CO_CLIENTECO_CLIENTE == CLIENTES.CLIENTES.CO_CLIENTECO_CLIENTE -- La clausula WHERE se aplica antes de realizar el calculo

-- La clausula WHERE se aplica antes de realizar el calculo WHERE

WHERECLIENTESCLIENTES..NOMBRENOMBRE !=!='UN NOMBRE''UN NOMBRE'

GROUP

GROUPBY BY CLIENTESCLIENTES..NOMBRENOMBRE,,

CLIENTES

CLIENTES..APELLIDO1APELLIDO1,,

CLIENTES

CLIENTES..APELLIDO2APELLIDO2

HAVING

HAVING SUMSUM((PRECIOPRECIO)) >> 100100 Funciones agregadas.

Funciones agregadas.

Transact SQL

Transact SQL pone a nuestra disposición multiples funciones agregadas, las máspone a nuestra disposición multiples funciones agregadas, las más

comunes son: comunes son: • • MAXMAX • • MINMIN • • COUNTCOUNT • • SUMSUM • • AVGAVG AVG AVG

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

especificado de una consulta. Su sintaxis es la siguiente

AVG

AVG((<expr> <expr> ))

En donde expr representa el campo que contiene los datos numéricos para los que 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 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 de dicho campo. La media calculada por Avg es la media aritmética (la suma de los

(28)

valores dividido por el número de valores). La función Avg no incluye ningún campo valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.

Null en el cálculo.

SELECT

SELECT CLIENTESCLIENTES..NOMBRENOMBRE,,

CLIENTES

AVG

AVG((PRECIOPRECIO)) -- Promedio del pedido-- Promedio del pedido FROM

FROMDETALLE_PEDIDODETALLE_PEDIDO INNER

INNER JOIN JOIN PEDIDOSPEDIDOS

ON

ONDETALLE_PEDIDO.DETALLE_PEDIDO.CO_PEDIDOCO_PEDIDO == PEDIDOSPEDIDOS..CO_PEDIDOCO_PEDIDO INNER

ON

ON PEDIDOSPEDIDOS..CO_CLIENTECO_CLIENTE == CLIENTES.CLIENTES.CO_CLIENTECO_CLIENTE GROUP

GROUP BYBY CLIENTESCLIENTES..NOMBRENOMBRE,,

CLIENTES

CLIENTES..APELLIDO2APELLIDO2

Count

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

COUNT((<expr> <expr> )) En donde

En donde expr expr contiene el nombre del campo que desea contar. Los operandos decontiene 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

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 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. funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto.

Aunque expr puede realizar un cálculo sobre un campo, Count simplemente 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 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 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 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(*) número total de registros, incluyendo aquellos que contienen campos null. Count(*) es considerablemente más rápida que Count(Campo).

(29)

SELECT

SELECT COUNTCOUNT(*)(*)

FROM

FROMPEDIDOSPEDIDOS

SELECT

SELECT CLIENTESCLIENTES..NOMBRENOMBRE,, COUNTCOUNT(*)(*)

FROM

FROMPEDIDOSPEDIDOS INNER

ON

ON PEDIDOSPEDIDOS..CO_CLIENTECO_CLIENTE == CLIENTES.CLIENTES.CO_CLIENTECO_CLIENTE

GROUP

GROUPBY BY CLIENTESCLIENTES..NOMBRENOMBRE

Max, Min

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

campo especifico de una consulta. Su sintaxis es:

MIN

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

MAX((<expr> <expr> ))

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

agregadas de SQL).

SELECT

SELECT CLIENTESCLIENTES..NOMBRENOMBRE,,

MIN

MIN((PEDIDOS.FX_ALTAPEDIDOS.FX_ALTA),),

MAX

MAX((PEDIDOS.FX_ALTAPEDIDOS.FX_ALTA))

FROM

FROMPEDIDOSPEDIDOS

INNER

ON

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTEPEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP

GROUPBY BY CLIENTESCLIENTES..NOMBRENOMBRE Sum

(30)

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

SUM (<expr> )

En donde expr respresenta 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).

SUM(PEDIDOS.TOTAL_PEDIDO) FROMPEDIDOS

INNER JOIN CLIENTES

GROUP BY CLIENTES.NOMBRE

Uso de Select TOP con consultas agregadas.

Podemos utilizar SELECT TOP con consultas agregadas como con cualquier otra instruccion Transact SQL.

En estos casos, la clausula TOP se aplica despues de calcular el agregado, devolviendo las N filas indicadas.

En este escenario es posible que queramos obtener los N valores que satisfagan una condicion. Por ejemplo, queremos si queremos obtener los tres primeros clientes con mayores pedidos, usariamos una consulta parecida a esta:

SELECT TOP3 CLIENTES.NOMBRE, SUM(DETALLE_PEDIDO.PRECIO) FROMDETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

(31)

INNER JOIN CLIENTES

GROUP BY CLIENTES.NOMBRE

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 agragado identico al tercero, (es decir, estan 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 clausula WITH TIES.

SELECT TOP3 WITH TIES

CLIENTES.NOMBRE, SUM(DETALLE_PEDIDO.PRECIO) FROMDETALLE_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

ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)

Select FOR XML

Clausula FOR XML.

A partir de la version 2000 SQL Server incluye la clausula 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 clausula FOR XML el resultado de la consulta es devuelto en formato XML.

La clausula FOR XML admite los siguientes modos que representan el formato en el que el XML es devuelto:

(32)

• 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

denominanrow, 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.

FROMFAMILIAS

ORDER BY FAMILIA FOR XML AUTO, TYPE

Obtendremos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

FROMFAMILIAS

FOR XML AUTO, ELEMENTS

(33)

<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:

FROMFAMILIAS

ORDER BY FAMILIA

FOR XML RAW , TYPE

Obtenemos el siguiente resultado:

(34)

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>

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

(35)

FROMFAMILIAS ORDER BY FAMILIA

FOR XML RAW ('FamiliasDeProductos') ,TYPE

Devuelve el siguiente resultado:

Del mismo modo con la opción ELEMENTS:

FROMFAMILIAS

ORDER BY FAMILIA

FOR XML RAW ('FamiliasDeProductos') ,ELEMENTS

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

(36)

<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 ASTAG, -- La primera columna debe tener el alias TAG

NULL ASPARENT, -- 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"

FROMFAMILIAS ORDER BY FAMILIA FOR XML EXPLICIT

Obtenemos el siguiente resultado:

(37)

<FamiliaDeProductos CODIGO_FAMILIA="4" DESCRIPCION="FAMILIA 4" /> 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 como trabajar con campos y variables XML.

-- Primero creamos una tabla con un campo XML

CREATE TABLE tablaXML (

IDint not null identity, DOCxmlnull,

constraintPK_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 INTOtablaXML (DOC)VALUES (@xml)

(38)

-- Hacemos lo mismo con los productos

SET@xml = (SELECT *

FROM PRODUCTOS FOR XML AUTO)

INSERT INTOtablaXML

(DOC)VALUES (@xml)

-- Consultamos la tabla y vemos el resultado

SELECT * FROMtablaXML

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

Operaciones con conjuntos.

SQL Server 2005 permite tres tipos de operaciones con conjuntos:

(39)

• 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

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

El siguiente ejemplo muestra el uso de UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif , FxNacimiento

FROMEMPLEADOS

UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif , FxNacimiento FROMCLIENTES

Cuando realizamos una consulta con UNIONinternamente se realiza una

operacion DISTINCT sobre el conjunto de resultados final. Si queremos obtener todos los valores debemos utiliza UNION ALL.

FROMEMPLEADOS

UNIONALL

FROMCLIENTES

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.

(40)

El siguiente ejemplo muestra el uso de EXCEPT

SELECT Nombre, Apellido1 , Apellido2, NifCif , FxNacimiento FROMEMPLEADOS

EXCEPT

FROMCLIENTES

El uso de EXCEPT, como norma general, es mucho más rápido que utilizar condiciones NOT IN o EXISTS en la clausula 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

FROMEMPLEADOS INTERSECT

FROMCLIENTES

Insertar datos en Transact SQL

Inserción individual de filas.

Para realizar la insercción individual de filas SQL posee la instrucciónINSERT INTO.La insercción individual de filas es la que más comunmente utilizaremos. Su

sintaxis es la siguiente:

INSERT INTO <nombre_tabla> [(<campo1>[,<campo2>,...])] values

(41)

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

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

INSERT INTOPRECIOS

(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO)

VALUES

(10, getdate(),getdate()+30, 1) Insertció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 multiple de filas.

INSERT INTOPRECIOS

(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) SELECT PRECIO_UNIDAD, getdate(), getdate() + 30, CO_PRODUCTO FROMDETALLE_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).

(42)

En SQL Sever podemos marcar un campo de una tabla como autonumérico

(identity), cuando insertamos un registro en dicha tabla el valor del campo se genera automaticamente. Para recuperar el valor generado disponemos de varios métodos:

• Utilizar la funcion @@identity, que devuelve el último valor identidad

insertado por la transaccion:

DECLARE @Codigo int

INSERT INTOPRECIOS

VALUES

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

set @Codigo = @@Identity

PRINT@Codigo

El uso de @@Identityno siempre es válido, ya que al devolver el úlitmo 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).

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

DECLARE @Codigo int

INSERT INTOPRECIOS

VALUES

SET@Codigo = SCOPE_IDENTITY()

(43)

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la

clausula 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 oDELETE. Es decir, son una copia de los datos "antes" del cambio.

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

Las columnas con prefijo INSERTED reflejan el valor después de que se complete

la instrucción UPDATE oINSERT, pero antes de que se ejecuten los

desencadenadores. Es decir, son una copia de los datos "despues" del cambio.

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

DECLARE @FILAS_INSERTADAS TABLE ( CO_PRECIOint, PRECIO decimal, FX_INICIO datetime, FX_FIN datetime, CO_PRODUCTO int )

INSERT INTO PRECIOS

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

(44)

Actualizar datos en Transact SQL

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 <campo1> = <valor1>

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

El siguiente ejemplo muestra el uso de UPDATE.

UPDATE CLIENTES SET NOMBRE = 'Devjoker', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' WHERECO_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 nosostros las transacciones pero es algo que hay que hacer de forma explicita con la instruccion BEGIN TRAN y que se verá en capitulos

posteriores de este tutorial. Update INNER JOIN

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

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

UPDATE CLIENTES SET

(45)

APELLIDO1 = FICHERO_CLIENTES.APELLIDO1, APELLIDO2 = FICHERO_CLIENTES.APELLIDO2 FROMCLIENTES INNER JOIN FICHERO_CLIENTES ON FICHERO_CLIENTES.CO_CLIENTE = CLIENTES.CO_CLIENTE Clausula OUTPUT

A partir de la version de SQL Server 2005disponemos de la

clausula OUTPUTpara 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 oDELETE. Es decir, son una copia de los datos "antes" del cambio.

DECLARE @FILAS_ACTUALIZADAS TABLE

( CO_CLIENTEint , NOMBRE varchar(100), APELLIDO1 varchar(100), APELLIDO2 varchar(100) ) UPDATE CLIENTES SET NOMBRE = 'Devjoker', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez'

(46)

WHERE

WHERECO_CLIENTECO_CLIENTE ININ ((1010,, 1111,, 1212))

SELECT

SELECT ** FROMFROM@FILAS_ACTUALIZADAS@FILAS_ACTUALIZADAS Las columnas con prefijo

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

la instrucción UPDATEUPDATE ooINSERTINSERT, pero antes de que se ejecuten los, pero antes de que se ejecuten los

desencadenadores. Es decir, son una copia de los datos "despues" del cambio. desencadenadores. Es decir, son una copia de los datos "despues" del cambio.

INSERTED

INSERTED no se puede utilizar con la cláusulano se puede utilizar con la cláusula OUTPUTOUTPUTen la instrucciónen la instrucción DELETEDELETE..

DECLARE

DECLARE @FILAS_ACTUALIZADAS@FILAS_ACTUALIZADAS TABLETABLE (

( CO_CLIENTECO_CLIENTE intint ,, NOMBRE

NOMBRE varcharvarchar((100100),),

APELLIDO1

APELLIDO1 varcharvarchar((100100),),

APELLIDO2

APELLIDO2 varcharvarchar((100100))

)

UPDATE

UPDATE CLIENTESCLIENTES

SET

NOMBRE

NOMBRE == 'Devjoker''Devjoker',,

APELLIDO1

APELLIDO1 == 'Herrarte''Herrarte',,

APELLIDO2

APELLIDO2 == 'Sánchez''Sánchez' OUTPUT

OUTPUTINSERTEDINSERTED.*.* INTOINTO @FILAS_ACTUALIZADAS@FILAS_ACTUALIZADAS

WHERE

WHERECO_CLIENTECO_CLIENTE ININ ((1010,, 1111,, 1212))

SELECT

SELECT ** FROMFROM@FILAS_ACTUALIZADAS@FILAS_ACTUALIZADAS

Borrar datos en Transact SQL

Delete

(47)

Para borrar datos de una tabla debemos utilizar la sentencia DELETE. Para borrar datos de una tabla debemos utilizar la sentencia DELETE.

Para ejecutar los ejemplos de este capitulo debemos ejecutar el siguiente script, Para ejecutar los ejemplos de este capitulo debemos ejecutar el siguiente script, que crea la tabla "DATOS" y carga registros en ella.

que crea la tabla "DATOS" y carga registros en ella.

CREATE

CREATE TABLE TABLE DATOSDATOS (

(

Id

Id intint identityidentity notnot null,null,

dato

dato varcharvarchar((100100),),

fx_alta

fx_alta datetimedatetime,,

constraint

constraintPK_DATOSPK_DATOS PRIMARYPRIMARY KEYKEY ((IdId))

)

GO GO

DECLARE

DECLARE@i@i intint,,

@dato

@dato varcharvarchar((100100))

set set @i@i == 00 WHILE WHILE ((@i@i <<100100)) BEGIN BEGIN SET SET@i@i == @i@i ++11 set

set @dato@dato == 'Dato:''Dato:' ++ cast(cast(@i@i asasvarcharvarchar))

INSERT

INSERTINTOINTO DATOSDATOS ((datodato,, fx_altafx_alta))

VALUES

VALUES ((@dato@dato,,getdategetdate())())

END END

GO GO

(48)

SELECT

SELECT ** fromfrom DATOSDATOS

Para borrar los registros de la tabla "DATOS" ejecutaremos la siguiente instrucción. Para borrar los registros de la tabla "DATOS" ejecutaremos la siguiente instrucción. Notese que no se especifica ninguna condición WHERE por lo que se borran todos los Notese que no se especifica ninguna condición WHERE por lo que se borran todos los datos de la tabla.

datos de la tabla.

DELETE DELETE FROM

FROMDATOSDATOS

Lógicamente podemos especicar que registros queremos borrar a través de la Lógicamente podemos especicar que registros queremos borrar a través de la clausula WHERE.

clausula WHERE.

DELETE DELETE FROM

FROMDATOSDATOS WHERE

WHERE IdId==1212

Cuando borramos datos de una tabla, podemos obtener el número de filas que han Cuando borramos datos de una tabla, podemos obtener el número de filas que han sido afectadas por la instrucción a través de la variable @@RowCount.

sido afectadas por la instrucción a través de la variable @@RowCount. El siguiente ejemplo ilustra el uso de @@RowCount.

El siguiente ejemplo ilustra el uso de @@RowCount.

DELETE DELETE FROM

FROMDATOSDATOS WHERE

WHERE IdId==1717

SELECT

SELECT @@ROWCOUNT@@ROWCOUNT Clausula OUTPUT

Clausula OUTPUT

A partir de la version de

A partir de la version de SQL Server 2005SQL Server 2005disponemos de ladisponemos de la

clausula

clausula OUTPUTOUTPUTpara recuperar los valores que hemos insertado. Al igual que en unpara recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas

(49)

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE oDELETE. Es decir, son una copia de los datos "antes" del

cambio.

DECLARE @FILAS_BORRADAS TABLE ( Id int, dato varchar(100), fx_alta datetime ) DELETE FROM DATOS

OUTPUT DELETED.* INTO @FILAS_BORRADAS WHERE Id=17

SELECT * from @FILAS_BORRADAS Truncate Table

Para borrar datos de forma masiva disponemos de la instrucción TRUNCATE TABLE, que borra todos los datos de una tabla.

TRUNCATE TABLE DATOS

Cuando trabajamos con TRUNCATE TABLE debemos tener en cuenta las siguientes consideraciones:

• TRUNCATE TABLE no admite la clausula WHERE.

• No podemos ejecutar TRUNCATE TABLE sobre tablas que sean "padres" en