Trigger, Función Y Procedimiento.docx

SISTEMA: CENTRO ODONTOLÓGICO Centro odontológico de la Universidad de Cuenca Universidad de Cuenca Ingeniería de Sistemas Bases de Datos 2 Ing. Carlos Morocho

Kevin Maxi, Patricio Fajardo [email protected], [email protected]

1

Contenido -Oracle ..................................................................................................................................................... 2 Creación tablas.................................................................................................................................... 2 Función para calcular la edad dada una fecha inicial ......................................................................... 2 Prueba de la función FUNCTIONCALCUEDAD en sqldeveloper ...................................................... 3 Procedimiento para verificar una cédula ecuatoriana válida ............................................................. 4 Prueba del procedimiento almacenado VERIFICACIONCEDULAECUATORIANA en sqldeveloper .. 6 Trigger para calcular la edad, después del ingreso de un paciente .................................................... 7 Prueba del trigger ........................................................................................................................... 7 PostgreSQL .............................................................................................................................................. 7 Creación de las tablas. ........................................................................................................................ 8 Creación de la función, donde se calcula la edad a partir de la fecha de nacimiento ....................... 8 Ejecución de la función ................................................................................................................... 9 Creacion del procedimiento, donde se valida la cedula de un paciente .......................................... 10 Ejecucion de procedimiento ......................................................................................................... 11

2 Realizado por Kevin Maxi

-Oracle Creación tablas CREATE TABLE paciente_servidor( cedula varchar(10), nombre varchar(30), apellido varchar(30), direccion varchar (30), telefono varchar (10), correo varchar(50), fecha_nacimiento date, --

Atributo unico del servidor fecha_ingreso date, edad number(3)

); CREATE TABLE paciente_becado( cedula varchar(10), nombre varchar(30), apellido varchar(30), direccion varchar (30), telefono varchar (10), correo varchar(50), fecha_nacimiento date, --

atributo tipo_beca varchar(20), edad number(3)

);

Función para calcular la edad dada una fecha inicial CREATE OR REPLACE FUNCTION FUNCTIONCALCUEDAD

3 (

FECHANACIMIENTO IN DATE ) RETURN NUMBER AS

BEGIN declare edad number; begin edad := to_number(to_char(sysdate,'YYYY'))-to_number(to_char(fechanacimiento,'YYYY')); /*Se pasa a numero el año ingresado y se resta del año actual*/ DBMS_OUTPUT.put_line(edad); RETURN edad; END FUNCTIONCALCUEDAD; end;

Prueba de la función FUNCTIONCALCUEDAD en sqldeveloper

4

Ilustración 1 Simulación del cálculo de una edad en sqldeveloper

Ilustración 2 Resultado de la ejecución de la simulación en el sqldeveloper de la función FUNCTIONCALCUEDAD

Procedimiento para verificar una cédula ecuatoriana válida create or replace procedure verificacionCedulaEcuatoriana(cedula in varchar2,salida out varchar2) is begin declare i number := 0;

/*Declaracion de variables para el cálculo del ultimo digito*/

resto number := 0; suma number := 0; contenedor varchar2(100);

5 digit number := 0; multiplicacion number := 0; begin suma :=0; i :=10; for contador in 1..i-1 loop /*Bucle para recorrer los diez dígitos*/ select substr(cedula,contador,1) into contenedor from dual; digit:=to_number(contenedor); select mod(contador,2) into contenedor from dual; resto:=to_number(contenedor); if resto <> 0 then digit:=digit*2; end if; if digit > 9 then digit:=digit-9; end if; suma := suma + digit; end loop; multiplicacion := suma *9; select substr(cedula,10,1) into contenedor from dual; /*Obtenemos el ultimo digito y lo guardamos en contenedor*/ digit:=to_number(contenedor); select mod(multiplicacion,10) into contenedor from dual; multiplicacion:=to_number(contenedor); if multiplicacion = digit /*Se verifica si el mod10 de lo obtenido en la suma de verificación es iguaL al ultimo digito*/ then salida:='Exito';

else

6 salida:='Cedula incorrecta por favor ingrese una cedula Ecuatoriana valida';

end if;

end; end;

Prueba del procedimiento almacenado VERIFICACIONCEDULAECUATORIANA en sqldeveloper

Ilustración 3 Simulación de la verificación de una cédula en sqldeveloper

Ilustración 4 Resultado de la ejecución de la función verificacioncedulaecuatoriana

7

Trigger para calcular la edad, después del ingreso de un paciente create or replace TRIGGER CALCULAREDAD before INSERT ON PACIENTE_BECADO for EACH ROW declare edadNueva number(3); /*Variable que contendra la edad del paciente calculada*/ BEGIN

select functioncalcuedad(:new.fecha_nacimiento) into edadNueva from dual; /*Llamando a la función para calcular el valor de la edad*/ :new.edad:=edadNueva;

END;

Prueba del trigger

Ilustración 5 Inserción de un paciente, con la edad inicial de cero, y ejecucion del select donde se puede apreciar la edad correcta

Realizado por Patricio Fajardo

PostgreSQL Proceso en el gestor de bases de datos PostgreSQL.

8

Creación de las tablas. CREATE TABLE paciente_servidor( cedula varchar(10), nombre varchar(30), apellido varchar(30), direccion varchar (30), telefono varchar (10), correo varchar(50), fecha_nacimiento date, --

Atributo unico del servidor fecha_ingreso date, edad integer

); CREATE TABLE paciente_becado( cedula varchar(10), nombre varchar(30), apellido varchar(30), direccion varchar (30), telefono varchar (10), correo varchar(50), fecha_nacimiento date, --

atributo unico del becado tipo_beca varchar(20), edad integer

);

Creación de la función, donde se calcula la edad a partir de la fecha de nacimiento CREATE OR REPLACE FUNCTION calcular_edad( fecha_nacimiento date) RETURNS integer AS $$ begin if extract(year from age(now(),fecha_nacimiento))<0 then return -1;

9 end if; if

extract(month from age(now(),fecha_nacimiento))<0 then return -1;

end if; if

(extract(day from age(now(),fecha_nacimiento)))<0 then return -1;

end if; return extract(year from age(now(),fecha_nacimiento)); end; $$ language plpgsql;

Ejecución de la función SELECT calcular_edad('1996-05-7');

Ilustración 6 calculando la edad dado la fecha de nacimiento

10

Creacion del procedimiento, donde se valida la cedula de un paciente create or replace procedure verificacionCedulaEcuatoriana(inout cedula varchar(10)) LANGUAGE 'plpgsql' AS $$ declare /*Declaracion de variables para el cálculo del ultimo digito*/ verificador integer; finalS integer := 0; suma integer := 0; aux integer:=0; valido integer:=0; begin verificador :=to_number(substring(cedula from 10),'9'); if to_number(substring(cedula from 1 for 2),'99')<24 then if to_number(substring(cedula from 3 for 1),'9')<6 then for i in 1..9 loop if mod(i,2)=0 then aux:= to_number(substring(cedula from i for 1),'9'); if aux>=10 then aux:=aux-9; end if; else aux:= to_number(substring(cedula from i for 1),'9')*2; if aux>=10 then aux:=aux-9; end if; end if; suma:=suma+aux; aux:=0; end loop; end if; finalS:=suma + verificador;

11 if mod(finalS,10)=0 then cedula:='Cedula Correcta''; else cedula:= 'Cedula Incorrecta'; end if; else cedula:='0'; end if; end; $$;

Ejecucion de procedimiento

Ilustración 7 comprobando que el procedimiento de verificación de cedula este correcto (parte 1)

12

Ilustración 8 comprobando que el procedimiento de verificación de cedula este correcto (parte 2)

Creacion del trigger, donde se ingresa la edad de un paciente dado la fecha de nacimiento. CREATE OR REPLACE FUNCTION rellenar_edad() RETURNS TRIGGER AS $rellenar_datos$ DECLARE aux integer; BEGIN select calcular_edad(NEW.fecha_nacimiento) into aux; NEW.edad := aux; RETURN NEW; END; $rellenar_datos$ LANGUAGE plpgsql;

CREATE TRIGGER rellenar_edad BEFORE INSERT ON PACIENTE_BECADO FOR EACH ROW EXECUTE PROCEDURE rellenar_edad();

13

Ejecución del trigger INSERT INTO paciente_becado VALUES('0106243751','patricio','fajardo','cuenca','1956654','patrico.fajardo','1966-10-10','beca1'); SELECT * FROM paciente_becado;

Ilustración 9 comprobando el trigger que llene la edad edad al ingresar un paciente.