1 / 27

Módulo 12

Módulo 12. Transact-SQL Avanzado. TEMARIO. Common Table Expression (CTE) PIVOT y UNPIVOT APPLY CROSS APPLY OUTER APPLY Referencia integral Declarativa (DRI) Manejo de Errores OUTPUT READPAST WAITFOR DLL Triggers Notificación de Eventos. Common Table Expression (CTE).

sahkyo
Download Presentation

Módulo 12

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Módulo 12 Transact-SQL Avanzado

  2. TEMARIO • Common Table Expression (CTE) • PIVOT y UNPIVOT • APPLY • CROSS APPLY • OUTER APPLY • Referencia integral Declarativa (DRI) • Manejo de Errores • OUTPUT • READPAST • WAITFOR • DLL Triggers • Notificación de Eventos

  3. Common Table Expression (CTE) • Una tabla temporal nominada, la cual deriva de un query simple y definido dentro del alcance de la ejecución del comando SELECT, INSERT, UPDATE, o DELETE

  4. Common Table Expression (CTE) • Ejemplo:

  5. Common Table Expression (CTE) • Tipos de CTE • No recursivo • CTEs que no estan autoreferenciados • Recursivo • CTE’s que tienen una referencia a si mismos • Uno o mas querys • Un query debe ser no recursivo Anchor Member (AM) • Otros querys son recursivos Recursive Members (RM) • UNION ALL join • Requiere que todos los miembros se referencien exactamente a la misma columna

  6. Common Table Expression (CTE) • Single-Parent Scenario

  7. PIVOT y UNPIVOT • Pre- SQL 2005 • Sentencia CASE– código extenso y complejo • PIVOT y UNPIVOT • PIVOT • Convierte filas en columnas • UNPIVOT • Convierte columnas en filas

  8. PIVOT

  9. Resultados de Pivot

  10. UNPIVOT • Revierte la operación de PIVOT • Normaliza datos pre-pivoted • Inserta columnas en filas • Elimina NULLs en las columnas resultantes

  11. Datos Unpivot

  12. APPLY • APPLY facilita las operaciones de join con funciones del tipo de valor tabla. • Permite la invocación de una función del tipo valor tabla para cada fila devuelta por una expresión outer table de la consulta • La función del tipo de valor tabla actúa como el right input y la expresión outer table actua como el left input • El right input es evaluado para cada fila desde el left input y las filas creadas son combinadas por el output final • La lista de columnas creadas por APPLY es el set de columnas definidos en el left input seguido por la lista de columnas devueltas por el right input

  13. CROSS APPLY • CROSS APPLY devuelve solamente las filas desde la outer table producida por una función del tipo de valor tabla

  14. CROSS APPLY

  15. OUTER APPLY • OUTER APPLY • Retorna tanto las filas que producen un resultado como las que no. • Retorna valores NULL en las columnas resultantes de una función del tipo de valor tabla • Cross Apply (Ejemplo): • El departamento de Logística no aparece en el resultado • Usando Outer Join, Logística aparecerá • Se mostrara Nulls will en las columnas que correspondan a una función del tipo tabla

  16. OUTER APPLY

  17. Mejoras en referencia integral Declarativa (DRI) • SET NULL • SET DEFAULT

  18. Mejoras en referencia integral Declarativa (DRI) • CREATE TABLE Customers • ( customerid CHAR(5) NOT NULL, • /* other columns */ • CONSTRAINT PK_Customers PRIMARY KEY(customerid) • ) • INSERT INTO Customers VALUES('DUMMY') • INSERT INTO Customers VALUES('FRODO') • INSERT INTO Customers VALUES('GNDLF') • INSERT INTO Customers VALUES('BILBO')

  19. Mejoras en referencia integral Declarativa (DRI) • CREATE TABLE Orders • ( orderid INT NOT NULL, customerid CHAR(5) NULL DEFAULT('DUMMY'), orderdate DATETIME NOT NULL, • CONSTRAINT PK_Orders PRIMARY KEY(orderid), • CONSTRAINT FK_Orders_Customers FOREIGN KEY(customerid) REFERENCES Customers(customerid) • ON DELETE SET NULL ON UPDATE SET DEFAULT ) • INSERT INTO Orders VALUES(10001, 'FRODO', '20040101') • INSERT INTO Orders VALUES(10002, 'FRODO', '20040102') • INSERT INTO Orders VALUES(10003, 'BILBO', '20040101') • INSERT INTO Orders VALUES(10004, 'BILBO', '20040103') • INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104') • INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')

  20. Manejo de Errores • Versiones anteriores: • El manejo de errores es requerido en código complejo y que sea pasible de errores. • Para centralizar el manejo se debían utilizar las sentencias GOTO y RETURN • Errores como la conversión de tipos provocaba la terminación de la ejecución; en consecuencia, estos no podían ser atrapados dentro del Transact-SQL • SQL Server 2005: • TRY/CATCH

  21. TRY/CATCH (Manejo de Errores) • Example • USE AdventureWorks • GO • BEGIN TRANSACTION • GO • BEGIN TRY • -- Generate a constraint violation error. • DELETE FROM Production.Product WHERE ProductID = 980; • END TRY • BEGIN CATCH • SELECT ERROR_NUMBER() AS ErrorNumber, • ERROR_SEVERITY() AS ErrorSeverity, • ERROR_STATE() as ErrorState, • ERROR_MESSAGE() as ErrorMessage; • END CATCH • GO • ROLLBACK TRANSACTION; • GO

  22. Mejoras en TOP • Limitaciones en versiones anteriores • La opción TOP en las versiones anteriores de SQL Server, requería un numero o porcentaje de filas como una constante • TOP no estaba disponible para comandos INSERT, UPDATE y DELETE • En SQL Server 2005 se agregaron las siguientes mejoras • Variables y Sub-Queries pueden ser utilizadas opcionalmente como una expresión numérica retornando el numero o porcentaje de filas que serán afectadas por el query • TOP esta disponible para comandos DELETE, UPDATE e INSERT

  23. DML con Resultados (Clausula OUTPUT) • La nueva cláusula OUTPUT que facilita el retorno de datos referenciando datos insertados o borrados de una tabla • En sentencias INSERT, refiriendo a INSERTED para obtener las columnas desde la ultima inserción • En sentencias DELETE refiriendo a DELETED para obtener las columnas eliminadas en la ultima acción • En sentencias UPDATE, referir a DELETED para obtener el ultimo estado de los datos, y a INSERTED para ver el estado actual.

  24. READPAST • En versiones anteriores de SQL Server, READPAST podía ser utilizado únicamente en sentencias SELECT. • En SQL Server 2005, READPAST puede ser utilizado con sentencias UPDATE y DELETE. • Permite múltiples procesos paralelos manejando filas non-locked, y salteando filas lockeadas que están siendo utilizadas por otra sesión. • READPAST especifica al motor que no lea filas y paginas que están lockeadas por otra transacción. • READPAST solamente puede ser especificado en transacciones operando con el nivel de aislamiento de READ COMMITTED o REPEATABLE READ.

  25. WAITFOR • WAITFOR bloquea la ejecución de un batch, stored procedure, o transacción hasta que un tiempo o intervalo se haya cumplido, o una sentencia especificada modifique al menos una fila. • Nuevas características • Timeout • WAITFOR valid RECEIVE statement (SQL Server Service Broker) • Los Cursores no pueden ser abiertos en una sentencia WAITFOR. • Vistas no pueden ser definidas WAITFOR.

  26. DDL Triggers • SQL Server 2005 permite la declaración de triggers en eventos DDL • Los triggers DDL se ejecutan en el contexto de la transacción desde la cual fueron disparados. • Un ROLLBACK puede suceder para deshacer eventos que han disparado el trigger.

  27. Notificación de Eventos Event Notifications Posted SQL Server Service Broker (SSB) Service Event Occurance Event Consumer Message Queue • Notificaciones de Eventos se ejecutan en respuesta a: • DDL • DML statements • Otros eventos a seguir.

More Related