• Author / Uploaded
• Juan Pedro Santos Fernández

• Categories
• SQL
• Microsoft Sql Server
• Tabla (Base de datos)
• Tecnologías de la información
• Ciencias de la Información

Citation preview

ÍNDICES



Índices – Teoría y ejercicios By Trainer SQL in Índices

Índices en SQL Server Objeto que ordena los registros de una tabla o vista por uno o más campos de manera ascendente o descendente.

Importante 

Es recomendable crear índices para optimizar las consultas.



Se pueden crear un índice agrupado, que se crea de manera automática en la tabla con clave primaria (primary key) y 999 índices no agrupados en la misma tabla o vista.



En la lista de campos por el cual va a ordenar los registros de la tabla o vista pueden haber hasta 32 campos.



El orden puede ser ascendente (Asc) o descendente (Desc). El valor por defecto es Asc.



Las columnas con tipos de datos ntext,text, varchar (max), nvarchar(max), varbinary (max), xml, o imagen no se pueden crear índices.



Se puede crear índices para las tablas particionadas, estos índices se les llama índices particionados. (Ver índices particionados)

Sintaxis Para la creación de índices vamos a dividir la sintaxis de acuerdo al tipo de índice.

Crear un índice no agrupado Create nonclustered index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden]) go Crear un índice agrupado Create clustered index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden]) go Crear un índice único Create unique index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden]) go Crear un índice filtrado Create index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden]) where ExpresiónLógica go Crear un índice con factor de relleno Create index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden]) with fillfactor = Valor go Crear un índice y sobre escribir el existente Create index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden]) with drop_existing = on go

Ejercicios Usando la base de datos Norwhwind use northwind go 1. Crear un índice para la tabla Categories, campo CategoryName Create index CategoriaNombreIDX on Categories (CategoryName asc) go

2. Ver los índices de la tabla Categorias, suponer que los nombres de los índices comienzan con la palabra Categoria. select * from sys.indexes where name like ‘Categoria%’ go

3. Ver la estructura de la tabla, también se presentan la lista de indexados creados en la tabla. sp_help Categories go

4. Crear un índice para los productos que tengan un precio mayor a 30 Create index ProductosPrecioMas30IDX on Products(UnitPrice) where UnitPrice > 30 go 5. Crear un índice único para los productos por el campo ProductName Create unique index ProductosNombreIDX on Products(ProductName) go

6. Crear un índice para empleados por los campos LastName y FirstName Create index EmpleadosNombreCompletoIDX on Employees(LastName, FirstName) go 7. Crear un índice para el campo UnitsInStock en la tabla Products con factor de relleno de 70 Create index ProductosStockIDX on Products(UnitsInStock) with fillfactor = 70 go 8. Crear un índice para el nombre del cliente (campo CompanyName) en Customers. Asignar un factor de relleno de 70 y sobre escribir el existente. Usar la estructura If para comprobar que el índice existe if not exists (select * from sys.indexes where name = ‘ClientesNombreIDX’) Begin Create index ClientesNombreIDX on Customers(CompanyName) with fillfactor = 70 End else Begin Create index ClientesNombreIDX on Customers(CompanyName) with (fillfactor = 70, drop_existing = on) End go



Indices – Modificación By Trainer SQL in Índices

Modificar un índice Reindexar o reconstruir un índice es una operación que se sugiere para planes de mantenimiento, esto reordena los registros y puede optimizar las consultas.

Modificación de Índices Instrucción Alter Index Modifica un índice en una tabla o vista. Al modificar se puede deshabilitar, reorganizar (reconstruir) o cambiar sus opciones. Reconstruir un índice Alter index NombreIndice on Tabla/Vista Rebuild Reconstruir un índice modificando sus opciones Alter index NombreIndice on Tabla/Vista Rebuild with (Opciones)

Ejercicios Usando Northwind use Northwind go — Los indices de la tabla Categories, primero obtenemos el object_id de la tabla select T.object_id from sys.tables As T where name = ‘Categories’ go — El Object_id es 309576141

— Listar los índices de una tabla teniendo el valor del object_id de la misma select * from sys.indexes where object_id = ‘309576141’ go

— Reconstruir el índice CategoriaNombreIDX Alter index CategoriaNombreIDX on Categories Rebuild go — Reconstruir PK_Categories asignando un factor de relleno de 70 Alter index PK_Categories on Categories Rebuild with (fillfactor = 70) go — Reconstruir todos los índices de Products Alter index All on Products Rebuild go



Índices – Eliminación By Trainer SQL in Índices

Eliminando un índice La instrucción Drop Index permite eliminar los índices de una tabla o vista. La eliminación de un índice tiene que ser decidida con cuidado, si el índice permitía que alguna consulta sea más óptima, eliminarlo podría causar que las consultas y reportes en los sistemas sean más lentos.

Sintaxis Drop index NombreIndice on Tabla/Vista — Eliminar el índice CategoriaNombreIDX de la tabla Categories Drop index CategoriaNombreIDX on Categories go — Eliminar varios índices Drop index CompanyName on Customers, EmpleadosNombreCompletoIDX on Employees go





Indices – ejercicios By Trainer SQL in Índices

Indices Los índices permiten ordenar los registros de una tabla o vista de acuerdo a uno a mas campos en orden ascendente o descendente. (Ver índices) Al crear tablas particionadas es recomendable crear índices particionados.

Ejercicios Usando la base de datos Northwind use Northwind go Crear INDICES Create index NombreIndice on Tabla/Vista (Campo1 ASC|DESC [, Campo2 Asc|Desc]) with (fillfactor = Valor , drop_existing = on) */ — Crear índice para la tabla Categories, campo CategoryName create index CategoriaNombreIDXa on Categories(Categoryname) go — Listar los índices Select * from sys.indexes go — Buscar un índice específico

Select * from sys.indexes where name = ‘CategoriaNombreIDXa’ go — Script para crear el índice sólo si el índice no existe if not exists (Select * from sys.indexes where name = ‘CategoriaNombreIDXa’) Begin create index CategoriaNombreIDXa on Categories(Categoryname) End go RECOMENDACION: Usar un factor de relleno (fillfactor) de 80

— Índice para la tabla Empleados select * from Employees order by LastName, FirstName go — Índice para LastName + FirstName if not exists (Select * from sys.indexes where name = ‘EmpleadosApellidosNombresIDXa’) Begin Create index EmpleadosApellidosNombresIDXa on Employees (LastName, FirstName) with (fillfactor = 80) End else — índice ya existe Begin Create index EmpleadosApellidosNombresIDXa on Employees (LastName, FirstName) with (fillfactor = 80, drop_existing = on) End go

Eliminar un índice: Drop Index NombreIndice on Tabla/Vista */ — Eliminar el índice CategoriaNombreIDXa de la tabla Categories Drop index CategoriaNombreIDXa on Categories go 



Índices particionados By Trainer SQL in Bases de Datos, Índices

Índices particionados Los índices particionados son tipos de índices usados en tablas particionadas. (Ver Tablas particionadas).

Conceptos previos 

La partición facilita el uso de tablas e índices grandes, ya que permite administrar y tener acceso a subconjuntos de datos de forma rápida y eficaz, a la vez que mantiene la integridad de la recopilación de datos.



Si se utilizan las particiones, una operación como la carga de datos desde un sistema OLTP a un sistema OLAP tarda solo unos segundos, en lugar de los minutos y las horas que tardaba en versiones anteriores de SQL Server.



Las operaciones de mantenimiento que se realizan en los subconjuntos de datos también se realizan de forma más eficaz porque estas operaciones solo afectan a los datos necesarios, en lugar de a toda la tabla.



Las tablas e índices con particiones únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.



Los datos de tablas e índices con particiones se dividen en unidades que pueden propagarse por más de un grupo de archivos de la base de datos.



Los datos se dividen en sentido horizontal, de forma que los grupos de filas se asignan a particiones individuales.



La tabla o el índice se tratarán como una sola entidad lógica cuando se realicen consultas o actualizaciones en los datos. Las particiones de un índice o una tabla deben encontrarse en la misma base de datos.



Las tablas y los índices con particiones admiten todas las propiedades y características asociadas con el diseño y la consulta de tablas e índices estándar, incluidas las restricciones, los valores predeterminados, los valores de identidad y marca de tiempo, así como los desencadenadores. Por tanto, si desea implementar una vista con particiones local en un servidor, puede interesarle implementar en su lugar una tabla con particiones.



La decisión acerca del uso de las particiones depende básicamente del tamaño actual o futuro de la tabla, la forma en que se utiliza y el rendimiento que presenta en las consultas de usuario y las operaciones de mantenimiento.

Ejemplo Para crear índices particionados vamos a crear primero una tabla particionada. Este ejercicio muestra una tabla con 5 particiones en tres grupos de archivos, la base de datos está creada en las unidades C: y D: xp_create_subdir ‘C:\Parte’ go xp_create_subdir ‘D:\Logica’ go Create database Sistemas on Primary (name= ‘Sistemas01’, Filename = ‘C:\Parte\Sistemas01.mdf’), Filegroup VENTAS (name= ‘Sistemas02’, Filename = ‘C:\Parte\Sistemas02.ndf’), Filegroup RECURSOS (name= ‘Sistemas03’, Filename = ‘C:\Parte\Sistemas03.ndf’) log on (name= ‘Tran01’, Filename = ‘C:\Parte\Tran01.ldf’) go use Sistemas go La tabla particionada, esta requiere una función de partición y un esquema de partición. Create partition function ProductosFP (nchar(10)) as range for values (‘E’,’J’, ‘O’, ‘T’) go Create partition scheme ProductosEP as partition ProductosFP to (VENTAS, VENTAS, RECURSOS, [Primary], RECURSOS) go Create table Productos (ProductosCodigo nchar(10), ProductosDescripcion nvarchar(100), ProductosPrecio Numeric(9,2), ProductosStock Numeric(9,2) constraint ProductosPK Primary key (ProductosCodigo))

on ProductosEP (ProductosCodigo) go Insertar datos a la tabla, se tomarán los datos de la tabla Products de Northwind insert into Productos select ProductID, ProductName, UnitPrice, UnitsInStock from Northwind.dbo.Products go Generar un nuevo código Primero mostrar el código. select ProductosCodigo, UPPER(left(ProductosDescripcion,4)+ Rtrim(Ltrim(iif(len(ProductosCodigo)=1,’000’+Ltrim(ProductosCodigo), ’00’+Ltrim(ProductosCodigo))))) + Rtrim(LTrim(‘PR’)) As ‘Nuevo Código’ from Productos go Recorrer los productos y actualizar los códigos. Usaremos un cursor (Ver Cursores) Declare cursorActualizaCodigos cursor for select * from Productos Open cursorActualizaCodigos Declare @Codigo nchar(10), @Descripcion nvarchar(100), @Precio Numeric(9,2), @Stock Numeric(9,2) Fetch cursorActualizaCodigos into @Codigo , @Descripcion, @Precio , @Stock While (@@FETCH_STATUS = 0) Begin Update Productos set ProductosCodigo = UPPER(left(@Descripcion,4)+ Rtrim(Ltrim(iif(len(@Codigo)=1,’000’+Ltrim(@Codigo), ’00’+Ltrim(@Codigo))))) + Rtrim(LTrim(‘PR’)) where ProductosCodigo = @Codigo Fetch cursorActualizaCodigos into @Codigo , @Descripcion, @Precio , @Stock End Close cursorActualizaCodigos Deallocate cursorActualizaCodigos go Ver los datos y las particiones donde se guardaron select ProductosCodigo, ProductosDescripcion, $Partition.ProductosFP(ProductosCodigo) As ‘Partición’ from Productos go

Índice particionado Create index ProductosIDXParticionado on Productos (ProductosCodigo) include (ProductosDescripcion) on ProductosEP (ProductosCodigo) go Al final de la creación del índice se incluye la cláusula on seguido del nombre del esquema de partición y el campo particionado. Note que para hacer mas eficiente el índice se puede incluir (usando include) el campo Descripción. (Ver Include en Índices)



Uso de Include en Índices By Trainer SQL in Índices

Como usar Include en Índices no agrupados El uso de include en los índices permite ampliar la funcionalidad de los índices no agrupados al incluir campos en la construcción del índice para lograr mejorar el rendimiento de las consultas.

Beneficios del uso de Include 

Se pueden usar tipos de datos no permitidos en índices.



No son considerados como una columna mas en la cantidad de columnas del índice o en el tamaño del índice. La cantidad de columnas máxima en un índice es 32.



Recuerde que el tamaño máximo del índice es de 1,700 bytes.



Aumenta la efectividad de las consultas

Limitaciones 

Se usan en sólo índices no agrupados



Se pueden usar todos los tipos de datos excepto text, ntext, and image



No se pueden eliminar las columnas incluidas con Include salvo que se elimine primero el índice.

Ejercicios Usando la base de datos Northwind use Northwind go

1. Crear un índice para la tabla productos por la descripción, incluir los campos Unidad y Precio Create index ProductosDescripcionIDXinUnidadPrecio on Products (ProductName) Include ([QuantityPerUnit],[UnitPrice]) go 2. Crear un índice para Empleados por LastName y FirstName, incluir Title y Address Create index EmpleadosCargoDireccionIDXi on Employees (LastName, FirstName) Include (Title , Address) go Las imágenes siguientes muestran las propiedades del índice, para ver estas propiedades despliegue el nodo índices en la tabla Employees, luego puede pulsar doble click en el índice que desea ver sus propiedades.

Ver los índices creados Para visualizar los índices creados se puede filtrar de acuerdo al nombre. select * from sys.indexes where name like ‘Empleados%’ or name like ‘Productos%’ go 3. Crear un índice para Clientes (Customers) para el campo ContactName, incluir Country, Region y City. Asignar factor de relleno de 70 y dar consistencia si existe. if not exists (select * from sys.indexes where name = ‘ClientesContactoIDX’) Begin Create index ClientesContactoIDX on Customers([ContactName]) include (Country, Region , City) with fillfactor = 70 End Else Begin Create index ClientesContactoIDX on Customers([ContactName]) include (Country, Region , City)

with (fillfactor = 70, drop_existing = on) End go

Como cambiar el nombre del índice en SQL Server Para cambiar el nombre de un objeto en la base de datos se utiliza el procedimiento almacenado sp_rename, especìficamente para cambiar el nombre a un índice la sintaxis es como sigue Execute sp_rename ‘Tabla.NombreIndiceOriginal’, ‘NuevoNombre’, ‘INDEX’ go Ejercicios 1. Cambiar el nombre del índice ProductosDescripcionIDXinUnidadPrecio por ProductosNombreUnidadPrecioIDX Execute sp_rename ‘Products.ProductosDescripcionIDXinUnidadPrecio’, ‘ProductosNombreUnidadPrecioIDX’, ‘Index’ go — Resultado Precaución: al cambiar cualquier parte del nombre de un objeto pueden dejar de ser scripts válidos y procedimientos almacenados.





Como crear vistas indizadas en SQL Server By Trainer SQL in Índices

Como crear indices en Vistas en SQL Server 

Las vistas son objetos en SQL Server que permiten guardar el resultado de una consulta con el objetivo de tenerla disponible mas rápidamente. Las vistas al crearse guardan el resultado en una estructura no indizada por lo que al listar una vista con algún tipo de orden el consumo de recursos de SQL Server puede perjudicar el rendimiento del servidor. En este artículo se mostrará como crear vistas indizadas en SQL Server.



Después de crear vistar (Ver Vistas) es recomendable crear en esta un índice agrupado como su clave primaria y los índices no agrupados necesarios para realizar las consultas en la vista de manera mas óptima.



Para crear índices en la vista se crea Create Index de igual forma que se utilizan en la creación de índices en tablas. (Ver Índices)



La vista tiene que estar ligada a un esquema, por lo que al crear la vista debemos incluir la cláusula With SchemaBinding. Al crear la vista, debe incluirse de manera obligatoria el esquema donde se encuentra la tablas o tablas origen de esta.



Al crear un índice agrupado en la vista debe ser único.



Se recomienda encriptar la vista para mayor seguridad.

Usando la base de datos Northwind use Northwind go

Ejercicios

1. Crear una vista para los productos Create view vistaProductosListaPrecio With SchemaBinding As select P.ProductID As ‘Código’, P.ProductName As ‘Descripción’, P.UnitPrice As ‘Precio’, P.UnitsInStock As ‘Stock’ from dbo.Products As P go

Como ver la estructura de la vista sp_help vistaProductosListaPrecio go Puede notar que no tiene índices, lo que hará que las consultas en la vista sean lentas.

Creando el índice agrupado para la vista Create unique clustered index vistaProductosListaPrecioIDX on vistaProductosListaPrecio (Código) go Ver nuevamente la estructura de la vista sp_help vistaProductosListaPrecio go

Crear índices para los otros campos Create index vistaProductosDescripcionIDX on vistaProductosListaPrecio (Descripción) go Create index vistaProductosDescripcionTodosIDX on vistaProductosListaPrecio (Descripción) include (Precio, Stock) go Note que se ha incluído los campos Precio y Stock. Ver Include en Índices

2. Crear una vista con los clientes Create view vistaClientesLista With SchemaBinding As select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’, C.ContactName As ‘Contacto’, C.Address As ‘Dirección’, C.Phone As ‘Teléfono’ from dbo.Customers As C go

Creando el índice para la vista Create unique clustered index vistaClientesListaCodigoIDX on vistaClientesLista (Código) go

Índices para los otros campos de la vista Create index vistaClientesListaNombreIDX on vistaClientesLista (Cliente) go

Se puede incluir la cláusula Include para optimizar las consultas en la vista Create index vistaClientesListaNombreDireccionContactoIDX on vistaClientesLista (Cliente) Include (Dirección, Contacto) go

PROGRAMACIÓN Procedimientos Almacenados 

By Trainer SQL in Programación

Procedimientos Almacenados Un procedimiento almacenado son instrucciones T-SQL almacenadas con un nombre en la base de datos.



Los procedimientos almacenados se pueden utilizar para Devolver un conjunto de resultados, se puede incluir parámetros de entrada para especificar el filtro del conjunto resultado.



Ejecutar instrucciones de programación.



Devolver valores numéricos que permiten realizar acciones cuando un grupo de instrucciones se realizó con éxito o no.

Ventajas del uso de procedimientos almacenados Reutilización del código El encapsulamiento en un procedimiento es óptimo para reutilizar su código. Se elimina la necesidad de escribir el mismo código, se reducen inconsistencias en el código y permite que cualquier usuario ejecute el código aún sin tener acceso a los objetos que hace referencia.

Mayor seguridad Se pueden ejecutar SP con instrucciones que hacen referencia a objetos que los usuarios no tienen permisos. El procedimiento realiza la ejecución del código y todas las instrucciones y controla el acceso a los objetos a los que hace referencia. Esto hace mas sencillo la asignación de permisos. Se puede implementar la suplantación de usuarios usando Exexute As. Existe un nivel fuerte de encapsulamiento. Tráfico de red reducido Un SP se ejecuta en un único lote de código. Esto reduce el tráfico de red cliente servidor porque únicamente se envía a través de la red la llamada que ejecuta el SP. La encapsulación del código del SP permite que viaje a través de la red como un solo bloque. Mantenimiento más sencillo Se puede trabajar en los aplicativos en base a capas, cualquier cambio en la Base de datos, hace sencillo los cambios en los procedimientos que hacen uso de los objetos cambiados en la BD. Rendimiento mejorado Los procedimientos almacenados se compila la primera vez que se ejecutan y crean un plan de ejecución que vuelve a usarse en posteriores ejecuciones.

Tipos de procedimientos Definidos por el usuario Se crea por el usuario en las bases de datos definidas por el usuario o en las de sistema (Master, Tempdb, Model y MSDB) Procedimientos almacenados Temporales Los procedimientos temporales son procedimientos definidos por el usuario, estos se almacenan en tempdb. Existen dos tipos de procedimientos temporales: locales (primer caracter es #) y globales (primer caracter ##). Se diferencian entre sí por los nombres, la visibilidad y la disponibilidad. Los procedimientos temporales locales tienen como primer carácter de sus nombres un solo signo de número (#); solo son visibles en la conexión actual del usuario y se eliminan cuando se cierra la conexión. Los procedimientos temporales globales presentan dos signos de número (##) antes del nombre; lo pueden usar todos los usuarios conectados, se eliminan cuando se desconectan todos los usuarios. Procedimientos Almacenados del Sistema Los procedimientos del sistema son propios de SQL Server. Los caracteres iniciales de estos procedimientos son sp_ la cual no se recomienda para los procedimientos almacenados definidos por el usuario. Extendidos definidos por el usuario Los procedimientos extendidos tienen instrucciones externas en un lenguaje de

programación como puede ser C. Estos procedimientos almacenados son DLL que una instancia de SQL Server puede cargar y ejecutar dinámicamente.

Sintaxis Para crear un procedimiento almacenado se utiliza Create procedure NombreProcedimiento ( @PrimerParametro TipoDato, @SegundoParametro TipoDato,… ) As Instrucciones del SP go Para modificar un SP Alter procedure NombreProcedimiento ( @PrimerParametro TipoDato, @SegundoParametro TipoDato,… cambios ) As Instrucciones del SP con cambios go Eliminar un SP Drop procedure NombreProcedimiento go Para listar los SP select * from sys.procedures go

Ejercicios Use Northwind go — Procedimiento para listar los productos Create procedure spProductosListadoPrecios As Select P.ProductID, P.ProductName, P.UnitPrice , P.UnitsInStock from Products As P go Ejecutar el Store Procedure creado Execute spProductosListadoPrecios go

— Procedimiento para insertar un registro en la tabla Shippers — La instrucción para insertar un Shipper es: insert into Shippers (CompanyName, Phone) values (‘Tolva Couriers’,’954542452′) go El SP para insertar Shippers se crea de la siguiente forma. create procedure spShippersInsertaNuevo ( @NombreEmpresa nvarchar(40), @Fono nvarchar(24) ) As insert into Shippers (CompanyName, Phone) values (@NombreEmpresa,@Fono) go Ejecutar el SP, se puede ejecutar de las siguiente formas: Execute spShippersInsertaNuevo ‘Chasqui’,’87545852′ go Execute spShippersInsertaNuevo @Fono = ‘345435645’, @NombreEmpresa =’Ford’ go Execute spShippersInsertaNuevo @NombreEmpresa =’Turbo XD’, @Fono = ‘8569856’ go — Procedimiento para el listado de productos de una determinada categoría Create procedure spProductosListadoPorCategoria ( @CategoriaCodigo int ) As select P.ProductID, P.ProductName, P.UnitPrice , P.UnitsInStock, P.UnitsOnOrder from Products As P where CategoryID = @CategoriaCodigo go Ejecutar el SP Productos de categoria 2 Execute spProductosListadoPorCategoria 2 go

Modificar el procedimiento de Listado de Productos por categoría que lo muestre ordenados por precio descendente Alter procedure spProductosListadoPorCategoria ( @CategoriaCodigo int ) As

select P.ProductID, P.ProductName, P.UnitPrice , P.UnitsInStock, P.UnitsOnOrder from Products As P where CategoryID = @CategoriaCodigo order by P.UnitPrice desc go — Procedimiento para crear una tabla con los productos de una determinada categoría Create procedure spCreaTablaProductosDeCategoria ( @CodigoCategoria int ) As Declare @NombreTabla nvarchar(40), @DropTablaTSQL nvarchar(100),@CrearTablaTSQL nvarchar(100) set @NombreTabla = ‘ProductosDeCategoria’+ LTRIM(STR(@CodigoCategoria)) Set @DropTablaTSQL = ‘Drop Table ‘+ @NombreTabla Set @CrearTablaTSQL = ‘select * into ‘+ @NombreTabla + ‘ from Products where CategoryID = ‘+LTRIM(STR(@CodigoCategoria)) if exists (select * from sys.tables where name = @NombreTabla) Begin Execute(@DropTablaTSQL) End Execute(@CrearTablaTSQL) go Ejecutar para la categoría 3 Execute spCreaTablaProductosDeCategoria 3 go Ver los registros select * from Productosdecategoria3 go



Procedimientos Almacenados – Ejercicio By Trainer SQL in Programación

Procedimientos Almacenados Ejercicio para el uso de los procedimientos almacenados con los datos de una tabla. En este ejercicio se crea una tabla para carreras en la Universidad SQL, se crean los procedimientos almacenados para insertar un registro, modificar los datos del registro, listar los registros ordenados por descripción y borrar un registro. El borrado del registro es lógico. (Ver Eliminación de registros)

Desarrollo del ejemplo Create database UniSQL go use UniSQL go Create table Carreras ( CarrerasCodigo nchar(5), CarrerasDescripcion nvarchar(100), CarrerasAcreditada nchar(1), CarrerasVacantes Numeric(9,2), CarrerasEstado nchar(1), constraint CarrerasPK Primary key (CarrerasCodigo) ) go

— Insertar registros insert into Carreras values (‘95642′,’Ing. de SISTEMAS’,’S’,250,’A’), (‘28596′,’ADMINISTRACION’,’S’,290,’A’), (‘05252′,’Ing. INDUSTRIAL’,’S’,450,’A’), (‘78596′,’MEDICINA’,’N’,100,’A’) go

— Procedimientos para Insertar, Editar y Eliminar — Insertar Create procedure spCarrerasInsertarNuevo ( @CarrerasCodigo nchar(5), @CarrerasDescripcion nvarchar(100), @CarrerasAcreditada nchar(1), @CarrerasVacantes Numeric(9,2), @CarrerasEstado nchar(1) ) As insert into Carreras (CarrerasCodigo, CarrerasDescripcion, CarrerasAcreditada, CarrerasVacantes, CarrerasEstado) values (@CarrerasCodigo, @CarrerasDescripcion, @CarrerasAcreditada, @CarrerasVacantes, @CarrerasEstado) go Prueba del SP para insertar Exec spCarrerasInsertarNuevo ‘65285’,’PSICOLOGIA’,’S’,130,’A’ go También se puede ejecutar de la siguiente manera: Exec spCarrerasInsertarNuevo @CarrerasVacantes =80 ,@CarrerasEstado =’A’,@CarrerasCodigo =’96325′, @CarrerasDescripcion=’CONTABILIDAD’,@CarrerasAcreditada=’S’ go Listado de Carreras SELECT * FROM Carreras go Crear un Índice por Descripción de la carrera (Ver Índices) create index CarrerasDescripcionIDXa on Carreras(CarrerasDescripcion) with (fillfactor = 80) go

Procedimientos para Listado, note que no aparecen los que tienen en el estado la letra E que indica que han sido eliminadas. Create procedure spCarrerasListado As select CarrerasCodigo, CarrerasDescripcion, CarrerasAcreditada, CarrerasVacantes, CarrerasEstado from Carreras where CarrerasEstado ‘E’ order by CarrerasDescripcion go Procedimiento almacenado para editar una carrera Create procedure spCarrerasEditar ( @CarrerasCodigo nchar(5), @CarrerasDescripcion nvarchar(100), @CarrerasAcreditada nchar(1), @CarrerasVacantes Numeric(9,2) ) As update Carreras set CarrerasDescripcion = @CarrerasDescripcion, CarrerasAcreditada =@CarrerasAcreditada , CarrerasVacantes =@CarrerasVacantes where CarrerasCodigo = @CarrerasCodigo go

Ejecutar el procedimiento para actualiza la carrera Medicina con los siguiente datos: Descripción: MEDICINA HUMANA, está acreditada y el número de vacantes es 150 Execute spCarrerasEditar ‘78596’,’MEDICINA HUMANA’,’S’,150,’A’ go Procedimiento almacenado para eliminar una carrera, la eliminación es lógica, cambia el estado. Create procedure spCarrerasEliminar ( @CarrerasCodigo nchar(5) ) As update Carreras set CarrerasEstado = ‘E’ where CarrerasCodigo = @CarrerasCodigo go

Ejecutar el procedimiento para borrar el registro 89456. spCarrerasEliminar ‘89456’ go

Procedimientos Almacenados con parámetros de salida 

By Trainer SQL in Consultas, Programación, Registros - Vistas

Procedimientos Almacenados con parámetros de salida Los procedimientos almacenados son bloques de código reutilizable guardados en la base de datos que tienen un propósito. (Ver Procedimientos Almacenados) Existen procedimientos almacenados que no tienen parámetros, es decir, no necesitan de ningún valor para que se ejecuten, las tareas que realizan estos generalmente son sencillas.

Parámetros 

Los parámetros se usan para intercambiar datos entre las aplicaciones y los procedimientos almacenados o la herramienta que ejecutó el procedimiento almacenado.



Los parámetros de entrada permiten pasar un valor de datos al procedimiento almacenado.



Los parámetros de salida permiten al procedimiento almacenado devolver un valor



Los procedimientos almacenados devuelven un código de retorno de tipo entero.



El valor de retorno por defecto es CERO si no se establece explícitamente un valor diferente

Ejemplos Los siguientes ejemplos muestran como usar los parámetros de salida en procedimientos almacenados. Usando Northwind use Northwind go Ejercicio 1 Procedimiento almacenado que reporta el número de productos de una determinada categoría Create procedure spCategoriasCantidadProductos (@CategoriaCodigo int, @CantidadProductos Numeric(9,2) output) As Select * from Products where CategoryID = @CategoriaCodigo Set @CantidadProductos = @@ROWCOUNT go Para ejecutar el procedimiento debemos crear antes una variable que permita capturar el valor devuelto por el parámetro de salida del procedimiento. Tomaremos como ejemplo la categoría 3. Declare @Cantidad int Execute spCategoriasCantidadProductos 3, @Cantidad output select @Cantidad

Ejercicio 2 Procedimiento almacenado que reporta la cantidad de productos que es

necesario comprar con urgencia. (Productos cuyas Unidades en Stock es menor a las unidades en Orden) Create procedure spCantidadProductosComprarUrgente (@CantidadProductos Numeric(9,2) output) As Select * from Products where UnitsOnOrder > UnitsInStock Set @CantidadProductos = @@ROWCOUNT go Para ejecutar el procedimiento debemos crear antes una variable que permita capturar el valor devuelto por el parámetro de salida del procedimiento. Declare @CantidadPorComprar int Execute spCantidadProductosComprarUrgente @CantidadPorComprar output select @CantidadPorComprar



Controlando Errores en SQL Server By Trainer SQL in Funciones en SQL Server, Programación

Manejando errores en SQL Server Al usar el lenguage Transact-SQL debemos tener en cuenta, como en cualquier lenguaje de programación, que algunas instrucciones nos pueden dar errores debido a los valores de parámetros de entrada incorrectos o faltantes, ingresos de datos con tipos incorrectos, falta de datos en un procedimiento o función definida por el usuario o de manera general en una transacción no finalizada de manera correcta.

Funciones para el manejo de errores Las funciones para el manejo de errores permiten conocer los parámetros que reporta un error, las funciones son las siguientes:

Función

Descripción

ERROR_NUMBER()

Devuelve el número de error.

ERROR_SEVERITY()

Devuelve la severidad del error.

ERROR_STATE()

Devuelve el estado del error.

ERROR_PROCEDURE()

Devuelve el nombre del procedimiento a

ERROR_LINE()

Devuelve el número de línea en el que se

ERROR_MESSAGE()

Devuelve el mensaje de error.

Ejemplo El siguiente ejemplo muestra una división entre CERO, lo que arroja error, luego se muestran los valores de cada función. Se utiliza la estructura Try Catch cuya explicación está al final de este artículo. BEGIN TRY DECLARE @Valor1 Numeric(9,2),@Valor2 Numeric(9,2), @Division Numeric(9,2) SET @Valor1 = 100 SET @Valor2 = 0 SET @Division = @Valor1/@Valor2 PRINT ‘La división no reporta error’ END TRY BEGIN CATCH select ERROR_NUMBER() As ‘Nº de Error’, ERROR_SEVERITY() As ‘Severidad’, ERROR_STATE() As ‘Estado’, ERROR_PROCEDURE() As ‘Procedimiento’, ERROR_LINE() As ‘Nº línea’, ERROR_MESSAGE() As ‘Mensaje’ END CATCH

La Función @@ERROR La función @@ERROR almacena el número de error producido por la última sentencia Transact SQL ejecutada, si no se ha producido ningún error el valor de la función es CERO. Se puede usar esta función para controlar los errores usando una estructura If

Ejemplo: El siguiente ejemplo muestra una división entre CERO, lo que arroja error, luego se da consistencia al error con una estructura If DECLARE @Valor1 Numeric(9,2),@Valor2 Numeric(9,2), @Division Numeric(9,2) SET @Valor1 = 100 SET @Valor2 = 0 SET @Division = @Valor1/@Valor2 If @@ERROR = 0 Begin Print ‘El resultado es: ‘ + Str(@Division) Print ‘No hubo error’ End Else Begin Print ‘Error al dividir entre CERO’ End go Probamos el mismo código con el Valor2 igual a 2 DECLARE @Valor1 Numeric(9,2),@Valor2 Numeric(9,2), @Division Numeric(9,2) SET @Valor1 = 100 SET @Valor2 = 2 SET @Division = @Valor1/@Valor2 If @@ERROR = 0 Begin Print ‘El resultado es: ‘ + Str(@Division) Print ‘No hubo error’ End Else Begin Print ‘Error al dividir entre CERO’ End go

Niveles de severidad del error Nivel de gravedad

Descripción

0-9

Mensajes informativos que devuelven información de estado o informan sobre errores q de 0 a 9.

Nivel de gravedad

Descripción

10

Mensajes informativos que devuelven información de estado o informan sobre errores q gravedad 10 en gravedad 0 antes de devolver la información de errores a la aplicación q

11-16

Indica errores que pueden ser corregidos por el usuario.

11

Indica que un objeto o una entidad determinados no existen.

12

Una gravedad especial para consultas que no utilizan bloqueo debido a sugerencias de c instrucciones podrían producir datos incoherentes, ya que no se adoptan bloqueos para g

13

Indica errores de interbloqueo de transacciones.

14

Indica errores relacionados con la seguridad, como es el caso de permisos denegados.

15

Indica errores de sintaxis en el comando Transact-SQL .

16

Indica errores generales que pueden ser corregidos por el usuario.

17-19

Indica errores de software que no pueden ser corregidos por el usuario. Informe al admi

17

Indica que la instrucción ha hecho que SQL Server se quede sin recursos (como, por eje los límites establecidos por el administrador del sistema.

18

Indica un problema en el software de Motor de base de datos pero la instrucción comple administrador del sistema debe ser informado cada vez que se produce un mensaje con u

Nivel de gravedad

Descripción

19

Indica que se ha superado un límite de Motor de base de datos no configurable y el proc detienen la ejecución del lote actual. Los errores de nivel de gravedad 19 son poco frecu asistencia técnica. Póngase en contacto con el administrador del sistema cuando se prod entre 19 y 25 se escriben en el registro de errores.

20-24

Indica problemas del sistema y son errores irrecuperables, lo que significa que ya no est lote. La tarea registra información sobre lo acontecido y, a continuación, finaliza. En la delMotor de base de datos . Si esto ocurre, dependiendo del problema, la aplicación pod

Los mensajes de error incluidos en este intervalo pueden afectar a todos los procesos qu u objeto está dañado. Los mensajes de error con nivel de gravedad entre 19 y 24 se escri 20

Indica que una instrucción ha encontrado un problema. Puesto que el problema ha afecta

21

Indica que se ha encontrado un problema que afecta a todas las tareas de la base de dato

22

Indica que la tabla o índice especificado en el mensaje ha sido dañado por un problema

Los errores de nivel de gravedad 22 se producen en raras ocasiones. Si se produce uno d dañados. Es posible que el problema se encuentre solo en la caché del búfer y no en el p problema. Para seguir trabajando, deberá volver a conectarse a la instancia del Motor de ocasiones, puede que tenga que restaurar la base de datos.

Si al reiniciar la instancia del Motor de base de datos no se corrige el problema, el probl en el mensaje de error. Por ejemplo, si el mensaje le indica que la instancia del Motor de índice y vuelva a generarlo. 23

Indica que se sospecha de la integridad de toda la base de datos debido al daño causado

Los errores de nivel de gravedad 23 se producen en raras ocasiones. Si se produce algun problema se encuentre solo en la caché y no en el propio disco. Si es así, al reiniciar la i volver a conectarse a la instancia del Motor de base de datos; en caso contrario, utilice D base de datos. 24

Indica un error en los medios. Puede que el administrador del sistema tenga que restaura

Mensajes de error SQL Server tienen una vista de catálogo con los mensajes definidos por defecto, la vista es sys.messages, a la cual se le pueden añadir mensajes de error con

sus parámetros respectivos usando el procedimiento almacenado sp_addmessage. El procedimiento almacenado sp_addmessage permite agregar a la vista de catálogo sys.messages mensajes de error definidos por el usuario con niveles de gravedad de 1 a 25. Use Raiserror para utilizar los mensajes de error definidos por el usuario. RAISERROR puede hacer referencia a un mensaje de error definidos por el usuario almacenados en la vista de catálogo sys.messages o puede generar un mensaje dinámicamente. Si se usa el mensaje de error definido por el usuario de sys.messages mientras se genera un error, la gravedad especificada por RAISERROR reemplazará a la gravedad especificada en sys.messages. Para visualizar los mensajes de la vista de catálogo Sys.messages puede ejecutar la siguiente instrucción. select * from sys.messages go

El procedimiento sp_addmessage Permite agregar un mensaje de error definido por el usuario a la vista de catálogo sys.messages Sintaxis: sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severidad , [ @msgtext= ] ‘mensaje’ [ , [ @lang= ] ‘languaje’ ] Donde: [ @msgnum= ] msg_id Especifica el Id del mensaje, se pueden iniciar en 50001,

el valor máximo es 2,147,483,647. [ @severity= ] severidad Indica el nivel de gravedad del error, puede ser un valor entre 1 y 25. [ @msgtext= ] ‘mensaje‘ Especifica el mensaje definido por el usuario. [ @lang= ] ‘languaje’ Especifica el lenguaje.

Ejemplos: Ejercicio 1 Agregar el mensaje para indicar que un porcentaje de descuento puede ser entre 0 y 25%. Note que es necesario insertar el mensaje para el idioma inglés y así poder agregar el mensaje para español. Use master go Execute sp_addmessage 50001, 16, ‘The discount percentage should be between 0 and 25%’, ‘us_english’ , false, replace Execute sp_addmessage 50001, 16, ‘El porcentaje de descuento debe ser entre 0 y 25%’, ‘Spanish’ , false, replace go Ejercicio 2 Agregar el mensaje para indicar que un precio debe ser CERO o mayor. Note que es necesario insertar el mensaje para el idioma inglés y así poder agregar el mensaje para español. Use master go Execute sp_addmessage 50002, 16, ‘The price should be 0 or greater’, ‘us_english’ , false, replace Execute sp_addmessage 50002, 16, ‘El precio debe ser 0 o mayor’, ‘Spanish’ , false, replace go

Procedimientos almacenados para los mensajes de error definidos por el usuario Procedimiento almacenado sp_altermessage Modifica el estado de los mensajes definidos por el usuario o del sistema en una instancia del motor de base de datos de SQL Server. Sintaxis:

sp_altermessage [ @message_id = ] message_number ,[ @parameter = ]’write_to_log’ ,[ @parameter_value = ]’value’ Donde: [ @message_id = ] message_number Especifica el Id del mensaje [ @parameter = ]’write_to_log‘ Especifica si se va a escribir en el log de Windows [ @parameter_value = ]’value’ Se utiliza con @parameter para indicar que el error debe escribirse en el registro de aplicación de Microsoft Windows Ejemplo El siguiente ejemplo permite especificar que el mensaje de error creado se escriba en el log de Windows. sp_altermessage 50001 , ‘with_log’ ,’true’ go Procedimiento almacenado sp_dropmessage Elimina un mensaje de error definido por el usuario Sintaxis: sp_dropmessage [ @msgnum = ] message_number [ , [ @lang = ] ‘language’ ] Donde: [ @msgnum = ] message_number Especifica el Id del mensaje [ @lang = ] ‘language’ Especifica el lenguaje. Ejemplo El siguiente código elimina el mensaje creado con Id 50001 sp_dropmessage 50001, ‘Spanish’ go

Controlando los errores Usando Try Catch 

Permite implementar el manejo de errores para Transact-SQL, esta estructura es similar a la de los lenguajes de programación.



La sentencias Transact-SQL que pueden dar error se incluyen en el bloque TRY. Si se produce un error en el bloque TRY, el control se pasa a otro grupo de sentencias incluidas en un bloque CATCH. Sintaxis:

BEGIN TRY Instrucciones Transact – SQL que pueden dar error END TRY BEGIN CATCH Instrucciones Transact – SQL para manejar el error END CATCH Ejemplo: Usando la base de datos Northwind, listar los registros de la tabla Ventas, tenga en cuenta que la tabla no existe. El select se incluirá en un procedimiento almacenado. Use Northwind go El procedimiento almacenado Create procedure spListaVentas As SELECT * FROM Ventas go Ahora ejecutamos el procedimiento almacenado. BEGIN TRY Execute spListaVentas END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ‘Nº Error’, ERROR_MESSAGE() AS ‘Mensaje’ END CATCH



Create procedure, Error, Message, Try Catch



Cursores By Trainer SQL in Programación

Como crear cursores en SQL Server Los cursores permiten almacenar los datos de una consulta T-SQL en memoria y poder manipular los datos de los elementos resultantes para realizar operaciones con ellos. Proceso para declarar, abrir, usar, cerrar y liberar los datos de un cursor 1. Declarar el cursor, utilizando DECLARE Cursor 2. Abrir el cursor, utilizando OPEN 3. Leer los datos del cursor, utilizando FETCH … INTO 4. Cerrar el cursor, utilizando CLOSE 5. Liberar el cursor, utilizando DEALLOCATE

Como crear un cursor en SQL Server 1. Para declarar el cursor DECLARE NombreCursor [Scroll] CURSOR FOR Instrucción Select 2. Abrir el cursor OPEN NombreDelCursor 3. Lectura de los datos del cursor, va a depender del tipo de cursor. Hay de avance hacia adelante solamente y Scroll. FETCH NombreDelCursor Si se va a recorrer el cursor, se debe almacenar los datos de cada registro en variables previamente definidas con la variante siguiente: FETCH NombreDelCursor INTO ListaVariables

Para lectura de manera automática de los registros del cursor se usa la estructura While, el bucle de esta estructura se ejecutará ciempre que la función @@FETCH_STATUS sea igual a CERO. La función @@FETCH_STATUS reporta CERO (0) cuando la instrucción Fetch un registro. Al finalizar @@FETCH_STATUS toma el valor de -1. FETCH NombreDelCursor INTO ListaVariables WHILE (@@FETCH_STATUS = 0) BEGIN … instrucciones del bloque FETCH NombreDelCursor INTO ListaVariables END — FIN DEL BUCLE WHILE

Cursor de tipo Scroll Los cursores de tipo Scroll se pueden recorrer hacia adelante o hacia atrás. Para un cursor de tipo Scroll se pueden usar: Firts, Next, Prior, Last, Relative n, Absolute n para mostrar los diferentes registros. 4. Cerrar el cursor CLOSE NombreDelCursor 5. Liberar el espacio de memoria ocupado por el cursor DEALLOCATE NombreDelCursor

Ejercicios 1. Cursor que reporta los Empleados Declare cursorEmpleados cursor for select EmployeeID, LastName, FirstName from Employees go — Abrir el cursor open cursorEmpleados go — Los datos disponibles se visualizar con Fetch, ejecutar varias veces para ver los resultados. Fetch cursorEmpleados go — La función @@FETCH_STATUS reporta CERO si hay registro y reporta -1 si ya no existen registros para mostrar. (Ver Funciones de Cursores) select @@FETCH_STATUS go

— Cerrar el cursor, liberar de memoria close cursorEmpleados Deallocate cursorEmpleados go 2. Cursor que reporte la lista de productos, si las unidades en orden son mayores al stock, mostrar COMPRAR URGENTE, de lo contrario mostrar STOCK ADECUADO Nota: El ejercicio muestra mensajes, los que en la práctica no son realmente útiles. Declare cursorProductoComprasUrgente cursor for SELECT P.ProductID, P.ProductName, P.UnitsInStock, P.UnitsOnOrder from Products As P Open cursorProductoComprasUrgente Declare @Codigo int, @Descripcion nvarchar(40), @Stock Numeric(9,2), @PorAtender Numeric(9,2) Fetch cursorProductoComprasUrgente into @Codigo, @Descripcion, @Stock, @PorAtender Print ‘================ LISTADO ======================’ While (@@FETCH_STATUS = 0) Begin Declare @Mensaje nvarchar(20) If (@PorAtender > @Stock) Begin Set @Mensaje = ‘COMPRAR URGENTE’ End Else Begin Set @Mensaje = ‘STOCK ADECUADO’ End — reportar el registro y luego leer el siguiente Print ‘Código: ‘ + STR(@Codigo) Print ‘Descripción: ‘ + @Descripcion + ‘ Stock: ‘ + Ltrim(STR(@Stock)) + ‘ Por Atender: ‘ + Ltrim(Str(@PorAtender)) Print ‘Mensaje: ‘ + @Mensaje Print ” Print ‘————————————————‘ Fetch cursorProductoComprasUrgente into @Codigo, @Descripcion, @Stock, @PorAtender End Close cursorProductoComprasUrgente Deallocate cursorProductoComprasUrgente go

Cursor de tipo Scroll 3. Cursor Scroll para Categorias

Declare cursorCategorias Scroll cursor for select * from Categories Open cursorCategorias go Note que en la definición del cursor se ha utilizado la palabra Scroll. — Mostrar los datos Fetch cursorCategorias go — Registro 6 Fetch Absolute 6 from cursorCategorias go — Siguiente Fetch Next from cursorCategorias go — Anterior Fetch Prior from cursorCategorias go — Último Fetch Last from cursorCategorias go — Tres anteriores Fetch Relative -3 from cursorCategorias go — Dos hacia adelante Fetch Relative 2 from cursorCategorias go — Primero Fetch First from cursorCategorias go — Cerrar y Liberar Close cursorCategorias Deallocate cursorCategorias go

Como crear cursores anidados en SQL Server 4. Listado de las categorías y sus productos Declare cursorCategoriasListadoProductos cursor for select C.CategoryID, C.CategoryName from Categories As C Open cursorCategoriasListadoProductos

Declare @CodigoCategoria int, @NombreCategoria nvarchar(15) Fetch cursorCategoriasListadoProductos into @CodigoCategoria, @NombreCategoria Print ‘============================================================’ Print ‘============ Listado de Productos por categoria ==========’ Print ‘============================================================’ Print ” While (@@FETCH_STATUS = 0) — Recorre el cursor de Categorías Begin Print ‘Código Categoria: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(2) + ‘Categoría: ‘ + @NombreCategoria — Definir el Cursor para los productos – CURSOR ANIDADO Declare cursorProductosCategoria cursor for select P.ProductID, P.ProductName, P.UnitPrice from Products As P where P.CategoryID = @CodigoCategoria Open cursorProductosCategoria Declare @CodigoProducto int, @NombreProducto nvarchar(40), @Precio Numeric(9,2) Fetch cursorProductosCategoria into @CodigoProducto, @NombreProducto, @Precio Print ” Print ‘====================== PRODUCTOS ==========================’ Print ‘ CODIGO DESCRIPCION PRECIO ‘ While (@@FETCH_STATUS = 0) — Estructura para Productos Begin Print Space(5) + Ltrim(str(@CodigoProducto)) + Space(10) + @NombreProducto + space(20) + Ltrim(str(@Precio)) Fetch cursorProductosCategoria into @CodigoProducto, @NombreProducto, @Precio End — Final While de Productos Print ” Close cursorProductosCategoria Deallocate cursorProductosCategoria — Leer la siguiente categoria Fetch cursorCategoriasListadoProductos into @CodigoCategoria, @NombreCategoria

End — Final While de Categorias Close cursorCategoriasListadoProductos Deallocate cursorCategoriasListadoProductos go

5. Incluir Cantidad de Productos, Valor del Stock por categoria Cantidad Total del Productos y Valor total del Stock Es necesario usar Acumuladores. Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories Open cursorCategorias Declare @CodigoCategoria Integer, @NombreCategoria nvarchar(15) Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria Declare @CantidadTotaldeProductos Numeric(9,2), @ValorTotaldelStock Numeric(9,2) — Variables para totales set @CantidadTotaldeProductos = 0 Set @ValorTotaldelStock = 0 Print ‘=====================================’ Print ‘=====PRODUCTOS POR CATEGORIA=========’ Print ‘=====================================’ While (@@FETCH_STATUS = 0) Begin Print ‘Código Categoria: ‘ + Ltrim(Str(@CodigoCategoria)) Print ‘Nombre Categoria: ‘ + Ltrim(@NombreCategoria) Print ‘===================================================’ — Definir el cursor para los productos de la categoria actual Declare cursorProductoPorCategoria cursor for select ProductID, ProductName, UnitPrice, UnitsInStock from Products where CategoryID = @CodigoCategoria Open cursorProductoPorCategoria Declare @CodigoProducto Integer, @NombreProducto nvarchar(40), @Precio Numeric(9,2), @Stock Numeric(9,2) Fetch cursorProductoPorCategoria into @CodigoProducto, @NombreProducto, @Precio, @Stock Declare @CantidadProductosPorCategoria Numeric(9,2), @ValorStockPorCategoria Numeric(9,2) Set @CantidadProductosPorCategoria = 0 Set @ValorStockPorCategoria = 0 While (@@FETCH_STATUS = 0) — Para Productos Begin Print ‘Código: ‘ + Ltrim(Str(@CodigoProducto)) + ‘ Descripción: ‘ + Ltrim(@NombreProducto)

— Acumular Set @CantidadProductosPorCategoria = @CantidadProductosPorCategoria + @Stock Set @ValorStockPorCategoria = @ValorStockPorCategoria + @Precio* @Stock Fetch cursorProductoPorCategoria into @CodigoProducto, @NombreProducto, @Precio,@Stock End Print ‘Cantidad Productos: ‘ + Ltrim(Str(@CantidadProductosPorCategoria)) Print ‘Valor del Stock: ‘ + Ltrim(Str(@ValorStockPorCategoria)) Close cursorProductoPorCategoria Deallocate cursorProductoPorCategoria Print ” — Acumulado Total Set @CantidadTotaldeProductos = @CantidadTotaldeProductos + @CantidadProductosPorCategoria Set @ValorTotaldelStock = @ValorTotaldelStock + @ValorStockPorCategoria Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria End Print ‘Cantidad Total Productos: ‘ + Ltrim(Str(@CantidadTotaldeProductos)) Print ‘Valor Total del Stock: ‘ + Ltrim(Str(@ValorTotaldelStock)) Close cursorCategorias Deallocate cursorCategorias go El resultado se muestra para la primera categoría en la siguiente imagen





Cursores – Casos prácticos By Trainer SQL in Programación

Cursores Elemento que almacena en memoria el resultado de un Select. Para mayor información ver Cursores en SQL Server Pasos: 1. Declarar el cursor, utilizando DECLARE 2. Abrir el cursor, utilizando OPEN 3. Leer los datos del cursor, utilizando FETCH … INTO 4. Cerrar el cursor, utilizando CLOSE 5. Liberar el cursor, utilizando DEALLOCATE Las sintaxis de las instrucciones es como sigue: DECLARE CURSOR FOR OPEN FETCH INTO WHILE (@@FETCH_STATUS = 0) BEGIN FETCH INTO … END — FIN DEL BUCLE WHILE CLOSE DEALLOCATE

Ejercicios Cursor que liste las categorias, el resultado se muestra como sigue: 1 Beverages 2 Condiments 3 Confections 4 Dairy Products 5 Grains/Cereals 6 Meat/Poultry 7 Produce 8 Seafood */ Crear y mostrar los resultados manualmente. Declare cursorCategorias Cursor for select CategoryID, CategoryName from Categories go Abrir Open cursorCategorias go Para mostrar las categorías usar la siguiente instrucción, repetir hasta terminar el listado. Fetch cursorCategorias go select @@FETCH_STATUS — 0 cuando existen registros por leer, -1 cuando ya no existen registros go Cerrar el cursor close cursorCategorias Liberar el cursor de la memoria. Deallocate cursorCategorias go Listado automático usando While y variables Declare cursorCategorias Cursor for select CategoryID, CategoryName from Categories Open cursorCategorias Declare @CodigoCategoria int, @NombreCategoria nvarchar(15) Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria WHILE (@@FETCH_STATUS = 0) BEGIN Print ‘Código: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(2) + ‘Nombre: ‘ + @NombreCategoria Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria END — FIN DEL BUCLE WHILE CLOSE cursorCategorias DEALLOCATE cursorCategorias go

El resultado se muestra en la imagen.

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ÓN DEL CURSOR DE ACTUALIZACION DECLARE ActualizaPreciosProductos CURSOR FOR SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products FOR UPDATE — APERTURA DEL CURSOR OPEN ActualizaPreciosProductos — DECLARACION DE VARIABLES PARA EL CURSOR DECLARE @ID INT, @NOMBRE VARCHAR(255), @PRECIO DECIMAL, @StockActual INT — LECTURA DE LA PRIMERA FILA DEL CURSOR FETCH ActualizaPreciosProductos INTO @ID, @NOMBRE, @PRECIO, @StockActual — MIENTRAS PUEDA LEER EL REGIStockActualRO WHILE (@@FETCH_Status = 0 ) BEGIN IF(@StockActual>=100) Begin SET @PRECIO = 0.5*@PRECIO End ELSE Begin SET @PRECIO = 0.80*@PRECIO End UPDATE Products SET UnitPrice = @PRECIO WHERE CURRENT OF ActualizaPreciosProductos

–IMPRIMIR PRINT ‘EL PRECIO DE PRODUCTO ‘+ @NOMBRE+ ‘ ES ‘ + Str(@PRECIO) — LECTURA DE LA SIGUIENTE FILA DEL CURSOR FETCH ActualizaPreciosProductos INTO @ID, @NOMBRE, @PRECIO, @StockActual END — CIERRE DEL CURSOR CLOSE ActualizaPreciosProductos — LIBERAR LOS RECURSOS DEALLOCATE ActualizaPreciosProductos go CURSORES ANIDADOS Cursor que muestre las categorías y de cada categoría los productos Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories Open cursorCategorias Declare @CodigoCategoria int, @NombreCategoria nvarchar(15) Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria While (@@FETCH_STATUS = 0) Begin Print ‘============================================================== ===========================’ Print ‘Código: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(3) + ‘ Categoria: ‘ + @NombreCategoria Print ‘============================================================== ===========================’ — Crear el cursor para leer los productos de la categoría actual Declare cursorProductosPorCategoria cursor for select ProductID, ProductName, UnitPrice from Products where CategoryID = @CodigoCategoria Open cursorProductosPorCategoria Declare @CodigoProducto int, @NombreProducto nvarchar(40),@Precio Decimal Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio While (@@FETCH_STATUS = 0) Begin Print ‘Código: ‘ + Ltrim(Str(@CodigoProducto)) + Space (3) + ‘Descripción: ‘ + @NombreProducto Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio End Close cursorProductosPorCategoria Deallocate cursorProductosPorCategoria Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria End close cursorCategorias

Deallocate cursorCategorias go El resultado con las dos primeras categorías y sus productos se muestran en la imagen siguiente:

Cursor Scroll: Para leer los datos del cursor definido como Scroll, en la instrucción Fetch se puede usar las siguientes palabras para desplazarse. — First – Primer registro — Last – Último registro — Next – Siguiente Registro — Prior – Registro Anterior — Relative n – Positivo Avanza n

— Negativo Retrocede n — Absolute n – Registro n — NO OLVIDAR USAR LA CLAUSULA…. FROM — Crear un cursor Scroll con las categorias Declare cursorScrollCategorias Scroll Cursor for select * from Categories Open cursorScrollCategorias go — Recién abierto… la siguiente instrucción lee el primer registro Fetch cursorScrollCategorias go — Primero Fetch First from cursorScrollCategorias go — Último Fetch Last from cursorScrollCategorias go — Retroceder 3 Fetch Relative -3 from cursorScrollCategorias go — Al número 6 Fetch Absolute 6 from cursorScrollCategorias go — Siguiente Fetch next from cursorScrollCategorias go — Anterior Fetch prior from cursorScrollCategorias go — Cerrar y liberar Close cursorScrollCategorias Deallocate cursorScrollCategorias go



Funciones definidas por el usuario FDU By Trainer SQL in Programación

FUNCIONES DEFINIDAS POR EL USUARIO Las Funciones definidas por el usuario son rutinas que aceptan parámetros de manera opcional, realizan acciones y devuelven el resultado como un valor o como una tabla. El valor devuelto puede ser un valor escalar único o un conjunto de resultados. Las Funciones Definidas por el usuario son de DOS TIPOS: 1. Las que retornan UN VALOR – InLine,se utilizan en otras instrucciones 2. Las que retornan UNA TABLA

Para crear las que devuelven un valor. Create function Esquema.NombreFuncion([Parámetros]) Returns TipoDato As Begin Instrucciones…. Return ValorRetornado End go Para crear las FDU que retornan UNA TABLA Create function Esquema.NombreFuncion([Parámetros]) Returns Table As Return (Select….) go

Ejercicios

Usando Northwind use Northwind go FDU que retorne los productos con tengan mas Unidades en Orden que Stock actual Create function fduProductoCompraUrgente () returns Table As Return (select * from Products where UnitsOnOrder > UnitsInStock) go Para usar la función creada select * from fduProductoCompraUrgente() go Listar de Pedidos de un cliente, escribir el nombre del cliente Primero: buscar el código del cliente. Ejemplo “Universidad SQL” select CustomerID from Customers where CompanyName = ‘Universidad SQL’ go Se comprueba que no existe el cliente Para el cliente “Antonio Moreno Taquería” select CustomerID from Customers where CompanyName = ‘Antonio Moreno Taquería’ go Resultado: ANTON Para las Órdenes del Cliente se puede usar una sub consulta. (Ver Sub consultas) SELECT * FROM Orders where CustomerID = (select CustomerID from Customers where CompanyName = ‘Antonio Moreno Taquería’) go En una FDU Create function fduOrdenesPorCliente(@Cliente nvarchar(40)) Returns Table As Return (SELECT * FROM Orders where CustomerID = (select CustomerID from Customers where CompanyName =@Cliente )) go Usando la FDU anterior, las Órdenes para ‘Antonio Moreno Taquería’ select * from fduOrdenesPorCliente(‘Antonio Moreno Taquería’) go Productos con precio mayor a valor ingresado Create function fduProductosPrecioHaciaArriba(@Precio Numeric(9,2)) Returns Table As Return (select * from Products where UnitPrice > @Precio) go

Usar la FDU select * from fduProductosPrecioHaciaArriba(80) go Pedidos de un cliente en un rango de fechas Create function fduOrdenesPorClienteRangoFechas(@Cliente nvarchar(40), @FechaInicial Date, @FechaFinal Date) Returns Table As Return (SELECT * FROM Orders where CustomerID = (select CustomerID from Customers where CompanyName =@Cliente ) and (OrderDate >= @FechaInicial and OrderDate 1 BEGIN Rollback Transaction PRINT ‘El Nombre de la Categoria ya existe…’ END ELSE PRINT ‘Categoría ingresada a la Base de datos’ go 6. Insertar categoría con nombre repetido: Pisos insert into Categories (CategoryName) values (‘Pisos’) go El Nombre de la Categoria ya existe… Mens. 3609, Nivel 16, Estado 1, Línea 87 La transacción terminó en el desencadenador. Se anuló el lote.

Trigger Instead Of Realizar acciones después de las instrucciones de un procedimiento o las escritas directamente por el usuario. (Ver Triggers Instead Of) 6. Copiar los datos a una vista de Clientes al insertar uno en la tabla Customers Primero se crea la vista con los clientes. Create View ClientesVista as Select CustomerID,CompanyName,Address,City,Phone from Customers go

Al agregar un Cliente en Customers se desea que se inserte en ClientesVista Crear un Trigger Instead of para la tabla Customers. Create trigger trClienteInsertaVista ON Customers Instead of Insert AS BEGIN Insert Into ClientesVista SELECT CustomerID, CompanyName, Address, City, Phone FROM Inserted Print ‘Insertado correctamente en la vista’ END go Insertar un cliente en Customers insert into Customers (CustomerID, CompanyName,ContactName, ContactTitle,Address, City, Phone) Values (‘TQ884’,’Trainer SQL Pro ‘,’SQL Professional’, ‘Gerente General’,’Av. Larco 94994′,’España’,’209834534′) go

Visualizar la vista para comprobar que el cliente ha sido insertado. select * from ClientesVista where CustomerID like ‘T%’ go





Triggers Instead Of en SQL Server By Trainer SQL in Consultas, Programación, Registros - Vistas

Triggers de Tipo Instead of Los triggers instead of son un tipo de Triggers que reemplazan las instrucciones que hace que se dispare, use estos tipos de Triggers cuando es necesario comprobar algunas condiciones al momento de realizar transacciones con los registros de tablas o vistas. Por ejemplo: si se crea un Trigger de tipo instead of para la tabla Clientes al insertar un registro, al ejecutar un Insert es cuando este tipo de Trigger se va a ejecutar en lugar de la instrucción Insert en la tabla o vista.

Notas importantes 1. Estos tipos de triggers cancelan la instrucción que hace que se dispare, reemplazando esta por las instrucciones del Trigger. 2. Generalmente se utilizan estos triggers en vistas 3. Las acciones que realice el trigger no deben cancelar la transacción que la dispara sino ejecutar las instrucciones cambiadas que son el cuerpo del trigger.

Ejemplo Usando la base de datos Northwind use Northwind go Para utilizar un Trigger de tipo instead of crearemos una vista llamada SociosEstrategicos con los Clientes (Customers) y los Proveedores (Suppliers)

Primero: los Clientes select C.CustomerID ‘Cód.Cliente’, C.CompanyName As ‘Cliente’, C.Country As ‘País’ from Customers As C go

Segundo: los proveedores select S.SupplierID As ‘Cód. Proveedor’, S.CompanyName As ‘Proveedor’, S.Country As ‘País’ from Suppliers As S go

Al juntar los dos listados en la vista SociosEstrategicos, la instrucción Select para la vista es como sigue:

select C.CustomerID ‘Cód.Cliente’, C.CompanyName As ‘Cliente’, C.Country As ‘País’ from Customers As C union all select Cast(S.SupplierID As nchar(5)) , S.CompanyName , S.Country from Suppliers As S go

Incluiremos una columna para diferenciar el tipo de socio, Cliente o Proveedor. Además de cambiar los nombres de campos (Ver opciones de consultas Union) select C.CustomerID ‘CodigoSocio’, C.CompanyName As ‘Socio’, C.Country As ‘País’, ‘Cliente’ As Tipo from Customers As C union all select Cast(S.SupplierID As nchar(5)) , S.CompanyName , S.Country , ‘Proveedor’ As Tipo from Suppliers As S go En la imagen se muestra parte de los Clientes y Proveedores.

Creamos la vista (Ver Vistas) Create view VistaSociosEstrategicos As select C.CustomerID ‘CodigoSocio’, C.CompanyName As ‘Socio’, C.Country As ‘País’, ‘Cliente’ As Tipo from Customers As C union all select Cast(S.SupplierID As nchar(5)) , S.CompanyName , S.Country , ‘Proveedor’ As Tipo from Suppliers As S go Si se desea visualizar los socios estratégicos del negocio solamente listamos la vista select * from VistaSociosEstrategicos go

El Trigger se creará para la vista, será de tipo Instead Of (Ver Triggers) create trigger trVistaSocioEstrategicoInsertar on VistaSociosEstrategicos instead of insert as insert into Customers (CustomerID, CompanyName, Country) select CodidoSocio,Socio,País from inserted where Tipo=’Cliente’ insert into Suppliers (CompanyName, Country) select Socio,País

from inserted where Tipo=’Proveedor’ go Al ejecutar una inserción en la vista

Insertar un Cliente insert into VistaSociosEstrategicos values (‘YT348′,’Sociedad SQL’,’France’,’Cliente’) go Listamos los clientes (tabla Customers) select * from Customers where CompanyName Like ‘Soci%’ go Insertar un Proveedor insert into VistaSociosEstrategicos values (‘BT599′,’Limbo Almacenes’,’Spain’,’Proveedor’) go Listamos los Proveedores (tabla Suppliers) select * from Suppliers where CompanyName Like ‘Limbo%’ go La vista tiene insertados los dos registros select * from VistaSociosEstrategicos where Socio like ‘Limbo%’ or Socio like ‘Soci%’ go

Note que al insertar registros en la vista debemos especificar todos los campos, en el Trigger para los proveedores no se especifica el código porque es un campo entero y es Identidad. (Ver Identity) 



Triggers – Activar y Desactivar By Trainer SQL in Programación

Triggers – Activar y Desactivar Este artículo muestra como activar o desactivar un Trigger, los triggers DML son procedimientos que se disparan cuando en una tabla se realizan las instrucciones Insert, Update o Delete. Para mayor información Ver Triggers

Desactivar el Trigger Disable Trigger NombreTrigger on Tabla/Vista

Activar el Trigger Enable Trigger NombreTrigger on Tabla/Vista Se puede eliminar un Trigger usando Drop Trigger NombreTrigger

Ejercicio Usando la base de datos Northwind use Northwind go 1. Crear un trigger para que se dispare cuando se actualiza una Region

Create trigger trRegionActualiza on Region for update As Begin Print ‘Se actualizaron los datos’ End go Al actualizar los datos de una región se dispara el trigger, mostrando el mensaje. Primero mostramos los registros para actualizar uno de ellos, el registro con código 4 se cambiará la descripción por Sur. select * from Region go

Note que el registro con código 4 es Southern. Actualizar Region update Region set RegionDescription = ‘Sur’ where RegionID = 4 go

2. Crear un trigger para Region que no permita insertar o modificar una región con la descripción de una región existente.

Create trigger trRegionInsertaModificaSinDuplicado on Region for Insert, Update As Begin — Inicio del Trigger — Contar la cantidad de registros con la misma descripción if (select Count(Region.RegionID) from inserted, Region where inserted.RegionDescription = Region.RegionDescription)>1 Begin Rollback transaction — Anula la inserción o actualización Print ‘No se realizó la transacción’ End Else Begin Print ‘Se realizó la inserción o modificación…’ End End — Final del Trigger go Ver las regiones select * from Region go Insertar una región insert into Region values (25,’Ica’) go Insertar una región con la misma descripción de una existente (Piura) insert into Region values (255,’Piura’) go 3. Crear un trigger en Region que no permita borrar mas de un registro Create trigger trRegionBorrarUnRegistro on Region for Delete As Begin If (Select Count(*) from deleted) > 1 Begin Rollback transaction Print ‘No se puede borrar mas de un registro…’ End End go Ver los registros de Region select * from Region go Eliminar un registro de Region (suponer que el registro con código 56 existe, de lo contrario insertar uno con el código 56)

delete Region where RegionID = 56 go Eliminar varios registros, se requiere insertar varios. Por ejemplo una vez insertados los registros con códigos 34, 87 y 25 delete Region where RegionID in (34,87,25) go /* Mensaje No se puede borrar mas de un registro… Mens. 3609, Nivel 16, Estado 1, Línea 80 La transacción terminó en el desencadenador. Se anuló el lote. */ Si se debe eliminar los registros, hay que desactivar o eliminar el Trigger Desactivar el Trigger Disable Trigger NombreTrigger on Tabla/Vista Activar el Trigger Enable Trigger NombreTrigger on Tabla/Vista

Para borrar las regiones con códigos 34,87,25 Desactivar el Trigger trRegionBorrarUnRegistro, borrar los registros y activar el Trigger Disable Trigger trRegionBorrarUnRegistro on Region go delete Region where RegionID in (34,87,25) go Enable Trigger trRegionBorrarUnRegistro on Region go Ver los registros select * from Region go



Triggers Encriptados By Trainer SQL in Sin categoría

Triggers Encriptados Este artículo muestra como encriptar un trigger, es importante que en todo nivel exista un cuidado con las instrucciones que realiza el trigger, la opción de encriptación evita que de alguna forma se pueda visualizar las instrucciones del Trigger usando el procedimiento almacenado (Ver Procedimientos Almacenados) sp_helpText. Los triggers DML son procedimientos que se disparan cuando en una tabla o vista se realizan las instrucciones Insert, Update o Delete. Para mayor información Ver Triggers. Como se describe en el artículo de Triggers, estos permiten ejecutar un conjunto de instrucciones al realizar instrucciones de inserción, modificación o eliminación de registros y es importante proteger estas instrucciones, para eso podemos encriptarlos usando la opción “with encryption”.

Como crear un trigger encriptado Para crear un Trigger DML encriptado se utiliza: CREATE [ OR ALTER ] TRIGGER [ Esquema . ]NombreTrigger ON { tabla | vista } with encryption { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS Begin Instrucciones T-SQL End

Note que después del nombre de la tabla o vista se utiliza la cláusula with encryption.

Ejercicios Usando la base de datos Northwind use Northwind go 1. Crear un trigger para que se dispare cuando se actualiza una Region Create trigger trRegionActualiza on Region with encryption for update As Begin Print ‘Se actualizaron los datos’ End go Si se desea ver el texto del Trigger podemos utilizar el procedimiento almacenados sp_helptext. sp_helpText trRegionActualiza go El mensaje que aparece es: El texto para el objeto ‘trRegionActualiza’ está cifrado. Al actualizar los datos de una región se dispara el trigger, mostrando el mensaje. Primero mostramos los registros para actualizar uno de ellos, el registro con código 4 se cambiará la descripción por Sur. select * from Region go Actualizar Region update Region set RegionDescription = ‘Sur’ where RegionID = 4 go 2. Crear un trigger para Region que no permita insertar o modificar una región con la descripción de una región existente. Inicialmente se creará el Trigger sin encriptación. Create trigger trRegionInsertaModificaSinDuplicado on Region for Insert, Update As Begin — Inicio del Trigger

— Contar la cantidad de registros con la misma descripción if (select Count(Region.RegionID) from inserted, Region where inserted.RegionDescription = Region.RegionDescription)>1 Begin Rollback transaction — Anula la inserción o actualización Print ‘No se realizó la transacción’ End Else Begin Print ‘Se realizó la inserción o modificación…’ End End — Final del Trigger go Visualizar el texto del objeto (Trigger) usando sp_helptext. sp_helptext trRegionInsertaModificaSinDuplicado go

Modificar el Trigger y encriptarlo

Alter trigger trRegionInsertaModificaSinDuplicado on Region with encryption for Insert, Update As Begin — Inicio del Trigger — Contar la cantidad de registros con la misma descripción if (select Count(Region.RegionID) from inserted, Region where inserted.RegionDescription = Region.RegionDescription)>1 Begin Rollback transaction — Anula la inserción o actualización Print ‘No se realizó la transacción’ End Else Begin Print ‘Se realizó la inserción o modificación…’ End End — Final del Trigger go Visualizar el texto del objeto (Trigger) usando sp_helptext. sp_helptext trRegionInsertaModificaSinDuplicado go El mensaje es: El texto para el objeto ‘trRegionInsertaModificaSinDuplicado’ está cifrado.

Probando el Trigger Ver las regiones select * from Region go Insertar una región insert into Region values (25,’Ica’) go Insertar una región con la misma descripción de una existente (Ica) insert into Region values (255,’Ica’) go 3. Crear un trigger en Region que no permita borrar mas de un registro. Create trigger trRegionBorrarUnRegistro on Region with encryption for Delete As Begin If (Select Count(*) from deleted) > 1 Begin Rollback transaction Print ‘No se puede borrar mas de un registro…’

End End go Ver los registros de Region select * from Region go Eliminar un registro de Region delete Region where RegionID = 56 go Eliminar varios registros delete Region where RegionID in (34,87,25) go /* Mensaje No se puede borrar mas de un registro… Mens. 3609, Nivel 16, Estado 1, Línea 80 La transacción terminó en el desencadenador. Se anuló el lote. */ Si se debe eliminar los registros, hay que desactivar o eliminar el Trigger Desactivar el Trigger (Ver Activar Desactivar Triggers) Disable Trigger NombreTrigger on Tabla/Vista Activar el Trigger Enable Trigger NombreTrigger on Tabla/Vista Para borrar las regiones con códigos 34,87,25 Desactivar el Trigger trRegionBorrarUnRegistro, borrar los registros y activar el Trigger Disable Trigger trRegionBorrarUnRegistro on Region go delete Region where RegionID in (34,87,25) go Enable Trigger trRegionBorrarUnRegistro on Region go Ver los registros select * from Region go

Notas adicionales:



Se pueden encriptar también Procedimientos Almacenados, Funciones definidas por el usuario y vistas, esto va a permitir un grado mayor de seguridad en los objetos.



Para mayor información ver: Procedimientos almacenados, Funciones definidas por el usuario y vistas.





Triggers Logon en SQL Server By Trainer SQL in Programación

Triggers Logon Como usar triggers logon en SQL Server Son los tipos de Triggers que se disparan en respuesta a un evento de INICIO DE SESIÓN. Este evento se pproduce cuando se establece una sesión de usuario con una instancia de SQL Server. El inicio de sesión dispara el Trigger logon después de la fase de autenticación de inicio de sesión, pero antes de que la sesión del usuario esté realmente establecida. (Ver Triggers). Por lo tanto, todos los mensajes que se originan dentro del desencadenador y que normalmente llegarían al usuario, como los mensajes de error y los mensajes de la instrucción PRINT se desvían al registro de errores de SQL Server.

Los Triggers Logon no se activan si la autenticación falla. Puede utilizar los Triggers de inicio de sesión para auditar y controlar las sesiones del servidor, como el seguimiento de la actividad de inicio de sesión, la restricción de inicios de sesión en SQL Server o la limitación del número de sesiones para un inicio de sesión específico.

Instrucción Create Trigger Permite crear Triggers de tipo Logon CREATE [ OR ALTER ] TRIGGER NombreTrigger ON ALL SERVER [ WITH [ ,…n ] ] { FOR| AFTER } LOGON AS { InstruccionesSQL [ ; ] [ ,…n ] | EXTERNAL NAME < MètodoEspecífico > } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

Orden de los Triggers Logon Se pueden definir múltiples Triggers LOGON, cualquiera de estos Triggers puede designarse como el primer o último Trigger que se activará en un evento mediante

el procedimiento almacenado del sistema sp_settriggerorder. SQL Server no garantiza el orden de ejecución de los desencadenadores restantes.

Procedimiento sp_settriggerorder Define el primero y el último de los Triggers Logon sp_settriggerorder [ @triggername = ] ‘[ Esquema. ] NombreTrigger’ , [ @order = ] ‘value’ , [ @stmttype = ] ‘Tipo_Instrucción_SQL’ [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ] Valores posibles del orden First Trigger se dispara primero Last Trigger se diapara último. None Trigger no se define su orden Ejemplo En el siguiente ejemplo se define como primero el Trigger trShippersInserta que realiza un Insert. USE Northwind go sp_settriggerorder @triggername= ‘dbo.trShippersInserta’, @order=’First’, @stmttype = ‘INSERT’ GO

Ejercicio Crear un Trigger Logon que deniegue intentos de inicio de sesión de SQL Server iniciados por login TrainerSQL si ya hay tres sesiones de usuario creadas por ese inicio de sesión. USE master go CREATE LOGIN TrainerSQL WITH PASSWORD = ‘cl@v3F1n@1’ go — Asignar el permiso para ver el estado del servidor Grant VIEW SERVER STATE TO TrainerSQL go — El Trigger Logon para evitar más de tres sesiones.

Create Trigger trServerLimitarConexiones ON ALL SERVER WITH EXECUTE AS ‘TrainerSQL’ FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= ‘TrainerSQL’ AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = ‘TrainerSQL’) > 3 ROLLBACK END go Para probar el Trigger, conectarse a la misma instancia más de tres veces con el mismo inicio de sesión, en la figura se muestra la conexión

Para visualizar las sesiones de un inicio de sesión select session_id As ‘Código de Sesión’, login_name As ‘Inicio de sesión’, Format(login_time,’dd/MM/yyy’) As ‘Fecha’, program_name As ‘Programa’ from sys.dm_exec_sessions where login_name = ‘TrainerSQL’ go

El resultado se muestra como sigue:

Después de la tercera conexión, al intentar conectarse se dispara el Trigger creado.



Consideraciones importantes Cualquier modificación de datos realizada hasta el punto de ROLLBACK TRANSACTION se revierte. Estas modificaciones incluyen las realizadas por el desencadenador actual y las realizadas por desencadenantes anteriores que se ejecutaron en el mismo evento.



Cualquier desencadenante restante para el evento específico no se ejecuta.



El Trigger actual continúa ejecutando las sentencias restantes que aparecen después de la instrucción ROLLBACK.



Si alguna de estas declaraciones modifica los datos, las modificaciones no se revierten.

 

No se establece una sesión de usuario si se produce una de las siguientes condiciones durante la ejecución de un desencadenante en un evento de INICIO de sesión: La transacción implícita original se retrotrae o falla. Se genera un error que tiene una gravedad superior a 20 dentro del cuerpo del Trigger. (Ver Errores)



Como usar Triggers DDL en SQL Server By Trainer SQL in Programación, Registros - Vistas

Como usar Triggers DDL en SQL Server Los Triggers DDL son aquellos que se disparan cuando se realizan eventos DDL, que son las siglas de Data Definition Language, estos comando son Create, Alter, Drop, GRANT, DENY, REVOKE o UPDATE STATISTICS

Puede interesar Triggers, definición y creación Triggers DML encriptados Triggers Logon Triggers, activar y desactivar.

Eventos de un Trigger DDL Los eventos que hacen que se dispare un Trigger DDL clasificados por el alcance de los mismos son de dos tipos: 

Eventos que tienen alcance de base de datos



Eventos que tienen alcance de servidor

Eventos que tienen alcance de base de datos CREATE_APPLICATION_ROLE CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY CREATE_ASYMMETRIC_KEY ALTER_ASYMMETRIC_KEY DROP_ASYMMETRIC_KEY

ALTER_AUTHORIZATION ALTER_AUTHORIZATION_DATABASE CREATE_BROKER_PRIORITY CREATE_BROKER_PRIORITY CREATE_BROKER_PRIORITY CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE CREATE_CONTRACT DROP_CONTRACT CREATE_CREDENTIAL ALTER_CREDENTIAL DROP_CREDENTIAL GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE CREATE_DATABASE_AUDIT_SPEFICIATION ALTER_DATABASE_AUDIT_SPEFICIATION DENY_DATABASE_AUDIT_SPEFICIATION CREATE_DATABASE_ENCRYPTION_KEY ALTER_DATABASE_ENCRYPTION_KEY DROP_DATABASE_ENCRYPTION_KEY CREATE_DEFAULT DROP_DEFAULT BIND_DEFAULT (También sp_bindefault.) UNBIND_DEFAULT (También sp_unbindefault.) CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION CREATE_EXTENDED_PROPERTY (También sp_addextendedproperty.) ALTER_EXTENDED_PROPERTY (También sp_updateextendedproperty.) DROP_EXTENDED_PROPERTY (También sp_dropextendedproperty.) CREATE_FULLTEXT_CATALOG (También the CREATE FULLTEXT CATALOG y sp_fulltextcatalog) ALTER_FULLTEXT_CATALOG (También ALTER FULLTEXT CATALOG , cuando sp_fulltextcatalog, start_full, Stop, o Rebuild es especificado, y sp_fulltext_database ) DROP_FULLTEXT_CATALOG (También DROP FULLTEXT CATALOG y sp_fulltextcatalog) CREATE_FULLTEXT_INDEX (También CREATE FULLTEXT INDEX y sp_fulltexttable) ALTER_FULLTEXT_INDEX (También ALTER FULLTEXT INDEX , sp_fulltextcatalog, sp_fulltext_column, y sp_fulltext_table) DROP_FULLTEXT_INDEX (También DROP FULLTEXT INDEX y sp_fulltexttable) CREATE_FULLTEXT_STOPLIST ALTER_FULLTEXT_STOPLIST DROP_FULLTEXT_STOPLIST CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION CREATE_INDEX ALTER_INDEX (También the ALTER INDEX y sp_indexoption.) DROP_INDEX CREATE_MASTER_KEY ALTER_MASTER_KEY DROP_MASTER_KEY CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME CREATE_PLAN_GUIDE (También sp_create_plan_guide.) ALTER_PLAN_GUIDE (También sp_control_plan_guide) DROP_PLAN_GUIDE (También sp_control_plan_guide ) CREATE_PROCEDURE ALTER_PROCEDURE (También ALTER PROCEDURE y sp_procoption.) DROP_PROCEDURE

CREATE_QUEUE ALTER_QUEUE DROP_QUEUE CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING CREATE_SPATIAL_INDEX RENAME (También sp_rename) CREATE_ROLE (También CREATE ROLE, sp_addrole, y sp_addgroup.) ALTER_ROLE DROP_ROLE (También DROP ROLE, sp_droprole, y sp_dropgroup.) ADD_ROLE_MEMBER DROP_ROLE_MEMBER CREATE_ROUTE ALTER_ROUTE DROP_ROUTE CREATE_RULE DROP_RULE BIND_RULE (También sp_bindrule.) UNBIND_RULE (También sp_unbindrule.) CREATE_SCHEMA (También CREATE SCHEMA, sp_addrole, sp_adduser, sp_addgroup, y sp_grantdbaccess.) ALTER_SCHEMA (También ALTER SCHEMA y sp_changeobjectowner.) DROP_SCHEMA CREATE_SEARCH_PROPERTY_LIST ALTER_SEARCH_PROPERTY_LIST DROP_SEARCH_PROPERTY_LIST CREATE_SEQUENCE_EVENTS CREATE_SEQUENCE_EVENTS CREATE_SEQUENCE_EVENTS CREATE_SERVER_ROLE ALTER_SERVER_ROLE DROP_SERVER_ROLE CREATE_SERVICE ALTER_SERVICE DROP_SERVICE ALTER_SERVICE_MASTER_KEY BACKUP_SERVICE_MASTER_KEY RESTORE_SERVICE_MASTER_KEY ADD_SIGNATURE DROP_SIGNATURE ADD_SIGNATURE_SCHEMA_OBJECT DROP_SIGNATURE_SCHEMA_OBJECT CREATE_SPATIAL_INDEX ALTER_INDEX DROP_INDEX CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS CREATE_SYMMETRIC_KEY ALTER_SYMMETRIC_KEY DROP_SYMMETRIC_KEY CREATE_SYNONYM DROP_SYNONYM CREATE_TABLE ALTER_TABLE (También ALTER TABLE y sp_tableoption.) DROP_TABLE CREATE_TRIGGER ALTER_TRIGGER (También ALTER TRIGGER y sp_settriggerorder.) DROP_TRIGGER CREATE_TYPE (También CREATE TYPE y sp_addtype.) DROP_TYPE (También DROP TYPE statement y sp_droptype.) CREATE_USER (También CREATE USER, sp_adduser, y sp_grantdbaccess.) ALTER_USER (También ALTER USER y sp_change_users_login.) DROP_USER (También DROP USER statement, sp_dropuser, y sp_revokedbaccess.) CREATE_VIEW ALTER_VIEW DROP_VIEW CREATE_XML_INDEX ALTER_INDEX DROP_INDEX CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

Eventos DDL que tienen alcance en el servidor ALTER_AUTHORIZATION_SERVER ALTER_SERVER_CONFIGURATION ALTER_INSTANCE CREATE_AVAILABILITY_GROUP ALTER_AVAILABILITY_GROUP DROP_AVAILABILITY_GROUP CREATE_CREDENTIAL ALTER_CREDENTIAL DROP_CREDENTIAL CREATE_CRYPTOGRAPHIC_PROVIDER ALTER_CRYPTOGRAPHIC_PROVIDER DROP_CRYPTOGRAPHIC_PROVIDER CREATE_DATABASE ALTER_DATABASE DROP_DATABASE CREATE_ENDPOINT ALTER_ENDPOINT DROP_ENDPOINT CREATE_EVENT_SESSION ALTER_EVENT_SESSION DROP_EVENT_SESSION CREATE_EXTENDED_PROCEDURE (También sp_addextendedproc.) DROP_EXTENDED_PROCEDURE (También sp_dropextendedproc.) CREATE_LINKED_SERVER (También sp_addlinkedserver.) ALTER_LINKED_SERVER (También sp_serveroption.) DROP_LINKED_SERVER (También sp_dropserver) CREATE_LINKED_SERVER_LOGIN (También sp_addlinkedsrvlogin.) DROP_LINKED_SERVER_LOGIN (También sp_droplinkedsrvlogin.) CREATE_LOGIN (También CREATE LOGIN, sp_addlogin, sp_grantlogin, xp_grantlogin, y sp_denylogin ALTER_LOGIN (También ALTER LOGIN, sp_defaultdb, sp_defaultlanguage, sp_password, y sp_change_users_login) DROP_LOGIN (También DROP LOGIN, sp_droplogin, sp_revokelogin, y xp_revokelogin.) CREATE_MESSAGE (También sp_addmessage.) ALTER_MESSAGE (También sp_altermessage.) DROP_MESSAGE (También sp_dropmessage.) CREATE_REMOTE_SERVER (También sp_addserver.) ALTER_REMOTE_SERVER (También sp_setnetname.) DROP_REMOTE_SERVER (También sp_dropserver) CREATE_RESOURCE_POOL ALTER_RESOURCE_POOL DROP_RESOURCE_POOL GRANT_SERVER DENY_SERVER REVOKE_SERVER ADD_SERVER_ROLE_MEMBER DROP_SERVER_ROLE_MEMBER CREATE_SERVER_AUDIT ALTER_SERVER_AUDIT DROP_SERVER_AUDIT CREATE_SERVER_AUDIT_SPECIFICATION ALTER_SERVER_AUDIT_SPECIFICATION DROP_SERVER_AUDIT_SPECIFICATION CREATE_WORKLOAD_GROUP ALTER_WORKLOAD_GROUP DROP_WORKLOAD_GROUP Como crear triggers DDL en SQL Server Ejemplos Usando la base de datos Northwind

use Northwind go 1. Crear un Trigger para evitar que se creen, modifiquen o eliminen tablas Create Trigger trNoCrearModificarBorrarTablas ON DataBase FOR Create_Table, DROP_TABLE, ALTER_TABLE AS BEGIN RAISERROR (‘Transacción anulada, no se permite crear, editar o eliminar tablas’ , 16, 1) Rollback transaction END go Al intentar crear una tabla Create table Prueba ( Codigo nchar(4), Descripcion nvarchar(100) ) go Resultado Mens 50000, Nivel 16, Estado 1, Procedimiento trNoCrearModificarBorrarTablas, Línea 131 Transacción anulada, no se permite crear, editar o eliminar tablas Mens. 3609, Nivel 16, Estado 2, Línea 127 La transacción terminó en el desencadenador. Se anuló el lote. Para poder crear tablas, se debe eliminar el Trigger o solamente desactivar (Ver Triggers Activar – Desactivar) disable trigger trNoCrearModificarBorrarTablas on Database go Probar ahora si se puede crear una tabla Create table Prueba ( Codigo nchar(4), Descripcion nvarchar(100) ) go Resultado: Comandos completados correctamente. Para activar nuevamente el Trigger se debe escribir la siguiente instrucción: Enable trigger trNoCrearModificarBorrarTablas on Database go

Como usar la función EventData() Esta función devuelve información sobre eventos del servidor o de la base de datos. Un Trigger Logon o DDL también admite el uso interno de EVENTDATA.

Sintaxis: EventData() 2. Crear un Trigger que se dispare cuando se crea una vista y capture el evento creado. Create trigger trCapturarCrearVista on Database For Create_View As Begin Select EVENTDATA() End go Crear una vista para que el trigger creado se dispare create view vistaCategorias As select C.CategoryID As ‘Código’, C.CategoryName As ‘Nombre’ from Categories As C go Puede notar que el resultado se presenta en un esquema XML que se muestra en la figura siguiente.

3. Crear un trigger que se dispare al crear, modificar o eliminar un procedimiento almacenado, almacenar la instrucción ejecutada en una tabla SPHistorial Primero crear la tabla, si se creo el trigger del ejercicio anterior debemos desactivarlo. disable trigger trNoCrearModificarBorrarTablas on Database go

Ahora si se podrá crear la tabla Create table HistorialCambiosStoreProcedures (TipoEvento nvarchar(200), Fecha DateTime, Servidor nvarchar(100), InicioSesion nvarchar(100), Equipo nvarchar(100), ComandoTSQL nvarchar(400) ) go Crear el Trigger para crear, editar o eliminar un Store Procedure Create trigger trHistoriaSP on Database for Create_Procedure, Alter_Procedure, Drop_Procedure As Begin insert into HistorialCambiosStoreProcedures (TipoEvento, Fecha, Servidor, InicioSesion, Equipo, ComandoTSQL) Select EVENTDATA().value (‘(/EVENT_INSTANCE/EventType)[1]’,’nvarchar(200)’), EVENTDATA().value (‘(/EVENT_INSTANCE/PostTime)[1]’,’Datetime’), EVENTDATA().value (‘(/EVENT_INSTANCE/ServerName)[1]’,’nvarchar(100)’), EVENTDATA().value (‘(/EVENT_INSTANCE/LoginName)[1]’,’nvarchar(100)’), HOST_NAME(), EVENTDATA().value (‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(400)’) End go Para que el Trigger se dispare, crear un procedimiento almacenado para listar los clientes Create procedure spClientesListado As Select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’ from Customers As C order by Cliente go Modificar el procedimiento para incluir un campo adicional Alter procedure spClientesListado As Select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’, C.Address As ‘Dirección’ from Customers As C order by Cliente go Borrar el SP

Drop procedure spClientesListado go Para visualizar el contenido del historial select * from HistorialCambiosStoreProcedures go La imagen siguiente muestra el resultado



Funciones de cursor By Trainer SQL in Funciones en SQL Server

Funciones de Cursor Las funciones de cursor devuelven información de los cursores. (Ver cursores) Las funciones de cursor son las siguientes: @CURSOR_ROWS CURSOR_STATUS @@FETCH_STATUS Función @Cursor_rows Devuelve el número de filas del cursor abierto. Valor devuelto

Descripción

-m

El cursor se llena asincrónicamente. El valor –m es el número de filas actualm

-1

El cursor es dinámico. Como los cursores dinámicos reflejan todos los cambio cambia constantemente. Nunca se puede afirmar que se han recuperado todas l

0

No se han abierto cursores, no hay filas calificadas para el último cursor abiert cancelado.

n

El cursor está completamente relleno. El valor devuelto (n) es el número total

Ejemplos Usando Northwind use Northwind go — Crear un cursor con los productos de categoría 5 — Antes de declarar y abrir el cursor select @@CURSOR_ROWS go Resultado: 0 Declare CursorProductosCategoria5 cursor for select * from Products where CategoryID = 5 go Open CursorProductosCategoria5 go Después de abrir el cursor select @@CURSOR_ROWS go Resultado: -1 Leer los datos del cursor Fetch CursorProductosCategoria5 go Cerrar y Liberar el cursor Close CursorProductosCategoria5 Deallocate CursorProductosCategoria5 go Función @@FETCH_STATUS Devuelve el estado de la última instrucción que lee los datos del cursor (Fetch). El tipo de dato devuelto es Entero. Valor devuelto

Descripción

0

La instrucción FETCH se ejecutó correctamente y muestra registro.

-1

La instrucción FETCH no se ejecutó correctamente y no muestra registro.

-2

Falta la fila capturada.

-9

El cursor no está realizando una operación de búsqueda.

Generalmente @@Fetch_Status es usada con la estructura While para leer los registros del cursor.

Ejemplo — Crear un cursor para mostrar los Empleados Declare cursorEmpleados Cursor for select E.EmployeeID As ‘Código’, Empleado = E.LastName + Space(1)+ E.FirstName from Employees As E order by Empleado Open cursorEmpleados Fetch from cursorEmpleados Print ‘========= EMPLEADOS ============= ‘ While @@FETCH_STATUS = 0 Begin Fetch from cursorEmpleados End Close cursorEmpleados Deallocate cursorEmpleados go Función Cursor_Status Permite determinar si el resultado de un procedimiento almacenado ha devuelto un cursor y un conjunto de resultados al recibir un parámetro. Valor

Descripción

-1

El cursor está cerrado.

1

El cursor tiene al menos una fila.

0

El conjunto de resultados del cursor está vacío.

-3

No existe un cursor con el nombre indicado.

Ejemplo Crear un cursor con las categorías Antes de Crear el cursor

select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de Crear’ go Resultado: -3 Declare cursorCategorias cursor for Select C.CategoryID, C.CategoryName from Categories As C go Antes de abrir el cursor select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de abrir’ go Resultado: -1 Despues de abrir el cursor Open cursorCategorias go select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de abrir’ go Resultado: 1 Despues de Cerrar el cursor Close cursorCategorias go select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de abrir’ go Resultado: -1



Merge By Trainer SQL in Programación

Instrucción Merge Realiza instrucciones de inserción de registros, actualización o eliminación de registros en una tabla de destino en la misma base de datos o en otra base de datos según los resultados de combinar los registros con una tabla de origen.

Sintaxis La forma de usar Merge es la siguiente: MERGE [ TOP ( n ) [ PERCENT ] ] [ INTO ] [ [ AS ] AliasTablaDestino ] USING [ [ As ] AliasTablaOrigen] ON [ WHEN MATCHED [ AND ] THEN ] [ …n ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND ] THEN ] [ WHEN NOT MATCHED BY SOURCE [ AND ] THEN ] [ …n ]

Ejemplo

En este ejemplo, se tienen dos bases de datos cada una con una tabla de Productos. La base de datos Planes, con la tabla ProductosPlanes y la base de datos Desarrollo con la tabla ProductosDesarrollo. Base de datos Planes create database Planes go use Planes go Create table ProductosPlanes ( ProductosCodigo nchar(4), ProductosDescripcion nvarchar(100), ProductosPrecioUnitario Numeric(10,2), ProductosStockActual Numeric(10,2), constraint ProductosPk Primary key (ProductosCodigo) ) go Insertar registros en la tabla ProductosPlanes insert into ProductosPlanes values (‘8856′,’Lámpara Personal’,25.4,100) insert into ProductosPlanes values (‘8636′,’Auriculares Deluxe’,98.4,20) insert into ProductosPlanes values (‘4685′,’Escritorio Gerencial’,525,6) insert into ProductosPlanes values (‘5780′,’Marco Foto’,20,80) insert into ProductosPlanes values (‘0665′,’Impresora HP’,65,15) go BASE DE DATOS DESARROLLO create database Desarrollo go use Desarrollo go Create table ProductosDesarrollo ( ProductosCodigo nchar(4), ProductosDescripcion nvarchar(100), ProductosPrecioUnitario Numeric(10,2), ProductosStockActual Numeric(10,2), constraint ProductosPk Primary key (ProductosCodigo) ) go Insertar los registros en la tabla ProductosDesarrollo Note los cambios, se han insertado registros y las coincidencias o diferencias se notan en la siguientes figuras. — Primer registro “Lámpara Personal” con un valor del Stock de 80 — Segundo registro “Auriculares Deluxe” con precio de 115

— Tercer y cuarto registros nuevos — Quinto registro “Impresora HP” tiene la descripción cambiada y nuevo Stock de 15 a 25 insert into ProductosDesarrollo values (‘8856′,’Lámpara Personal’,25.4,80) insert into ProductosDesarrollo values (‘8636′,’Auriculares Deluxe’,115,20) insert into ProductosDesarrollo values (‘9879′,’Switch Ethernet 993’,85,3) insert into ProductosDesarrollo values (‘4567′,’Memoria USB 16 GB’,50,10) insert into ProductosDesarrollo values (‘0665′,’Impresora HP Multifuncional’,65,25) go Antes del Merge select * from Planes.dbo.ProductosPlanes select * from Desarrollo.dbo.ProductosDesarrollo go

Haciendo el Merge La tabla Origen es Productos de la base de datos Desarrollo y la tabla Destino en Productos en la base de datos Planes Merge into Planes.dbo.ProductosPlanes as TablaDestino using Desarrollo.dbo.ProductosDesarrollo as TablaOrigen on (TablaDestino.ProductosCodigo = TablaOrigen.ProductosCodigo) when not matched then insert values (TablaOrigen.ProductosCodigo, TablaOrigen.ProductosDescripcion, TablaOrigen.ProductosPrecioUnitario, TablaOrigen.ProductosStockActual) when matched then update set ProductosDescripcion = TablaOrigen.ProductosDescripcion, ProductosPrecioUnitario = TablaOrigen.ProductosPrecioUnitario,

ProductosStockActual = TablaOrigen.ProductosStockActual; go Visualizar los resultados En la tabla destino ProductosPlanes se han insertados dos registros. select * from Planes.dbo.ProductosPlanes En la tabla origen de la base de datos Desarrollo los registros son los mismos. select * from Desarrollo.dbo.ProductosDesarrollo go La imagen muestra los resultados.

REGISTROS – VISTAS Insertar Registros 

By Trainer SQL in Registros - Vistas

Insertar Registros Para insertar registros en una tabla se utiliza la instrucción Insert. Se pueden agregar registros también utilizando una consulta como origen de los datos. Insert permite agregar uno a o mas registros a la vez. Sintaxis Insert [into] NombreTabla [ ( Lista de columnas ) ] VALUES ( DEFAULT | NULL | expression } [ ,…n] ) | Select….) Donde



Into

Palabra clave inicial para indicar a que tabla se insertarán los registros.

NombreTabla

Nombre de la tabla a insertar los registros.

Values

Palabra reservada para especificar los valores de cada campo en la lista de columnas.

Notas importantes: La columna que tiene activada la propiedad Identity no se espacifica en la lista de campos a insertar.



En lo posible se debe evitar los datos Null, los que ocurren cuando en una campo no se escribe el dato a ingresar.



Si no se especifican los campos a insertar los datos, SQL Server supone que se van a insertar todos los campos de la tabla.



Si la tabla tienen campos calculados, estos no se ingresan al usar Insert.



Es recomendable usar restricciones de tipo Default para evitar en las tablas los valores Null.



Los campos que deben de ser obligatorios al insertar un registro se deben crear usando la cláusula Not Null.



Para información de inserción de datos de tipo Image pulse aquí

Ejercicios Usando Northwind use Northwind go 1. Insertar un registro en Customers. En este ejercicio se insertará los datos de todos los campos. insert into Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) values (‘IRLUP’,’Iris Lupulo Luna Park’,’Ingrid Huamán’,’Manager’,’Av. El Sol 456′, ‘Madrid’,’SP’,’05432-043′,’España’,'(91) 555 93 46′,'(91) 666 44 92′) go 2. Los campos pueden escribirse en cualquier orden. insert into Customers (CustomerID, CompanyName, PostalCode, Country, Phone, Fax, ContactName, ContactTitle, Address, City, Region ) values (‘ARAMT’,’Aracnis Management Co.’,’05432-043′,’España’,'(91) 555 93 46′,'(91) 666 44 92′, ‘Esmeralda Vics’,’Sales Manager’,’Av. Los Algarrobos 4678′, ‘Barcelona’,’SP’) go — Importante: si se escriben los datos de todos los campos, se puede obviar la lista de estos. 3. Insertar dos nuevas regiones, la tabla Region solamente tiene los campos RegionID de tipo Int y RegionDescripcion de tipo nchar(50)

insert into Region values (6,’Antártida’),(7, ‘Bosques Albinos’) go

4. Crear una tabla con los productos descontinuados y luego insertar los registros. Los productos descontinuados son: Select * from Products where Discontinued = 1 go — Creamos la tabla Create table ProductosDescontinuados ( Codigo nchar(10), Descripcion nvarchar(100), Precio Numeric(9,2), Stock Numeric(8,2), Unidad nvarchar(50) ) go — Insertar los registros, usaremos un select con la misma cantidad de campos. insert into ProductosDescontinuados Select ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit from Products where Discontinued = 1 go — Para visualizar los registros. select * from ProductosDescontinuados go — Importante: note que el tipo de datos en la tabla ProductosDescontinuados para el campo Código es nchar(10) y la tabla Products originalmente el tipo es Int.

5. Una tabla con campos calculados. Crearemos una tabla con campos calculados, luego insertar registros. Create table Promedios ( PromediosCodigo nchar(4), PromedioPrimerValor Numeric(4,2), PromedioSegundoValor Numeric(4,2), PromedioTercerValor Numeric(4,2), PromediosResultado As (PromedioPrimerValor + PromedioSegundoValor + PromedioTercerValor)/3

) go — Insertar registros insert into Promedios values (‘8588’,12,16,14) go — Insertar varios al mismo tiempo insert into Promedios values (‘6789’,13,10,17),(‘9984’,3,12,18),(‘5737’,18,10,7) go — Al listar los registros puede verse el campo de Resultado calculado automáticamente. select * from Promedios go

Note en la imagen el campo PromediosResultado

Insertar imágenes desde SQL Server Management Studio 

By Trainer SQL in Esquemas - Tablas, Funciones en SQL Server, Registros - Vistas

Insertar imágenes en un campo Image desde SQL Server Al crear una tabla, es necesario con regularidad almacenar las imágenes de los registros, por ejemplo, la imagen de un empleado, la imagen de un producto, la imagen de una intervención quirúrgica, las fotos de un auto siniestrado en un sistema de seguros vehiculares, etc. En este artículo se muestra como insertar imágenes en SQL Server desde Microsoft SQL Server Management Studio. Las imágenes pueden ocupar un poco mas de espacio en el disco que los datos de tipo texto o los datos numéricos así como las fechas, si van a ser muchos registros, es recomendable separar las imágenes en un grupo de archivos diferente de donde están los datos del mismo registro usando la partición vertical de la tabla. (Ver Partición Vertical)

Ejemplo En este ejemplo se creará una base de datos y luego crear dos tablas, una con empleados y otra con productos. Luego insertaremos los registros incluyendo las fotos de los empleados y las imágenes de los productos. (Ver Insertar Registros) Para que los ejercicios funcionen, antes de ejecutar los scripts, cree las carpetas e inserte en ellas las imágenes a insertar.

Las fotos de los empleados se ubicarán en la carpeta D:\EmpleadosFotos y las imágenes de los productos estarán en la carpeta D:\ProductosImagenes La base de datos Create database BaseImagenes go use BaseImagenes go

La tabla para Empleados Create table Empleados ( EmpleadosCodigo nchar(6), EmpleadosPaterno varchar(50), EmpleadosMaterno varchar(50), EmpleadosNombre varchar(50), EmpleadosFechaNacimiento Date, EmpleadosEstado nchar(1), EmpleadosFoto Image, constraint EmpleadosPK primary key (EmpleadosCodigo) ) go Insertar los empleados. insert into Empleados (EmpleadosCodigo, EmpleadosPaterno, EmpleadosMaterno, EmpleadosNombre, EmpleadosFechaNacimiento, EmpleadosEstado, EmpleadosFoto) select ‘PR3498’, ‘Campos’,’Pardo’,’Luis’,’15/01/1997′, ‘A’, * from OpenRowset(Bulk ‘D:\EmpleadosFotos\CamposPardoLuis.jpg’, Single_Blob) As EmpleadosFoto go insert into Empleados (EmpleadosCodigo, EmpleadosPaterno, EmpleadosMaterno, EmpleadosNombre, EmpleadosFechaNacimiento, EmpleadosEstado, EmpleadosFoto) select ‘TF9085’, ‘Chávez’,’Terranova’,’Ingrid’,’10/02/1999′, ‘A’, * from OpenRowset(Bulk ‘D:\EmpleadosFotos\ChavezTerranovaIngrid.jpg’, Single_Blob) As EmpleadosFoto go insert into Empleados (EmpleadosCodigo, EmpleadosPaterno, EmpleadosMaterno, EmpleadosNombre, EmpleadosFechaNacimiento, EmpleadosEstado, EmpleadosFoto) select ‘PW0974’, ‘Pereda’,’Smith’,’Marco’,’18/09/1989′, ‘A’, * from OpenRowset(Bulk ‘D:\EmpleadosFotos\PeredaSmithMarco.jpg’, Single_Blob)

As EmpleadosFoto go insert into Empleados (EmpleadosCodigo, EmpleadosPaterno, EmpleadosMaterno, EmpleadosNombre, EmpleadosFechaNacimiento, EmpleadosEstado, EmpleadosFoto) select ‘FT4503’, ‘Villa’,’Benavente’,’Anne’,’13/03/1983′, ‘A’, * from OpenRowset(Bulk ‘D:\EmpleadosFotos\VillaBenaventeAnne.jpg’, Single_Blob) As EmpleadosFoto go insert into Empleados (EmpleadosCodigo, EmpleadosPaterno, EmpleadosMaterno, EmpleadosNombre, EmpleadosFechaNacimiento, EmpleadosEstado, EmpleadosFoto) select ‘NT4587’, ‘Llanos’,’Saenz’,’Jose’,’17/10/1963′, ‘A’, * from OpenRowset(Bulk ‘D:\EmpleadosFotos\LlanosSaenzJose.jpg’, Single_Blob) As EmpleadosFoto go Note el uso de la función OpenRowSet para insertar la imágen. Visualizar los empleados select * from Empleados go

Productos Create table Productos ( ProductosCodigo nchar(6), ProductosDescripcion varchar(100), ProductosPrecio Numeric(9,2), ProductosStock Numeric(9,2), ProductosEstado nchar(1), ProductosFoto Image, constraint ProductosPK primary key (ProductosCodigo) ) go

Insertar los Productos

insert into Productos (ProductosCodigo, ProductosDescripcion, ProductosPrecio, ProductosStock, ProductosEstado, ProductosFoto) select ‘T00799’, ‘Teclado’,85,15,’A’, * from OpenRowset(Bulk ‘D:\ProductosImagenes\Teclado.jpg’, Single_Blob) As ProductosFoto go insert into Productos (ProductosCodigo, ProductosDescripcion, ProductosPrecio, ProductosStock, ProductosEstado, ProductosFoto) select ‘T00599’, ‘Monitor’,260,10,’A’, * from OpenRowset(Bulk ‘D:\ProductosImagenes\Monitor.jpg’, Single_Blob) As ProductosFoto go insert into Productos (ProductosCodigo, ProductosDescripcion, ProductosPrecio, ProductosStock, ProductosEstado, ProductosFoto) select ‘T00777’, ‘Mouse’,185,25,’A’, * from OpenRowset(Bulk ‘D:\ProductosImagenes\Mouse.jpg’, Single_Blob) As ProductosFoto go Ver el listado de Productos select * from Productos go

Como usar OpenRowSet en SQL Server 

Se utiliza la función OPENROWSET para hacer referencia en la cláusula FROM de una consulta como si fuera un nombre de tabla.



Esta función OPENROWSET puede hacer referencia como la tabla de destino de una INSERT , UPDATE o DELETE



Si la consulta devuelve varios registros, OPENROWSET devuelve sólo el primero.



OPENROWSET también permite el uso del proveedor BULK incorporado que permite leer los datos de un archivo de imagen y crear el arreglo de bits para almacenarlo en un campo.



Actualizar datos – Update By Trainer SQL in Registros - Vistas

Actualización de los datos de los registros 

Los registros guardados en las tablas requieren en ocasiones que los datos de sus campos sean actualizados.



Para actualizar debemos usar la instrucción Update, se asume que se tienen los permisos para hacerlo.



Al ejecutar la instrucción Update se debe tener mucho cuidado en filtrar exactamente los registros a actualizar, de no utilizar de manera correcta el filtro con Where para los registros a actualizar, los cambios pueden afectar a registros que no se deben modificar y no hay forma de anular los cambios hechos. (Ver Filtros).



Asegure de realizar el filtro correcto, tenga en cuenta los diversos operadores de SQL Server.

Instrucción Update Permite la actualización de los datos de los registros de las tablas. Sintaxis La forma de escribir la instrucción Update para SQL Server es como sigue: UPDATE [ Top n [ Percent ] ] Tabla / Vista Set { NombreColumn = { expresión | DEFAULT | NULL } [ FROM{ } [ ,…n ] ] [ WHERE {

Donde Top n [Percent]

Especifica los primeros registros que se actualizarán, puede expresarse en po

Tabla / Vista

Nombre de la tabla o vista a actualizar. Puede incluir el servidor, el esquema

Set

Permite especificar las columnas de la tabla o vista que se actualizarán. Las c tiene Identity no se pueden actualizar.

Las columnas con restricciones Foreign Key, Check y Unique debe cumplir c From

Especifica la tabla origen para la actualización de los registros

Where

Especifica la condición o condiciones que debe de cumplir los registros para

Ejercicios Usando Northwind use Northwind go 1. Cambiar la dirección del cliente Island Trading a “Av. Camino Real 3993” update Customers set Address = ‘Av. Camino Real 3993’ where CompanyName = ‘Island Trading’ go 2. Ver el resultado select * from Customers where CompanyName = ‘Island Trading’ go 3. Actualizar los precios de los productos de la categoria Bebidas (Beverages). Aumentar en 3 unidades cada uno. Update Products set UnitPrice = UnitPrice + 3 where CategoryID = (Select CategoryID from Categories where CategoryName = ‘Beverages’) go Note la subconsulta usada en where. (Ver Subconsultas)

4. Actualizar los datos del proveedor Tokyo Traders (Código 4). Contacto: Sumiko Chavez, Cargo: Administrador, Dirección: 1005 Oklahoma St., Ciudad: Nagasaky Update Suppliers set ContactName = ‘Sumiko Chavez’, ContactTitle = ‘Administrador’, Address = ‘1005 Oklahoma St.’, City = ‘Nagasaky’ where SupplierID = 4 go 5. Actualizar la region de las órdenes que no han sido especificadas (Null) Cambiar el Null por una cadena de caracteres en blanco Las órdenes son que tienen Null en el campo ShipRegion son: select * from Orders where ShipRegion is null go update Orders set ShipRegion = ” where ShipRegion is null go — Importante: En las tablas en toda la base de datos no debería aparecer el valor de Null. 6. Disminuir el precio en 20% de los productos que tienen mas de 30 unidades en Stock sin incluir las unidades por atender.(UnitsInStock – UnitsOnOrder > 30) Update Products Set UnitPrice = UnitPrice * 0.80 where UnitsInStock – UnitsOnOrder > 30 go



Eliminar registros – Delete By Trainer SQL in Registros - Vistas

Eliminación de Registros Eliminar los registros de las tablas o vistas es una transacciones que debe tenerse mucho cuidado en ejecutar.



Importante: Es recomendable que los registros tengan una eliminación lógica, utilizar para esto un campo para manejar el Estado del registro.



En ocasiones no se podrá eliminar los registros por restricciones de tipo Foreign key. (Ver Cláves Foráneas)



En lo posible no implemente el borrado en cascada.



Puede usar transacciones para la eliminación física de registros para tener la posibilidad de anular la eliminación si se comete algún error.



Puede usar Truncate con algún cuidado especial para eliminar todos los registros de la tabla.



Recuerde que si se eliminan los registros en una tabla con un campo Identity, se debe restablecer la propiedad al valor inicial.(Ver Post Propiedad Identity)



Si se eliminan los registros físicamente, se sugiere guardarlos en un historial. (Ver Triggers – Historia de eliminados)

Sintaxis: Delete [ TOP ( expression ) [ PERCENT ] ] [ FROM ] Tabla [ WHERE ] Donde:

Top n [Percent]

Especifica los primeros registros que se eliminarán, puede expresarse en

Tabla

Nombre de la tabla donde se encuentran los registros a eliminar. Puede i tabla.

Where

Especifica la condición o condiciones que debe de cumplir los registros

Ejercicios Vamos a incluir algunos ejercicios, tenga cuidado con hacer estas pruebas, posiblemente se elimine información útil. — Eliminar los productos descontinuados Delete Products where Discontinued = 1 go — Eliminar los clientes de España Delete from Customers where Country = ‘Spain’ go — Eliminar los 5 productos mas caros. delete Products where ProductID in (select top 5 ProductID from Products order by UnitPrice desc) go — La instrucción anterior es correcta, la restricción de tipo Foreign key con la tabla “Order Details” no permite la eliminación. Se ha incluido una subconsulta. (Ver Subconsultas) 



Uso de Identity By Trainer SQL in Registros - Vistas

Uso Identity Identity es una propiedad que permite que un campo en una tabla incremente su valor de manera automática al insertar los registros en ella. Para el uso de la propiedad Identity el tipo de dato debe ser entero Int. Es necesario definir un valor inicial y un valor de incremento. Es importante anotar que Identity no asegura la unicidad de valor, esta únicamente es posible con la restricciones Primary key, Unique o con el índice Unique. Solamente puede existir una columna por tabla con la propiedad Identidad. Ejemplo En el ejemplo, se crea una tabla con la propiedad Identity. Se insertan registros, luego se eliminan, y si fuera posible siempre se debe tener en cuenta el valor de Identity. Create table Personas ( Codigo int Primary key not null identity(1,1) , NombreCompleto nvarchar(200) ) go Insertar registros insert into Personas (NombreCompleto) values (‘Arturo Pérez’),(‘Fernando Sánchez’),(‘Antonio Ríos’),(‘José Campos’) go Listar los registros. select * from Personas go Los registros anteriores toman los valores de Identidad del 1 al 4.

Para ver el valor de Indentity. Reporta Null si no se ha ingresado registros con Identidad. select @@IDENTITY go Si insertamos dos serían los números 5 y 6. insert into Personas (NombreCompleto) values (‘Rosa Alcántara’),(‘Yolanda Mejía’) go Si se eliminan físicamente los registros 5 y 6 quedarían del 1 al 4, al insertar uno nuevo ¿qué código debería tener? delete Personas where Codigo in (5,6) go Ahora quedar sólo del 1 al 4 Agregar el 5 insert into Personas (NombreCompleto) values (‘Pedro Picapiedra’) go Al insertar el registro, se creo con el valor de 7, que era el Identity que debería seguir.

Corregir, antes de agregar, se debe especificar el valor de Identity en 4 Borrar el registro primero delete Personas where Codigo = 7 go Usar DBCC CheckIdent DBCC CHECKIDENT (Tabla, RESEED ,Valor) Para regresar el valor de Identity a 4 dbcc checkident (Personas, Reseed, 4) go Ahora insertar el Quinto registro. insert into Personas (NombreCompleto) values (‘Pedro Picapiedra’) go El listado se muestra select * from Personas go

Si se borran todos los registros delete Personas dbcc checkident (Personas, Reseed, 0) go



Identity – Caso práctico By Trainer SQL in Registros - Vistas

Propiedad Identity – Caso 

La propiedad Identity puede ser asignada a un solo campo en la tabla, generalmente se usa para saber la cantidad de registros insertados, o para ayudar a construir un código.



No es recomendable usarla como columna de código ya que los códigos generalmente son datos de tipo caracter.



Se recomienda usar para los códigos de las tablas campos de tipo nchar. En este ejemplo se muestra como trabajar con la propiedad Identity y al borrar los últimos registros resetear la propiedad para que los siguientes registros insertados tengan el valor correcto correlativo. (Ver Uso de Identity) Crear la base de datos Create database SistemaER go use SistemaER go — Tabla con campo Identity Create table Datos ( DatosCodigo int Identity(1,1) primary key not null, DatosDescripcion nvarchar(50), DatosPrecio Numeric(9,2) ) go

— Insertar registros insert into Datos (DatosDescripcion, DatosPrecio)

values (‘Casaca’,180),(‘Pantalón’,800),(‘Sombrero’,250) go — Los código de los registros insertados son: 1,2 y 3 select * from Datos go

— Al insertar no se especifica el campo Identidad insert into Datos (DatosCodigo, DatosDescripcion, DatosPrecio) values (4,’Corbata’,60) go Mens. 544, Nivel 16, Estado 1, Línea 1 No se puede insertar un valor explícito en la columna de identidad de la tabla ‘Datos’ cuando IDENTITY_INSERT es OFF. — Insertar dos registro mas insert into Datos ( DatosPrecio, DatosDescripcion) values (390, ‘Zapatos’),(120,’Correo’) go — Los dos nuevos registros toman los códigos 4 y 5 select * from Datos go

— Borrar físicamente los registros 3, 4 y 5 (Ver Eliminar registros) delete Datos where DatosCodigo in (3,4,5) go — Quedaron solamente los registro 1 y 2, si se inserta un nuevo registro debería tomar el número 3, el valor de la identidad quedó en CINCO (Ver la propiedad con @@Identity) select @@IDENTITY go El valor de identity debe corregirse, insertamos un registro sin la corrección insert into Datos ( DatosPrecio, DatosDescripcion) values (1500, ‘Reloj’) go — Listar los registros select * from Datos go

El código del registro insertado debería ser TRES Primero: borrar el registro con el identidad incorrecto (Registro 6) delete Datos where DatosCodigo = 6 go — El valor de Identity debería ser 2, para que al insertar el nuevo registro sea el número 3 DBCC CheckIdent (Datos, Reseed, 2) go Se inserta ahora el Reloj… que debe ser el número 3 insert into Datos ( DatosPrecio, DatosDescripcion) values (1500, ‘Reloj’) go — Listado select * from Datos go

— Si se eliminan todos los registros físicamente delete Datos go

DBCC CheckIdent (Datos, Reseed, 0) go

— Uso de Identidad con valores inicial e incremento diferente de 1. Tabla Apuestas Create table Apuestas ( ApuestasCodigo int Identity(10,5) primary key not null, ApuestasDescripcion nvarchar(50), ApuestasPrecio Numeric(9,2) ) go — Insertar registros insert into Apuestas ( ApuestasPrecio, ApuestasDescripcion) values (1500, ‘Ganador Premio’),(50, ‘Empate’),(-60, ‘Perdedor’) go — Los códigos son: select * from Apuestas go



Vistas en SQL Server By Trainer SQL in Registros - Vistas

Como crear vistas en SQL Server 

La vista es un objeto en la base de datos creado como resultado de una consulta.



La vista se puede crear con datos de una o varias tablas de la base de datos.



Las vistas pueden guardar listados en la base de datos con un conjunto de registros de consulta frecuente.



Al usar una vista se puede restringir la visualización de algunas columnas que están presente en tablas.



Para mantener compatibilidad con versiones anteriores de SQL Server.



Puede que necesite crear índices en las tablas, ver Índices en vistas para mas información

Instrucción Create View Permite crear una vista Create View [ Esquema .] NombreVista AS Instrucción Select Donde: Esquema.NombreVista: especifica el nombre de la vista a crear. Instrucción Select: es la instrucción Select que define la vista.

Instrucción Alter View

Permite modificar una vista Alter View [ Esquema . ] NombreVista As Instrucción Select

Instrucción Drop View Permite eliminar una vista Drop view [ Esquema . ] NombreVista

Ejercicios Usando Northwind use Northwind go 1. Vista con los Productos descontinuados Create view VistaProductosDescontinuados As Select P.ProductID As ‘Código’, P.ProductName As ‘Descripción’, P.UnitPrice As ‘Precio’, P.UnitsInStock As ‘Stock’, P.UnitsInStock As ‘En Orden’ from Products As P where P.Discontinued = 1 go 2. Vista con los empleados Create view VistaEmpleadosListado As select EmployeeID As ‘Código’, Empleado = LastName + SPACE(1) + FirstName, Format(BirthDate,’dd/MM/yyyy’) As ‘Fecha Nac.’ from Employees go 3. Para listar los registros de la vista de Empleados select * from VistaEmpleadosListado order by [Fecha Nac.] go 4. Vista de Productos y Categorias

Create View vistaProductosyCategoria As select P.ProductID As ‘Código’, P.ProductName, P.UnitPrice As ‘Precio’, C.CategoryName from Products As P join Categories As C on P.CategoryID = C.CategoryID go 5. Cambiar la vista con mas campos Alter View vistaProductosyCategoria As select P.ProductID As ‘Código’, P.ProductName, P.UnitPrice As ‘Precio’, C.CategoryName, P.UnitsInStock As ‘Stock’, P.UnitsOnOrder As ‘En Orden’ from Products As P join Categories As C on P.CategoryID = C.CategoryID go 6. Vista con los Clientes que compraron mas de 2000 Create view VistaClientesTopComprasMas2000 As select C.CustomerID As ‘Código Cliente’, C.CompanyName As ‘Cliente’, C.Address As ‘Dirección’, C.Country As ‘País’, SUM(O.Freight) As ‘Total Comprado’ from Customers As C join Orders As O on C.CustomerID = O.CustomerID Group by C.CustomerID, C.CompanyName,C.Address, C.Country Having SUM(O.Freight) > 2000 go — Ver Funciones de Agregado, Joins, Agrupamientos

Importante 

En la definición de la consulta no se puede usar Order By salvo que se incluya Top.



Todas las columnas de la definición de la vista deben tener nombre.



El select de la definición de la vista no puede tener la cláusula Into Tabla.



La vista puede tener hasta 1,024 columnas.

7. Vista con Order by y Top Create view VistaClientesTopComprasMas2000Solo3 As

select Top 3 C.CustomerID As ‘Código Cliente’, C.CompanyName As ‘Cliente’, C.Address As ‘Dirección’, C.Country As ‘País’, SUM(O.Freight) As ‘Total Comprado’ from Customers As C join Orders As O on C.CustomerID = O.CustomerID Group by C.CustomerID, C.CompanyName,C.Address, C.Country Having SUM(O.Freight) > 2000 order by SUM(O.Freight) desc go



Secuencias en SQL Server By Trainer SQL in Esquemas - Tablas, Registros - Vistas

Como crear Secuencias en SQL Server Se puede definir una secuencia como un conjunto de valores que parten de un valor inicial, tienen un incremento o decremento, lo que significa que la secuencia puede ser ascendente o descendente y pueden tener un valor final. SQL Server permite la creación de secuencias que pueden ser utilizadas para la generación de códigos en las tablas. Lo más importante de las secuencias es que no están ligadas a ningún campo en una tabla. Se recomienda usar la opción de Secuencias en lugar de usar la propiedad Identity, es necesario incidir en sugerir adicionalmente que no use la propiedad Identity.

Tipos de datos permitidos en secuencias El tipo de dato de la secuencia es un dato Entero, los tipos de datos permitidos son Tipo de dato

Valores

Tinyint

Rango 0 to 255

smallint

Rango -32,768 to 32,767

int

Rango -2,147,483,648 to 2,147,483,647

bigint

Rango -9,223,372,036,854,775,808 to 9,223,372,036,8 Este es el tipo de dato por defecto.

decimal y numeric

con una escala de CERO.

Un tipo de dato definido por el usuario creado en base a los tipos anteriores. (Ver tipos de datos definidos por el usuario)

La propiedad Identity Identity es una propiedad que permite que un campo en una tabla incremente su valor de manera automática al insertar los registros en ella. Para el uso de la propiedad Identity el tipo de dato debe ser entero Int. Es necesario definir un valor inicial y un valor de incremento. Es importante anotar que Identity no asegura la unicidad de valor, esta únicamente es posible con la restricciones Primary key, Unique o con el índice Unique. Solamente puede existir una columna por tabla con la propiedad Identidad. (Ver Identity)

Secuencia vs. Identity 

En SQL Server se debe usar una secuencia en lugar de la propiedad Identity en los siguientes casos: La aplicación requiere obtener el valor antes de insertar el registro.



La aplicación requiere compartir series de números entre multiples tablas o multiples columnas en las tablas.



La aplicación requiere reiniciar el valor de la serie con un valor especíifico. Por ejemplo, reiniciar una secuencia que fue creada desde 1 hasta 100 con los mismos valores.



La aplicación requiere valores que son ordenados por otro campo. La instrucción “NEXT VALUE FOR function” puede aplicarse la cláusula Over en la función de llamada.



Una aplicación requiere múltiples valores asignados al mismo tiempo. Por ejemplo, una aplicación necesita obtener tres números seguidos al mismo tiempo.

Como crear una secuencia en SQL Server

Instrucción Create Sequence Crea una secuencia en SQL Server. Create Sequence [Esquema. ] NombreDeSecuencia [ AS [ TipoEntero | TipoEnteroDefinidoPorElUsuario ] ] [ START WITH ] [ INCREMENT BY ] [ { MINVALUE [ ] } | { NO MINVALUE } ] [ { MAXVALUE [ ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] Donde NombreDeSecuencia: es el nombre de la secuencia a crear. TipoEntero: Tipo de dato entero de SQL Server. La tabla está definida líneas arriba. TipoEnteroDefinidoPorElUsuario: Tipo de dato definido por el usuario en base a los números enteros de SQL Server. (Ver tipos de datos definidos por el usuario) Start With: define el valor inicial Increment by: Define el incremento o decremento. MinValue: Especifica el valor mínimo, por defecto es CERO para el tipo tinyint y un valor negativo para el resto de tipos. MaxValue: Especifica el valor máximo. El valor por defecto está definido de acuerdo al valor máximo del tipo de dato entero. (Ver tabla arriba) Cycle: Permite que la secuencia se reinice cuando llega a su valor mínimo o máximo, dependiendo si es ascendente o descendente.

Ejercicios Usando la base de datos Northwind USE Northwind go 1. Crear una secuencia con los valores por defecto Create sequence ValoresPorDefecto go Para visualizar los datos de la secuencia Select name, start_value, increment, maximum_value, minimum_value, is_cycling, type, system_type_id, current_value from sys.sequences where name = ‘ValoresPorDefecto’ go

Note que el ID del tipo de dato es 127, para visualizar el tipo de dato select * from sys.types where system_type_id = 127 go

Para obtener el valor inicial de acuerdo al tipo de dato bigint. Tenga en cuenta que al ejecutar la siguiente instrucción, el valor de la secuencia se va incrementando 1. En la tabla en la parte superior se puede visualizar el rango del tipo de dato binint. select next value for ValoresPorDefecto go 2. Crear una secuencia llamada EquipoBasket que inicia en 1 y termina en 12. Create sequence EquipoBasket As int start with 1 increment by 1 minValue 1 maxvalue 12 Cycle go Visualizar los valores, ejecute mas de 12 veces la siguiente instrucción. Note que al llegar al valor máximo se reinicia en el valor mínimo por la especificación de la cláusula Cycle. select next value for EquipoBasket go Eliminar la secuencia Drop sequence EquipoBasket go 3. Crear una secuencia que permita especificar el código para los departamentos en una empresa. Create sequence SecuenciaDepartamentos As tinyint Start With 1

INCREMENT By 1 go Crear la tabla de Departamentos Create Table Departamentos ( DepartamentosCodigo tinyint, DepartamentosDescripcion nvarchar(150), constraint DepartamentosPK Primary key (DepartamentosCodigo) ) go

Usar la secuencia para obtener el valor para el código usando NEXT VALUE FOR… Insertar Departamentos Insert into Departamentos (DepartamentosCodigo,DepartamentosDescripcion) values (Next value FOR SecuenciaDepartamentos, ‘Gerencia General’) go Insert into Departamentos (DepartamentosCodigo,DepartamentosDescripcion) values (Next value FOR SecuenciaDepartamentos, ‘Producción’) go Insert into Departamentos (DepartamentosCodigo,DepartamentosDescripcion) values (Next value FOR SecuenciaDepartamentos, ‘Contabilidad’) go Consultar la tabla Select * from Departamentos go

Se sugiere crear algún algoritmo o código para generar un código que no sea únicamente números. 4. Ver las secuencias creadas en la base de datos

select * from sys.sequences go (adsbygoogle = window.adsbygoogle || []).push({});

Modificación de una Secuencia Instrucción Alter Sequence Modifica los argumentos de una secuencia existente. Importante: para cambiar el tipo de dato numérico de la secuencia, esta se debe eliminar y luego volver a crear con el nuevo tipo. Sintaxis: ALTER SEQUENCE [Esquema. ] NombreDeSecuencia [ RESTART [ WITH ] ] [ INCREMENT BY ] [ { MINVALUE } | { NO MINVALUE } ] [ { MAXVALUE } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] Donde: NombreDeSecuencia: es el nombre de la secuencia a modificar. Restart With: define el valor en el que reiniciará la secuencia. Increment by: Define el incremento o decremento. MinValue: Especifica el valor mínimo, por defecto es CERO para el tipo tinyint y un valor negativo para el resto de tipos. MaxValue: Especifica el valor máximo. El valor por defecto está definido de acuerdo al valor máximo del tipo de dato entero. (Ver tabla arriba) Cycle: Permite que la secuencia se reinice cuando llega a su valor mínimo o máximo, dependiendo si es ascendente o descendente.

Ejercicios 5. Crear una secuencia con valores por defecto y luego modificarla para que su valor inicial sea 10 y se incremente de 5 en 5 Create sequence PruebaCambio go Alter sequence PruebaCambio restart with 10 increment by 5 go

Visualizar los valores de la secuencia Select name, start_value, increment, maximum_value, minimum_value, is_cycling, type, system_type_id, current_value from sys.sequences where name = ‘PruebaCambio’ go

Eliminar un secuencia Instrucción Drop Sequence Elimina una secuencia de la base de datos Sintaxis: Drop sequence [Esquema.]NombreSecuencia Donde: Esquema: es el nombre del esquema donde se encuentra la secuencia. (Ver esquemas) NombreSecuencia: nombre de la secuencia a eliminar.

Ejercicios 6. Eliminar la secuencia PruebaCambio Drop sequence PruebaCambio go



Mostrar Vistas en Excel By Trainer SQL in Registros - Vistas

Mostrar Vistas en Excel Una de las mejores soluciones para los usuarios de la organización que requieran de información de la base de datos es crearles una vista y luego presentar esta en Excel. La vista en Excel va a mantener una conexión con la base de datos, de esta manera el usuario cuando abra el archivo con la vista presente en la base de datos de SQL Server podrá tener la información en tiempo real y actualizada. El usuario para que pueda mostrar los datos reales de la base de datos debe actualiza la vista, para ello puede pulsar botón derecho en cualquier celda de la vista y seleccionar Actualizar. En este artículo se enseñará a visualizar en Excel una vista creada en SQL Server.

Para crear una vista vea Vistas en SQL Server Usando Northwind use Northwind go Crear una vista con los productos, su categoría y proveedor. (Ver Vistas) Create View VistaProductosConCategoriaYProveedor As select P.ProductID As ‘Cód. Producto’, P.ProductName As ‘Descripción’, P.UnitPrice As ‘Precio’, P.UnitsInStock As ‘Stock’, C.CategoryName As ‘Categoría’ , S.CompanyName As ‘Proveedor’

from Products As P join Categories As C on P.CategoryID = C.CategoryID join Suppliers As S on P.SupplierID = S.SupplierID go Para visualizar en Excel esta vista, se debería crear un inicio de sesión (Ver inicios de sesión) al cual se le debe dar el acceso para visualizar la vista, y poder conectarse a SQL Server. (Ver Grant – Permisos)

El procedimiento para mostrar la vista en Excel es el siguiente: 1. Abrir Excel, ir a la opción Datos del menú, en el grupo “Obtener datos externos” desplegar el botón “De otras fuentes” y seleccionar “Desde SQL Server”

2. Aparece la ventana para autenticarse, se debe escribir el nombre del servidor y la instancia a la que se va a conectar, especificar también las credenciales de conexión, puede ser Windows o de manera correcta y mas segura, especificar el

Nombre de usuario y Contraseña de un Inicio de Sesión de SQL Server. Luego pulsar Siguiente.

3. Una vez conectado al servidor se debe seleccionar la base de datos y seleccionar la vista que se desea mostrar en Excel. Pulsar Siguiente.

4. Luego escribir una descripción adecuada para la conexión creada entre Excel y la vista seleccionada. Pulsar Finalizar.

5. Aparece la ventana para especificar la forma de como se presentará la vista de SQL Server en Excel. Se recomienda que sea incluida como tabla. Además seleccionar la celda de la esquina superior izquierda desde donde se mostrará el contenido de la vista. Pulsar Aceptar para mostrar los datos.

6. La apariencia final de la vista podría ser como la imagen siguiente. Note que se ha asignado una etiqueta a la hoja además de asignar un título a la vista. Además se ha desactivado el filtro automático.

Al actualizar los datos con los de la base de datos desde Excel, se debe pulsar botón derecho en cualquier celda de la lista y luego elegir la opción Actualizar.



Recomendaciones: No cree más de una consulta en una hoja de Excel, es preferible una consulta en cada hoja.



Puede administrar las conexiones de las diferentes vistas mostradas en Excel usando el botón Conexiones de la opción Datos del menú de Excel.



El archivo de Excel puede transportarse a otro equipo, al abrirlo perderá la conexión con el origen de datos.



Encriptar y desencriptar datos By Trainer SQL in Registros - Vistas

Encriptar y Desencriptar datos en SQL Server La importancia de proteger los datos es una actividad que no se debe descuidar, cuando se guarda información en las tablas, algunos datos no deben ser fácilmente accesibles y si de alguna forma se pueden acceder a estos, deben en los posible presentarse de manera encriptada.

Tipo de dato Binarios Los tipos de datos binarios permiten guardar datos en un formato que se muestra diferente al texto plano, estos tipos de datos tienen una longitud fija (binary) y logitud variable (varbinary). Cuando desee encriptar los datos de las tablas de su base de datos siguiendo las instrucciones de este ejemplo, los campos que va a encriptar deben ser de estos tipos.

Encriptando los datos Existen varias formas de guardar los datos encriptados en las tablas, se pueden usar diversos algoritmos o se pueden crear y generar los propios. Por ejemplo, puede generar datos encriptados usando Visual Studio .Net y el algoritmo MD5. Para este ejercicio utilizaremos las funciones de SQL Server para guardar los datos en formato binario, para ello se puede usar la función EncryptByPassPhrase para encriptar y para desencriptar se utiliza la función DecryptByPassPhrase. Para poder mostrar los datos sin encriptar se utilizará Cast para convertir el tipo de dato. (Ver funciones de conversión de datos)

Función EncryptByPassPhrase Permite encriptar datos, asignando una clave de tipo texto. Sintaxis: EncryptByPassPhrase ( ‘FraseClave’ , ‘TextoAEncriptar’ ) Donde: FraseClave: se utilizará como clave simétrica para poder desencriptar. TextoAEncriptar: Texto plano a encriptar.

Función DecryptByPassPhrase Permite desencriptar datos, es necesario dar la clave simétrica asignada al momento de encriptar. Sintaxis: DecryptByPassPhrase ( ‘FraseClave’ , ‘TextoADesencriptar’ ) Donde: FraseClave: es clave simétrica utilizada al encriptar. TextoADesencriptar: Texto a desencriptar, generalmente es el contenido de un campo. Importante: Los campos donde se guardarán los datos deben ser de tipo binarios (varbinary).

Ejemplo Usando la base de datos Northwind. use Northwind go Crear una tabla para usuarios, donde se encriptará tanto el nombre del usuario como su password. Create table Usuarios ( UsuariosCodigo nchar(4), UsuariosNombre varbinary(200), UsuariosPassword varbinary(200) constraint UsuariosCodigoPK Primary key (UsuariosCodigo) ) go Insertar los siguientes registros Nombre: TrainerSQL Password: Bum2020 insert into usuarios values (‘0102’,EncryptByPassPhrase(‘ClaveUsuarios’,’TrainerSQL’),

EncryptbyPassphrase(‘ClaveUsuarios’,’Bum2020′)) go Listar el registros para visualizar los datos encriptados select * from Usuarios go

Si se desea listar los registros con los datos desencriptados select UsuariosCodigo, Cast(DecryptByPassPhrase(‘ClaveUsuarios’, UsuariosNombre) As varchar(max)) As ‘Nombre’, Cast(DecryptByPassPhrase(‘ClaveUsuarios’, UsuariosPassword) As varchar(200)) As ‘Password’ from Usuarios go

Si el texto usado como clave simétrica no es el correcto, muestra Null en los campos encriptados. Note que para desencriptar se ha utilizado la clave simétrica OtraClave, que no la correcta. select UsuariosCodigo, Cast(DecryptByPassPhrase(‘OtraClave‘, UsuariosNombre) As varchar(max)) As ‘Nombre’, Cast(DecryptByPassPhrase(‘OtraClave‘, UsuariosPassword) As varchar(200)) As ‘Password’ from Usuarios go



Cursores By Trainer SQL in Programación

Como crear cursores en SQL Server Los cursores permiten almacenar los datos de una consulta T-SQL en memoria y poder manipular los datos de los elementos resultantes para realizar operaciones con ellos. Proceso para declarar, abrir, usar, cerrar y liberar los datos de un cursor 1. Declarar el cursor, utilizando DECLARE Cursor 2. Abrir el cursor, utilizando OPEN 3. Leer los datos del cursor, utilizando FETCH … INTO 4. Cerrar el cursor, utilizando CLOSE 5. Liberar el cursor, utilizando DEALLOCATE

Como crear un cursor en SQL Server 1. Para declarar el cursor DECLARE NombreCursor [Scroll] CURSOR FOR Instrucción Select 2. Abrir el cursor OPEN NombreDelCursor 3. Lectura de los datos del cursor, va a depender del tipo de cursor. Hay de avance hacia adelante solamente y Scroll. FETCH NombreDelCursor Si se va a recorrer el cursor, se debe almacenar los datos de cada registro en variables previamente definidas con la variante siguiente: FETCH NombreDelCursor INTO ListaVariables

Para lectura de manera automática de los registros del cursor se usa la estructura While, el bucle de esta estructura se ejecutará ciempre que la función @@FETCH_STATUS sea igual a CERO. La función @@FETCH_STATUS reporta CERO (0) cuando la instrucción Fetch un registro. Al finalizar @@FETCH_STATUS toma el valor de -1. FETCH NombreDelCursor INTO ListaVariables WHILE (@@FETCH_STATUS = 0) BEGIN … instrucciones del bloque FETCH NombreDelCursor INTO ListaVariables END — FIN DEL BUCLE WHILE

Cursor de tipo Scroll Los cursores de tipo Scroll se pueden recorrer hacia adelante o hacia atrás. Para un cursor de tipo Scroll se pueden usar: Firts, Next, Prior, Last, Relative n, Absolute n para mostrar los diferentes registros. 4. Cerrar el cursor CLOSE NombreDelCursor 5. Liberar el espacio de memoria ocupado por el cursor DEALLOCATE NombreDelCursor

Ejercicios 1. Cursor que reporta los Empleados Declare cursorEmpleados cursor for select EmployeeID, LastName, FirstName from Employees go — Abrir el cursor open cursorEmpleados go — Los datos disponibles se visualizar con Fetch, ejecutar varias veces para ver los resultados. Fetch cursorEmpleados go — La función @@FETCH_STATUS reporta CERO si hay registro y reporta -1 si ya no existen registros para mostrar. (Ver Funciones de Cursores) select @@FETCH_STATUS go

— Cerrar el cursor, liberar de memoria close cursorEmpleados Deallocate cursorEmpleados go 2. Cursor que reporte la lista de productos, si las unidades en orden son mayores al stock, mostrar COMPRAR URGENTE, de lo contrario mostrar STOCK ADECUADO Nota: El ejercicio muestra mensajes, los que en la práctica no son realmente útiles. Declare cursorProductoComprasUrgente cursor for SELECT P.ProductID, P.ProductName, P.UnitsInStock, P.UnitsOnOrder from Products As P Open cursorProductoComprasUrgente Declare @Codigo int, @Descripcion nvarchar(40), @Stock Numeric(9,2), @PorAtender Numeric(9,2) Fetch cursorProductoComprasUrgente into @Codigo, @Descripcion, @Stock, @PorAtender Print ‘================ LISTADO ======================’ While (@@FETCH_STATUS = 0) Begin Declare @Mensaje nvarchar(20) If (@PorAtender > @Stock) Begin Set @Mensaje = ‘COMPRAR URGENTE’ End Else Begin Set @Mensaje = ‘STOCK ADECUADO’ End — reportar el registro y luego leer el siguiente Print ‘Código: ‘ + STR(@Codigo) Print ‘Descripción: ‘ + @Descripcion + ‘ Stock: ‘ + Ltrim(STR(@Stock)) + ‘ Por Atender: ‘ + Ltrim(Str(@PorAtender)) Print ‘Mensaje: ‘ + @Mensaje Print ” Print ‘————————————————‘ Fetch cursorProductoComprasUrgente into @Codigo, @Descripcion, @Stock, @PorAtender End Close cursorProductoComprasUrgente Deallocate cursorProductoComprasUrgente go

Cursor de tipo Scroll 3. Cursor Scroll para Categorias

Declare cursorCategorias Scroll cursor for select * from Categories Open cursorCategorias go Note que en la definición del cursor se ha utilizado la palabra Scroll. — Mostrar los datos Fetch cursorCategorias go — Registro 6 Fetch Absolute 6 from cursorCategorias go — Siguiente Fetch Next from cursorCategorias go — Anterior Fetch Prior from cursorCategorias go — Último Fetch Last from cursorCategorias go — Tres anteriores Fetch Relative -3 from cursorCategorias go — Dos hacia adelante Fetch Relative 2 from cursorCategorias go — Primero Fetch First from cursorCategorias go — Cerrar y Liberar Close cursorCategorias Deallocate cursorCategorias go

Como crear cursores anidados en SQL Server 4. Listado de las categorías y sus productos Declare cursorCategoriasListadoProductos cursor for select C.CategoryID, C.CategoryName from Categories As C Open cursorCategoriasListadoProductos

Declare @CodigoCategoria int, @NombreCategoria nvarchar(15) Fetch cursorCategoriasListadoProductos into @CodigoCategoria, @NombreCategoria Print ‘============================================================’ Print ‘============ Listado de Productos por categoria ==========’ Print ‘============================================================’ Print ” While (@@FETCH_STATUS = 0) — Recorre el cursor de Categorías Begin Print ‘Código Categoria: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(2) + ‘Categoría: ‘ + @NombreCategoria — Definir el Cursor para los productos – CURSOR ANIDADO Declare cursorProductosCategoria cursor for select P.ProductID, P.ProductName, P.UnitPrice from Products As P where P.CategoryID = @CodigoCategoria Open cursorProductosCategoria Declare @CodigoProducto int, @NombreProducto nvarchar(40), @Precio Numeric(9,2) Fetch cursorProductosCategoria into @CodigoProducto, @NombreProducto, @Precio Print ” Print ‘====================== PRODUCTOS ==========================’ Print ‘ CODIGO DESCRIPCION PRECIO ‘ While (@@FETCH_STATUS = 0) — Estructura para Productos Begin Print Space(5) + Ltrim(str(@CodigoProducto)) + Space(10) + @NombreProducto + space(20) + Ltrim(str(@Precio)) Fetch cursorProductosCategoria into @CodigoProducto, @NombreProducto, @Precio End — Final While de Productos Print ” Close cursorProductosCategoria Deallocate cursorProductosCategoria — Leer la siguiente categoria Fetch cursorCategoriasListadoProductos into @CodigoCategoria, @NombreCategoria

End — Final While de Categorias Close cursorCategoriasListadoProductos Deallocate cursorCategoriasListadoProductos go

5. Incluir Cantidad de Productos, Valor del Stock por categoria Cantidad Total del Productos y Valor total del Stock Es necesario usar Acumuladores. Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories Open cursorCategorias Declare @CodigoCategoria Integer, @NombreCategoria nvarchar(15) Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria Declare @CantidadTotaldeProductos Numeric(9,2), @ValorTotaldelStock Numeric(9,2) — Variables para totales set @CantidadTotaldeProductos = 0 Set @ValorTotaldelStock = 0 Print ‘=====================================’ Print ‘=====PRODUCTOS POR CATEGORIA=========’ Print ‘=====================================’ While (@@FETCH_STATUS = 0) Begin Print ‘Código Categoria: ‘ + Ltrim(Str(@CodigoCategoria)) Print ‘Nombre Categoria: ‘ + Ltrim(@NombreCategoria) Print ‘===================================================’ — Definir el cursor para los productos de la categoria actual Declare cursorProductoPorCategoria cursor for select ProductID, ProductName, UnitPrice, UnitsInStock from Products where CategoryID = @CodigoCategoria Open cursorProductoPorCategoria Declare @CodigoProducto Integer, @NombreProducto nvarchar(40), @Precio Numeric(9,2), @Stock Numeric(9,2) Fetch cursorProductoPorCategoria into @CodigoProducto, @NombreProducto, @Precio, @Stock Declare @CantidadProductosPorCategoria Numeric(9,2), @ValorStockPorCategoria Numeric(9,2) Set @CantidadProductosPorCategoria = 0 Set @ValorStockPorCategoria = 0 While (@@FETCH_STATUS = 0) — Para Productos Begin Print ‘Código: ‘ + Ltrim(Str(@CodigoProducto)) + ‘ Descripción: ‘ + Ltrim(@NombreProducto)

— Acumular Set @CantidadProductosPorCategoria = @CantidadProductosPorCategoria + @Stock Set @ValorStockPorCategoria = @ValorStockPorCategoria + @Precio* @Stock Fetch cursorProductoPorCategoria into @CodigoProducto, @NombreProducto, @Precio,@Stock End Print ‘Cantidad Productos: ‘ + Ltrim(Str(@CantidadProductosPorCategoria)) Print ‘Valor del Stock: ‘ + Ltrim(Str(@ValorStockPorCategoria)) Close cursorProductoPorCategoria Deallocate cursorProductoPorCategoria Print ” — Acumulado Total Set @CantidadTotaldeProductos = @CantidadTotaldeProductos + @CantidadProductosPorCategoria Set @ValorTotaldelStock = @ValorTotaldelStock + @ValorStockPorCategoria Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria End Print ‘Cantidad Total Productos: ‘ + Ltrim(Str(@CantidadTotaldeProductos)) Print ‘Valor Total del Stock: ‘ + Ltrim(Str(@ValorTotaldelStock)) Close cursorCategorias Deallocate cursorCategorias go El resultado se muestra para la primera categoría en la siguiente imagen





Cursores – Casos prácticos By Trainer SQL in Programación

Cursores Elemento que almacena en memoria el resultado de un Select. Para mayor información ver Cursores en SQL Server Pasos: 1. Declarar el cursor, utilizando DECLARE 2. Abrir el cursor, utilizando OPEN 3. Leer los datos del cursor, utilizando FETCH … INTO 4. Cerrar el cursor, utilizando CLOSE 5. Liberar el cursor, utilizando DEALLOCATE Las sintaxis de las instrucciones es como sigue: DECLARE CURSOR FOR OPEN FETCH INTO WHILE (@@FETCH_STATUS = 0) BEGIN FETCH INTO … END — FIN DEL BUCLE WHILE CLOSE DEALLOCATE

Ejercicios Cursor que liste las categorias, el resultado se muestra como sigue: 1 Beverages 2 Condiments 3 Confections 4 Dairy Products 5 Grains/Cereals 6 Meat/Poultry 7 Produce 8 Seafood */ Crear y mostrar los resultados manualmente. Declare cursorCategorias Cursor for select CategoryID, CategoryName from Categories go Abrir Open cursorCategorias go Para mostrar las categorías usar la siguiente instrucción, repetir hasta terminar el listado. Fetch cursorCategorias go select @@FETCH_STATUS — 0 cuando existen registros por leer, -1 cuando ya no existen registros go Cerrar el cursor close cursorCategorias Liberar el cursor de la memoria. Deallocate cursorCategorias go Listado automático usando While y variables Declare cursorCategorias Cursor for select CategoryID, CategoryName from Categories Open cursorCategorias Declare @CodigoCategoria int, @NombreCategoria nvarchar(15) Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria WHILE (@@FETCH_STATUS = 0) BEGIN Print ‘Código: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(2) + ‘Nombre: ‘ + @NombreCategoria Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria END — FIN DEL BUCLE WHILE CLOSE cursorCategorias DEALLOCATE cursorCategorias go

El resultado se muestra en la imagen.

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ÓN DEL CURSOR DE ACTUALIZACION DECLARE ActualizaPreciosProductos CURSOR FOR SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products FOR UPDATE — APERTURA DEL CURSOR OPEN ActualizaPreciosProductos — DECLARACION DE VARIABLES PARA EL CURSOR DECLARE @ID INT, @NOMBRE VARCHAR(255), @PRECIO DECIMAL, @StockActual INT — LECTURA DE LA PRIMERA FILA DEL CURSOR FETCH ActualizaPreciosProductos INTO @ID, @NOMBRE, @PRECIO, @StockActual — MIENTRAS PUEDA LEER EL REGIStockActualRO WHILE (@@FETCH_Status = 0 ) BEGIN IF(@StockActual>=100) Begin SET @PRECIO = 0.5*@PRECIO End ELSE Begin SET @PRECIO = 0.80*@PRECIO End UPDATE Products SET UnitPrice = @PRECIO WHERE CURRENT OF ActualizaPreciosProductos

–IMPRIMIR PRINT ‘EL PRECIO DE PRODUCTO ‘+ @NOMBRE+ ‘ ES ‘ + Str(@PRECIO) — LECTURA DE LA SIGUIENTE FILA DEL CURSOR FETCH ActualizaPreciosProductos INTO @ID, @NOMBRE, @PRECIO, @StockActual END — CIERRE DEL CURSOR CLOSE ActualizaPreciosProductos — LIBERAR LOS RECURSOS DEALLOCATE ActualizaPreciosProductos go CURSORES ANIDADOS Cursor que muestre las categorías y de cada categoría los productos Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories Open cursorCategorias Declare @CodigoCategoria int, @NombreCategoria nvarchar(15) Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria While (@@FETCH_STATUS = 0) Begin Print ‘============================================================== ===========================’ Print ‘Código: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(3) + ‘ Categoria: ‘ + @NombreCategoria Print ‘============================================================== ===========================’ — Crear el cursor para leer los productos de la categoría actual Declare cursorProductosPorCategoria cursor for select ProductID, ProductName, UnitPrice from Products where CategoryID = @CodigoCategoria Open cursorProductosPorCategoria Declare @CodigoProducto int, @NombreProducto nvarchar(40),@Precio Decimal Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio While (@@FETCH_STATUS = 0) Begin Print ‘Código: ‘ + Ltrim(Str(@CodigoProducto)) + Space (3) + ‘Descripción: ‘ + @NombreProducto Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio End Close cursorProductosPorCategoria Deallocate cursorProductosPorCategoria Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria End close cursorCategorias

Deallocate cursorCategorias go El resultado con las dos primeras categorías y sus productos se muestran en la imagen siguiente:

Cursor Scroll: Para leer los datos del cursor definido como Scroll, en la instrucción Fetch se puede usar las siguientes palabras para desplazarse. — First – Primer registro — Last – Último registro — Next – Siguiente Registro — Prior – Registro Anterior — Relative n – Positivo Avanza n

— Negativo Retrocede n — Absolute n – Registro n — NO OLVIDAR USAR LA CLAUSULA…. FROM — Crear un cursor Scroll con las categorias Declare cursorScrollCategorias Scroll Cursor for select * from Categories Open cursorScrollCategorias go — Recién abierto… la siguiente instrucción lee el primer registro Fetch cursorScrollCategorias go — Primero Fetch First from cursorScrollCategorias go — Último Fetch Last from cursorScrollCategorias go — Retroceder 3 Fetch Relative -3 from cursorScrollCategorias go — Al número 6 Fetch Absolute 6 from cursorScrollCategorias go — Siguiente Fetch next from cursorScrollCategorias go — Anterior Fetch prior from cursorScrollCategorias go — Cerrar y liberar Close cursorScrollCategorias Deallocate cursorScrollCategorias go



Triggers – Como crear un historial de registros eliminados en SQL Server 

By Trainer SQL in Esquemas - Tablas, Programación, Registros - Vistas

Trigger historial de eliminados Los Triggers son una herramienta muy poderosa para asegurar integridad de datos, posibilidad de recuperar los datos si se usa una eliminación usando Delete, guardar historial de acciones para efectos de auditoria (Ver Historial con Triggers), acciones que pueden reemplazar a la inserción (Ver Triggers Instead of), etc.

Eliminación de registros La eliminación de registros de una tabla se puede hacer usando la instrucción Delete (Ver Eliminación de registros), lo que en ocasiones no pueda ejecutarse debido a restricciones de tipo Foreign Key (Ver Claves Foráneas). Un registro eliminado con Delete es imposible de recuperar, se recomienda el borrado lógico, es decir, usando un campo que puede ser de tipo caracter donde se pueda guardar datos como A de Activo y E de Eliminado.

Como crear un historial de registros eliminados En este artículo se creará una tabla de Historial de registros eliminado para la tabla Productos (Products), al eliminar un registro con Delete, se utilizará un Trigger para guardar en la tabla historial el registro eliminado.

Como crear el historia con un trigger en SQL Server

1. Primero crear una tabla con la misma estructura de Products La tabla a crear es recomendable que no tenga restricciones, es posible que se elimine mas de una vez un registro y debería guardarse en la tabla, de preferencia con la fecha de eliminación. USE Northwind go CREATE TABLE dbo.ProductosHistorialEliminados ( ProductID int NOT NULL, ProductName nvarchar(40) NOT NULL, SupplierID int NULL, CategoryID int NULL, QuantityPerUnit nvarchar(20) NULL, UnitPrice money , UnitsInStock smallint , UnitsOnOrder smallint , ReorderLevel smallint , Discontinued bit , FechaEliminacion Datetime ) go Note que se ha incluido un campo para la fecha de eliminación del tipo DateTime para guardar el momento exacto de la eliminación. Puede guardar el usuario, el equipo y algunos datos posiblemente necesarios. (Ver Triggers DDL)

2. Crear el Trigger para guardar los datos del registro eliminado El Trigger que va a guardar los datos del registro eliminado usando Delete en la tabla Historial de Productos será como sigue Create trigger trProductosGuardaHistorialEliminado on Products for Delete As insert into ProductosHistorialEliminados select *, GetDate() from deleted go Insertaremos algunos registros en Products para luego poder eliminarlos. (Ver Insertar registros) insert into Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) values (‘Ron Solera’,6,1,’Bot. 750ml’,70,40,0,20,0), (‘Pavita’,3,6,’Uni. 7kg’,170,30,0,10,0),

(‘Queso Parmesano’,8,4,’Env. x 200gr’,50,20,5,10,0) go Los registros insertados tienen los códigos 78, 79 y 80 respectivamente. select * from Products go

Eliminar registros Primero intentaremos eliminar productos que tienen registros en detalle de venta, por ejemplo, el registro con código 10. Delete Products where ProductID = 10 go El mensaje es claro, no se puede eliminar porque hay un conflicto con la restricción de Clave foránea de la tabla Order Details. Mens. 547, Nivel 16, Estado 0, Línea 72 Instrucción DELETE en conflicto con la restricción REFERENCE “FK_Order_Details_Products”. El conflicto ha aparecido en la base de datos “Northwind”, tabla “dbo.Order Details”, column ‘ProductID’. Se terminó la instrucción.

3. Eliminar un producto con éxito. Eliminar uno de los productos insertados, los que no figuran con clave foránea. Según los registros insertados líneas arriba son los que tiene código 78, 79 y 80 Delete Products where ProductID = 79 go Listado de la tabla Historial de registros eliminados select * from ProductosHistorialEliminados go

Al tener el historial se puede conservar los datos del registro eliminado. Notas importantes 

Se sugiere evitar los campos con la propiedad Identity (Ver Identity)



Se recomienda para almacenar los datos numéricos el uso de Numeric. (Ver Crear tablas)



En ocasiones se pueden usar campos que especifiquen que los registros no se pueden eliminar debido a que son de sistema.



Se sugiere con por ningún motivo se implemente el borrado en cascada.





Cursores con variables tipo tabla By Trainer SQL in Programación

Cursores con variables tipo tabla Este artículo muestra como se puede trabajar con un cursor y llenar los datos de este en una tabla tipo variable. (Ver Tablas tipo variable). Los cursores permiten guardar en memoria el resultado de una instrucción Select y analizar los registros de manera individual. Para ver cursores pulse en Ver cursores.

Ejercicios Usando Northwind use Northwind go

1. Como crear un cursor que muestre el código del empleado y el número de ordenes de pedido emitidas Declare @EmpleadosOrdenes table ( EmpleadoCodigo nchar(5), EmpleadoNombreCompleto nvarchar(100), EmpleadoCantidadOrdenes int ) — Definir las variables declare @EmpleadoCodigo nchar(5) declare @EmpleadoNombreCompleto nvarchar(100) declare @EmpleadoCantidadOrdenes int — Crear el cursor declare cursorEmpleadosOrdenes cursor for select Right(‘0000’ + LTrim(Str(E.EmployeeID)),5) As ‘Código’, Empleado = E.LastName + Space(1) + E.FirstName, count(O.OrderID) As ‘Cantidad’ from Employees As E join Orders As O on E.EmployeeID = O.EmployeeID group by E.EmployeeID, E.LastName + Space(1) + E.FirstName — Abrir el cursor open cursorEmpleadosOrdenes — Leer los datos a las variables del primer registro fetch next from cursorEmpleadosOrdenes into @EmpleadoCodigo,@EmpleadoNombreCompleto,

@EmpleadoCantidadOrdenes while @@FETCH_STATUS =0 — recorrer el cursor begin insert @EmpleadosOrdenes values (@EmpleadoCodigo,@EmpleadoNombreCompleto, @EmpleadoCantidadOrdenes) fetch next from cursorEmpleadosOrdenes into @EmpleadoCodigo,@EmpleadoNombreCompleto, @EmpleadoCantidadOrdenes end close cursorEmpleadosOrdenes deallocate cursorEmpleadosOrdenes select * from @EmpleadosOrdenes order by EmpleadoCantidadOrdenes desc go La figura muestra el resultado de la ejecución del cursor.

2.Crear un cursor que muestre un producto y la cantidad de unidades vendidas y el monto total generado. — Crear la variable tipo tabla Declare @ProductosTotales table (ProductoCodigo nchar(5), ProductoDescripcion nvarchar(50), ProductoTotalUnidades Numeric(9,2), ProductosMontoTotal Numeric(9,2)) — Crear las variables para que el cursos guarde los datos declare @ProductoCodigo nchar(5) declare @ProductoDescripcion nvarchar(50) declare @ProductoTotalUnidades Numeric(9,2) declare @ProductosMontoTotal Numeric(9,2) — Crear el cursor

declare CursorProductosTotales cursor for select Right(‘0000’ + LTrim(Str(P.ProductID)),5) As ‘Código’, P.ProductName As ‘Descripción’, Sum(D.Quantity) As ‘Cantidad’, Sum(D.Quantity * D.UnitPrice) As ‘Monto Total’ from Products As P join [Order Details] As D on P.ProductID = D.ProductID group by P.ProductID, P.ProductName open CursorProductosTotales fetch next from CursorProductosTotales into @ProductoCodigo, @ProductoDescripcion, @ProductoTotalUnidades, @ProductosMontoTotal — Recorrer el cursor while @@fetch_status = 0 begin insert @ProductosTotales values (@ProductoCodigo, @ProductoDescripcion, @ProductoTotalUnidades, @ProductosMontoTotal) fetch next from CursorProductosTotales into @ProductoCodigo, @ProductoDescripcion, @ProductoTotalUnidades, @ProductosMontoTotal end close CursorProductosTotales deallocate CursorProductosTotales select * from @ProductosTotales go



Funciones de cursor By Trainer SQL in Funciones en SQL Server

Funciones de Cursor Las funciones de cursor devuelven información de los cursores. (Ver cursores) Las funciones de cursor son las siguientes: @CURSOR_ROWS CURSOR_STATUS @@FETCH_STATUS Función @Cursor_rows Devuelve el número de filas del cursor abierto. Valor devuelto

Descripción

-m

El cursor se llena asincrónicamente. El valor –m es el número de filas actualm

-1

El cursor es dinámico. Como los cursores dinámicos reflejan todos los cambio cambia constantemente. Nunca se puede afirmar que se han recuperado todas l

0

No se han abierto cursores, no hay filas calificadas para el último cursor abiert cancelado.

n

El cursor está completamente relleno. El valor devuelto (n) es el número total

Ejemplos Usando Northwind use Northwind go — Crear un cursor con los productos de categoría 5 — Antes de declarar y abrir el cursor select @@CURSOR_ROWS go Resultado: 0 Declare CursorProductosCategoria5 cursor for select * from Products where CategoryID = 5 go Open CursorProductosCategoria5 go Después de abrir el cursor select @@CURSOR_ROWS go Resultado: -1 Leer los datos del cursor Fetch CursorProductosCategoria5 go Cerrar y Liberar el cursor Close CursorProductosCategoria5 Deallocate CursorProductosCategoria5 go Función @@FETCH_STATUS Devuelve el estado de la última instrucción que lee los datos del cursor (Fetch). El tipo de dato devuelto es Entero. Valor devuelto

Descripción

0

La instrucción FETCH se ejecutó correctamente y muestra registro.

-1

La instrucción FETCH no se ejecutó correctamente y no muestra registro.

-2

Falta la fila capturada.

-9

El cursor no está realizando una operación de búsqueda.

Generalmente @@Fetch_Status es usada con la estructura While para leer los registros del cursor.

Ejemplo — Crear un cursor para mostrar los Empleados Declare cursorEmpleados Cursor for select E.EmployeeID As ‘Código’, Empleado = E.LastName + Space(1)+ E.FirstName from Employees As E order by Empleado Open cursorEmpleados Fetch from cursorEmpleados Print ‘========= EMPLEADOS ============= ‘ While @@FETCH_STATUS = 0 Begin Fetch from cursorEmpleados End Close cursorEmpleados Deallocate cursorEmpleados go Función Cursor_Status Permite determinar si el resultado de un procedimiento almacenado ha devuelto un cursor y un conjunto de resultados al recibir un parámetro. Valor

Descripción

-1

El cursor está cerrado.

1

El cursor tiene al menos una fila.

0

El conjunto de resultados del cursor está vacío.

-3

No existe un cursor con el nombre indicado.

Ejemplo Crear un cursor con las categorías Antes de Crear el cursor

select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de Crear’ go Resultado: -3 Declare cursorCategorias cursor for Select C.CategoryID, C.CategoryName from Categories As C go Antes de abrir el cursor select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de abrir’ go Resultado: -1 Despues de abrir el cursor Open cursorCategorias go select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de abrir’ go Resultado: 1 Despues de Cerrar el cursor Close cursorCategorias go select CURSOR_STATUS (‘global’,’cursorCategorias’) As ‘Antes de abrir’ go Resultado: -1 



Triggers By Trainer SQL in Programación

Triggers Un desencadenador o Trigger es una clase de procedimiento almacenado que se ejecuta automáticamente cuando se realiza una transacción en la bases de datos.

Tipos de Triggers Existen los siguientes tipos de Triggers 1. Los Triggers DML se ejecutan cuando se realizan operaciones de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE realizados en una tabla o vista. 2. Los Triggers DDL se ejecutan al realizar eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden a instrucciones CREATE, ALTER y DROP. 3. Los Triggers Logon, que se disparan al ejecutarse un inicio de sesión en SQL Server.

Consideraciones 

Una tabla puede tener un máximo de tres triggers: uno de actualización, uno de inserción y uno de eliminación.



Cada trigger puede aplicarse a una sola tabla o vista. Por otro lado, un mismo trigger se puede aplicar a las tres acciones: UPDATE, INSERT y DELETE.



No se puede crear un trigger en una vista ni en una tabla temporal, pero el trigger puede hacer referencia a estos objetos.



Los trigger no se permiten en las tablas del sistema.

Las tablas Inserted y Deleted

Son tablas especiales que tienen la misma estructura de las tablas que han desencadenado la ejecución del trigger. La tabla Inserted está disponible en las operaciones INSERT y UPDATE. La tabla Deleted está disponible en las operaciones UPDATE y DELETE. Note que para una operación de actualización, las dos tablas pueden ser utilizadas. Para crear un Trigger DML se utiliza: CREATE [ OR ALTER ] TRIGGER [ Esquema . ]NombreTrigger ON { tabla | vista } { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS Begin Instrucciones T-SQL End Para crear un Trigger DDL se utiliza: CREATE [ OR ALTER ] TRIGGER NombreTrigger ON { ALL SERVER | BaseDatos } { FOR | AFTER } { TipoEvento | GrupoEventos } [ ,…n ] AS Begin Instrucciones T-SQL End Ejercicios Usando Northwind use Northwind go 1. Trigger que evita que se borren en la tabla categorias mas de un registro, el Trigger detecta que se borra mas de un registro y dispara un error. (Ver Manejo de errores) Create Trigger trEliminaSoloUnaCategoria on Categories for delete As — Se cuentan cuantos registros se eliminaron IF (Select COUNT(*) from Deleted)>1 BEGIN Raiserror(‘NO PUEDE ELIMINAR MÁS DE UN REGISTRO’,16,1) Rollback Tran END go

Raiserror Raiserror(‘Mensaje’,Severidad,Estado) Genera un mensaje de error e inicia el procesamiento de errores de la sesión. RAISERROR puede hacer referencia un mensaje definido por el usuario almacenado en la vista de catálogo sys.messages o puede generar un mensaje dinámicamente. 2. Visualizar los triggers de la base de datos select * from sys.triggers go 3. Probar si el Trigger creado trEliminaSoloUnaCategoria funciona se insertarán dos categorías con nombre Pisos y Carros Insertar Categorias insert into Categories (CategoryName) values (‘Pisos’),(‘Carros’) go Eliminar una categoria delete Categories where CategoryID = 10 go Se elimina sin inconvenientes porque es una sola categoría. Al eliminar varias categorias el Trigger se dispara y no lo permite. delete Categories where CategoryID in (12,13,15) go Mens 50000, Nivel 16, Estado 1, Procedimiento trEliminaSoloUnaCategoria, Línea 62 NO PUEDE ELIMINAR MÁS DE UN REGISTRO Mens. 3609, Nivel 16, Estado 1, Línea 56 La transacción terminó en el desencadenador. Se anuló el lote. 4. Borrar una sola categoria delete Categories where CategoryID = 17 go La categoría se borra con éxito

Trigger de Inserción 5. Crear un Trigger que permita comprobar que se inserta una categoría con nombre diferente. La tabla Inserted será utilizada para comprobar si ya hay una categoría con el mismo nombre que la insertada.

Create trigger trCategoriaInsertaSinRepetidos ON Categories For Insert AS IF (Select COUNT (*) From Inserted, Categories WHERE Inserted.CategoryName = Categories.CategoryName) >1 BEGIN Rollback Transaction PRINT ‘El Nombre de la Categoria ya existe…’ END ELSE PRINT ‘Categoría ingresada a la Base de datos’ go 6. Insertar categoría con nombre repetido: Pisos insert into Categories (CategoryName) values (‘Pisos’) go El Nombre de la Categoria ya existe… Mens. 3609, Nivel 16, Estado 1, Línea 87 La transacción terminó en el desencadenador. Se anuló el lote.

Trigger Instead Of Realizar acciones después de las instrucciones de un procedimiento o las escritas directamente por el usuario. (Ver Triggers Instead Of) 6. Copiar los datos a una vista de Clientes al insertar uno en la tabla Customers Primero se crea la vista con los clientes. Create View ClientesVista as Select CustomerID,CompanyName,Address,City,Phone from Customers go Al agregar un Cliente en Customers se desea que se inserte en ClientesVista Crear un Trigger Instead of para la tabla Customers. Create trigger trClienteInsertaVista ON Customers Instead of Insert AS BEGIN Insert Into ClientesVista SELECT CustomerID, CompanyName, Address, City, Phone FROM Inserted Print ‘Insertado correctamente en la vista’ END go

Insertar un cliente en Customers insert into Customers (CustomerID, CompanyName,ContactName, ContactTitle,Address, City, Phone) Values (‘TQ884’,’Trainer SQL Pro ‘,’SQL Professional’, ‘Gerente General’,’Av. Larco 94994′,’España’,’209834534′) go

Visualizar la vista para comprobar que el cliente ha sido insertado. select * from ClientesVista where CustomerID like ‘T%’ go





Triggers Instead Of en SQL Server By Trainer SQL in Consultas, Programación, Registros - Vistas

Triggers de Tipo Instead of Los triggers instead of son un tipo de Triggers que reemplazan las instrucciones que hace que se dispare, use estos tipos de Triggers cuando es necesario comprobar algunas condiciones al momento de realizar transacciones con los registros de tablas o vistas. Por ejemplo: si se crea un Trigger de tipo instead of para la tabla Clientes al insertar un registro, al ejecutar un Insert es cuando este tipo de Trigger se va a ejecutar en lugar de la instrucción Insert en la tabla o vista.

Notas importantes 1. Estos tipos de triggers cancelan la instrucción que hace que se dispare, reemplazando esta por las instrucciones del Trigger. 2. Generalmente se utilizan estos triggers en vistas 3. Las acciones que realice el trigger no deben cancelar la transacción que la dispara sino ejecutar las instrucciones cambiadas que son el cuerpo del trigger.

Ejemplo Usando la base de datos Northwind use Northwind go Para utilizar un Trigger de tipo instead of crearemos una vista llamada SociosEstrategicos con los Clientes (Customers) y los Proveedores (Suppliers)

Primero: los Clientes select C.CustomerID ‘Cód.Cliente’, C.CompanyName As ‘Cliente’, C.Country As ‘País’ from Customers As C go

Segundo: los proveedores select S.SupplierID As ‘Cód. Proveedor’, S.CompanyName As ‘Proveedor’, S.Country As ‘País’ from Suppliers As S go

Al juntar los dos listados en la vista SociosEstrategicos, la instrucción Select para la vista es como sigue:

select C.CustomerID ‘Cód.Cliente’, C.CompanyName As ‘Cliente’, C.Country As ‘País’ from Customers As C union all select Cast(S.SupplierID As nchar(5)) , S.CompanyName , S.Country from Suppliers As S go

Incluiremos una columna para diferenciar el tipo de socio, Cliente o Proveedor. Además de cambiar los nombres de campos (Ver opciones de consultas Union) select C.CustomerID ‘CodigoSocio’, C.CompanyName As ‘Socio’, C.Country As ‘País’, ‘Cliente’ As Tipo from Customers As C union all select Cast(S.SupplierID As nchar(5)) , S.CompanyName , S.Country , ‘Proveedor’ As Tipo from Suppliers As S go En la imagen se muestra parte de los Clientes y Proveedores.

Creamos la vista (Ver Vistas) Create view VistaSociosEstrategicos As select C.CustomerID ‘CodigoSocio’, C.CompanyName As ‘Socio’, C.Country As ‘País’, ‘Cliente’ As Tipo from Customers As C union all select Cast(S.SupplierID As nchar(5)) , S.CompanyName , S.Country , ‘Proveedor’ As Tipo from Suppliers As S go Si se desea visualizar los socios estratégicos del negocio solamente listamos la vista select * from VistaSociosEstrategicos go

El Trigger se creará para la vista, será de tipo Instead Of (Ver Triggers) create trigger trVistaSocioEstrategicoInsertar on VistaSociosEstrategicos instead of insert as insert into Customers (CustomerID, CompanyName, Country) select CodidoSocio,Socio,País from inserted where Tipo=’Cliente’ insert into Suppliers (CompanyName, Country) select Socio,País

from inserted where Tipo=’Proveedor’ go Al ejecutar una inserción en la vista

Insertar un Cliente insert into VistaSociosEstrategicos values (‘YT348′,’Sociedad SQL’,’France’,’Cliente’) go Listamos los clientes (tabla Customers) select * from Customers where CompanyName Like ‘Soci%’ go Insertar un Proveedor insert into VistaSociosEstrategicos values (‘BT599′,’Limbo Almacenes’,’Spain’,’Proveedor’) go Listamos los Proveedores (tabla Suppliers) select * from Suppliers where CompanyName Like ‘Limbo%’ go La vista tiene insertados los dos registros select * from VistaSociosEstrategicos where Socio like ‘Limbo%’ or Socio like ‘Soci%’ go

Note que al insertar registros en la vista debemos especificar todos los campos, en el Trigger para los proveedores no se especifica el código porque es un campo entero y es Identidad. (Ver Identity) 



Triggers – Creando un historial By Trainer SQL in Programación

Creando un Historial de cambios con Trigger Los triggers DML son procedimientos guardados en la base de datos que se disparan cuando se insertan registros, cuando se actualizan los datos de un registro o cuando se eliminan registros. Este ejercicio muestra como crear un Historial de cambios usando un Trigger, el trigger se disparará cuando se inserte o actualice un registro.Para mayor información Ver Triggers

Usando la base de datos Norhtwind use Northwind go La tabla Shippers (Compañías de envío) tiene los campos: ShipperID, Companyname y Phono. Primero se creará una tabla HistorialShippers con los campos Código, Nombre, Teléfono y Fecha. Create table HistorialShippers ( HistorialShippersCodigo nchar(10), HistorialShippersNombre nvarchar(40), HistorialShippersFono nvarchar(24), HistorialShippersFecha Date ) go Crear el trigger para la tabla Shippers que se dispare cuando se inserta o modifica un registro

Create trigger triggerShippersHistorial on Shippers for insert, update As Begin Insert into HistorialShippers select inserted.ShipperID, inserted.CompanyName, inserted.Phone, GetDate() from inserted End go

Antes de insertar o actualizar registros, visualizar los registros en las tablas. En Shippers select * from Shippers go

En HistorialShippers select * from HistorialShippers go Puede notarse que no hay registros. Insertar un registro en la tabla Shippers, esto hará que se dispare el Trigger y se inserte un registro en la tabla creada HistorialShippers insert into Shippers (CompanyName, Phone) values (‘Trainer SQL’,’87852541′) go Prueba con la actualización de los datos de un registro. El registro insertado líneas arriba se generó su código 4, se cambiará su nombre y teléfono.

update Shippers set CompanyName = ‘Capacitador SQL’, Phone = ‘963258741’ where ShipperID = 4 go Probando con insertar otro registro insert into Shippers (CompanyName, Phone) values (‘Tracks Moves’,’952369985′) go Puede observar que la tabla HistorialShippers tiene los datos de los registros que se insertaron o que se actualizaron. select * from HistorialShippers go



Como usar Triggers DDL en SQL Server By Trainer SQL in Programación, Registros - Vistas

Como usar Triggers DDL en SQL Server Los Triggers DDL son aquellos que se disparan cuando se realizan eventos DDL, que son las siglas de Data Definition Language, estos comando son Create, Alter, Drop, GRANT, DENY, REVOKE o UPDATE STATISTICS

Puede interesar Triggers, definición y creación Triggers DML encriptados Triggers Logon Triggers, activar y desactivar.

Eventos de un Trigger DDL Los eventos que hacen que se dispare un Trigger DDL clasificados por el alcance de los mismos son de dos tipos: 

Eventos que tienen alcance de base de datos



Eventos que tienen alcance de servidor

Eventos que tienen alcance de base de datos CREATE_APPLICATION_ROLE CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY CREATE_ASYMMETRIC_KEY ALTER_ASYMMETRIC_KEY DROP_ASYMMETRIC_KEY

ALTER_AUTHORIZATION ALTER_AUTHORIZATION_DATABASE CREATE_BROKER_PRIORITY CREATE_BROKER_PRIORITY CREATE_BROKER_PRIORITY CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE CREATE_CONTRACT DROP_CONTRACT CREATE_CREDENTIAL ALTER_CREDENTIAL DROP_CREDENTIAL GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE CREATE_DATABASE_AUDIT_SPEFICIATION ALTER_DATABASE_AUDIT_SPEFICIATION DENY_DATABASE_AUDIT_SPEFICIATION CREATE_DATABASE_ENCRYPTION_KEY ALTER_DATABASE_ENCRYPTION_KEY DROP_DATABASE_ENCRYPTION_KEY CREATE_DEFAULT DROP_DEFAULT BIND_DEFAULT (También sp_bindefault.) UNBIND_DEFAULT (También sp_unbindefault.) CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION CREATE_EXTENDED_PROPERTY (También sp_addextendedproperty.) ALTER_EXTENDED_PROPERTY (También sp_updateextendedproperty.) DROP_EXTENDED_PROPERTY (También sp_dropextendedproperty.) CREATE_FULLTEXT_CATALOG (También the CREATE FULLTEXT CATALOG y sp_fulltextcatalog) ALTER_FULLTEXT_CATALOG (También ALTER FULLTEXT CATALOG , cuando sp_fulltextcatalog, start_full, Stop, o Rebuild es especificado, y sp_fulltext_database ) DROP_FULLTEXT_CATALOG (También DROP FULLTEXT CATALOG y sp_fulltextcatalog) CREATE_FULLTEXT_INDEX (También CREATE FULLTEXT INDEX y sp_fulltexttable) ALTER_FULLTEXT_INDEX (También ALTER FULLTEXT INDEX , sp_fulltextcatalog, sp_fulltext_column, y sp_fulltext_table) DROP_FULLTEXT_INDEX (También DROP FULLTEXT INDEX y sp_fulltexttable) CREATE_FULLTEXT_STOPLIST ALTER_FULLTEXT_STOPLIST DROP_FULLTEXT_STOPLIST CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION CREATE_INDEX ALTER_INDEX (También the ALTER INDEX y sp_indexoption.) DROP_INDEX CREATE_MASTER_KEY ALTER_MASTER_KEY DROP_MASTER_KEY CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME CREATE_PLAN_GUIDE (También sp_create_plan_guide.) ALTER_PLAN_GUIDE (También sp_control_plan_guide) DROP_PLAN_GUIDE (También sp_control_plan_guide ) CREATE_PROCEDURE ALTER_PROCEDURE (También ALTER PROCEDURE y sp_procoption.) DROP_PROCEDURE

CREATE_QUEUE ALTER_QUEUE DROP_QUEUE CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING CREATE_SPATIAL_INDEX RENAME (También sp_rename) CREATE_ROLE (También CREATE ROLE, sp_addrole, y sp_addgroup.) ALTER_ROLE DROP_ROLE (También DROP ROLE, sp_droprole, y sp_dropgroup.) ADD_ROLE_MEMBER DROP_ROLE_MEMBER CREATE_ROUTE ALTER_ROUTE DROP_ROUTE CREATE_RULE DROP_RULE BIND_RULE (También sp_bindrule.) UNBIND_RULE (También sp_unbindrule.) CREATE_SCHEMA (También CREATE SCHEMA, sp_addrole, sp_adduser, sp_addgroup, y sp_grantdbaccess.) ALTER_SCHEMA (También ALTER SCHEMA y sp_changeobjectowner.) DROP_SCHEMA CREATE_SEARCH_PROPERTY_LIST ALTER_SEARCH_PROPERTY_LIST DROP_SEARCH_PROPERTY_LIST CREATE_SEQUENCE_EVENTS CREATE_SEQUENCE_EVENTS CREATE_SEQUENCE_EVENTS CREATE_SERVER_ROLE ALTER_SERVER_ROLE DROP_SERVER_ROLE CREATE_SERVICE ALTER_SERVICE DROP_SERVICE ALTER_SERVICE_MASTER_KEY BACKUP_SERVICE_MASTER_KEY RESTORE_SERVICE_MASTER_KEY ADD_SIGNATURE DROP_SIGNATURE ADD_SIGNATURE_SCHEMA_OBJECT DROP_SIGNATURE_SCHEMA_OBJECT CREATE_SPATIAL_INDEX ALTER_INDEX DROP_INDEX CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS CREATE_SYMMETRIC_KEY ALTER_SYMMETRIC_KEY DROP_SYMMETRIC_KEY CREATE_SYNONYM DROP_SYNONYM CREATE_TABLE ALTER_TABLE (También ALTER TABLE y sp_tableoption.) DROP_TABLE CREATE_TRIGGER ALTER_TRIGGER (También ALTER TRIGGER y sp_settriggerorder.) DROP_TRIGGER CREATE_TYPE (También CREATE TYPE y sp_addtype.) DROP_TYPE (También DROP TYPE statement y sp_droptype.) CREATE_USER (También CREATE USER, sp_adduser, y sp_grantdbaccess.) ALTER_USER (También ALTER USER y sp_change_users_login.) DROP_USER (También DROP USER statement, sp_dropuser, y sp_revokedbaccess.) CREATE_VIEW ALTER_VIEW DROP_VIEW CREATE_XML_INDEX ALTER_INDEX DROP_INDEX CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

Eventos DDL que tienen alcance en el servidor ALTER_AUTHORIZATION_SERVER ALTER_SERVER_CONFIGURATION ALTER_INSTANCE CREATE_AVAILABILITY_GROUP ALTER_AVAILABILITY_GROUP DROP_AVAILABILITY_GROUP CREATE_CREDENTIAL ALTER_CREDENTIAL DROP_CREDENTIAL CREATE_CRYPTOGRAPHIC_PROVIDER ALTER_CRYPTOGRAPHIC_PROVIDER DROP_CRYPTOGRAPHIC_PROVIDER CREATE_DATABASE ALTER_DATABASE DROP_DATABASE CREATE_ENDPOINT ALTER_ENDPOINT DROP_ENDPOINT CREATE_EVENT_SESSION ALTER_EVENT_SESSION DROP_EVENT_SESSION CREATE_EXTENDED_PROCEDURE (También sp_addextendedproc.) DROP_EXTENDED_PROCEDURE (También sp_dropextendedproc.) CREATE_LINKED_SERVER (También sp_addlinkedserver.) ALTER_LINKED_SERVER (También sp_serveroption.) DROP_LINKED_SERVER (También sp_dropserver) CREATE_LINKED_SERVER_LOGIN (También sp_addlinkedsrvlogin.) DROP_LINKED_SERVER_LOGIN (También sp_droplinkedsrvlogin.) CREATE_LOGIN (También CREATE LOGIN, sp_addlogin, sp_grantlogin, xp_grantlogin, y sp_denylogin ALTER_LOGIN (También ALTER LOGIN, sp_defaultdb, sp_defaultlanguage, sp_password, y sp_change_users_login) DROP_LOGIN (También DROP LOGIN, sp_droplogin, sp_revokelogin, y xp_revokelogin.) CREATE_MESSAGE (También sp_addmessage.) ALTER_MESSAGE (También sp_altermessage.) DROP_MESSAGE (También sp_dropmessage.) CREATE_REMOTE_SERVER (También sp_addserver.) ALTER_REMOTE_SERVER (También sp_setnetname.) DROP_REMOTE_SERVER (También sp_dropserver) CREATE_RESOURCE_POOL ALTER_RESOURCE_POOL DROP_RESOURCE_POOL GRANT_SERVER DENY_SERVER REVOKE_SERVER ADD_SERVER_ROLE_MEMBER DROP_SERVER_ROLE_MEMBER CREATE_SERVER_AUDIT ALTER_SERVER_AUDIT DROP_SERVER_AUDIT CREATE_SERVER_AUDIT_SPECIFICATION ALTER_SERVER_AUDIT_SPECIFICATION DROP_SERVER_AUDIT_SPECIFICATION CREATE_WORKLOAD_GROUP ALTER_WORKLOAD_GROUP DROP_WORKLOAD_GROUP Como crear triggers DDL en SQL Server Ejemplos Usando la base de datos Northwind

use Northwind go 1. Crear un Trigger para evitar que se creen, modifiquen o eliminen tablas Create Trigger trNoCrearModificarBorrarTablas ON DataBase FOR Create_Table, DROP_TABLE, ALTER_TABLE AS BEGIN RAISERROR (‘Transacción anulada, no se permite crear, editar o eliminar tablas’ , 16, 1) Rollback transaction END go Al intentar crear una tabla Create table Prueba ( Codigo nchar(4), Descripcion nvarchar(100) ) go Resultado Mens 50000, Nivel 16, Estado 1, Procedimiento trNoCrearModificarBorrarTablas, Línea 131 Transacción anulada, no se permite crear, editar o eliminar tablas Mens. 3609, Nivel 16, Estado 2, Línea 127 La transacción terminó en el desencadenador. Se anuló el lote. Para poder crear tablas, se debe eliminar el Trigger o solamente desactivar (Ver Triggers Activar – Desactivar) disable trigger trNoCrearModificarBorrarTablas on Database go Probar ahora si se puede crear una tabla Create table Prueba ( Codigo nchar(4), Descripcion nvarchar(100) ) go Resultado: Comandos completados correctamente. Para activar nuevamente el Trigger se debe escribir la siguiente instrucción: Enable trigger trNoCrearModificarBorrarTablas on Database go

Como usar la función EventData() Esta función devuelve información sobre eventos del servidor o de la base de datos. Un Trigger Logon o DDL también admite el uso interno de EVENTDATA.

Sintaxis: EventData() 2. Crear un Trigger que se dispare cuando se crea una vista y capture el evento creado. Create trigger trCapturarCrearVista on Database For Create_View As Begin Select EVENTDATA() End go Crear una vista para que el trigger creado se dispare create view vistaCategorias As select C.CategoryID As ‘Código’, C.CategoryName As ‘Nombre’ from Categories As C go Puede notar que el resultado se presenta en un esquema XML que se muestra en la figura siguiente.

3. Crear un trigger que se dispare al crear, modificar o eliminar un procedimiento almacenado, almacenar la instrucción ejecutada en una tabla SPHistorial

Primero crear la tabla, si se creo el trigger del ejercicio anterior debemos desactivarlo. disable trigger trNoCrearModificarBorrarTablas on Database go Ahora si se podrá crear la tabla Create table HistorialCambiosStoreProcedures (TipoEvento nvarchar(200), Fecha DateTime, Servidor nvarchar(100), InicioSesion nvarchar(100), Equipo nvarchar(100), ComandoTSQL nvarchar(400) ) go Crear el Trigger para crear, editar o eliminar un Store Procedure Create trigger trHistoriaSP on Database for Create_Procedure, Alter_Procedure, Drop_Procedure As Begin insert into HistorialCambiosStoreProcedures (TipoEvento, Fecha, Servidor, InicioSesion, Equipo, ComandoTSQL) Select EVENTDATA().value (‘(/EVENT_INSTANCE/EventType)[1]’,’nvarchar(200)’), EVENTDATA().value (‘(/EVENT_INSTANCE/PostTime)[1]’,’Datetime’), EVENTDATA().value (‘(/EVENT_INSTANCE/ServerName)[1]’,’nvarchar(100)’), EVENTDATA().value (‘(/EVENT_INSTANCE/LoginName)[1]’,’nvarchar(100)’), HOST_NAME(), EVENTDATA().value (‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(400)’) End go Para que el Trigger se dispare, crear un procedimiento almacenado para listar los clientes Create procedure spClientesListado As Select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’ from Customers As C order by Cliente go Modificar el procedimiento para incluir un campo adicional Alter procedure spClientesListado As Select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’, C.Address As ‘Dirección’ from Customers As C

order by Cliente go Borrar el SP Drop procedure spClientesListado go Para visualizar el contenido del historial select * from HistorialCambiosStoreProcedures go La imagen siguiente muestra el resultado





Procedimientos Almacenados By Trainer SQL in Programación

Procedimientos Almacenados Un procedimiento almacenado son instrucciones T-SQL almacenadas con un nombre en la base de datos.



Los procedimientos almacenados se pueden utilizar para Devolver un conjunto de resultados, se puede incluir parámetros de entrada para especificar el filtro del conjunto resultado.



Ejecutar instrucciones de programación.



Devolver valores numéricos que permiten realizar acciones cuando un grupo de instrucciones se realizó con éxito o no.

Ventajas del uso de procedimientos almacenados Reutilización del código El encapsulamiento en un procedimiento es óptimo para reutilizar su código. Se elimina la necesidad de escribir el mismo código, se reducen inconsistencias en el código y permite que cualquier usuario ejecute el código aún sin tener acceso a los objetos que hace referencia. Mayor seguridad Se pueden ejecutar SP con instrucciones que hacen referencia a objetos que los usuarios no tienen permisos. El procedimiento realiza la ejecución del código y todas las instrucciones y controla el acceso a los objetos a los que hace referencia. Esto hace mas sencillo la asignación de permisos. Se puede implementar la suplantación de usuarios usando Exexute As. Existe un nivel fuerte de encapsulamiento.

Tráfico de red reducido Un SP se ejecuta en un único lote de código. Esto reduce el tráfico de red cliente servidor porque únicamente se envía a través de la red la llamada que ejecuta el SP. La encapsulación del código del SP permite que viaje a través de la red como un solo bloque. Mantenimiento más sencillo Se puede trabajar en los aplicativos en base a capas, cualquier cambio en la Base de datos, hace sencillo los cambios en los procedimientos que hacen uso de los objetos cambiados en la BD. Rendimiento mejorado Los procedimientos almacenados se compila la primera vez que se ejecutan y crean un plan de ejecución que vuelve a usarse en posteriores ejecuciones.

Tipos de procedimientos Definidos por el usuario Se crea por el usuario en las bases de datos definidas por el usuario o en las de sistema (Master, Tempdb, Model y MSDB) Procedimientos almacenados Temporales Los procedimientos temporales son procedimientos definidos por el usuario, estos se almacenan en tempdb. Existen dos tipos de procedimientos temporales: locales (primer caracter es #) y globales (primer caracter ##). Se diferencian entre sí por los nombres, la visibilidad y la disponibilidad. Los procedimientos temporales locales tienen como primer carácter de sus nombres un solo signo de número (#); solo son visibles en la conexión actual del usuario y se eliminan cuando se cierra la conexión. Los procedimientos temporales globales presentan dos signos de número (##) antes del nombre; lo pueden usar todos los usuarios conectados, se eliminan cuando se desconectan todos los usuarios. Procedimientos Almacenados del Sistema Los procedimientos del sistema son propios de SQL Server. Los caracteres iniciales de estos procedimientos son sp_ la cual no se recomienda para los procedimientos almacenados definidos por el usuario. Extendidos definidos por el usuario Los procedimientos extendidos tienen instrucciones externas en un lenguaje de programación como puede ser C. Estos procedimientos almacenados son DLL que una instancia de SQL Server puede cargar y ejecutar dinámicamente.

Sintaxis Para crear un procedimiento almacenado se utiliza Create procedure NombreProcedimiento (

@PrimerParametro TipoDato, @SegundoParametro TipoDato,… ) As Instrucciones del SP go Para modificar un SP Alter procedure NombreProcedimiento ( @PrimerParametro TipoDato, @SegundoParametro TipoDato,… cambios ) As Instrucciones del SP con cambios go Eliminar un SP Drop procedure NombreProcedimiento go Para listar los SP select * from sys.procedures go

Ejercicios Use Northwind go — Procedimiento para listar los productos Create procedure spProductosListadoPrecios As Select P.ProductID, P.ProductName, P.UnitPrice , P.UnitsInStock from Products As P go Ejecutar el Store Procedure creado Execute spProductosListadoPrecios go — Procedimiento para insertar un registro en la tabla Shippers — La instrucción para insertar un Shipper es: insert into Shippers (CompanyName, Phone) values (‘Tolva Couriers’,’954542452′) go El SP para insertar Shippers se crea de la siguiente forma. create procedure spShippersInsertaNuevo (

@NombreEmpresa nvarchar(40), @Fono nvarchar(24) ) As insert into Shippers (CompanyName, Phone) values (@NombreEmpresa,@Fono) go Ejecutar el SP, se puede ejecutar de las siguiente formas: Execute spShippersInsertaNuevo ‘Chasqui’,’87545852′ go Execute spShippersInsertaNuevo @Fono = ‘345435645’, @NombreEmpresa =’Ford’ go Execute spShippersInsertaNuevo @NombreEmpresa =’Turbo XD’, @Fono = ‘8569856’ go — Procedimiento para el listado de productos de una determinada categoría Create procedure spProductosListadoPorCategoria ( @CategoriaCodigo int ) As select P.ProductID, P.ProductName, P.UnitPrice , P.UnitsInStock, P.UnitsOnOrder from Products As P where CategoryID = @CategoriaCodigo go Ejecutar el SP Productos de categoria 2 Execute spProductosListadoPorCategoria 2 go

Modificar el procedimiento de Listado de Productos por categoría que lo muestre ordenados por precio descendente Alter procedure spProductosListadoPorCategoria ( @CategoriaCodigo int ) As select P.ProductID, P.ProductName, P.UnitPrice , P.UnitsInStock, P.UnitsOnOrder from Products As P where CategoryID = @CategoriaCodigo order by P.UnitPrice desc go

— Procedimiento para crear una tabla con los productos de una determinada categoría Create procedure spCreaTablaProductosDeCategoria ( @CodigoCategoria int ) As Declare @NombreTabla nvarchar(40), @DropTablaTSQL nvarchar(100),@CrearTablaTSQL nvarchar(100) set @NombreTabla = ‘ProductosDeCategoria’+ LTRIM(STR(@CodigoCategoria)) Set @DropTablaTSQL = ‘Drop Table ‘+ @NombreTabla Set @CrearTablaTSQL = ‘select * into ‘+ @NombreTabla + ‘ from Products where CategoryID = ‘+LTRIM(STR(@CodigoCategoria)) if exists (select * from sys.tables where name = @NombreTabla) Begin Execute(@DropTablaTSQL) End Execute(@CrearTablaTSQL) go Ejecutar para la categoría 3 Execute spCreaTablaProductosDeCategoria 3 go Ver los registros select * from Productosdecategoria3 go



Procedimientos Almacenados – Ejercicio By Trainer SQL in Programación

Procedimientos Almacenados Ejercicio para el uso de los procedimientos almacenados con los datos de una tabla. En este ejercicio se crea una tabla para carreras en la Universidad SQL, se crean los procedimientos almacenados para insertar un registro, modificar los datos del registro, listar los registros ordenados por descripción y borrar un registro. El borrado del registro es lógico. (Ver Eliminación de registros)

Desarrollo del ejemplo Create database UniSQL go use UniSQL go Create table Carreras ( CarrerasCodigo nchar(5), CarrerasDescripcion nvarchar(100), CarrerasAcreditada nchar(1), CarrerasVacantes Numeric(9,2), CarrerasEstado nchar(1), constraint CarrerasPK Primary key (CarrerasCodigo) ) go

— Insertar registros insert into Carreras values (‘95642′,’Ing. de SISTEMAS’,’S’,250,’A’), (‘28596′,’ADMINISTRACION’,’S’,290,’A’), (‘05252′,’Ing. INDUSTRIAL’,’S’,450,’A’), (‘78596′,’MEDICINA’,’N’,100,’A’) go

— Procedimientos para Insertar, Editar y Eliminar — Insertar Create procedure spCarrerasInsertarNuevo ( @CarrerasCodigo nchar(5), @CarrerasDescripcion nvarchar(100), @CarrerasAcreditada nchar(1), @CarrerasVacantes Numeric(9,2), @CarrerasEstado nchar(1) ) As insert into Carreras (CarrerasCodigo, CarrerasDescripcion, CarrerasAcreditada, CarrerasVacantes, CarrerasEstado) values (@CarrerasCodigo, @CarrerasDescripcion, @CarrerasAcreditada, @CarrerasVacantes, @CarrerasEstado) go Prueba del SP para insertar Exec spCarrerasInsertarNuevo ‘65285’,’PSICOLOGIA’,’S’,130,’A’ go También se puede ejecutar de la siguiente manera: Exec spCarrerasInsertarNuevo @CarrerasVacantes =80 ,@CarrerasEstado =’A’,@CarrerasCodigo =’96325′, @CarrerasDescripcion=’CONTABILIDAD’,@CarrerasAcreditada=’S’ go Listado de Carreras SELECT * FROM Carreras go Crear un Índice por Descripción de la carrera (Ver Índices) create index CarrerasDescripcionIDXa on Carreras(CarrerasDescripcion) with (fillfactor = 80) go

Procedimientos para Listado, note que no aparecen los que tienen en el estado la letra E que indica que han sido eliminadas. Create procedure spCarrerasListado As select CarrerasCodigo, CarrerasDescripcion, CarrerasAcreditada, CarrerasVacantes, CarrerasEstado from Carreras where CarrerasEstado ‘E’ order by CarrerasDescripcion go Procedimiento almacenado para editar una carrera Create procedure spCarrerasEditar ( @CarrerasCodigo nchar(5), @CarrerasDescripcion nvarchar(100), @CarrerasAcreditada nchar(1), @CarrerasVacantes Numeric(9,2) ) As update Carreras set CarrerasDescripcion = @CarrerasDescripcion, CarrerasAcreditada =@CarrerasAcreditada , CarrerasVacantes =@CarrerasVacantes where CarrerasCodigo = @CarrerasCodigo go

Ejecutar el procedimiento para actualiza la carrera Medicina con los siguiente datos: Descripción: MEDICINA HUMANA, está acreditada y el número de vacantes es 150 Execute spCarrerasEditar ‘78596’,’MEDICINA HUMANA’,’S’,150,’A’ go Procedimiento almacenado para eliminar una carrera, la eliminación es lógica, cambia el estado. Create procedure spCarrerasEliminar ( @CarrerasCodigo nchar(5) ) As update Carreras set CarrerasEstado = ‘E’ where CarrerasCodigo = @CarrerasCodigo go

Ejecutar el procedimiento para borrar el registro 89456. spCarrerasEliminar ‘89456’ go





Merge By Trainer SQL in Programación

Instrucción Merge Realiza instrucciones de inserción de registros, actualización o eliminación de registros en una tabla de destino en la misma base de datos o en otra base de datos según los resultados de combinar los registros con una tabla de origen.

Sintaxis La forma de usar Merge es la siguiente: MERGE [ TOP ( n ) [ PERCENT ] ] [ INTO ] [ [ AS ] AliasTablaDestino ] USING [ [ As ] AliasTablaOrigen] ON [ WHEN MATCHED [ AND ] THEN ] [ …n ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND ] THEN ] [ WHEN NOT MATCHED BY SOURCE [ AND ] THEN ] [ …n ]

Ejemplo

En este ejemplo, se tienen dos bases de datos cada una con una tabla de Productos. La base de datos Planes, con la tabla ProductosPlanes y la base de datos Desarrollo con la tabla ProductosDesarrollo. Base de datos Planes create database Planes go use Planes go Create table ProductosPlanes ( ProductosCodigo nchar(4), ProductosDescripcion nvarchar(100), ProductosPrecioUnitario Numeric(10,2), ProductosStockActual Numeric(10,2), constraint ProductosPk Primary key (ProductosCodigo) ) go Insertar registros en la tabla ProductosPlanes insert into ProductosPlanes values (‘8856′,’Lámpara Personal’,25.4,100) insert into ProductosPlanes values (‘8636′,’Auriculares Deluxe’,98.4,20) insert into ProductosPlanes values (‘4685′,’Escritorio Gerencial’,525,6) insert into ProductosPlanes values (‘5780′,’Marco Foto’,20,80) insert into ProductosPlanes values (‘0665′,’Impresora HP’,65,15) go BASE DE DATOS DESARROLLO create database Desarrollo go use Desarrollo go Create table ProductosDesarrollo ( ProductosCodigo nchar(4), ProductosDescripcion nvarchar(100), ProductosPrecioUnitario Numeric(10,2), ProductosStockActual Numeric(10,2), constraint ProductosPk Primary key (ProductosCodigo) ) go Insertar los registros en la tabla ProductosDesarrollo Note los cambios, se han insertado registros y las coincidencias o diferencias se notan en la siguientes figuras. — Primer registro “Lámpara Personal” con un valor del Stock de 80 — Segundo registro “Auriculares Deluxe” con precio de 115

— Tercer y cuarto registros nuevos — Quinto registro “Impresora HP” tiene la descripción cambiada y nuevo Stock de 15 a 25 insert into ProductosDesarrollo values (‘8856′,’Lámpara Personal’,25.4,80) insert into ProductosDesarrollo values (‘8636′,’Auriculares Deluxe’,115,20) insert into ProductosDesarrollo values (‘9879′,’Switch Ethernet 993’,85,3) insert into ProductosDesarrollo values (‘4567′,’Memoria USB 16 GB’,50,10) insert into ProductosDesarrollo values (‘0665′,’Impresora HP Multifuncional’,65,25) go Antes del Merge select * from Planes.dbo.ProductosPlanes select * from Desarrollo.dbo.ProductosDesarrollo go

Haciendo el Merge La tabla Origen es Productos de la base de datos Desarrollo y la tabla Destino en Productos en la base de datos Planes Merge into Planes.dbo.ProductosPlanes as TablaDestino using Desarrollo.dbo.ProductosDesarrollo as TablaOrigen on (TablaDestino.ProductosCodigo = TablaOrigen.ProductosCodigo) when not matched then insert values (TablaOrigen.ProductosCodigo, TablaOrigen.ProductosDescripcion, TablaOrigen.ProductosPrecioUnitario, TablaOrigen.ProductosStockActual) when matched then update set ProductosDescripcion = TablaOrigen.ProductosDescripcion, ProductosPrecioUnitario = TablaOrigen.ProductosPrecioUnitario,

ProductosStockActual = TablaOrigen.ProductosStockActual; go Visualizar los resultados En la tabla destino ProductosPlanes se han insertados dos registros. select * from Planes.dbo.ProductosPlanes En la tabla origen de la base de datos Desarrollo los registros son los mismos. select * from Desarrollo.dbo.ProductosDesarrollo go La imagen muestra los resultados.



Procedimientos Almacenados con parámetros de salida 

By Trainer SQL in Consultas, Programación, Registros - Vistas

Procedimientos Almacenados con parámetros de salida Los procedimientos almacenados son bloques de código reutilizable guardados en la base de datos que tienen un propósito. (Ver Procedimientos Almacenados) Existen procedimientos almacenados que no tienen parámetros, es decir, no necesitan de ningún valor para que se ejecuten, las tareas que realizan estos generalmente son sencillas.

Parámetros 

Los parámetros se usan para intercambiar datos entre las aplicaciones y los procedimientos almacenados o la herramienta que ejecutó el procedimiento almacenado.



Los parámetros de entrada permiten pasar un valor de datos al procedimiento almacenado.



Los parámetros de salida permiten al procedimiento almacenado devolver un valor



Los procedimientos almacenados devuelven un código de retorno de tipo entero.



El valor de retorno por defecto es CERO si no se establece explícitamente un valor diferente

Ejemplos Los siguientes ejemplos muestran como usar los parámetros de salida en procedimientos almacenados. Usando Northwind use Northwind go Ejercicio 1 Procedimiento almacenado que reporta el número de productos de una determinada categoría Create procedure spCategoriasCantidadProductos (@CategoriaCodigo int, @CantidadProductos Numeric(9,2) output) As Select * from Products where CategoryID = @CategoriaCodigo Set @CantidadProductos = @@ROWCOUNT go Para ejecutar el procedimiento debemos crear antes una variable que permita capturar el valor devuelto por el parámetro de salida del procedimiento. Tomaremos como ejemplo la categoría 3. Declare @Cantidad int Execute spCategoriasCantidadProductos 3, @Cantidad output select @Cantidad

Ejercicio 2 Procedimiento almacenado que reporta la cantidad de productos que es

necesario comprar con urgencia. (Productos cuyas Unidades en Stock es menor a las unidades en Orden) Create procedure spCantidadProductosComprarUrgente (@CantidadProductos Numeric(9,2) output) As Select * from Products where UnitsOnOrder > UnitsInStock Set @CantidadProductos = @@ROWCOUNT go Para ejecutar el procedimiento debemos crear antes una variable que permita capturar el valor devuelto por el parámetro de salida del procedimiento. Declare @CantidadPorComprar int Execute spCantidadProductosComprarUrgente @CantidadPorComprar output select @CantidadPorComprar



Pivot By Trainer SQL in Funciones en SQL Server, Programación

Como usar Pivot en SQL Server Las operaciones con Pivot nos permitirá convertir los resultados de una consulta que se presentan en filas y mostrarlos en columnas. Pivot utiliza las funciones de agregado para presentar los datos en columnas. Para información de las funciones de agregado Ver Funciones de agregado.

Ejemplos Ejercicio 01 En el siguiente ejemplo se va a crear una base de datos, en ella una tabla Ventas, insertar registros con los datos de tres clientes de los cuales se han registrado varias ventas. Luego se mostrará los resultados en columnas de acuerdo al artículo vendido. Create database PruebaPivot go use PruebaPivot go Create table Ventas ( VentasCliente nvarchar(50), VentasProducto nvarchar(50), VentasCantidad Numeric(9,2) ) go Esta tabla puede ser generada por una consulta, revisar la opción Into en Consultas en Consultas Opciones

Insertar datos Insert into Ventas values (‘Carla’,’Teclado’,10),(‘Pedro’,’Monitor’,12),(‘Carla’,’Monitor’,6), (‘Carla’,’Mouse’,24),(‘Pedro’,’Teclado’,16),(‘José’,’Monitor’,22),(‘José’,’Teclado’,3), (‘Carla’,’Teclado’,42),(‘Pedro’,’Mouse’,34),(‘José’,’Mouse’,10),(‘Pedro’,’Teclado’,10 ) go Haciendo la consulta de los datos de ventas, se presenta ordenado por cliente para mejor entendimiento select * from Ventas order by VentasCliente go Note que Carla ha comprado diferentes productos, así como también José y Pedro. En la imagen se resalta la cantidad total comprada de Teclados por Carla.

Pivot permitirá mostrar por cada Cliente, cuantos Teclados, Monitores y Mouses compraron. Select * from Ventas Pivot (Sum(VentasCantidad) for VentasProducto in ([Monitor],[Mouse],[Teclado])) As TablaPivot go

Usando la base de datos Northwind use Northwind go Ejercicio 02 — Los clientes y las compras por año. select C.CompanyName As ‘Cliente’, DATEPART(YY,O.OrderDate) As ‘Año’, Sum(Od.UnitPrice * OD.Quantity) As ‘Importe’ from Customers as C join Orders as O on C.CustomerID = O.CustomerID join [Order Details] as OD on O.OrderID = OD.OrderID group by C.CompanyName, DATEPART(YY,O.OrderDate) go — Para hacer mas sencilla la lectura de la consulta, se utilizará una Expresión de tabla común — Common Table Expression, recuerde que se utiliza en el mismo lote. with Ventas As ( select C.CompanyName As ‘Cliente’, DATEPART(YY,O.OrderDate) As ‘Año’, Sum(Od.UnitPrice * OD.Quantity) As ‘Importe’ from Customers as C join Orders as O on C.CustomerID = O.CustomerID join [Order Details] as OD on O.OrderID = OD.OrderID group by C.CompanyName, DATEPART(YY,O.OrderDate))

select * from Ventas pivot (Sum(Importe) for Año in ([1996],[1997],[1998])) PivotTable — Columnas para los años 1996, 1997 y 1998 Pivot para los años

Ejercicio 03 — Empleados y las órdenes generadas por año. Select Empleado = E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) As ‘Año’, COUNT(O.OrderID) As ‘Órdenes’ from Employees As E join Orders As O on E.EmployeeID = O.EmployeeID Group by E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) go — Se sabe que los años son 1996, 1997 y 1998 — Se desea por cada empleado y por año las cantidades de órdenes with TotalOrdenes As ( Select Empleado = E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) As ‘Año’, COUNT(O.OrderID) As ‘Órdenes’ from Employees As E join Orders As O on E.EmployeeID = O.EmployeeID Group by E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) ) — El pivot select * from TotalOrdenes pivot (Sum(Órdenes) for Año in ([1996],[1997],[1998])) As Calculos go





Common Table Expressions CTE By Trainer SQL in Consultas, Funciones en SQL Server, Programación

Como crear Common Table Expressions en SQL Server Una expresión de tabla común (CTE) es un conjunto de resultados temporal definido en la ejecución de una instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Es como asignar un nombre a una consulta pero sin almacenarla en la base de datos como el caso de las vistas. (Ver Vistas) Una CTE es similar a una tabla derivada (Ver tablas derivadas) en que no se almacena como un objeto y dura sólo el tiempo que dura la consulta. A diferencia de una tabla derivada, una CTE puede hacer referencia a sí misma y se puede hacer referencia a ella varias veces en la misma consulta.



Una CTE se puede usar para: Crear una consulta recursiva.



Sustituir la creación de una vista cuando el uso de una vista no sea necesario; es decir, cuando no se tenga que almacenar la definición de la vista en la base de datos.



Hacer referencia a la tabla resultante varias veces en la misma instrucción.



Las CTE tiene ventajas de legibilidad mejorada y facilidad de mantenimiento de consultas complejas.



Las CTE se pueden definir en rutinas definidas por el usuario, como funciones, procedimientos almacenados, desencadenadores o vistas.

Estructura de una CTE en SQL Server

Las CTE tienen un nombre de expresión que representa la CTE, una lista de columnas opcional y una consulta que define la CTE. Después de definir una CTE, se puede hacer referencia a ella como una tabla o vista en una instrucción SELECT, INSERT, UPDATE o DELETE. La estructura de las CTE es: WITH NombreCTE [ ( NombreColumna [,…n] ) ] AS ( Consulta compleja ) Select FROM NombreCTE

Ejercicios Usando Northwind use Northwind go

Ejercicio 01 Mostrar los productos, su categoría y su proveedor. Mostrar los que no están descontinuados ordenados por precio descendentemente. Para entender las relaciones entre las tablas Ver Joins en SQL — La consulta sin CTE es como sigue select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock, C.CategoryName, S.CompanyName from Products As P join Categories As C on P.CategoryID = C.CategoryID join Suppliers As S on P.SupplierID = S.SupplierID where P.Discontinued = 0 order by P.UnitPrice desc go

— Usando CTE with ListaProductos (Codigo, Descripción, Precio, Stock, Categoría, Proveedor) As (select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock, C.CategoryName, S.CompanyName from Products As P join Categories As C on P.CategoryID = C.CategoryID join Suppliers As S on P.SupplierID = S.SupplierID where P.Discontinued = 0 )

Select * from ListaProductos order by Precio go La imagen muestra el resultado

— En la orden anterior se pueden presentar solamente algunos campos with ListaProductos (Codigo, Descripción, Precio, Stock, Categoría, Proveedor) As (select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock, C.CategoryName, S.CompanyName from Products As P join Categories As C on P.CategoryID = C.CategoryID join Suppliers As S on P.SupplierID = S.SupplierID where P.Discontinued = 0 ) Select Codigo, Descripción, Categoría from ListaProductos order by Precio go La imagen muestra únicamente el Codigo, la Descripción y la Categoría.

Ejercicio 02 Mostrar los empleados y las órdenes generadas. Es conveniente que la consulta tenga sus alias correctos. Ver Alias La consulta sin CTE select E.EmployeeID As ‘Codigo’, E.LastName As ‘Apellido’ , E.FirstName As ‘Nombre’, O.OrderID As ‘Nº Orden’, Format(O.OrderDate,’dd/MMM/yyyy’) As ‘Fecha’ from Employees As E join Orders As O on E.EmployeeID = O.EmployeeID go — Asignando la CTE with OrdenesEmpleados As (select E.EmployeeID As ‘Codigo’, E.LastName As ‘Apellido’ , E.FirstName As ‘Nombre’, O.OrderID As ‘Nº Orden’, Format(O.OrderDate,’dd/MMM/yyyy’) As ‘Fecha’ from Employees As E join Orders As O on E.EmployeeID = O.EmployeeID) select * from OrdenesEmpleados go — Usando la CTE con una consulta diferente, incluye el total de órdenes. Ver agrupamientos with OrdenesEmpleados As (select E.EmployeeID As ‘Codigo’, E.LastName As ‘Apellido’ , E.FirstName As ‘Nombre’, O.OrderID As ‘Nº Orden’, Format(O.OrderDate,’dd/MMM/yyyy’) As ‘Fecha’ from Employees As E join Orders As O on E.EmployeeID = O.EmployeeID) select Codigo, Apellido, Nombre, COUNT([Nº Orden]) As ‘Total Órdenes’ from OrdenesEmpleados group by Codigo, Apellido, Nombre go

Ejercicio 03 Mostrando los datos de una relación recursiva. En la tabla Empleados (Employees) existe en campo ReportsTo que indica que un empleado debe reportar su trabajo al que se indica en ese campo.

— Visualizar los datos select E.EmployeeID, E.FirstName, E.LastName, E.ReportsTo from Employees As E go En la imagen se puede mostrar que el empleado Nancy Davolio debe reportar al empleado con código 2 que es Andrew Fuller.

— Para un listado de los empleados y sus jefes podemos usar la siguiente instrucción. with Jefe As (select EmployeeID, LastName, FirstName from Employees) Select J.EmployeeID As ‘Cód. Jefe’, [Empleado Jefe] = J.LastName + Space(1) + J.FirstName, E.EmployeeID As ‘Cód. Empleado’, Subordinado = E.LastName + Space(1) + E.FirstName from Jefe As J join Employees As E on J.EmployeeID = E.ReportsTo go

