Conceptos de Bases de Datos Relacionales Parte 2

1
Conceptos de Bases de Datos Relacionales Parte 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
• select function_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
• select column_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 1200 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 1200 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("[phone]",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 gt (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
(No Transcript)
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
• -- _at_amount is a monetary amount to be
  transferred.
• -- _at_from_account is the account to be debited.
• -- _at_to_account is the account to be credited.
• begin tran
• update accounts
• set balance balance - _at_amount
• where account _at_from_account
• update accounts
• set balance balance _at_amount
• where account _at_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 _at_from_account is below 0, abort the
  transfer
• begin tran
• update accounts
• set balance balance - _at_amount
• where account _at_from_account
• update accounts
• set balance balance _at_amount
• where account _at_to_account
• if (select balance from accounts
• where account _at_from_account) lt 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 _at__at_error ltgt 0 or _at__at_rowcount ltgt 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 _at__at_error ltgt 0 or
_at__at_rowcount ltgt 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 _at__at_error ltgt 0 or _at__at_rowcount ltgt 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 _at__at_error
ltgt 0 or _at__at_rowcount ltgt 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 _at__at_error ltgt 0 begin rollback
  tran return enddelete sales where stor_id
  "5023" and ord_num "AB-123-DEF-425-1Z3"
• if _at__at_error ltgt 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 _at__at_error ltgt 0 begin rollback
  tran return enddelete sales where stor_id
  "5023" and ord_num "AB-123-DEF-425-1Z3"
• if _at__at_error ltgt 0 begin rollback
  tran return endcommit tran

47
Ejemplo

• begin tran
• insert sales values
• ("5023", "AB-123-DEF-425-1Z3", "Oct 31 1985")
• if _at__at_error ltgt 0
• begin
• rollback transaction
• return
• end
• insert salesdetail values
• ("5023", "AB-123-DEF-425-1Z3", "TC4203",
• 2500, 60.5)
• if _at__at_error ltgt 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 _at__at_error ltgt 0 or _at__at_rowcount ltgt 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
_at__at_error ltgt 0 begin rollback tran /
Rollback both updates/ return end / You
might not check _at__at_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 _at_err int, _at_rows int
• begin tran
• update publishers set pub_id "x999" where
  pub_id "0736"
• select _at_err _at__at_error, _at_rows _at__at_rowcount
• if _at_err ltgt 0
• begin
• rollback tran raiserror 31001,
  "0736" return
• endif _at_rows 0 begin rollback tran
• raiserror 35001, "publishers"
• return
• end

51
Ejemplo
update titles -- cascade change to titles set
pub_id "x999" where pub_id "0736"select
_at_err _at__at_errorif _at_err ltgt 0 begin rollback
tran raiserror 31002, "0736" return
end commit tran
52
Ejemplo

• Messages añadidos a sysusermessages
• sp_addmessage 31001, "Transaction aborted. Error
  occurred while updating publishers table with
  publisher id 1!."
• exec sp_addmessage 31002, "Transaction aborted.
  Error occurred while updating titles table for
  titles with publisher id 1!."
• exec sp_addmessage 35001, "Transaction aborted.
  No rows encountered for the search condition
  while updating the 1! table."

53
Ejemplo

• Salida adicional retornada como resultado de la
  sentencia raiserror (del ejemplo de la página
  anterior)
• Server Message Number 31001, Severity 16
• Line 10
• Transaction aborted. Error occurred while
  updating
• publishers table with publisher id '0736'.

54
Ejemplo

• Salida generada por el sistema (del ejemplo de la
  página anterior)
• Server Message Number 3621, Severity 10
• Line 3
• Command has been aborted.
• Server Message Number 552, Severity 16
• Line 3
• A column insert or update conflicts with a rule
  bound to
• the column. The command is aborted. The conflict
  occured
• in database 'user1db', table 'publishers', rule
• 'pub_idrule', column 'pub_id'.
• (1 row affected)

55
(No Transcript)
56
Necesidad de aislamiento

• En ambientes multiusuario, las transacciones
  acceden a los datos simultáneamente
• Datos que no estén aislados pueden estar errados

57
Bloqueo (locking)

• Mecanismo automático que aisla los datos para
  prevenir conflictos de los datos que se están
  modificando

58
Estructura interna de una tabla
59
Alcance de los candados

• El alcance de un candado determina cuántos datos
  se aislan
• Tres alcances

60
Tipos de candados

• El tipo de candado determina la extensión del
  aislamiento de datos de otras transacciones
• Tres tipos de candados
• Shared
• Exclusive
• Update

61
Candados Shared

• Usado por sentencias que leen datos (selects)
• Otros procesos pueden leer los datos (coloca
  candado shared), pero ningún proceso puede
  cambiar los datos (coloca candado exclusive)

62
Candados exclusive

• Usado por sentencias que cambian datos (inserts,
  updates, deletes)
• Ningún otro proceso puede leer los datos (coloca
  candado shared) o cambiar los datos (coloca
  candado exclusive sobre la página)

63
Candados shared y exclusive

• Nota Se intenta involucrar dos tablas un select
  para
• publishers y un delete para authors
• Nota Se usa la opción holdlock, la cual asegura
  que los candados shared no se liberan hasta
  cuando concluya la transacción.
• El instructor tipea
• begin tran
• select from pubs2..publishers
• holdlock
• delete from pubs2..authors
• Espera ver los datos de publishers
• select from pubs2..publishers

64
Candados update

• Usado por operaciones que pueden o no cambiar los
  datos (updates, deletes)
• Cuando el proceso primero escanea los datos, le
  aplica un candado update. Otros procesos pueden
  colocar candados shared, pero ningún proceso
  puede colocar candados exclusive o update

65
Resúmen de tipos de candados

• Updates y deletes usan candados exclusive
  solamente para encontrar los datos que necesitan
  modificar

66
Deadlock
67
Resolución del deadlock
68
Esquema de bloqueo

• Esquema de bloqueo es un atributo de la tabla que
  determina qué datos asociados con la tabla están
  bloqueados

69
bloqueo allpages

• Se pueden bloquear las páginas de índices
• El servidor usa candados de tabla y candados de
  página, pero no candados de fila

70
Bloqueo datapages

• Las páginas de índices nunca se bloquean
• El servidor usa candados de tabla y candados de
  página, pero no candados de fila

71
Bloqueo datarows

• Las páginas de índices nunca se bloquean
• El servidor usa candados de tabla, candados de
  página y candados de fila

72
Comparación de esquemas de bloqueo
73
Fijar el esquema bloqueo

• Sintaxis simplificada
• create table table_name (
• column_name datatype NULL NOT NULL
  IDENTITY ,
• ...
• column_name datatype NULL NOT NULL
  IDENTITY )
• lock allpages datapages datarows
• Ejemplo
• create table publishers (pub_id char(4) NOT
  NULL,pub_name varchar(40) NULL,city varchar(2
  0) NULL,state char(2) NULL)lock datarows
• Si no se especifica un esquema de bloqueo, la
  tabla usa el esquema default de bloqueo

74
Cambiar el esquema de bloqueo

• Sintaxis simplificada
• alter table table_name
• lock allpages datapages datarows
• Ejemplo
• alter table publishers
• lock datapages

75
Ejemplo

• Ver el default del esquema de bloqueo actual
• sp_configure "lock scheme"
• Crear una tabla con el esquema default de
  bloqueo
• create table def_scheme (a int)
• Crear una tabla con un esquema de bloqueo
  especifico
• create table dpl_scheme (a int) lock datapages
• Ver el esquema de bloqueo ambas tablas
• sp_help def_scheme
• exec sp_help dpl_scheme

76
Ejemplo

• Cambiar el esquema de bloqueo de la primera
  tabla
• alter table def_scheme lock datarows
• Ver el esquema de bloqueo ambas tablas
• sp_help def_schemeexec sp_help dpl_scheme
• Borrar los objetos de base de datos creados
• drop table dpl_schemedrop table def_scheme

77
Leer datos no aislados

• Hay tres tipos de consultas o reads, que pueden
  retornar datos que son inadecuados para limitar
  el aislamiento de datos
• Las características de cómo se hacen estos
  reads son propios de cada DBMS
• Hay tres tipo de reads
• Dirty reads
• Nonrepeatable reads
• Phantom reads

78
Lectura sucia

• La transacción 1 modifica datos
• La transacción 2 lee los datos modificados antes
  de que la modificación haya terminado
• Esta transacción lee datos uncommitted o dirty

79
Lectura no repetible

• La transacción 1 lee datos
• La transacción 2 modifica esos datos antes de que
  la primera transacción haya terminado
• La primera lectura es ahora nonrepeatable

80
Lectura fantasma

• La transacción 1 lee un conjunto de filas que
  cumplen una condición
• La transacción 2 modifica los datos de algunas
  columnas que no cumplían esa condición y ahora la
  cumplen, o al contrario
• Las filas que aparecen y desaparecen se denominan
  phantoms

81
Nivel de aislamiento

• Un nivel de aislamiento es un conjunto de
  candados que permiten o no una combinación
  particular de los tres tipos de lectura sucia,
  no repetible o con fantasmas
• ANSI define cuatro niveles de aislamiento, cada
  uno más restrictivo que el anterior

82
Nivel 1 de aislamiento

• Nivel 1 - Comportamiento de select
• Se fijan candados Shared hasta que el select
  termine la lectura de una fila o página
• select espera a que se liberen los candados
  exclusive

83
Nivel 2 de aislamiento

• Nivel 2 - Comportamiento de select
• Se fijan candados Shared hasta que termine la
  transacción
• Este comportamiento es diferente al del nivel 1
• select espera a que se liberen los candados
  exclusive
• Comportamiento discreto de nivel 2 requiere
  bloqueo row-level
• Tables APL y tables DPL no tienen bloqueo
  row-level
• Si una consulta con nivel de aislamiento 2 lee
  una tabla APL o DPL, se forza comportamiento de
  aislamiento nivel 3

84
Nivel 3 de aislamiento

• Nivel 3 El nivel más restrictivo
• Nivel 3 - Comportamiento de select
• Se fijan candados shared hasta que termine la
  transacción
• Este comportamiento es diferente al del nivel 1
• select espera a que se liberen los candados
  exclusive

85
Nivel 0 de aislamiento

• Nivel 0 El nivel menos restrictivo
• Nivel 0 - Comportamiento de select
• Se fijan candados Shared hastaque select termine
  la lectura de una fila o página
• select ignora los candados exclusive
• Este comportamiento es diferente al del nivel 1

86
Fijar nivel de aislamiento

• Sintaxis para aislamiento a nivel de sesión
• set transaction isolation level 0 read
  uncommitted
• 1 read committed 2 repeatable read
• 3 serializable
• Sintaxis para aislamiento a nivel de sentencia
• select ...
• at isolation
• 0 read uncommitted 1 read committed
• 2 read repeatable
• 3 serializable

87
holdlock y noholdlock

• holdlock forza nivel de ailamiento 3, sin
  importar el nivel de aislamiento actual
• Para select se fijan candados shared hasta que
  termine la transacción
• noholdlock forza nivel de ailamiento 1, sin
  importar el nivel de aislamiento actual
• Para select se liberan los candados shared cuando
  se ha leido una fila o página
• Sintaxis simplificada
• select column_list
• from table_list holdlock noholdlock
• Ejemplo
• select title
• from titles holdlock
• where pub_id "0877"

88
(No Transcript)
89
Cursor

• Un cursor es un mecanismo que sirve para procesar
  fila por fila los resultados de una consulta

90
Beneficios de los cursores

• Se pueden procesar los datos fila por fila
• SQL es un lenguaje orientado a conjuntos
• El procesamiento se hace normalmente sobre las
  filas que cumplan con una condición dada
• Los cursors permiten el procesamiento fila por
  fila
• Se pueden modificar los datos fila por fila
• Se puede sortear la brecha existente entre la
  orientación a conjuntos de las bases de datos
  relacionales y la orientación a filas de muchos
  lenguajes de programación

91
Ciclo de vida de un cursor

• 1. Declarar el cursor
• 2. Abrir el cursor
• 3. Tomar cada fila
• 4. Cerrar el cursor
• 5. Desasignar el cursor

92
Paso 1 Declarar el cursor

• Cuando se declara un cursor
• Se especifica una consulta
• Se especifica un modo para el cursor
• De solo lectura
• Para actualización

93
Sintaxis para declarar un cursor

• Sintaxis simplificada
• declare cursor_name cursor
• for select_statement
• for read only update of
  column_name_list
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go

94
Paso 2 Abrir el cursor

• Cuando se abre el cursor
• El servidor crea el conjunto resultado
• El apuntador está señalando antes de la primera
  fila del conjunto respuesta

95
Sintaxis para la apertura de un cursor

• Sintaxis
• open cursor_name
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go
• declare _at_title char(80), _at_title_id char(6)
• open biz_book
• fetch biz_book into _at_title, _at_title_id
• while _at__at_sqlstatus 0
• begin
• -- process _at_title and _at_title_id fetch
  biz_book into _at_title, _at_title_id
• end
• close biz_book
• deallocate cursor biz_book

96
Paso 3 Tomar cada fila

• Cuando se ejecuta un fetch
• El cursor señala a la siguiente fila válida
• Retorna la siguiente fila válida

97
Sintaxis de un fetch

• Sintaxis
• fetch cursor_name into fetch_target_list
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go
• declare _at_title char(80), _at_title_id char(6)
• open biz_book
• fetch biz_book into _at_title, _at_title_id
• while _at__at_sqlstatus 0
• begin
• -- process _at_title and _at_title_id fetch
  biz_book into _at_title, _at_title_id
• end
• close biz_book
• deallocate cursor biz_book

98
Pasos 4 y 5 Cerrar y desasignar el Cursor

• Cuando se cierra un cursor
• Termina el procesamiento de la consulta hecha
• Cuando se desasigna el cursor
• Se liberan todos los recursos de memoria
  asignados al cursor

99
Cerrar y desasignar un Cursor

• Sintaxis
• close cursor_name
• deallocate cursor cursor_name
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go
• declare _at_title char(80), _at_title_id char(6)
• open biz_book
• fetch biz_book into _at_title, _at_title_id
• while _at__at_sqlstatus 0
• begin
• -- process _at_title and _at_title_id fetch
  biz_book into _at_title, _at_title_id
• end
• close biz_book
• deallocate cursor biz_book

100
Variables para el manejo de cursores

• Se tiene una variable que retorna el número total
  de filas procesadas (_at__at_rowcount)
• Se tiene una variable que indica el estado o
  resultado de mover el cursor (_at__at_sqlstatus)
• Exitoso se alcanzó una fila válida
• Hay un error al tratar de tomar la fila
• Ya se procesaron todas las filas

101
Notas adicionales para fetch

• fetch siempre mueve el apuntador a la siguiente
  fila válida en el conjunto respuesta
• Algunos servidores permiten regresarse a una fila
  anterior
• Cerrar y reabrir un cursor hace que el apuntador
  siempre señale al comienzo
• Por default, fetch siempre retorna una fila
• Algunos servidores permiten cambiar este defaullt
• Sintaxis
• set cursor rows number for cursor_name
• Ejemplo
• set cursor rows 5 for biz_book

102
Prácticas recomendadas para desarrollo

• Siempre especificar el modo del cursor en la
  sentencia declare
• Como los cursores pueden demandar muchos
  recursos, evitar dejar abiertos los cursores por
  mucho
• Si se ejecuta la misma operación en cada fila del
  cursor, hay que buscar una alternativa

103
Ejemplo de cursor

• declare books_csr cursor for
• select title_id, type, price
• from titles
• for read only
• go
• -- List all business and mod_cook books. Show
  business books
• -- at 8 increase in price. This cursor allows
  you to
• -- selectively manipulate a subset of the rows
  while
• -- retaining a single result set.
• declare _at_title_id tid, _at_type char(12), _at_pr
  ice money
• open books_csr
• -- initial fetch
• fetch books_csr into _at_title_id, _at_type, _at_price

104
Ejemplo de cursor
while _at__at_sqlstatus 0 begin if _at__at_sqlstatus
1 begin raiserror 30001 "select
failed" close books_csr deallocate cursor
books_csr return end if
_at_type"business" select _at_title_id,
_at_type,CONVERT(money,_at_price1.08) else if
_at_type"mod_cook" select _at_title_id, _at_type,
_at_price -- subsequent fetches within
loop fetch books_csr into _at_title_id, _at_type,
_at_price end
105
Ejemplo de cursor

• close books_csr
• deallocate cursor books_csr
• go
• Results
• - - ------------------------
• BU1032 business 21.59
• - - ------------------------
• BU1111 business 12.91
• - - ------------------------
• BU2075 business 3.23
• - - ------------------------
• BU7832 business 21.59
• - - ------------------------
• MC2222 mod_cook 19.99
• - - ------------------------
• MC3021 mod_cook 2.99

106
Alternativas al uso de cursores

• Los cursores no son la única manera de ejecutar
  una tarea
• Alternativa usar case
• select title_id, type,
• case type
• when "business" then price 1.08
• when "mod_cook" then price
• end
• from titles
• where type in ("business", "mod_cook")
• Alternativa hacer dos consultas
• select title_id, type, price 1.08
• from titles
• where type "business"
• select title_id, type, price
• from titles
• where type "mod_cook"

107
Ejemplo de cursor

• declare title_author_csr cursor for
• select authors.au_id, au_fname, au_lname, title
• from titles, authors, titleauthor
• where titles.title_id titleauthor.title_id
• and authors.au_id titleauthor.au_id
• order by upper(au_lname), upper(au_fname)
• for read only
• go
• set nocount on --Turns off display of rows
  affected
• declare _at_fname varchar(20), _at_lname varchar(40),
•         _at_title varchar(80), _at_au_id char(11),
•         _at_old_au_id char(11)
• open title_author_csr
• fetch title_author_csr into _at_au_id, _at_fname,
  _at_lname, _at_title

108
Ejemplo de cursor
while _at__at_sqlstatus 0   begin      if _at__at_sqlstatus
1        begin          raiserror 23000
"Select failed."          return        end      i
f _at_au_id ltgt _at_old_au_id        begin          print
"   "          print "1! 2! is the author of
these books", _at_fname, _at_lname        end      
print "    1!", _at_title      select _at_old_au_id
_at_au_id      fetch title_author_csr into _at_au_id,
_at_fname, _at_lname, _at_title   end
109
Ejemplo de cursor

• close title_author_csr
• deallocate cursor title_author_csr
• set nocount off --Turns back on display of rows
  affected
• go
• Resultados
• ...
• Ann Dull is the author of these books
•        Secrets of Silicon Valley
• Marjorie Green is the author of these books
•        You Can Combat Computer Stress!
•        The Busy Executives Database Guide
• Burt Gringlesby is the author of these books
•        Sushi, Anyone?
• ...

110
Ejercicio con cursores

• Declarar un cursor
• declare ca_authors cursor
• for select au_lname, au_fname, state
• from pubs2..authors
• where state "CA"
• for read only
• Abrir el cursor
• open ca_authors
• Tomar tres filas y mostrarlas
• fetch ca_authors
• fetch ca_authors
• fetch ca_authors
• select _at__at_rowcount

111
Ejercicio con cursores

• Cerrar el cursor
• close ca_authors
• Cómo se pueden tomar más de una fila del
  cursor?_____________________________________
• Desasignar el cursor
• deallocate cursor ca_authors

112
Actualizar datos usando cursores

• Sintaxis simplificada
• update table_name
• set column1 expression select_statement
• , column2 expression select_statement
  ...
• where current of cursor_name
• Ejemplo
• update titles
• set title "The Executives Database Guide"
• where current of biz_book
• Actualiza la fila a la que señala el apuntador
• En la mayoría de casos, esta fila es la tomada
  más recientemente
• NO mueve el cursor a la siguiente fila
• Sólo se pueden actualiza cursores declarados en
  modo update

113
Borrar datos usando cursores

• Sintaxis simplificada
• delete from table_name where current of
  cursor_name
• Ejemplo
• delete from titles
• where current of biz_book
• Borra la fila que está siendo señalada por el
  apuntador
• En la mayoría de casos, esta fila es la tomada
  más recientemente
• Mueve el aputador del cursor a la fila siguiente
• Sólo se pueden actualiza cursores declarados en
  modo update

114
Reglas para actualizar cursores

• La tabla sobre la cual el cursor va a actuar debe
  estar declarada
• Con un índice único
• o
• Usando un esquema de bloqueo tipo Datapages o
  Datarows

115
Ejemplo de cursor

• -- Increase all prices less than the average
  price by 50
• -- Decrease all prices greater than or equal to
  the average
• -- price by 25
• declare title_update cursor
• for select title_id, price from titles
• for update
• declare _at_avg_price money, -- local
  variables _at_title_id  tid, _at_price
      money
• open title_update -- execute cursor
• begin tran
• -- calculate average price
• select _at_avg_price avg(price) from titles
  holdlock
• fetch title_update into _at_title_id, _at_price

116
Ejemplo de cursor
while _at__at_sqlstatus 0 begin if _at__at_sqlstatus
1 -- error occurred begin rollback
tran raiserror 21001 "Fetch failed in
cursor" close title_update deallocate
cursor title_update return end if _at_price lt
_at_avg_price update titles --increase by
50 set price price 1.50 where
current of title_update else update
titles -- decrease by 25 set price price
.75 where current of title_update
117
Ejemplo de cursor
if _at__at_error ltgt 0 begin rollback
tran raiserror 22001 "Update failed" close
title_update deallocate cursor
title_update return end fetch title_update
into _at_title_id, _at_price end commit tran close
title_update deallocate cursor title_update go ...

118
Cursores y transacciones

• Para cursores for update obtener bloqueos update
• Los bloqueos se promueven a bloqueos exclusivos
  cuando se ejecuta un update where current of o
  delete where current of
• Si no se promueve, el bloqueo update se libera
  cuando el cursor se mueve a la siguiente página
  de datos
• close on endtran es una opción que determina qué
  le pasa al cursor en una transacción cuando se
  llega a un rollback o commit
• Cuando está activo, el cursor se cierra después
  de un rollback o commit
• Cuando no está activo
• El cursor permanece abierto después de un
  rollback o commit
• Las modificaciones basadas en la posición de un
  cursor se pueden ejecutar fila por fila, lo cual
  puede incrementar la concurrencia
• Sintaxis
• set close on endtran on off

119
Cursor a nivel de servidor

• Un cursor a nivel de servidor es aquel creado en
  un stored procedure
• Ejemplo
• create proc proc_fetch_bookasdeclare _at_title
  char(30), _at_title_id char(6)declare
  biz_book cursor for select title, title_id from
  titles where type "business"
• for read onlyopen biz_bookfetch biz_book into
  _at_title, _at_title_id-- additional processing
  hereclose biz_bookdeallocate cursor
  biz_bookreturn

120
Alcance de cursores a nivel servidor

• Los stored procedures pueden tomar datos de
  cursores creados por un procedimiento que llama
  al procedimiento dado