· 5 years ago · Mar 27, 2020, 11:22 PM
1-- US DE LA BASE DE DADES
2delimiter ;
3
4use empresa;
5
6-- DROPS DE PROCEDIMENTS I COLUMNES
7drop procedure if exists EMPLEADOS;
8drop procedure if exists appex;
9drop procedure if exists permdept;
10drop procedure if exists update_cliente;
11drop procedure if exists insert_cliente;
12drop procedure if exists delete_cliente;
13drop procedure if exists update_producto;
14drop procedure if exists insert_producto;
15drop procedure if exists delete_producto;
16drop procedure if exists update_comanda;
17drop procedure if exists insert_comanda;
18drop procedure if exists delete_comanda;
19drop procedure if exists update_detalle;
20drop procedure if exists insert_detalle;
21drop procedure if exists delete_detalle;
22DROP USER if exists CAP_CONTABILIDAD;
23DROP USER if exists CAP_INVESTIGACION;
24DROP USER if exists CAP_PRODUCCION;
25DROP USER if exists CAP_VENTAS;
26drop procedure if exists columnes_esborrat;
27drop procedure if exists permcaps;
28drop procedure if exists duplicado;
29drop procedure if exists alta;
30Drop function if exists sumimport;
31DROP TABLE if exists registres;
32
33-- CAPS I PERMISOS CAPS
34CREATE USER IF NOT EXISTS CAP_CONTABILIDAD IDENTIFIED BY 'P@ssw0rddd';
35CREATE USER IF NOT EXISTS CAP_INVESTIGACION IDENTIFIED BY 'P@ssw0rddd';
36CREATE USER IF NOT EXISTS CAP_PRODUCCION IDENTIFIED BY 'P@ssw0rddd';
37CREATE USER IF NOT EXISTS CAP_VENTAS IDENTIFIED BY 'P@ssw0rddd';
38
39
40-- CREAR REGISTRES TAULES I VISTES
41CREATE TABLE if not exists registres (
42 NUM int(6) auto_increment primary key,
43 COGNOM varchar(30) not null,
44 ACT varchar(60),
45 DATA_CRE timestamp default current_timestamp
46);
47
48-- CREACIÓ DE PROCEDIMENTS
49delimiter //
50
51create procedure columnes_esborrat ()
52begin
53declare continue handler for 1060
54select "La columna 'esborrat' ja existeix!";
55
56ALTER TABLE empresa.PRODUCTO ADD COLUMN esborrat boolean default 0;
57ALTER TABLE empresa.DETALLE ADD COLUMN esborrat boolean default 0;
58ALTER TABLE empresa.CLIENTE ADD COLUMN esborrat boolean default 0;
59ALTER TABLE empresa.EMP ADD COLUMN esborrat boolean default 0;
60ALTER TABLE empresa.COMANDA ADD COLUMN esborrat boolean default 0;
61ALTER TABLE empresa.DEPT ADD COLUMN esborrat boolean default 0;
62end//
63
64delimiter ;
65
66create or replace view VEMP as select EMP_NO,APELLIDO,OFICIO,JEFE,FECHA_ALTA,DEPT_NO from empresa.EMP where esborrat=0;
67create or replace view VCLIENTE as select * from empresa.CLIENTE where esborrat=0;
68create or replace view VCOMANDA as select * from empresa.COMANDA where esborrat=0;
69create or replace view VDETALLE as select * from empresa.DETALLE where esborrat=0;
70create or replace view VDEPT as select * from empresa.DEPT where esborrat=0;
71create or replace view VPRODUCTO as select * from empresa.PRODUCTO where esborrat=0;
72
73delimiter //
74
75create procedure appex (in cgn varchar(45))
76BEGIN
77declare continue handler for 1406
78select "El nombre té molts més caràcters dels necessàris";
79
80declare continue handler for 1264
81select "El nombre té molts més caràcters dels necessàris";
82
83declare continue handler for 1001
84select "El cursor és invàlid";
85
86declare continue handler for 6500
87select "S'ha corromput la memòria!";
88
89set @sql := CONCAT('create user if not exists ',cgn,' IDENTIFIED BY "P@ssword123456"');
90prepare stmt23 from @sql;
91execute stmt23;
92deallocate prepare stmt23;
93END //
94
95-- UPDATE dades de client
96create procedure update_cliente (in CLIENTE_COD1 int(4),in NOMBRE1 varchar(45),in DIRECCIÓN1 varchar (40) ,in CIUDAD1 varchar(30),in ESTADO1 varchar(2),
97in CODI_POSTAL1 varchar(9),in AREA1 smallint(3),in TELEFONO1 varchar(9),in REPR_COD1 smallint(3),in LIMIT_CREDITO1 decimal(9,2),in OBSERVACIONES1 text)
98BEGIN
99declare continue handler for 1406
100SELECT "El nombre té molts més caràcters dels necessàris";
101
102declare continue handler for 1264
103SELECT "El nombre té molts més caràcters dels necessàris";
104
105UPDATE CLIENTE SET CLIENTE_COD=CLIENTE_COD,NOMBRE=NOMBRE1,DIRECCIÓN=DIRECCIÓN1,CIUDAD=CIUDAD1,ESTADO=ESTADO1,CODI_POSTAL=CODI_POSTAL1,AREA=AREA1,TELEFONO=TELEFONO1,
106REPR_COD=REPR_COD1,LIMIT_CREDITO=LIMIT_CREDITO1,OBSERVACIONES=OBSERVACIONES1 WHERE CLIENTE_COD=CLIENTE_COD1;
107INSERT into registres (COGNOM,ACT) values (USER(),'ACTUALITZACIÓ CLIENT');
108END //
109
110-- INSERT dades de client
111create procedure insert_cliente (in CLIENTE_COD1 int(4),in NOMBRE varchar(45),in DIRECCIÓN varchar (40) ,in CIUDAD varchar(30),in ESTADO varchar(2),
112in CODI_POSTAL varchar(9),in AREA smallint(3),in TELEFONO varchar(9),in REPR_COD smallint(3),in LIMIT_CREDITO decimal(9,2),in OBSERVACIONES text)
113BEGIN
114declare continue handler for 1406
115SELECT "El nombre té molts més caràcters dels necessàris";
116
117declare continue handler for 1264
118SELECT "El nombre té molts més caràcters dels necessàris";
119
120INSERT into CLIENTE (CLIENTE_COD,NOMBRE,DIRECCIÓN,CIUDAD,ESTADO,CODI_POSTAL,AREA,TELEFONO,REPR_COD,LIMIT_CREDITO,OBSERVACIONES) VALUES (CLIENTE_COD1,NOMBRE,DIRECCIÓN,CIUDAD,ESTADO,CODI_POSTAL,AREA,TELEFONO,REPR_COD,LIMIT_CREDITO,OBSERVACIONES);
121INSERT into registres (COGNOM,ACT) values (USER(),'INSERCIÓ CLIENT');
122END //
123
124-- DELETE dades de client
125
126create procedure delete_cliente (in CLIENTE_COD1 int(4))
127BEGIN
128declare continue handler for 1406
129SELECT "El nombre té molts més caràcters dels necessàris";
130
131declare continue handler for 1264
132SELECT "El nombre té molts més caràcters dels necessàris";
133
134UPDATE CLIENTE set esborrat=1 WHERE CLIENTE_COD=CLIENTE_COD1;
135INSERT into registres (COGNOM,ACT) values (USER(),'SUPRESSIÓ CLIENT');
136END //
137
138-- UPDATE dades de producto
139create procedure update_producto (in PROD_NUM1 int(6),in DESCRIPCIÓN1 varchar(30))
140BEGIN
141declare continue handler for 1406
142SELECT "El nombre té molts més caràcters dels necessàris";
143
144declare continue handler for 1264
145SELECT "El nombre té molts més caràcters dels necessàris";
146
147UPDATE PRODUCTO SET PROD_NUM=PROD_NUM,DESCRIPCIÓN=DESCRIPCIÓN1 WHERE PROD_NUM=PROD_NUM1;
148INSERT into registres (COGNOM,ACT) values (USER(),'ACTUALITZACIÓ PRODUCTE');
149END //
150
151-- INSERT dades de producto
152create procedure insert_producto (in PROD_NUM1 int(6),in DESCRIPCIÓN varchar(30))
153BEGIN
154declare continue handler for 1406
155SELECT "El nombre té molts més caràcters dels necessàris";
156
157declare continue handler for 1264
158SELECT "El nombre té molts més caràcters dels necessàris";
159
160INSERT into PRODUCTO (PROD_NUM, DESCRIPCIÓN) VALUES (PROD_NUM1, DESCRIPCIÓN);
161INSERT into registres (COGNOM,ACT) values (USER(),'INSERT PRODUCTE');
162END //
163
164-- DELETE dades de producto
165create procedure delete_producto (in PROD_NUM1 int(6))
166BEGIN
167declare continue handler for 1406
168SELECT "El nombre té molts més caràcters dels necessàris";
169
170declare continue handler for 1264
171SELECT "El nombre té molts més caràcters dels necessàris";
172
173UPDATE PRODUCTO set esborrat=1 WHERE PROD_NUM=PROD_NUM1;
174INSERT into registres (COGNOM,ACT) values (USER(),'SUPRESSIÓ PRODUCTE');
175END //
176
177-- UPDATE COMANDA
178create procedure update_comanda (in COM_NUM1 smallint(4),in COM_FECHA1 date,in COM_TIPO1 char(1),in CLIENTE_COD1 int(6) , in FECHA_TRAM1 date ,in TOTAL1 decimal(8,2))
179BEGIN
180declare continue handler for 1406
181SELECT "El nombre té molts més caràcters dels necessàris";
182
183declare continue handler for 1264
184SELECT "El nombre té molts més caràcters dels necessàris";
185
186UPDATE COMANDA SET COM_NUM=COM_NUM,COM_FECHA=COM_FECHA1,COM_TIPO=COM_TIPO1,CLIENTE_COD=CLIENTE_COD1,FECHA_TRAM=FECHA_TRAM1,TOTAL=TOTAL1 WHERE COM_NUM1=COM_NUM1;
187INSERT into registres (COGNOM,ACT) values (USER(),'ACTUALITZACIÓ COMANDA');
188END //
189
190-- INSERT COMANDA
191create procedure insert_comanda (in COM_NUM1 smallint(4),in COM_FECHA date,in COM_TIPO char(1),in CLIENTE_COD int(6),in FECHA_TRAM date,in TOTAL decimal(8,2))
192BEGIN
193declare continue handler for 1406
194SELECT "El nombre té molts més caràcters dels necessàris";
195
196declare continue handler for 1264
197SELECT "El nombre té molts més caràcters dels necessàris";
198
199INSERT INTO COMANDA (COM_NUM,COM_FECHA,COM_TIPO,CLIENTE_COD,FECHA_TRAM,TOTAL) values (COM_NUM1,COM_FECHA,COM_TIPO,CLIENTE_COD,FECHA_TRAM,TOTAL);
200INSERT into registres (COGNOM,ACT) values (USER(),'INSERT COMANDA');
201END //
202
203-- DELETE COMANDA.
204
205create procedure delete_comanda (in COM_NUM1 smallint(4))
206BEGIN
207declare continue handler for 1406
208SELECT "El nombre té molts més caràcters dels necessàris";
209
210declare continue handler for 1264
211SELECT "El nombre té molts més caràcters dels necessàris";
212
213UPDATE COMANDA set esborrat=1 WHERE COM_NUM=COM_NUM1;
214INSERT into registres (COGNOM,ACT) values (USER(),'SUPRESSIÓ COMANDA');
215END //
216
217-- UPDATE DETALLE
218create procedure update_detalle (in COM_NUM1 smallint(4),in DETALLE_NUM1 smallint(4),in PROD_NUM1 int(6),in PRECIO_VENTA1 decimal(8,2),in CANTIDAD1 int(8),in IMPORTE1 decimal(8,2))
219BEGIN
220declare continue handler for 1406
221SELECT "El nombre té molts més caràcters dels necessàris";
222
223declare continue handler for 1264
224SELECT "El nombre té molts més caràcters dels necessàris";
225
226UPDATE DETALLE SET COM_NUM1=COM_NUM1,DETALLE_NUM=DETALLE_NUM1,PROD_NUM=PROD_NUM1,PRECIO_VENTA=PRECIO_VENTA1,CANTIDAD=CANTIDAD1,IMPORTE=IMPORTE1 WHERE COM_NUM1=COM_NUM1;
227INSERT into registres (COGNOM,ACT) values (USER(),'ACTUALITZACIÓ DETALLE');
228END //
229
230-- INSERT DETALLE
231create procedure insert_detalle (in COM_NUM1 smallint(4),in DETALLE_NUM smallint(4),in PROD_NUM int(6),in PRECIO_VENTA decimal(8,2),in CANTIDAD int(8),in IMPORTE decimal(8,2))
232BEGIN
233declare continue handler for 1406
234SELECT "El nombre té molts més caràcters dels necessàris";
235
236declare continue handler for 1264
237SELECT "El nombre té molts més caràcters dels necessàris";
238
239INSERT INTO DETALLE (COM_NUM,DETALLE_NUM,PROD_NUM,PRECIO_VENTA,CANTIDAD,IMPORTE) values (COM_NUM1,DETALLE_NUM,PROD_NUM,PRECIO_VENTA,CANTIDAD,IMPORTE);
240INSERT into registres (COGNOM,ACT) values (USER(),'INSERT DETALLE');
241END //
242
243-- DELETE DETALLE.
244create procedure delete_detalle (in COM_NUM1 smallint(4))
245BEGIN
246declare continue handler for 1406
247SELECT "El nombre té molts més caràcters dels necessàris";
248
249declare continue handler for 1264
250SELECT "El nombre té molts més caràcters dels necessàris";
251
252UPDATE DETALLE set esborrat=1 where COM_NUM=COM_NUM1;
253INSERT into registres (COGNOM,ACT) values (USER(),'SUPRESSIÓ DETALLE');
254END //
255
256-- CLASIFICACIÓ PERMISOS
257create procedure permdept (in cgn varchar(45))
258BEGIN
259declare dept int (2);
260Select DEPT_NO INTO dept FROM EMP where APELLIDO=cgn;
261if (dept=10) then
262 Set @select_emp := CONCAT('GRANT Select on empresa.VEMP TO ',cgn);
263 Set @select_comanda := CONCAT('GRANT Select on empresa.VCOMANDA TO ',cgn);
264 Set @select_detalle := CONCAT('GRANT Select on empresa.VDETALLE TO ',cgn);
265 Set @update_comanda := CONCAT('GRANT EXECUTE on procedure empresa.update_comanda TO ',cgn);
266 Set @insert_comanda := CONCAT('GRANT EXECUTE on procedure empresa.insert_comanda TO ',cgn);
267 Set @delete_comanda := CONCAT('GRANT EXECUTE on procedure empresa.delete_comanda TO ',cgn);
268 Set @update_detalle := CONCAT('GRANT EXECUTE on procedure empresa.update_detalle TO ',cgn);
269 Set @insert_detalle := CONCAT('GRANT EXECUTE on procedure empresa.insert_detalle TO ',cgn);
270 Set @delete_detalle := CONCAT('GRANT EXECUTE on procedure empresa.delete_detalle TO ',cgn);
271
272 prepare select_emp1 from @select_emp;
273 execute select_emp1;
274 deallocate prepare select_emp1;
275
276 prepare update_comanda1 from @update_comanda;
277 execute update_comanda1;
278 deallocate prepare update_comanda1;
279
280 prepare insert_comanda1 from @insert_comanda;
281 execute insert_comanda1;
282 deallocate prepare insert_comanda1;
283
284 prepare delete_comanda1 from @delete_comanda;
285 execute delete_comanda1;
286 deallocate prepare delete_comanda1;
287
288 prepare update_detalle1 from @update_detalle;
289 execute update_detalle1;
290 deallocate prepare update_detalle1;
291
292 prepare insert_detalle1 from @insert_detalle;
293 execute insert_detalle1;
294 deallocate prepare insert_detalle1;
295
296 prepare delete_detalle1 from @delete_detalle;
297 execute delete_detalle1;
298 deallocate prepare delete_detalle1;
299
300 prepare select_detalle1 from @select_detalle;
301 execute select_detalle1;
302 deallocate prepare select_detalle1;
303
304 prepare select_comanda1 from @select_comanda;
305 execute select_comanda1;
306 deallocate prepare select_comanda1 ;
307
308end if;
309if (dept=20) then
310 Set @select_dept := CONCAT('GRANT Select on empresa.VDEPT TO ',cgn);
311 Set @select_emp := CONCAT('GRANT Select on empresa.VEMP TO ',cgn);
312 Set @select_cliente := CONCAT('GRANT Select on empresa.VCLIENTE TO ',cgn);
313 Set @select_comanda := CONCAT('GRANT Select on empresa.VCOMANDA TO ',cgn);
314 Set @select_detalle := CONCAT('GRANT Select on empresa.VDETALLE TO ',cgn);
315 Set @select_producto := CONCAT('GRANT Select on empresa.VPRODUCTO TO ',cgn);
316
317 prepare select_emp1 from @select_emp;
318 execute select_emp1;
319 deallocate prepare select_emp1;
320
321 prepare select_dept1 from @select_dept;
322 execute select_dept1;
323 deallocate prepare select_dept1;
324
325 prepare select_cliente1 from @select_cliente;
326 execute select_cliente1;
327 deallocate prepare select_cliente1;
328
329 prepare select_detalle1 from @select_detalle;
330 execute select_detalle1;
331 deallocate prepare select_detalle1;
332
333 prepare select_comanda1 from @select_comanda;
334 execute select_comanda1;
335 deallocate prepare select_comanda1 ;
336
337 prepare select_producto1 from @select_producto;
338 execute select_producto1;
339 deallocate prepare select_producto1;
340
341
342
343end if;
344if (dept=30) then
345 Set @select_cliente := CONCAT('GRANT Select on empresa.VCLIENTE TO ',cgn);
346 Set @update_cliente := CONCAT('GRANT EXECUTE on procedure empresa.update_cliente TO ',cgn);
347 Set @insert_cliente := CONCAT('GRANT EXECUTE on procedure empresa.insert_cliente TO ',cgn);
348 Set @delete_cliente := CONCAT('GRANT EXECUTE on procedure empresa.delete_cliente TO ',cgn);
349 Set @select_comanda := CONCAT('GRANT Select on empresa.VCOMANDA TO ',cgn);
350 Set @select_detalle := CONCAT('GRANT Select on empresa.VDETALLE TO ',cgn);
351
352 prepare select_cliente1 from @select_cliente;
353 execute select_cliente1;
354 deallocate prepare select_cliente1;
355
356 prepare update_cliente1 from @update_cliente;
357 execute update_cliente1;
358 deallocate prepare update_cliente1;
359
360 prepare insert_cliente1 from @insert_cliente;
361 execute insert_cliente1;
362 deallocate prepare insert_cliente1;
363
364 prepare delete_cliente1 from @delete_cliente;
365 execute delete_cliente1;
366 deallocate prepare delete_cliente1;
367
368 prepare select_detalle1 from @select_detalle;
369 execute select_detalle1;
370 deallocate prepare select_detalle1;
371
372 prepare select_comanda1 from @select_comanda;
373 execute select_comanda1;
374 deallocate prepare select_comanda1 ;
375
376end if;
377if (dept=40) then
378 Set @select_producto := CONCAT('GRANT Select on empresa.VPRODUCTO TO ',cgn);
379 Set @update_producto := CONCAT('GRANT EXECUTE on procedure empresa.update_producto TO ',cgn);
380 Set @insert_producto := CONCAT('GRANT EXECUTE on procedure empresa.insert_producto TO ',cgn);
381 Set @delete_producto := CONCAT('GRANT EXECUTE on procedure empresa.delete_producto TO ',cgn);
382
383 prepare select_producto1 from @select_producto;
384 execute select_producto1;
385 deallocate prepare select_producto1;
386
387 prepare update_producto1 from @update_producto;
388 execute update_producto1;
389 deallocate prepare update_producto1;
390
391 prepare insert_producto1 from @insert_producto;
392 execute insert_producto1;
393 deallocate prepare insert_producto1;
394
395 prepare delete_producto1 from @delete_producto;
396 execute delete_producto1;
397 deallocate prepare delete_producto1;
398end if;
399END //
400
401-- DUPLICATS USUARIS
402create procedure duplicado (in origen varchar(20),in cgn varchar(20))
403BEGIN
404declare maximo smallint (4);
405declare continue handler for 1406
406select "El nombre té molts més caràcters dels necessàris";
407
408declare continue handler for 1264
409select "El nombre té molts més caràcters dels necessàris";
410
411declare continue handler for 1001
412select "El cursor és invàlid";
413
414declare continue handler for 6500
415select "S'ha corromput la memòria!";
416
417Create table if not exists temporal like EMP;
418select max(EMP_NO)+1 into maximo from EMP;
419insert into temporal (EMP_NO,APELLIDO,OFICIO,JEFE,FECHA_ALTA,SALARIO,COMISIÓN,DEPT_NO) select maximo,APELLIDO,OFICIO,JEFE,FECHA_ALTA,SALARIO,COMISIÓN,DEPT_NO from EMP where APELLIDO=origen;
420update temporal set APELLIDO=cgn;
421insert into EMP select * from temporal;
422drop table temporal;
423END //
424
425-- ALTA USUARIS
426create procedure alta (in EMP_NO1 smallint(4),in APELLIDO1 varchar(10),in OFICIO1 varchar(10),in JEFE1 smallint(4),in FECHA_ALTA1 date,in SALARIO1 int(10),in COMISIÓN1 int(10),in DEPT_NO1 tinyint(2))
427BEGIN
428declare continue handler for 1406
429select "El nombre té molts més caràcters dels necessàris";
430
431declare continue handler for 1264
432select "El nombre té molts més caràcters dels necessàris";
433
434declare continue handler for 1001
435select "El cursor és invàlid";
436
437declare continue handler for 6500
438select "S'ha corromput la memòria!";
439
440insert into EMP (EMP_NO,APELLIDO,OFICIO,JEFE,FECHA_ALTA,SALARIO,COMISIÓN,DEPT_NO) values (EMP_NO1,APELLIDO1,OFICIO1,JEFE1,FECHA_ALTA1,SALARIO1,COMISIÓN1,DEPT_NO1);
441call EMPLEADOS();
442END //
443
444delimiter ;
445
446Grant execute on procedure empresa.appex to CAP_CONTABILIDAD;
447Grant execute on procedure empresa.appex to CAP_INVESTIGACION;
448Grant execute on procedure empresa.appex to CAP_PRODUCCION;
449Grant execute on procedure empresa.appex to CAP_VENTAS;
450
451Grant execute on procedure empresa.duplicado to CAP_CONTABILIDAD;
452Grant execute on procedure empresa.duplicado to CAP_INVESTIGACION;
453Grant execute on procedure empresa.duplicado to CAP_PRODUCCION;
454Grant execute on procedure empresa.duplicado to CAP_VENTAS;
455
456Grant select,insert,update,delete on empresa.* to CAP_CONTABILIDAD;
457Grant select,insert,update,delete on empresa.* to CAP_INVESTIGACION;
458Grant select,insert,update,delete on empresa.* to CAP_PRODUCCION;
459Grant select,insert,update,delete on empresa.* to CAP_VENTAS;
460
461delimiter //
462
463-- GESTIO COMANDES
464Create function sumimport (com int) RETURNS FLOAT(8,2)
465DETERMINISTIC
466BEGIN
467declare Var1 FLOAT(8,2);
468Set Var1 =(select sum(IMPORTE) from DETALLE where COM_NUM=com);
469update COMANDA set TOTAL = Var1 where COM_NUM=com;
470Return Var1;
471END //
472
473-- PROCEDIMENT EMPLEATS FINAL
474CREATE procedure EMPLEADOS ()
475BEGIN
476declare cgn varchar (20);
477declare finalitza int default false;
478declare apart1 cursor for select APELLIDO FROM EMP;
479declare continue handler for NOT FOUND set finalitza=true;
480declare exit handler for sqlexception rollback;
481declare exit handler for sqlwarning rollback;
482declare continue handler for 1406
483select "El nombre té molts més caràcters dels necessàris";
484
485declare continue handler for 1264
486select "El nombre té molts més caràcters dels necessàris";
487
488declare continue handler for 1001
489select "El cursor és invàlid";
490
491declare continue handler for 6500
492select "S'ha corromput la memòria!";
493
494declare continue handler for 6504
495select "El cursor retorna un valor incompatible";
496
497declare continue handler for 6501
498select "Cursor ja obert!";
499
500open apart1;
501Bucle: LOOP
502 fetch apart1 into cgn;
503 if finalitza=true then
504 leave bucle;
505 end if;
506 call appex(cgn);
507 call permdept(cgn);
508 end LOOP bucle;
509
510close apart1;
511end //
512delimiter ;
513call columnes_esborrat();
514call empleats();