PDF de programación - Diseño e Implementación de Bases de Datos

Universidad Tecnológica Equinoccial

Facultad de Ciencias de la Ingeniería

Departamento de Informática

Diseño e Implementación de

Bases de Datos
Segundo Semestre 2011-2012



Prof. Diego Ordóñez Camacho, Ph.D.



Implementación Avanzada

● T-SQL

● Transact SQL
● Lenguaje declarativo

– Definimos qué hacer, mas que cómo hacer

● Ej. lenguaje imperativo

– Una función que ordena una lista

● Ej. T-SQL

– ...ORDER BY...





Subsets de (T-)SQL

● Query

● SELECT

● DML: Data Manipulation Language

● INSERT, UPDATE, DELETE, MERGE

● DDL: Data Definition Language

● CREATE, ALTER, DROP

● DCL: Data Control Language

● GRANT, REVOKE

● TCL: Transactional Control Language

● BEGIN TRANSACTION, COMMIT, ROLLBACK





Referencia

● Coles, Michael

● Pro T-SQL 2008 Programmer’s Guide

– Capítulo 2

● Páginas 26 - 34





MERGE

● Es una instrucción compuesta que puede efectuar

● INSERT, UPDATE, DELETE

● Dependiendo de la existencia de registros que

cumplan un cierto criterio (“match”)

● Modifica registros en una tabla “target”
● Basado en los registros existentes en una tabla

“source”

● Generalmente utilizado para diferenciar entre

UPDATE e INSERT (“upsert”)

● Ej: sincronizar dos tablas





...MERGE

● MERGE table AS target
● USING table2 AS source
● ON condition/expression

● WHEN MATCHED THEN

● UPDATE

– SET expression

● DELETE
-----------





...MERGE

● WHEN NOT MATCHED BY TARGET THEN

● INSERT(fields) VALUES(values)

● WHEN NOT MATCHED BY TARGET AND

condition
● THEN INSERT(fields) VALUES(values)





...MERGE

● WHEN NOT MATCHED BY SOURCE THEN

● UPDATE

– SET expression

● DELETE

● WHEN NOT MATCHED BY SOURCE AND condition

● THEN

– UPDATE

● SET expression

– DELETE





Referencia

● Coles, Michael

● Pro T-SQL 2008 Programmer’s Guide

– Capítulo 4

● Páginas 81 - 113





Lógica de 3 valores (SQL 3VL)

● En lógica booleana

● 1 < 2 ==> true
● 1 = 2 ==> false
● X > 2 ???? unknown

● NULL representa unknown









Comparaciones con NULL

● @x = NULL
● Nombre = NULL
● Nombre <> NULL

==> unknown
==> unknown
==> unknown

● @x IS NULL
● Nombre IS NOT NULL

==> true | false
==> true | false





Control de flujo

● BEGIN ..... END
● IF ..... ELSE
● WHILE ....
● GOTO ....





BEGIN ... END

● Bloque de instrucciones

● Equivale a { ... } (en Java o C, por ej.)

● Ej:

● BEGIN

END

DECLARE @j int = 10;

● PRINT @j;





...BEGIN ... END





IF ... ELSE

● Condicional
● Ej:

● DECLARE @i int = NULL;

IF @i = 10

PRINT 'TRUE.';

ELSE IF NOT (@i = 10)

PRINT 'FALSE.';

ELSE

PRINT 'UNKNOWN.';





WHILE

● Iteración
● Ej:

● DECLARE @i int = 1;

WHILE @i <= 10

BEGIN

END

PRINT @i;
SET @i = @i + 1;





CONTINUE

● Pasar a la siguiente iteración
● Ej:

● DECLARE @i int = 1;

WHILE @i <= 10

BEGIN

PRINT @i;
SET @i = @i + 1;
CONTINUE; -- Force the WHILE loop to

PRINT 'The CONTINUE keyword ensures that

this will never be printed.';

END



restart



BREAK

● Terminar la iteración
● Ej:

● DECLARE @i int = 1;

WHILE @i <= 10

BEGIN

PRINT @i;
SET @i = @i + 1;
BREAK; -- Force the WHILE loop to

terminate

PRINT 'The BREAK keyword ensures that

this will never be printed.';

END





GOTO

● Transferencia del control a un punto específico
● Ej:

● PRINT 'Step 1 Begin.';

GOTO Step3_Label;

PRINT 'Step 2 will not be printed.';

Step3_Label:
PRINT 'Step 3 End.';





WAITFOR

● Espera un período de tiempo
● Ej:

● DECLARE @time_to_pass nvarchar(8);

SET @time_to_pass = N'00:00:03';
WAITFOR DELAY @time_to_pass;
PRINT 'Step 2 completed three seconds later. ';

● DECLARE @time_absolute nvarchar(8);

SET @time_absolute = N'12:30:00';
WAITFOR TIME @time_absolute;
PRINT 'Step 2 completed at twelve thirty. ';





RETURN

● Finaliza un procedimiento o función

inmediatamente

● Opcionalmente puede devolver un valor

● Entero

– 0 significa finalización normal (sin error)
● Si no se especifica, 0 se devuelve por default

– Cualquier valor diferente de cero se considera un error





TRY ... CATCH

● Captura y maneja un error de manera

estructurada

● Si cualquier instrucción dentro del bloque TRY

genera un error
● El control pasa inmediatamente al bloque CATCH

● Solo un bloque CATCH es permitido
● Los bloques TRY ... CATCH pueden estar

anidados





... TRY ... CATCH

● Ej:

● BEGIN TRY

INSERT INTO Person.Contact(

ContactID, FirstName, LastName

) VALUES (10, 'Joe', 'Louis');

END TRY
BEGIN CATCH

SELECT

ERROR_NUMBER() AS "@Number",
ERROR_STATE() AS "@State",
ERROR_SEVERITY() AS "@Severity",
ERROR_MESSAGE() AS "Message",
ERROR_LINE() AS "Procedure/@Line",
ERROR_PROCEDURE() AS "Procedure"

FOR XML PATH('Error');





END CATCH

... TRY ... CATCH





... TRY ... CATCH

● Mensajes/funciones de error

● ERROR_NUMBER() (@@ERROR)

– Código del error

● ERROR_SEVERITY()

– Número entre 0 y 25 que indica el tipo de error

● 0 – 10: mensajes de información
● 11 – 16: errores que pueden ser corregidos por el usuario
● 17 – 25: errores graves que requieren intervención del dba

● ERROR_STATE()

– Número entre 1 y 127 que indica la fuente del error





... TRY ... CATCH

● Mensajes/funciones de error

● ERROR_PROCEDURE()

– Nombre del procedimiento o función donde ocurrió el

error

● ERROR_LINE()

– Línea de código que generó el error

● ERROR_MESSAGE()

– Mensaje descriptivo sobre el error
–





CASE

● Permite procesar expresiones, condicionalmente,

dentro de una query

● CASE Simple: compara solo la igualdad
● SELECT ProductNumber, Category =

CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product

ORDER BY ProductNumber;



...CASE

● CASE con búsqueda: comparaciones complejas

● SELECT ProductNumber, Name, "Price Range" =

CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN

WHEN ListPrice >= 250 and ListPrice < 1000 THEN

'Under $250'

'Under $1000'

ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;





Cursores

● Permiten iterar un conjunto de datos, un

registro a la vez

● Ej:

● DECLARE vend_cursor CURSOR

FOR SELECT * FROM Purchasing.Vendor

● OPEN vend_cursor
● FETCH NEXT FROM vend_cursor;





... Cursores

● DECLARE @Entity nvarchar(128), @Job nvarchar(128);
● DECLARE Employee_Cursor CURSOR FOR

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee;

● OPEN Employee_Cursor;
● FETCH NEXT FROM Employee_Cursor

INTO @Entity, @Job;

● WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @Entity + ' ' + @Job;

FETCH NEXT FROM Employee_Cursor



INTO @Entity, @Job;

END;

● CLOSE Employee_Cursor;
● DEALLOCATE Employee_Cursor;





Referencia

● Coles, Michael

● Pro T-SQL 2008 Programmer’s Guide

– Capítulo 5

● Páginas 117 - 146





UDF: User Defined Function

● Rutinas que pueden recibir parámetros,

ejecutar acciones basadas en dichos
parámetros, y devolver un resultado

● Las funciones no pueden modificar el estado

del entorno

● Tipos

● Escalares (scalars)
● Embebidas (Inline Table-valued)
● De tabla (Multi-statement Table-valued)





Scalar UDF

● Devuelve un valor simple

● Número, string, ...

● Ej:

● CREATE FUNCTION dbo.CalculateCircleArea

(@Radius float = 1.0)
RETURNS float
AS
BEGIN

END;

RETURN PI() * POWER(@Radius, 2);

● SELECT dbo.CalculateCircleArea(10);





Inline UDF

● Devuelve el resultado de una query (SELECT...) como

una tabla
● Se las conoce como vistas parametrizadas

● Ej:

● CREATE FUNCTION Sales.ufn_SalesByStore

(@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Sales.Customer AS C
WHERE C.StoreID = @storeid
);



● SELECT * FROM Sales.ufn_SalesByStore (934)



Multi-statement UDF

● Regresa una tabla construida por la función
● La estructura de la tabla se debe declarar

correctamente

● La función puede ser mucho más compleja que

en el caso anterior

● Ej:
● ...





... Multi-statement UDF

● CREATE FUNCTION dbo.SomeUnits
(@First char(1), @Second char(1))

RETURNS @SomeUnits TABLE

(Code nchar(3), Measure nvarchar(50))

AS
BEGIN

INSERT @SomeUnits(Code, Measure)
SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode LIKE('%' + @First + '%');

INSERT @SomeUnits(Code, Measure)
SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode LIKE('%' + @Second + '%');

RETURN

END


● SELECT * FROM dbo.SomeUnits('A', 'B')



Referencia

● Coles, Michael

● Pro T-SQL 2008 Programmer’s Guide

– Capítulo 6

● Páginas 151 – 162





SP: Stored Procedures

● Programas o unidades de código compuestas

por instrucciones T-SQL

● Permiten especificar parámetros de salida
● Permiten especificar los parámeros por nombre

o por posición

● Pueden modificar el estado del entorno
● RETURN solo puede devolver un entero

● Se usan tablas temporales, p.ej., para compartir

valores





Stored Procedures

● Ej:

● CREATE PROCEDURE

HumanResources.uspGetEmployeesTest2
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SELECT FirstName, LastName, Department FROM

HumanResources.vEmployeeDepartmentHistory

WHERE FirstName = @FirstName

AND LastName = @LastName
AND EndDate IS NULL;


GO

● EXECUTE

HumanResources.uspGetEmployeesTest2
N'Ackerman', N'Pilar';





... Stored Procedures

● Ej:

● CREATE PROCEDURE

HumanResources.uspGetEmployeesTest3
@LastName nvarchar(50), @FirstName nvarchar(50),

@Department nvarchar(50) OUTPUT

AS
SELECT @Department = Department FROM

HumanResources.vEmployeeDepartmentHistory

WHERE FirstName = @FirstName

AND LastName = @LastName
AND EndDate IS NULL;


GO

● ===>>>





... Stored Procedures

● Ej: (ejecución)

● DECLARE @Depar nvarchar(50);
● EXECUTE