Insercion de datos.. Tabla persona. create procedure insertarpersona (@DNI varchar(9), @nombre...

• Insercion de datos.

• Tabla persona.

• create procedure insertarpersona (@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)

• exec insertarpersona '34535435', 'juan', 'perez', 'concepcion', 'ongolmo', '34','3434343', '12-2-1978', '1'

• Qué problema podría surgir al insertar una nueva persona?

• Que condicion se podría aplicar aquí?

• Persona con ese ID ya existe…

• create procedure insertarpersona1 (@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• if not exists(select * from persona where dni=@dni)• begin• insert into persona (DNI, Nombre, Apellido, Ciudad,

DireccionCalle, DireccionNum, Telefono, FechaNacimiento, Varon)• values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle,

@DireccionNum, @Telefono, @FechaNacimiento, @Varon)• print ‘Persona insertada.'• end• else• print 'Ya existe una persona con ese DNI.'

• Que pasa si se ingresa en el atributo varon un valor distinto a 0 o 1?

• create procedure insertarpersona2 (@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• if not exists(select * from persona where dni=@dni)• begin• if @varon in ('0', '1')• begin• insert into persona (DNI, Nombre, Apellido, Ciudad, DireccionCalle, DireccionNum,

Telefono, FechaNacimiento, Varon)• values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle, @DireccionNum,

@Telefono, @FechaNacimiento, @Varon)• print 'Persona insertada.'• end• else• print 'Persona debe ser 1 o 0'• end• else• print 'Ya existe una persona con ese DNI.'

• RETURN. Salida incondicional del procedimiento. Las instrucciones que siguen a RETURN no se ejecutan.

• Sintaxis• RETURN [ integer_expression ]

• Argumentos. • integer_expression: Es el valor entero que se devuelve.

Los procedimientos almacenados pueden devolver un valor entero al procedimiento que realiza la llamada o a una aplicación.

• Tipos devueltos• Opcionalmente devuelve int.

• Veamos el ejemplo anterior usando RETURN

• CREATE procedure insertarpersona3 (@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 • if not exists(select * from persona where dni=@dni) • begin • if @varon in ('0', '1') • begin • insert into persona (DNI, Nombre, Apellido, Ciudad, DireccionCalle, DireccionNum, Telefono,

FechaNacimiento, Varon) • values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle, @DireccionNum, @Telefono,

@FechaNacimiento, @Varon) • return 0 • end • else • return 1 • end • else • return 2

• Como se ejecuta esto?

• declare @status int• exec @status=insertarpersona3 '264562', 'juan', 'perez',

'concepcion', 'ongolmo', '34','3434343', '12-2-1978', '3'• if @status=0• print 'Datos de persona ingresados correctamente.'• else• if @status=1• print 'Atributo varon de Persona debe ser 0 o 1.'• else • if @status=2• print 'Ya existe persona con ese dni.'

• Si solo queremos conocer el valor de la variable status:

• declare @status int• exec @status=insertarpersona2 '264562', 'juan', 'perez',

'concepcion', 'ongolmo', '34','3434343', '12-2-1978', '1'• print @status• **********************• declare @status int• exec @status=insertarpersona2 '364562', 'juan', 'perez',

'concepcion', 'ongolmo', '34','3434343', '12-2-1978', '1'• print 'Status es: ' +cast( @status as varchar)• **********************• declare @status int• exec @status=insertarpersona2 '3645623', 'juan', 'perez',

'concepcion', 'ongolmo', '34','3434343', '12-2-1978', '1'• select Status=@status • /* select @status as Status*/

• Return siempre hara salir de la rutina programada…

• Return no devuelve valor…

• create procedure insertarpersona22 (@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 • if not exists(select * from persona where dni=@dni) • begin • if @varon in ('0', '1') • begin • insert into persona (DNI, Nombre, Apellido, Ciudad, DireccionCalle, DireccionNum,

Telefono, FechaNacimiento, Varon) • values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle, @DireccionNum,

@Telefono, @FechaNacimiento, @Varon) • print 'Datos de persona ingresados' • select * from persona where dni=@dni • return• end • else • print 'Atributo varon de Persona debe ser 0 o 1.'• end • else • print 'Ya existe persona con ese dni.'

• create procedure insertarpersona23 (@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 • if not exists(select * from persona where dni=@dni) • begin • if @varon in ('0', '1') • begin • insert into persona (DNI, Nombre, Apellido, Ciudad, DireccionCalle, DireccionNum,

Telefono, FechaNacimiento, Varon) • values (@DNI, @Nombre, @APellido, @Ciudad, @DireccionCalle, @DireccionNum,

@Telefono, @FechaNacimiento, @Varon) • print 'Datos de persona ingresados' • return• select * from persona where dni=@dni • end • else • print 'Atributo varon de Persona debe ser 0 o 1.'• end • else • print 'Ya existe persona con ese dni.'

• Uso de parametros de entrada con valores por defecto:

• Create procedure proc (@uno varchar(10)=default, @dos int=default)

• Default puede ser NULL

• Ejercicio.

• Insertar nuevo registro en tabla titulacion.

• Usar RETURN

• Tomar en cuenta?

– Si hay otra titulacion con ese id– Id titulacion no puede ser null– Nombre titulacion no puede ser null

• create PROCEDURE insert_Titulacion (@IdTitulacion numeric(6)=NULL, @Nombre varchar(20)=NULL)

• AS • if @IdTitulacion is NULL• return 1• else • if @Nombre is NULL• return 2• else• if not exists (select * from titulacion where idtitulacion=@idtitulacion) • begin • INSERT INTO Titulacion (IdTitulacion,Nombre) • VALUES (@IdTitulacion, @Nombre)• return 0• end• else• return 3

• declare @status int• exec @status=insert_Titulacion 343424, ‘Aleman'• if @status=1• print 'Debe ingresar un ID de titulación'• if @status=2• print 'Debe haber un nombre de titulación relacionada

con el ID'• if @status=0• print 'Se ingreso titulación correctamente'• if @status=3• print 'Ya existe titulacion con ese id'

• Insertar datos en tabla Alumno. Recordar:

• Cuál(es) seria(n) la(s) condicion(es) para poder hacer la inserción?

• (Asumiendo que valores son <> de NULL)

1. No debe haber otro alumno con el mismo id.

2. Para ser ingresado como alumno, debe estar como persona. (integridad referencial)

3. No puede haber otro alumno con el mismo DNI (duplicacion)

• create procedure insertaralumno (@idalumno varchar(7), @DNI varchar(9))• as• if not exists(select * from alumno where idalumno=@idalumno)• begin• if exists (Select * from persona where dni=@dni)• begin • if not exists (select * from alumno where dni=@dni)• begin• insert into alumno (idalumno, DNI)• values (@idalumno, @DNI)• return 0• end• else• return 1• end• else• return 2• end• else• return 3

• declare @status int• exec @status=insertaralumno 'A02024', '26262626A'

• if @status=0• print 'Alumno ingresado correctamente.'• else• if @status=1• print 'Ya hay un alumno con ese DNI'• else • if @status=2• print 'No esta ingresado como persona'• else• if @status=3 • print 'Ya existe alumno con ese id.'

• Ejercicio.

• Insertar datos en tabla alumnoasignatura. Usar IF ELSE, RETURN, PRINT

• Qué condiciones se deben tomar en cuenta?

• (Realizar)

• Otras condiciones…

• Alumnos solo pueden tomar cierta cantidad de ramos…: 3 ramos.

• …

• Alumnos pueden tomar solo hasta 20 creditos…(TAREA)

• create procedure insertaralumnoasignatura1 (@idalumno varchar(7), @idasignatura numeric(6)) • as • • if (select count(*) from alumnoasignatura where idalumno=@idalumno and idasignatura=@idasignatura)>=1• return 1 /*registro ya existe*/ • else • begin • if exists (select * from alumno where idalumno=@idalumno)/*si existe el alumno...*/ • begin • if exists (select * from asignatura where idasignatura=@idasignatura)/*si existe el ramo*/ • begin • if (select count(idasignatura) from alumnoasignatura where idalumno=@idalumno)<3• begin• insert into alumnoasignatura (IdAlumno, IdAsignatura) • values (@idalumno, @idasignatura) • return 0 /*se inserto registro*/ • end• else• return 4 /*ya tiene 3 ramos registrados*/• end • else • return 2 /*ramo no existe*/ • end • else • return 3 /*alumno no existe*/ • end

• declare @status int• exec @status=insertaralumnoasignatura1 'A010101', 443534• if @status=0• print 'Registro ingresado correctamente.'• else• if @status=1• print 'Registro ya existe'• else • if @status=2• print 'Ramo no registrado'• else• if @status=3 • print 'Alumno no registrado'• else• if @status=4 • print 'Alumno ya tiene 3 ramos registrados'

• Otras…

• Ejercicio.

• Por ejemplo, los ramos de la titulación Matemáticas sólo pueden ser tomados por alumnos varones.

• (Realizar)

• Modificar/Update datos de una tabla.

• Ejercicio.

• AP que modifique nombre, creditos y costebasico de una asignatura a partir de su id.

• Como seria? Usar IF ELSE y RETURN

• Create PROCEDURE updateAsignatura (@IdAsignatura numeric, @Nombre varchar(50), @Creditos float, @CosteBasico numeric)

• AS • if exists (select * from asignatura where

idasignatura=@idasignatura)• begin• UPDATE Asignatura • SET Nombre=@Nombre, Creditos=@Creditos,

CosteBasico=@CosteBasico • WHERE IdAsignatura=@IdAsignatura• return 0• end• else • return 1

• declare @status int• exec @status=updateAsignatura 114545,

'Ingles', 4, 20

• if @status=0• print 'Registro actualizado.'• else• if @status=1• print 'No se pudo actualizar, asignatura no

existe.'

• Ejercicio.

• Crear un PA que modifique el telefono de una persona a partir de su nombre.

• Que pasa si hay 2 personas con el mismo nombre?

• (Realizar)

• Que pasa si quiero actualizar un id de alguna tabla?

• Tomar en cuenta restricciones de integridad: cascade/restrict

• Otras condiciones?

• Actualizar id de tabla titulacion: es usada en asignatura.

• Si esta en cascade…que significa?

• 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

• declare @status int• exec @status=updatetitulacion1 130110, 130112

• if @status=0• print 'Se actualizo id titulacion'• else• if @status=1• print 'No existe esa titulacion'• if @status=2• print 'Id nuevo ya esta tomado'

• Si esta en restrict, que significa?

• Create PROCEDURE updatetitulacion (@idtitantiguo numeric(6), @idtitnuevo numeric(6))• AS • if (select count(*) from titulacion where idtitulacion=@idtitantiguo)=1• begin• if exists (select * from asignatura where idtitulacion=@idtitantiguo)• begin• update asignatura• set idtitulacion=NULL• where idtitulacion=@idtitantiguo• update titulacion• set idtitulacion=@idtitnuevo• where idtitulacion=@idtitantiguo• update asignatura• set idtitulacion=@idtitnuevo• where idtitulacion is NULL• return 0• end• else• begin• update titulacion• set idtitulacion=@idtitnuevo• where idtitulacion=@idtitantiguo• return 1• end• end• else • return 2

• declare @status int• exec @status=updatetitulacion 130111, 130110

• if @status=0• print 'Se actualizo id en titulacion y tb en asignatura'• else• if @status=1• print 'Se actualizo id en titulacion' • else• if @status=2• print 'No existe esa titulacion'

• Tambien podria imprimirse un mensaje diciendo que no se puede actualizar…