BthD5H54

1#EJERCICIO 1
2#CREAR LA BASE DE DATOS
3CREATE DATABASE db_examen;
4USE db_examen;
5CREATE TABLE articulo (
6id SMALLINT(6) UNSIGNED AUTO_INCREMENT,
7tipo CHAR(1) NOT NULL,
8nombre VARCHAR(20) NOT NULL,
9
10CONSTRAINT fk_articulo PRIMARY KEY (id)
11);
12
13CREATE TABLE numtipoproducto(
14
15tipo CHAR(1) NOT NULL,
16cont SMALLINT(6) NOT NULL,
17
18CONSTRAINT fk_tipo PRIMARY KEY (tipo)
19
20);
21
22#INSERTAR DATOS EN LAS BASES DE DATOS
23INSERT INTO articulo (tipo,nombre)
24VALUES ('A',"Valor1");
25
26INSERT INTO articulo (tipo,nombre)
27VALUES ('B',"Valor2");
28
29INSERT INTO articulo (tipo,nombre)
30VALUES ('B',"Valor3");
31
32INSERT INTO articulo (tipo,nombre)
33VALUES ('A',"Valor4");
34
35INSERT INTO articulo (tipo,nombre)
36VALUES ('A',"Valor5");
37
38INSERT INTO articulo (tipo,nombre)
39VALUES ('C',"Valor6");
40
41#TRIGGERS
42#TRIGGER INSERT
43DROP TRIGGER IF EXISTS trig_insert;
44DELIMITER //
45CREATE TRIGGER trig_insert
46BEFORE INSERT
47ON articulo
48FOR EACH ROW
49BEGIN
50	IF new.id < 0 THEN
51	 SET new.id=NULL;
52	END IF;
53END //
54DELIMITER ;
55
56/*SI HAGO UN INSERT EN UN CAMPO QUE ES AUTOINCREMENT, SI EL INSERT ES CON NULL
57COGE EL SIGUIENTE VALOR */
58
59
60#TRIGGER UPDATE
61DROP TRIGGER IF EXISTS trig_upd;
62DELIMITER //
63CREATE TRIGGER trig_upd
64BEFORE UPDATE
65ON articulo
66FOR EACH ROW
67BEGIN
68	IF new.id < 0 THEN
69	 SET new.id=old.id;
70	END IF;
71END //
72
73DELIMITER ;
74
75#EL TRIGGER DE BORRADO NO ES NECESARIO
76
77
78#EJERCICIO 2
79#Función que obtenga el número de artículos de un determinado tipo pasado por parámetro.
80DROP FUNCTION IF EXISTS ejer1;
81DELIMITER //
82CREATE FUNCTION ejer1 (var_tipo CHAR(1))
83RETURNS SMALLINT(6)
84READS SQL DATA
85DETERMINISTIC
86COMMENT 'Funcion que obtenga el numero de articulos de un
87determinado tipo'
88BEGIN
89DECLARE var1 SMALLINT(6) DEFAULT 0;
90SELECT cont INTO var1
91FROM numtipoproducto
92WHERE tipo=var_tipo;
93
94RETURN var1;
95END //
96DELIMITER ;
97
98SELECT ejer1('A') AS 'Numero de articulos';
99
100
101
102#EJERCICIO 3
103#Procedimiento que simule la función anterior obteniendo el número de artículos mediante un parámetro.
104DROP PROCEDURE IF EXISTS num_art;
105DELIMITER //
106CREATE PROCEDURE num_art (IN var_tipo CHAR(1), OUT total SMALLINT(6))
107COMMENT 'Procedimiento que obtenga el numero de articulos de un determinado tipo'
108READS SQL DATA
109DETERMINISTIC
110BEGIN
111
112  SELECT cont INTO total
113  FROM numtipoproducto
114  WHERE tipo=var_tipo;
115
116END //
117DELIMITER ;
118CALL num_art('A',@total);
119
120SELECT @total as 'Num articulos';
121
122
123
124#EJERCICIO 4
125#Realizar una función para obtener el número de caracteres que una cadena
126#suministrada como parámetro contiene a un carácter también proporcionado como parámetro.
127
128*******PRIMER EJEMPLO QUE ENVIE**********
129
130#DROP FUNCTION IF EXISTS ejercicio_3;
131DELIMITER //
132CREATE FUNCTION ejercicio_3 (var_palabra VARCHAR(40),var_letra CHAR(1))
133RETURNS SMALLINT
134READS SQL DATA
135SQL SECURITY INVOKER
136BEGIN
137DECLARE num_veces_letra INT DEFAULT 0;
138SELECT LENGTH(var_palabra) - LENGTH(REPLACE(var_palabra, var_letra, '')) INTO num_veces_letra;
139
140#Lo que hago en REPLACE es eliminar la letra dentro de la palabra que coincida con var_letra
141#entonces si tengo facilisimo y tengo una i , me elimina las "i" y se me queda la palabra faclsmo
142#la longitud de faclsmo es = 7
143# la longitud de facilisimo es 10
144# entonces 10 - 7 = 3, 3 veces se repite la i
145
146RETURN num_veces_letra;
147END //
148DELIMITER ;
149SELECT ejercicio_3('facilisimo','i') as 'Numero de veces que se repite una letra';
150
151
152**********SEGUNDO EJEMPLO*************
153DROP FUNCTION IF EXISTS ejercicio_3;
154DELIMITER //
155CREATE FUNCTION ejercicio_3 (var_palabra VARCHAR(40),var_letra CHAR(1))
156RETURNS SMALLINT
157READS SQL DATA
158SQL SECURITY INVOKER
159BEGIN
160
161DECLARE letra_repetida INT DEFAULT 0;
162DECLARE var_cont INT DEFAULT 0;
163DECLARE long_palabra INT DEFAULT 0;
164
165SELECT CHAR_LENGTH(var_palabra) INTO long_palabra;
166
167WHILE var_cont <= long_palabra DO
168IF (SUBSTRING(var_palabra,var_cont,1) = var_letra) THEN
169SET letra_repetida= letra_repetida + 1;
170END IF;
171SET var_cont= var_cont +1;
172END WHILE;
173RETURN letra_repetida;
174END //
175DELIMITER ;
176SELECT ejercicio_3('facilisimo','i') as 'Numero de veces que se repite una letra';
177
178
179
180#EJERCICIO 5
181#Función que obtiene el disponible de un libro a partir de un id_libro.
182DROP FUNCTION IF EXISTS ejer_disponibles ;
183DELIMITER //
184CREATE FUNCTION ejer_disponibles (var_id SMALLINT(5)UNSIGNED)
185RETURNS ENUM ('S','N')
186READS SQL DATA
187DETERMINISTIC
188COMMENT 'Funcion que obtenga el disponible de un libro a partir de un id_libro'
189BEGIN
190DECLARE dispo ENUM ('S','N');
191
192SELECT disponible INTO dispo
193FROM libros
194WHERE id_libro=var_id;
195
196RETURN dispo;
197END //
198DELIMITER ;
199
200SELECT ejer_disponibles(5) AS 'Disponibilidad libro';
201
202
203#EJERCICIO 6
204#Procedimiento que a partir de un id_libro modifica el disponible con un determinado valor pasado por parámetro.
205DROP PROCEDURE IF EXISTS proc_edit;
206DELIMITER //
207CREATE PROCEDURE proc_edit (IN var_id SMALLINT(5)UNSIGNED, var_dispo enum('S','N'))
208COMMENT 'Procedimiento que obtenga el disponible de un libro a partir de un id_libro'
209MODIFIES SQL DATA
210DETERMINISTIC
211BEGIN
212UPDATE libros SET disponible=var_dispo
213WHERE id_libro=var_id;
214END //
215DELIMITER ;
216CALL proc_edit(5,"N");
217
218#EJERCICIO 7
219#Función booleana que nos indique si un determinado id_libro suministrado como parámetro existe dentro de la tabla libros.
220DROP FUNCTION IF EXISTS id_existe;
221DELIMITER //
222CREATE FUNCTION id_existe (var_id SMALLINT(5)UNSIGNED)
223RETURNS BIT
224READS SQL DATA
225DETERMINISTIC
226COMMENT 'Funcion que diga si un id_libro existe o no '
227BEGIN
228DECLARE cont BIT DEFAULT 0;
229
230SELECT COUNT(id_libro) INTO cont
231FROM libros
232WHERE id_libro=var_id;
233RETURN cont ;
234
235END //
236DELIMITER ;
237
238
239SELECT id_existe(1) AS 'Existe o no';
240
241#EJERCICIO 8
242#Procedimiento para realizar un préstamo.
243DROP PROCEDURE IF EXISTS proc_prestamo;
244DELIMITER //
245CREATE PROCEDURE proc_prestamo (IN var_nif CHAR(9), var_idlibro SMALLINT(5) UNSIGNED)
246COMMENT 'Procedimiento que simule un prestamo'
247MODIFIES SQL DATA
248DETERMINISTIC
249BEGIN
250IF id_existe(var_idlibro) <> 0 AND nif_existe(var_nif) <> 0 AND ejer_disponibles(var_idlibro) = 'S' THEN
251#INSERTAR PRESTAMO
252INSERT INTO prestamos (nif, id_libro, fecha_prestamo)
253VALUES (var_nif, var_idlibro,CURRENT_DATE());
254#LLAMADA AL PROCEDIMIENTO PARA QUE MODIFIQUE LA DISPONIBILIDAD DEL LIBRO A N
255CALL proc_edit(var_idlibro,'N');
256
257ELSE
258SELECT 'ERROR' as 'INFO';
259
260END IF;
261END //
262DELIMITER ;
263CALL proc_prestamo('62877137F',5);
264
265
266#EJERCICIO 9
267#Realizar un cursor para simular la siguiente instrucción sin utilizar la función COUNT:
268/*
269INSERT INTO numTipoProducto
270SELECT tipo,COUNT(*)
271FROM articulo
272GROUP BY tipo;
273*/
274DROP PROCEDURE IF EXISTS p_cursor;
275DELIMITER //
276CREATE PROCEDURE p_cursor()
277READS SQL DATA
278SQL SECURITY INVOKER
279BEGIN
280
281DECLARE v_tipo CHAR DEFAULT "";
282DECLARE v_grupo CHAR DEFAULT "";
283DECLARE contador INT DEFAULT 0;
284
285DECLARE v_eof BIT DEFAULT 0;
286
287DECLARE n_cur CURSOR FOR
288SELECT tipo
289FROM articulo
290GROUP BY tipo;
291
292DECLARE n_cur2 CURSOR FOR
293SELECT tipo
294FROM articulo
295ORDER BY tipo;
296
297DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_eof=1;
298OPEN n_cur;
299OPEN n_cur2;
300SET v_eof=0;
301FETCH n_cur INTO v_grupo;
302FETCH n_cur2 INTO v_tipo;
303
304WHILE v_eof=0 DO
305WHILE v_tipo = v_grupo AND v_eof=0 DO
306SELECT tipo FROM articulo WHERE tipo=v_tipo;
307SELECT FOUND_ROWS() INTO contador;
308FETCH n_cur2 INTO v_tipo;
309END WHILE;
310INSERT INTO numtipoproducto (tipo,cont) VALUES (v_grupo,contador);
311SET contador=0;
312FETCH n_cur INTO v_grupo;
313END WHILE;
314CLOSE n_cur;
315CLOSE n_cur2;
316END //
317DELIMITER ;
318
319CALL p_cursor();