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
• Las funciones se pueden usar en:
– Listas de un select
– Cláusulas where
– En cualquier sitio donde se permita una expresión
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
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)
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
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"
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
Categorías de funciones
•
•
•
•
Date
Mathematical
String
Aggregate
Funciones date
• Las funciones Date son extensiones SQL que
manipulan datos tipo datetime
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
Funciones matemáticas
• Las funciones matemáticas son extensiones SQL
que manipulan datos numéricos
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
Funciones string
• Las funciones de string son extensiones SQL
que manipulan datos de caracteres
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
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
----172-32-1176
213-46-8915
238-95-7766
...
name
---White, Johnson
Green, Marjorie
Carson, Cheryl
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)
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
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, nonNULL value)
• Ejemplo:
select avg(price) from titles
-----14.77
select avg(isnull (price, $0.00)) from titles
-----13.13
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
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
Cuándo usar transacciones?
• Cuando un conjunto de sentencias se deben
comportar como una unidad
Sentencias para transacciones
• Cuatro sentencias definen la estructura de una
transacción
–
–
–
–
begin tran
commit tran
rollback tran
save
begin tran y commit tran
• begin tran
– Inicia la transacción
• commit tran
– Finaliza la transacción
– Todas las modificaciones quedan en firme
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
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
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
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
/* 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]"
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
• 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
save
• save crea un nombre de un punto de grabación
– Es una extensión SQL que permite rollbacks
parciales
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
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 +
where acct_num = "83165-S"
save transaction charge
/* deduct funds from savings */
update accounts
set balance = balance - 100
where acct_num = "83165-S"
$1.50
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
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
• 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
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.
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
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.
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
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
Ejemplo de modo Unchained
set chained off
begin tran
delete salesdetail
where stor_id = "5023"
and ord_num = "AB-123-DEF-425-1Z3"
if @@error <> 0
begin
rollback tran
return
end
delete sales
where stor_id = "5023"
and ord_num = "AB-123-DEF-425-1Z3"
if @@error <> 0
begin
rollback tran
return
end
commit tran
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
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
end
delete sales
where stor_id = "5023"
and ord_num = "AB-123-DEF-425-1Z3"
if @@error <> 0
begin
rollback tran
return
end
commit tran
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
Ejemplo
begin tran
update 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
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
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
end
if @rows = 0
begin
rollback tran
raiserror 35001, "publishers"
return
end
Ejemplo
update titles
-- cascade change to titles
set pub_id = "x999"
where pub_id = "0736"
select @err = @@error
if @err <> 0
begin
rollback tran
raiserror 31002, "0736"
return
end
commit tran
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."
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'.
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)
Necesidad de aislamiento
• En ambientes
multiusuario, las
transacciones
acceden a los datos
simultáneamente
• Datos que no estén
aislados pueden
estar errados
Bloqueo (locking)
• Mecanismo
automático que aisla
los datos para
prevenir conflictos
de los datos que se
están modificando
Estructura interna de una tabla
Alcance de los candados
• El alcance de un candado determina cuántos datos se aislan
• Tres alcances
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
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)
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)
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
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
Resúmen de tipos de candados
S tatem en ts
T h at Im p o se
T h is L o ck
If D ata
A lread y
H as an S
L o ck
P lace
another S
lock
W ait for lock
to be
released
S h ared
(S )
select
E xclu sive
(X )
in sert,
u p d ate*,
d elete*
U p d ate
(U )
u p d ate, d elete P lace the U
lock
If D ata
A lread y
H as an X
L o ck
W ait for
lock to be
released
W ait for
lock to be
released
If D ata
A lread y
H as a U
L o ck
P lace
another S
lock
W ait for
lock to be
released
S co p es fo r
W h ich T h is
L o ck
E xists
R ow , page,
table
W ait for
lock to be
released
W ait for
lock to be
released
P age
R ow , page,
table
*Updates y deletes usan candados exclusive solamente para
encontrar los datos que necesitan modificar
Deadlock
Resolución del deadlock
Esquema de bloqueo
• Esquema de bloqueo es un atributo de la tabla que
determina qué datos asociados con la tabla están
bloqueados
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
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
Bloqueo “datarows”
• Las páginas de índices nunca se bloquean
• El servidor usa candados de tabla, candados de página y
candados de fila
Comparación de esquemas de bloqueo
C an In d ex
P ag es b e
L o cked ?
A llp ag es lo ckin g
sch em e
Y es
D atap ag es
lo ckin g sch em e
D ataro w s lo ckin g
sch em e
No
No
W h at K in d s A vailab le in
o f L o cks A re R eleases
U sed ?
P rio r to A S E
11.9?
T able and
Y es
page
T able and
page
T able, page,
and row
No
No
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)
pub_name varchar(40)
city
varchar(20)
state
char(2)
lock datarows
NOT NULL,
NULL,
NULL,
NULL)
• Si no se especifica un esquema de bloqueo, la tabla usa el
esquema default de bloqueo
Cambiar el esquema de bloqueo
• Sintaxis simplificada:
alter table table_name
lock { allpages | datapages | datarows }
• Ejemplo:
alter table publishers
lock datapages
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
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_scheme
exec sp_help dpl_scheme
• Borrar los objetos de base de datos creados:
drop table dpl_scheme
drop table def_scheme
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
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”
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”
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”
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
D irty R ead s
N o n rep eatab le
R ead s
P h an to m
R ead s
L evel 0
A llow ed
A llow ed
A llow ed
L evel 1
P revented
A llow ed
A llow ed
L evel 2
P revented
P revented
A llow ed
L evel 3
P revented
P revented
P revented
Nivel 1 de aislamiento
D irty reads
prevented
N onrepeatable reads
allow ed
P hantom reads
allow ed
• 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
Nivel 2 de aislamiento
D irty reads
prevented
N onrepeatable reads
prevented
P hantom reads
allow ed
• 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
Nivel 3 de aislamiento
• Nivel 3 – El nivel más restrictivo:
D irty reads
prevented
N onrepeatable reads
prevented
P hantom reads
prevented
• 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
Nivel 0 de aislamiento
• Nivel 0 – El nivel menos restrictivo:
D irty reads
allow ed
N onrepeatable reads
allow ed
P hantom reads
allow ed
• 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
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 }
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"
Cursor
• Un cursor es un mecanismo que sirve para procesar
fila por fila los resultados de una consulta
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
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
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
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
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
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 @title char(80), @title_id char(6)
open biz_book
fetch biz_book into @title, @title_id
while @@sqlstatus = 0
begin
-- process @title and @title_id
fetch biz_book into @title, @title_id
end
close biz_book
deallocate cursor biz_book
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
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 @title char(80), @title_id char(6)
open biz_book
fetch biz_book into @title, @title_id
while @@sqlstatus = 0
begin
-- process @title and @title_id
fetch biz_book into @title, @title_id
end
close biz_book
deallocate cursor biz_book
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
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 @title char(80), @title_id char(6)
open biz_book
fetch biz_book into @title, @title_id
while @@sqlstatus = 0
begin
-- process @title and @title_id
fetch biz_book into @title, @title_id
end
close biz_book
deallocate cursor biz_book
Variables para el manejo de cursores
• Se tiene una variable que retorna el número total
de filas procesadas (@@rowcount)
• Se tiene una variable que indica el estado o
resultado de mover el cursor (@@sqlstatus)
– Exitoso: se alcanzó una fila válida
– Hay un error al tratar de tomar la fila
– Ya se procesaron todas las filas
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
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
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
@title_id
tid,
@type
char(12),
@price
money
open books_csr
-- initial fetch
fetch books_csr into @title_id, @type, @price
Ejemplo de cursor
while @@sqlstatus = 0
begin
if @@sqlstatus = 1
begin
raiserror 30001 "select failed"
close books_csr
deallocate cursor books_csr
return
end
if @type="business"
select @title_id, @type,CONVERT(money,@price*1.08)
else
if @type="mod_cook"
select @title_id, @type, @price
-- subsequent fetches within loop
fetch books_csr into @title_id, @type, @price
end
Ejemplo de cursor
close books_csr
deallocate cursor books_csr
go
• Results:
BU1032
BU1111
BU2075
BU7832
MC2222
MC3021
business
business
business
business
mod_cook
mod_cook
-----------------------21.59
-----------------------12.91
-----------------------3.23
-----------------------21.59
-----------------------19.99
-----------------------2.99
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"
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 @fname varchar(20), @lname varchar(40),
@title varchar(80), @au_id char(11),
@old_au_id char(11)
open title_author_csr
fetch title_author_csr into @au_id, @fname, @lname,
@title
Ejemplo de cursor
while @@sqlstatus = 0
begin
if @@sqlstatus = 1
begin
raiserror 23000 "Select failed."
return
end
if @au_id <> @old_au_id
begin
print "
"
print "%1! %2! is the author of these books:",
@fname, @lname
end
print "
%1!", @title
select @old_au_id = @au_id
fetch title_author_csr into @au_id, @fname, @lname,
@title
end
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 Executive’s Database Guide
Burt Gringlesby is the author of these books:
Sushi, Anyone?
...
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 @@rowcount
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
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 Executive’s 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
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
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
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
@avg_price money,
-- local variables
@title_id tid,
@price
money
open title_update
-- execute cursor
begin tran
-- calculate average price
select @avg_price = avg(price) from titles holdlock
fetch title_update into @title_id, @price
Ejemplo de cursor
while @@sqlstatus = 0
begin
if @@sqlstatus = 1
-- error occurred
begin
rollback tran
raiserror 21001 "Fetch failed in cursor"
close title_update
deallocate cursor title_update
return
end
if @price < @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
Ejemplo de cursor
if @@error <> 0
begin
rollback tran
raiserror 22001 "Update failed"
close title_update
deallocate cursor title_update
return
end
fetch title_update into @title_id, @price
end
commit tran
close title_update
deallocate cursor title_update
go
...
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 }
Cursor a nivel de servidor
• Un cursor a nivel de servidor es aquel creado en un
stored procedure
• Ejemplo:
create proc proc_fetch_book
as
declare
@title
char(30),
@title_id
char(6)
declare biz_book cursor
for select title, title_id from titles
where type = "business"
for read only
open biz_book
fetch biz_book into @title, @title_id
-- additional processing here
close biz_book
deallocate cursor biz_book
return
Alcance de cursores a nivel servidor
• Los “stored procedures” pueden tomar datos de cursores
creados por un procedimiento que llama al procedimiento
dado
Descargar

Conceptos de Bases de Datos Relacionales