Unidad 3
• De acuerdo a lo visto, cómo es la
organización o estructura de un programa
en el servidor de BD?
• …
• Organización de programa en bloques.
• CREATE…
•
•
•
•
•
AS
DECLARE…
…
Sentencias SQL
…
• Manejo de excepciones: control de errores.
• PL/SQL usa bloque de exceptions.
DECLARE
....
excep EXCEPTION; /* (2) */
BEGIN
...
IF ... THEN
RAISE excep; /* (2) */
END IF;
...
EXCEPTION
WHEN TOO_MANY_ROWS THEN /* (1) */
sentencias_manejo_excepcion_sistema;
WHEN excep THEN /* (2) */
sentencias_manejo_excepcion_usuario;
WHEN OTHERS THEN ...;
;
END;
• SQL Server 2005 posee un mecanismo de
manejo de excepciones en un formato de
bloque TRY/CATCH:
BEGIN TRY
INSERT INTO Sales.Currency (CurrencyCode, Name, ModifiedDate )
VALUES('LAE','Pol','01/06/2005')
PRINT 'Inserción completada con exito.'
END TRY
BEGIN CATCH
PRINT 'Inserción fallida.'
END CATCH
BEGIN TRY
DECLARE @divisor int , @dividendo int, @resultado int
SET @dividendo = 100
SET @divisor = 0
-- Esta linea provoca un error de division por 0
SET @resultado = @[email protected]
PRINT 'No hay error‘
END TRY
BEGIN CATCH
PRINT 'Se ha producido un error‘
END CATCH
• SQL Server 2000 no maneja el
TRY/CATCH.
• Qué se ha usado para controlar errores?
• IF ELSE…PRINT…RETURN
• @@ERROR. Variable global de sistema. Almacena el
número de error producido por la última sentencia
Transact SQL ejecutada.
• Devuelve 0 si la última instrucción Transact-SQL se
ejecutó con éxito.
• Si la instrucción causó un error, devuelve el número de
error.
• El valor de @@ERROR cambia al finalizar cada
instrucción Transact-SQL.
• Qué se aconseja?
• Guardar @@ERROR en una variable de tipo entero
inmediatamente después de que se complete la
instrucción Transact-SQL. El valor de la variable se
puede usar posteriormente.
DECLARE @divisor int , @dividendo int ,
@resultado int
SET @dividendo = 100
SET @divisor = 0
/*se genera una division por 0->error*/
SET @resultado = @[email protected]
IF @@ERROR = 0
PRINT 'No hay error'
ELSE
PRINT 'Error de division'
DECLARE @divisor int , @dividendo int ,
@resultado int
SET @dividendo = 100
SET @divisor = 0
/*se genera una division por 0->error*/
SET @resultado = @[email protected]
/*esta linea vuelve a establecer @@error a 0*/
PRINT 'Controlando el error ...'
IF @@ERROR = 0
PRINT 'No hay error'
ELSE
PRINT 'Error de division'
DECLARE @divisor int,@dividendo int ,
@resultado int
declare @nu int
SET @dividendo = 100
SET @divisor = 0
SET @resultado = @[email protected]
set @nu=@@error
print 'Controlando el error...'
IF @nu = 0
PRINT 'No hay error'
ELSE
PRINT 'Hay error: ' +cast(@nu as varchar)
• En ocasiones es necesario provocar
voluntariamente un error; nos puede interesar
que se genere un error cuando los datos
incumplen una regla de negocio.
• Se puede provocar un error en tiempo de
ejecución a través de la función RAISERROR.
• Raiserror. Devuelve un mensaje de error
definido por el usuario y establece un indicador
del sistema para registrar que se ha producido
un error; más eficaz que PRINT para devolver
mensajes a las aplicaciones.
• Sintaxis
• RAISERROR ( { msg_id | msg_str } { , severity ,
state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
• msg_id. Número de mensaje de error definido por el
usuario que está almacenado en la tabla sysmessages.
Deben ser mayores de 50.000.
• msg_str. Mensaje ad hoc con un formato similar al
estilo de formato PRINT... Puede contener un máximo
de 400 caracteres. Si el mensaje contiene más de 400
caracteres, solamente aparecerán los 397 primeros y se
agregarán puntos suspensivos…
• Severity. Nivel de gravedad definido por el usuario que
se asocia con este mensaje. Todos los usuarios pueden
utilizar los niveles de gravedad de 0 a 18.
• State. Entero arbitrario entre 1 y 127 que representa
información acerca del estado de llamada del error. Un
valor negativo de state pasa a tener un valor
predeterminado de 1.
• VER RESTO DE CONFIGURACION
Create procedure chequearpersona1 (@dni varchar(9))
as
if exists (select * from persona where [email protected])
begin
select nombre, apellido from persona where [email protected]
end
else
raiserror ('Valor pedido no existe', 16, 1)
exec chequearpersona1 '343434343'
• Es posible definir errores de usuario con el fin
de poder reutilizarlos, y así ofrecer un
comportamiento homogéneo.
• Esto puede realizarse a través del
procedimiento almacenado del sistema
sp_addmessage (especificando código de
error, severidad, texto del error, e idioma).
• Pueden consultarse los errores existentes en
sysmessages.
• EXEC sp_addmessage @msgnum = 50001, @severity
= 16, @msgtext = 'No existe elemento buscado', @lang
= 'us_english‘
• SELECT * FROM master.dbo.sysmessages
Create procedure chequearpersona1 (@dni varchar(9))
as
if exists (select * from persona where [email protected])
begin
select nombre, apellido from persona where [email protected]
end
else
raiserror (50001, 16, 1)
• Apliquemos @@error y raiserror a algun
procedimiento realizado anteriormente.
• Que tipo de problema podria ser?
• Select?
• Qué error podría surgir con un select?
• Uso de @@rowcount
Create procedure chequearpersona (@dni
varchar(9))
as
if exists (select * from persona where [email protected])
begin
select nombre, apellido from persona where
[email protected]
end
else
print 'Persona no esta registrada'
• Insertar un registro de persona.
• Puede surgir un error ?
Create procedure insertarpersona5 (@DNI varchar(9),
@nombre varchar(25), @apellido varchar(50), @ciudad
varchar(25), @direccioncalle varchar(50),
@direccionnum varchar(3), @telefono varchar(9),
@fechanacimiento datetime, @varon char(1))
as
insert into persona (DNI, Nombre, Apellido, Ciudad,
DireccionCalle, DireccionNum, Telefono,
FechaNacimiento, Varon)
values (@DNI, @Nombre, @APellido, @Ciudad,
@DireccionCalle, @DireccionNum, @Telefono,
@FechaNacimiento, @Varon)
• Hacer lo mismo con el siguiente PA (actualiza id de una titulacion)
create PROCEDURE updatetitulacion1 (@idtitantiguo numeric(6),
@idtitnuevo numeric(6))
AS
if (select count(*) from titulacion where [email protected])=1
begin
if (select count(*) from titulacion where [email protected])=0
begin
update titulacion
set [email protected]
where [email protected]
return 0
end
else
return 2
end
else
return 1
• Uso de INTO en SELECT
• Into en select…
• INTO (en SELECT). La cláusula INTO habilita
para especificar que el conjunto de resultados
se utilizará para crear una tabla nueva con el
nombre definido en la cláusula: Crea una nueva
tabla e inserta en ella las filas resultantes…
• Se suele utilizar para crear tablas de trabajo, o
tablas intermedias; se crean para una
determinada tarea y luego se borran.
• Se puede utilizar para crear una copia de
seguridad de la tabla.
• Sintaxis.
• INTO new_table
SELECT *
INTO new_table_name
FROM old_tablename
SELECT column_name(s)
INTO new_table_name
FROM old_tablename
En BD Universidad:
select *
into copia
from persona
select Nombre, Apellido
into copia1
from persona
select Nombre, Apellido
into copia1
from persona
Where ciudad=‘Concepcion’
select Asignatura.Nombre as asig, persona.nombre,
persona.apellido
into copia2
from asignatura, profesor, persona
Where asignatura.idprofesor=profesor.idprofesor
and profesor.dni=persona.dni
• No confundir con…
• INSERT INTO…SELECT:
• Inserción multiple de filas.
• La sentencia INSERT permite tambien insertar
varios registros en una tabla.
• Pare ello se utiliza una combinación de la
sentencia INSERT junto a una sentencia
SELECT.
• El resultado es que se insertan todos los
registros devueltos por la consulta.
• Sintaxis.
• INSERT INTO <nombre_tabla>
[(<campo1>[,<campo2>,...])]
SELECT
[(<campo1>[,<campo2>,...])]
FROM
<nombre_tabla_origen>;
• Se deben cumplir las siguientes normas:
• La lista de campos de las sentencias insert y
select deben coincidir en número y tipo de
datos.
• Ninguna de las filas devueltas por la consulta
debe infringir las reglas de integridad de la tabla
en la que vayamos a realizar la inserción.
create table ciudades
(id int identity (1,1) primary key,
ciudad varchar (30))
insert into ciudades (ciudad)
select distinct ciudad
from persona
• Hacer un PA para la tabla persona, con
parametro de entrada (ciudad) que realice
un select de todas las personas de esas
ciudad, y genere una copia de seguridad
con esos datos.
• Modificar PA usando @@error o
@rowcount, raiserror
• Cursor. Variable que permite recorrer un
conjunto de resultados obtenidos a través de
una SELECT fila a fila: permiten situarse en
filas específicas del conjunto de resultados.
• Recuperan una fila o bloque de filas.
• Aceptan modificaciones de los datos de las filas
en la posición actual del conjunto de resultados
• En qué se podrían relacionar los SELECT INTO
e INSERT INTO SELECT con cursores?
• Para trabajar con cursores se deben seguir los
siguientes pasos:
• Declarar el cursor, utilizando DECLARE
• Abrir el cursor, utilizando OPEN
• Leer los datos del cursor, utilizando FETCH ...
INTO
• Cerrar el cursor, utilizando CLOSE
• Liberar el cursor, utilizando DEALLOCATE
• Sintaxis General
•
•
•
•
-- Declaración del cursor
DECLARE <nombre_cursor> CURSOR
FOR
<sentencia_sql>
•
•
-- apertura del cursor
OPEN <nombre_cursor>
•
•
-- Lectura de la primera fila del cursor
FETCH <nombre_cursor> INTO <lista_variables>
•
•
•
•
•
•
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Lectura de la siguiente fila de un cursor
FETCH <nombre_cursor> INTO <lista_variables>
...
END -- Fin del bucle WHILE
•
•
•
•
-- Cierra el cursor
CLOSE <nombre_cursor>
-- Libera los recursos del cursor
DEALLOCATE <nombre_cursor>
• @@FETCH_STATUS. Variable global. Devuelve
el estado de la última instrucción FETCH de
cursor ejecutada.
• Ejemplo. Abrir un cursor y recorrerlo:
DECLARE persona_Cursor CURSOR FOR
SELECT Nombre, Apellido, Ciudad
FROM persona
WHERE varon=1
OPEN persona_Cursor
FETCH NEXT FROM persona_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM persona_Cursor
END
CLOSE persona_Cursor
DEALLOCATE persona_Cursor
• Ejemplo. Abrir un cursor, recorrerlo e imprimir:
DECLARE @nombre varchar(20)
DECLARE @apellido varchar(20)
DECLARE @ciudad varchar(20)
DECLARE persona_Cursor CURSOR FOR
SELECT Nombre, Apellido, Ciudad
FROM persona
WHERE varon=1
ORDER BY Apellido, Nombre
OPEN persona_Cursor
FETCH NEXT FROM persona_Cursor INTO @nombre, @apellido, @ciudad
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT [email protected] +' [email protected] +' vive en [email protected]
FETCH NEXT FROM persona_Cursor
INTO @nombre, @apellido, @ciudad
END
CLOSE persona_Cursor
DEALLOCATE persona_Cursor
• Ejemplo. Abrir un cursor, recorrerlo y actualizar:
DECLARE @nombre varchar(20)
DECLARE @creditos float
DECLARE @coste float
DECLARE asig_Cursor CURSOR FOR
SELECT Nombre, creditos, costebasico
FROM asignatura
OPEN asig_Cursor
FETCH NEXT FROM asig_Cursor into @nombre, @creditos, @coste
WHILE @@FETCH_STATUS = 0
BEGIN
update asignatura
set [email protected][email protected]*4
where [email protected]
FETCH NEXT FROM asig_Cursor into @nombre, @creditos, @coste
END
CLOSE asig_Cursor
DEALLOCATE asig_Cursor
• Ejecutar el siguiente cursor
• Que hace?
SET NOCOUNT ON
DECLARE
@nom varchar(20),
@cont varchar(30),
@message varchar (80),
@nombp varchar (20)
PRINT '-------- proveedores almacen kollao--------'
DECLARE proov_cursor CURSOR FOR
SELECT nombre, nombre_contacto
FROM proveedor
OPEN proov_cursor
FETCH NEXT FROM proov_cursor
INTO @nom, @cont
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- productos por proveedor: ' + @nom + ' ,' + @cont
PRINT @message
DECLARE prod_cursor CURSOR FOR
SELECT nombre_fantasia
FROM proveedor, producto
WHERE producto.proveedor=proveedor.codigo_proveedor and [email protected]
OPEN prod_cursor
FETCH NEXT FROM prod_cursor INTO @nombp
IF @@FETCH_STATUS <> 0
PRINT ' <<No productos>>'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ' ' + @nombp
PRINT @message
FETCH NEXT FROM prod_cursor INTO @nombp
END
CLOSE prod_cursor
DEALLOCATE prod_cursor
FETCH NEXT FROM proov_cursor
INTO @nom, @cont
END
CLOSE proov_cursor
DEALLOCATE proov_cursor
•
/* Este cursor deja las contraseñas iguales al nombre de usuario.
La tabla Cliente tiene estos tres campos: CliCod, CliUser, CliPass */
-- declaramos las variables
declare @cod as int
declare @user as varchar(50)
declare @pass as varchar(50)
•
-- declaramos un cursor llamado "CURSORITO".
declare CURSORITO cursor for
select CliCod, CliUser, CliPass from Cliente
•
open CURSORITO
-- Avanzamos un registro y cargamos en las variables los valores
encontrados en el primer registro
•
fetch next from CURSORITO
into @cod, @user, @pass
while @@fetch_status = 0
begin
update Cliente set CliPass= @user where [email protected]
-- Avanzamos otro registro
fetch next from CURSORITO
into @cod, @user, @pass
end
-- cerramos el cursor
close CURSORITO
deallocate CURSORITO
• Generar un PA que a traves de un cursor,
imprima el nombre, creditos y cuatrimestre
de las asignaturas registradas.
• Generar un PA que a traves de un cursor,
imprima el nombre, creditos y cuatrimestre
de ciertas asignaturas registradas, de
acuerdo a parametro de entrada
(cuatrimestre).
• Cursores con parametros.
• Al declarar un cursor podemos definir:
• DECLARE <nombre_cursor> CURSOR [ LOCAL |
GLOBAL ]
• [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET |
DYNAMIC | FAST_FORWARD ] [ READ_ONLY |
SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ]
FOR <sentencia_sql>
INVESTIGAR
•
•
•
•
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY]
• Triggers-Disparadores
• Clase especial de procedimiento almacenado
que se ejecuta automáticamente (se “dispara”)
cuando se produce un evento en el servidor de
bases de datos.
• Se ejecuta siempre que se intenta modificar los
datos de una tabla que el trigger protege:
realizar un INSERT, UPDATE o
DELETE…dependiendo de la accion
especificada.
• No es posible evitar su ejecución.
• Los triggers se definen para una tabla
específica, denominada tabla del trigger.
• No es posible invocar directamente los triggers,
que tampoco pasan ni aceptan parámetros.
• Gran herramienta para controlar reglas de
negocio más complejas que una simple
integridad referencial.
• Ojo: el usuario no espera que el trigger le
devuelva registros luego de agregar o modificar
información.
• Sintaxis.
CREATE trigger <Nombre del trigger>
ON <Nombre de la Tabla>
FOR <INSERT l UPDATE l DELETE>
AS
Sentencias….
• Un trigger para inserción de registros genera
automáticamente una tabla en el cache con la
información que intenta añadir, esta tabla se denomina
INSERTED y es a través de esta tabla que se pueden
hacer comparaciones en otras tablas.
• Un trigger para eliminación de registros genera
automáticamente una tabla en el cache con la
información que intenta eliminar, esta tabla se denomina
DELETED y es a través de esta tabla que se pueden
hacer comparaciones en otras tablas.
• Si se trata de un trigger para actualización se generan
ambas tablas INSERTED con los nuevos datos y
DELETED con la información que será reemplazada.
• Un trigger se "dispara" sólo cuando la
instrucción de modificación de datos finaliza.
• SQL Server verifica la posible violación de tipos
de datos, reglas o restricciones de integridad.
• El trigger y la instrucción que lo "dispara" se
consideran una sola transacción que puede
revertirse desde dentro del disparador.
• Si se detecta un error grave, se revierte toda la
transacción.
create TRIGGER reminder ON persona
FOR INSERT, UPDATE AS
PRINT 'Se está intentando modificar datos‘
/*RAISERROR...*/
• INSERT
• Aplicar reglas de negocio…???
• Las personas que se registren en almacenes
Kollao como clientes deben ser mayores de 18
años.
• Como seria un trigger que verifique esto?
create trigger insertclienteedad on cliente
for insert as
declare @fecha datetime
select @fecha= fecha_nacimiento from inserted
if (datediff(yy,@fecha, getdate()))<18
begin
raiserror (‘No se aceptan clientes menores de 18
años',16,1)
end
else
print ‘Se inserto cliente exitosamente'
create trigger insertclienteedad on cliente
for insert as
declare @fecha datetime
select @fecha= fecha_nacimiento from inserted
if (datediff(yy,@fecha, getdate()))<18
begin
raiserror (‘No se aceptan clientes menores de 18
años',16,1)
rollback transaction /*deshace la transaccion*/
end
else
print ' Se inserto cliente exitosamente '
• Se necesita insertar un producto cuya fecha de
vencimiento sea minimo en 15 dias despues del
registro.
• No se aceptan productos con un stock menor a
10.
• No mas de 5 productos de un proveedor.
• Insercion en compra.
• …
• Cantidad comprada con tarjeta de credito no
pueden ser de mayor de 10.
create trigger insertcompratc on compra
for insert as
if (select forma_pago from inserted)='t' and (select
cantidad from inserted)>10
begin
raiserror ('No se aceptan compras con tarjeta de
credito de cantidad mayor de 10 ',16,1)
rollback transaction
end
else
print 'Compra valida'
• No se puede comprar una cantidad de producto
mayor al stock.
• Sin embargo, ese trigger no actualiza el stock, si
es que éste es suficiente…
• Veamos que pasa con Update.
• Al modificar algun dato de sucursal, se
debe manejar un respaldo de sus datos,
incluida fecha y usuario que genero la
modificacion.
create table auditsuc
(nr int identity (1,1) primary key,
cod varchar(6),
dir varchar (50),
tel varchar (12),
fecha datetime,
usuario varchar(10))
create trigger modsucursal on sucursal
for update as
declare @cod varchar(6), @dir varchar (50), @tel
varchar(12)
select @cod=(select codigo_sucursal from
deleted)
select @dir=(select direccion from deleted)
select @tel=(select telefono_sucursal from
deleted)
insert into auditsuc (cod, dir, tel,fecha,usuario)
values(@cod, @dir, @tel, getdate(), user)
• @@rowcount. Variable global. Guarda el
numero de filas afectadas por la ultima
instrucción.
alter trigger modsucursal on sucursal
for update as
if @@rowcount=1
begin
declare @cod varchar(6), @dir varchar (50), @tel
varchar(12)
select @cod=(select codigo_sucursal from deleted)
select @dir=(select direccion from deleted)
select @tel=(select telefono_sucursal from deleted)
insert into auditsuc (cod, dir, tel,fecha,usuario)
values(@cod, @dir, @tel, getdate(), user)
end
else
raiserror(‘No se pueden realizar actualizaciones de varias
filas',16,1)
rollback transaction
• On UPDATE en una fila especifica…
create table auditsuc1
(nr int identity (1,1) primary key,
cod varchar(6),
tel varchar (12),
fecha datetime,
usuario varchar(10))
create trigger modsucursaltel on sucursal
for update as
if update(telefono_sucursal)
begin
declare @cod varchar(6), @tel varchar(12)
select @cod=(select codigo_sucursal from deleted)
select @tel=(select telefono_sucursal from deleted)
insert into auditsuc1 (cod, tel,fecha,usuario)
values(@cod, @tel, getdate(), user)
end
• No esta permitido realizar modificaciones
los fines de semana
• DELETE.
• Al eliminarse un proveedor, se debe
manejar un respaldo de los datos
eliminados.
create table auditproveedor
(nr int identity (1,1) primary key,
cod varchar(6),
nom varchar (20),
contacto varchar (30),
tel varchar (12),
fecha datetime,
usuario varchar(10))
create trigger delproveedor on proveedor
for delete as
declare @cod varchar(6), @nom varchar (20), @contacto
varchar (30),@tel varchar(12)
select @cod=(select codigo_proveedor from deleted)
select @nom=(select nombre from deleted)
select @contacto=(select nombre_contacto from deleted)
select @tel=(select telefono_contacto from deleted)
insert into auditproveedor (cod, nom, contacto, tel
,fecha,usuario)
values(@cod, @nom, @contacto, @tel, getdate(), user)
create trigger delproveedor on proveedor
for delete as
declare @cod varchar(6), @nom varchar (20), @contacto
varchar (30),@tel varchar(12)
If @@rowcount=1
begin
select @cod=(select codigo_proveedor from deleted)
select @nom=(select nombre from deleted)
select @contacto=(select nombre_contacto from deleted)
select @tel=(select telefono_contacto from deleted)
insert into auditproveedor (cod, nom, contacto, tel
,fecha,usuario)
values(@cod, @nom, @contacto, @tel, getdate(), user)
end
Else
Raiserror (‘No se puede eliminar mas de un proveedor a la
vez’,16,1)
• No se puede borrar mas de un proveedor
a la vez!!!
create trigger delproveedor1 on proveedor
for delete as
IF (SELECT COUNT(*) FROM Deleted) > 1
BEGIN
RAISERROR(‘No puede borrar más de un
proveedor al mismo tiempo.’,16, 1)
ROLLBACK TRANSACTION
END
create trigger delproveedor1 on proveedor
for delete as
IF @@rowcount > 1
BEGIN
RAISERROR(‘No puede borrar más de un
proveedor al mismo tiempo.’,16, 1)
ROLLBACK TRANSACTION
END
• Conocer triggers creados:
• select *
from sysobjects
where type = ‘TR’
• Obtener el texto del trigger
• sp_helptext nombretrigger
• ALTER TRIGGER
• DROP Trigger
• Deshabilitar
• ALTER TABLE compra
• DISABLE TRIGGER insertcomprastock
• Habilitar
• ALTER TABLE compra
• ENABLE TRIGGER insertcomprastock
• **************
Descargar

Document