· 6 years ago · Jul 12, 2019, 03:02 AM
1drop database if exists db_prueba4;
2create database db_prueba4;
3use db_prueba4;
4
5create table tbl_contacto(
6 id int auto_increment primary key,
7 nombre varchar(32),
8 apellido_p varchar(32),
9 apellido_m varchar(32),
10 correo_electronico varchar(64),
11 telefono int,
12 iniciales char(3)
13);
14
15-- 1. Crear una función retorne la concatenación de las iniciales de tres cadenas en Mayúsculas (20 puntos).
16delimiter %%
17create function fn_iniciales(cadena1 varchar(32), cadena2 varchar(32), cadena3 varchar(32))
18 returns varchar(32) deterministic
19 begin
20 set @salida = '';
21 set @c1 = left(cadena1, 1);
22 set @c2 = left(cadena2, 1);
23 set @c3 = left(cadena3, 1);
24 set @salida = upper(concat(@c1, @c2, @c3));
25 return @salida;
26 end;
27%% delimiter ;
28
29-- select fn_iniciales('league','of','legends');
30
31-- 2. Crear un Trigger para antes de insertar un registro en la tabla contacto
32-- que muestre un error si no se está ingresando un numero con un 9 al inicio 987654321 (15 puntos).
33
34delimiter %%
35create trigger tr_contacto_antes_ingresar_telefono before insert on tbl_contacto for each row
36 begin
37 -- verificar requisito
38 set @n1 = left(new.telefono,1);
39 if(@n1 != '9') then
40 signal sqlstate '02000'
41 set message_text = 'El telefono debe iniciar en 9';
42 end if;
43 end;
44%% delimiter ;
45
46-- insert into tbl_contacto(telefono) values('876482731');
47
48delimiter %%
49create trigger tr_contacto_antes_ingresar_correo before insert on tbl_contacto for each row
50 begin
51 -- set @correo = '@correodominio.cl';
52 set @correo = new.correo_electronico;
53 set @largo = length(@correo);
54
55 -- verificar posicion del @
56 -- si el resultado de la funcion locate es igual a 1 -> está al inicio
57 if (locate('@', @correo) = 1) then
58 signal sqlstate '02000'
59 set message_text = 'El @ no debe ir al inicio del campo correo';
60
61 -- si el resultado de la funcion locate coincide con el largo de la cadena -> está al final
62 elseif(locate('@', @correo) = @largo) then
63 signal sqlstate '02000'
64 set message_text = 'El @ no debe ir al final del campo correo';
65
66 -- si el resultado de la funcion locate es igual a 0 -> no se encuentra el carácter requerido
67 elseif(locate('@', @correo) = 0) then
68 signal sqlstate '02000'
69 set message_text = 'El correo debe contener al menos un @';
70 end if;
71
72 -- verificar posicion del .
73 -- si el resultado de la funcion locate es igual a 1 -> está al inicio
74 if (locate('.', @correo) = 1) then
75 signal sqlstate '02000'
76 set message_text = 'El . no debe ir al inicio del campo correo';
77
78 -- si el resultado de la funcion locate coincide con el largo de la cadena -> está al final
79 elseif(locate('.', @correo) = @largo) then
80 signal sqlstate '02000'
81 set message_text = 'El . no debe ir al final del campo correo';
82
83 -- si el resultado de la funcion locate es igual a 0 -> no se encuentra el carácter requerido
84 elseif(locate('.', @correo) = 0) then
85 signal sqlstate '02000'
86 set message_text = 'El correo debe contener al menos un .';
87 end if;
88 end;
89%% delimiter ;
90
91-- insert into tbl_contacto(correo_electronico) values('@dominio.cl');
92-- insert into tbl_contacto(correo_electronico) values('.correo@dominio.cl');
93-- insert into tbl_contacto(correo_electronico) values('correo@dominio.cl');
94
95delimiter %%
96create procedure pa_ingresar_contacto (
97 v_nombre varchar(32),
98 v_apellido_p varchar(32),
99 v_apellido_m varchar(32),
100 v_mail varchar(32),
101 v_telefono varchar(15))
102
103 begin
104 -- manejador tipo de dato del telefono
105 declare continue handler for 1366
106 select 'Campo telefono debe ser numerico';
107
108 -- manejador caracteres del telefono
109 declare continue handler for 1264
110 select 'Campo telefono debe contener como maximo 10 digitos';
111
112 insert into tbl_contacto
113 (nombre, apellido_p, apellido_m, correo_electronico, telefono)
114 values
115 (v_nombre, v_apellido_p, v_apellido_m, v_mail, v_telefono);
116 end;
117%% delimiter ;
118
119-- call pa_ingresar_contacto('ernesto', 'quezada', 'contreras', 'correo@dominio.cl', 'hola'); -- telefono como texto
120-- call pa_ingresar_contacto('ernesto', 'quezada', 'contreras', 'correo@dominio.cl', '9876543210'); -- telefono muy largo
121
122call pa_ingresar_contacto('ernesto', 'quezada', 'contreras', 'correo@dominio.cl', '987654321'); -- ok
123call pa_ingresar_contacto('ernesto', 'quezada', 'contreras', 'correo@dominio.cl', '987654321');
124call pa_ingresar_contacto('cristian', 'aviles', 'farias', 'correo@dominio.cl', '987654321');
125call pa_ingresar_contacto('elias', 'figueroa', 'contreras', 'correo@dominio.cl', '987654321');
126call pa_ingresar_contacto('alvaro', 'espinoza', 'farias', 'correo@dominio.cl', '987654321');
127
128delimiter $$
129create procedure pa_generar_iniciales()
130 begin
131 -- declarar variables locales
132 declare v_finalizado bit default 0;
133 declare v_id int;
134 declare v_nombre, v_apellido_p, v_apellido_m varchar(32);
135 declare v_inicial varchar(3);
136
137 -- declarar cursor + not found handler
138 declare cr_persona cursor for select id, nombre, apellido_p, apellido_m from tbl_contacto;
139 declare continue handler for not found set v_finalizado = 1;
140
141 -- abrir cursor
142 open cr_persona;
143 -- crear loop
144 i: loop
145 if v_finalizado then
146 leave i;
147 end if;
148
149 fetch cr_persona into v_id, v_nombre, v_apellido_p, v_apellido_m;
150 -- procesar
151 set v_inicial = fn_iniciales(v_nombre, v_apellido_p, v_apellido_m);
152 update tbl_contacto set iniciales = v_inicial where id = v_id;
153 end loop i;
154
155 -- cerrar cursor
156 close cr_persona;
157 end;
158$$ delimiter ;
159
160call pa_generar_iniciales();
161
162delimiter %%
163create function fn_formato_telefono(telefono varchar(10))
164 returns varchar(15) deterministic
165 begin
166 -- set @telefono = '987654321';
167 set @telefono = telefono;
168 set @telefono = insert(@telefono,4,0,' ');
169 set @telefono = insert(@telefono,8,0,' ');
170 return concat('+56 ',@telefono);
171 end;
172%% delimiter ;
173
174delimiter %%
175create view vs_contactos as
176 select
177 iniciales as Iniciales,
178 fn_formato_telefono(telefono) as Teléfono,
179 correo_electronico as Correo
180 from tbl_contacto;
181%% delimiter ;
182
183-- select * from vs_contactos;