Bases de Datos Relacionales

1. Bases de Datos Relacionales BATCHS Preparó: Ismael Castañeda Fuentes Fuentes: Manuales Sybase Manuales SQL Server Manuales Oracle

2. Ambiente de ejecución • El código se puede ejecutar en: • Cliente • Servidor • Ventajas de programar en el lado cliente • Atiende usuarios finales con diferentes necesidades • Evita tráfico en la red • Se distribuye una parte del procesamiento • Puede facilitar la interacción del usuario • Ventajas de programar en el lado servidor • Se tiene concentración de los recursos • Uniformisa la aplicación de las reglas del negocio • Se puede actualizar más fácilmente el código

3. Batch • Una o más sentencias enviadas y ejecutadas como una sola • Ejemplo: delete sales where stor_id = "5023" and ord_num = "AB-123-DEF-425-1Z3"delete salesdetail where stor_id = "5023" and ord_num = "AB-123-DEF-425-1Z3"select * from sales where stor_id = "5023"select * from salesdetail where stor_id = "5023" go

4. Restriciones de los batch • Esta sentencias deben tener su propio batch: • create default • create rule • create procedure • create trigger • declare cursor • No se puede borrar y recrear un objeto en el mismo batch • sentencia use • No se puede asignar una regla o default a una columna e insertarle valores en el mismo batch

5. Comentarios • Porción de código ignorado por el servidor • Usados para documentar • Dos formas: • -- comentario hasta el final de una línea • /* comentario multilínea */

6. Variables locales • Variable local: nombre de una posición de memoria para almacenar un valor • Uso típico de las variables locales • Para facilitar el uso repetido de valores constantes • Para ejecutar bifurcamiento en código SQL • Para capturar mensajes que contienen información variable • Para intercambio de información con stored procedures • Para evitar el uso de subquerys

7. Variables locales - Asignación con select y expresiones • Sintaxis simplificada: selectvariable_name = expression [, variable_name = expression ...] • Ejemplos: declare @number int, @copy int, @sum int select @number = 10 select @copy = @number, @sum = @number + 100 • Si select no retorna valores, la variable no cambia su valor

8. Variables locales - Asignación con select y valores de tabla • Sintaxis simplificada: select variable_name = column_name from table_name [where condition] • Ejemplos: declare @AD_id char(11) select @AD_id = au_id from authors where au_fname = "Ann" and au_lname = "Dull" • Si el select retorna múltiples valores, solamente toma el último

9. Variables locales - Asignación con update • Sintaxis simplificada: updatetable_name set {column_name | variable_name } = expression [, {column_name | variable_name } = expression ... ] [where condition] • Ejemplos: declare @pub_name varchar(40) update publishers set city = "Escanaba", state = "MI", @pub_name = pub_name where pub_id = "0736" • Si update modifica múltiples filas, solamente el último asignado permanece en la variable

10. Variables locales – Declaración - Ejemplo • Las variables se deben declarar antes de usarlas • Ejemplo: select pub_id from publishers select @myvar = total_sales from titles where title_id = "BU2075" select @myvar Server Message: Number 137, Severity 15 Line 3: Must declare variable '@myvar'.

11. Variables locales – Concordancia - Ejemplo • Los valores de las variables deben de concordar con el tipo de dato de la variable • Ejemplo: declare @myvariable int select @myvariable = title from titles where title_id = "BU2075"select @myvariableServer Message: Number 257, Severity 16 Line 2: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.

12. Variables locales – Retorno de un select - Ejemplo • Si el select no retorna valores, la variable no cambia • Ejemplo: declare @value int select @value = total_sales from titles where title_id = "BU2075"select @value as "BU2075 sales"select @value = total_sales from titles where title_id = "BU11" -- should be "BU1111"select @value as "BU1111 sales" Primer query: Segundo query: BU2075 sales BU1111 sales ------------ ------------ 18722 18722

13. Variables locales - Alcance -Ejemplo • Las variables locales se borran al terminar el batch que las crea -- Declare a local variable: declare @pub_var char(4) -- Assign the variable a value from a table: select @pub_var = pub_id from pubs2..publishers where pub_name = "New Age Books" -- View the variable’s value: select @pub_var --- Use the variable in a select statement: select title_id, title, pub_id from pubs2..titles where pub_id = @pub_var -- Execute the batch. (Users of SQL Advantage do -- not need to enter "go" to complete this step): go

14. Variables globales • Variable global: nombre de una posición de memoria para almacenar un valor definido y mantenido por el servidor

15. Variables globales - Ejemplo • Ejemplo_1: delete from titles where type = "popular_comp" select @@rowcount ------ 3 • Ejemplo_2: delete from titles where type = "popular_comp" select @@error ------ 0

16. Variables globales – Ejemplo con @@error • Ejemplo: -- The word "from" is misplaced delete titles from where type = "psychology" Server Message: Number 156, Severity 15 Line 4: Incorrect syntax near the keyword 'where'. select @@error ------ 156

17. Variables globales – Ejemplo con @@error • Crear una tabla: select * into mytitles from pubs2..titles • Intentar borrar una tabla inexistente @@error: delete from no_table select @@error • Ejecutar un delete con una condición que nunca se cumple: delete from mytitles where 1 = 2 select @@error • ¿El segundo delete generate un error? ¿Por qué o por qué no? • Borrar los objetos de bases de datos creados: drop table mytitles

18. Sentencias de control de flujo • Por default los servidores ejecutan las sentencias en el orden en que ellas aparecen • Las siguientes son sentencias de control de flujo: • if...else • begin...end • if exists • while • break • continue • return

19. if...else • Sintaxis simplificada: if condition block_to_execute_when_condition_is_true [ else block_to_execute_when_condition_is_false ] • Ejemplo: delete from titles where type = "mod_cook" if @@error <> 0 select "An error has occurred!" else select "The delete was successful."

20. Condiciones anidadas • Ejemplo: delete from titles where type = "mod_cook" if @@rowcount = 0 select "No rows were deleted." else if @@rowcount = 1 select "One row was deleted." else select "Multiple rows were deleted."

21. begin...end • Sintaxis: begin statement statement ... end • Ejemplo: ... if @temp_id = "TC4203" begin select @temp_type = "trad_cook" delete titles where type = "trad_cook" end else begin select "Title has already been deleted" end

22. if exists • Sintaxis simplificada: if [ not ] exists ( select_statement ) code_to_execute_when_condition_is_true [ else code_to_execute_when_condition_is_false ] • Ejemplo_1: if exists (select * from titles where price > $50.00) update titles set price = price * $0.90 else select "All titles priced under $50.00“ • Ejemplo_2: if exists (select * from sysobjects where name = "vw_utah_authors" and type = "V") drop view vw_utah_authors go create view vw_utah_authors as select * from authors where state = "UT" with check option

23. Sentencias if • Ejemplo: if (select avg(price) from pubs2..titles) > $25 select "These books are expensive." else select "These books are a bargain." • Ejemplo : drop table mysales • Ejemplo : if exists (select * from sysobjects where name = "mysales“ and type = "U") drop table mysales

24. while • Sintaxis: while condition block_to_execute • Ejemplo_1: while (select avg(price) from titles) < $40 begin update titles set price = price + $2 end • Ejemplo_2: declare @price money select @price = price from titles where title_id = "PS1372" while @price < $30 begin update titles set price = price * $1.10 where title_id = "PS1372" end -- The loop updates the price of PS1372, but -- never updates the value of @price. Because -- the loop condition is based on @price, -- execution of the loop would never terminate.

25. break • Ejemplo: -- While the average price is greater than -- $20, this loop cuts all prices in half. -- However, if the maximum price falls below -- $40, the loop is immediately terminated. while (select avg(price) from titles) > $20 begin update titles set price = price / 2 if (select max(price) from titles) < $40 break end

26. continue • Ejemplo: -- While @price is less than $20, this loop adds -- $1 to @price. If there are 5 or more titles -- at @price, it restarts the loop. Otherwise, -- it increases all books priced at @price by 10%. ... while @price < $20.00 begin select @price = @price + $1.00 if (select count(price) from titles where price = @price) >= 5 continue else update titles set price = price * $1.10 where price = @price end

27. return • Ejemplo: -- @avg_price is declared and set to the average -- price in titles. If the average is less than -- $10, execution exits the entire batch. -- Otherwise, it continues execution. declare @avg_price money select @avg_price = avg(price) from titles if @avg_price < $10 return while @avg_price < $20 begin update titles set price = price * $0.95 select @avg_price = avg(price) from titles end

28. Ciclos while • Ejemplo: declare @x money select @x = $0 while @x < (select max(price) from pubs2..titles) begin select @x = @x + $1 select "Titles less than $", @x select price, title from pubs2..titles where price < @x end

29. select • Sintaxis simplificada: select { "user_message" | variable_name | column_list } • Ejemplo: select "The average price is too low." select "The average price is ", @avg_price select "The average price is ", avg(price) from titles • Puede retornar texto, valores variables, y datos de tabla en cualquier combinación