Alumno: Eleazar Jimenez Cruz

Funciones y procedimientos en

MYSQL y PostgreSQL.

INSTITUTO TECNOLGICO DELVALLE DE OAXACA

TALLER DE BASE DE DATOS

1. Escriba una función para generar claves aleatorias que tengan el siguiente formato

-Las cuatro primeras debe ser a-z y combinación de mayúsculas y minúsculas

- El 5 elemento debe ser un digito

- El sexto debe ser uno de los siguientes caracteres #$%&/()

- El resto debe ser una combinación de las anteriores

* La clave tiene al menos de longitud 6 y un máximo de 20

MySQL:

delimiter //

create function clavesal () RETURNS varchar(100)

begin

declare clAve varchar(200);

declare LETRAS varchar(100) DEFAULT

'ABCDEFGHIJKLMNÑOPQRSTUVWXYZabcdefghijklmnñopqrstuvwxyz';

declare tamanio,i INT DEFAULT 0;

SET clave=concat(concat(concat(

SUBSTR(LETRAS,ROUND(RAND()*53+1),1),

SUBSTR(LETRAS,ROUND(RAND()*53+1),1),

SUBSTR(LETRAS,ROUND(RAND()*53+1),1),

SUBSTR(LETRAS,ROUND(RAND()*53+1),1))));

set clave = concat(clave,ROUND(RAND()*9));

set clave = concat(clave,CHAR(ROUND(RAND()*(47-33))+33));

SET TAMANIO=6+ROUND(RAND()*14);

ciclo : LOOP

set clave = concat(clave,CHAR(ROUND(RAND()*(122-33))+33));

SET i=i+1;

IF i=tamanio THEN

LEAVE ciclo;

END if;

END LOOP;

RETURN clave;

END;

// delimiter ;

En PostgreSQL:

CREATE OR REPLACE FUNCTION generarclave() RETURNS varchar(100) AS $BODY$ DECLARE clave varchar (200); letras varchar(100) DEFAULT 'ABCDEFGHIJKLMNÑOPQRSTUVWXYZabcdefghijklmnñopqrstuvwxyz'; tamanio int DEFAULT 0; i int DEFAULT 0; BEGIN clave= substr(letras,cast( ROUND(RANDOM()*53+1)as int),1 )|| substr(letras, cast(ROUND(RANDOM()*53+1)as int),1)|| substr(letras, cast(ROUND(RANDOM()*53+1)as int),1)|| substr(letras, cast(ROUND(RANDOM()*53+1)as int),1); clave= clave||cast(ROUND(RANDOM()*9)as int); clave= clave||CHR(cast(ROUND(RANDOM()*(47-33))as int)+33); tamanio=6+cast(ROUND(RANDOM()*14)as int);

loop clave= clave||CHR(cast(ROUND(RANDOM()*(122-33))as int)+33); i=i+1; IF i=tamanio THEN exit; END IF ; END LOOP; RETURN clave; END; $BODY$ Language 'plpgsql';

2. Escriba un procedimiento almacenado que reciba como parámetro un código de proveedor y

devuelve el número de órdenes en las que están incluidos medicamentos de ese proveedor.

En MySQL:

DELIMITER //

CREATE PROCEDURE NumOrdenes(in codigo int)

BEGIN

SELECT COUNT(*)

FROM compra

WHERE proveedor_id like codigo;

END; //

DELIMITER;

En PostgreSQL:

CREATE OR REPLACE FUNCTION orden (provedor int ) RETURNS integer AS $BODY$ declare medic int; BEGIN select count(compra.proveedor_id) into medic from compra where proveedor_id=provedor; return medic; end;

$BODY$ LANGUAGE 'plpgsql' ;

3. Trigger

MySQL:

Delimiter //

CREATE TRIGGER tri_actualiza_artiuclo

AFTER UPDATE ON articulo

FOR EACH ROW

INSERT INTO bitacora (articulo_id,descripcion, precioanterior, precioactual, autor, fechacambio)

VALUES(OLD.articulo_id, OLD.descripcion, OLD.precioventa,NEW.precioventa, current_user,now());

// Delimiter ;

En PostgreSQL:

CREATE FUNCTION articulo_update ( ) RETURNS trigger AS $$ BEGIN IF(TG_OP = 'UPDATE') THEN INSERT INTO bitacora (articulo_id, descripcion, precioanterior, precioactual, autor, fechacambio) VALUES (OLD.articulo_id, OLD.descripcion, OLD.precioventa, NEW.precioventa, current_user,now()); END IF; RETURN NULL; END; $$ Language 'plpgsql'; CREATE TRIGGER tri_actualiza_articulo AFTER UPDATE ON articulo FOR EACH ROW EXECUTE PROCEDURE articulo_update( );

4.- escriba una función que permita calcular edad recibiendo como parámetro la curp

MySQL:

Delimiter //

CREATE FUNCTION edad(curp VARCHAR(20))RETURNS INT

BEGIN

DECLARE anio INT;

DECLARE caracter VARCHAR(1);

SET caracter=SUBSTR(curp,17,1);

IF caracter REGEXP('[0-9]') THEN

SET anio=1900+SUBSTR(curp,5,2);

ELSE

SET anio=2000+SUBSTR(curp,5,2);

END IF;

RETURN (Year(now())-anio);

END;

//Delimiter;

En PostgreSQL:

CREATE OR REPLACE FUNCTION edad (curp VARCHAR(20)) RETURNS INTEGER AS $$ DECLARE anioNacimiento INTEGER; anioActual INTEGER; mesNacimiento INTEGER; mesActual INTEGER; diaNacimiento INTEGER; diaActual INTEGER; anios INTEGER; BEGIN anioActual = date_part('year',current_date);

mesActual = date_part('month',current_date); diaActual = date_part('day',current_date); anioNacimiento = CAST(SUBSTR(curp,5,2) AS INTEGER); mesNacimiento = CAST(SUBSTR(curp,7,2) AS INTEGER); diaNacimiento = CAST(SUBSTR(curp,9,2) AS INTEGER); IF (SUBSTR(curp,17,1) ~'[0-9]') THEN anioNacimiento = anioNacimiento + 1900; ELSE anioNacimiento = anioNacimiento + 2000; END IF; anios = anioActual - anioNacimiento; IF (mesActual < mesNacimiento) OR (mesActual = mesNacimiento AND diaActual < diaNacimiento) THEN anios = anios - 1; END if; RETURN (anios); END; $$ LANGUAGE 'plpgsql';