Funciones y Procedimientos en MYSQL y PostgreSQL
-
Upload
eleazar-jimenez-cruz -
Category
Documents
-
view
18 -
download
5
description
Transcript of Funciones y Procedimientos en MYSQL y PostgreSQL
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';