5bw1eFVV

1USE caderneta;
2
3-- 1)
4CREATE TABLE IF NOT EXISTS `Caderneta`.`ListaFaltas` (
5	`Nr` INT NOT NULL,
6    PRIMARY KEY (`Nr`))
7ENGINE = InnoDB;
8
9-- DROP TABLE `Caderneta`.`ListasFaltas`;
10
11-- 2)
12DELIMITER $$
13CREATE PROCEDURE povoamentoListaFaltas(IN total INT)
14BEGIN
15	DECLARE adq char(1) DEFAULT 'N';
16	test_loop : LOOP
17		IF total=0 THEN
18			LEAVE test_loop;
19		END IF;
20		
21        SET adq = 'N';
22		
23        SELECT adquirido into adq FROM Cromo WHERE Nr = total;
24		
25        IF(adq='N') THEN
26			INSERT INTO ListaFaltas VALUES (total);
27		END IF;
28		
29        SET total = total - 1;
30	END LOOP;
31END $$
32
33CALL povoamentoListaFaltas(350);
34-- DROP PROCEDURE povoamentoListaFaltas;
35-- SELECT * FROM ListaFaltas;
36
37-- 3)
38DELIMITER %%
39CREATE PROCEDURE adquireCromo(IN id INT)
40BEGIN
41	DECLARE  Erro BOOLEAN DEFAULT 0;
42    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET Erro = 1;
43	START TRANSACTION;
44    
45    UPDATE Cromo SET adquirido = 'S' WHERE Cromo.Nr = id;
46    DELETE FROM ListaFaltas WHERE Nr = id;
47    
48    IF Erro = 1 
49	THEN ROLLBACK;
50	ELSE COMMIT;
51	END IF;
52END %%
53
54CALL adquireCromo(70);
55-- DROP PROCEDURE adquireCromo;
56-- SELECT * FROM ListaFaltas;
57
58-- 4)
59DELIMITER &&
60CREATE PROCEDURE desadquireCromo(IN id INT)
61BEGIN
62	DECLARE  Erro BOOLEAN DEFAULT 0;
63    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET Erro = 1;
64	START TRANSACTION;
65    
66    UPDATE Cromo SET adquirido = 'N' WHERE Cromo.Nr = id;
67    INSERT INTO ListaFaltas VALUES(id);
68    
69    IF Erro = 1 
70	THEN ROLLBACK;
71	ELSE COMMIT;
72	END IF;
73END &&
74
75CALL desadquireCromo(70);
76-- DROP PROCEDURE desadquireCromo;
77-- SELECT * FROM ListaFaltas;
78
79-- 5)
80DELIMITER //
81CREATE TRIGGER atualizaListaFaltas AFTER UPDATE ON Cromo
82	FOR EACH ROW
83    BEGIN
84		IF (NEW.Adquirido = 'S' AND OLD.Adquirido = 'N') THEN
85			DELETE FROM ListaFaltas WHERE Nr = OLD.Nr;
86        END IF;
87	END //
88    
89-- DROP TRIGGER atualizaListaFaltas;
90UPDATE Cromo SET Adquirido = 'S' WHERE Nr = 37;
91
92-- 6)
93DELIMITER ==
94CREATE PROCEDURE EquipaToda (IN id CHAR(3) , OUT todaEquipa VARCHAR(500))
95BEGIN
96	DECLARE v_finished INTEGER DEFAULT 0;
97    DECLARE v_treinador VARCHAR(50);
98    DECLARE v_nome_equipa VARCHAR(45);
99    DECLARE v_nome_jogador VARCHAR(75);
100    DECLARE v_posicao VARCHAR (20);
101    
102    DECLARE jogador_cursor CURSOR FOR
103    SELECT j.Nome, p.designacao
104		FROM Jogador j, Posicao P WHERE j.posicao = p.id AND j.Equipa = id;
105    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
106    
107	SELECT treinador, designacao INTO v_treinador, v_nome_equipa
108		FROM Equipa WHERE Equipa.id = id;
109        
110	SET todaEquipa = CONCAT(v_nome_equipa, ";" , v_treinador);
111    
112    OPEN jogador_cursor;
113    get_jogador : LOOP
114		IF v_finished = 1 THEN 
115			LEAVE get_jogador;
116		END IF;
117		FETCH jogador_cursor INTO v_nome_jogador, v_posicao;
118        SET todaEquipa = CONCAT(v_nome_jogador, ";" , v_posicao , ";" , todaEquipa);
119	END LOOP;
120    CLOSE jogador_cursor;
121END ==
122
123SET @listanomes = "";
124CALL EquipaToda("FCP",@listanomes);
125-- DROP PROCEDURE EquipaToda;
126SELECT @listanomes;