Base de Datos Avanzado I

1
CARRERAS PROFESIONALES CIBERTEC

Base de Datos
Avanzado I
2
BASE DE DATOS AVANZADO I I 3
CIBERTEC CARRERAS PROFESIONALES

NDICE

Pgina
Presentacin 5
Red de contenidos 7
Unidad de aprendizaje 1 Lenguaje de manipulacin de datos (DML)
1.1 Tema 1 : Introduccin 11
1.2 Tema 2 : Lenguaje para la manipulacin de datos DML
1.2.1. : Operadores 17
1.2.2. : Funciones para el manejo de datos 22
1.2.3. : Comandos de LMD 25
1.2.4. Declaracin MERGE 39
1.3 Tema 3 : Recuperacin avanzada de datos
1.3.1. : Combinacin de tablas 40
1.3.2. : Datos agrupados GROUP BY, HAVING 44
1.3.3. : Agregar conjunto de resultados: UNION 50
1.3.4. : Resumen de datos: operador CUBE y ROLLUP 52
Unidad de aprendizaje 2: Programacin TRANSACT SQL
2.1 Tema 4 : Fundamentos de Programacin TRANSACT SQL
2.1.1. : Construccin de programacin TRANSACT SQL 61
2.1.2. : Variables 61
2.2 Tema 5 : Herramientas para el control de Flujos
2.2.1. : Estructura de control IF 65
2.2.2. : Estructura condicional CASE 66
2.2.3. : Estructura de control WHILE 49
2.3 Tema 6 : Control de Errores en TRANSACT SQL
2.3.1. : Funciones especiales de Error 72
2.3.2. : Variable de sistema @@ERROR 73
2.3.3. : Generar un error RAISERROR 74
2.4. Tema 7 : Cursores en TRANSACT SQL
2.4.1. : Declare Cursor 76
2.4.2. : Abrir un Cursor 78
2.4.3. : Cerrar el cursor 79

4
Unidad de aprendizaje 3: Programacin Avanzada TRANSACT SQL
3.1 Tema 8 : Programacin avanzada TRANSACT SQL
3.1.1. : Funciones definida por el usuario 91
3.1.2. : Procedimientos almacenados 97
3.1.3. : Modificar datos con procedimientos almacenados 107
3.1.4. : Transacciones en TRANSACT SQL 109
3.1.5. : Triggers o disparadores 114
Unidad de aprendizaje 4: Manejo de datos XML en SQL SERVER
4.1 Tema 9 : Introduccin 127
4.1.1. : Por que utilizar bases de datos relacionales para
datos XML
127
4.1.2. : Tipos de datos XML 128
4.1.3. : FOR XML y mejoras OPENXML 130
4.2 Tema 10 : Procesamiento XML en SQL SERVER 131
4.2.1. : Tipos de datos XML 131
4.2.2. : Almacenamiento de datos XML 132
4.2.3. : Recuperando datos de tipo XML 135
4.2.4. : Recuperar datos con OPENXML 147
Unidad de aprendizaje 5: Manejo de Usuarios en SQL SERVER
5.1. Tema 11 : Introduccin 155
5.1.1. : Entidades de seguridad 155
5.1.2. : Autenticacin 157
5.1.3. : Inicios de sesin y usuarios 159
5.1.4. : Permisos en el motor de base de datos 169
Unidad de aprendizaje 6: Seguridad y Restauracin en SQL SERVER
6.1. Tema 12 : Introduccin a las estrategias de seguridad y
restauracin
181
6.1.1. : Impacto del modelo de recuperacin de copia de
seguridad y restauracin
181
6.1.2. : Diseo de la estrategia de copia de seguridad 182
6.1.3. : Copia de Seguridad en SQL Server 183
6.1.4. : Restaurando una copia de seguridad 195


PRESENTACIN

Base de Datos Avanzado I es un curso que pertenece a la Escuela de Tecnologas de
Informacin y se dicta en las carreras de Administracin y Sistemas, y Computacin e
Informtica. El presente manual ha sido desarrollado para que los alumnos del curso de
Base de Datos Avanzado I puedan aplicar los conocimientos adquiridos en el curso de
Base de Datos teora y laboratorio. Todo ello, en conjunto, le permitir manejar los datos
de una base de datos relacional utilizando comandos TRANSACT-SQL.

El manual para el curso ha sido diseado bajo la modalidad de unidades de aprendizaje,
las que se desarrollan durante semanas determinadas. En cada una de ellas, hallar los
logros, que debe alcanzar al final de la unidad; el tema tratado, el cual ser ampliamente
desarrollado; y los contenidos, que debe desarrollar, es decir, los subtemas. Por ltimo,
encontrar las actividades que deber desarrollar en cada sesin, que le permitirn
reforzar lo aprendido en la clase.

El curso es eminentemente prctico. Se inicia con la creacin de la base de datos de
trabajo usando el lenguaje Transact/SQL en el manejador de base de datos relacional
SQL Server 2008. Posteriormente, se efecta un repaso de las operaciones bsicas de
manipulacin de datos (Data Manipulation Lenguaje DML) para hacer uso de
comandos que se emplean en la insercin, modificacin y eliminacin de los mismos. A
continuacin vamos a realizar operaciones de consulta avanzada de base de datos
utilizando clausulas de unin, de agrupamiento, de combinacin, entre otras. A
continuacin aprenderemos a manejar la programacin TRANSACT-SQL aplicando los
conceptos en cursores, procedimientos almacenados, funciones y desencadenantes o
trigger. Para integrar los temas de actualidad, aprenderemos a manejar datos XML en
la base de datos relacional y finalmente, en la ltima parte del manual, aprenderemos a
manejar usuarios y generar copias de respaldo de una base de datos y restaurar una
base de datos de SQL SERVER.

6


RED DE CONTENIDOS

Base de Datos Avanzado II
Lenguaje de
Manipulacin
de datos
LMD LDD
Programacin
Transact SQL
Estructuras Cursores
Manejo de
datos XML
FOR
XML
OPEN
XML
8


LENGUAJE DE MANIPULACIN DE DATOS (DML)
LOGRO DE LA UNIDAD DE APRENDIZAJE
Al trmino de la unidad, el alumno recupera, inserta, actualiza y elimina
informacin de una base de datos aplicando mltiples condiciones de comparacin
o funciones para el manejo de campos tipo fecha. Obtiene registros originados por
la seleccin de uno o varios grupos haciendo uso de las funciones agrupamiento y
columna procedentes de dos o ms tablas.

TEMARIO
1.1. Introduccin
1.1.1. Tablas a usar en el curso
1.1.2. Manejo de Esquemas
1.2. Lenguaje para la manipulacin de datos DML (3 horas)
1.2.1. Operadores
1.2.2. Funciones usados en las consultas condicionales
1.2.2.1. Funciones para el manejo de fecha
1.2.2.2. Funciones pare el manejo de cadena
1.2.2.3. Funciones de conversin de datos
1.2.3. Insercin de datos: INSERT
1.2.4. Actualizacin de datos: UPDATE
1.2.5. Eliminacin de datos: DELETE
1.2.6. Seleccin de datos: SELECT
1.2.7. Instruccin MERGE
1.3. Recuperacin avanzada de datos (6 horas)
1.3.1. Combinacin de tablas: J OIN
1.3.2. Consultas agregadas: empleo de GROUP BY, HAVING. Empleo de funciones
agregadas: SUM, MIN, MAX, AVG, COUNT.
1.3.3. Agregar conjunto de resultados: UNION
1.3.4. Resumen de datos: CUBE
1.3.5. Resumen de datos: ROLLUP

ACTIVIDADES PROPUESTAS
Los alumnos implementan sentencias SQL para recuperar y actualizar datos en una
base de datos relacional.
Los alumnos implementan sentencias SQL para agrupar y resumir los datos.

UNIDAD DE
APRENDIZAJ E
1
10

1.1 INTRODUCCION

1.1.1 Estructura de la Base de Datos Negocios2011

En el curso, usaremos las tablas de la base de datos NEGOCIOS2011. A
continuacin, se muestra la estructura de algunas tablas de la base de datos
NEGOCIOS2011 a utilizar en el presente curso:

Tabla Paises
Contiene informacin o relacin de pases en donde viven los clientes o empleados.
La tabla Paises se encuentra en el esquema Venta

Columna Tipo de datos Nulos Descripcin
Idpais char ( 3) No NULL I dent i f i cador de pa s.
Cl ave pr i mar i a
NombrePais Var char ( 40) No NULL Nombr e del pa s.

Tabla Categorias
Contiene informacin o relacin de categoras en donde se encuentran registrados los
productos. La tabla Categorias se encuentra en el esquema Compra.

IdCategoria i nt No NULL I dent i f i cador de cat egor a.
Cl ave pr i mar i a
NombreCategoria Var char ( 40) No NULL Nombr e de l a cat egor a.
Descripcin Text Nul l Descr i pci n de l a cat egor a

Tabla Clientes
Contiene informacin o relacin de clientes que se encuentran registrados en la base
de datos. La tabla Clientes se encuentra en el esquema Venta

12
IdCliente Char ( 5) No NULL I dent i f i cador de cl i ent e.
Cl ave pr i mar i a
NomCliente Var char ( 40) No NULL Nombr e del cl i ent e.
DirCliente Var char ( 80) No NULL Di r ecci n del cl i ent e
Idpais Char ( 3) No NULL I dent i f i cador de pa s.
Cl ave ext er na de pa ses.
fonoCliente Var char ( 15) NULL Tel f ono del cl i ent e

Tabla Proveedores
Contiene informacin o relacin de los proveedores que se encuentran registrados en
la base de datos. La tabla Proveedores se encuentra en el esquema Compra

IdProveedor I nt No NULL I dent i f i cador de pr oveedor .
Cl ave pr i mar i a
nomProveedor Var char ( 80) No NULL Nombr e del pr oveedor .
dirProveedor Var char ( 100) No NULL Di r ecci n del pr oveedor .
nomContacto Var char ( 80) No NULL Nombr e del cont act o del
pr oveedor .
cargoContacto Var char ( 50) No NULL Car go del cont act o del pr oveedor
idpais Char ( 3) No NULL I dent i f i cador del pa s.
Cl ave ext er na de pa ses
fonoProveedor Var char ( 15) No NULL Tel f ono del pr oveedor .
faxProveedor Var char ( 15) No NULL Fax del pr oveedor .

Tabla Productos
Contiene informacin o relacin de los productos que ofrecen para la venta y que se
encuentran registrados en la base de datos. La tabla Productos se encuentra en el
esquema Compra.

IdProducto I nt No NULL I dent i f i cador de pr oduct o.
Cl ave pr i mar i a
nomProducto var char ( 80) No NULL Nombr e del pr oduct o.
idProveedor I nt No NULL I dent i f i cador del pr oveedor .
Cl ave ext er na de pr oveedor es
idCategoria I nt No NULL I dent i f i cador de l a cat egor a.
Cl ave ext er na de cat egor as.
cantxUnidad var char ( 50) No NULL Cant i dad de pr oduct os por
uni dad al macenada
precioUnidad deci mal ( 10, 2) No NULL Pr eci o por uni dad del pr oduct o
UniEnExistencia smal l i nt No NULL Uni dades en exi st enci a o st ock
del pr oduct o
UniEnPedido smal l i nt No NULL Uni dades que se encuent r an en
pedi do.

Tabla Cargos
Contiene informacin o relacin de los cargos que se le asigna a cada empleado que
se encuentran registrados en la base de datos. La tabla Cargos se encuentra en el
esquema RRHH.

IdCargo I nt No NULL I dent i f i cador de car go.
Cl ave pr i mar i a
desCargo var char ( 30) No NULL Descr i pci n del car go

Tabla Distritos
Contiene informacin o relacin de los distritos que se le asigna a cada empleado que
se encuentran registrados en la base de datos. La tabla Distritos se encuentra en el
esquema RRHH.
14

IdDistrito I nt No NULL I dent i f i cador de di st r i t o.
Cl ave pr i mar i a
nomDistrito var char ( 50) No NULL Nombr e del di st r i t o

Tabla Empleados
Contiene informacin o relacin de los empleados que se encuentran registrados en la
base de datos. La tabla Empleados se encuentra en el esquema RRHH.

IdEmpleado I nt No NULL I dent i f i cador del empl eado.
Cl ave pr i mar i a
nomEmpleado var char ( 50) No NULL Nombr e del empl eado
apeEmpleado var char ( 50) No NULL Apel l i do del empl eado
fecNac Dat et i me No NULL Fecha de Naci mi ent o
dirEmpleado var char ( 100) No NULLL Di r ecci n del empl eado
idDistrito I nt No NULL I dent i f i cador de di st r i t o.
Cl ave ext er na de di st r i t os.
fonoEmpleado var char ( 15) NULL Tel f ono del empl eado
idcargo I nt No NULL I dent i f i cador de car go, cl ave
ext er na de car gos
fecContrata Dat et i me No NULL Fecha de cont r at aci n
fotoEmpleado I mage NULL Fot o del empl eado

Tabla PedidosCabe
Contiene informacin o relacin de la cabecera de los pedidos que se registran en el
proceso de la venta y que se encuentran registrados en la base de datos. La tabla
PedidosCabe se encuentra en el esquema Venta.

IdPedido I nt No NULL I dent i f i cador de l a cabecer a de
pedi do. Cl ave pr i mar i a
idcliente var char ( 5) No NULL I dent i f i cador de cl i ent e.
Cl ave ext er na de cl i ent es
idEmpleado I nt No NULL I dent i f i cador del empl eado.
Cl ave ext er na de empl eados
fechaPedido Dat et i me No NULL Fecha de sol i ci t ud del pedi do
fechaEntrega Dat et i me No NULL Fecha de ent r ega del pedi do
fechaEnvio Dat et i me No NULL Fecha de env o del pedi do
enviopedido char ( 1) No NULL I ndi ca si el pedi do ha si do o
no ent r egado
destinatario var char ( 60) No NULL Nombr e del dest i nat ar i o
dirdestinatario var char ( 100) No NULL Di r ecci n del dest i nat ar i o

Tabla PedidosDeta
Contiene informacin o relacin del detalle de los productos solicitados en los pedidos
de venta y que se encuentran registrados en la base de datos. La tabla PedidosDeta
se encuentra en el esquema Compra.

IdPedido I nt No NULL I dent i f i cador de pedi do.
Cl ave ext er na de pedi doscabe
idProducto I nt No NULL I dent i f i cador del pr oduct o.
Cl ave ext er na de pr oduct o
precioUnidad Deci mal ( 10, 2) No NULL Pr eci o del pr oduct o en el pedi do
Cantidad smal l i nt No NULL Cant i dad sol i ci t ada del pr oduct o
Descuento Deci mal ( 10, 2) No NULL Cant i dad de pr oduct os por uni dad
al macenada

16
1.1.2 Asignar nombres a los objetos de una Base de Datos
A menos que se especifique lo contrario, todas las referencias de Transact-SQL al
nombre de un objeto de base de datos pueden ser un nombre de cuatro partes con el
formato siguiente:
server_name.[database_name].[schema_name].object_name
database_name.[schema_name].object_name
schema_name.object_name
object_name
server_name: Especifica un nombre de servidor vinculado o un nombre de servidor
remoto.

database_name: Especifica el nombre de una base de datos de SQL Server si el
objeto reside en una instancia local de SQL Server. Cuando el objeto est en un
servidor vinculado, database_name especifica un catlogo de OLE DB.

schema_name: Especifica el nombre del esquema que contiene el objeto si dicho
objeto se encuentra en una base de datos de SQL Server. Si el objeto se encuentra en
un servidor vinculado, schema_name especifica un nombre de esquema OLE DB.
object_name: Cuando se hace referencia a un objeto especfico, no siempre hay que
especificar el servidor, la base de datos y el esquema del SQL Server Database
Engine (Motor de base de datos de SQL Server) para identificar el objeto. No obstante,
si no se encuentra el objeto, se muestra un error.

1.1.3 Manejo de Esquemas
Todos los objetos dentro de una base de datos, se crean dentro de un esquema. Los
esquemas permiten agrupar objetos y ofrecer seguridad.
La definicin de un esquema es simple, slo se necesita identificar el comienzo de la
definicin con la instruccin CREATE SCHEMA y una clusula adicional
AUTHORIZATION y a continuacin definir cada dominio, tabla, vista y dems en el
esquema. Para crear los esquemas que se implementarn en la base de datos
Negocios2011 autorizado por el propietario dbo:

USE NEGOCI OS2011
GO

- - CREAR LOS ESQUEMAS DE LA BASE DE DATOS
CREATE SCHEMA VENTA AUTHORI ZATI ON DBO
GO

CREATE SCHEMA COMPRA AUTHORI ZATI ON DBO
GO

CREATE SCHEMA RRHH AUTHORI ZATI ON DBO
GO

Para listar los esquemas creados por el
propietario de la base de datos (el
database owner - dbo) se invoca a la
tabla sys.schemas, tal como se
muestra:

1.2 LENGUAJE DE MANIPULACION DE DATOS

1.2.1. Operadores
Un operador es un smbolo que especifica una accin que se realiza en una o ms
expresiones. A continuacin, detallamos las categoras de operadores que utilizan
SQL Server.

1.2.1.1. Operadores aritmticos
Son aquellos que realizan operaciones matemticas entre dos expresiones numricas.

18
Operador Significado
+(sumar) Suma
- (restar) Resta
* (multiplicar) Multiplicacin
/ (dividir) Divisin
% (Mdulo) Devuelve el resto entero de una divisin. Por ejemplo, 12 % 5 =2
porque el resto de 12 dividido entre 5 es 2.

Los operadores de suma (+) y resta (-) son utilizados para realizar operaciones
aritmticas sobre valores datetime y smalldatetime.

1.2.1.2. Operadores de Asignacin
El operador (=) es slo el operador de asignacin del SQL Server. En el siguiente
ejemplo, definimos la variable @num, asigne un valor a dicha variable.

El operador de asignacin se utiliza para establecer encabezados de una columna. En
el siguiente ejemplo, mostrar los encabezados de las columnas a la tabla Distritos.

1.2.1.3. Operadores de comparacin
Los operadores de comparacin permiten comprobar dos expresiones retornando un
valor verdadero o falso, es decir, un dato Boolean. Se pueden utilizar en todas las
expresiones excepto en las de los tipos de datos text, ntext o image. En la siguiente
tabla, se presentan los operadores de comparacin Transact-SQL.
Operador de Comparacin Significado
= Igual a
> Mayor que
< Menor que
>= Mayor o igual que
<= Menor o igual que
DECLARE @NUM I NT
SET @NUM=15
PRI NT ' EL NUMERO I NGRESADO ES: ' + STR( @NUM)
<> No es igual a
!= No es igual a (no es del estndar ISO)
!< No es menor que (no es del estndar ISO)
!> No es mayor que (no es del estndar ISO)

1.2.1.4. Operadores lgicos
Los operadores lgicos comprueban la veracidad de alguna condicin. stos, como los
operadores de comparacin, devuelven el tipo de datos Boolean con el valor TRUE,
FALSE o UNKNOWN.
Operador Significado
ALL TRUE si el conjunto completo de comparaciones es TRUE.
AND TRUE si ambas expresiones booleanas son TRUE.
ANY TRUE si cualquier miembro del conjunto de comparaciones es TRUE.
BETWEEN TRUE si el operando est dentro de un intervalo.
EXISTS TRUE si una subconsulta contiene cualquiera de las filas.
IN TRUE si el operando es igual a uno de la lista de expresiones.
LIKE TRUE si el operando coincide con un patrn.
NOT Invierte el valor de cualquier otro operador booleano.
OR TRUE si cualquiera de las dos expresiones booleanas es TRUE.
SOME TRUE si alguna de las comparaciones de un conjunto es TRUE.

1.2.1.5. Operador BETWEEN
Especifica un intervalo que se va a evaluar, retorna un valor boolean; retorna TRUE si
el valor de la expresin a evaluar es mayor o igual que el valor de inicio expresin y
menor o igual que el valor de fin expresin.
NOT BETWEEN devuelve TRUE si el valor de expresin a evaluar es menor que el
valor de inicio expresin y mayor que el valor de fin expresin.
Sintaxis:

20
EXPRESI N_A_EVALUAR [ NOT] BETWEEN I NI CI O_EXPRESI N AND
FI N_EXPRESI N

Ejemplo: Mostrar todos los productos donde el valor del precioUnidad se encuentre
entre 27 a 30

USE NEGOCI OS2011
GO

SELECT P. NOMPRODUCTO ' PRODUCTO' ,
C. NOMCATEGORI A ' CATEGORI A'
FROM COMPRA. PRODUCTOS P
J OI N COMPRA. CATEGORI AS C ON P. I DCATEGORI A = C. I DCATEGORI A
WHERE P. PRECI OUNI DAD BETWEEN 27 AND 30
ORDER BY P. NOMPRODUCTO
GO

1.2.1.6. Operador LIKE
Determina si una cadena de caracteres especfica coincide con un patrn
determinado. Un patrn puede contener caracteres normales y caracteres comodn.
Durante la operacin de bsqueda de coincidencias de patrn, los caracteres
normales deben coincidir exactamente con los caracteres especificados en la cadena
de caracteres. Sin embargo, los caracteres comodn pueden coincidir con fragmentos
arbitrarios de la cadena. La utilizacin de caracteres comodn hace que el operador
LIKE sea ms flexible que los operadores de comparacin de cadenas =y !=.

Sintaxis
MATCH_EXPRESSI ON [ NOT] LI KE PATTERN [ ESCAPE ESCAPE_CHARACTER]

Argumentos:
match_expression: Es cualquier expresin vlida de tipo de datos de caracteres.
Pattern: Es la cadena de caracteres especfica que se busca en match_expression;
puede incluir los siguientes caracteres comodn vlidos. pattern puede tener 8.000
bytes como mximo.

Carcter
comodn
Descripcin Ejemplo
% Cualquier cadena de
cero o ms caracteres.
WHERE title LIKE '%computer%' busca todos los ttulos de
libros que contengan la palabra 'computer' en el ttulo.
_ (carcter de
subrayado)
Cualquier carcter. WHERE au_fname LIKE _ean busca todos los nombres de
cuatro letras que terminen en ean (Dean, Sean, etc.)
[ ] Cualquier carcter del
intervalo ([a-f]) o conjunto
([abcdef]) que se ha
especificado.
WHERE au_lname LIKE [C-P]arsen busca apellidos de
autores que terminen en arsen y empiecen por cualquier
carcter individual entre C y P, como Carsen, Larsen,
Karsen, etc.
[^] Cualquier carcter que
no se encuentre en el
intervalo ([^a-f]) o
conjunto ([^abcdef]) que
se ha especificado.
WHERE au_lname LIKE de[^l]% busca todos los apellidos
de autores que empiecen por de y en los que la siguiente
letra no sea l.

escape_character: Es un carcter que se coloca delante de un carcter comodn para
indicar que el comodn no debe interpretarse como un comodn, sino como un carcter
normal. escape_character es una expresin de caracteres que no tiene ningn valor
predeterminado y se debe evaluar como un nico carcter.

Ejercicio:
USE NEGOCI OS2011
GO

- - RETORNA LOS REGI STROS DE EMPLEADOS DONDE SU APELLI DO TERMI NE
EN KI NG
SELECT * FROM RRHH. EMPLEADOS
WHERE APEEMPLEADO LI KE ' %KI NG'
GO

- - RETORNA LOS REGI STROS DE EMPLEADOS DONDE SU APELLI DO I NI CI E
CON KI NG
WHERE APEEMPLEADO LI KE ' KI NG%'
GO

22
- - RETORNA LOS REGI STROS DE EMPLEADOS DONDE SU APELLI DO
CONTENGA LA EXPRESI ON KI NG
WHERE APEEMPLEADO LI KE ' %KI NG%'
GO

1.2.2. Funciones para el manejo de datos

1.2.2.1. Funciones para el manejo de fechas

Funcin Descripcin
DATEADD Devuelve un valor date con el intervalo number especificado,
agregado a un valor datepart especificado de ese valor date.

DATEADD (datepart , number , date )

DECLARE @FECHA DATE = ' 1- 8- 2011'
SELECT ' YEAR' ' PERI ODO ' , DATEADD( YEAR, 1, @FECHA) ' NUEVA
FECHA'
UNI ON ALL
SELECT ' QUARTER' , DATEADD( QUARTER, 1, @FECHA)
UNI ON ALL
SELECT ' MONTH' , DATEADD( MONTH, 1, @FECHA)
UNI ON ALL
SELECT ' DAY' , DATEADD( DAY, 1, @FECHA)
UNI ON ALL
SELECT ' WEEK' , DATEADD( WEEK, 1, @FECHA)
GO
DATEDIFF Devuelve el nmero de lmites datepart de fecha y hora entre dos
fechas especificadas.

DATEDIFF ( datepart , startdate , enddate )

SET DATEFORMAT DMY
DECLARE @FECHAI NI CI AL DATE = ' 01- 08- 2011' ;
DECLARE @FECHAFI NAL DATE = ' 01- 09- 2011' ;
SELECT DATEDI FF( DAY, @FECHAI NI CI AL, @FECHAFI NAL) AS
' DURACI ON'
DATENAME Devuelve una cadena de caracteres que representa el datepart
especificado de la fecha especificada.

DATENAME ( datepart , date )

SELECT DATENAME( MONTH, GETDATE( ) ) AS ' MES' ;
DATEPART Devuelve un entero que representa el datepart especificado del
date especificado.

DATEPART ( datepart , date )

SELECT DATEPART( MONTH, GETDATE( ) ) AS ' MES' ;
DAY Devuelve un entero que representa la parte del da datepart de la
fecha especificada.

SELECT DAY( ' 01/ 9/ 2011' ) AS ' D A DEL MES' ;
GETDATE Devuelve la fecha del sistema

SELECT GETDATE( ) ' FECHA DEL SI STEMA' ;
MONTH Devuelve un entero que representa el mes de date especificado.
MONTH devuelve el mismo valor que DATEPART (month, date).

SELECT MONTH( GETDATE( ) ) AS ' MES DE LA FECHA DE
SI STEMA' ;
YEAR Devuelve un entero que representa el ao de date especificado.
YEAR devuelve el mismo valor que DATEPART (year, date).

SELECT YEAR( GETDATE( ) ) AS ' AO DE LA FECHA DE SI STEMA' ;

1.2.2.2. Funciones para el manejo de cadenas

Funcin Descripcin
LEFT Devuelve la parte izquierda de una cadena de caracteres con el
nmero de caracteres especificado.

LEFT ( character_expression , integer_expression )
24
LEN Devuelve el nmero de caracteres de la expresin de cadena
especificad, excluidos los espacios en blanco finales.

LEN ( string_expression )
LOWER Devuelve una expresin de caracteres despus de convertir en
minsculas los datos de caracteres en maysculas.

LOWER ( character_expression )
LTRIM Devuelve una expresin de caracteres tras quitar todos los espacios
iniciales en blanco.

LTRIM ( character_expression )
RTRIM Devuelve una cadena de caracteres despus de truncar todos los
espacios en blanco finales.

RTRIM ( character_expression )
SUBSTRING Devuelve parte de una expresin de caracteres, binaria, de texto o
de imagen. Para obtener ms informacin acerca de los tipos de
datos vlidos de SQL Server que se pueden usar con esta funcin.

SUBSTRING (value_expression, start_expression,
length_expression)
UPPER Devuelve una expresin de caracteres con datos de caracteres en
minsculas convertidos a maysculas.

UPPER ( character_expression )

Ejercicio

- - MANEJ O DE CADENAS: RETORNA LA EXPRESI ON BASE CONVERTI DA EN
MAYSCULAS
DECLARE @CADENA VARCHAR( 30)
SELECT @CADENA = ' BASE DE DATOS AVANZADO ' ;
SELECT LEFT( UPPER( LTRI M( @CADENA) ) , 4) AS ' CADENA RESULTANTE'
GO

1.2.2.3. Funciones de conversin

Convierte una expresin de un tipo de datos en otro tipo de dato definido en SQL
Server 2008.

Funcin Descripcin
CAST Convierte una expresin a un tipo de datos

CAST ( expr esi n AS t i po_dat o[ ( l ongi t ud) ] )
CONVERT Convierte una expresin a un tipo de datos indicando un estilo.

CONVERT ( t i po_dat o [ ( l ongi t ud) ] , expr esi n [ , est i l o] )

Ejemplo

USE NEGOCI OS2011
GO

SELECT DI STI NCT CAST( P. NOMPRODUCTO AS CHAR( 15) ) AS NOMBRE,
CONVERT( DECI MAL( 10, 2) , P. PRECI OUNI DAD) AS ' PRECI O UNI TARI O'
FROM COMPRA. PRODUCTOS
WHERE P. NOMPRODUCTO LI KE ' PAN%' ;
GO

1.2.3. Comandos de LMD (Lenguaje de Manipulacin de Datos)

1.2.3.1. Insertar registros: INSERT

Agrega una o varias filas nuevas a una tabla o una vista en SQL Server 2008.

26
Sintaxis:

I NSERT
{ [ TOP ( expr esi n) [ PERCENT ] ] [ I NTO ] { <OBJ ETO> }
{
{ VALUES ( { DEFAULT | NULL | expr essi on } [ , . . . n ] )
[ , . . . n ]
| t abl e_der i vada
| sent enci a_ej ecut ar
| <t abl e_or i gen>
| DEFAULT VALUES
}
}
}

El formato bsico de la sentencia es:

INSERT INTO tabla [(columna1, columna2, columnan)] VALUES (expr1, expr2, exprn)

Tabla es el nombre de la tabla donde se desea ingresar los nuevos datos.
Columna es una lista opcional de nombres de campo en los que se insertarn
valores en el mismo nmero y orden que se especificarn en la clusula VALUES.
Si no se especifica la lista de campos, los valores de expr en la clusula VALUES
deben ser tantos como campos tenga la tabla y en el mismo orden que se
definieron al crear la tabla.
Expr es una lista de expresiones o valores constantes, separados por comas,
para dar valor a los distintos campos del registro que se aadir a la tabla. Las
cadenas de caracteres debern estar encerradas entre apstrofes.

1.2.3.1.1. Insertar un nico registro

A. Especificando todos los campos a ingresar.

Cada sentencia INSERT aade un nico registro a la tabla.
En el ejemplo, se han especificado todos los campos con sus respectivos valores. Si
no se ingresara valores a un campo, este se cargar con el valor DEFAULT o NULL
(siempre y cuando haya sido especificado en la estructura de la tabla). Un valor nulo
NULL- no significa blancos o ceros, sino que el campo nunca ha tenido un valor.

USE NEGOCI OS2011
GO

I NSERT I NTO VENTA. CLI ENTES( I DCLI ENTE, NOMCLI ENTE, DI RCLI ENTE,
I DPAI S, FONOCLI ENTE)
VALUES ( ' DRATR' , ' DARI O TRAGODARA' , ' CALLE LUI S MI RO 123' ,
' 003' , ' 3245566' ) ;
GO

SELECT * FROM VENTA. CLI ENTES
GO

B. Especificando nicamente los valores de los campos.

Si no se especifica la lista de campos, los valores en la clusula VALUES deben ser
tantos como campos tenga la tabla y en el mismo orden que se definieron al crear la
tabla. Si se va a ingresar parcialmente los valores en una tabla, se debe especificar el
nombre de los campos a ingresar, como en el ejemplo A.

USE NEGOCI OS2011
GO

I NSERT I NTO VENTA. CLI ENTES
VALUES ( ' DRAPR' , ' DARI O PRADO' , ' CALLE 32' , ' 001' , ' 3245566' ) ;
GO

SELECT * FROM VENTA. CLI ENTES
GO

1.2.3.1.2. Insertar varias filas de datos

En el siguiente ejemplo, se usa el constructor de valores de tabla para insertar tres
filas en la tabla Venta.Paises en una instruccin INSERT. Dado que los valores para
todas las columnas se suministran e incluyen en el mismo orden que las columnas de
la tabla, no es necesario especificar los nombres de columna en la lista de columnas.

28
USE NEGOCI OS2011
GO

I NSERT I NTO VENTA. PAI SES
VALUES ( ' 095' , ' NORUEGA' ) , ( ' 096' , ' I SLANDI A' ) , ( ' 097' ,
' GRECI A' ) ;
GO

SELECT * FROM VENTA. PAI SES P
WHERE P. I DPAI S I N ( ' 095' , ' 096' , ' 097' )
GO

A. Insertar Mltiples Registros

Utilizando el comando SELECT, podemos agregar mltiples registros. Veamos un
ejemplo:

USE NEGOCI OS2011
GO

CREATE TABLE RRHH. EMPLEADOS2011(
I DEMPLEADO I NT NOT NULL,
NOMEMPLEADO VARCHAR( 50) NOT NULL,
APEEMPLEADO VARCHAR( 50) NOT NULL,
FONOEMPLEADO VARCHAR( 15) NULL,
DI REMPLEADO VARCHAR( 100) NOT NULL,
I DDI STRI TO I NT NOT NULL
)
GO

I NSERT I NTO RRHH. EMPLEADOS2011
SELECT A. I DEMPLEADO, A. NOMEMPLEADO, A. APEEMPLEADO,
A. FONOEMPLEADO,
A. DI REMPLEADO, A. I DDI STRI TO
FROM RRHH. EMPLEADOS AS A
WHERE YEAR( A. FECCONTRATA) = ' 2011
GO

SELECT * FROM RRHH. EMPLEADOS2011
GO

B. Insertar datos en una variable de tabla

En el siguiente ejemplo, se especifica una variable de tabla como el objeto de destino.

USE NEGOCI OS2011;
GO

- - CREA UNA VARI ABLE TI PO TABLA
DECLARE @PRODUCTO TABLE(
PRODUCTOI D I NT NOT NULL,
PRODUCTONOMBRE VARCHAR( 100) NOT NULL,
PRODUCTOPRE AS DECI MAL,
PRODUCTOCAN I NT) ;
GO

- - I NSERTA VALORES DENTRO DE LA VARI ABLE TI PO TABLA
I NSERT I NTO @PRODUCTO ( PRODUCTOI D, PRODUCTONOMBRE, PRODUCTOPRE,
PRODUCTOCAN)
SELECT I DPRODUCTO, NOMPRODUCTO, PRECI OUNI DAD,
UNI DADESENEXI STENCI A
WHERE PRECI OUNI DAD > 100;

- - VER EL CONJ UNTO DE VALORES DE LA VARI ABLE TI PO TABLA
SELECT * FROM @PRODUCTO;
GO

30
C. Insertar datos en una tabla con columnas que tienen valores
predeterminados

USE NEGOCI OS2011;
GO

CREATE TABLE DBO. PRUEBA
(
COLUMNA_1 AS ' COLUMNA CALCULADA ' + COLUMNA_2,
COLUMNA_2 VARCHAR( 30) DEFAULT ( ' COLUMNA POR DEFECTO' ) ,
COLUMNA_3 ROWVERSI ON,
COLUMNA_4 VARCHAR( 40) NULL
)
GO

I NSERT I NTO DBO. PRUEBA ( COLUMN_4) VALUES ( ' VALOR' ) ;
I NSERT I NTO DBO. PRUEBA ( COLUMN_2, COLUMN_4) VALUES ( ' VALOR' ,
' VAL' ) ;
I NSERT I NTO DBO. PRUEBA ( COLUMN_2) VALUES ( ' VALOR' ) ;
I NSERT I NTO PRUEBA DEFAULT VALUES;
GO

SELECT COLUMNA_1, COLUMAN_2, COLUMNA_3, COLUMNA_4
FROM DBO. PRUEBA;
GO

1.2.3.2. Actualizacin de datos: UPDATE

La sentencia UPDATE se utiliza para cambiar el contenido de los registros de una o
varias columnas de una tabla de la base de datos. Su formato es:

UPDATE Nombr e_t abl a
SET nombr e_col umna1 = expr 1, nombr e_col umna2 = expr 2, . . .
[ WHERE {condi ci n}]
Nombre_tabla nombre de la tabla donde se cambiar los datos.
Nombre_columna columna cuyo valor se desea cambiar. En una misma
sentencia UPDATE pueden actualizarse varios campos de cada registro.
Expr es el nuevo valor que se desea asignar al campo. La expresin puede ser
un valor constante o una subconsulta. Las cadenas de caracteres debern
estar encerradas entre comillas. Las subconsultas entre parntesis.

La clusula WHERE sigue el mismo formato que la vista en la sentencia SELECT y
determina qu registros se modificarn.

1.2.3.2.1. Actualizar varias columnas

En el siguiente ejemplo, se actualizan los valores de las columnas precioUnidad y
UnidadesEnExistencia para todas las filas de la tabla Productos.

USE NEGOCI OS2011;
GO

UPDATE COMPRA. PRODUCTOS
SET PRECI OUNI DAD = 6000, UNI DADESENEXI STENCI A *= 1. 50
GO

1.2.3.2.2. Limitar las filas que se actualizan usando la clusula WHERE

En el ejemplo siguiente, actualice el valor de la columna precioUnidad de la tabla
Compra.Productos incrementando su valor en un 25% ms, para todas las filas cuyo
nombre del producto inicie con A y su stock o unidadesenExistencia sea mayor a
100.

USE NEGOCI OS2011;
GO

UPDATE COMPRA. PRODUCTOS
SET PRECI OUNI DAD *= 1. 25
WHERE NOMPRODUCTO LI KE ' A%' AND UNI DADESENEXI STENCI A > 100;
GO

32
1.2.3.2.3. Usar la instruccin UPDATE con informacin de otra tabla

En este ejemplo, se modifica la columna ventaEmp de la tabla SalesEmpleado para
reflejar las ventas registradas en la tabla Pedidos.

USE NEGOCI OS2011;
GO

UPDATE VENTA. SALESEMPLEADO
SET VENTAEMP = VENTAEMP + ( SELECT SUM( PRECI OUNI DAD*CANTI DAD)
FROM VENTA. PEDI DOSCABE PE J OI N VENTA. PEDI DOSDETA AS PD
ON PE. I DPEDI DO= PD. I DPEDI DO)
GO

1.2.3.3. Eliminacin de datos: DELETE

La sentencia DELETE se utiliza para eliminar uno o varios registros de una misma
tabla. En una instruccin DELETE con mltiples tablas, debe incluir el nombre de tabla
(Tabla.*). Si especifica ms de una tabla para eliminar registros, todas deben tener
una relacin de muchos a uno. Si desea eliminar todos los registros de una tabla,
eliminar la propia tabla es ms eficiente que ejecutar una consulta de borrado.

Las operaciones de eliminacin en cascada en una consulta nicamente eliminan
desde varios lados de una relacin. Por ejemplo, en la base de datos
NEGOCIOS2011, la relacin entre las tablas Clientes y PedidosCabe, la tabla
PedidosCabe es la parte de muchos, por lo que las operaciones en cascada slo
afectarn a la tabla PedidosCabe. Una consulta de borrado elimina los registros
completos, no nicamente los datos en campos especficos. Si desea eliminar valores
en un campo especificado, crea una consulta de actualizacin que cambie los valores
a Null.

El formato de la sentencia es:

DELETE FROM Nombr e_Tabl a
[ WHERE { condi ci n }]
Nombre_Tabla es el nombre de la tabla donde se desea borrar los datos.
La clusula WHERE sigue el mismo formato que la vista en la sentencia
SELECT y determina qu registros se borrarn.

1.2.3.3.1. Eliminar registros

En el siguiente ejemplo, elimine los registros de la tabla PedidosCabe. Cada sentencia
DELETE borra los registros que cumplen la condicin impuesta o todos si no se indica
clusula WHERE

USE NEGOCI OS2011;
GO

DELETE FROM VENTA. PEDI DOSCABE
GO

1.2.3.3.2. Eliminar las filas usando la clusula WHERE

En el ejemplo siguiente, elimine los registros de la tabla PedidosDeta de todos aquellos
pedidos cuya antigedad sea mayor a 10 aos.

USE NEGOCI OS2011;
GO

DELETE VENTA. PEDI DOSDETA
FROM VENTA. PEDI DOSCABE PE J OI N VENTA. PEDI DOSDETA PD
ON PE. I DPEDI DO=PD. I DPEDI DO
WHERE DATEDI FF( YY, GETDATE( ) , FECHAPEDI DO) > 10;
GO

34
1.2.3.4. Seleccin de datos : SELECT

Recupera las filas de la base de datos y habilita la seleccin de una o varias filas o
columnas de una o varias tablas en SQL Server 2008.
La sintaxis completa de la instruccin SELECT es compleja, aunque las clusulas
principales se pueden resumir del modo siguiente:

Sintaxis:
<SELECT st at ement > : : =
[ WI TH <common_t abl e_expr essi on> [ , . . . n] ]
<quer y_expr essi on>
[ ORDER BY { or der _by_expr essi on | col umn_posi t i on [ ASC |
DESC ] }
[ , . . . n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MI N | SUM } ( expr essi on ) } [ , . . . n ]
[ BY expr essi on [ , . . . n ] ]
]
[ <FOR Cl ause>]
[ OPTI ON ( <quer y_hi nt > [ , . . . n ] ) ]
<quer y_expr essi on> : : =
{ <quer y_speci f i cat i on> | ( <quer y_expr essi on> ) }
[ { UNI ON [ ALL ] | EXCEPT | I NTERSECT }
<quer y_speci f i cat i on> | ( <quer y_expr essi on> ) [ . . . n ] ]
<quer y_speci f i cat i on> : : =
SELECT [ ALL | DI STI NCT ]
[ TOP ( expr essi on) [ PERCENT] [ WI TH TI ES ] ]
< sel ect _l i st >
[ I NTO new_t abl e ]
[ FROM { <t abl e_sour ce> } [ , . . . n ] ]
[ WHERE <sear ch_condi t i on> ]
[ <GROUP BY> ]
[ HAVI NG < sear ch_condi t i on > ]

Para nuestro curso usaremos la siguiente sintaxis:

SELECT [ ALL| DI STI NCT] [ TOP ( expr esi n) [ PERCENT] [ WI TH TI ES] ]
< l i st a de sel ecci n >
[ I NTO nombr e de l a nueva t abl a]
FROM <nombr e de t abl a>
WHERE <condi ci n>
GROUP BY <nombr e de campos>
HAVI NG <condi ci n> [ AND | OR <condi ci n>]
ORDER BY

1.2.3.4.1. Orden de procesamiento lgico de la instruccin SELECT

Los pasos siguientes muestran el orden de procesamiento lgico, u orden de enlace,
para una instruccin SELECT. Este orden determina el momento en que los objetos
definidos en un paso estn disponibles para las clusulas de los pasos subsiguientes.
Por ejemplo, si el procesador de consultas se puede enlazar (obtener acceso) a las
tablas o vistas definidas en la clusula FROM, estos objetos y sus columnas quedan
disponibles para todos los pasos subsiguientes. A la inversa, dado que la clusula
SELECT es el paso 8, las clusulas precedentes no pueden hacer referencia a los
alias de columna o las columnas derivadas definidos en esa clusula. Sin embargo, las
clusulas subsiguientes, como la clusula ORDER BY, s pueden hacer referencia a
ellos. Observe que la ejecucin fsica real de la instruccin est determinada por el
procesador de consultas y el orden de esta lista puede variar.

1. FROM
2. ON
3. J OIN
4. WHERE
5. GROUP BY
6. WITH CUBE o WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Ejemplo: Recupera las filas de la tabla Productos cuyo precioUnidad sea mayor a 50

USE NEGOCI OS2011;
GO

BEGI N
DECLARE @MYPRODUCTO I NT
SET @MYPRODUCTO = 750
- - EVALUAR SI LA VARI ABLE @MYPRODUCTO ES DI FERENTE DE 0
I F ( @MYPRODUCTO <> 0)
36
SELECT I DPRODUCTO ' CODI GO' ,
NOMPRODUCTO ' PRODUCTO' ,
PRECI OUNI DAD ' PRECI O'
WHERE I DPRODUCTO = @MYPRODUCTO;
END
GO

1.2.3.4.2. Crear una tabla a partir de una consulta

Utilice la siguiente sintaxis para la creacin de una tabla con datos a partir de una
consulta:

SELECT <CAMPOS> I NTO TABLA
FROM TABLA_EXI STENTE
WHERE <CONDI CI ON>

Por ejemplo: Recuperar los registros de empleados cuyo cargo sea Supervisor de
Ventas y almacenarlos en la tabla EmpleadosBAK

USE NEGOCI OS2011
GO

SELECT I DEMPLEADO,
APEEMPLEADO,
NOMEMPLEADO
I NTO DBO. EMPLEADOBAK
FROM RRHH. EMPLEADOS
WHERE I DCARGO = ( SELECT C. I DCARGO
FROM RRHH. CARGOS C
WHERE DESCARGO = ' SUPERVI SOR DE VENTAS' )
GO

SELECT * FROM DBO. EMPLEADOBAK
GO

1.2.4. INSTRUCCION MERGE

La instruccin MERGE, nos permite realizar mltiples acciones sobre una tabla
tomando uno o varios criterios de comparacin; es decir, realiza operaciones de
insercin, actualizacin o eliminacin en una tabla de destino segn los resultados de
una combinacin con una tabla de origen. Por ejemplo, puede sincronizar dos tablas
insertando, actualizando o eliminando las filas de una tabla segn las diferencias que
se encuentren en la otra.

La instruccin MERGE nos sirve bsicamente para dos cosas:

1 Sincronizar los datos de 2 tablas. Supongamos que tenemos 2 bases distintas
(Produccin y Desarrollo por ejemplo) y queremos sincronizar los datos de una
tabla para que queden exactamente iguales. Lo que antes hubiese implicado
algunas sentencias mezcladas con INNER J OIN y NOT EXISTS, ahora es
posible resumirlo en una operacin atmica mucho ms sencilla y eficiente.

2 La otra razn por la cual podramos usar MERGE, es cuando tenemos nuevos
datos que queremos almacenar en una tabla y no sabemos si la primary key de
la tabla ya existe o no, por lo tanto, no sabemos si hacer un UPDATE o un
INSERT en la tabla.

Sintaxis:
MERGE [ I NTO] <t ar get t abl e>
USI NG <sour ce t abl e>
ON <j oi n/ mer ge pr edi cat e>
WHEN [ TARGET] NOT MATCHED <st at ement t o r unt >

Donde:
<target table>: Es la tabla de destino de las operaciones de insercin, actualizacin o
eliminacin que las clusulas WHEN de la instruccin MERGE especifican.

<source table>: Especifica el origen de datos que se hace coincidir con las filas de
datos en target_table. El resultado de esta coincidencia dicta las acciones que tomarn
las clusulas WHEN de la instruccin MERGE.

38
<join/merge predicate>: Especifica las condiciones en las que table_source se
combina con target_table para determinar dnde coinciden.

<statement to run when match found in target>: Especifica que todas las filas de
target_table que coinciden con las filas que devuelve <table_source> ON
<merge_search_condition>y que satisfacen alguna condicin de bsqueda adicional
se actualizan o eliminan segn la clusula <merge_matched>.

La instruccin MERGE puede tener a lo sumo dos clusulas WHEN MATCHED. Si se
especifican dos clusulas, la primera debe ir acompaada de una clusula AND
<search_condition>. Si hay dos clusulas WHEN MATCHED, una debe especificar
una accin UPDATE y la otra una accin DELETE. Puede actualizar la misma fila ms
de una vez, ni actualizar o eliminar la misma fila.

Ejemplo: Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla
en una sola instruccin. Implemente un escenario para actualizar o insertar un
registro a la tabla pases: Si existe el cdigo del pas, actualice su nombre; sino inserte
el registro a la tabla

USE NEGOCI OS2011
GO

DECLARE @PAI S VARCHAR( 50) , @I D CHAR( 3)
SET @PAI S=' NI GERI A'
SET @I D=' 99'

MERGE VENTAS. PAI SES AS TARGET
USI NG ( SELECT @I D, @PAI S)
AS SOURCE ( I DPAI S, NOMBREPAI S)
ON ( TARGET. I DPAI S = SOURCE. I DPAI S)
WHEN MATCHED THEN
UPDATE SET NOMBREPAI S = SOURCE. NOMBREPAI S
WHEN NOT MATCHED THEN
I NSERT VALUES( SOURCE. I DPAI S, SOURCE. NOMBREPAI S) ;
GO

Ejemplo: Usar MERGE para realizar operaciones DELETE y UPDATE en una tabla
en una sola instruccin. Implemente un escenario para actualizar o eliminar un
registro a la tabla productos: Si existe el cdigo del producto y las
unidadesEnExistencia es menor o igual a cero, elimine el registro; sino actualice el
nombre del producto

USE NEGOCI OS2011
GO

DECLARE @PRODUCTO VARCHAR( 50) , @I D I NT
SET @PRODUCTO = ' VI NO'
SET @I D = 22

MERGE COMPRAS. PRODUCTOS AS TARGET
USI NG ( SELECT @I D, @PRODUCTO)
AS SOURCE ( I DPRODUCTO, NOMPRODUCTO)
ON ( TARGET. I DPRODUCTO = SOURCE. I DPRODUCTO)
WHEN MATCHED AND TARGET. UNI DADESENEXI STENCI A<=0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET NOMPRODUCTO = SOURCE. NOMPRODUCTO;
GO

Ejemplo: Usar MERGE para realizar operaciones INSERT, DELETE y UPDATE en
una tabla en una sola instruccin. Implemente un escenario para insertar, actualizar
o eliminar un registro a la tabla clientesBAK: Si existe el cdigo del cliente, si el
nombre del cliente y su direccin no coincide, actualice sus datos; sino existe el cdigo
Inserte el registro; y si no coincide en el origen elimine el Registro

USE NEGOCI OS2011
GO

MERGE VENTAS. CLI ENTESBAK AS TARGET
USI NG VENTAS. CLI ENTES AS SOURCE
ON ( TARGET. I DCLI ENTE = SOURCE. I DCLI ENTE)
WHEN MATCHED AND TARGET. NOMBRECLI ENTE <> SOURCE . NOMBRECLI ENTE THEN
UPDATE SET TARGET. NOMBRECLI ENTE = SOURCE . NOMBRECLI ENTE,
40
TARGET. DI RCLI ENTE = SOURCE . DI RCLI ENTE
I NSERT VALUE( SOURCE. NOMBRECLI ENTE, SOURCE . DI RCLI ENTE)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
GO

1.3. RECUPERACIN AVANZADA DE CONSULTA DE DATOS

Conocidos los fundamentos bsicos del comando SELECT, y est familiarizado con
varias de sus clusulas, a continuacin vamos a aprender tcnicas de consultas ms
avanzadas. Una de estas tcnicas es la de combinar el contenidos de una o ms
tablas para producir un conjunto de resultados que incorpore filas y columnas de cada
tabla. Otra tcnica es la de agrupar los datos para obtener, desde un conjunto de filas,
datos agrupados. Se pueden usar los elementos de Transact-SQL tales con CUBE y
ROLLUP para resumir datos.

1.3.1. COMBINACION DE TABLAS: JOIN

La sentencia J OIN en el lenguaje de consulta, permite combinar registros de dos o ms
tablas en una base de datos relacional. La sentencia J OIN se pueden especificar en las
clusulas FROM o WHERE, aunque se recomienda que se especifiquen en la clusula
FROM.

Las combinaciones se pueden clasificar en:

1.3.1.1. COMBINACION INTERNA.

Con esta operacin, se calcula el producto cruzado de los registros de dos tablas, pero solo
permanecen aquellos registros en la tabla combinada que satisfacen las condiciones que
se especifican. La clusula INNER JOIN permite la combinacin de los registros de las
tablas, comparando los valores de la columna especfica en ambas tablas. Cuando no
existe esta correspondencia, el registro no se muestra
Esta consulta de Transact SQL es un ejemplo de una combinacin interna:

USE NEGOCI OS2011
GO

SELECT C. I DCLI ENTE, C. NOMCLI ENTE, C. DI RCLI ENTE, P. NOMBREPAI S
FROM VENTAS. CLI ENTES C I NNER J OI N VENTAS. PAI SES P
ON C. I DPAI S = P. I DPAI S
GO

Esta combinacin interna se conoce como una combinacin equivalente. Devuelve todas
las columnas de ambas tablas y slo devuelve las filas en las que haya un valor igual en la
columna de la combinacin. Es equivalente a la siguiente consulta:

USE NEGOCI OS2011
GO

SELECT C. I DCLI ENTE, C. NOMCLI ENTE, C. DI RCLI ENTE, P. NOMBREPAI S
FROM VENTAS. CLI ENTES C, VENTAS. PAI SES P
WHERE C. I DPAI S = P. I DPAI S
GO

1.3.1.2. COMBINACIONES EXTERNAS

Mediante esta operacin no se requiere que cada registro en las tablas a tratar tenga un
registro equivalente en la otra tabla. El registro es mantenido en la tabla combinada si no
existe otro registro que le corresponda. Este tipo de operacin se subdivide dependiendo
de la tabla a la cual se le admitirn los registros que no tienen correspondencia, ya sean de
tabla izquierda, de tabla derecha o combinacin completa.
SQL Server 2008 utiliza las siguientes palabras clave para las combinaciones externas
especificadas en una clusula FROM:
LEFT OUTER JOIN o LEFT JOIN
RIGHT OUTER JOIN o RIGHT JOIN
FULL OUTER JOIN o FULL JOIN

42
A. LEFT JOIN o LEFT OUTER JOIN
La sentencia LEFT J OIN retorna la pareja de todos los valores de la izquierda con los
valores de la tabla de la derecha correspondientes, o retorna un valor nulo NULL en caso
de no correspondencia.

El operador de combinacin LEFT JOIN, indica que todas las filas de la primera tabla se
deben incluir en los resultados, con independencia si hay datos coincidentes en la segunda
tabla.

Ejemplo: Mostrar los registros de los clientes que han solicitado pedidos y aquellos clientes
que aun no han registrado pedidos
USE NEGOCI OS2011
GO

SELECT C. *, P. I DPEDI DO
FROM VENTAS. CLI ENTES C I NNER J OI N VENTAS. PEDI DOSCABE P
ON C. I DCLI ENTE = P. I DCLI ENTE
GO

B. RIGHT JOIN o RIGHT OUTER JOIN
Una combinacin externa derecha es el inverso de una combinacin externa izquierda. Se
devuelven todas las filas de la tabla de la derecha. Cada vez que una fila de la tabla de la
derecha no tenga correspondencia en la tabla de la izquierda, se devuelven valores NULL
para la tabla de la izquierda.

El operador de combinacin RIGHT JOIN, indica que todas las filas de la segunda tabla se
deben incluir en los resultados, con independencia si hay datos coincidentes en la primera
tabla.

Ejemplo: Mostrar los pedidos registrados por los productos, incluya los productos que
aun no se ha registrado en algn pedido.

USE NEGOCI OS2011
GO

SELECT PD. *, PO. NOMPRODUCTO
FROM COMPRAS. PRODUCTOS PO RI GHT J OI N VENTAS. PEDI DOSDETA PD
ON PD. I DPRODUCTO = PO. I DPRODUCTO
GO

C. FULL JOIN o FULL OUTER JOIN
Una combinacin externa completa devuelve todas las filas de las tablas de la izquierda y
la derecha. Cada vez que una fila no tenga coincidencia en la otra tabla, las columnas de la
lista de seleccin de la otra tabla contendrn valores NULL. Cuando haya una coincidencia
entre las tablas, la fila completa del conjunto de resultados contendr los valores de datos
de las tablas base.
Para retener la informacin que no coincida al incluir las filas no coincidentes en los
resultados de una combinacin, utilice una combinacin externa completa. SQL Server
2008 proporciona el operador de combinacin externa completa, FULL JOIN, que incluye
todas las filas de ambas tablas, con independencia de que la otra tabla tenga o no un valor
coincidente. En forma prctica, podemos decir que FULL JOIN es una combinacin de
LEFT JOIN y RIGHT JOIN.

Ejemplo: Mostrar los pedidos registrados por los productos, incluya los productos que
aun no se ha registrado en algn pedido.

USE NEGOCI OS2011
GO

SELECT PD. *, PO. NOMPRODUCTO
FROM VENTAS. PEDI DOSDETA PD FULL J OI N COMPRAS. PRODUCTOS PO
ON PD. I DPRODUCTO = PO. I DPRODUCTO
GO

D. COMBINACION CRUZADA

Las combinaciones cruzadas presentan el producto cartesiano de todos los registros de las
dos tablas. Se emplea el CROSS J OIN cuando se quiere combinar todos los registros de
una tabla con cada registro de otra tabla.

44
Por ejemplo, elaborar una lista de los productos donde asigne a cada producto todos los
posibles proveedores registrados en la base de datos. Aplique combinacin cruzada:

USE NEGOCI OS2011
GO

SELECT P. I DPRODUCTO, P. NOMPRODUCTO, PR. NOMPROVEEDOR
FROM COMPRAS. PRODUCTOS P CROSS J OI N COMPRAS. PROVEEDORES PR
GO

1.3.2. Datos Agrupados

Los resultados de consultas se pueden resumir, agrupar y ordenar utilizando funciones
agregadas y las clusulas GROUP BY, HAVING y ORDER BY con la instruccin
SELECT. Tambin, se puede usar la clusula compute (una extensin Transact-SQL)
con funciones agregadas para generar un informe con filas detalladas y resumidas.

1.3.2.1. Funciones Agregadas
Las funciones agregadas calculan valores sumarios a partir de datos de una columna
concreta. Las funciones agregadas se pueden aplicar a todas las filas de una tabla, a
un subconjunto de la tabla especificada por una clusula WHERE o a uno o ms
grupos de filas de la tabla. De cada conjunto de filas al que se aplica una funcin
agregada se genera un solo valor.

A continuacin detallamos la sintaxis y resultados de las funciones agregadas:
Funcin Agregada Resultado
Sum( [ al l | di st i nct ]
expr esi n)
Retorna la suma total de los valores (distintos)
de la expresin o columna
Avg ( [ al l | di st i nct ]
expr esi n)
Retorna el promedio de los valores (distintos) de
la expresin o columna
Count ( [ al l | di st i nct ]
expr esi n)
Retorna el nmero de valores (distintos) no
nulos de la expresin
Count ( *) Numero de filas seleccionadas
Max( expr esi n)
Retorna el mximo valor de la expresin o
columna
Mi n( expr esi n)
Retorna el mnimo valor de la expresin o
columna

Las funciones SUM y AVG slo pueden utilizarse con columnas numricas: int ,
smallint , tinyint, decimal, numeric, float y Money. Las funciones MIN y MAX no pueden
usarse con tipos de datos bit .
Las funciones agregadas distintas de COUNT(*) no pueden utilizarse con los tipos de
datos text e image

1.3.2.1.1. Uso de la funcin COUNT(*)

La funcin COUNT(*) no requiere ninguna expresin como argumento, porque no
emplea informacin sobre alguna columna. Esta funcin se utiliza para hallar el
nmero total de filas de una tabla.

Ejemplo: Mostrar la cantidad de pedidos registrados en el ao 2011

USE NEGOCI OS2011
GO

SELECT COUNT( *) AS ' CANTI DAD DE PEDI DOS'
FROM VENTAS. PEDI DOSCABE
WHERE DATEPART( YY, FECHAPEDI DO) =2011
GO

La palabra clave DISTINCT es opcional con SUM, AVG y COUNT, y no se permite con
MIN, MAX ni COUNT (*). Si utiliza DISTINCT, el argumento no puede incluir una
expresin aritmtica, slo debe componerse de un nombre de columna, esta palabra
clave aparece entre parntesis y antes del nombre de la columna.

Ejemplo: Mostrar la cantidad de clientes que han generado pedidos.

USE NEGOCI OS2011
GO

46
SELECT COUNT( DI STI NCT I DCLI ENTE) AS ' NUMERO DE CLI ENTES'
GO

1.3.2.1.2. Uso de la funcin AVG

La funcin AVG () calcula la media aritmtica de un conjunto de valores en un campo
especfico de la consulta
La media calcula por la funcin AVG es la media aritmtica (la suma de los valores
dividido por el nmero de valores).
La funcin AVG no incluye ningn campo NULL en el clculo.

Ejemplo: Mostrar el precio Promedio de los productos.

USE NEGOCI OS2011
GO

SELECT AVG( PRECI OUNI DAD) AS ' PRECI O PROMEDI O'
FROM COMPRAS. PRODUCTOS
GO

1.3.2.1.3. Uso de la funcin MAX() y MIN()

La funcin MAX (expr) y la funcin MIN (expr) devuelven el mximo o mnimo valor de
un conjunto de valores contenidos en un campo especfico de una consulta.
La expresin (expr) es el campo sobre el que se desea realizar el clculo; expr pueden
incluir el nombre de un campo de una tabla, una constante o una funcin (la cual
puede ser intrnseca o definida por el usuario pero no otras de las funciones
agregadas de SQL).

Ejemplo: Mostrar el mximo y el mnimo precio de los productos.

USE NEGOCI OS2011
GO

SELECT MAX( PRECI OUNI DAD) AS ' MAYOR PRECI O' , MI N( PRECI OUNI DAD)
AS ' MENOR PRECI O'
GO

1.3.2.1.4. Uso de la funcin SUM

La funcin SUM (expr) retorna la suma del conjunto de valores contenido en un campo
especfico de una consulta.
La expresin (expr) representa el nombre del campo que contiene los datos que
desean sumarse o una expresin que realiza un clculo utilizando los datos de dichos
campos.

Ejemplo: Mostrar la suma de los pedidos registrados en este ao.

USE NEGOCI OS2011
GO

SELECT SUM( PRECI OUNI DAD*CANTI DAD) AS ' SUMA'
FROM VENTAS. PEDI DOSDETA PD J OI N VENTAS. PEDI DOSCABE PC
ON PD. I DPEDI DO = PC. I DPEDI DO
WHERE YEAR( FECHAPEDI DO) =2011
GO

1.3.2.2. Clusula GROUP BY

Agrupa un conjunto de filas seleccionado en un conjunto de filas de resumen por los
valores de una o ms columnas o expresiones de SQL Server 2008.

La clusula GROUP BY se utiliza en las instrucciones SELECT para dividir la salida de
una tabla en grupos. Puede formar grupos segn uno o varios nombres de columna, o
48
segn los resultados de las columnas calculadas utilizando tipos de datos numricos
en una expresin. El nmero mximo de columnas o expresiones es 16.

La clusula GROUP BY aparece casi siempre en instrucciones que tambin incluyen
funciones agregadas, en cuyo caso el agregado genera un valor para cada grupo. A
estos valores se les llama agregados vectoriales. Un agregado escalar es un solo
valor generado por una funcin agregada sin una clusula GROUP BY.

Los valores sumarios (agregados vectoriales) generados por las instrucciones
SELECT con agregados y una clusula GROUP BY aparecen como columnas en
cada fila de los resultados.

Ejemplo: Mostrar la suma y la cantidad de pedidos registrados por cada cliente.

USE NEGOCI OS2011
GO

SELECT C. NOMCLI ENTE AS ' CLI ENTE' ,
COUNT( *) AS ' CANTI DAD' , SUM( PRECI OUNI DAD*CANTI DAD) AS
' SUMA'
J OI N VENTAS. CLI ENTES C ON C. I DCLI ENTE = PC. I DCLI ENTE
GROUP BY C. NOMCLI ENTE
GO

Si incluye la clausula WHERE en una consulta agregada, sta se aplica antes de
calcular el valor o la funcin agregada.

Ejemplo: Mostrar la suma de pedidos registrados por cada cliente en el ao 1996.

USE NEGOCI OS2011
GO

SUM( PRECI OUNI DAD*CANTI DAD) AS ' SUMA'
GO

1.3.2.3. Clusula HAVING

Es posible que necesitemos calcular un agregado, pero que no necesitemos obtener
todos los datos, solo los que cumplan una condicin 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 ms 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 clusula GROUP BY que satisfaga las condiciones de la
clusula HAVING.

Ejemplo: Mostrar los clientes cuyo importe total de pedidos (suma de pedidos
registrados por cada cliente) sea mayor a 1000.

USE NEGOCI OS2011
GO

HAVI NG SUM( PRECI OUNI DAD*CANTI DAD) >1000
GO

Se utiliza la clusula WHERE para excluir aquellas filas que no desea agrupar y la
clusula HAVING para filtrar los registros una vez agrupados.
50
Ejemplo: Mostrar los clientes cuyo importe total de pedidos (suma de pedidos
registrados por cliente) sea mayor a 1000 siendo registrados en el ao 2011.

USE NEGOCI OS2011
GO

HAVI NG SUM( PRECI OUNI DAD*CANTI DAD) >1000
GO

1.3.3. AGREGAR CONJUNTO DE RESULTADOS: UNION

La operacin UNION combina los resultados de dos o ms consultas en un solo
conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la
unin. La operacin UNION es distinta de la utilizacin de combinaciones de columnas
de dos tablas.
Para utilizar la operacin UNION, debemos aplicar algunas reglas bsicas para
combinar los conjuntos de resultados de dos consultas con UNION:
El nmero y el orden de las columnas deben ser idnticos en todas las

consultas.
Los tipos de datos deben ser compatibles.

Sintaxis:
{ <consul t a> | ( <expr esi n> ) }
UNI ON [ ALL]
<consul t a | ( <expr esi n>)
[ UNI ON [ ALL]
<consul t a > | ( <expr esi n> )
[ . . . n] ]

Argumentos:
<consulta > | ( <expresin> ): Es una especificacin o expresin de consulta que
devuelve datos que se van a combinar con los datos de otra especificacin o
expresin de consulta. No es preciso que las definiciones de las columnas que forman
parte de una operacin UNION sean iguales, pero deben ser compatibles a travs de
una conversin implcita. Cuando los tipos son los mismos, pero varan en cuanto a
precisin, escala o longitud, el resultado se determina segn las mismas reglas para
combinar expresiones.

UNION: Especifica que se deben combinar varios conjuntos de resultados para ser
devueltos como un solo conjunto de resultados.

ALL: Agrega todas las filas a los resultados. Incluye las filas duplicadas. Si no se
especifica, las filas duplicadas se quitan.

Ejemplos: En el siguiente ejemplo, mostrar los productos que tengan el mayor y
menor precio, visualice en ambos casos el nombre del producto.

USE NEGOCI OS2011
GO

SELECT NOMPRODUCTO, PRECI OUNI DAD
WHERE PRECI OUNI DAD = ( SELECT MAX( P. PRECI OUNI DAD)
FROM COMPRAS. PRODUCTOS P)
UNI ON
SELECT NOMPRODUCTO, PRECI OUNI DAD
WHERE PRECI OUNI DAD = ( SELECT MI N( P. PRECI OUNI DAD)
FROM COMPRAS. PRODUCTOS P)
GO

Ejemplos: En el siguiente ejemplo, mostrar la cantidad de pedidos registrados por
empleado en el ao 2011 y los empleados que no registraron pedidos en el 2011.

52
USE NEGOCI OS2011
GO

SELECT E. NOMEMPLEADO,
E. APEEMPLEADO,
COUNT( *) AS ' CANTI DAD'
FROM RRHH. EMPLEADOS E J OI N VENTAS. PEDI DOSCABE P
ON E. I DEMPLEADO = P. I DEMPLEADO
GROUP BY E. NOMEMPLEADO, E. APEEMPLEADO
UNI ON
SELECT E. NOMEMPLEADO, E. APEEMPLEADO, 0 AS ' CANTI DAD'
FROM RRHH. EMPLEADOS E
WHERE E. I DEMPLEADO NOT I N( SELECT P. I DEMPLEADO FROM
VENTAS. PEDI DOSCABE P
WHERE YEAR( FECHAPEDI DO) =2011)
GO

1.3.4. RESUMEN DE DATOS: CUBE

El operador CUBE genera un conjunto de resultados que es un cubo multidimensional.
Este operador genera filas de agregado mediante la clusula GROUP BY simple, filas
de super agregado mediante la instruccin ROLLUP y filas de tabulacin cruzada.

CUBE genera una agrupacin para todas las permutaciones de expresiones de la
<lista de elementos compuestos>. El nmero de agrupaciones generado es igual a
(2
n
), donde n es el nmero de expresiones de la <lista de elementos compuestos>.

Ejemplo, la siguiente instruccin:

USE NEGOCI OS2011
GO

SELECT C. NOMBRECLI ENTE AS ' CLI ENTE' ,
YEAR( FECHAPEDI DO) AS AO,
GROUP BY CUBE( C. NOMBRECLI ENTE, YEAR( FECHAPEDI DO) )
GO

Se genera una fila para cada combinacin nica de valores de (cliente, ao), (cliente) y
(ao), con un subtotal para cada fila y una fila de total general.

1.3.4.1. Agregar permutaciones mltiples: operador CUBE

El operador CUBE es una opcin adicional de la clusula GROUP BY en una
sentencia SELECT. El operador CUBE se puede aplicar a todas las funciones
agregadas, incluidas AVG, SUM, MAX, MIN y COUNT. Se utiliza para producir juegos
de resultados que, normalmente, se utilizan para informes de tabulacin cruzada.
Mientras ROLLUP produce slo una fraccin de las posibles combinaciones
subtotales, CUBE produce subtotales para todas las posibles combinaciones de
agrupamientos especificados en la clusula GROUP BY y una suma total.

SELECT [ col umn, ] gr oup_f unct i on( col umn) . . .
FROM t abl e
[ WHERE condi t i on]
[ GROUP BY gr oup_by_expr essi on]
[ WI TH CUBE]
[ HAVI NG havi ng_expr essi on]
[ ORDER BY col umn] ;

El operador CUBE se utiliza con una funcin agregada para generar filas adicionales
en un juego de resultados. Las columnas incluidas en la clusula GROUP BY tienen
referencias cruzadas para producir un super juego de grupos.

Ejemplo: Listar la cantidad de pedidos registrados por cada empleado y ao,
totalizando la cantidad de pedidos por cada ao.

54
USE NEGOCI OS2011
GO

SELECT E. NOMEMPLEADO+' , ' +E. APEEMPLEADO AS ' EMPLEADO' ,
YEAR( P. FECHAPEDI DO) AS ' AO' ,
FROM VENTAS. PEDI DOSCABE P J OI N VENTAS. EMPLEADOS E
ON P. I DEMPLEADO = E. I DEMPLEADO
GROUP BY CUBE( E. NOMEMPLEADO +' , ' +E. APEEMPLEADO,
YEAR( P. FECHAPEDI DO) )
GO

Al ejecutar el proceso se puede
observar que se lista por cada ao y
por cada empleado la cantidad de
pedidos, visualizando en la lnea 10. La
suma de la cantidad de pedidos por
dicho ao, luego se inicializa para el
siguiente ao.

1.3.4.2. RESUMEN DE DATOS: ROLLUP

El operador ROLLUP es til para generar reportes que contienen subtotales y totales.
Genera un conjunto de resultados que es similar al conjunto de resultados del CUBE.
Las diferencias entre los operadores CUBE y ROLLUP son las siguientes:
CUBE genera un conjunto de resultados mostrando agregaciones para todas
las combinaciones de valores en las columnas seleccionadas.
ROLLUP genera un conjunto de resultados mostrando agregaciones para
jerarquas en las columnas seleccionadas.

Ejemplo: Mostrar la cantidad de pedidos registrados por cada ao. Al finalizar visualice
la cantidad total de pedidos.

USE NEGOCI OS2011
GO

SELECT YEAR( FECHAPEDI DO) AS ' AO' ,
GROUP BY YEAR( FECHAPEDI DO)
WI TH ROLLUP
GO

Al usar el operador ROLLUP, se pueden crear agrupamientos en el conjunto de
resultados. Para las filas agrupadas, se usa un valor NULL para representar todos los
valores para la columna (excepto la columna Sum).
Si se usa un comando SELECT sin el operador ROLLUP, el comando generar los
siguientes datos cuando se listen las columnas Ed_nombre, Au_nombre, y Titulo (en
ese orden) en la clusula GROUP_BY.

Ejemplo: Mostrar la cantidad de pedidos registrados por cada empleado y por ao, al
finalizar visualice la cantidad total de pedidos por cada empleado y la cantidad total de
todos los pedidos.

USE NEGOCI OS2011
GO

SELECT E. APELLI DOS AS ' EMPLEADO' ,
YEAR( P. FECHAPEDI DO) AS ' AO' ,
FROM TB_PEDI DOSCABE P J OI N TB_EMPLEADOS E
ON P. I DEMPLEADO = E. I DEMPLEADO
GROUP BY E. APELLI DOS, YEAR( P. FECHAPEDI DO)
WI TH ROLLUP
GO

56

En este caso, visualizamos la cantidad
de pedidos por empleado en cada ao,
totalizando la cantidad de pedidos por
empleados (lneas 43, 50 y 54) y el
total de todos los pedidos (lnea 55).


Resumen
Un operador es un smbolo que especifica una accin que se realiza en una o ms
expresiones. Los operadores se clasifican en aritmticos, de asignacin, de
comparacin, lgicos
Las funciones son mtodos que permiten retornar un valor. Las funciones en SQL
SERVER se clasifican en: funciones de fechas, de cadena, de conversin.
La sentencia INSERT agrega una o varias filas a una tabla o vista en SQL
SERVER. Utilizando el comando SELECT, podemos agregar mltiples registros.
La sentencia UPDATE se utiliza para cambiar el contenido de los registros de una
o varias columnas de una tabla de la base de datos.
La sentencia DELETE se utiliza para eliminar uno o varios registros de una misma
tabla. En una instruccin DELETE , con mltiples tablas, debe incluir el nombre de
tabla (Tabla.*). Si especifica ms de una tabla para eliminar registros, todas deben
tener una relacin de muchos a uno.
La sentencia SELECT recupera las filas de la base de datos y habilita la seleccin
de una o varias filas o columnas de una o varias tablas en SQL Server 2008.
La instruccin MERGE nos permite realizar mltiples acciones sobre una tabla
tomando uno o varios criterios de comparacin, es decir, realiza operaciones de
insercin, actualizacin o eliminacin en una tabla de destino segn los resultados
de una combinacin con una tabla de origen.
La sentencia J OIN , en el lenguaje de consulta, permite combinar registros de dos o
ms tablas en una base de datos relacional. La sentencia J OIN se pueden especificar
en las clusulas FROM o WHERE, aunque se recomienda que se especifiquen en la
clusula FROM. La clusula INNER JOIN permite la combinacin de los registros de
las tablas, comparando los valores de la columna especfica en ambas tablas.
La sentencia LEFT J OIN retorna la pareja de todos los valores de la izquierda con los
valores de la tabla de la derecha correspondientes, o retorna un valor nulo NULL en
caso de no correspondencia. El operador RIGHT JOIN indica que todas las filas de la
segunda tabla se deben incluir en los resultados, con independencia si hay datos
coincidentes en la primera tabla.
Los resultados de consultas se pueden resumir, agrupar y ordenar utilizando funciones
agregadas y las clusulas GROUP BY, HAVING y ORDER BY con la instruccin
SELECT. Tambin, se puede usar la clusula compute (una extensin Transact-SQL)
con funciones agregadas para generar un informe con filas detalladas y resumidas.
58
La operacin UNION combina los resultados de dos o ms consultas en un solo
conjunto de resultados que incluye todas las filas que pertenecen a las consultas
de la unin. La operacin UNION es distinta de la utilizacin de combinaciones de
columnas de dos tablas
El operador CUBE genera un conjunto de resultados que es un cubo
multidimensional. Este operador genera filas de agregado mediante la clusula
GROUP BY simple, filas de supe agregado mediante la instruccin ROLLUP y filas
de tabulacin cruzada.
El operador ROLLUP es til para generar reportes que contienen subtotales y
totales, genera un conjunto de resultados que es similar al conjunto de resultados
del CUBE. Las diferencias entre los operadores CUBE y ROLLUP son las
siguientes:
CUBE genera un conjunto de resultados mostrando agregaciones para todas
las combinaciones de valores en las columnas seleccionadas.
ROLLUP genera un conjunto de resultados mostrando agregaciones para
jerarquas en las columnas seleccionadas.

Si desea saber ms acerca de estos temas, puede consultar las siguientes
pginas.

http://www.sqlmax.com/centro/moduloIII_3.asp?MX=
Aqu hallar los conceptos de tcnicas avanzadas de consulta de datos.
http://msdn.microsoft.com/es-es/library/ms180026.aspx
En esta pgina, hallar los conceptos de la sentencia UNION.
Error! Referencia de hipervnculo no vlida.www.devjoker.com
Aqu hallar los conceptos de programacin TRANSACT SQL.
http://manuals.sybase.com/onlinebooks/group-
asarc/svs11001/tsqlsp/@Generic__BookTocView/2367;hf=0;pt=2367;lang=es
En esta pgina, hallar los conceptos de lenguaje de consulta.


PROGRAMACIN TRANSACT-SQL
Al trmino de la unidad, el alumno escribe rutinas complejas utilizando sentencias
TRANSACT-SQL para recupera, inserta, actualiza y elimina informacin de una
base de datos.

TEMARIO
1. Fundamentos de Programacin TRANSACT-SQL
2. Construcciones de Programacin TRANSACT-SQL
2.1. Variables
2.1.1. Variables Locales
2.1.2. Variables Pblicas
2.2. Herramientas para el control de flujos
2.2.1. Estructuras de control IF
2.2.2. Estructura condicional CASE
2.2.2.1. Usar una instruccin SELECT con una expresin CASE de bsqueda
2.2.3. Estructura de control WHILE
2.3. Control de errores en TRANSACT-SQL
2.3.1. Funciones especiales de ERROR
2.3.2. Variable de sistema @@ERROR
2.3.3. Generar un error RAISERROR
2.4. Cursores en TRANSACT-SQL
2.4.1. DECLARE Cursor
2.4.2. Abrir un Cursor: OPEN
2.4.3. Leer un Registro: FETCH
2.4.4. Cerrar el Cursor: CLOSE
2.4.5. Liberar los recursos: DEALLOCATE

Los alumnos implementan sentencias SQL utilizando estructuras de programacin
para recuperar y actualizar datos
Los alumnos implementan sentencias SQL definiendo sentencias de control de
errores.
Los alumnos implementan listados y consultas de datos con totales y subtotales
utilizando cursores.

UNIDAD DE
APRENDIZAJ E
2
60
2.1 FUNDAMENTOS DE PROGRAMACION TRANSACT-SQL

SQL es un lenguaje de consulta para los sistemas de bases de datos relacinales,
pero que no posee la potencia de los lenguajes de programacin. No permite el uso de
variables, estructuras de control de flujo, bucles y dems elementos caractersticos de
la programacin. No es de extraar, SQL es un lenguaje de consulta, no un
lenguaje de programacin.

Sin embargo, SQL es la herramienta ideal para trabajar con bases de datos. Cuando
se desea realizar una aplicacin 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 programacin tradicionales.
TRANSACT SQL es el lenguaje de programacin que proporciona Microsoft SQL
Server para extender el SQL estndar con otro tipo de instrucciones y elementos
propios de los lenguajes de programacin.

2.1.1 CONSTRUCCION DE PROGRAMACION TRANSACT-SQL

TRANSACT-SQL ampla el SQL estndar con la implementacin de estructuras de
programacin. Estas implementaciones le resultarn familiares a los desarrolladores
con experiencia en C++, J ava, Visual Basic .NET, C# y lenguajes similares. A
continuacin, vamos a definir las estructuras de programacin implementadas en SQL
Server.

2.1.2 VARIABLES

Una variable es una entidad a la que se asigna un valor. Este valor puede cambiar
durante el proceso donde se utiliza la variable. SQL Server tiene dos tipos de
variables: locales y globales. Las variables locales estn definidas por el usuario,
mientras que las variables globales las suministra el sistema y estn predefinidas.

2.1.2.1 Variables Locales
Las variables locales se declaran, nombran y escriben mediante la palabra clave
declare, y reciben un valor inicial mediante una instruccin select o set.

62
Los nombres de las variables locales deben empezar con el smbolo @ . A cada
variable local se le debe asignar un tipo de dato definido por el usuario o un tipo de
dato suministrado por el sistema distinto de text, image o sysname. Sintaxis:

- - DECLARA UNA VARI ABLE
DECLARE @VARI ABLE <TI PO DE DATO>

- - ASI GNA VALOR A UNA VARI ABLE
SET @VARI ABLE= VALOR

En el ejemplo siguiente, declaramos una variable y le asignamos un valor, la cual ser
utilizada en una clausula WHERE.

DECLARE @PRECI O DECI MAL
SET @PRECI O = 50
SELECT * FROM COMPRA. PRODUCTOS
WHERE P. PRECI OUNI DAD > @PRECI O
GO

Podemos utilizar la instruccin SELECT en lugar de la instruccin SET. Una
instruccin SELECT utilizada para asignar valores a una o ms variables se denomina
SELECT de asignacin. Si utilizamos el SELECT de asignacin, no puede devolver
valores al cliente como un conjunto de resultados.

En el ejemplo siguiente, declaramos dos variables y le asignamos el mximo y mnimo
precio desde la tabla Compras.productos.

USE NEGOCI OS2011
GO

DECLARE @MX DECI MAL, @MN DECI MAL
SELECT @MX=MAX( PRECI OUNI DAD) ,
@MN=MI N( PRECI OUNI DAD)

- - I MPRI MI R LOS VALORES DE LAS VARI ABLES
PRI NT ' MAYOR PRECI O: ' +STR( @MX)
PRI NT ' MENOR PRECI O: ' +STR( @MN)
GO

2.1.2.2 Variables Pblicas

Las variables globales son variables predefinidas suministradas por el sistema. Se
distinguen de las variables locales por tener dos smbolos @ . Estas son algunas
variables globales del servidor:

Variable Contenido
@@ERROR Contiene 0 si la ltima transaccin se ejecut de forma
correcta; en caso contrario, contiene el ltimo nmero de
error generado por el sistema. La variable global @@error
se utiliza generalmente para verificar el estado de error de
un proceso ejecutado.
@@I DENTI TY Contiene el ltimo valor insertado en una columna
IDENTITY mediante una instruccin insert
@@VERSI ON Devuelve la Versin del SQL Server
@@SERVERNAME Devuelve el Nombre del Servidor
@@LANGUAGE Devuelve el nombre del idioma en uso
@@MAX_CONNECTI ONS Retorna la cantidad mxima de conexiones permitidas

En este ejemplo, mostramos la informacin de algunas variables pblicas:

- - LA VERSI ON DEL SQL SERVER
PRI NT ' VERSI ON: ' + @@VERSI ON

- - LENGUAJ E DEL APLI CATI VO
PRI NT ' LENGUAJ E: ' + @@LANGUAGE

- - NOMBRE DEL SERVI DOR
PRI NT ' SERVI DOR: ' + @@SERVERNAME
64
- - NUMERO DE CONEXI ONES PERMI TI DAS
PRI NT ' CONEXI ONES: ' + STR( @@MAX_CONNECTI ONS)

2.2 HERRAMIENTAS PARA EL CONTROL DE FLUJOS

El lenguaje de control de flujo se puede utilizar con instrucciones interactivas, en lotes
y en procedimientos almacenados. El control de flujo y las palabras clave relacionadas
y sus funciones son las siguientes:

Palabra Clave Funcin
IF ELSE Define una ejecucin condicional, cuando la condicin la
condicin es verdadera y la alternativa (else) cuando la
condicin es falsa
CASE Es la forma ms sencilla de realizar operaciones de tipo IF-
ELSE IF-ELSE IF-ELSE. La estructura CASE permite evaluar
una expresin y devolver un valor alternativo
WHILE Estructura repetitiva que ejecuta un bloque de instrucciones
mientras la condicin es verdadera
BEGIN END Define un bloque de instrucciones. El uso del BEGINEND
permite ejecutar un bloque o conjunto de instrucciones.
DECLARE Declara variables locales
BREAK Sale del final del siguiente bucle while ms interno
CONTINUE Reinicia del bucle while
RETURN [n] Sale de forma incondicional, suele utilizarse en
procedimientos almacenados o desencadenantes.
Opcionalmente, se puede definir un numero entero como
estado devuelto, que puede asignarse al ejecutar el
procedimiento almacenado
PRINT Imprime un mensaje definido por el usuario o una variable
local en la pantalla del usuario
/*COMENTARIO*/ Inserta un comentario en cualquier punto de una instruccin
SQL
--COMENTARIO Inserta una lnea de comentario en cualquier punto de una
instruccin SQL

2.2.1 Estructuras de control IF

La palabra clave IF se utiliza para definir una condicin que determina si se ejecutar
la instruccin siguiente. La instruccin SQL se ejecuta si la condicin se cumple, es
decir, si devuelve TRUE (verdadero). La palabra clave ELSE introduce una instruccin
SQL alternativa que se ejecuta cuando la condicin IF devuelva FALSE. La sintaxis de
la estructura condicional IF:

IF ( <expr essi on>)
BEGIN
...
END
ELSE IF ( <expr essi on>)
BEGIN
...
END
ELSE
BEGIN
...
END

Ejemplo: Visualice un mensaje donde indique si un empleado (ingrese su codigo) ha
realizado pedidos.

DECLARE @I DEMP I NT, @CANTI DAD I NT
SET @I DEMP = 6
- - RECUPERAR LA CANTI DAD DE PEDI DOS DEL EMPLEADO DE CODI GO 6
SELECT @CANTI DAD = COUNT( *)
FROM VENTAS. PEDI DOSCABE WHERE I DEMPLEADO = @I DEMP
- - EVALUA EL VALOR DE CANTI DAD
I F @CANTI DAD = 0
PRI NT ' EL EMPLEADO NO HA REALI ZADO ALGUN PEDI DO'
ELSE I F @CANTI DAD = 1
PRI NT ' HA REGI STRADO 1 PEDI DO, CONTI NUE TRABAJ ANDO'
ELSE
PRI NT ' HA REGI STRADO PEDI DOS'
GO

66
Ejemplo: utilizamos la estructura IF para evaluar la existencia de un registro; si existe
actualizamos los datos de la tabla; si no existe (ELSE) insertamos el registro.

DECLARE @COPAI S VARCHAR( 3) , @NOMBRE VARCHAR( 50)
SET @COPAI S = ' 99'
SET @NOMBRE = ' ESPAA'
- - EVALUA SI EXI STE EL REGI STRO DE LA TABLA, SI EXI STE
ACTUALI ZO, SI NO I NSERTO
I F EXI STS( SELECT * FROM TB_PAI SES WHERE I DPAI S = @COPAI S)
BEGI N
UPDATE TB_PAI SES
SET NOMBREPAI S = @NOMBRE
WHERE I DPAI S = @COPAI S
END
ELSE
BEGI N
I NSERT I NTO TB_PAI SES VALUES ( @COPAI S, @NOMBRE)
END
GO

2.2.2 Estructura condicional CASE
La estructura CASE evala una lista de condiciones y devuelve una de las varias
expresiones de resultado posibles. La expresin CASE tiene dos formatos:
La expresin CASE sencilla compara una expresin con un conjunto de
expresiones sencillas para determinar el resultado.
La expresin CASE buscada evala un conjunto de expresiones booleanas
para determinar el resultado.
Ambos formatos admiten un argumento ELSE opcional. La sintaxis del CASE:
CASE <expr esi n>
WHEN <val or _expr esi on> THEN <val or _devuel t o>
WHEN <val or _expr esi on1> THEN <val or _devuel t o1>
ELSE <val or _devuel t o2> - - Val or por def ect o
END

Ejemplo: Declare una variable donde le asigne el numero del mes, evale el valor de
la variable y retorne el mes en letras.

La estructura CASE se puede utilizar en cualquier instruccin o clusula que permite
una expresin vlida. Por ejemplo, puede utilizar CASE en instrucciones como
SELECT, UPDATE, DELETE y SET, y en clusulas como select_list, IN, WHERE,
ORDER BY y HAVING. La funcin CASE es una expresin especial de Transact SQL
que permite que se muestre un valor alternativo dependiendo de una columna. Este
cambio es temporal, con lo que no hay cambios permanentes en los datos.

Ejemplo: Mostrar los datos de los empleados evaluando el valor del campo
tratamiento asignando, para cada valor, una expresin.

DECLARE @M I NT, @MES VARCHAR( 20)
SET @M=4
SET @MES = ( CASE @M
WHEN 1 THEN ' ENERO'
WHEN 2 THEN ' FEBRERO'
WHEN 3 THEN ' MARZO'
WHEN 4 THEN ' ABRI L'
WHEN 5 THEN ' MAYO'
WHEN 6 THEN ' J UNI O'
WHEN 7 THEN ' J ULI O'
WHEN 8 THEN ' AGOSTO'
WHEN 9 THEN ' SETI EMBRE'
WHEN 10 THEN ' OCTUBRE'
WHEN 11 THEN ' NOVI EMBRE'
WHEN 12 THEN ' DI CI EMBRE'
ELSE ' NO ES MES VALI DO'
END)
PRI NT @MES
68
USE NEGOCI OS2011;
GO

SELECT ( CASE TRATAMI ENTO
WHEN ' SRTA. ' THEN ' SEORI TA'
WHEN ' SR. ' THEN ' SEOR'
WHEN ' DR. ' THEN ' DOCTOR'
WHEN ' SRA. ' THEN ' SEORA'
ELSE ' NO TRATAMI ENTO'
END) , APELLI DOS, NOMBRE
ORDER BY 1;
GO

2.2.2.1 Usar una instruccin SELECT con una expresin CASE de bsqueda
En una instruccin SELECT, la expresin CASE de bsqueda permite sustituir valores
en el conjunto de resultados basndose en los valores de comparacin.
En el ejemplo siguiente, listamos los datos de los productos y definimos una columna
llamada ESTADO, el cual evaluar stock de cada producto imprimiendo un valor:
Stockeado, Limite, Haga una solicitud.

DECLARE @STOCK I NT
SET @STOCK=100

SELECT NOMBREPRODUCTO, PRECI OUNI DAD,
UNI DADESENEXI STENCI A,
' ESTADO' = ( CASE
WHEN UNI DADESENEXI STENCI A>@STOCK THEN ' STOCKEADO'
WHEN UNI DADESENEXI STENCI A=@STOCK THEN ' LI MI TE'
WHEN UNI DADESENEXI STENCI A<@STOCK THEN ' HAGA UNA
SOLI CI TUD'
END)
FROM TB_PRODUCTOS
GO

Al ejecutar la operacin listamos los productos y su estado, tal como se muestra.

2.2.3 Estructura de control WHILE
La estructura WHILE ejecuta en forma repetitiva un conjunto o bloque de instrucciones
SQL siempre que la condicin especificada sea verdadera. Se puede controlar la
ejecucin de instrucciones en el bucle WHILE con las palabras clave BREAK y
CONTINUE.
La sintaxis de WHILE es:
WHILE <expr esi on>
BEGIN
. . .
END

Por ejemplo: Implemente un programa que permita listar los 100 primeros nmeros
enteros, visualizando en cada caso si es par o impar.

DECLARE @cont ador i nt
SET @cont ador = 0
WHI LE ( @cont ador < 100)
BEGI N
SET @cont ador = @cont ador + 1
I F @cont ador %2 =0
PRI NT cast ( @cont ador AS var char ) +' es un Nmer o Par '
ELSE
PRI NT cast ( @cont ador AS var char ) + ' es un Nmer o I mpar '
END

70
Por ejemplo: Listar los 5 primeros registros de la tabla productos.

DECLARE @COUNTER I NT = 1;
WHI LE @COUNTER < 6
BEGI N
SELECT TOP( 1) P. I DPRODUCTO, P. NOMBREPRODUCTO, P. PRECI OUNI DAD
FROM COMPRAS. PRODUCTOS P
WHERE I DPRODUCTO = @COUNTER
SET @COUNTER += 1;
END;

BREAK y CONTINUE controlan el funcionamiento de las instrucciones dentro de un
bucle WHILE. BREAK permite salir del bucle WHILE. CONTINUE hace que el bucle
WHILE se inicie de nuevo. La sintaxis de BREAK y CONTINUE es:

WHILE BOOLEAN_EXPRESI ON
BEGI N
EXPRESI ON_SQL

[ BREAK]
[ EXPRESI ON_SQL]

[ CONTINUE]
[ EXPRESI ON_SQL]
END

Por ejemplo: Actualizar las unidades de existencia de los productos asignndoles el
valor de 1000 de aquellos productos cuyo stock sea cero.

USE NEGOCI OS2011
GO

DECLARE @I D I NT, @NOMBRE VARCHAR( 50)
WHI LE EXI STS ( SELECT *
FROM TB_PRODUCTOS
WHERE UNI DADESENEXI STENCI A=0)
BEGI N
SELECT TOP 1 @I D= P. I DPRODUCTO, @NOMBRE=P. NOMBREPRODUCTO
FROM TB_PRODUCTOS P WHERE UNI DADESENEXI STENCI A=0

UPDATE TB_PRODUCTOS
SET UNI DADESENEXI STENCI A=1000
WHERE I DPRODUCTO=@I D

PRI NT ' PRODUCTO: ' +@NOMBRE + ' SE ACTUALI ZO EL STOCK'
CONTI NUE
END;

2.3 CONTROL DE ERRORES EN TRANSACT-SQL

SQL Server proporciona el control de errores a travs de las instrucciones TRY y
CATCH. Estas nuevas instrucciones suponen un gran paso adelante en el control de
errores en SQL Server. La sintaxis de TRY CATCH es la siguiente:

BEGI N TRY
EXPRESI ON_SQL
END TRY
BEGI N CATCH
EXPRESI ON_SQL
END CATCH

Ejemplo: Implemente un programa que evala la divisin de dos nmeros enteros.Si la
divisin ha sido exitosa, imprima un mensaje: NO HAY ERROR. Caso contrario
imprimir un mensaje: SE HA PRODUCIDO UN ERROR

BEGI N TRY
DECLARE @DI VI SOR I NT, @DI VI DENDO I NT, @RESULTADO I NT
SET @DI VI DENDO = 100
SET @DI VI SOR = 9

72
- - ESTA LI NEA PROVOCA UN ERROR DE DI VI SI ON POR 0
SET @RESULTADO = @DI VI DENDO/ @DI VI SOR
PRI NT ' NO HAY ERROR'
END TRY
BEGI N CATCH
PRI NT ' SE HA PRODUCI DO UN ERROR'
END CATCH;

2.3.1 Funciones especiales de Error

Las funciones especiales de error, estn disponibles nicamente en el
bloque CATCH para la obtencin de informacin detallada del error. A continuacin,
presentamos las funciones que se utilizan en el control de errores

Error Descripcin
ERROR_NUMBER( ) Devuelve el numero de error
ERROR_SEVERI TY( ) 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_LI NE( ) Devuelve el nmero de lnea en la que se ha
producido el error.
ERROR_MESSAGE( ) Devuelve el mensaje de error

Ejemplo: Implemente un programa que evala la divisin de dos nmeros enteros. Si
la divisin ha sido exitosa, imprima un mensaje: NO HAY ERROR. Caso contrario
imprimir el mensaje de error y el estado del error

BEGI N TRY
DECLARE @DI VI SOR I NT, @DI VI DENDO I NT, @RESULTADO I NT
SET @DI VI DENDO = 100
SET @DI VI SOR = 9
- - ESTA LI NEA PROVOCA UN ERROR DE DI VI SI ON POR 0
SET @RESULTADO = @DI VI DENDO/ @DI VI SOR
PRI NT ' NO HAY ERROR'
END TRY
BEGI N CATCH
PRI NT ERROR_MESSAGE( )
PRI NT ERROR_STATE( )
END CATCH;

2.3.2 Variable de sistema @@ERROR

Devuelve el nmero de error de la ltima instruccin TRANSACT-SQL ejecutada; si la
variable devuelve 0, la TRANSACT-SQL anterior no encontr errores.

Ejemplo: Implemente un bloque de instrucciones que permita eliminar un registro de
Cliente por su cdigo. Si hay un error en el proceso, visualice un mensaje de error

DELETE FROM VENTAS. CLI ENTES WHERE I DCLI ENTE = ' ALFKI '
I F @@ERROR<>0
PRI NT ' NO SE PUEDE ELI MI NAR'

Otra forma de implementar este proceso, es controlndolo a travs del bloque TRY
CATCH.

BEGI N TRY
DELETE FROM TB_CLI ENTES
WHERE I DCLI ENTE = ' ALFKI '
END TRY
BEGI N CATCH
I F @@ERROR=547
PRI NT ' NO SE PUEDE ELI MI NAR ESTE CLI ENTE'
END CATCH

La variable @@ERROR devuelve un nmero de error que representa el error de la
operacin. Si el error se encuentra en la vista de catlogo sys.sysmessages,
entonces @@ERROR, contendr el valor de la columna sys.sysmessages.error para
dicho error. Puede ver el texto asociado con el nmero de error @@ERROR en
sys.sysmessages.description

74
Ejemplo: Implemente un proceso que elimine los productos cuyo valor de sus unidades
en existencia, sea menor a 20, en caso de no ejecutar exitosamente el error,
implemente el CATCH para controlar el error.

BEGI N TRY
DELETE FROM TB_PRODUCTOS
WHERE UNI DADESENEXI STENCI A<20
END TRY
BEGI N CATCH
DECLARE @MENSAJ E VARCHAR( 255)
- - RECUPERAR LA DESCRI PCI ON DEL VALOR DE @@ERROR
SELECT @MENSAJ E= M. DESCRI PTI ON
FROM SYS. SYSMESSAGES M WHERE M. ERROR=@@ERROR
PRI NT @MENSAJ E
END CATCH

2.3.3 Generar un error RAISERROR

En ocasiones, es necesario provocar voluntariamente un error, por ejemplo nos puede
interesar que se genere un error cuando los datos incumplen una regla de negocio.
Podemos provocar un error en tiempo de ejecucin a travs de la funcin
RAISERROR.

La funcin RAISERROR recibe tres parmetros, el mensaje del error (o cdigo 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 conexin que ejecuta el comando RAISERROR.
Para asignar valores del 19 al 25, necesitaremos ser miembros de la funcin de SQL
Server sysadmin.

En el presente ejercicio, evaluamos los valores de dos variables provocando un error
utilizando RAISERROR.

DECLARE @TI PO I NT, @CLASI FI CACI ON I NT
SET @TI PO = 1
SET @CLASI FI CACI ON = 3
I F ( @TI PO = 1 AND @CLASI FI CACI ON = 3)
BEGI N
RAI SERROR ( ' EL TI PO NO PUEDE VALER UNO Y LA CLASI FI CACI ON 3' ,
16, - - SEVERI DAD
1 - - ESTADO
)
END

2.4 CURSORES EN TRANSACT-SQL

Un cursor es una variable que nos permite recorres con un conjunto de resultados
obtenidos a travs de una sentencia SELECT fila por fila.

El uso de los cursores es una tcnica que permite tratar fila por fila el resultado de una
consulta, contrariamente al SELECT SQL que trata a un conjunto de fila. Los cursores
pueden ser implementador por instrucciones TRANSACT-SQL (cursores ANSI-SQL) o
por la API OLE-DB.

Se utilizarn los cursores ANSI cuando sea necesario tratar las filas de manera
individual en un conjunto o cuando SQL no pueda actuar nicamente sobre las filas
afectadas. Los cursores API sern utilizados por las aplicaciones cliente para tratar
volmenes importantes o para gestionar varios conjuntos de resultados.

Cuando trabajemos con cursores, debemos seguir los siguientes pasos:
Declarar el cursor, utilizando DECLARE
Abrir el cursor, utilizando OPEN
Leer los datos del cursor, utilizando FETCH ... INTO
Cerrar el cursor, utilizando CLOSE
Liberar el cursor, utilizando DEALLOCATE

La sintaxis general para trabajar con un cursor es la siguiente:

76
- - DECLARACI N DEL CURSOR
DECLARE <NOMBRE_CURSOR> CURSOR
FOR <SENTENCI A_SQL>

- - APERTURA DEL CURSOR
OPEN <NOMBRE_CURSOR>

- - LECTURA DE LA PRI MERA FI LA DEL CURSOR
FETCH <NOMBRE_CURSOR> I NTO <LI STA_VARI ABLES>

WHI LE ( @@FETCH_STATUS = 0)
BEGI N
- - LECTURA DE LA SI GUI ENTE FI LA DE UN CURSOR
FETCH <NOMBRE_CURSOR> I NTO <LI STA_VARI ABLES>
. . .
END - - FI N DEL BUCLE WHI LE

- - CI ERRA EL CURSOR
CLOSE <NOMBRE_CURSOR>

- - LI BERA LOS RECURSOS DEL CURSOR
DEALLOCATE <NOMBRE_CURSOR>

2.4.1 Declare CURSOR

Esta instruccin permite la declaracin y la descripcin del cursor ANSI.
Sintaxis:

DECLARE <NOMBRE_CURSOR> [ I NSENSI TI VE] [ SCROLL] CURSOR
FOR <SENTENCI A_SQL>
FOR [ READ ONLY | UPDATE[ OF LI STA DE COLUMNAS] ]

INSENSITIVE solo se permiten las operaciones sobre la fila siguiente
SCROLL los desplazamientos en las filas del cursor podrn hacerse en todos
los sentidos.
UPDATE especifica que las actualizaciones se harn sobre la tabla de origen
del cursor. Un cursor INSENSITIVE con una clusula ORDER BY no puede
actualizarse. Un cursor con ORDER BY, UNION, DISTINCT o HAVING es
INSENSITIVE y READ ONLY.

Adems de esta sintaxis conforme con la norma ISO, TRANSACT-SQL propone una
sintaxis ampliada en la definicin de los cursores:

DECLARE <nombr e_cur sor > [ LOCAL | GLOBAL ]
FORWARD_ONLY | SCROLL ]
[ STATI C | KEYSET | DYNAMI C | FAST_FOWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTI MI STI C ]
[ TYPE_WARNI NG]
FOR <sent enci a_sql >
{FOR UPDATE [ OF l i st a de col umnas ] }

LOCAL el alcance del cursor es local en el lote, el procedimiento o la funcin
en curso de ejecucin en el que est definido el curso.
GLOBAL el alcance del cursor el global a la conexin. La opcin de base de
datos default to local cursor est definido en falso de manera
predeterminada.
FOWARD_ONLY los datos se extraen del cursor por orden de aparicin (del
primero al ltimo).
STATIC se realiza una copia temporal de los datos en la base de datos
tempdb para que el cursor no se vea afectado por las modificaciones que
puedan realizarse sobre la base de datos.
KEYSET las filas y su orden en el cursor se fijan en el momento de la apertura
del cursor. Las referencias hacia cada una de estas filas de informacin se
conservan en una tabla temporal en tempdb.
DYNAMIC el cursor refleja exactamente los datos presentes en la base de
datos. Esto significa que el nmero de filas, su orden y su valor pueden variar
de forma dinmica.
78
FAST_FORWARD permite definir un cursor hacia adelante y como slo lectura
(FORWARD_ONLY y READ_ONLY).
SCROLL_LOCKS permite garantizar el xito de las instrucciones UPDATE y
DELETE que pueden ser ejecutadas en relacin al cursor. Con este tipo de
cursor, se fija un bloqueo en la apertura para evitar que otra transaccin trate
de modificar los datos.
OPTIMISTIC con esta opcin, puede que una operacin de UPDATE o DLETE
realizada en el cursor no pueda ejecutarse correctamente, porque otra
transaccin haya modificado los datos en paralelo.
TYPE_WARNING se enva un mensaje (warning) a la aplicacin cliente, si se
efectan conversiones implcitas de tipo.

2.4.2 Abrir un Cursor

Esta instruccin permite hacer operativo el cursor y crear eventualmente las tablas
temporales asociadas. La variable @@CURSOR_ROWS se asigna despus de
OPEN.

Teniendo en cuenta el espacio en disco y la memoria utilizada, y el bloqueo eventual
de los datos en la apertura del cursor, esta operacin debe ser ejecutada lo ms cerca
posible del tratamiento de los datos extrados del cursor.

Sintaxis:

OPEN [ GLOBAL ] <nombr e_cur sor >

2.4.2.1 Leer un Registro: FETCH
Es la instruccin que permite extraer una fila del cursor y asignar valores a variables
con su contenido. Tras FETCH, la variable @@FETCH_STATUS est a 0 si FETCH
no retorna errores.

Sintaxis:

FETCH [ NEXT | PRI OR | LAST | ABSOLUTE n | RELATI VE n ]
[ FROM] [ GLOBAL ] <nombr e del cur sor >
[ I NTO l i st a de var i abl es ]
NEXT

NEXT lee la fila siguiente (nica opcin posible para los INSENSITIVE
CURSOR).
PRIOR lee la fila anterior
FIRST lee la primera fila
LAST lee la ltima fila
ABSOLUTE n lee la ensima fila del conjunto
RELATIVE n lee la ensima fila a partir de la fila actual.

Cuando trabajamos con cursores, la funcin @@FETCH_STATUS nos indica el
estado de la ltima instruccin FETCH emitida. Los valores posibles son los
siguientes:

Valor devuelto Descripcin
0 La instruccin FETCH se ejecut correctamente
-1 La instruccin FETCH no se ejecut correctamente o la fila
estaba ms all del conjunto de resultados.
-2 Falta la fila recuperada.

2.4.3 Cerrar el cursor

Cierra el cursor y libera la memoria. Esta operacin debe interponerse lo antes posible
con el fin de liberar los recursos cuanto antes. La sintaxis es la siguiente:

CLOSE <nombr e_cur sor >

Despus de cerrado el cursor, ya no es posible capturarlo o actualizarlo/eliminarlo. Al
cerrar el cursor se elimina su conjunto de claves dejando la definicin del cursor
intacta, es decir, un cursor cerrado puede volver a abrirse sin tener que volver a
declararlo.

80
2.4.3.1 Liberar los recursos

Es un comando de limpieza, no forma parte de la especificacin ANSI. La sintaxis es la
siguiente:

DEALLOCATE <nombr e_cur sor >

Ejemplo 1: Trabajando con un cursor, mostrar el primer registro de productos

- - CURSORES
DECLARE MI _CURSOR CURSOR FOR
SELECT TOP 1 * FROM COMPRAS. PRODUCTOS

- - ABRI R
OPEN MI _CURSOR

- - I MPRI MI R EL PRI MER REGI STRO
FETCH NEXT FROM MI _CURSOR

- - CERRAR
CLOSE MI _CURSOR

- - LI BERAR
DEALLOCATE MI _CURSOR

Ejemplo 2: Trabajando con un cursor dinmico, defina un cursor dinmico que permita
visualizar: el primer registro, el registro en la posicin 6 y el ltimo registro.

- - CURSORES
DECLARE MI _CURSOR CURSOR SCROLL FOR
SELECT * FROM COMPRAS. PRODUCTOS

- - ABRI R
OPEN MI _CURSOR

- - I MPRI MI R LOS REGI STROS
FETCH FI RST FROM MI _CURSOR
FETCH ABSOLUTE 6 FROM MI _CURSOR
FETCH LAST FROM MI _CURSOR

- - CERRAR
CLOSE MI _CURSOR

- - LI BERAR
DEALLOCATE MI _CURSOR

Ejemplo: Trabajando con un cursor, listar los clientes registrados en la base de datos,
incluya el nombre del pas.

- - DECLARO VARI ABLES DE TRABAJ O
DECLARE @I D VARCHAR( 5) , @NOMBRE VARCHAR( 50) , @PAI S VARCHAR( 50)

- - DECLARO EL CURSOR
SELECT C. I DCLI ENTE,
C. NOMBRECLI ENTE,
P. NOMBREPAI S
FROM VENTAS. CLI ENTES C
J OI N VENTAS. PAI SES P ON C. I DPAI S=P. I DPAI S

- - ABRI R
OPEN MI _CURSOR

- - LEER EL PRI MER REGI STRO
FETCH MI _CURSOR I NTO @I D, @NOMBRE, @PAI S

- - MI ENTRAS PUEDA LEER EL REGI STRO
WHI LE @@FETCH_STATUS=0
BEGI N
- - I MPRI MI R EL REGI STRO
PRI NT @I D + ' , ' +@NOMBRE+' , ' +@PAI S
- - LEER EL REGI STRO SI GUI ENTE
FETCH MI _CURSOR I NTO @I D, @NOMBRE, @PAI S
END
82
- - CERRAR
CLOSE MI _CURSOR

- - LI BERAR
DEALLOCATE MI _CURSOR;
GO

Ejemplo: Trabajando con un cursor, listar la relacin de los clientes que han registrado
pedidos. En dicho proceso, debemos imprimir el nombre del cliente, la cantidad de
pedidos registrados y al finalizar totalizar el proceso (suma total de todos los pedidos).

DECLARE @NOMBRE VARCHAR( 50) , @Q I NT, @TOTAL I NT
SET @TOTAL=0

SELECT C. NOMBRECI A,
COUNT( *)
FROM VENTAS. CLI ENTES C
J OI N VENTAS. PEDI DOSCABE PC ON C. I DCLI ENTE=PC. I DCLI ENTE
GROUP BY C. NOMBRECLI ENTE

- - ABRI R
OPEN MI _CURSOR

FETCH MI _CURSOR I NTO @NOMBRE, @Q

BEGI N
PRI NT @NOMBRE+' , ' +CAST( @Q AS VARCHAR)
- - ACUMULAR
SET @TOTAL += @Q
FETCH MI _CURSOR I NTO @NOMBRE, @Q
END
- - CERRAR
CLOSE MI _CURSOR

- - LI BERAR

- - I MPRI MI R
PRI NT ' TOTAL DE PEDI DOS: ' + CAST( @TOTAL AS VARCHAR)

GO

Ejemplo: Trabajando con un cursor, imprimir un listado de los pedidos realizados por
cada ao. En dicho proceso, listar por cada ao los pedidos registrados y totalizando
dichos pedidos por dicho ao.

DECLARE @Y I NT, @Y1 I NT, @PEDI DO I NT, @MONTO DECI MAL, @TOTAL
DECI MAL

SET @TOTAL=0

SELECT YEAR( FECHAPEDI DO) ,
PC. I DPEDI DO,
SUM( PRECI OUNI DAD*CANTI DAD)
FROM VENTAS. PEDI DOSCABE C
J OI N VENTAS. PEDI DOSDETA D ON C. I DPEDI DO=D. I DPEDI DO
GROUP BY YEAR( FECHAPEDI DO) , PC. I DPEDI DO
ORDER BY 1

- - ABRI R EL CURSOR
OPEN MI _CURSOR

FETCH MI _CURSOR I NTO @Y, @PEDI DO, @MONTO

84
- - ASI GNAR A LA VARI ABLE @Y1 EL VALOR I NI CI AL DE @Y
SET @Y1 = @Y

- - I MPRI MI R EL PRI MER AO
PRI NT ' AO: ' + CAST( @Y1 AS VARCHAR)

BEGI N
- - SI COI NCI DEN LOS VALORES ACUMULAR EL TOTAL
I F( @Y = @Y1)
SET @TOTAL += @MONTO
ELSE
- - SI NO COI NCI DEN I MPRI MI R EL TOTAL, I NI CI ALI ZAR
VARI ABLES
BEGI N
PRI NT ' I MPORTE EN: ' +CAST( @Y1 AS VARCHAR) +
SPACE( 2) + ' ES ' +CAST( @TOTAL AS VARCHAR)
PRI NT ' AO: ' + CAST( @Y AS VARCHAR)
SET @Y1=@Y
SET @TOTAL=@MONTO
END
PRI NT CAST( @PEDI DO AS VARCHAR) + SPACE( 5) +STR( @MONTO)
END

- - CERRAR
CLOSE MI _CURSOR

- - LI BERAR

- - I MPRI MI R LOS TOTALES FI NALES
PRI NT ' I MPORTE EN: ' +CAST( @Y1 AS VARCHAR) +SPACE( 2) +' ES
' +STR( @TOTAL)

Para actualizar los datos de un cursor, debemos especificar la clusula FOR UPDATE
despus de la sentencia SELECT en la declaracin del cursor, y WHERE CURRENT
OF <nombre_cursor>en la sentencia UPDATE.

En el ejemplo siguiente, actualizamos el precio de los productos: si su stock es mayor
o igual a 1000, se descuenta el precio al 50%, sino se descuenta el precio al 20%.

- - DECLARACI ON DE VARI ABLES PARA EL CURSOR
DECLARE @I D I NT, @NOMBRE VARCHAR( 255) , @PRECI O DECI MAL, @ST I NT

- - DECLARACI N DEL CURSOR DE ACTUALI ZACI ON
DECLARE CPRODUCTO CURSOR FOR
SELECT I DPRODUCTO,
NOMBREPRODUCTO,
PRECI OUNI DAD,
FROM TB_PRODUCTOS
FOR UPDATE

OPEN CPRODUCTO

FETCH CPRODUCTO I NTO @I D, @NOMBRE, @PRECI O, @ST

WHI LE ( @@FETCH_STATUS = 0 )
BEGI N
I F( @ST>=1000)
SET @PRECI O = 0. 5*@PRECI O
ELSE
SET @PRECI O = 0. 80*@PRECI O

UPDATE TB_PRODUCTOS SET PRECI OUNI DAD = @PRECI O
WHERE CURRENT OF CPRODUCTO

- - I MPRI MI R
PRI NT ' EL PRECI O DE PRODUCTO ' + @NOMBRE+ ' ES ' + STR( @PRECI O)

86
- - LECTURA DE LA SI GUI ENTE FI LA DEL CURSOR
END

- - CI ERRE DEL CURSOR
CLOSE CPRODUCTO

- - LI BERAR LOS RECURSOS
DEALLOCATE CPRODUCTO


Resumen
SQL es un lenguaje de consulta para los sistemas de bases de datos relacinales,
pero que no posee la potencia de los lenguajes de programacin. No permite el uso
de variables, estructuras de control de flujo, bucles y dems elementos
caractersticos de la programacin.
TRANSACT-SQL ampla el SQL estndar con la implementacin de estructuras de
programacin. Estas implementaciones le resultarn familiares a los
desarrolladores con experiencia en C++, J ava, Visual Basic .NET, C#y lenguajes
similares.
Una variable es una entidad a la que se asigna un valor. Este valor puede cambiar
durante el proceso donde se utiliza la variable. SQL Server tiene dos tipos de
variables: locales y globales. Las variables locales estn definidas por el usuario,
mientras que las variables globales las suministra el sistema y estn predefinidas.
La estructura condicional IF se utiliza para definir una condicin que determina si
se ejecutar la instruccin siguiente. La instruccin SQL se ejecuta si la condicin
se cumple, es decir, si devuelve TRUE (verdadero). La palabra clave ELSE
introduce una instruccin SQL alternativa que se ejecuta cuando la condicin IF
devuelva FALSE.
La estructura CASE evala una lista de condiciones y devuelve una de las varias
expresiones de resultado posibles. La expresin CASE tiene dos formatos:
La expresin CASE sencilla compara una expresin con un conjunto de
expresiones sencillas para determinar el resultado.
La expresin CASE buscada evala un conjunto de expresiones booleanas
para determinar el resultado.
La estructura WHILE ejecuta en forma repetitiva un conjunto o bloque de
instrucciones SQL siempre que la condicin especificada sea verdadera. Se puede
controlar la ejecucin de instrucciones en el bucle WHILE con las palabras clave
BREAK y CONTINUE.
BREAK y CONTINUE controlan el funcionamiento de las instrucciones dentro de
un bucle WHILE. BREAK permite salir del bucle WHILE. CONTINUE hace que el
bucle WHILE se inicie de nuevo.
88
SQL Server proporciona el control de errores a travs de las instrucciones TRY y
CATCH. Estas nuevas instrucciones suponen un gran paso adelante en el control
de errores en SQL Server. La variable @@ERROR devuelve el nmero de error de
la ltima instruccin TRANSACT-SQL ejecutada; si la variable devuelve 0, la
TRANSACT-SQL anterior no encontr errores. Si el error se encuentra en la vista
de catlogo sys.sysmessages, entonces @@ERROR contendr el valor de la
columna sys.sysmessages.error para dicho error. Puede ver el texto asociado
con el nmero de error @@ERROR en sys.sysmessages.description.
Un cursor es una variable que nos permite recorres con un conjunto de resultados
obtenidos a travs de una sentencia SELECT fila por fila.
Se utilizarn los cursores ANSI cuando sea necesario tratar las filas de manera
individual en un conjunto o cuando SQL no pueda actuar nicamente sobre las
filas afectadas. Los cursores API sern utilizados por las aplicaciones cliente para
tratar volmenes importantes o para gestionar varios conjuntos de resultados
Cuando trabajemos con cursores, debemos seguir los siguientes pasos:
Declarar el cursor, utilizando DECLARE
Abrir el cursor, utilizando OPEN
Leer los datos del cursor, utilizando FETCH ... INTO
Cerrar el cursor, utilizando CLOSE
Liberar el cursor, utilizando DEALLOCATE
Para actualizar los datos de un cursor debemos especificar la clusula FOR UPDATE
despus de la sentencia SELECT en la declaracin del cursor, y WHERE CURRENT
OF <nombre_cursor>en la sentencia UPDATE.

pginas.

http://www.devjoker.com/contenidos/catss/240/Cursores-en-Transact-SQL.aspx
Aqu hallar los conceptos de cursores TRANSACT SQL.
http://www.a2sistemas.com/blog/2009/02/06/uso-de-cursores-en-transact-sql/
En esta pgina, hallar los conceptos y ejercicios de Cursores
http://www.devjoker.com/gru/Tutorial-Transact-SQL/TSQL/Tutorial-Transact-
SQL.aspx
Aqu hallar los conceptos de programacin TRANSACT SQL.


PROGRAMACIN AVANZADA TRANSACT SQL
Al trmino de la unidad, el alumno, haciendo uso de las estructuras de
programacin TRANSACT-SQL desarrolladas en clase, define e implementa
objetos de base de datos que permite realizar operaciones de consulta y
actualizacin de datos.

TEMARIO
1. Funciones definidas por el usuario
1.1. Funciones escalares
1.2. Funciones de tabla en lnea
1.3. Funciones de tabla de multi sentencias
1.4. Limitaciones
2. Procedimientos almacenados
2.1. Especificar parmetros
2.2. Uso de cursores en procedimientos almacenados
2.3. Modificar datos con procedimientos almacenados
2.4. Transacciones en TRANSACT-SQL
2.4.1. Transacciones implcitas y explcitas
3. Triggers o disparadores
3.1. Definicin de trigger
3.2. Creacin de disparadores
3.2.1. Funcionamiento de los disparadores
3.3. Uso de INSTEAD OF
Los alumnos programan objetos de base de datos para recuperar datos.
Los alumnos programan objetos de base de datos utilizando TRANSACT-SQL para
recuperar y actualizar datos.

UNIDAD DE
APRENDIZAJ E
3
90
3.1 PROGRAMACIN AVANZADA TRANSACT SQL

3.1.1 Funciones definidas por el usuario

Las funciones son rutinas que permiten encapsular sentencias TRANSACT-SQL que
se ejecutan frecuentemente.

Las funciones definidas por el usuario, en tiempo de ejecucin de lenguaje
TRANSACT-SQL o comn (CLR), acepta parmetros, realiza una accin, como un
clculo complejo, y devuelve el resultado de esa accin como un valor. El valor de
retorno puede ser un escalar (nico) valor o una tabla.

Las funciones de usuario son definidas para crear una rutina reutilizables que se
pueden utilizar en las siguientes maneras:
En TRANSACT-SQL como SELECT
En las aplicaciones de llamar a la funcin
En la definicin de otra funcin definida por el usuario
Para parametrizar una vista o mejorar la funcionalidad de una vista indizada
Para definir una columna en una tabla
Para definir una restriccin CHECK en una columna
Para reemplazar a un procedimiento almacenado

Las funciones de usuario, segn el tipo de retorno se clasifican en las siguientes:
1. Funciones Escalares
2. Funciones con valores de tabla de varias instrucciones
3. Funciones con valores de tabla en lnea

3.1.1.1 Funciones escalares

Son aquellas funciones donde retornan un valor nico: tipo de datos como int, Money,
varchar, real, etc. Pueden ser utilizadas en cualquier lugar, incluso, incorporada dentro
de las sentencias SQL.

92
CREATE FUNCTI ON [ PROPI ETARI O. ] NOMBRE_FUNCI ON
( [ {@PARAMETER TI PO DE DATO [ =DEFAULT] } [ , . . N] ] )
RETURNS VALOR_ESCALAR
[ AS]
BEGI N
CUERPO DE LA FUNCI ON
RETURN EXPRESI ON_ESCALAR
END

Ejemplo: Crear una funcin que retorne el precio promedio de todos los productos

CREATE FUNCTI ON DBO. PRECI OPROMEDI O( ) RETURNS DECI MAL
AS
BEGI N
DECLARE @PROM DECI MAL
SELECT @PROM=AVG( PRECI OUNI DAD)
RETURN @PROM
END
GO

- - MOSTRAR EL RESULTADO
PRI NT DBO. PRECI OPROMEDI O( )

Ejemplo: Defina una funcin donde ingrese el id del empleado y retorne la cantidad de
pedidos registrados en el presente ao

CREATE FUNCTI ON DBO. PEDI DOSEMPLEADO( @I D I NT) RETURNS DECI MAL
AS
BEGI N
DECLARE @Q DECI MAL=0
SELECT @Q=COUNT( *)
WHERE YEAR( FECHAPEDI DO) =YEAR( GETDATE( ) ) AND I DEMPLEADO=@I D
I F @Q I S NULL
SET @Q=0
RETURN @Q
END
GO
- - MOSTRAR EL RESULTADO DEL EMPLEADO DE CODI GO 4
PRI NT DBO. PEDI DOSEMPLEADO( 4)

3.1.1.2 Funciones de tabla en lnea

Las funciones de tabla en lnea son las funciones que devuelven la salida de una
simple declaracin SELECT. La salida se puede utilizar adentro de J OINS o querys
como si fuera una tabla de estndar.

La sintaxis para una funcin de tabla en lnea es como sigue:

CREATE FUNCTION [ pr opi et ar i o. ] nombr e_f unci on
( [ { @par amet er t i po de dat o [ = def aul t ] } [ , . . n] ] )
RETURNS TABLE
[ AS]
RETURN [ ( ] Sent enci a SQL [ ) ]

Ejemplo: Defina una funcin que liste los registros de los clientes, e incluya el nombre
del Pais.

CREATE FUNCTI ON DBO. CLI ENTES( )
RETURNS TABLE
AS
RETURN ( SELECT I DCLI ENTE AS ' CODI GO' ,
NOMBRECI A AS ' CLI ENTE' ,
DI RECCI ON,
NOMBREPAI S AS ' PAI S'
FROM VENTAS. CLI ENTES C J OI N VENTAS. PAI SES P
ON C. I DPAI S = P. I DPAI S)
GO

- - EJ ECUTANDO LA FUNCI ON
SELECT * FROM DBO. CLI ENTES( ) WHERE PAI S=' CHI LE'
GO

94
Ejemplo: Defina una funcin que liste los registros de los pedidos por un determinado
ao, incluya el nombre del producto, el precio que fue vendido y la cantidad vendida

CREATE FUNCTI ON DBO. PEDI DOSAO( @Y I NT)
RETURNS TABLE
AS
RETURN ( SELECT PC. I DPEDI DO AS ' PEDI DO' ,
FECHAPEDI DO,
NOMBREPRODUCTO,
PD. PRECI OUNI DAD AS ' PRECI O' ,
CANTI DAD
FROM VENTAS. PEDI DOSCABE PC
J OI N VENTAS. PEDI DOSDETA PD ON PC. I DPEDI DO=PD. I DPEDI DO
J OI N COMPRAS. PRODUCTOS P ON PD. I DPRODUCTO=P. I DPRODUCTO
WHERE YEAR( FECHAPEDI DO) = @Y)
GO

SELECT * FROM DBO. PEDI DOSAO( 2010)
GO

3.1.1.3 Funciones de tabla de multisentencias

Son similares a los procedimientos almacenados excepto que vuelven una tabla. Este
tipo de funcin se usa en situaciones donde se requiere ms lgica y proceso. Lo que
sigue es la sintaxis para unas funciones de tabla de multisentencias:

CREATE FUNCTION [ pr opi et ar i o. ] nombr e_f unci on
( [ {@par amet er t i po de dat o [ = def aul t ] } [ , . . n] ] )
RETURNS TABLE
[ AS]
BEGI N
Cuer po de l a f unci n
RETURN
END

Ejemplo: Defina una funcin que retorne el inventario de los productos registrados en
la base de datos.

CREATE FUNCTI ON DBO. I NVENTARI O( )
RETURNS @TABLA TABLE( I DPRODUCTO I NT,
NOMBRE VARCHAR( 50) ,
PRECI O DECI MAL,
STOCK I NT)
AS
BEGI N
I NSERT I NTO @TABLA
SELECT I DPRODUCTO,
NOMBREPRODUCTO,
PRECI OUNI DAD,
RETURN
END
GO

SELECT * FROM DBO. I NVENTARI O( )
GO

Ejemplo: Defina una funcin que permita generar un reporte de ventas por empleado,
en un determinado ao. En este proceso, la funcin debe retornar: los datos del
empleado, la cantidad de pedidos registrados y el monto total por empleado

CREATE FUNCTI ON DBO. REPORTEVENTAS( @Y I NT)
RETURNS @TABLA TABLE( I D I NT,
NOMBRE VARCHAR( 50) ,
CANTI DAD I NT,
MONTO DECI MAL)
AS
BEGI N
I NSERT I NTO @TABLA
SELECT E. I DEMPLEADO,
APELLI DOS,
COUNT( *) ,
96
SUM( PRECI OUNI DAD*CANTI DAD)
FROM VENTAS. PEDI DOSCABE PC J OI N VENTAS. PEDI DOSDETA PD
ON PC. I DPEDI DO = PD. I DPEDI DO J OI N VENTAS. EMPLEADOS E
ON E. I DEMPLEADO = PC. I DEMPLEADO
WHERE YEAR( FECHAPEDI DO) = @Y
GROUP BY E. I DEMPLEADO, APELLI DOS
RETURN
END
GO

- - I MPRI MI R EL REPORTE DEL AO 2010
SELECT * FROM DBO. REPORTEVENTAS( 2010)
GO

3.1.1.4 Limitaciones

Las funciones definidas por el usuario tienen algunas restricciones. No todas las
sentencias SQL son vlidas dentro de una funcin. Las listas siguientes enumeran las
operaciones vlidas e invlidas de las funciones:

Vlido:
Las sentencias de asignacin
Las sentencias de Control de Flujo
Sentencias SELECT y modificacin de variables locales
Operaciones de cursores sobre variables locales Sentencias INSERT,
UPDATE, DELETE con variables locales

Invlidas:
Armar funciones no determinadas como GetDate()
Sentencias de modificacin o actualizacin de tablas o vistas
Operaciones CURSOR FETCH que devuelven datos del cliente

3.1.2 Procedimientos Almacenados

Los procedimientos almacenados son grupos formados por instrucciones SQL y el
lenguaje de control de flujo. Cuando se ejecuta un procedimiento, se prepara un plan
de ejecucin para que la subsiguiente ejecucin sea muy rpida. Los procedimientos
almacenados pueden:

Incluir parmetros
Llamar a otros procedimientos
Devolver un valor de estado a un procedimiento de llamada o lote para indicar el
xito o el fracaso del mismo y la razn de dicho fallo.
Devolver valores de parmetros a un procedimiento de llamada o lote
Ejecutarse en SQL Server remotos

La posibilidad de escribir procedimientos almacenados mejora notablemente la
potencia, eficacia y flexibilidad de SQL. Los procedimientos compilados mejoran la
ejecucin de las instrucciones y lotes de SQL de forma dramtica. Adems, los
procedimientos almacenados pueden ejecutarse en otro SQL Server si el servidor del
usuario y el remoto estn configurados para permitir logins remotos.

Los procedimientos almacenados se diferencian de las instrucciones SQL ordinarias y
de lotes de instrucciones SQL en que estn precompilados. La primera vez que se
ejecuta un procedimiento, el procesador de consultas SQL Server lo analiza y prepara
un plan de ejecucin que se almacena en forma definitiva en una tabla de sistema.
Posteriormente, el procedimiento se ejecuta segn el plan almacenado, puesto que ya
se ha realizado la mayor parte del trabajo de procesamiento de consultas, los
procedimientos almacenados se ejecutan casi de forma instantnea.

Los procedimientos almacenados se crean con CREATE PROCEDURE. Para ejecutar
un procedimiento almacenado, ya sea un procedimiento del sistema o uno definido por
el usuario, use el comando EXECUTE. Tambin, puede utilizar el nombre del
procedimiento almacenado solo, siempre que sea la primera palabra de una
instruccin o lote.

Sintaxis para crear un procedimiento almacenado:

98
CREATE PROCEDURE <Pr ocedur e_Name, sysname, Pr ocedur eName>
- - Aadi r par met r os al pr ocedi mi ent o al macenado
<@Par am1> <Dat at ype_For _Par am1> = <Def aul t _Val ue_For _Par am1>,
<@Par am2> <Dat at ype_For _Par am2> = <Def aul t _Val ue_For _Par am2>
AS
BEGI N
- - I nser t ar l a sent enci a par a el pr ocedi mi ent o
Sent enci a SQL
END

Sintaxis para modificar un procedimiento almacenado:

ALTER PROCEDURE NOMBRE_PROCEDI MI ENTO
<@Par am1> <Dat at ype_For _Par am1> = <Def aul t _Val ue_For _Par am1>,
<@Par am2> <Dat at ype_For _Par am2> = <Def aul t _Val ue_For _Par am2>
AS
CONSULTA_SQL

Sintaxis para eliminar un procedimiento almacenado:

DROP PROCEDURE NOMBRE_PROCEDI MI ENTO

Por ejemplo: Defina un procedimiento almacenado que liste todos los clientes

- - PROCEDI MI ENTO ALMACENADO
CREATE PROCEDURE USP_CLI ENTES
AS
SELECT I DCLI ENTE AS CODI GO,
NOMBRECI A AS CLI ENTE,
DI RECCI ON,
TELEFONO
FROM VENTAS. CLI ENTES
GO

Como se aprecia, el procedimiento mostrado no tiene parmetros de entrada y para
ejecutarlo deber usar una de las siguientes sentencias:

- - EJ ECUTANDO EL PROCEDI MI ENTO ALMACENADO
EXEC USP_CLI ENTES
GO

O simplemente

USP_CLI ENTES
GO

Por ejemplo: Cree un procedimiento almacenado que permita buscar los datos de los
pedidos registrados en una determinada fecha. El procedimiento deber definir un
parmetro de entrada de tipo DateTime

CREATE PROCEDURE USP_PEDI DOSFECHAS
@F1 DATETI ME
AS
SELECT *
WHERE FECHAPEDI DO = @F1
GO

Como se aprecia, el procedimiento mostrado tiene 1 parmetro de entrada y para
ejecutarlo deber usar la siguiente sentencia:

EXEC USP_PEDI DOSBYFECHAS @F1=' 10- 01- 1996'
GO

O simplemente, colocar la lista de los valores el cual ser asignada a cada parmetro,
donde el primer valor le corresponde a @f1

EXEC USP_PEDI DOSBYFECHAS ' 10- 01- 1996'
GO

100
La sentencia ALTER PROCEDURE permite modificar el contenido del procedimiento
almacenado. En este procedimiento, realizamos la consulta de pedidos entre un rango
de dos fechas.

ALTER PROCEDURE USP_PEDI DOSBYFECHAS
@F1 DATETI ME,
@F2 DATETI ME
AS
SELECT *
WHERE FECHAPEDI DO BETWEEN @F1 AND @F2
GO

Para ejecutar el procedimiento almacenado

EXEC USP_PEDI DOSBYFECHAS @F1=' 10- 01- 1996' , @F2=' 10- 10- 1996'
GO

Para eliminar un procedimiento almacenado, ejecute la instruccin DROP
PROCEDURE

DROP PROCEDURE USP_PEDI DOSBYFECHAS
GO

3.1.2.1 Especificar parmetros

Un procedimiento almacenado se comunica con el programa que lo llama mediante
sus parmetros. Cuando un programa ejecuta un procedimiento almacenado, es
posible pasarle valores mediante los parmetros del procedimiento.

Estos valores se pueden utilizar como variables estndar en el lenguaje de
programacin TRANSACT-SQL. El procedimiento almacenado tambin puede
devolver valores al programa que lo llama mediante parmetros OUTPUT. Un
procedimiento almacenado puede tener hasta 2.100 parmetros, cada uno de ellos
con un nombre, un tipo de datos, una direccin y un valor predeterminado

3.1.2.2 Especificar el nombre del parmetro

Cada parmetro de un procedimiento almacenado, debe definirse con un nombre
nico. Los nombres de los procedimientos almacenados deben empezar por un solo
carcter @, como una variable estndar de TRANSACT-SQL, y deben seguir las
reglas definidas para los identificadores de objetos. El nombre del parmetro se puede
utilizar en el procedimiento almacenado para obtener y cambiar el valor del parmetro.

3.1.2.3 Especificar la direccin del parmetro

La direccin de un parmetro puede ser de entrada, que indica que un valor se pasa al
parmetro de entrada de un procedimiento almacenado o de salida, que indica que el
procedimiento almacenado devuelve un valor al programa que lo llama mediante un
parmetro de salida. El valor predeterminado es un parmetro de entrada.
Para especificar un parmetro de salida, debe indicar la palabra clave OUTPUT en la
definicin del parmetro del procedimiento almacenado. El programa que realiza la
llamada tambin debe utilizar la palabra clave OUTPUT al ejecutar el procedimiento
almacenado, a fin de guardar el valor del parmetro en una variable que se pueda
utilizar en el programa que llama.

3.1.2.4 Especificar un valor de parmetro predeterminado

Puede crear un procedimiento almacenado con parmetros opcionales especificando
un valor predeterminado para los mismos. Al ejecutar el procedimiento almacenado, se
utilizar el valor predeterminado si no se ha especificado ningn otro.
Es necesario especificar valores predeterminados, ya que el sistema devuelve un error
si en el procedimiento almacenado no se especifica un valor predeterminado para un
parmetro y el programa que realiza la llamada no proporciona ningn otro valor al
ejecutar el procedimiento.

Por ejemplo: Cree un procedimiento almacenado que muestre los datos de los
pedidos, los productos que fueron registrados por cada pedido, el precio del producto y
la cantidad registrada por un determinado cliente y ao. El procedimiento recibir
como parmetro de entrada el cdigo del cliente y el ao. Considere que el parmetro
ao tendr un valor por defecto: el ao del sistema.
102

CREATE PROCEDURE USP_PEDI DOSCLI ENTEAO
@I D VARCHAR( 5) ,
@AO I NT = 2011
AS
SELECT PC. I DPEDI DO AS ' PEDI DO' ,
FECHAPEDI DO, NOMBREPRODUCTO,
PD. PRECI OUNI DAD AS ' PRECI O' ,
CANTI DAD
ON PC. I DPEDI DO = PD. I DPEDI DO J OI N COMPRAS. PRODUCTOS P
ON PD. I DPRODUCTO = P. I DPRODUCTO
WHERE YEAR( FECHAPEDI DO) = @AO
AND I DCLI ENTE = @I D
GO

Como el procedimiento almacenado ha definido un valor por defecto al parmetro
@ao, podemos ejecutar el procedimiento enviando solamente el valor para el
parmetro @id

EXEC USP_PEDI DOSCLI ENTEAO @I D=' ALFKI '
GO

O simplemente enviando los valores a los dos parmetros

EXEC USP_PEDI DOSCLI ENTEAO @I D=' ALFKI ' , @AO=1997
GO

Por ejemplo: Implemente un procedimiento almacenado que retorne la cantidad de
pedidos y el monto total de pedidos, registrados por un determinado empleado
(parmetro de entrada su id del empleado) y en determinado ao (parmetro de
entrada). Dicho procedimiento retornar la cantidad de pedidos y el monto total

CREATE PROCEDURE USP_REPORTEPEDI DOSEMPLEADO
@I D I NT,
@Y I NT,
@Q I NT OUTPUT,
@MONTO DECI MAL OUTPUT
AS
SELECT @Q= COUNT( *) ,
@MONTO = SUM( PRECI OUNI DAD*CANTI DAD)
ON PC. I DPEDI DO = PD. I DPEDI DO
WHERE I DEMPLEADO =@I D AND YEAR( FECHAPEDI DO) = @Y
GO

Al ejecutar el procedimiento almacenado, primero declaramos las variables de retorno
y al ejecutar, las variables de retorno se le indicara con la expresin OUTPUT.

DECLARE @Q I NT, @M DECI MAL
EXEC USP_REPORTEPEDI DOSEMPLEADO @I D=2,
@Y=1997,
@Q=@Q OUTPUT,
@MONTO=@M OUTPUT
GO

PRI NT ' CANTI DAD DE PEDI DOS COLOCADOS: ' + STR( @Q)
PRI NT ' MONTO PERCI BI DO: ' +STR( @M)
GO

3.1.2.5 Uso de cursores en procedimientos almacenados

Los cursores son especialmente tiles en procedimientos almacenados. Permiten
llevar a cabo la misma tarea utilizando slo una consulta que, de otro modo, requerira
varias. Sin embargo, todas las operaciones del cursor deben ejecutarse dentro de un
solo procedimiento. Un procedimiento almacenado no puede abrir, recobrar o cerrar un
cursor que no est declarado en el procedimiento. El cursor no est definido fuera del
alcance del procedimiento almacenado.

104
Por ejemplo: Implemente un procedimiento almacenado que imprimir cada uno de los
registros de los productos, donde al finalizar, visualice el total del inventario (Suma de
cantidad de productos)

CREATE PROCEDURE USP_I NVENTARI O
AS
- - DECLARACI ON DE VARI ABLES PARA EL CURSOR
DECLARE @I D I NT, @NOMBRE VARCHAR( 255) , @PRECI O DECI MAL, @ST I NT,
@I NV I NT
SET @I NV=0

DECLARE CPRODUCTO CURSOR FOR
SELECT I DPRODUCTO,
NOMBREPRODUCTO,
PRECI OUNI DAD,

OPEN CPRODUCTO

WHI LE ( @@FETCH_STATUS = 0 )
BEGI N
- - I MPRI MI R
PRI NT STR( @I D) + SPACE( 5) + @NOMBRE + SPACE( 5) +
STR( @PRECI O) + SPACE( 5) + STR( @ST)
- - ACUMULAR
SET @I NV += @ST
- - LECTURA DE LA SI GUI ENTE FI LA DEL CURSOR
END

- - CI ERRE DEL CURSOR
CLOSE CPRODUCTO

- - LI BERAR LOS RECURSOS
DEALLOCATE CPRODUCTO
PRI NT ' I NVENTARI O DE PRODUCTOS: ' + STR( @I NV)
GO

En el siguiente ejemplo, definimos un procedimiemto que permita generar un reporte
de los pedidos realizados por un empleado en cada ao, totalizando el monto de sus
operaciones por cada ao.

CREATE PROCEDURE USP_REPORTEPEDI DOSXAOXEMPLEADO
@EMP I NT=1
AS
- - DECLARACI N DE VARI ABLES DE TRABAJ O
DECLARE @Y I NT, @Y1 I NT, @PEDI DO I NT, @MONTO DECI MAL, @TOTAL DECI MAL
SET @TOTAL=0

SELECT YEAR( FECHAPEDI DO) AS ' AO' ,
PC. I DPEDI DO,
SUM( PRECI OUNI DAD*CANTI DAD) AS MONTO
FROM VENTAS. PEDI DOSCABE PC
J OI N VENTAS. PEDI DOSDETA PD
ON PC. I DPEDI DO=PD. I DPEDI DO
WHERE I DEMPLEADO = @EMP
GROUP BY YEAR( FECHAPEDI DO) , PC. I DPEDI DO
ORDER BY 1

OPEN MI _CURSOR

- - LECTURA DEL PRI MER REGI STRO

- - ASI GNACI N DEL VALOR I NI CI AL DE @Y EN LA VARI ABLE @Y1
SET @Y1 = @Y

- - I MPRI MI R EL PRI MER AO
PRI NT ' AO: ' + CAST( @Y1 AS VARCHAR)

106
- - RECORRER EL CURSOS MI ENTRAS HAYAN REGI STROS
BEGI N
I F( @Y = @Y1)
BEGI N
- - ACUMULAR
SET @TOTAL += @MONTO
END
ELSE
BEGI N
PRI NT ' AO: ' + CAST( @Y1 AS VARCHAR) + SPACE( 2) +
' I MPORTE: ' + CAST( @TOTAL AS VARCHAR)
PRI NT ' AO: ' + CAST( @Y AS VARCHAR)
SET @Y1=@Y
SET @TOTAL=@MONTO
END
PRI NT CAST( @PEDI DO AS VARCHAR) + SPACE( 5) +
CAST( @MONTO AS VARCHAR)
- - LECTURA DEL SI GUI ENTE REGI STRO
END

- - CERRAR EL CURSOR
CLOSE MI _CURSOR

- - LI BERAR EL RECURSO

PRI NT ' AO: ' + CAST( @Y1 AS VARCHAR) + SPACE( 2) + ' I MPORTE: ' +
STR( @TOTAL)
GO

Al ejecutar el procedimiento almacenado, se le enva el parmetro que representa el id
del empleado, imprimiendo su record de ventas de pedidos por ao

USP_REPORTEPEDI DOSXAOXEMPLEADO 2
GO
107

Ejecutado el procedimiento almacenado
lista cada pedido por ao, mostrando al
finalizar el total por cada ao.

3.1.3 Modificar datos con procedimientos almacenados

Los procedimientos almacenados pueden aceptar datos como parmetros de entrada
y pueden devolver datos como parmetros de salida, conjuntos de resultados o
valores de retorno. Adicionalmente, los procedimientos almacenados pueden ejecutar
sentencias de actualizacin de datos: INSERT, UPDATE, DELETE

Por ejemplo, defina un procedimiento almacenado para insertar un registro de la tabla
Clientes, en este procedimiento, definiremos parmetros de entrada que representan
los campos de la tabla.

CREATE PROCEDURE USP_I NSERTACLI ENTE
@I D VARCHAR( 5) ,
@NOMBRE VARCHAR( 50) ,
@DI RECCI ON VARCHAR( 100) ,
@I DPAI S CHAR( 3) ,
@FONO VARCHAR( 15)
AS
I NSERT I NTO VENTAS. CLI ENTES( I DCLI ENTE, NOMCLI ENTE, DI RECCI ON, I DPAI S,
TELEFONO)
VALUES( @I D, @NOMBRE, @DI RECCI ON, @I DPAI S, @FONO)
GO

108
El ejecutar el procedimiento almacenado, se enviar la lista de los parmetros
definidos en el procedimiento.

EXEC USP_I NSERTACLI ENTE ' ABCDE' , ' J UAN CARLOS MEDI NA' ,
' CALLE 25 NO 123' , ' 006' , ' 5450555'
GO

En el siguiente ejemplo, definimos un procedimiento almacenado que permita evaluar
la existencia de un registro de empleado para insertar o actualizar sus datos: Si existe
el cdigo del empleado, actualice sus datos; sino agregue el registro de empleados

CREATE PROCEDURE USP_ACTUALI ZAEMPLEADO
@I D I NT,
@APELLI DO VARCHAR( 50) ,
@FN DATETI ME,
@I DDI S I NT,
@FONO VARCHAR( 15) ,
@I DCARGO I NT,
@FC DATETI ME
AS
MERGE RRHH. EMPLEADOS AS TARGET
USI NG
( SELECT @I D, @NOMBRE, @APELLI DO, @FN, @DI RECCI ON, @I DDI S, @FONO,
@I DCARGO, @FC) AS SOURCE
( I DEMPLEADO, NOMEMPLEADO, APEEMPLEADO, FECNAC, DI RECCI ON, I DDI STRI TO,
FONOEMPLEADO, I DCARGO, FECCONTRATA)
ON ( TARGET. I DEMPLEADO = SOURCE. I DEMPLEADO)
WHEN MATCHED THEN
UPDATE RRHH. EMPLEADOS
SET NOMEMPLEADO=@NOMBRE, APEEMPLEADO=@APELLI DO, FECNAC=@FN,
DI RECCI ON=@DI RECCI ON, I DDI STRI TO=@I DDI S,
FONOEMPLEADO=@FONO, I DCARGO=I DCARGO, FECCONTRATA=@FC
I NSERT I NTO RRHH. EMPLEADOS VALUES( @I D, @NOMBRE, @APELLI DO,
@FN, @DI RECCI ON, @I DDI S, @FONO, @I DCARGO, @FC) ;
GO

Para este caso, hemos utilizado la instruccin MERGE que evala la existencia de un
registro. Si existe, actualizar los datos, sino agrega el registro.

En ocasiones, no sabemos si estamos realizando correctamente un proceso de
insercin, actualizacin o eliminacin de datos a una tabla(s). El script de T-SQL
consiste en realizar un procedimiento almacenado que reciba los datos necesarios
para insertarlos en la tabla, para garantizar la ejecucin correcta de las actualizacin
utilizo las transacciones TRANSACT SQL y para validar la reversin de la
transaccin en caso de que ocurra un ERROR utilizo el control de Errores Try Catch
con ROLLBACK.

3.1.4 TRANSACCIONES EN TRANSACT-SQL

Una transaccin es un conjunto de operaciones TRANSACT SQL que se ejecutan
como un nico bloque, es decir, si falla una operacin TRANSACT SQL fallan todas.
Si una transaccin tiene xito, todas las modificaciones de los datos realizadas
durante la transaccin se confirman y se convierten en una parte permanente de la
base de datos. Si una transaccin encuentra errores y debe cancelarse o revertirse, se
borran todas las modificaciones de los datos.

El ejemplo clsico de transaccin es un registro de pedidos, donde agregamos un
registro a la tabla pedidoscabe y agregamos un registro a la tabla pedidosdeta
(producto solicitado por el cliente) y descontamos el stock del producto solicitado en el
pedido.

CREATE PROCEDURE USP_AGREGAPEDI DO
- - PARMETROS DE PEDI DOSCABE
@I DPED I NT,
@I DCLI VARCHAR( 5) ,
@I DEMP I NT,
@FECPED DATETI ME,

- - PARMETROS DE PEDI DOSDETA
@I DPROD I NT,
@PRE DECI MAL,
@CANT I NT
AS
110
- - AGREGANDO UN REGI STRO A PEDI DOSCABE
I NSERT I NTO VENTAS. PEDI DOSCABE( I DPEDI DO, I DCLI ENTE,
I DEMPLEADO, FECHAPEDI DO)
VALUES( @I DPED, @I DCLI , @I DEMP, @FECPED)

- - AGREGANDO UN REGI STRO A PEDI DOSDETA
I NSERT I NTO VENTAS. PEDI DOSDETA( I DPEDI DO, I DPRODUCTO,
PRECI OUNI DAD, CANTI DAD, DESCUENTO)
VALUES( @I DPED, @I DPROD, @PRE, @CANT, 0)

- - DESCONTANDO EL STOCK DE PRODUCTOS
UPDATE COMPRAS. PRODUCTOS SET UNI DADESENEXI STENCI A - =@CANT
WHERE I DPRODUCTO = @I DPROD
GO

Esta forma de procesar las operaciones de pedidos sera errnea, ya que cada
instruccin se ejecutara y confirmara de forma independiente, por lo que un error en
alguna de las operaciones dejara los datos errneos en la base de datos.

3.1.4.1 Transacciones implcitas y explicitas

Para agrupar varias sentencias TRANSACT SQL en una nica transaccin,
disponemos de los siguientes mtodos:
Transacciones explcitas: Cada transaccin se inicia explcitamente con la
instruccin BEGIN TRANSACTION y se termina explcitamente con una
instruccin COMMIT o ROLLBACK.
Transacciones implcitas: Se inicia automticamente una nueva transaccin
cuando se ejecuta una instruccin que realiza modificaciones en los datos, pero
cada transaccin se completa explcitamente con una instruccin COMMIT o
ROLLBACK.

Sintaxis para el control de errores:

BEGI N TRY
/ *Bl oque de i nst r ucci ones a val i dar */
END TRY

BEGI N CATCH
/ *Bl oque de i nst r ucci ones que se ej ecut an si ocur r e un ERROR*/
END CATCH

Sintaxis para el control de las transacciones

- - I ni ci o de t r ansacci n con nombr e
BEGI N TRAN Nombr eTr ansacci on
/ *Bl oque de i nst r ucci ones a ej ecut ar en l a Tr ansacci n*/
COMMI T TRAN Nombr eTr ansacci on- - Conf i r maci n de l a t r ansacci n.
ROLLBACK TRAN Nombr eTr ansacci on- - Rever si n de l a t r ansacci n.

En este ejemplo, definimos un procedimiento almacenado que agregue un registro a la
tabla de clientes. En este proceso, controlamos la operacin a travs de una
transaccin llamada tcliente, evaluamos el proceso con la variable @@ERROR

CREATE PROCEDURE USP_I NSERTACLI ENTE
@I D VARCHAR( 5) ,
@I DPAI S CHAR( 3) ,
@FONO VARCHAR( 15)
AS
- - I NI CI O DE LA TRANSACCI ON
BEGI N TRAN TCLI ENTE
I NSERT I NTO VENTAS. CLI ENTES( I DCLI ENTE, NOMCLI ENTE, DI RECCI ON,
I DPAI S, TELEFONO)
VALUES( @I D, @NOMBRE, @DI RECCI ON, @I DPAI S, @FONO)
GO

- - CONTROLAR EL PROCESO
I F @@ERROR = 0
BEGI N
- - CONFI RMACI N DE LA I NSERCI N
COMMI T TRAN TCLI ENTE
PRI NT ' CLI ENTE REGI STRADO'
END
112
ELSE
BEGI N
PRI NT @@ERROR
- - DESHACER LA I NSERCI N
ROLLBACK TRAN TCLI ENTE
END
GO

Para un mejor control de los errores, ejecutamos los procesos dentro del bloque TRY
CATCH, donde en caso que las operaciones hayan tenido xito, ejecutar COMMIT
TRAN, pero en caso de error CATCH, ejecutar ROLLBACK TRAN, tal como se
muestra en el siguiente ejercicio.

CREATE PROCEDURE USP_AGREGAPEDI DO
- - PARMETROS DE PEDI DOSCABE
@I DPED I NT,
@I DCLI VARCHAR( 5) ,
@I DEMP I NT,
@FECPED DATETI ME,
- - PARMETROS DE PEDI DOSDETA
@I DPROD I NT,
@PRE DECI MAL,
@CANT I NT
AS
- - I NI CI O DE LA TRANSACCI ON
BEGI N TRAN TPEDI DO

- - I NI CI O DEL CONTROL DE ERRORES
BEGI N TRY

- - AGREGANDO UN REGI STRO A PEDI DOSCABE
I NSERT I NTO
VENTAS. PEDI DOSCABE( I DPEDI DO, I DCLI ENTE, I DEMPLEADO, FECHAPEDI DO)
VALUES( @I DPED, @I DCLI , @I DEMP, @FECPED)

- - AGREGANDO UN REGI STRO A PEDI DOSDETA
I NSERT I NTO VENTAS. PEDI DOSDETA( I DPEDI DO, I DPRODUCTO,
PRECI OUNI DAD, CANTI DAD, DESCUENTO)
VALUES( @I DPED, @I DPROD, @PRE, @CANT, 0)

- - DESCONTANDO EL STOCK DE PRODUCTOS
UPDATE COMPRAS. PRODUCTOS SET UNI DADESENEXI STENCI A - =@CANT
WHERE I DPRODUCTO = @I DPROD

- - CONFI RMANDO LA ACTUALI ZACI ON
COMMI T TRAN TPEDI DO
PRI NT ' PEDI DO REGI STRADO'
END TRY
BEGI N CATCH
PRI NT @@ERROR
ROLLBACK TRAN TPEDI DO
END CATCH
GO

En el siguiente ejercicio, implementamos un procedimiento almacenado para realizar
el BACKUP a la tabla ClienteBAK. En este proceso, los registros de la tabla Clientes
se irn agregando (si no existen) o actualizando sus datos (si existe el registro) o
eliminar el registro en ClienteBAK si ste no existe en Clientes.

- - CREAR LA TABLA CLI ENTESBAK
CREATE TABLE VENTAS. CLI ENTESBAK (
I DCLI ENTE VARCHAR( 5) PRI MARY KEY,
NOMBRECLI ENTE VARCHAR( 40) NOT NULL,
DI RECCI ON VARCHAR( 60) NOT NULL,
I DPAI S CHAR( 3) ,
TELEFONO VARCHAR( 24) NOT NULL
)
GO

- - PROCEDI MI ENTO QUE REALI ZA BACK UP A LA TABLA CLI ENTES
CREATE PROCEDURE USP_CLI ENTEBAK
AS
BEGI N TRAN BK
BEGI N TRY
MERGE VENTAS. CLI ENTESBAK AS TARGET
USI NG VENTAS. CLI ENTES AS SOURCE
ON ( TARGET. I DCLI ENTE = SOURCE. I DCLI ENTE)
WHEN MATCHED AND TARGET. NOMBRECLI ENTE <> SOURCE. NOMCLI ENTE THEN
UPDATE SET TARGET. NOMBRECLI ENTE = SOURCE. NOMCLI ENTE,
TARGET. DI RECCI ON = SOURCE . DI RCLI ENTE, TARGET. I DPAI S =
114
SOURCE. I DPAI S, TARGET. TELEFONO=SOURCE. FONOCLI ENTE
I NSERT VALUES( SOURCE. I DCLI ENTE, SOURCE. NOMCLI ENTE,
SOURCE. DI RCLI ENTE, SOURCE. I DPAI S, SOURCE. FONOCLI ENTE)
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
PRI NT ' TRANSACCI ON COMPLETADA'
COMMI T TRAN BK
END TRY
BEGI N CATCH
PRI NT @@ERROR
ROLLBACK TRAN
END CATCH
GO

3.1.5 TRIGGERS O DISPARADORES

Los disparadores pueden usarse para imponer la integridad de referencia de los datos
en toda la base de datos. Los disparadores tambin permiten realizar cambios en
cascada en tablas relacionadas, imponer restricciones de columna ms complejas
que las permitidas por las reglas, compara los resultados de las modificaciones de
datos y llevar a cabo una accin resultante.

3.1.5.1 Definicin del disparador

Un disparador es un tipo especial de procedimiento almacenado que se ejecuta
cuando se insertan, eliminan o actualizan datos de una tabla especificada. Los
disparadores pueden ayuda a mantener la integridad de referencia de los datos
conservando la consistencia entre los datos relacionados lgicamente de distintas
tablas. Integridad de referencia significa que los valores de las llaves primarias y los
valores correspondientes de las llaves forneas deben coincidir de forma exacta.

La principal ventaja de los disparadores es que son automticos: funcionan cualquiera
sea el origen de la modificacin de los datos. Cada disparador es especfico de una o
ms operaciones de modificacin de datos, UPDATE, INSERT o DELETE. El
disparador se ejecuta una vez por cada instruccin.

3.1.5.2 Creacin de disparadores

Un disparador es un objeto de la base de datos. Cuando se crea un disparador, se
especifica la tabla y los comandos de modificacin de datos que deben disparar o
activar el disparador. Luego, se indica la accin o acciones que debe llevar a cabo un
disparador.

CREATE TRI GGER [ esquema. ] nombr e_t r i gger
ON { Tabl a | Vi st a }
{ FOR | AFTER | I NSTEAD OF }
{ [ I NSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WI TH APPEND ]
[ NOT FOR REPLI CATI ON ]
AS
sent enci a sql [ ; ]

A continuacin, se muestra un ejemplo sencillo. Este disparador imprime un mensaje
cada vez que alguien trata de insertar, eliminar o actualizar datos de la tabla Productos

CREATE TRI GGER TX_Pr oduct os
ON Compr as. Pr oduct os
FOR I NSERT, UPDATE, DELETE
AS
PRI NT ' Act ual i zaci on de l os r egi st r os de Pr oduct os'

Dependencia del TRIGGER o
Disparador en relacin a la tabla.
116
Para modificar el TRIGGER, se utiliza la siguiente sintaxis:

ALTER TRI GGER TX_PRODUCTOS
ON COMPRAS. PRODUCTOS
FOR I NSERT, UPDATE, DELETE
AS
PRI NT ' ACTUALI ZACI ON DE LOS REGI STROS DE PRODUCTOS'

Para borrar un TRIGGER, se utiliza la siguiente sintaxis:

DROP TRI GGER TX_PRODUCTOS

3.1.5.3 Funcionamiento de los Disparadores

A. Disparador de Insercin
Cuando se inserta una nueva fila en una tabla, SQL Server inserta los nuevos valores
en la tabla INSERTED el cual es una tabla del sistema. Est tabla toma la misma
estructura del cual se origin el TRIGGER, de tal manera que se pueda verificar los
datos y ante un error podra revertirse los cambios.

Cree un TRIGGER que permita insertar los datos de un Producto siempre y cuando la
descripcin o nombre del producto sea nico.

CREATE TRI GGER TX_PRODUCTO_I NSERTA
FOR I NSERT
AS
I F ( SELECT COUNT ( *) FROM I NSERTED, COMPRAS. PRODUCTOS
WHERE I NSERTED. NOMPRODUCTO = PRODUCTOS. NOMPRODUCTO) >1
BEGI N
ROLLBACK TRANSACTI ON
PRI NT ' LA DESCRI PCI ON DEL PRODUCTO SE ENCUENTRA REGI STRADO'
END
ELSE
PRI NT ' EL PRODUCTO FUE I NGRESADO EN LA BASE DE DATOS'
GO

En este ejemplo, verificamos el nmero de productos que tienen la misma descripcin
y de encontrarse ms de un registro de productos no se deber permitir ingresar los
datos del producto. Este disparador imprime un mensaje si la insercin se revierte y
otro si se acepta.

B. Disparador de Eliminacin

Cuando se elimina una fila de una tabla, SQL Server inserta los valores que fueron
eliminados en la tabla DELETED el cual es una tabla del sistema. Est tabla toma la
misma estructura del cual se origino el TRIGGER, de tal manera que se pueda
verificar los datos y ante un error podra revertirse los cambios. En este caso, la
reversin de los cambios significar restaurar los datos eliminados.

Cree un TRIGGER el cual permita eliminar Clientes los cuales no han registrado algn
pedido. De eliminarse algn Cliente que no cumpla con dicha condicin la operacin
no deber ejecutarse.

CREATE TRI GGER TX_ELI MI NA_ELI MI NA
ON VENTAS. CLI ENTES
FOR DELETE
AS
I F EXI STS ( SELECT * FROM VENTAS. PEDI DOSCABE
WHERE PEDI DOSCABE. I DCLI ENTE = ( SELECT I DCLI ENTE FROM DELETED) )
BEGI N
PRI NT ' EL CLI ENTE TI ENE REGI STRADO POR LO MENOS 1 PEDI DOS'
END

En este ejemplo, verificamos si el cliente tiene pedidos registrados, de ser as la
operacin deber ser cancelada.

C. Disparador de Actualizacin

Cuando se actualiza una fila de una tabla, SQL Server inserta los valores que antiguos
en la tabla DELETED y los nuevos valores los inserta en la tabla INSERTED. Usando
118
estas dos tablas se podr verificar los datos y ante un error podran revertirse los
cambios.

Cree un TRIGGER que valide el precio unitario y su Stock de un producto, donde
dichos datos sean mayores a cero.

CREATE TRI GGER TX_PRODUCTO_ACTUALI ZA
FOR UPDATE
AS
I F ( SELECT PRECI OUNI DAD FROM I NSERTED) <=0 OR
( SELECT UNI DADESENEXI STENCI A FROM I NSERTED) <=0
BEGI N
PRI NT ' EL PRECI O O UNI DADESENEXI STENCI A DEBEN SER MAYOR A CERO'
END

Cree un TRIGGER que bloquee actualizar el id del producto en el proceso de
actualizacin.

CREATE TRI GGER TX_PRODUCTO_ACTUALI ZA_I D
FOR UPDATE
AS
I F UPDATE( I DPRODUCTO)
BEGI N
PRI NT ' NO SE PUEDE ACTUALI ZAR EL I D DEL PRODUCTO'
END

3.1.5.4 Uso de INSTEAD OF

Los desencadenadores INSTEAD OF pasan por alto las acciones estndar de la
instruccin de desencadenamiento: INSERT, UPDATE o DELETE. Se puede definir un
desencadenador INSTEAD OF para realizar comprobacin de errores o valores en una
o ms columnas y, a continuacin, realizar acciones adicionales antes de insertar el
registro.

Por ejemplo, cuando el valor que se actualiza en un campo de tarifa de una hora de
trabajo de una tabla de planilla excede un valor especfico, se puede definir un
desencadenador para producir un error y revertir la transaccin o insertar un nuevo
registro en un registro de auditora antes de insertar el registro en la tabla de planilla.

A. INSTEAD OF INSERT
Se pueden definir desencadenadores INSTEAD OF INSERT en una vista o tabla para
reemplazar la accin estndar de la instruccin INSERT. Normalmente, el
desencadenador INSTEAD OF INSERT se define en una vista para insertar datos en
una o ms tablas base.

Las columnas de la lista de seleccin de la vista pueden o no admitir valores NULL. Si
una columna de la vista no admite valores NULL, una instruccin INSERT debe
proporcionar los valores para la columna.

- - CREAR UNA VI STA QUE LI STE LAS COLUMNAS DE CLI ENTES.
CREATE VI EWI NSTEADVI EW
AS
SELECT I DCLI ENTE,
NOMCLI ENTE,
DI RCLI ENTE,
I DPAI S,
FONOCLI ENTE
GO

- - CREAR UN TRI GGER I NSTEAD OF I NSERT PARA LA VI STA.
CREATE TRI GGER I NSTEADTRI GGER ON I NSTEADVI EW
I NSTEAD OF I NSERT
AS
BEGI N
I NSERT I NTO VENTAS. CLI ENTESBAK
SELECT I DCLI ENTE, NOMCLI ENTE, DI RCLI ENTE, I DPAI S, FONOCLI ENTE
FROM I NSERTED
END
GO

120
B. INSTEAD OF UPDATE
Se pueden definir desencadenadores INSTEAD OF UPDATE en una vista o tabla para
reemplazar la accin estndar de la instruccin UPDATE. Normalmente, el
desencadenador INSTEAD OF UPDATE se define en una vista para modificar datos
en una o ms tablas. Las instrucciones UPDATE que hacen referencia a vistas que
contienen desencadenadores INSTEAD OF UPDATE deben suministrar valores para
todas las columnas que no admiten valores NULL a las que hace referencia la clusula
SET

C. INSTEAD OF DELETE
Se pueden definir desencadenadores INSTEAD OF DELETE en una vista o una tabla
para reemplazar la accin estndar de la instruccin DELETE. Normalmente, el
desencadenador INSTEAD OF DELETE se define en una vista para modificar datos en
una o ms tablas. Las instrucciones DELETE no especifican modificaciones de los
valores de datos existentes. Las instrucciones DELETE slo especifican las filas que
se van a eliminar. La tabla inserted pasada a un desencadenador DELETE siempre
est vaca. La tabla deleted enviada a un desencadenador DELETE contiene una
imagen de las filas en el estado que tenan antes de emitir la instruccin DELETE

3.1.5.5 Restricciones de los disparadores

A continuacin, se describen algunas limitaciones o restricciones impuestas a los
disparadores por SQL Server:
Una tabla puede tener un mximo de tres disparadores: uno de actualizacin, uno de
insercin y uno de eliminacin.
Cada disparador puede aplicarse a una sola tabla. Sin embargo, un mismo disparador
se puede aplicar a las tres acciones del usuario: UPDATE, INSERT y DELETE.
No se puede crear un disparador en una vista ni en una tabla temporal, aunque los
disparadores pueden hacer referencia a las vistas o tablas temporales.
Los disparadores no se permiten en las tablas del sistema. Aunque no aparece ningn
mensaje de error su crea un disparador en una tabla del sistema, el disparador no se
utilizar.

Resumen
Las funciones definidas por el usuario, en tiempo de ejecucin de lenguaje
TRANSACT-SQL o comn (CLR), acepta parmetros, realiza una accin, como un
clculo complejo, y devuelve el resultado de esa accin como un valor. El valor de
retorno puede ser un escalar (nico) valor o una tabla.
Las funciones escalares son aquellas funciones donde retornan un valor nico: tipo
de datos como int, Money, varchar, real, etc. Pueden ser utilizadas en cualquier
lugar, incluso, incorporada dentro de las sentencias SQL. Las funciones de tabla en
lnea son las funciones que devuelven la salida de una simple declaracin
SELECT. La salida se puede utilizar adentro de J OINS o querys como si fuera una
tabla de estndar. Las funciones de tabla multisentencias son similares a los
procedimientos almacenados excepto que vuelven una tabla
Los procedimientos almacenados son grupos formados por instrucciones SQL y el
lenguaje de control de flujo. Cuando se ejecuta un procedimiento, se prepara un
plan de ejecucin para que la subsiguiente ejecucin sea muy rpida. Los
procedimientos almacenados pueden:
1. Incluir parmetros
2. Llamar a otros procedimientos
3. Devolver un valor de estado a un procedimiento de llamada o lote para
indicar el xito o el fracaso del mismo y la razn de dicho fallo.
4. Devolver valores de parmetros a un procedimiento de llamada o lote
5. Ejecutarse en SQL Server remotos
Un procedimiento almacenado se comunica con el programa que lo llama
mediante sus parmetros. Cuando un programa ejecuta un procedimiento
almacenado, es posible pasarle valores mediante los parmetros del
procedimiento. Estos valores se pueden utilizar como variables estndar en el
lenguaje de programacin TRANSACT-SQL. El procedimiento almacenado
tambin puede devolver valores al programa que lo llama mediante parmetros
OUTPUT. Un procedimiento almacenado puede tener hasta 2.100 parmetros,
cada uno de ellos con un nombre, un tipo de datos, una direccin y un valor
predeterminado
122
Los procedimientos almacenados pueden aceptar datos como parmetros de
entrada y pueden devolver datos como parmetros de salida, conjuntos de
resultados o valores de retorno. Adicionalmente, los procedimientos almacenados
pueden ejecutar sentencias de actualizacin de datos: INSERT, UPDATE,
DELETE
Una transaccin es un conjunto de operaciones TRANSACT SQL que se ejecutan
como un nico bloque, es decir, si falla una operacin TRANSACT SQL fallan
todas. Si una transaccin tiene xito, todas las modificaciones de los datos
realizadas durante la transaccin se confirman y se convierten en una parte
permanente de la base de datos. Si una transaccin encuentra errores y debe
cancelarse o revertirse, se borran todas las modificaciones de los datos.
Los disparadores pueden usarse para imponer la integridad de referencia de los
datos en toda la base de datos. Los disparadores tambin permiten realizar
cambios en cascada en tablas relacionadas, imponer restricciones de columna
ms complejas que las permitidas por las reglas, compara los resultados de las
modificaciones de datos y llevar a cabo una accin resultante.
Cuando se inserta una nueva fila en una tabla, SQL Server inserta los nuevos
valores en la tabla INSERTED el cual es una tabla del sistema. Est tabla toma la
misma estructura del cual se origin el TRIGGER, de tal manera que se pueda
verificar los datos y ante un error podra revertirse los cambios
Cuando se elimina una fila de una tabla, SQL Server inserta los valores que fueron
eliminados en la tabla DELETED el cual es una tabla del sistema. Est tabla toma
la misma estructura del cual se origino el TRIGGER, de tal manera que se pueda
verificar los datos y ante un error podra revertirse los cambios. En este caso la
reversin de los cambios significar restaurar los datos eliminados.
Cuando se actualiza una fila de una tabla, SQL Server inserta los valores que
antiguos en la tabla DELETED y los nuevos valores los inserta en la tabla
INSERTED. Usando estas dos tablas se podr verificar los datos y ante un error
podran revertirse los cambios

pginas:
http://www.devjoker.com/contenidos/catss/292/Transacciones-en-Transact-SQL.aspx
Aqu hallar los conceptos de Transacciones en TRANSACT SQL.
http://www.sqlmax.com/func2.asp

En esta pgina, hallar los conceptos funciones


Aqu hallar los conceptos de manejo de parmetros en procedimientos
almacenados

124

MANEJO DE DATOS XML EN SQL SERVER
Al trmino de la unidad, el alumno, haciendo uso de los comandos de consulta y
actualizacin de datos, realizamos operacin de recuperacin de datos en formato
XML.

TEMARIO
1.1. Introduccin
1.1.1. Porque utilizar bases de datos relacionales para datos XML
1.1.2. Tipos de datos XML
1.1.3. Integracin de relaciones y XML
1.1.4. FOR XML y mejoras OPENXML
1.2. Procesamiento XML en SQL SERVER
1.2.1. Tipos de datos XML
1.2.2. Almacenamiento de datos XML
1.2.3. Recuperacin de datos de tipo XML
1.2.3.1. Usar modo RAW
1.2.3.2. Usar modo AUTO
1.2.3.3. Usar modo EXPLICIT
1.2.3.4. Usar modo PATH
1.2.4. Recuperacin de datos con OPENXML

Los alumnos actualizan datos con formato XML.
Los alumnos recuperan los datos de una base de datos relacional en formato XML.

UNIDAD DE
APRENDIZAJ E
4
126
4.1 INTRODUCCION

Extensible Markup Language (XML) ha sido ampliamente adoptado como un formato
independiente de la plataforma de representacin de datos. Es til para el intercambio
de informacin entre los dbilmente acoplados, sistemas dispares, como en el negocio
a negocio (B2B) y flujos de trabajo. El intercambio de datos ha sido un gran impulsor
de las tecnologas XML.

XML es cada vez ms presente en las aplicaciones empresariales que se utilizan para
el modelado de datos semi-estructurados y no estructurados. Una de estas
aplicaciones es la gestin de documentos. Documentos (mensajes de correo
electrnico, por ejemplo) son semi-estructuradas por la naturaleza.

Si los documentos se almacenan en un servidor de base de datos como XML,
potentes aplicaciones pueden ser desarrolladas, tales como:
Las aplicaciones que recuperar los documentos en base a su contenido.
Las aplicaciones que consulta por el contenido parcial, como la bsqueda de la
seccin cuyo ttulo contiene la palabra "fondo".
Las aplicaciones que los documentos agregados.

Estos escenarios se estn convirtiendo en posibles con el aumento en el desarrollo y
la disponibilidad de las aplicaciones que generan y consumen XML. Por ejemplo, el
Microsoft Office 2008 System permite a los usuarios generar Microsoft Word, Excel,
Visio, InfoPath y documentos de formato XML.

4.1.1 Por qu utilizar bases de datos relacionales para los datos de
XML?

El almacenamiento de datos XML en una base de datos relacional proporciona
beneficios en las reas de gestin de datos y el procesamiento de consultas.

Microsoft SQL Server proporciona una consulta de gran alcance y las capacidades de
modificacin de datos en datos relacionales. En SQL Server 2008, estas capacidades
se extienden a consultar y modificar datos XML. Esto permite a su empresa para
aprovechar las inversiones hechas en versiones anteriores, como las inversiones en
128
las reas de costos basado en optimizacin y almacenamiento de datos. Por ejemplo,
las tcnicas de indexacin en bases de datos relacionales son bien conocidas. Estos
se han extendido a la indexacin de XML para que las consultas se puedan optimizar
el uso de costos basado en las decisiones.

Los datos XML pueden interactuar con datos relacionales y aplicaciones SQL. Esto
significa que XML puede ser introducido en el sistema segn las necesidades de
modelado de datos se presentan sin interrumpir las aplicaciones existentes. El
servidor de base de datos tambin proporciona una funcionalidad administrativa para
la gestin de datos XML (por ejemplo, BACKUP, recuperacin y replicacin).

La compatibilidad nativa con XML en SQL Server 2008 es necesario para hacer frente
a aumentar el uso de XML. Beneficios de la empresa de desarrollo de aplicaciones de
la compatibilidad con XML en SQL Server 2008.

En las secciones siguientes, se ofrece una visin general de la compatibilidad con
XML en SQL Server 2005 y 2008, describe algunos de los escenarios de motivacin
para el uso de XML, y analizar en detalle el lado del servidor y de cliente XML
conjuntos de caractersticas.

4.1.2 Tipos de datos XML

El modelo de datos XML tiene caractersticas que lo hacen muy difcil por no decir
prcticamente imposible asignar al modelo de datos relacional. De datos XML tiene
una estructura jerrquica que puede ser recursivo, bases de datos relacionales ofrecen
poco apoyo para los datos jerrquicos (modelados como relaciones de clave externa).

El orden del documento es una propiedad inherente de instancias XML y debe ser
preservada en los resultados de la consulta. Esto est en contraste con los datos
relacionales, que no est ordenado, el orden debe ser ejecutada con columnas
adicionales pedidos. Volver a montar el resultado en la consulta es costoso para los
realistas esquemas XML que se descomponen los datos XML en un gran nmero de
tablas.

SQL Server 2008 introduce un tipo de datos nativo llamado XML. Un usuario puede
crear una tabla que tiene una o ms columnas de tipo XML, adems de columnas
relacionales. Las variables y los parmetros de XML tambin son permitidos. Los
valores XML se almacenan en un formato interno como objetos binarios grandes
(BLOB) para apoyar a las caractersticas del modelo XML, tales como el orden del
documento y las estructuras recursivas, con mayor fidelidad.

SQL Server 2008 ofrece colecciones de esquemas XML como una manera de manejar
esquemas XML del W3C como metadatos. Un tipo de datos XML se puede asociar
con una coleccin de esquemas XML para hacer cumplir las restricciones de esquema
en las instancias XML. Cuando los datos XML se asocian con una coleccin de
esquemas XML, se llama XML con tipo, de lo contrario se llama XML sin tipo. Con tipo
y XML sin tipo se alojan en un marco nico, el modelo de datos XML se conserva, y el
procesamiento de consultas aplica la semntica XML. La infraestructura relacional
subyacente se utiliza ampliamente para este propsito. Es compatible con la
interoperabilidad entre datos relacionales y XML, con objeto de alcanzar la adopcin
ms generalizada de las caractersticas XML.

4.1.2.1 Integracin de Relacionales y XML

Los usuarios pueden almacenar datos relacionales y XML dentro de la misma base de
datos. En pocas palabras, el motor de base de datos sabe hacer honor a la modelo de
datos XML, adems del modelo de datos relacional. De datos relacionales y
aplicaciones de SQL, siguen comportndose correctamente en la actualizacin a SQL
Server 2008. De datos XML que residen en los archivos y las columnas de texto o la
imagen se puede mover en las columnas de tipo de datos XML en el servidor. La
columna XML se puede indexar, consultar y modificar con los mtodos de tipo de
datos XML.

La base de datos relacional mejora la infraestructura existente y los componentes del
motor como el motor de almacenamiento y el procesador de consultas para
procesamiento de XML. Por ejemplo, crear ndices XML B
+
rboles y los planes de
consulta se pueden ver en la salida del plan de presentacin. Puesto que la
funcionalidad de gestin de datos, tales como copia de seguridad / restauracin y
replicacin, se integra en el marco relacional, esta funcionalidad est disponible en los
datos XML. Adems, las nuevas caractersticas de datos de gestin, como reflejo de
base y el aislamiento de instantneas, el trabajo con el tipo de datos XML para
proporcionar una experiencia de usuario sin fisuras.
130

Los datos estructurados se almacenan en tablas y columnas relacionales. El tipo de
datos XML es una eleccin adecuada de los datos semi-estructurados y marcado con
XML cuando la aplicacin necesita para llevar a cabo de grano fino de la consulta y
modificacin de los datos.

4.1.3 FOR XML y mejoras OpenXML

La funcionalidad XML se ha mejorado de varias maneras. Se trabaja sobre casos tipo
de datos XML y otros nuevos tipos de SQL, tales como [n] varchar (max).

La nueva directiva TYPE genera una instancia de tipo de datos XML que se pueden
asignar a una columna de XML, una variable o parmetro, o consultar utilizando los
mtodos de tipo de datos XML. Esto permite el anidamiento de SELECT... FOR
instrucciones de tipo XML.

El modo PATH permite a los usuarios especificar la ruta en el rbol XML donde el valor
de una columna debe aparecer y junto con el mencionado anidacin. Es ms cmodo
para escribir que para EXPLCITA XML.

El XSINIL Directiva, que se utiliza en conjuncin con elementos, mapas NULL a un
elemento con el atributo xsi: nil = " true" . Adems, la Directiva permite a la nueva raz
un nodo raz que se especificarn en todos los modos de FOR XML. La directiva
XMLSCHEMA nueva genera un esquema XSD en lnea. FOR XML en SQL Server
2008 tambin permite a los usuarios especificar los nombres de elementos para
sustituir a la <row> por defecto en modo FOR XML RAW.

Mejoras funcionales OpenXML consisten en la posibilidad de aceptar el tipo de datos
XML en sp_preparedocument, y para generar XML y nuevas columnas de tipo SQL
del conjunto de filas.

4.2 PROCESAMIENTO XML EN SQL SERVER

SQL Server permite proporcionar una base de datos que puede almacenar datos
relacionales y XML.

4.2.1 Tipo de datos XML

4.2.1.1 XML sin tipo
El tipo de datos XML en SQL Server implementa la norma ISO estndar SQL-2003
tipo de datos XML. Como tal, puede almacenar no slo bien formado XML 1.0
documentos, sino tambin fragmentos de XML llamado contento con los nodos de
texto y un nmero arbitrario de elementos de nivel superior.

Comprueba la buena formacin de los datos se lleva a cabo, que no requiere el tipo de
datos XML en obligarse a los esquemas XML. Datos que no estn bien formados son
rechazados. XML sin tipo es til cuando el esquema no se sabe a priori para que una
solucin basada en la cartografa no sea posible. Tambin, es til cuando el esquema
se conoce, pero la asignacin a un modelo de datos relacional es muy complejo y
difcil de mantener, o existen varios esquemas y estn obligados a fines de los datos
basados en los requerimientos externos.

La siguiente sentencia crea una tabla llamada SOLICITUD con una clave entera y una
columna XML sin tipo llamada detalle

USE NEGOCI OS2011
GO

CREATE TABLE CREDI TO(
I D I NT PRI MARY KEY,
DETALLE XML NOT NULL)

4.2.1.2 XML con tipo
Si se han definido esquemas XML que describe los datos XML, se puede asociar la
coleccin de esquemas XML con la columna de XML para obtener XML con tipo. Los
esquemas XML se utilizan para validar los datos, realizar comprobaciones de tipo ms
preciso que el XML sin tipo durante la compilacin de las declaraciones de
132
modificacin y consulta de datos y optimizar el almacenamiento y el procesamiento de
consultas.

Las columnas XML, los parmetros y las variables pueden almacenar documentos
XML o contenido, que se puede especificar como una opcin (documento o contenido,
respectivamente, con un contenido por defecto) en el momento de la declaracin.
Adems, debe proporcionar la coleccin de esquemas XML.

Especificar el documento si cada instancia XML tiene exactamente un elemento de
nivel superior, de lo contrario, usar el contenido. El compilador de consultas utiliza el
indicador DOCUMENTO en los controles de tipo SINGLETON para inferir elementos
de nivel superior.

La siguiente sentencia crea una tabla llamada XmlCredito con un documento XML
con tipo columna con myCollection. La columna XML se especifica tambin que
aceptar fragmentos de XML, no slo los documentos XML.

USE NEGOCI OS2011
GO

CREATE TABLE XMLCREDI TO (
I D I NT PRI MARY KEY,
SOLI CI TUD XML( CONTENT MYCOLLECTI ON) )

4.2.2 Almacenamiento de datos XML

Puede proporcionar un valor para una columna XML a travs de un parmetro o una
variable de varias maneras:
Como tipo binario o de SQL que se convierte implcitamente en el tipo de
datos XML.
A travs del contenido de un archivo.
Como la salida del mecanismo de publicacin XML FOR XML con la directiva
TYPE, que genera una instancia de datos de tipo XML.

El valor proporcionado se comprueba la buena formacin. Una columna XML por
defecto permite a ambos documentos y fragmentos XML para ser almacenados. Si los
datos no pasan la comprobacin de buena formacin, se rechaza con un mensaje de
error apropiado.

Para un dato XML con tipo, el valor proporcionado se comprueba la conformidad con
los esquemas XML que se registran en la coleccin de esquemas XML que est
escribiendo la columna XML. La instancia XML se rechaza si no est validacin.
Adems, la bandera DOCUMENTO en XML restringe los valores aceptados para
documentos XML slo mientras que el contenido permite a ambos documentos XML y
contenido para su entrega.

4.2.2.1 Insertar datos en una columna XML sin tipo

La siguiente declaracin se inserta una nueva fila en la tabla denominada CREDITOS
con el valor 1 para la columna de clave primaria y una instancia de <credito> para la
columna XML. Los datos <credito>, suministrado como una cadena, se convierte
implcitamente en el tipo de datos XML y comprobar la buena formacin durante la
insercin.

USE NEGOCI OS2011
GO

I NSERT I NTO CREDI TO VALUES
( 1,
' <CREDI TO FECHA=" 10- 08- 2011" >
<CLI ENTE>
<NOMBRE>MI CHAEL</ NOMBRE>
<APELLI DO>HOWARD</ APELLI DO>
<MONTO>1200</ MONTO>
</ CLI ENTE>
<CLI ENTE>
<NOMBRE>DAVI D</ NOMBRE>
<APELLI DO>LEBLANC</ APELLI DO>
</ CLI ENTE>
<CLI ENTE>
<NOMBRE>DAVI D</ NOMBRE>
<APELLI DO>LEBLANC</ APELLI DO>
</ CLI ENTE>
134
</ CREDI TO>' ) ,
( 2,
' <CREDI TO FECHA=" 12- 08- 2011" >
<CLI ENTE>
<NOMBRE>MANUEL</ NOMBRE>
<APELLI DO>LUNA</ APELLI DO>
</ CLI ENTE>
<CLI ENTE>
<NOMBRE>J ORGE</ NOMBRE>
<APELLI DO>LAURA</ APELLI DO>
</ CLI ENTE>
<CLI ENTE>
<NOMBRE>I NES</ NOMBRE>
<APELLI DO>RI OS</ APELLI DO>
</ CLI ENTE>
</ CREDI TO>' )

Para listar los registros de la tabla, ejecutamos la sintaxis siguiente, donde el proceso
listar los registros y la estructura XML insertado. Para abrir un archivo, hacer un click
al link, tal como se muestra.

Ms adelante aprenderemos a consultar los datos de una estructura XML.

4.2.2.2 Insertar datos en la columna XML a travs de un archivo de tipo XML

La instruccin INSERT en el segmento de cdigo siguiente lee el contenido del archivo
C:\temp\credito.xml como un BLOB mediante el uso de OPENROWSET. Una nueva
fila se insertar en la tabla denominada CREDITOS con un valor de 3 para la clave
principal y el contenido del archivo en la columna XML llamada detalle.

USE NEGOCI OS2011
GO

I NSERT I NTO CREDI TO
SELECT 3, DETALLE
FROM ( SELECT *
FROM OPENROWSET
( BULK ' C: \ TEMP\ CREDI TOS. XML' ,
SI NGLE_BLOB)
AS DETALLE) AS R( DETALLE)
GO

4.2.3 Recuperando datos de tipo XML

Una consulta SELECT devuelve los resultados como un conjunto de filas.
Opcionalmente, se pueden recuperar resultados formales de una consulta SQL como
XML especificando la clusula FOR XML en la consulta. La clusula FOR XML puede
usarse en consultas de nivel superior y en subconsultas.

A continuacin se muestra la sintaxis bsica de la clusula FOR XML

[ FOR { BROWSE | <XML> } ]
<XML> : : =
XML
{
{ RAW[ ( ' El ement Name' ) ] | AUTO }
[
<CommonDi r ect i ves>
[ , { XMLDATA | XMLSCHEMA [ ( ' Tar get NameSpaceURI ' ) ] } ]
[ , ELEMENTS [ XSI NI L | ABSENT ]
]
| EXPLI CI T
[
[ , XMLDATA ]
]
| PATH [ ( ' El ement Name' ) ]
136
[
[ , ELEMENTS [ XSI NI L | ABSENT ] ]
]
}
<CommonDi r ect i ves> : : =
[ , BI NARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ( ' Root Name' ) ] ]

Argumentos Descripcin
RAW[ ( ' ElementName' ) ] Obtiene el resultado de la consulta y transforma cada
fila del conjunto de resultados en un elemento XML
con un identificador genrico <row />como etiqueta
del elemento. Opcionalmente, puede especificar un
nombre para el elemento de fila cuando se utiliza esta
directiva. El XML resultante utilizar el ElementName
especificado como el elemento de fila generado para
cada fila.
AUTO Devuelve los resultados de la consulta en un rbol
anidado XML sencillo. Cada tabla en la clusula FROM
de la que al menos se presenta una columna en la
clusula SELECT se representa como un elemento
XML. A las columnas presentadas en la clusula
SELECT se les asignan los atributos de elemento
apropiados.
EXPLI CI T Especifica que la forma del rbol XML resultante est
definida explcitamente. Con este modo, es necesario
escribir las consultas de una cierta manera, de modo
que se pueda especificar explcitamente informacin
adicional acerca de la anidacin deseada.
PATH Facilita la mezcla de elementos y atributos, as como la
especificacin de anidacin adicional para representar
propiedades complejas. Puede utilizar consultas FOR
XML de modo EXPLICIT para construir XML a partir de
un conjunto de filas, pero el modo PATH supone una
alternativa ms simple a las consultas de modo
EXPLICIT potencialmente complicadas. De forma
predeterminada, el modo PATH genera un contenedor
de elementos <row> para cada fila del conjunto de
resultados. Tambin se puede especificar un nombre
de elemento. En este caso, el nombre especificado se
utilizar como nombre del elemento contenedor. Si se
proporciona una cadena vaca (FOR XML PATH ('')),
no se generar ningn elemento contenedor.
XMLDATA Especifica que se debe devolver un esquema XDR
(XML-Data Reduced) insertado. El esquema se
antepone al documento como un esquema insertado.
XMLSCHEMA Devuelve un esquema XML W3C (XSD) insertado.
Opcionalmente, puede especificar un URI de espacio
de nombres de destino al especificar esta directiva. De
este modo, se devuelve el espacio de nombres
especificado en el esquema.
ELEMENTS Si se especifica la opcin ELEMENTS, las columnas
se devuelven como subelementos. Sin embargo, se
les asignan atributos XML. Esta opcin solo se admite
en los modos RAW, AUTO y PATH. Tambin puede
especificar XSINIL o ABSENT cuando utilice esta
directiva. XSINIL especifica que se puede crear un
elemento con un atributo XSI:NIL establecido en True
para los valores de columna NULL. De forma
predeterminada, o cuando se especifica ABSENT junto
con ELEMENTS, no se crea ningn elemento para los
valores NULL.
BI NARY BASE64 Si se especifica la opcin BINARY Base64, todos los
datos binarios que devuelve la consulta se representan
en formato codificado base64. Para recuperar datos
binarios mediante el modo RAW y EXPLICIT, se debe
especificar esta opcin. En modo AUTO, de forma
predeterminada, se devuelven datos binarios como
una referencia.

TYPE Especifica que la consulta devuelve los resultados
como el tipo XML.
ROOT [ ( ' RootName' ) ] Especifica que se puede agregar un solo elemento de
138
nivel superior al XML resultante. Tambin se puede
especificar el nombre del elemento raz que se
generar. El valor predeterminado es "root".

La clusula FOR XML de nivel superior slo puede usarse en la instruccin SELECT.
En el caso de las subconsultas, FOR XML puede usarse en las instrucciones INSERT,
UPDATE y DELETE. Tambin puede usarse en instrucciones de asignacin.

En una clusula FOR XML se especifica uno de estos modos:
RAW
AUTO
EXPLICIT
PATH

4.2.3.1 Usar Modo RAW

El modo RAW transforma cada fila del conjunto de resultados de la consulta en un
elemento XML que tiene el identificador genrico <row>o el nombre del elemento, que
se proporciona de manera opcional. De manera predeterminada, cada valor de
columna del conjunto de filas que no es NULL se asigna a un atributo del elemento
<row>. Si se agrega la directiva ELEMENTS a la clusula FOR XML, cada valor de
columna se asigna a un subelemento del elemento <row>. Opcionalmente, junto con la
directiva ELEMENTS se puede especificar la opcin XSINIL para asignar los valores
de columna NULL del conjunto de resultados a un elemento que tenga el atributo,
xsi:nil="true".

El siguiente ejemplo nos permite recuperar un fragmento XML que contienen los datos
utilizando la clusula FOR XML en modo RAW

USE NEGOCI OS2011
GO

SELECT I DPEDI DO,
FECHAPEDI DO,
C. NOMCLI ENTE AS CLI ENTE,
C. DI RCLI ENTE AS DI RECCI ON
FROM VENTAS. CLI ENTES C J OI N VENTAS. PEDI DOSCABE P
ORDER BY I DPEDI DO
FOR XML RAW
GO

La consulta produce un fragmento XML
que contiene elementos <row>. Al abrir el
fragmento se lista todas las filas de la
consulta en formato XML, tal como se
muestra en la figura siguiente

El siguiente ejemplo muestra cmo recuperar la data como elementos en lugar de
atributos especificando la opcin ELEMENTS

USE NEGOCI OS2011
GO

140
SELECT I DPEDI DO,
FECHAPEDI DO,
C. NOMCLI ENTE AS CLI ENTE,
C. DI RCLI ENTE AS DI RECCI ON
ORDER BY I DPEDI DO
FOR XML RAW, ELEMENTS
GO

La consulta produce el fragmento XML
en el formato que se muestra.

El formato XML, los campos estn
distribuidos como elementos de <row>.

4.2.3.2 Usar Modo AUTO

El modo AUTO devuelve los resultados de la consulta como elementos XML anidados.
Esto no ofrece un gran control sobre la forma del XML generado a partir del resultado
de una consulta. Las consultas en modo AUTO son tiles si desea generar jerarquas
sencillas. Sin embargo, Usar el modo EXPLICIT y Usar el modo PATH ofrecen mayor
control y flexibilidad a la hora de decidir la forma del XML procedente del resultado de
una consulta.

Cada tabla de la clusula FROM, de la que al menos se presenta una columna en la
clusula SELECT, se representa como un elemento XML. Las columnas que se
incluyen en la clusula SELECT se asignan a atributos o subelementos, si se
especifica la opcin ELEMENTS en la clusula FOR XML.

La jerarqua XML, anidamiento de los elementos, del XML resultante est basada en el
orden de las tablas identificadas por las columnas especificadas en la clusula
SELECT. Por tanto, el orden en que se especifican los nombres de columna en la
clusula SELECT es importante. La primera, la tabla situada ms a la izquierda que se
identifica, constituye el elemento superior del documento XML resultante. La segunda
tabla situada ms a la izquierda, identificada por las columnas de la instruccin
SELECT, constituye un subelemento del elemento superior, etc.

En el siguiente ejemplo, muestra cmo utilizar la consulta de modo AUTO que retorne
un fragmento XML que contiene la lista de pedidos por cliente

SELECT C. NOMCLI ENTE AS CLI ENTE,
C. DI RCLI ENTE AS DI RECCI ON,
I DPEDI DO,
FECHAPEDI DO
ORDER BY NOMCLI ENTE
FOR XML AUTO
GO

142

En el fragmento XML, agrupa
los pedidos por cada cliente,
donde los atributos representan
el nombre del cliente y su
direccin; sus elementos son
los datos de cada pedido.

En el siguiente ejemplo, vamos a recuperar los datos como elementos en lugar de
atributos especificando con la opcin ELEMENTS.

SELECT C. NOMCLI ENTE AS CLI ENTE,
C. DI RCLI ENTE AS DI RECCI ON,
I DPEDI DO,
FECHAPEDI DO
ORDER BY NOMCLI ENTE
FOR XML AUTO, ELEMENTS
GO

El fragmento XML, visualizamos los
datos como elementos, donde
agrupamos por Cliente, y por cada
cliente listamos los pedidos.

4.2.3.3 Usar Modo EXPLICIT

El modo EXPLICIT concede un mayor control de la forma del XML. Es posible mezclar
atributos y elementos con total libertad para decidir la forma del XML. Requiere un
formato especfico para el conjunto de filas resultante generado debido a la ejecucin
de la consulta. Despus, el formato del conjunto de filas se asigna a una forma de
XML.

El modo EXPLICIT transforma en un documento XML el conjunto de filas resultante de
la ejecucin de la consulta. Para que el modo EXPLICIT, pueda generar el documento
XML, el conjunto de filas debe ajustarse a un determinado formato. Por ello, es
necesario escribir la consulta SELECT para generar el conjunto de filas, la tabla
universal, con un formato especfico que permita a la lgica del procesamiento
generar el XML deseado.

En primer lugar, la consulta debe crear las dos columnas de metadatos siguientes:
La primera columna debe proporcionar el nmero de etiqueta, el tipo de entero,
del elemento actual, y el nombre de la columna debe ser Tag. La consulta debe
proporcionar un nmero de etiqueta nico para cada elemento que se vaya a
construir a partir del conjunto de filas.
La segunda columna debe proporcionar un nmero de etiqueta del elemento
primario, y el nombre de la columna debe ser Parent. De este modo, las
columnas Tag y Parent ofrecen informacin sobre la jerarqua.

Los valores de estas columnas de metadatos, junto con la informacin de los nombres
de columna, se usan para generar el XML deseado. Tenga en cuenta que la consulta
debe proporcionar los nombres de columna de una manera determinada. Observe
tambin que un valor 0 NULL en la columna Parent indica que el elemento
correspondiente no tiene uno primario. El elemento se agrega al XML como elemento
de nivel superior.

En el siguiente ejemplo, vamos a listar los datos de los clientes, donde el campo id y
nombre sern atributos, y el campo direccin y telfono sern elementos.

144
SELECT 1 AS TAG,
NULL AS PARENT,
I DCLI ENTE AS [ CLI ENTE! 1! CODI GO] ,
NOMCLI ENTE AS [ CLI ENTE! 1! NOMBRE] ,
DI RCLI ENTE AS [ CLI ENTE! 1! DI RECCI ON! ELEMENT] ,
TELEFONO AS [ CLI ENTE! 1! TELEFONO! ELEMENT]
FOR XML EXPLI CI T
GO

El fragmento XML lista las filas de la
tabla de clientes, donde el cdigo y el
nombre son atributos; y los campos
direccin y telfono son elementos.

4.2.3.4 Usar Modo PATH

El modo PATH facilita la combinacin de elementos y atributos. Tambin, facilita la
especificacin de anidacin adicional para representar propiedades complejas. Puede
utilizar consultas de modo FOR XML EXPLICIT para generar XML a partir de un
conjunto de filas, pero el modo PATH supone una alternativa ms sencilla a las
consultas de modo EXPLICIT potencialmente complicadas. El modo PATH, junto con
la posibilidad de escribir consultas FOR XML anidadas y la directiva TYPE para
devolver instancias de tipo XML, permite escribir consultas de forma ms fcil.

En el modo PATH, los nombres o alias de columna se tratan como expresiones XPath.
Estas expresiones indican el modo en el que se asignan los valores a XML. Cada
expresin XPath es una expresin relativa que proporciona el tipo de elemento, como
el atributo, el elemento y el valor escalar, as como el nombre y la jerarqua del nodo
que se generar en relacin con el elemento de fila.

En el siguiente ejemplo, vamos a ejecutar una consulta de pedidos cuyo ao de pedido
sea en el 2011 en modo FOR XML PATH.

SELECT I DPEDI DO, FECHAPEDI DO, DESTI NATARI O
FOR XML PATH
GO

El resultado siguiente es XML centrado
en elementos en el que cada valor de
columna del conjunto de filas resultante
se agrupa en un elemento. Puesto que
la clusula SELECT no especifica
ningn alias para los nombres de
columna, los nombres de elemento
secundario generados son los mismos
que los nombres de columna
correspondientes de la clusula
SELECT. Para cada fila del conjunto de
filas, se agrega una etiqueta <row>.

El siguiente ejemplo muestra cmo utilizar la consulta de modo PATH y retorna un
fragmento XML que contiene la lista de los empleados

SELECT I DEMPLEADO " @I D" ,
APELLI DOS " EMPLEADO/ APELLI DO" ,
NOMBRE " EMPLEADO/ NOMBRE" ,
DI RECCI ON " EMPLEADO/ DI RECCI ON"
FOR XML PATH
GO

146

El id del empleado es mapeado como
atributo con el signo (@), las columnas
apellidos, nombre y direccin estn
mapeados como sub elementos de
Empleado con la marca slash /.

El siguiente ejemplo muestra cmo utilizar el argumento opcional ElementName a la
consulta de modo PATH para modificar el nombre del elemento row.

SELECT I DEMPLEADO " @I D" ,
APELLI DOS " EMPLEADO/ APELLI DO" ,
NOMBRE " EMPLEADO/ NOMBRE" ,
DI RECCI ON " EMPLEADO/ DI RECCI ON"
FROM TB_EMPLEADOS
FOR XML PATH( ' EMPLOYEE' )
GO

4.2.4 Recuperar datos con OPENXML

OPENXML, palabra clave de Transact-SQL, proporciona un conjunto de filas en
documentos XML en memoria que es similar a una tabla o una vista. OPENXML
permite el acceso a los datos XML a pesar de ser un conjunto de filas relacional. Para
ello, proporciona una vista de conjunto de filas de la representacin interna de un
documento XML. Los registros del conjunto de filas pueden almacenarse en tablas de
base de datos.

OPENXML puede utilizarse en instrucciones SELECT y SELECT INTO donde puedan
aparecer como origen proveedores de conjuntos de filas, una vista u OPENROWSET.

Para escribir consultas en un documento XML mediante OPENXML, primero es
necesario llamar a sp_xml_preparedocument, que analiza el documento XML y
devuelve un identificador para el documento analizado que est listo para su uso. El
documento analizado es una representacin en rbol del modelo de objetos de
documento (DOM) de los distintos nodos del documento XML. Este identificador de
documento se pasa a OPENXML. A continuacin, OPENXML proporciona una vista de
conjunto de filas del documento, basndose en los parmetros que ha recibido.

4.2.4.1 Ejecutar una instruccin SELECT simple con OPENXML
El documento XML de este ejemplo se compone de los elementos <Clientes>,
<Pedido>y <PedidoDetalle>. La instruccin OPENXML recupera la informacin del
cliente desde el documento XML en un conjunto de filas de dos columnas (IDCliente y
Nombre).
Primero, para obtener un identificador de documento se llama al procedimiento
almacenado sp_xml_preparedocument. Este identificador de documento se pasa a
OPENXML.
La instruccin OPENXML muestra lo siguiente:
rowpattern (/ROOT/Clientes) identifica los nodos <Clientes> que se van a
procesar.
El valor del parmetro flags se establece en 1 e indica una asignacin centrada
en atributos. Como resultado, los atributos XML se asignan a las columnas del
conjunto de filas definido en SchemaDeclaration.
148
En SchemaDeclaration, en la clusula WITH, los valores ColName
especificados coinciden con los nombres de atributo XML correspondientes.
Por lo tanto, el parmetro ColPattern no se especifica en SchemaDeclaration.

A continuacin, la instruccin SELECT recupera todas las columnas del conjunto de
filas que proporciona OPENXML.

DECLARE @DocHandl e i nt
DECLARE @Xml Document nvar char ( 1000)
SET @Xml Document = N' <ROOT>
<Cl i ent e I DCl i ent e=" VI NET" Nombr e=" Paul Henr i ot " >
<Pedi do I Dpedi do=" 10248" I DCl i ent e=" VI NET" I DEmpl eado=" 5"
Fecha=" 1996- 07- 04T00: 00: 00" >
<Pedi doDet al l e I DPr oduct o=" 11" Cant i dad=" 12" / >
</ Pedi do>
</ Cl i ent e>
<Cl i ent e I DCl i ent e=" LI LAS" Nombr e=" Car l os Gonzl ez" >
<Pedi do I Dpedi do=" 10283" I DCl i ent e=" LI LAS" I DEmpl eado=" 3"
Fecha=" 1996- 08- 16T00: 00: 00" >
</ Pedi do>
</ Cl i ent e>
</ ROOT>'
- - Cr ear l a r epr esent aci on i nt er na del document o XML.
EXEC sp_xml _pr epar edocument @DocHandl e OUTPUT, @Xml Document
- - Ej ecut ar una sent enci a SELECT ut i l i zando OPENXML .
SELECT *
FROM OPENXML ( @DocHandl e, ' / ROOT/ Cl i ent e' , 1)
WI TH ( I DCl i ent e var char ( 10) ,
Nombr e var char ( 20) )
EXEC sp_xml _r emovedocument @DocHandl e
Go

4.2.4.2 Especificar ColPattern para la asignacin entre columnas del conjunto
de filas y los atributos y elementos XML
Este ejemplo muestra cmo se especifica el patrn XPath en el parmetro opcional
ColPattern para proporcionar una asignacin entre columnas del conjunto de filas y los
atributos y elementos XML. El documento XML de este ejemplo se compone de los
elementos <Cliente>, <Pedido>y <PedidoDetalle>. La instruccin OPENXML recupera
la informacin del cliente y del pedido del documento XML en forma de conjunto de
filas (IDCliente, Fecha, IDProducto y Cantidad).

Primero, para obtener un identificador de documento se llama al procedimiento
almacenado sp_xml_preparedocument. Este identificador de documento se pasa a
OPENXML. La instruccin OPENXML muestra lo siguiente:

rowpattern (/ROOT/Cliente/Pedido/PedidoDetalle) identifica los nodos
<PedidoDetalle>que se van a procesar.

A modo de ilustracin, el valor del parmetro flags se establece en 2 e indica una
asignacin centrada en elementos. Sin embargo, la asignacin especificada en
ColPattern sobrescribe esta asignacin,es decir, el patrn XPath especificado en
ColPattern asigna a atributos las columnas del conjunto de filas. El resultado es una
asignacin centrada en atributos.

En SchemaDeclaration, en la clusula WITH, tambin se especifica ColPattern con los
parmetros ColName y ColType. El parmetro opcional ColPattern es el patrn XPath
especificado e indica lo siguiente:
Las columnas IDPedido, IDCliente y Fechae del conjunto de filas se asignan a
los atributos del elemento primario de los nodos identificados por rowpattern, y
rowpattern identifica los nodos <OrderDetail>. Por lo tanto, las columnas
IDCliente y Fecha se asignan a los atributos IDCliente y Fecha del elemento
<Order>.
Las columnas IDProducto y Cantidad del conjunto de filas se asignan a los
atributos IDProducto y Cantidad de los nodos identificados en rowpattern.

A continuacin, la instruccin SELECT recupera todas las columnas del conjunto de
filas que proporciona OPENXML.
150

DECLARE @Xml Document Handl e i nt
DECLARE @Xml Document nvar char ( 1000)
SET @Xml Document = N' <ROOT>
<Cl i ent e I DCl i ent e=" VI NET" Nombr e=" Paul Henr i ot " >
<Pedi do I Dpedi do=" 10248" I DCl i ent e=" VI NET" I DEmpl eado=" 5"
Fecha=" 1996- 07- 04T00: 00: 00" >
</ Pedi do>
</ Cl i ent e>
<Cl i ent e I DCl i ent e=" LI LAS" Nombr e=" Car l os Gonzl ez" >
<Pedi do I Dpedi do=" 10283" I DCl i ent e=" LI LAS" I DEmpl eado=" 3"
Fecha=" 1996- 08- 16T00: 00: 00" >
</ Pedi do>
</ Cl i ent e>
</ ROOT>'

- - Cr ea una r epr esent aci on i nt er na del document o XML.
EXEC sp_xml _pr epar edocument @Xml Document Handl e OUTPUT, @Xml Document

- - Ej ecut a l a sent enci a SELECT ut i l zi ando OPENXML
SELECT * FROM OPENXML ( @Xml Document Handl e,
' / ROOT/ Cl i ent e/ Pedi do/ Pedi doDet al l e' , 2)
WI TH ( I Dpedi do i nt ' . . / @I Dpedi do' ,
I DCl i ent e var char ( 10) ' . . / @I DCl i ent e' ,
Fecha dat et i me ' . . / @Fecha' ,
I DPr oduct o i nt ' @I DPr oduct o' ,
Cant i dad i nt ' @Cant i dad' )

EXEC sp_xml _r emovedocument @Xml Document Handl e


Resumen
Los datos XML pueden interactuar con datos relacionales y aplicaciones SQL.
Esto significa que XML puede ser introducido en el sistema segn las necesidades
de modelado de datos se presentan sin interrumpir las aplicaciones existentes. El
servidor de base de datos tambin proporciona una funcionalidad administrativa
para la gestin de datos XML (por ejemplo, BACKUP, recuperacin y replicacin).
SQL Server 2008 introduce un tipo de datos nativo llamado XML. Un usuario
puede crear una tabla que tiene una o ms columnas de tipo XML, adems de
columnas relacionales. Las variables y los parmetros de XML tambin son
permitidos.
Puede crear una tabla con una columna XML mediante la instruccin CREATE
TABLE de costumbre. La columna XML puede ser indexado de una manera
especial. Puede trabaja con formato o sin formato
Para almacenar datos XML, puede proporcionar un valor para una columna XML a
travs de un parmetro o una variable de varias maneras:
Como tipo binario o de SQL que se convierte implcitamente en el tipo de
datos XML.
A travs del contenido de un archivo.
Como la salida del mecanismo de publicacin XML FOR XML con la directiva
TYPE, que genera una instancia de datos de tipo XML.
El valor proporcionado se comprueba la buena formacin. Una columna XML por
defecto permite a ambos documentos y fragmentos XML para ser almacenados. Si
los datos no pasan la comprobacin de buena formacin, se rechaza con un
mensaje de error apropiado.
Opcionalmente, se pueden recuperar resultados formales de una consulta SQL
como XML especificando la clusula FOR XML en la consulta. La clusula FOR
XML puede usarse en consultas de nivel superior y en subconsultas.
El modo RAW transforma cada fila del conjunto de resultados de la consulta en un
elemento XML que tiene el identificador genrico <row>o el nombre del elemento,
que se proporciona de manera opcional. El modo AUTO devuelve los resultados de
la consulta como elementos XML anidados. El modo EXPLICIT concede un mayor
152
control de la forma del XML. Es posible mezclar atributos y elementos con total
libertad para decidir la forma del XML.
OPENXML, palabra clave de Transact-SQL, proporciona un conjunto de filas en
documentos XML en memoria que es similar a una tabla o una vista. OPENXML
permite el acceso a los datos XML a pesar de ser un conjunto de filas relacional.

pginas:

http://technet.microsoft.com/es-es/library/ms187897(SQL.90).aspx
Aqu hallar los conceptos de OPENXML
En esta pgina, hallar los conceptos de consulta FOR XML
http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx
Aqu hallar los conceptos de manejo de datos en formato XML


MANEJO DE USUARIOS EN SQL SERVER
Al trmino de la unidad, el alumno, haciendo uso de los comandos del SQL
SERVER o utilizando asistentes, crearn un inicio de sesin autenticado en SQL
SERVER que otorgar o denegar permisos sobre los objetos de una base de
datos.

TEMARIO
1.1. Introduccin
1.1.1. Entidades de seguridad
1.1.2. Autenticacin
1.1.3. Inicio de sesin y usuarios
1.1.3.1. Creacin de login
1.1.3.2. Creacin de usuarios en SQL Server
1.1.3.3. Manejo de roles a nivel servidor
1.1.3.4. Manejo de roles a nivel base de datos
1.1.4. Permisos en el motor de base de datos
1.1.4.1. Comando GRANT
1.1.4.2. Comando DENY
1.1.4.3. Comando REVOKE

Los alumnos crea usuarios para manejar los objetos de la base de datos.
Los alumnos asigna permisos a los usuarios de una base de datos para realizar
operaciones de manipulacin de datos y definicin de datos.

UNIDAD DE
APRENDIZAJ E
5
154
5.1 INTRODUCCION

SQL Server 2008 es compatible con los modos de autenticacin de Windows y se
mezcla y se integra estrechamente con l. En este modo de acceso, se concede
sobre la base de un TOKEN de seguridad asignada durante el inicio de sesin de
dominio con xito por una cuenta de Windows y SQL Server se solicita el acceso
posteriormente. La condicin previa es que ambos deben pertenecer al mismo
entorno de ventanas.

El entorno de dominio de ACTIVE DIRECTORY proporciona un nivel adicional de
proteccin del protocolo KERBEROS. Este protocolo regula el comportamiento del
mecanismo de autenticacin de Windows. En el servidor de autenticacin de modo
mixto SQL, tambin se puede utilizar. Las credenciales se verifican desde el
repositorio mantenido por el servidor SQL Server.

El aumento de la seguridad se ha despedido de la necesidad de mantener el conjunto
separado de cuentas. Sin embargo, los inicios de sesin de SQL Server se han
mejorado con el cifrado de los Certificados de SQL generado para las comunicaciones
que involucran el software de cliente basado en MADC. NET.

SQL Server incluye varios mtodos y herramientas que permiten configurar la
seguridad para que los usuarios, los servicios y las otras cuentas puedan tener acceso
al sistema.

5.1.1 Entidades de seguridad

Las entidades de seguridad son entidades que pueden solicitar recursos de SQL
Server. Igual que otros componentes del modelo de autorizacin de SQL Server, las
entidades de seguridad se pueden organizar en jerarquas.

El mbito de influencia de una entidad de seguridad depende del mbito de su
definicin: Windows, servidor o base de datos; y de si la entidad de seguridad es
indivisible o es una coleccin. Un Inicio de sesin de Windows es un ejemplo de
entidad de seguridad indivisible y un Grupo de Windows es un ejemplo de una del tipo
coleccin. Toda entidad de seguridad tiene un identificador de seguridad (SID).
156

Entidades de seguridad a nivel de Windows
Inicio de sesin del dominio de Windows
Inicio de sesin local de Windows

Entidad de seguridad de SQL Server
Inicio de sesin de SQL Server

Entidades de seguridad a nivel de bases de datos
Usuario de base de datos
Funcin de base de datos
Funcin de aplicacin

5.1.1.1 Inicio de sesin sa de SQL Server

El inicio de sesin sa de SQL Server es una entidad de seguridad del servidor. Se crea
de forma predeterminada cuando se instala una instancia. En SQL Server 2005 y SQL
Server 2008, la base de datos predeterminada de sa es master. Es un cambio de
comportamiento con respecto a versiones anteriores de SQL Server.

5.1.1.2 Funcin PUBLIC de base de datos

Todos los usuarios de una base de datos pertenecen a la funcin public de la base de
datos. Cuando a un usuario no se le han concedido ni denegado permisos de un
elemento que puede protegerse, el usuario hereda los permisos de ese elemento
concedidos a public.

5.1.1.3 Cliente y servidor de base de datos

Por definicin, un cliente y un servidor de base de datos son entidades de seguridad y
se pueden proteger. Estas entidades se pueden autenticar mutuamente antes de
establecer una conexin de red segura. SQL Server admite el protocolo de
autenticacin Kerberos, que define cmo interactan los clientes con un servicio de
autenticacin de red.

En este ejemplo, listamos todos los usuarios de la base de datos Negocios2011,
donde le resultado se muestra en la figura siguiente

USE NEGOCI OS2011
GO

SELECT *
FROM SYS. SYSLOGI NS
GO

5.1.2 Autenticacin

Para acceder a SQL SERVER, se dispone de dos modos de autenticacin:
Autenticacin de Windows y Autenticacin en modo mixto.

En la autenticacin de Windows, el sistema operativo es el responsable de identificar
al usuario.

SQL Server usa despus esa identificacin del sistema operativo para determinar los
permisos del usuario que hay que aplicar. Con la autenticacin en modo mixto, los dos
Windows y SQL Server son responsables de identificar al usuario.

5.1.2.1 Configurar el modo de autenticacin

En la configuracin del modo de autenticacin, se siguen los siguientes pasos:

En la ventana del explorador de objetos, hacer click derecho sobre el administrador del
SQL Server, seleccione la opcin Propiedades, tal como se muestra en la figura.
158

En la ventana de propiedades, selecciona la opcin SEGURIDAD, para visualizar las
opciones de seguridad. Seleccione el modo de autenticacin de servidor, seleccione
Modo Windows y SQL Server, modo mixto, para que el usuario ingrese por cualquier
de las dos formas, cuando realice los cambios presione el botn ACEPTAR

5.1.3 Inicios de sesin y usuarios

En las siguientes secciones, aprenderemos cmo crear inicios de sesin y usuarios,
pero antes de iniciar el proceso es necesario comprender que es un inicio de sesin.

Segn lo visto anteriormente, una cuenta de usuario de Windows puede ser necesaria
para conectarse a una base de datos. Tambin, podra ser necesaria la autenticacin
de SQL Server. Tanto si se va a usar autenticacin de Windows o autenticacin de
modo mixto, la cuenta que se usa para la conexin a SQL SERVER se le conoce
como inicio de sesin de SQL Server.

En forma predeterminada, una cuenta de inicio de sesin de SQL Server no tiene un
ID de usuario de base de datos asociado con ella, por ello carece de permisos.

5.1.3.1 Creacin de Inicios de Sesin o Login

Podemos manejar un inicio de sesin utilizando el explorador de objetos en el
manejador del SQL Server o ejecutando las sentencias CREATE LOGIN, ALTER
LOGIN y DROP LOGIN

Para crear un inicio de sesin o login, usamos la sentencia CREATE LOGIN, la
sintaxis es la siguiente:

CREATE LOGI N l ogi nName { WI TH <opt i on_l i st 1> | FROM <sour ces> }
<opt i on_l i st 1> : : =
PASSWORD = { ' passwor d' | hashed_passwor d HASHED } [ MUST_CHANGE
]
[ , <opt i on_l i st 2> [ , . . . ] ]

<opt i on_l i st 2> : : =
SI D = si d
| DEFAULT_DATABASE =dat abase
| DEFAULT_LANGUAGE =l anguage
| CHECK_EXPI RATI ON = { ON | OFF}
| CHECK_POLI CY = { ON | OFF}
| CREDENTI AL =cr edent i al _name <sour ces> : : =
WI NDOWS [ WI TH <wi ndows_opt i ons>[ , . . . ] ]
160
| CERTI FI CATE cer t name
| ASYMMETRI C KEY asym_key_name<wi ndows_opt i ons> : : =
DEFAULT_DATABASE =dat abase
| DEFAULT_LANGUAGE =l anguage

loginName Especifica el nombre del inicio de sesin que se va a
crear. Hay cuatro tipos de inicio de sesin: de SQL
Server, de Windows, asignado a un certificado y
asignado a una clave asimtrica. Cuando crea inicios de
sesin que se asignan desde una cuenta de dominio de
Windows, debe usar el nombre de inicio de sesin de
usuario anterior a Windows 2000 con el formato
[<Dominio>\<InicioDeSesin>].
PASSWORD ='password' Slo se aplica a inicios de sesin de SQL Server.
Especifica la contrasea del inicio de sesin que se est
creando. Debe usar siempre una contrasea segura.
DEFAULT_DATABASE
=database

Especifica la base de datos predeterminada que debe
asignarse al inicio de sesin. Si no se incluye esta
opcin, la base de datos predeterminada es master.
DEFAULT_LANGUAGE
=language
Especifica el idioma predeterminado que debe asignarse
al inicio de sesin. Si no se incluye esta opcin, el
idioma predeterminado es el del servidor. Si el idioma
predeterminado del servidor se cambia ms tarde, el del
inicio de sesin se mantiene igual.
CHECK_EXPIRATION = {
ON | OFF }
Slo se aplica a inicios de sesin de SQL Server.
Especifica si debe aplicarse la directiva de expiracin de
contraseas en este inicio de sesin. El valor
predeterminado es OFF.
CHECK_POLICY = {ON |
OFF }
Slo se aplica a inicios de sesin de SQL Server.
Especifica que se deben aplicar las directivas de
contraseas de Windows en el equipo que ejecuta SQL
Server para este inicio de sesin. El valor
predeterminado es ON.

En el ejemplo siguiente, crea un inicio de sesin para un usuario determinado y le
asigna un password. La opcin MUST_CHANGE exige a los usuarios que cambien el
password la primera vez que se conecten al servidor.

CREATE LOGI N ci ber t ec
WI TH PASSWORD = ' 12345678'
MUST_CHANGE;
GO

En el ejemplo siguiente, crea un inicio de sesin para un usuario determinado y le
asigna un password, la opcin DEFAULT_DATABASE predeterminada.

CREATE LOGI N ci ber t ec
WI TH PASSWORD = ' pa$$w0r d' ,
DEFAULT_DATABASE=Negoci os2011
GO

Se puede modificar un inicio de sesin o login ejecutando la sentencia ALTER LOGIN.
En el ejemplo siguiente, modificamos el password o clave del inicio de sesin cibertec,
tal como se muestra.

ALTER LOGI N ci ber t ec
WI TH PASSWORD = ' upc_ci ber t ec'
GO

Podemos remover un login ejecutando la sentencia DROP LOGIN, tal como se
muestra en la sentencia.

DROP LOGI N ci ber t ec
GO
162

Utilizando el asistente del administrador
podemos crear un inicio de sesin.

Desde el explorador de objetos,
despliegue la opcin SEGURIDAD, en
la opcin Inicios de sesin, hacer click
derecho y seleccione la opcin Nuevo
inicio de sesin

En la ventana inicio de sesin:
Asigne un nombre al inicio de sesin
Defina la autenticacin, se recomienda que sea de tipo SQL Server
Escriba y confirme el password
Desmarcar la opcin Exigir la expiracin de la contrasea
Seleccione la base de datos predeterminada (Negocios2011), si no lo
selecciona, por defecto es la base de datos master.
Seleccione el idioma.


En la opcin Asignacin de
usuarios, asigne al inicio de
sesin la base de datos as
como el esquema donde
trabajar el inicio de sesin, tal
como se muestra en la figura.

En la opcin Estada, habilitar la
sesin para trabajar con el inicio
de sesin al momento de realizar
la conexin. A continuacin,
presione el botn ACEPTAR.

5.1.3.2 Creacin de usuarios en SQL Server

Se puede crear un usuario utilizando el Administrador corporativo o comandos
TRANSACT-SQL.

Para crear un usuario utilizando comandos TRANSACT-SQL, utilice el comando
CREATE USER, cuya sintaxis es la siguiente:

164
CREATE USER user _name
[ { { FOR | FROM }
{
LOGI N l ogi n_name
}
| WI THOUT LOGI N
]
[ WI TH DEFAULT_SCHEMA = schema_name ]

user_name
Especifica el nombre por el que se identifica al usuario en
esta base de datos. user_name es de tipo sysname. Puede
tener una longitud mxima de 128 caracteres.
LOGI N login_name
Especifica el inicio de sesin de SQL Server del usuario de
base de datos que se va a crear. login_name debe ser un
inicio de sesin vlido en el servidor. Cuando este inicio de
sesin de SQL Server se introduzca en la base de datos
adquirir el nombre y el identificador del usuario de la base
de datos que se va a crear.
WI THOUT LOGI N
Especifica que el usuario no se debe asignar a un inicio de
sesin existente

En el siguiente ejemplo, vamos a crear primero un inicio de sesin de servidor
denominado BDCibertec con su password, y a continuacin, se crea el usuario de
base de datos BDCibertec correspondiente a la base de datos Negocios2011

CREATE LOGI N BDCI BERTEC
WI TH PASSWORD = ' PA$$W0RD' ,
DEFAULT_DATABASE=NEGOCI OS2011;
GO

USE NEGOCI OS2011;
GO

CREATE USER BDCI BERTEC
FOR LOGI N BDCI BERTEC;
GO

165

Utilizando el explorador de objetos,
podemos crear un usuario
Seleccione la base de datos para
crear el usuario: Negocios2011
Desplegar la base de datos,
seleccione la carpeta Seguridad.
Despliegue Seguridad, hacer click
derecho en la opcin Usuarios,
seleccione la opcin Nuevo
Usuarios tal como se muestra en
la figura.

En la ventana Nuevo usuario, asigne
el nombre del usuario, asigne el inicio
de sesin del usuario. Presione el
botn ACEPTAR, para crear el
usuario de la base de datos
Negocios2011.

Para verificar si un usuario se encuentra en la lista de usuarios de la base de datos,
ejecutar una consulta a la tabla sys.sysusers, tal como se muestra.

USE NEGOCI OS2011;
GO

166
SELECT *
FROM SYS. SYSUSERS
GO

5.1.3.3 Manejo de Roles a nivel servidor

Para administrar con facilidad los permisos en el servidor, SQL Server proporciona
varios roles, que son las entidades de seguridad que agrupan a otras entidades de
seguridad. Los roles son como los grupos del sistema operativo Microsoft Windows.

Los roles de nivel de servidor tambin se denominan roles fijos de servidor porque no
se pueden crear nuevos roles de nivel de servidor. Puede agregar inicios de sesin de
SQL Server, cuentas de Windows y grupos de Windows a los roles de nivel de
servidor. Cada miembro de un rol fijo de servidor puede agregar otros inicios de sesin
a ese mismo rol.

En la tabla siguiente se muestran los roles de nivel de servidor y sus capacidades

rol de servidor Descripcin
sysadmin Los miembros del rol fijo de servidor sysadmin pueden realizar
cualquier actividad en el servidor.
serveradmin Los miembros del rol fijo de servidor serveradmin pueden cambiar
las opciones de configuracin en el servidor y apagarlo.
securityadmin Los miembros del rol fijo de servidor securityadmin administran los
inicios de sesin y sus propiedades. Pueden administrar los
permisos de servidor GRANT, DENY y REVOKE. Tambin, pueden
administrar los permisos de nivel de base de datos GRANT, DENY
y REVOKE si tienen acceso a una base de datos. Asimismo,
pueden restablecer las contraseas para los inicios de sesin de
SQL Server.
Nota de seguridad.- La capacidad de conceder acceso a Motor de
base de datos y configurar los permisos de usuario permite que el
administrador de seguridad asigne la mayora de los permisos de
servidor. El rol securityadmin se debe tratar como equivalente del
rol sysadmin.
processadmin Los miembros del rol fijo de servidor processadmin pueden finalizar
los procesos que se ejecuten en una instancia de SQL Server.
setupadmin Los miembros del rol fijo de servidor setupadmin pueden agregar y
quitar servidores vinculados.
bulkadmin Los miembros del rol fijo de servidor bulkadmin pueden ejecutar la
instruccin BULK INSERT.
diskadmin El rol fijo de servidor diskadmin se usa para administrar archivos de
disco.
dbcreator Los miembros del rol fijo de servidor dbcreator pueden crear,
modificar, quitar y restaurar cualquier base de datos.
public Cada inicio de sesin de SQL Server pertenece al rol public de
servidor. Cuando a una entidad de seguridad de servidor no se le
han concedido ni denegado permisos especficos para un objeto
protegible, el usuario hereda los permisos concedidos al rol public
en ese objeto. Solo asigne los permisos pblicos en objetos
cuando desee que estos estn disponibles para todos los usuarios.

5.1.3.4 Manejo de Roles a nivel base de datos

Los roles de nivel de base de datos se aplican a toda la base de datos en lo que
respecta a su mbito de permisos.

Existen dos tipos de roles de nivel de base de datos en SQL Server: los roles fijos de
base de datos, que estn predefinidos en la base de datos, y los roles flexibles de
base de datos, que pueden crearse.

Los roles fijos de base de datos se definen en el nivel de base de datos y existen en
cada una de ellas. Los miembros de los roles de base de datos db_owner y
168
db_securityadmin pueden administrar los miembros de los roles fijos de base de
datos. Sin embargo, slo los miembros del rol de base de datos db_owner pueden
agregar miembros al rol fijo de base de datos db_owner.

Puede agregar cualquier cuenta de la base de datos y otros roles de SQL Server a los
roles de nivel de base de datos. Cada miembro de un rol fijo de base de datos puede
agregar otros inicios de sesin a ese mismo rol.
En la tabla siguiente, se muestran los roles fijos de nivel de base de datos y sus
capacidades. Estos roles existen en todas las bases de datos.

rol de base de datos Descripcin
db_owner Los miembros del rol fijo de base de datos db_owner pueden
realizar todas las actividades de configuracin y mantenimiento
en la base de datos y tambin pueden quitar la base de datos.
db_securityadmin Los miembros del rol fijo de base de datos db_securityadmin
pueden modificar la pertenencia a roles y administrar permisos.
Si se agregan entidades de seguridad a este rol, podra
habilitarse un aumento de privilegios no deseado.
db_accessadmin Los miembros del rol fijo de base de datos db_accessadmin
pueden agregar o quitar el acceso a la base de datos para
inicios de sesin de Windows, grupos de Windows e inicios de
sesin de SQL Server.
db_backupoperator Los miembros del rol fijo de base de datos db_backupoperator
pueden crear copias de seguridad de la base de datos.
db_ddladmin Los miembros del rol fijo de base de datos db_ddladmin
pueden ejecutar cualquier comando del lenguaje de definicin
de datos (DDL) en una base de datos.
db_datawriter Los miembros del rol fijo de base de datos db_datawriter
pueden agregar, eliminar o cambiar datos en todas las tablas
de usuario.
db_datareader Los miembros del rol fijo de base de datos db_datareader
pueden leer todos los datos de todas las tablas de usuario.
db_denydatawriter Los miembros del rol fijo de base de datos db_denydatawriter
no pueden agregar, modificar ni eliminar datos de tablas de
usuario de una base de datos.
db_denydatareader Los miembros del rol fijo de base de datos db_denydatareader
no pueden leer datos de las tablas de usuario dentro de una
base de datos.

5.1.4 Permisos en el motor de base de datos

Todos los elementos protegibles de SQL Server tienen permisos asociados que se
pueden conceder a una entidad de seguridad.

Los permisos se pueden manipular con las conocidas consultas GRANT, DENY y
REVOKE de TRANSACT-SQL.

5.1.4.1 Comando GRANT
Concede permisos sobre un elemento protegible a una entidad de seguridad. El
concepto general es GRANT <algn permiso>ON <algn objeto>TO <algn usuario,
inicio de sesin o grupo>.
La sintaxis de este comando:

GRANT { ALL [ PRI VI LEGES ] }
| per mi ssi on [ ( col umn [ , . . . n ] ) ] [ , . . . n ]
[ ON [ cl ass : : ] secur abl e ] TO pr i nci pal [ , . . . n ]
[ WI TH GRANT OPTI ON ] [ AS pr i nci pal ]

La sintaxis completa de la instruccin GRANT es compleja. El anterior diagrama de
sintaxis se ha simplificado para concentrar la atencin en su estructura. La sintaxis
completa para conceder permisos para elementos protegibles especficos se describe
en los temas enumerados a continuacin.

En la siguiente tabla, se enumeran los elementos protegibles y los temas donde se
describe la sintaxis especfica de los mismos.
170

Rol de aplicacin GRANT (permisos de entidad de seguridad de
base de datos de Transact-SQL)
Certificado GRANT (permisos de certificado de Transact-SQL)
Base de datos GRANT (permisos de base de datos de Transact-
SQL)
Funcin GRANT (permisos de objeto de Transact-SQL)
Inicio de sesin GRANT (permisos de entidad de seguridad de
servidor de Transact-SQL)
Objeto GRANT (permisos de objeto de Transact-SQL)
Rol GRANT (permisos de entidad de seguridad de
Esquema GRANT (permisos de esquema de Transact-SQL)
Servidor GRANT (permisos de servidor de Transact-SQL)
Procedimiento almacenado GRANT (permisos de objeto de Transact-SQL)
Objetos de sistema GRANT (permisos de objeto de sistema de
Transact-SQL)
Tabla GRANT (permisos de objeto de Transact-SQL)
Tipo GRANT (permisos de tipo de Transact-SQL)
Usuario GRANT (permisos de entidad de seguridad de
Vista GRANT (permisos de objeto de Transact-SQL)

El siguiente ejemplo muestra cmo otorgar permiso de alterar cualquier base de datos
al usuario ciber

GRANT ALTER ANY DATABASE
TO ci ber

El siguiente ejemplo muestra como otorgar permiso de ejecutar el comando SELECT y
UPDATE hacia la tabla Ventas.Cliente, al usuario ciber

GRANT SELECT, UPDATE
ON Vent as. Cl i ent es
TO ci ber

5.1.4.2 Comando DENY
Deniega un permiso a una entidad de seguridad. Evita que la entidad de seguridad
herede permisos por su pertenencia a grupos o roles. La sintaxis simplificada de este
comando:

DENY { ALL [ PRI VI LEGES ] }
[ ON [ cl ass : : ] secur abl e ] TO pr i nci pal [ , . . . n ]
[ CASCADE] [ AS pr i nci pal ]

La sintaxis completa de la instruccin DENY es compleja. El diagrama anterior se ha
simplificado para concentrar la atencin en su estructura.

DENY producir un error si CASCADE no se especifica al denegar un permiso a una
entidad de seguridad a la que se concedi ese permiso con GRANT OPTION.

El siguiente ejemplo muestra cmo denegar permiso de crear un procedimiento
almacenado al usuario ciber

DENY CREATE PROCEDURE
TO ci ber

172
El siguiente ejemplo muestra como denegar permiso de ejecutar el comando UPDATE
y DELETE hacia la tabla RRHH.Empleados, al usuario ciber

DENY UPDATE, DELETE
ON RRHH. Empl eados
TO ci ber

5.1.4.3 Comando REVOKE
Quita un permiso concedido o denegado previamente. La sintaxis simplificada de este
comando:

REVOKE [ GRANT OPTI ON FOR ] {
[ ALL [ PRI VI LEGES ] ]
}
[ ON [ cl ass : : ] secur abl e ]
{ TO | FROM } pr i nci pal [ , . . . n ]
[ CASCADE] [ AS pr i nci pal ]

La sintaxis completa de la instruccin REVOKE es compleja. El diagrama de sintaxis
anterior se ha simplificado para concentrar la atencin en su estructura.

El siguiente ejemplo muestra como revocar permiso de crear una tabla al usuario ciber

REVOKE CREATE TABLE
FROM ci ber

El siguiente ejemplo muestra como revocar permiso de ejecutar el comando SELECT,
UPDATE y DELETE hacia la tabla RRHH.Empleados, al usuario ciber

REVOKE SELECT, UPDATE, DELETE
ON RRHH. Empl eados
TO ci ber
go

5.1.4.4 Asignando Permisos al usuario utilizando el Administrador Corporativo

El administrador de una base de datos puede otorgar, denegar o revocar permisos al
usuario sobre los objetos de la base de datos asignada.

Para realizar este proceso,
seleccione la base de datos, para
nuestro case ser Negocios2011, tal
como se muestra.

Seleccione la carpeta SEGURIDAD,
desplegar la carpeta.
Seleccione la carpeta USUARIOS,
hacer click derecho sobre la carpeta
y seleccione la opcin
PROPIEDADES, tal como se
muestra en la figura.

En la ventana Usuario, seleccione la
opcin Elementos que pueden
protegerse tal como se muestra en
la figura.

Presione el botn Buscar para elegir
los objetos a proteger.

174

En esta opcin, debemos seleccionar
los objetos que vamos a asignar
permisos.
Seleccione todos los objetos para
que liste los objetos de la base de
datos. Presione el botn ACEPTAR.

Seleccione los tipos de objetos que
vamos a manejar sus permisos.
Para nuestro caso, seleccionamos el
objeto TABLAS, tal como se muestra.
Presione el botn ACEPTAR.

A continuacin, se lista todas las
tablas de la base de datos.
Al seleccionar una tabla, se listan los
permisos de la tabla para cada uno
de sus procesos.
Incluso, podemos controlar permisos
a cada columna de la tabla.


En esta ventana, podemos dar
permisos a las columnas de la tabla
en un determinado proceso
(ACTUALIZAR)
Para confirmar el proceso, presione
el botn ACEPTAR.

Para confirmar los permisos, presione el botn ACEPTAR de la ventana principal.

Si abrimos la ventana de propiedades del usuario, visualizamos los permisos
efectuado a la tabla RRHH.Cargos que concede el permiso de UPDATE y denega el
permiso de SELECT

176


Resumen
Las entidades de seguridad son entidades que pueden solicitar recursos de SQL
Server. Igual que otros componentes del modelo de autorizacin de SQL Server,
las entidades de seguridad se pueden organizar en jerarquas.
El inicio de sesin sa de SQL Server es una entidad de seguridad del servidor. Se
crea de forma predeterminada cuando se instala una instancia. En SQL Server
2005 y SQL Server 2008, la base de datos predeterminada de sa es master. Es un
cambio de comportamiento con respecto a versiones anteriores de SQL Server.
Por definicin, un cliente y un servidor de base de datos son entidades de
seguridad y se pueden proteger. Estas entidades se pueden autenticar
mutuamente antes de establecer una conexin de red segura. SQL Server admite
el protocolo de autenticacin Kerberos, que define cmo interactan los clientes
con un servicio de autenticacin de red.
Para acceder a SQL SERVER, se dispone de dos modos de autenticacin:
Autenticacin de Windows y Autenticacin en modo mixto. En la autenticacin de
Windows, el sistema operativo es el responsable de identificar al usuario. SQL
Server usa despus esa identificacin del sistema operativo para determinar los
permisos del usuario que hay que aplicar. Con la autenticacin en modo mixto, los
dos Windows y SQL Server son responsables de identificar al usuario.
Una cuenta de usuario de Windows puede ser necesaria para conectarse a una
base de datos. Tambin, podra ser necesaria la autenticacin de SQL Server.
Tanto si se va a usar autenticacin de Windows o autenticacin de modo mixto, la
cuenta que se usa para la conexin a SQL SERVER se le conoce como inicio de
sesin de SQL Server.
Opcionalmente, se pueden recuperar resultados formales de una consulta SQL
como XML especificando la clusula FOR XML en la consulta. La clusula FOR
XML puede usarse en consultas de nivel superior y en subconsultas.
Para administrar con facilidad los permisos en el servidor, SQL Server proporciona
varios roles, que son las entidades de seguridad que agrupan a otras entidades de
seguridad. Los roles son como los grupos del sistema operativo Microsoft Windows
178
Los roles de nivel de base de datos se aplican a toda la base de datos en lo que
respecta a su mbito de permisos. Existen dos tipos de roles de nivel de base de
datos en SQL Server: los roles fijos de base de datos, que estn predefinidos en la
base de datos, y los roles flexibles de base de datos, que pueden crearse.
Todos los elementos protegibles de SQL Server tienen permisos asociados que se
pueden conceder a una entidad de seguridad. Los permisos se pueden manipular
con las conocidas consultas GRANT, DENY y REVOKE de TRANSACT-SQL
pginas:

Aqu hallar los conceptos de crear usuarios
http://mredison.wordpress.com/2009/03/15/como-creo-un-usuario-en-sql-server/
En esta pgina, hallar los conceptos de cmo crear un usuario
http://msdn.microsoft.com/es-es/library/bb510418.aspx
Aqu hallar los conceptos de manejo identidades y control de acceso


SEGURIDAD Y RESTAURACIN EN SQL SERVER
Al trmino de la unidad, el alumno haciendo uso de los conocimientos de mejores
prcticas en la copia de seguridad y restauracin de una base de datos, realizar
copias de respaldo o restauracin de una base de datos en SQL SERVER.

TEMARIO
1.1. Introduccin a las estrategias de seguridad y restauracin en SQL Server
1.1.1. Impacto del modelo de recuperacin de copia de seguridad.
1.1.2. Diseo de la estrategia de copia de seguridad
1.2. Copia de Seguridad en SQL Server
1.2.1. Copia de seguridad completa
1.2.2. Copia de seguridad diferencial
1.2.3. Copia de seguridad de registro de transacciones
1.2.4. BACK UP (Transact-SQL)
1.2.5. Realizar copia de seguridad utilizando el Management Studio de SQL Server
1.3. Restaurando una copia de seguridad
1.3.1. RESTORE (Transact-SQL)
1.3.2. Restaurando una base de datos utilizando Management Studio

Los alumnos crea copia de respaldo o seguridad a una base de datos y su registro
de transacciones en forma completa o diferencial.
Los alumnos restauran una base de datos desde una copia de respaldo o de
seguridad de una base de datos o de registro de transacciones.

UNIDAD DE
APRENDIZAJ E
6
180
6.1 INTRODUCCION A LAS ESTRATEGIAS DE SEGURIDAD Y
RESTAURACION EN SQL SERVER

El propsito de crear copias de seguridad de SQL Server es para que usted pueda
recuperar una base de datos daada. Sin embargo, copias de seguridad y
restauracin de los datos debe ser personalizado para un ambiente particular y debe
trabajar con los recursos disponibles. Por lo tanto, un uso fiable de copia de seguridad
y restauracin para la recuperacin exige una copia de seguridad y restauracin de la
estrategia.

Una copia de seguridad bien diseado y restaurar la estrategia maximiza la
disponibilidad de datos y minimiza la prdida de datos, teniendo en cuenta sus
necesidades de negocio en particular.

Una estrategia de copia de seguridad y restauracin de copia de seguridad contiene
una porcin de seguridad y una porcin de restauracin. La parte de seguridad de la
estrategia define el tipo y frecuencia de las copias de seguridad, la naturaleza y la
velocidad del hardware que se requiere para que, como copias de seguridad deben
ser probados, y dnde y cmo los medios de comunicacin copia de seguridad se van
a almacenar (incluyendo las consideraciones de seguridad).

La parte de restauracin de la estrategia define quin es responsable de las
restauraciones y cmo restaurar se debe realizar para cumplir con sus objetivos de
disponibilidad de la base de datos y para minimizar la prdida de datos. Le
recomendamos que documente la copia de seguridad y restaurar los procedimientos y
guardar una copia de la documentacin en su libro de ejecutar.

6.1.1 Impacto del modelo de recuperacin de copia de seguridad y
restauracin

Copia de seguridad y restauracin de las operaciones se producen en el contexto de
un modelo de recuperacin. Un modelo de recuperacin es una propiedad de base de
datos que controla el registro de transacciones que se gestiona. Adems, el modelo
de recuperacin de una base de datos determina qu tipos de copias de seguridad y
182
restauracin son compatibles con la base de datos. Normalmente, una base de datos
utiliza ya sea el modelo de recuperacin simple o el modelo de recuperacin completa.

La mejor opcin de modelo de recuperacin de la base de datos depende de los
requerimientos de su negocio. Para evitar la gestin del registro de transacciones y
simplificar el BACKUP y restauracin, utilice el modelo de recuperacin simple. Para
minimizar la prdida de trabajo, a costa de los gastos generales de administracin,
utilice el modelo de recuperacin completa.

6.1.2 Diseo de la estrategia de copia de seguridad

Seleccionado un modelo de recuperacin que se ajuste a sus requisitos de negocio
para una base de datos especfica, usted tiene que planificar e implementar una
estrategia de copia de seguridad correspondiente.

La estrategia de copia de seguridad ptima depende de una variedad de factores, de
los cuales los siguientes son especialmente importantes:
Cuntas horas al da no tiene aplicaciones para acceder a la base de datos?
Si hay una predecible temporada baja perodo, le recomendamos que programe
copias de seguridad completas de bases de datos para ese perodo.
Con qu frecuencia se producen cambios y actualizaciones probables que
ocurra?
Si los cambios son frecuentes, considere lo siguiente:
Bajo el modelo de recuperacin simple, considere la programacin de copias
de seguridad diferenciales entre copias de seguridad de base de datos
completa.
Bajo el modelo de recuperacin completa, debe programar copias de seguridad
frecuentes de registro. Programacin de copias de seguridad diferenciales
entre copias de seguridad completas puede reducir el tiempo de restauracin
mediante la reduccin del nmero de copias de seguridad de registros que se
deben restaurar despus de restaurar los datos.
Son los cambios probables de ocurrir en tan slo una pequea parte de la base
de datos o en una gran parte de la base de datos?

Para una gran base de datos en la que los cambios se concentran en una parte de los
archivos o grupos de archivos, copias de seguridad parciales o copias de seguridad de
archivos y puede ser til.
Cunto espacio en disco una copia de seguridad completa requiere?

6.1.3 Copia de seguridad en SQL SERVER

El alcance de una copia de seguridad de los datos puede ser una base de datos
completa, una base de datos parciales, o un conjunto de archivos o grupos de
archivos. Para cada uno de estos, SQL Server admite copias de seguridad completas
y diferenciales:

6.1.3.1 Copia de seguridad completa

Contiene todos los datos en una base de datos especfica o un conjunto de grupos de
archivos o archivos, y tambin suficiente registro para permitir la recuperacin de los
datos.

6.1.3.2 Copia de seguridad diferencial

Se basa en la ltima copia de seguridad completa de los datos. Esto se conoce como
copia diferencial. Una copia diferencial es una copia de seguridad completa de lectura
/ escritura de datos. Una copia de seguridad diferencial incluye slo los datos que ha
cambiado desde la base diferencial. Por lo general, los respaldos diferenciales que se
toman poco despus de la copia de seguridad de base son ms pequeos y ms
rpidos de crear que la base de una copia de seguridad completa. Por lo tanto, utilizar
los respaldos diferenciales puede acelerar el proceso de hacer copias de seguridad
frecuentes para disminuir el riesgo de prdida de datos. Por lo general, una base
diferencial es utilizada por varias copias de seguridad diferenciales sucesivas. En el
tiempo de restauracin, la copia de seguridad que se restaura el primero, seguido por
la copia de seguridad diferencial ms reciente.

184
6.1.3.3 Copia de seguridad del registro de transacciones

Bajo el modelo de recuperacin optimizado para cargas masivas de registros de
modelo de recuperacin, copias de seguridad del registro de transacciones (o copias
de seguridad de registro) son obligatorias. Cada copia de seguridad de registro cubre
la parte del registro de transacciones que estaba activa cuando la copia de seguridad
fue creada, e incluye todos los registros que no fueron respaldados en una copia de
seguridad de registros anterior. Una secuencia ininterrumpida de seguridad del registro
contiene la cadena de registro completo de la base de datos, lo que se dice que es
continua. Bajo el modelo de recuperacin completa, y, a veces bajo el registro masivo
modelo de recuperacin, una cadena de registros ininterrumpida permite restaurar la
base de datos a cualquier punto en el tiempo.

Antes de crear la copia de seguridad de registro en primer lugar, debe crear una copia
de seguridad completa, como una copia de seguridad de bases de datos. A partir de
entonces, la copia de seguridad del registro de transacciones con regularidad es
necesaria, no slo para minimizar la prdida de trabajo sino tambin para permitir el
truncamiento del registro de transacciones.
.
6.1.3.4 BACKUP (TRANSACT-SQL)

Comando TRANSACT-SQL que realiza copias de seguridad de una base de datos
completa, o uno o ms archivos o grupos de archivos (BASE DE DATOS DE
SEGURIDAD). Adems, bajo el modelo de recuperacin optimizado para cargas
masivas de registros de modelo de recuperacin, copias de seguridad del registro de
transacciones (BACKUP LOG).

La sintaxis para realizar un BACK UP a la base de datos es la siguiente:

BACKUP DATABASE { nombr e_base_dat os | @var _nombr e_base_dat os }
TO <di sposi t i vo_backup> [ , . . . n ]
[ <MI RROR TO cl ause> ] [ next - mi r r or - t o ]
[ WI TH { DI FFERENTI AL | <gener al _WI TH_opt i ons> [ , . . . n ] } ]
[ ; ]

La sintaxis para realizar un BACK UP a los Filegroup de la base de datos

BACKUP DATABASE { nombr e_base_dat os | @var _nombr e_base_dat os }
<f i l e_or _f i l egr oup> [ , . . . n ]
[ WI TH { DI FFERENTI AL | <gener al _WI TH_opt i ons> [ , . . . n ] } ]
[ ; ]

La sintaxis para realizar un BACK UP al registro de transacciones o LOG de la base de
datos

BACKUP LOG { nombr e_base_dat os | @var _nombr e_base_dat os }
[ WI TH { <gener al _WI TH_opt i ons> | <l og- speci f i c_opt i onspec> } [
, . . . n ] ]
[ ; ]

Argumentos

Argumento Descripcin
DATABASE
Especifica una copia de seguridad
completa de la base de datos. Si se
especifica una lista de archivos y grupos
de archivos, slo se realiza la copia de
seguridad de esos archivos o grupos de
archivos. Durante una copia de seguridad
completa o diferencial de una base de
datos, SQL Server realiza la copia de
seguridad de una parte suficiente del
registro de transacciones para producir
una base de datos coherente cuando se
restaure la base de datos.
LOG
Especifica que slo se realizar la copia
de seguridad del registro de
transacciones. Se realiza la copia de
186
seguridad del registro desde la ltima
copia de seguridad del registro ejecutada
correctamente hasta el final actual del
registro.
{ nombr e_base_dat os |
@var _nombr e_base_dat os }
Es la base de datos para la que se realiza
la copia de seguridad del registro de
transacciones, de una parte de la base de
datos o de la base de datos completa. Si
se proporciona como una variable
(@var_nombre_base_datos), este
nombre se puede especificar como una
constante de cadena
(@var_nombre_base_datos =database
name) o como una variable de un tipo de
datos de cadena de caracteres
<f i l e_or _f i l egr oup> [ ,. . . n ]

Se utiliza slo con BACKUP DATABASE,
especifica un grupo de archivos o un
archivo de copia de seguridad que se va
a incluir en una copia de seguridad de
archivos, o especifica un grupo de
archivos o un archivo de slo lectura que
se va a incluir en una copia de seguridad
parcial.
En el modelo de recuperacin simple, se
permite la copia de seguridad de un
grupo de archivos slo si se trata de un
grupo de archivos de slo lectura.
Indica que el conjunto de dispositivos de
copia de seguridad correspondiente es un
conjunto de medios no reflejado o el
primero de los reflejos de un conjunto de
medios reflejado.
<dispositivo_backup >
Especifica el dispositivo de copia de
seguridad fsico o lgico que se va a
utilizar para la operacin de copia de
seguridad.
{device_logico | @var_device_logico }
Es el nombre lgico del dispositivo de
copia de seguridad en que se hace la
copia de seguridad de la base de datos.
El nombre lgico debe seguir las reglas
definidas para los identificadores.
{ DISK | TAPE } = { 'device_fisico' |
@var_device_fisico }
Especifica un archivo de disco o un
dispositivo de cinta.
[ <MI RROR TO cl ause>] [ next -
mi r r or - t o ]
Especifica un conjunto de hasta tres
dispositivos de copia de seguridad, cada
uno de los cuales reflejar los
dispositivos de copia de seguridad
especificados en la clusula TO. La
clusula MIRROR TO debe incluir el
mismo nmero y tipo de dispositivos de
copia de seguridad que la clusula TO.

Opciones de WITH

Opcin Descripcin
DI FFERENTI AL
Se utiliza slo con BACKUP DATABASE.
Especifica que la copia de seguridad de la base
de datos o el archivo slo debe estar compuesta
por las partes de la base de datos o el archivo
que hayan cambiado desde la ltima copia de
seguridad completa. Una copia de seguridad
diferencial suele ocupar menos espacio que una
copia de seguridad completa.
{ NOI NI T | I NI T }
NOINIT
Indica que el conjunto de copia de seguridad se
anexa al conjunto de medios especificado,
conservando as los conjuntos de copia de
seguridad existentes, es el valor predeterminado.
188
INIT
Especifica que se deben sobrescribir todos los
conjuntos de copia de seguridad, pero conserva el
encabezado de los medios. Si se especifica INIT,
se sobrescriben todos los conjuntos de copia de
seguridad existentes en el dispositivo, si las
condiciones lo permiten.
{ NOSKI P | SKI P }
NOSKIP
Indica a la instruccin BACKUP que compruebe la
fecha de expiracin de todos los conjuntos de
copia de seguridad de los medios antes de
permitir que se sobrescriban. ste es el
comportamiento predeterminado.
SKIP
Deshabilita la comprobacin de la expiracin y el
nombre del conjunto de copia de seguridad que
suele realizar la instruccin BACKUP para impedir
que se sobrescriban los conjuntos de copia de
seguridad. Para obtener ms informacin acerca
de las interacciones entre {INIT | NOINIT} y
{NOSKIP | SKIP},
{ NOFORMAT | FORMAT }
NOFORMAT
Especifica que la operacin de copia de seguridad
conservar los conjuntos de copias de seguridad
y el encabezado del medio existentes en los
volmenes del medio usados en esta operacin
de copia de seguridad. ste es el comportamiento
predeterminado.
FORMAT
Especifica que se debe crear un conjunto de
medios nuevo. FORMAT hace que la operacin
de copia de seguridad escriba un nuevo
encabezado en todos los volmenes del medio
usados en la operacin de copia de seguridad.
{NO_CHECKSUM| CHECKSUM}
NO_CHECKSUM
Deshabilita de forma explcita la generacin de
sumas de comprobacin de copia de seguridad (y
la validacin de sumas de comprobacin de
pgina). Es el comportamiento predeterminado,
salvo para una copia de seguridad comprimida.
CHECKSUM
Habilita las sumas de comprobacin de copia de
seguridad.
NO_TRUNCATE
Especifica que el registro no se va a truncar y
hace que Motor de base de datos intente hacer la
copia de seguridad con independencia del estado
de la base de datos. Por consiguiente, una copia
de seguridad realizada con NO_TRUNCATE
puede tener metadatos incompletos.

El siguiente ejemplo muestra cmo se consigue una copia de seguridad de la base de
datos Negocios2011 en forma complete en el disco

Use Negoci os2011
Go

BACKUP DATABASE Negoci os2011
TO DI SK = ' D: \ SQLNegoci os2011. Bak'
WI TH FORMAT;

El siguiente ejemplo muestra cmo se logra una copia de seguridad de la base de
datos Negocios2011 en forma diferencial en el disco

Use Negoci os2011
Go

WI TH DI FFERENTI AL;
go

190
En el ejemplo siguiente, se crea una copia de seguridad de archivos completa de cada
archivo en los dos grupos de archivos secundarios.

Use Negoci os2011
Go

FI LEGROUP = ' Sal esGr oup1' ,
FI LEGROUP = ' Sal esGr oup2'
Go

En el siguiente ejemplo, crea una copia de seguridad de archivos diferencial de todos
los archivos secundarios.

Use Negoci os2011
Go

FI LEGROUP = ' Sal esGr oup1' ,
FI LEGROUP = ' Sal esGr oup2'
WI TH DI FFERENTI AL;
Go

En el ejemplo siguiente, se realiza la copia de seguridad de la base de datos de
ejemplo Negocios2011, que usa de forma predeterminada un modelo de recuperacin
simple. Para admitir las copias de seguridad del registro, la base de datos
Negocios2011 se ha modificado para usar el modelo de recuperacin completa.

A continuacin, en el ejemplo se usa sp_addumpdevice para crear un dispositivo de
copia de seguridad lgico para realizar la copia de seguridad de datos, NegociosData,
y se crea un dispositivo de copia de seguridad lgico para copiar el registro,
NegociosLog.

A continuacin, en el ejemplo se crea una copia de seguridad de base de datos
completa en NegociosData y, tras un periodo de actividad de actualizacin, se copia el
registro en NegociosLog.

/ *Par a per mi t i r copi as de segur i dad de r egi st r o, ant es de l a copi a de
segur i dad compl et a, modi f i car l a base de dat os ut i l i ce f ul l r ecover y
model */

USE Negoci os2011;
GO
ALTER DATABASE Negoci os2011
SET RECOVERY FULL;
GO

- - - Cr ear Negoci osDat a y di sposi t i vos de copi a de segur i dad
Negoci osLog l gi ca.

EXEC sp_addumpdevi ce ' di sk' , ' Negoci osDat a' ,
' D: \ Negoci osDat a. bak' ;
GO

EXEC sp_addumpdevi ce ' di sk' , ' Negoci osLog' ,
' D: \ Negoci osLog. bak' ;
GO

- - Copi a de segur i dad de l a base de dat os compl et a Negoci os2011.
BACKUP DATABASE Negoci os2011 TO Negoci osDat a;
GO
- - Copi a de segur i dad del r egi st r o de Negoci os2011.
BACKUP LOG Negoci os2011
TO Negoci osLog;
GO

En el ejemplo siguiente, se da formato a los medios, que crean un nuevo conjunto de
medios y se realiza una copia de seguridad completa comprimida de la base de datos
Negocios2011.

192
Use Negoci os2011
Go

WI TH FORMAT, COMPRESSI ON;
Go

En el siguiente ejemplo, se crea un conjunto de medios reflejado que contiene una
sola familia de medios y cuatro reflejos, y se realiza una copia de seguridad de la base
de datos Negocios2011.

TO TAPE = ' \ \ . \ t ape0'
MI RROR TO TAPE = ' \ \ . \ t ape1'
WI TH FORMAT
Go

6.1.3.5 Realizar una copia de seguridad utilizando el SQL SERVER Management
Studio

1. Despus de conectarse a la instancia adecuada del motor de datos de Microsoft
SQL Server, en el Explorador de objetos, haga clic en el nombre del servidor para
expandir el rbol del servidor.
2. Expanda Bases de datos, seleccione una base de datos de usuario.
3. Haga clic derecho en la base de datos, seleccione Tareas y, a continuacin, haga
clic en copia de seguridad. La copia de seguridad de base de datos aparece el
cuadro de dilogo.


4. En el cuadro de lista Base de datos, compruebe el nombre de base de datos. Si
lo desea, puede seleccionar otra base de datos de la lista.
5. Usted puede realizar una copia de seguridad de base de datos para cualquier
modelo de recuperacin (FULL, BULK_LOGGED, o simple).
6. En el cuadro de lista Tipo de copia de seguridad, seleccione Completa.

7. Especificar cuando el conjunto de copia de seguridad caduca y pueden ser
anuladas sin saltar explcitamente la verificacin de los datos de caducidad.

194
8. Para que el conjunto de copia de seguridad caduque despus de un nmero
especfico de das, haga clic en Despus de (opcin predeterminada), e introduzca
el nmero de das despus de la creacin del conjunto en que expirar. Este valor
puede ser de 0 a 99999 das; un valor de 0 significa que el conjunto de copia de
seguridad nunca se expira.

9. Seleccione el tipo de destino de copia de seguridad, haga clic en Disco o Cinta.
Para seleccionar las rutas de hasta 64 unidades de disco o cinta que contengan un
conjunto de medios, haga clic en Agregar. Las rutas seleccionadas se muestran
en el cuadro de lista Copia de seguridad. Para eliminar un destino de copia de
seguridad, seleccione y haga clic en Quitar. Para ver el contenido de un destino
de copia de seguridad, seleccione y haga clic en Contenido.

10. Seleccionar las opciones avanzadas, haga clic en Opciones en el panel
Seleccionar una pgina.
11. Seleccione una opcin de Sobrescribir medios de comunicacin, haciendo clic
en: Copia de seguridad en el conjunto de medios existente
12. En la seccin de fiabilidad, de manera opcional de verificacin:
Comprobar copia de seguridad cuando haya terminado.
Realizar suma de comprobacin antes de escribir en los medios de
comunicacin.

6.1.4 Restaurando una copia de seguridad

Un escenario de restauracin es un proceso que restaura los datos de una o ms
copias de seguridad y se recupera la base de datos cuando la ltima copia de
seguridad se restaura.

6.1.4.1 Restore Transact-SQL

Restaura copias de seguridad realizadas con el comando BACKUP. Este comando le
permite realizar los siguientes escenarios de restauracin:

196
Restaurar una base de datos completa a partir de una copia de seguridad
completa de la base de datos (restauracin completa).
Restaurar parte de una base de datos (restauracin parcial).
Restaurar archivos o grupos de archivos en una base de datos.
Restaurar pginas especficas en una base de datos (restauracin de pginas).
Restaurar un registro de transacciones en una base de datos (restauracin del
registro de transacciones).
Revertir una base de datos al punto temporal capturado por una instantnea de
la base de datos.

La sintaxis para restaurar una base de datos desde una copia de seguridad completa

RESTORE DATABASE { nombr e_base_dat os | @var _nombr e_base_dat os }
[ FROM <backup_devi ce> [ , . . . n ] ]
[ WI TH
{
[ RECOVERY | NORECOVERY | STANDBY =
{st andby_f i l e_name | @st andby_f i l e_name_var }
]

La sintaxis para restaurar archivos especficos o Filegroups de la base de datos

RESTORE DATABASE { nombr e_base_dat os | @var _nombr e_base_dat os }
<f i l e_or _f i l egr oup> [ , . . . n ]
[ FROM < di sposi t i vo_backup > [ , . . . n ] ]
WI TH
{
[ RECOVERY | NORECOVERY ]
[ , <gener al _WI TH_opt i ons> [ , . . . n ] ]
} [ , . . . n ]
[ ; ]

La sintaxis para restaurar el registro de transacciones o LOG

RESTORE LOG { nombr e_base_dat os | @var _nombr e_base_dat os }
[ <f i l e_or _f i l egr oup_or _pages> [ , . . . n ] ]
[ FROM <ba di sposi t i vo_backup ckup_devi ce> [ , . . . n ] ]
[ WI TH {
[ RECOVERY | NORECOVERY | STANDBY =
{st andby_f i l e_name | @st andby_f i l e_name_var }
]

Argumentos

Argumento Descripcin
DATABASE
Especifica la base de datos de destino. Si se
especifica una lista de archivos y grupos de
archivos, slo se restauran esos archivos y
grupos de archivos.
LOG
Especifica que slo se va a aplicar una copia
de seguridad de registro de transacciones a
esta base de datos. Los registros de
transacciones deben aplicarse en orden
secuencial. Para aplicar varios registros de
transacciones, utilice la opcin
NORECOVERY en todas las operaciones de
restauracin.
{ nombr e_base_dat os |
@var _nombr e_base_dat os }
Es la base de datos para la que se realiza la
restauracin del registro o de la base de
datos completa. Si se proporciona como una
variable (@var_nombre_base_datos), este
nombre se puede especificar como una
constante de cadena
(@var_nombre_base_datos =database
name) o como una variable de un tipo de
datos de cadena de caracteres
<f i l e_or _f i l egr oup> [ ,. . . n ]

Especifica un grupo de archivos o un archivo
de que se van a incluir en una instruccin
RESTORE DATABASE o RESTORE LOG.
Puede especificar una lista de archivos o
grupos de archivos.
198
FROM <di sposi t i vo_backup> [
, . . . n ]
Especifica los dispositivos de copia de
seguridad desde los que se restaurar la
copia de seguridad. Alternativamente, en una
instruccin RESTORE DATABASE, la
clusula FROM puede especificar el nombre
de una instantnea de base de datos a la que
va a revertir la base de datos, en cuyo caso
no se admite ninguna clusula WITH.
Si se omite la clusula FROM, no se produce
la restauracin de la copia de seguridad. En
su lugar, se recupera la base de datos. Esto
permite recuperar una base de datos
restaurada con la opcin NORECOVERY o
cambiar a un servidor en espera.
.
<dispositivo_backup >
Especifica el dispositivo de copia de
seguridad fsico o lgico que se va a utilizar
para la operacin de copia de seguridad.
{device_logico | @var_device_logico }
Es el nombre lgico del dispositivo de copia
de seguridad en que se hace la copia de
seguridad de la base de datos. El nombre
lgico debe seguir las reglas definidas para
los identificadores.
{ DISK | TAPE } = { 'device_fisico' |
@var_device_fisico }
Especifica un archivo de disco o un
dispositivo de cinta.
DATABASE_SNAPSHOT
=database_snapshot_name

Revierte la base de datos a la instantnea de
base de datos especificada por
database_snapshot_name. La opcin
DATABASE_SNAPSHOT solo est
disponible para una restauracin de base de
datos completa. En una operacin de
reversin, la instantnea de base de datos
ocupa el lugar de una copia de seguridad de
base de datos completa.
En una operacin de reversin, se requiere
que la instantnea de base de datos
especificada sea la nica en la base de
datos. Durante la operacin de reversin, la
instantnea de base de datos y la base de
datos de destino se marcan como In restore. .

Opciones de WITH

Opcin Descripcin
PARTIAL Especifica una operacin de restauracin parcial
que solo restaura el grupo de archivos principal y
cualquiera de los grupos de archivos secundarios
especificados. La opcin PARTIAL selecciona
implcitamente el grupo de archivos principal; por
tanto, no es necesario especificar FILEGROUP =
'PRIMARY'. Para restaurar un grupo de archivos
secundarios, debe especificarlo de forma explcita
mediante la opcin FILE o FILEGROUP.
La opcin PARTIAL no se permite en las
instrucciones RESTORE LOG.
[ RECOVERY | NORECOVERY
| STANDBY ]

RECOVERY
Indica a la operacin de restauracin que revierta
las transacciones no confirmadas. Despus del
proceso de recuperacin, la base de datos est
preparada para ser utilizada, la opcin
predeterminada es RECOVERY.
NORECOVERY
Indica a la operacin de restauracin que no
revierta las transacciones no confirmadas. Si se
utiliza la opcin NORECOVERY durante una
operacin de restauracin sin conexin, la base
de datos no puede utilizarse.
STANDBY =standby_file_name
200
Especifica un archivo en espera que permite
deshacer los efectos de la recuperacin. La
opcin STANDBY se puede utilizar en
operaciones de restauracin sin conexin
(incluida la restauracin parcial).
{NO_CHECKSUM|CHECKSUM} NO_CHECKSUM
Deshabilita de forma explcita la generacin de
sumas de comprobacin de copia de seguridad (y
la validacin de sumas de comprobacin de
pgina). Es el comportamiento predeterminado,
salvo para una copia de seguridad comprimida.
CHECKSUM
Habilita las sumas de comprobacin de copia de
seguridad.

El siguiente ejemplo se restaura una copia de seguridad completa de la base de datos
desde un dispositivo lgico de copia de seguridad de la base de datos
Negocios2011Back

RESTORE DATABASE Negoci os2011
FROM Negoci os2011Backups
Go

En el siguiente ejemplo, se restaura una copia de seguridad completa despus de una
copia de seguridad diferencial del dispositivo de copia de seguridad
D:\SQLNegocios2011.Bak, que contiene las dos copias de seguridad. La copia de
seguridad de bases de datos completa que se va a restaurar es el sexto conjunto de
copias de seguridad del dispositivo (FILE =6), y la copia de seguridad de base de
datos diferencial es el noveno conjunto del dispositivo (FILE =9).

RESTORE DATABASE NEGOCI OS2011
FROM NEGOCI OS2011BACKUPS

FROM DI SK = ' D: \ NEGOCI OSDATA. BAK'
WI TH FI LE = 6, NORECOVERY;
GO

FROM DI SK = ' D: \ NEGOCI OSDATA. BAK'
WI TH FI LE = 9, RECOVERY;
GO

En el ejemplo siguiente, se restaura una base de datos completa y el registro de
transacciones, y se mueve la base de datos restaurada al directorio C:\Data.

RESTORE DATABASE Negoci os2011
WI TH NORECOVERY,
MOVE ' Negoci os2011_Dat a' TO ' C: \ Dat a\ Negoci os2011. mdf ' ,
MOVE ' Negoci os2011_Log' TO ' C: \ Dat a\ Negoci os2011. l df '
RESTORE LOG Negoci os2011
WI TH RECOVERY

En el ejemplo siguiente, se restaura el registro de transacciones hasta la marca de la
transaccin marcada denominada ActualizarPrecios.

USE NEGOCI OS2011;
GO

BEGI N TRANSACTI ON ACTUALI ZARPRECI OS
WI TH MARK ' UPDATE LI STA PRECI OS' ;
GO

UPDATE COMPRAS. PRODUCTOS
SET PRECI OUNI DAD *= 1. 10 WHERE NOMPRODUCTO LI KE ' BK- %' ;
GO

COMMI T TRANSACTI ON ACTUALI ZARPRECI OS;
GO

202
USE MASTER
GO

FROM NEGOCI OS2011BACKUPS WI TH FI LE = 3, NORECOVERY;
GO

RESTORE LOG NEGOCI OS2011
FROM NEGOCI OS2011BACKUPS WI TH FI LE = 4, RECOVERY,
STOPATMARK = ' ACTUALI ZARPRECI OS' ;

6.1.4.2 Restaurando una base de datos utilizando SQL SERVER Management
Studio

1. En el Explorador de objetos, expandir el rbol del servidor.
2. Expanda Bases de datos. Dependiendo de la base de datos, seleccione una
base de datos de usuario.
3. Haga clic derecho en la base de datos, seleccione Tareas y, a continuacin, haga
clic en Restaurar.
4. Haga clic en la base de datos, que se abre el cuadro de dilogo Restaurar base
de datos.

Para especificar el origen y la ubicacin de la copia de seguridad conjuntos para
restaurar, haga clic en una de las siguientes opciones:
Base de datos
Escriba un nombre de base de datos en el cuadro de lista.
Desde el dispositivo

Haga clic en el botn Examinar, que abrir el cuadro de dilogo Especificar copia de
seguridad. En el cuadro de lista Copia de seguridad de los medios de
comunicacin, seleccione uno de los tipos de dispositivo. Para seleccionar uno o
varios dispositivos del cuadro de lista Ubicacin de copia de seguridad, haga clic en
Agregar.

Despus de agregar los dispositivos que desee al cuadro de lista Ubicacin de copia
de seguridad, haga clic en Aceptar para regresar a la pgina General.

204
En el panel Opciones de restauracin, puede elegir cualquiera de las siguientes
opciones, si es apropiado para su situacin:

1. Sobrescribir la base de datos existente
2. Conservar la configuracin de la replicacin
3. Preguntar antes de restaurar cada copia de seguridad
4. Restringir el acceso a la base de datos restaurada


Resumen
El propsito de crear copias de seguridad de SQL Server es para que usted pueda
recuperar una base de datos daada. Sin embargo, copias de seguridad y
restauracin de los datos deben ser personalizados para un ambiente particular y
debe trabajar con los recursos disponibles. Por lo tanto, un uso fiable de copia de
seguridad y restauracin para la recuperacin exige una copia de seguridad y
restauracin de la estrategia.
La mejor opcin de modelo de recuperacin de la base de datos depende de los
requerimientos de su negocio. Para evitar la gestin del registro de transacciones
y simplificar el BACKUP y restauracin, utilice el modelo de recuperacin simple.
Para minimizar la prdida de trabajo, a costa de los gastos generales de
administracin, utilice el modelo de recuperacin completa
El alcance de una copia de seguridad de los datos puede ser una base de datos
completa, una base de datos parciales, o un conjunto de archivos o grupos de
archivos. Para cada uno de estos, SQL Server admite copias de seguridad
completas y diferenciales
Bajo el modelo de recuperacin optimizado para cargas masivas de registros de
modelo de recuperacin, copias de seguridad del registro de transacciones (o
copias de seguridad de registro) son obligatorias. Cada copia de seguridad de
registro cubre la parte del registro de transacciones que estaba activa cuando la
copia de seguridad fue creada, e incluye todos los registros que no fueron
respaldados en una copia de seguridad de registros anterior
BACK UP TRANSACT-SQL realiza copias de seguridad de una base de datos
completa, o uno o ms archivos o grupos de archivos (BASE DE DATOS DE
SEGURIDAD). Adems, bajo el modelo de recuperacin optimizado para cargas
masivas de registros de modelo de recuperacin, copias de seguridad del registro
de transacciones (BACKUP LOG).
Un escenario de restauracin es un proceso que restaura los datos de una o ms
copias de seguridad y se recupera la base de datos cuando la ltima copia de
seguridad se restaura

206
pginas.

Error! Referencia de hipervnculo no vlida. http://technet.microsoft.com/es-
es/library/ms186858.aspx
Aqu hallar los conceptos RESTORE TRANSACT SQL
Error! Referencia de hipervnculo no vlida.http://technet.microsoft.com/es-
es/library/ms186865.aspx
En esta pgina, hallar los conceptos de BACK UP TRANSACT SQL

http://translate.googleusercontent.com/translate_c?hl=es&prev=/search%3Fq%3DPl
anning%2Ba%2Bbackup%2Bstrategy%2Bin%2Bsql%2Bserver%26hl%3Des%26biw
%3D1280%26bih%3D619%26prmd%3Divns&rurl=translate.google.com.pe&sl=en&
u=http://msdn.microsoft.com/en-
us/library/ms187048.aspx&usg=ALkJ rhgYgMJ DrnvutDfU9eIJ 2B0a0Ps1mw
Aqu hallar los conceptos de copia de seguridad y recuperacin.

Base de Datos Avanzado I

Cargado por

Copyright:

Formatos disponibles

Base de Datos Avanzado I

Cargado por

Información del documento

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Base de Datos Avanzado I

Cargado por

Copyright:

Formatos disponibles

1

CARRERAS PROFESIONALES CIBERTEC

El nmero y el orden de las columnas deben ser idnticos en todas las

Los tipos de datos deben ser compatibles.

También podría gustarte