ASIX/M10/UF1/A06/E2

ASIX M10 UF1

Pt62. Cursors amb exportació

Guillem Solà i Boeck (host: PC05)

Enunciat

Solució

Procediment per afegir alumne

CREATE DEFINER=`root`@`localhost` PROCEDURE `afegir_alumne`(IN nomAlumne VARCHAR(50), IN dni INT)
BEGIN
	DECLARE lletra CHAR;
    DECLARE resto INT;
    DECLARE division INT;
    
    IF length(dni)=8 THEN
    
    SET division=dni/23;
    SET resto=dni-(division*23);
	
    SET lletra = SUBSTR("TRWAGMYFPDXBNJZSQVHLCKE", MOD(dni, 23) + 1, 1);
    
    INSERT INTO alumnes VALUES (CONCAT(dni,lletra),nomAlumne);
    
    END IF;
END

Procediment per afegir assignatura

CREATE DEFINER=`root`@`localhost` PROCEDURE `afegir_assignatura`(IN idAssignatura VARCHAR(5), IN nomAssignatura VARCHAR(50))
BEGIN
	INSERT INTO assignatures VALUES (idAssignatura, nomAssignatura);
END

Procediment per afegir nota a un alumne per a una assignatura

CREATE DEFINER=`root`@`localhost` PROCEDURE `afegir_nota`(IN dni INT, IN idAssignatura VARCHAR(5), nota DECIMAL (4,2))
BEGIN

	DECLARE lletra CHAR;
    DECLARE resto INT;
    DECLARE division INT;
	
    IF length(dni)=8 THEN
    
		SET division=dni/23;
		SET resto=dni-(division*23);
	
		SET lletra = SUBSTR("TRWAGMYFPDXBNJZSQVHLCKE", MOD(dni, 23) + 1, 1);
	
		INSERT INTO notes VALUES(idAssignatura, CONCAT(dni,lletra), nota);
    END IF;
END

Procediment per veure la nota mitjana per a cada alumne i exportar a un arxiu CSV

CREATE DEFINER=`root`@`localhost` PROCEDURE `veure_nota_mitjana`()
BEGIN
	DECLARE done INT DEFAULT FALSE;
    DECLARE alumne VARCHAR(9);
    DECLARE total DECIMAL(10,2);
    DECLARE recompteNotes INTEGER;
    DECLARE nomAlumne VARCHAR(50);
    
    DECLARE cAlumne CURSOR FOR
		SELECT idAlumne FROM alumnes;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    DROP TABLE IF EXISTS temporal;
    CREATE TABLE temporal (alumne VARCHAR(50),nota DECIMAL(4,2));
    
    OPEN cAlumne;
    bucle:LOOP
		FETCH cAlumne INTO alumne;
			IF done THEN LEAVE bucle; END IF;
		
        SELECT nom FROM alumnes WHERE idAlumne=alumne INTO nomAlumne;
                
        SELECT count(*) FROM notes WHERE idAlumne=alumne INTO recompteNotes;
                
        SELECT sum(nota) from notes WHERE idAlumne=alumne INTO total;
        
        INSERT INTO temporal VALUES (nomAlumne, total/recompteNotes);
		
    ITERATE bucle;
    END LOOP bucle;
    
    SELECT * FROM temporal; #Mostro per pantalla el resultat
        
        SELECT * FROM temporal INTO OUTFILE '/dades/export/notes.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';  
    
END

Execució

MariaDB [world]> call veure_nota_mitjana;
+---------+------+
| alumne  | nota |
+---------+------+
| Andreu  | 5.25 |
| Guillem | 8.25 |
+---------+------+
2 rows in set (0.55 sec)

Query OK, 2 rows affected (0.55 sec)

Contingut de notes.csv

"Andreu","5.25"
"Guillem","8.25"

ANNEXOS

Contingut taules

MariaDB [NotesInstitut]> select * from alumnes;
+-----------+---------+
| idAlumne  | nom     |
+-----------+---------+
| 47821459C | Andreu  |
| 47852734S | Guillem |
+-----------+---------+
2 rows in set (0.01 sec)

MariaDB [NotesInstitut]> select * from assignatures;
+---------------+----------------+
| idAssignatura | assignatura    |
+---------------+----------------+
| M10           | SGBD           |
| M2            | Bases de Dades |
+---------------+----------------+
2 rows in set (0.00 sec)

MariaDB [NotesInstitut]> select * from notes;
+---------------+-----------+-------+
| idAssignatura | idAlumne  | nota  |
+---------------+-----------+-------+
| M10           | 47821459C | 3.50  |
| M10           | 47852734S | 10.00 |
| M2            | 47821459C | 7.00  |
| M2            | 47852734S | 6.50  |
+---------------+-----------+-------+
4 rows in set (0.00 sec)