1 / 120

Conceptos de Bases de Datos Relacionales Parte 2

Conceptos de Bases de Datos Relacionales Parte 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. Uso de funciones.

cole-cross
Download Presentation

Conceptos de Bases de Datos Relacionales Parte 2

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. 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

  20. 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

  21. 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

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

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

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

  25. 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

  26. 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

  27. 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

  28. 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

  29. /* 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]"

  30. 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

  31. 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

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

  33. 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

  34. 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"

  35. 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

  36. 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

  37. 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

  38. 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.

  39. 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

  40. 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.

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

  46. 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

  47. 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

  48. 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

  49. 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

More Related