Conceptos de Bases de Datos Relacionales Parte 2

1. Conceptos de Bases de Datos RelacionalesParte 2

2. Function (funciones) • Una function es una operación que manipula datos de una determinada manera • Terminología • Argumento – Valor o expresión dada a la function • Resultado – Valor o expresión retornado por la function

3. Uso de funciones • Las funciones se pueden usar en: • Listas de un select • Cláusulas where • En cualquier sitio donde se permita una expresión

4. Uso de funciones en listas de un select • Sintaxis simplificada: selectfunction_name (arguments) • Ejemplo: select distinct upper(type) from titles - BUSINESS MOD_COOK TRAD_COOK UNDECIDED PSYCHOLOGY POPULAR_COMP

5. Uso de funciones en una cláusula where • Sintaxis simplificada: selectcolumn_list from table_name where condition_with_one_or_more_functions • Ejemplo: select title from titles where lower(title) like "%the%" title ----- The Gourmet Microwave The Psychology of Computer Cooking (2 rows affected)

6. Conversión de datatypes • Bajo mucha circunstancias, los servidores pueden comparar dos valores de diferentes datatypes • Conversión implícita • La que hace automáticamente el servidor • Conversion explícita • Conversion que requiere de la function convert • Conversion no soportada • Conversion que no puede realizar el servidor

7. Función convert • La función convert cambia valores de un datatype a otro • Sintaxis simplificada: convert (datatype, expression [, style ] ) • Ejemplo que convierte money a char(10): select price from titles where convert (char(10), price) like "%.99"

8. Ejemplo con la función convert • Verificar la base de datos en que se trabaja: select db_name() • Escribir esta consulta usando “+” (requiere operandos tipo string. La consulta fallará: select "The price of "+ title + " is $" + price from pubs2..titles • ¿Por qué falló la consulta?Reescribir la sentencia usando convert: select "The price of "+ title + "is $" + convert(varchar(10),price) from pubs2..titles

9. Categorías de funciones • Date • Mathematical • String • Aggregate

10. Funciones date • Las funciones Date son extensiones SQL que manipulan datos tipo datetime

11. Ejemplos de funciones date • Función getdate select getdate() - Feb 4 1999 12:00 AM • Función datename select datename(mm, pubdate) from titles where title = "Net Etiquette" - July • Función dateadd select dateadd(dd, 5, pubdate) from titles where title = "Net Etiquette" - Jul 29 1991 12:00 A

12. Funciones matemáticas • Las funciones matemáticas son extensiones SQL que manipulan datos numéricos

13. Ejemplos de funciones matemáticas: • Función round select round(price, 0) from titles where title_id = "PC1035" - 23.00 • Función floor select floor(3.14159) - 3 • Función sqrt (raíz cuadrada) select sqrt(122) - 11.045361017187261

14. Funciones string • Las funciones de string son extensiones SQL que manipulan datos de caracteres

15. Ejemplos de funciones string • Función substring : • select substring("(510) 922-4087",7,8) • - • 922-4087 • Función right : • select right("Mr. Ringer", 6) • - • Ringer • Función lower : • select au_lname, au_fname from authors • where lower(au_lname) like ("de%") • au_lname au_fname • -------- -------- • DeFrance Michael • del Castillo Innes

16. Operador + • Funcionalmente similar a una función de string • Concatena dos o más strings de caracteres • Ejemplo: select "Mr. " + "Harry Sullivan" - Mr. Harry Sullivan • Ejemplo: select au_id, au_lname + ", " + au_fname as "name" from authors au_id name ----- ---- 172-32-1176 White, Johnson 213-46-8915 Green, Marjorie 238-95-7766 Carson, Cheryl ...

17. Funciones aggregate • Las funciones Aggregate son estándares ANSI que ejecutan operaciones matemáticas con valores de las columnas • Excepto count(*), las funciones aggregate ignoran los NULLs • No se pueden usar en una cláusula where, excepto si hace parte de un subquery • Ejemplo select title_id, price from titles where price > (select avg(price) from titles)

18. Ejemplos de funciones aggregate • función count(*) • select count(*) from titles • where type = "popular_comp" • - • 3 • función count(column_name) • select count(price) from titles • where type = "popular_comp" • - • 2 • función avg • select avg(price) from titles • where type = "popular_comp" • - • 21.48

19. Función isnull • La función isnull reemplaza valores tipo NULL en un determinado valor no NULL • Sintaxis: • isnull (column_which_may_have_NULL_values, non-NULL value) • Ejemplo: • select avg(price) from titles • ------ • 14.77 • select avg(isnull (price, $0.00)) from titles • ------ • 13.13

21. Transacciones • Una transacción es una o más sentencias que se toman como una unidad (todo termina bien o todo se aborta) • Una transacción es una unidad lógica de trabajo • Definida para las reglas del negocio • Típicamente incluye al menos una modificación de datos • Pasa la base de datos de un estado consistente a otro • Una transacción tiene dos posibles salidas: • Committed • Todas las modificaciones quedan en firme • Rolled back • Las modificaciones retornan a su estado inicial

22. Rol de las transacciones • Proteger los datos de las fallas del software, hardware, y potencia eléctrica • Permitir el aislamiento de datos de tal forma que varios usuarios pueden acceder simultáneamente a los datos sin interferencia

23. Cuándo usar transacciones? • Cuando un conjunto de sentencias se deben comportar como una unidad

24. Sentencias para transacciones • Cuatro sentencias definen la estructura de una transacción • begin tran • commit tran • rollback tran • save

25. begin tran y commit tran • begin tran • Inicia la transacción • commit tran • Finaliza la transacción • Todas las modificaciones quedan en firme

26. begin tran y commit tran • Sintaxis: begin { tran | transaction} [ transaction_name ] commit [ tran | transaction | work ] [ transaction_name | savepoint_name ] • Ejemplo: -- @amount is a monetary amount to be transferred. -- @from_account is the account to be debited. -- @to_account is the account to be credited. begin tran update accounts set balance = balance - @amount where account = @from_account update accounts set balance = balance + @amount where account = @to_account commit tran

27. rollback tran • rollback tran termina una transacción • Deshace las modificaciones que se hayan hecho • La ejecución continua con la instrucción siguiente a rollback

28. Sintaxis para rollback tran • Sintaxis: rollback [ tran [ transaction_name | savepoint_name ] | transaction [ transaction_name | savepoint_name ] | work [ transaction_name | savepoint_name ] ] • Ejemplo: -- If @from_account is below 0, abort the transfer begin tran update accounts set balance = balance - @amount where account = @from_account update accounts set balance = balance + @amount where account = @to_account if (select balance from accounts where account = @from_account) < 0 rollback tran else commit tran

29. Ejemplo con rollback tran -- When transferring money from savings to -- checking, the balance in savings must -- decrease and the balance in checking must -- increase by the same amount. Both actions -- must occur or else the transaction will fail. begin transaction /* take money out of savings account */ update accounts set balance = balance - $1000 where acct_num = "83165-S" if @@error <> 0 or @@rowcount <> 1 begin -- The update failed. Either there -- was a rule violation, unexpected error, -- no accounts were affected, or more than -- one account was affected rollback tran return -- ends execution of transaction end

30. /* put money into checking account */ update accounts set balance = balance + $1000 where acct_num = "83165-C" if @@error <> 0 or @@rowcount <> 1 begin -- The update failed. Either there -- was a rule violation, unexpected error, -- no accounts were affected, or more than -- one account was affected rollback tran return -- ends execution of transaction end commit transaction select acct_num, balance from accounts where acct_num like "83165-[SC]"

31. Ejemplo con begin tran, commit tran, rollback tran • Crear una tabla: select * into mytitles from pubs2..titles • Iniciar una transacción: begin tran • Borrar todas las filas de la tabla: delete from mytitles select * from mytitles • Deshacer el borrado: rollback tran select * from mytitles • Iniciar una transacción: begin tran

32. Borrar todas las filas de la tabla: delete from mytitles select * from mytitles • Dejar en firme el borrado: commit tran select * from mytitles • Borrar los objetos de base de datos creados: drop table mytitles

33. save • save crea un nombre de un punto de grabación • Es una extensión SQL que permite rollbacks parciales

34. Sintaxis para save • Sintaxis: save { transaction | tran } savepoint_name • Ejemplo: -- The rollback rolls back to point1. This undoes -- the delete of business books, but not the -- delete of mod_cook books. Execution resumes -- with the statement after the rollback, which -- deletes popular_comp books. begin tran delete from titles where type = "mod_cook" save tran point1 delete from titles where type = "business" rollback tran point1 delete from titles where type = "popular_comp" commit tran

35. Ejemplo con savepoint -- This bank charges a fee for every use of an ATM. -- If the funds cannot be deducted from savings,-- the fee for the ATM usage remains. However, if -- the funds cannot be added to checking, the usage -- fee for the ATM is waived. begin tran /* apply ATM usage fee */ update accounts set serv_chge = serv_chge + $1.50 where acct_num = "83165-S" save transaction charge /* deduct funds from savings */ update accounts set balance = balance - 100 where acct_num = "83165-S"

36. if @@error <> 0 or @@rowcount <> 1 begin rollback transaction charge commit tran return end else update accounts /* add funds to checking */ set balance = balance + $100 where acct_num = "83165-C" if @@error <> 0 or @@rowcount <> 1 begin rollback tran return end else commit tran return go

37. Ejemplo con savepoints • Crear una tabla: select * into mytitles from pubs2..titles • Iniciar una transacción: begin tran • Borrar datos de una tabla: delete from mytitles where type = "psychology" select * from mytitles • Establecer un savepoint: save tran title_sav • Borrar los restantes datos de la tabla: delete from mytitles select * from mytitles

38. Restaurar hasta el savepoint: rollback tran title_sav select * from mytitles • Dejar en firme la transacción: commit tran • Borrar los objetos de base de datos creados: drop table mytitles

39. Transacciones anidadas • Se pueden tener transacciones anidadas: • El begin y commit más externos comienzan y finalizan las transacciones • Las sentencias begin y commit internos solamente guardan un registro del nivel de anidamiento • Ejemplo: begin tran delete from titles where type = "mod_cook" begin tran delete from titles where type = "business" begin tran delete from titles where type = "trad_cook" commit tran -- No deletes committed yet. commit tran -- No deletes committed yet. commit tran -- All deletes committed here.

40. Rollbacks anidados • Cuando se ejecutan rollback anidados sin puntos de grabación: • El rollback deshace todas las transacciones en progreso, sin importar el nivel de anidamiento del rollback • Termina la transacción • La ejecución continúa con la sentencia siguiente al rollback • Ejemplo: begin tran delete from titles where type = "mod_cook" begin tran delete from titles where type = "business" begin tran delete from titles where type = "trad_cook" rollback tran -- Entire transaction rolled back commit tran -- This statement has no effect commit tran -- This statement has no effect

41. Transacciones y el registro de transacciones • El registro de transacciones almacena los efectos de cada insert, update y delete • El sistema utiliza el registro de transacciones para rehacer las transacciones que se reversaron • Se registra el comienzo de una transacción, los commits y rollbacks • Si un servidor falla durante una transacción, no hay registro de un rollback o commit • Durante la recuperación (recovery), las modificaciones en transacciones sin un registro de rollback o commit no tendrán efecto. Si las modificaciones fueron grabadas en disco, se revertirán.

42. Modo de transacción • Un modo de transacción especifica cómo el servidor debe definir las transacciones • Dos modos de transacción • Unchained • Chained

43. Modo unchained • En modo unchained, se requiere explícitamente de una sentencia begin tran • También se requiere de commit tran o rollback tran explícitos

44. Ejemplo de modo Unchained set chained off begin trandelete salesdetail where stor_id = "5023" and ord_num = "AB-123-DEF-425-1Z3" if @@error <> 0 begin rollback tran return enddelete sales where stor_id = "5023" and ord_num = "AB-123-DEF-425-1Z3" if @@error <> 0 begin rollback tran return endcommit tran

45. Modo chained • En modo chained, el servidor ejecuta un begin implícito antes de: • Sentencias DML– insert, update, delete, select • Sentencias de Cursor– open, fetch • Se requiere de commit tran o rollback tran explícitos • Este modo es ANSI compliant

46. Ejemplo de modo chained set chained on -- The server executes an implicit begin tran before -- the next statement.delete salesdetail where stor_id = "5023" and ord_num = "AB-123-DEF-425-1Z3" if @@error <> 0 begin rollback tran return enddelete sales where stor_id = "5023" and ord_num = "AB-123-DEF-425-1Z3" if @@error <> 0 begin rollback tran return endcommit tran

47. Ejemplo begin tran insert sales values ("5023", "AB-123-DEF-425-1Z3", "Oct 31 1985") if @@error <> 0 begin rollback transaction return end insert salesdetail values ("5023", "AB-123-DEF-425-1Z3", "TC4203", 2500, 60.5) if @@error <> 0 begin rollback transaction return end commit transaction

48. Ejemplo begin tranupdate publishers set pub_id = "9999" where pub_id = "9988" /* check for system error or no rows affected */if @@error <> 0 or @@rowcount <> 1 begin rollback tran /* Rollback to begin tran*/ return end

49. Ejemplo update titles set pub_id = "9999" -- cascade change to titles where pub_id = "9988"if @@error <> 0 begin rollback tran /* Rollback both updates*/ return end /* You might not check @@rowcount for the ** update to the titles table because a publisher ** may not have any titles associated with it. ** A message sent by a print or raiserror ** statement may be used to advise the user that ** no rows were modified in titles. */commit tran

50. Ejemplo • Batch que contiene transacción: declare @err int, @rows int begin tran update publishers set pub_id = "x999" where pub_id = "0736" select @err = @@error, @rows = @@rowcount if @err <> 0 begin rollback tran raiserror 31001, "0736" return endif @rows = 0 begin rollback tran raiserror 35001, "publishers" return end