1 / 107

Unidad 3

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. cabecera. cuerpo. BLOQUE. se incluyen todos los elementos de definición del programa; como nombre, parámetros (entrada y/o salida),

lacota-santiago
Download Presentation

Unidad 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Unidad 3

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

  3. cabecera cuerpo BLOQUE se incluyen todos los elementos de definición del programa; como nombre, parámetros (entrada y/o salida), tipo de trigger, etc. • CREATE… • AS • DECLARE… • … • Sentencias SQL • … se definen todos los elementos de desarrollo como declaración de variables y bloque de sentencias

  4. cabecera declaración cuerpo ejecución BLOQUE • CREATE… • AS • DECLARE… • … • Sentencias SQL • …

  5. excepciones BLOQUE cabecera • CREATE… • AS • DECLARE… • … • Sentencias SQL • … declaración cuerpo ejecución

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

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

  8. 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 = @dividendo/@divisor PRINT 'No hay error‘ END TRY BEGIN CATCH PRINT 'Se ha producido un error‘ END CATCH

  9. SQL Server 2000 no maneja el TRY/CATCH. • Qué se ha usado para controlar errores? • IF ELSE…PRINT…RETURN

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

  11. DECLARE @divisor int , @dividendo int , @resultado int SET @dividendo = 100 SET @divisor = 0 /*se genera una division por 0->error*/ SET @resultado = @dividendo/@divisor IF @@ERROR = 0 PRINT 'No hay error' ELSE PRINT 'Error de division'

  12. DECLARE @divisor int , @dividendo int , @resultado int SET @dividendo = 100 SET @divisor = 0 /*se genera una division por 0->error*/ SET @resultado = @dividendo/@divisor /*esta linea vuelve a establecer @@error a 0*/ PRINT 'Controlando el error ...' IF @@ERROR = 0 PRINT 'No hay error' ELSE PRINT 'Error de division'

  13. DECLARE @divisor int,@dividendo int , @resultado int declare @nu int SET @dividendo = 100 SET @divisor = 0 SET @resultado = @dividendo/@divisor set @nu=@@error print 'Controlando el error...' IF @nu = 0 PRINT 'No hay error' ELSE PRINT 'Hay error: ' +cast(@nu as varchar)

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

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

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

  17. Create procedure chequearpersona1 (@dni varchar(9)) as if exists (select * from persona where dni=@dni) begin select nombre, apellido from persona where dni=@dni end else raiserror ('Valor pedido no existe', 16, 1) exec chequearpersona1 '343434343'

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

  19. 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 dni=@dni) begin select nombre, apellido from persona where dni=@dni end else raiserror (50001, 16, 1)

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

  21. Create procedure chequearpersona (@dni varchar(9)) as if exists (select * from persona where dni=@dni) begin select nombre, apellido from persona where dni=@dni end else print 'Persona no esta registrada'

  22. Create procedure chequearpersona6 (@dni varchar(9)) as select nombre, apellido from persona where dni=@dni if @@rowcount=0 print 'Persona no esta registrada'

  23. alter procedure chequearpersona6 (@dni varchar(9)) as select nombre, apellido from persona where dni=@dni if @@rowcount=0 raiserror ('Persona no esta registrada', 16,1)

  24. Insertar un registro de persona. • Puede surgir un error ?

  25. 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) if @@error<>0 print 'Hubo un error de insercion'

  26. alter 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 declare @nu int insert into persona (DNI, Nombre, Apellido, Ciudad, DireccionCalle, DireccionNum, Telefono, FechaNacimiento, Varon) values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle, @DireccionNum, @Telefono, @FechaNacimiento, @Varon) set @nu=@@error if @nu=2627 print 'No se aceptan claves duplicadas. No se pudo insertar persona.' else if @nu=547 print 'Conflicto con restricciones tipo check en insercion. No se pudo insertar persona.' if @nu=0 print 'Insercion realizada correctamente'

  27. alter 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 declare @nu int insert into persona (DNI, Nombre, Apellido, Ciudad, DireccionCalle, DireccionNum, Telefono, FechaNacimiento, Varon) values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle, @DireccionNum, @Telefono, @FechaNacimiento, @Varon) set @nu=@@error if @nu=2627 raiserror ('No se aceptan claves duplicadas',16,1) else if @nu=547 raiserror ('Conflicto con restricciones tipo check en insercion',16,1) if @nu=0 print 'Insercion realizada correctamente'

  28. 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 idtitulacion=@idtitantiguo)=1 begin if (select count(*) from titulacion where idtitulacion=@idtitnuevo)=0 begin update titulacion set idtitulacion=@idtitnuevo where idtitulacion=@idtitantiguo return 0 end else return 2 end else return 1

  29. create PROCEDURE updatetitulacion5 (@idtitantiguo numeric(6), @idtitnuevo numeric(6)) AS update titulacion set idtitulacion=@idtitnuevo where idtitulacion=@idtitantiguo if @@error<>0 print 'Hubo un error...' else if @@error=0 print 'todo bien'

  30. alter PROCEDURE updatetitulacion5 (@idtitantiguo numeric(6), @idtitnuevo numeric(6)) AS update titulacion set idtitulacion=@idtitnuevo where idtitulacion=@idtitantiguo if @@error<>0 print 'Hubo un error...' else if @@error=0 print 'todo bien' if @@rowcount=0 print 'no se encontro titulacion'

  31. Uso de INTO en SELECT • Into en select…

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

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

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

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

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

  37. create table ciudades (id int identity (1,1) primary key, ciudad varchar (30)) insert into ciudades (ciudad) select distinct ciudad from persona

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

  39. create procedure chequearpersonaycopiar (@ciudad varchar(25)) as if exists (select * from persona where ciudad=@ciudad) Begin select * from persona where ciudad=@ciudad select * into copiapersonaciudad from persona where ciudad=@ciudad end else print 'Persona no esta registrada'

  40. Modificar PA usando @@error o @rowcount, raiserror

  41. alter procedure chequearpersonaycopiar (@ciudad varchar(25)) as select * from persona where ciudad=@ciudad select * into copiapersonaciudad from persona where ciudad=@ciudad if @@rowcount=0 raiserror ('No hay registros de personas en esa ciudad, no se pudo generar copia',16,1) else print ('Copia generada')

  42. alter procedure chequearpersonaycopiar (@ciudad varchar(25)) as IF EXISTS (SELECT name FROM sysobjects WHERE name = 'copiapersonaciudad' AND xtype = 'U') drop table copiapersonaciudad else Begin select * from persona where ciudad=@ciudad select * into copiapersonaciudad from persona where ciudad=@ciudad if @@rowcount=0 raiserror ('No hay registros de personas en esa ciudad, no se pudo generar copia',16,1) else print ('Copia generada') end

  43. Cursores

  44. Cursor. Variable que permite recorrer un conjunto de resultados obtenidos a través de un 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?

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

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

  47. @@FETCH_STATUS. Variable global. Devuelve el estado de la última instrucción FETCH de cursor ejecutada.

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

  49. 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 +@nombre +' '+@apellido +' vive en '+@ciudad FETCH NEXT FROM persona_Cursor INTO @nombre, @apellido, @ciudad END CLOSE persona_Cursor DEALLOCATE persona_Cursor

  50. 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 costebasico=@coste+@creditos*4 where nombre=@nombre FETCH NEXT FROM asig_Cursor into @nombre, @creditos, @coste END CLOSE asig_Cursor DEALLOCATE asig_Cursor

More Related