View
5
Download
0
Category
Preview:
Citation preview
D R . M I G U E L Á N G E L O R O S H E R N Á N D E Z
Advanced SQL
Agenda
SQL Avanzado
Funcionalidades SQL99 y SQL2003
Constraints, storedprocedures, triggers
Tipos de datos
SQL 99
Other names SQL 3
SQL 1999
Common table expressionsand recursive queriesWITH [RECURSIVE] construct
Some OLAP capabilities ROLLUP
CUBE
GROUPING SETS
Role-based access control CREATE ROLE
Data types Boolean data types
No soportados por SQL Server 2005, Oracle 9i, IBM DB2)
Distinct user-defined typesof power Distinct types Extended typesCREATE TYPE age AS INTEGER FINAL;
CREATE TYPE salary AS INTEGER FINAL;
Structured user-definedtypes
SQL 2003
5° revisión
SQL 2008: última revisión
Características relacionadas con XML
Secuencias estandarizadas
Tipos de columnas
Valores auto-generados
Identidad de columnas
Sentencia MERGE
Ampliaciones para CREATE TABLE
CREATE TABLE AS
CREATE TABLE LIKE
Funciones de ventana
SQL 2003MERGE
CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCodenchar(3), @Name nvarchar(25) AS
BEGIN
SET NOCOUNT ON;
-- Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES (@UnitMeasureCode, @Name)
END
END;
GO -- Test the procedure and return the results.
SQL 2003MERGE
EXEC InsertUnitMeasure
@UnitMeasureCode = 'ABC',
@Name = 'Test Value';
SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO
SQL 2003MERGE
-- Rewrite the procedure to perform the same
-- operations using the MERGE statement.
-- Create a temporary table to hold the updated
-- or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable (
ExistingCode nchar(3), ExistingName nvarchar(50),
ExistingDate datetime, ActionTaken nvarchar(10),
NewCode nchar(3), NewName nvarchar(50),
NewDate datetime );
GO
SQL 2003MERGE
ALTER PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS target USING
(SELECT @UnitMeasureCode, @Name) AS source
(UnitMeasureCode, Name) ON
(target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
SQL 2003MERGE
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC',
@Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ',
@Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC',
@Name = 'Another Test Value';
SELECT * FROM #MyTempTable;
-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable; GO
Definición Clases
Especificación de reglas para los datos
Violación de la restricción operación abortada
NOT NULL
UNIQUE
PRIMARY KEY = NOT
NULL + UNIQUE
FOREIGN KEY
CHECK
DEFAULT
Constraints, stored procedure, triggersConstraints
Constraints, stored procedure, triggersNOT NULL Constraint
Default: NULL para
columna de una tabla
NOT NULL que un
campo siempre contiene un valor
No se pude insertar un nuevo registro o actualizar si la restricción no se cumple
CREATE TABLE PersonsNotNull
(
P_Id int
NOT NULL,
LastName varchar(255)
NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Constraints, stored procedure, triggersUNIQUE Constraint
Identifica de manera única cada registro de una tabla
Las restricciones UNIQUE y PRIMARY KEY garantizan
unicidad
Una restricción PRIMARY KEY automáticamente tiene una restricción UNIQUE
Pueden existir varias restricciones UNIQUE, pero sólo una PRIMARY KEY
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255)
NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID
(P_id, LastName)
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
Constraints, stored procedure, triggersPRIMARY KEY Constraint
Identifica de manera única cada registro de una tabla
Sólo valores únicos
No puede contener valores NULL
La mayoría de las tablas tienen una llave primaria y cada tabla sólo puede contener una llave primaria
CREATE TABLE Persons
(
P_Id int NOT NULL
PRIMARY KEY,
LastName varchar(255)
NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Constraints, stored procedure, triggersPRIMARY KEY Constraint
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID
PRIMARY KEY (P_Id,
LastName)
ALTER TABLE Persons
DROP PRIMARY KEY
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
Constraints, stored procedure, triggersFOREIGN KEY Constraint
Una FK apunta hacia una PK
Previene acciones que destruyan la integridad en las tablas
Previene la inserción de datos inválidos en las columnas asociadas con FK
CREATE TABLE Orders
(
O_Id int NOT NULL
PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY
REFERENCES Persons(P_Id)
)
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
Constraints, stored procedure, triggersFOREIGN KEY Constraint
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons
(P_Id)
ALTER TABLE Orders
ADD CONSTRAINT
fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES
Persons(P_Id)
ALTER TABLE Orders
DROP CONSTRAINT
fk_PerOrders
Constraints, stored procedure, triggersCHECK Constraint
Limitar el rango de valoresen una columna
CREATE TABLE Persons
(
P_Id int NOT NULL
CHECK (P_Id>0),
LastName varchar(255)
NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Constraints, stored procedure, triggersCHECK Constraint
ALTER TABLE Persons
ADD CHECK (P_Id>0)
ALTER TABLE Persons
ADD CONSTRAINT chk_Person
CHECK (P_Id>0 AND
City=‘Córdoba’)
ALTER TABLE Persons
DROP CHECK chk_Person
Constraints, stored procedure, triggersDEFAULT Constraint
Insertar un valor pordefault en una columna
Si ningún valor esespecificado, el valor pordefault será agregado en todos los nuevos registros
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date
DEFAULT GETDATE()
)
Constraints, stored procedure, triggersDEFAULT Constraint
ALTER TABLE Persons
ALTER COLUMN City
SET DEFAULT ‘Córdoba’
ALTER TABLE Persons
ALTER COLUMN City
DROP DEFAULT
Constraints, stored procedure, triggersStored procedure
¿qué es un procedimiento almacenado (stored procedure)? sentencias SQL que son almacenadas en la base de datos
grupo de sentencias Transact-SQL compiladas en un plan de ejecución
[MSDN]
No puede ser utilizado directamente en expresiones
similitudes con los procedimientos de otros lenguajes de programación Aceptan parámetros de entrada y regresan múltiples valores en la forma de
parámetros de salida
Contienen sentencias que ejecutan operaciones en la base de datos, incluyen la invocación a otros stored procedures
Regresan un valor de status para indicar el éxito o fracaso (así como la razón del fracaso)
Constraints, stored procedure, triggersStored procedure: beneficios
Programación modular
Ejecuciones más rápidas – mejoras en el desempeño
Reducción del tráfico de red
Uso como mecanismo de seguridad
Constraints, stored procedure, triggersstored procedure: resultados
1. parámetros de salida (valores de tipo entero o de tipo carácter, o variable de tipo cursor)
2. códigos (valor entero)
3. Un result set por cada sentencia SQL contenida en stored procedure
4. Un cursor global que puede ser referenciado fuera del stored procedure
Constraints, stored procedure, triggersstored procedure: ¿para qué?
implementación de la lógica de negocio
encapsulación/* comentario multilínea: utilización de condicionales,
variables y sentencias SQL */
IF (@QuantityOrdered < (SELECT QuantityOnHand
FROM Inventorys
WHERE PartID = @PartOrdered))
BEGIN
-- sentencias SQL, p. e para actualizar
END
ELSE
BEGIN
-- sentencias SQL, p. e. para recuperar los IDs
END
Constraints, stored procedure, triggersstored procedure
planes de ejecución y stored procedures
plan de ejecución parcialmente compilado eficiencia en la ejecución
tipos de stored procedures
System stored procedures
Catalog stored procedures: Active Directory Procedures, Catalog Procedures, Cursor Procedures, Database Maintenance Plan Procedures, DistributedQueries Procedures, Full-Text Search Procedures, Log Shipping Procedures, …
ejecución
exec sp_displayallusers
sintaxis: creación
CREATE PROCEDURE <nombre_storedprocedure> AS
Constraints, stored procedure, triggersstored procedure: ejemplo 1
CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
-- resultado tipo 3: result set
SELECT Ord.EmployeeID,
SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON
(Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID ORDER BY Ord.EmployeeID
-- resultado tipo 1: parámetro de salida
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]
-- resultado tipo 2: regreso de código
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO
Constraints, stored procedure, triggersstored procedure: ejemplo 1: pruebas
/* Declaración de las variables que recibirán el código de
regreso y el parámetro de salida */
DECLARE @OrderSum INT
DECLARE @LargestOrder INT
-- ejecución del sp, que regresa un result set
EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder
OUTPUT
-- utilización del código de regreso y el parámetro de salida
PRINT ‘La cantidad solicitada más grande es: ' +
CONVERT(CHAR(6), @LargestOrder)
PRINT 'La suma de las cantidades solicitadas es: ' +
CONVERT(CHAR(6), @OrderSum)
GO
Constraints, stored procedure, triggersstored procedure: ejemplo 2
CREATE PROCEDURE sp_DepositUpdate(
@customerName varchar(10),
@branchName varchar(10),
@saldo money)
AS
BEGIN TRANSACTION actualizaSaldo
BEGIN TRY
UPDATE deposit
SET balance = @saldo
WHERE customer_name = @customerName
AND branch_name = @branchName
COMMIT TRANSACTION actualizaSaldo
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION actualizaSaldo
END CATCH
EXEC sp_DepositUpdate 'Hernández', 'Plaza', 250
Constraints, stored procedure, triggersstored procedure: ejemplo 3 …
USE [InterfazSapBOSmartIt]
GO
IF EXISTS (SELECT ID FROM dbo.SysObjects WITH (NOLOCK)
WHERE ID = Object_ID('HistoricoGarantiaRegistrar') AND
OBJECTPROPERTY(Id, 'IsProcedure') = 1)
DROP PROCEDURE HistoricoGarantiaRegistrar
GO
Constraints, stored procedure, triggersstored procedure: … ejemplo 3 …
/* Objeto: HistoricoGarantiaRegistrar
Descripción: Registrar el estatus de la operación del
servicio web Garantias
Parámetros de Entrada:
Número de serie del autobús
Estatus
Comentarios
Parámetros de Salida:
Conjunto de registros
Retorno: n/a
Autor: Héctor NR
Fecha: 21/09/2011
Modificaciones:
21/09/2011 – Héctor NR - Creación
Modo de ejecución:
EXEC HistoricoGarantiaRegistrar '123456890', 'Ok', ''
*/
Constraints, stored procedure, triggersstored procedure: … ejemplo 3 …
CREATE Proc [dbo].[HistoricoGarantiaRegistrar]
@NumeroSerie VARCHAR(17),
@Estatus VARCHAR(10),
@Comentarios VARCHAR(50),
@Exito BIT = 0 OUTPUT
AS
DECLARE @Accion VARCHAR(100)
DECLARE @Mensaje VARCHAR(250)
Constraints, stored procedure, triggersstored procedure: … ejemplo 3 …
BEGIN TRY
SET NOCOUNT ON
SET @Exito = 0
SET @Accion = 'Registrar en la bitácora de garantías el' +
'estatus de la operación'
INSERT INTO HistoricoGarantia
(NumeroSerie, HoraConsulta, Estatus, Comentarios)
VALUES (@NumeroSerie, GETDATE(), @Estatus, @Comentarios)
SET @Exito = 1
SET NOCOUNT OFF
END TRY
Constraints, stored procedure, triggersstored procedure: … ejemplo 3
BEGIN CATCH
SET NOCOUNT OFF
SET @Mensaje = 'Acción: ' + @Accion + ' ' +
'Error: ' + LTRIM(STR(ERROR_NUMBER())) +
' ' + ERROR_MESSAGE() + ' ' +
'Línea: ' + LTRIM(STR(ERROR_LINE()))
RAISERROR(@Mensaje,10,1)
END CATCH
Constraints, stored procedure, triggersfunciones
Subrutinas utilizadas para encapsular la lógica
Tipos de funciones
Built-in functions
User-defined functions
EjemploCREATE FUNCTION CubicVolume
-- datos de entrada en centímetros
(@CubeLength decimal(4,1),
@CubeWidth decimal(4,1),
@CubeHeight decimal(4,1))
RETURNS decimal(12,3) –- centímetros cúbicos
AS
BEGIN
RETURN (@CubeLength * @CubeWidth * @CubeHeight)
END
Constraints, stored procedure, triggersfunciones: ejemplos de uso
CREATE TABLE Bricks
(BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(dbo.CubicVolume(BrickHeight, BrickLength,
BrickWidth)))
SELECT *
FROM tb_Employees AS E,
dbo.fn_EmployeesInDept('shipping') AS EID
WHERE E.EmployeeID = EID.EmployeeID
Constraints, stored procedure, triggersfunciones: ejemplos de uso
CREATE FUNCTION LargeOrderShippers
(@FreightParm money)
RETURNS @OrderShipperTab TABLE
(ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName, O.OrderID,
O.ShippedDate, O.Freight
FROM Shippers AS S
INNER JOIN Orders AS O ON
(S.ShipperID = O.ShipVia)
WHERE O.Freight > @FreightParm
RETURN
END
Constraints, stored procedure, triggersfunciones: ejemplos de uso
@OrderShipperTab
nombre de la variable local
Inserción de resultados en esta variable
ejecuciónSELECT * FROM LargeOrderShippers($500)
Constraints, stored procedure, triggerstriggers
Sentencia ejecutada automáticamente por el sistema como side effectde una modificación en la base de datos
Es uno de los objetos para mantener la integridad (otros: constraints, rules, defaults)
Clase especial de stored procedures asociados a tablas o vistas cuando se ejecutan sentencias UPDATE, INSERT, DELETE
Las tablas pueden tener múltiples triggers
Diseño
Especificar las condiciones para las cuales el trigger se ejecutará
Especificar las acciones a realizar cuando el trigger se ejecuta
Constraints, stored procedure, triggerstriggers: ejemplo
CREATE TRIGGER reminder
ON titles
FOR INSERT
AS
EXEC master..xp_sendmail 'MaryM',
'Nuevo título, mencionar en el
reporte a distribuir.'
Constraints, stored procedure, triggerscursores
Referencia a un registro
Tipo de datos para variables o parámetros de salida en los stored procedures
Operaciones
Declarar variables locales
Ejecutar sentencias OPEN, FETCH, CLOSE, DEALLOCATE
asociadas a las cursores
Parámetros de salida del stored procedure
Función CURSOR_STATUS
Constraints, stored procedure, triggerscursores
Constraints, stored procedure, triggerscursores: @@FETCH_STATUS
Valor regresado
Descripción
0 Éxito del FETCH
-1 Falló el FETCH
-2 Registro inexistente
Constraints, stored procedure, triggerscursores: ejemplo 1
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2008R2.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
Constraints, stored procedure, triggerscursores: ejemplo 2 …
DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80),
@product nvarchar(50);
PRINT '-------- Vendor Products Report --------';
DECLARE vendor_cursor CURSOR FOR
SELECT BusinessEntityID, Name FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;
OPEN vendor_cursor;
FETCH NEXT FROM vendor_cursor INTO @vendor_id,
@vendor_name;
Cursores… ejemplo 2 …
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ';
SELECT @message = '----- Products From Vendor: ' +
@vendor_name;
PRINT @message;
-- Declare an inner cursor based
-- on vendor_id from the outer cursor.
DECLARE product_cursor CURSOR FOR
SELECT v.Name FROM Purchasing.ProductVendor AS pv
INNER JOIN Production.Product AS v
ON pv.ProductID = v.ProductID AND
pv.BusinessEntityID = @vendor_id;
Constraints, stored procedure, triggerscursores: … ejemplo 2 …
-- Variable value from the outer cursor
OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @product;
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>';
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product;
END;
CLOSE product_cursor;
Constraints, stored procedure, triggerscursores: … ejemplo 2
DEALLOCATE product_cursor; -- Get the next vendor.
FETCH NEXT FROM vendor_cursor INTO @vendor_id,
@vendor_name;
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
Fin
Recommended