1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
| DELIMITER //
CREATE OR REPLACE PROCEDURE pr_fix_auto_increment
(
IN nom_bdd VARCHAR(32),
OUT resultat VARCHAR(255)
)
BEGIN
DECLARE nom_table VARCHAR(48) DEFAULT NULL;
DECLARE nom_colonne VARCHAR(48) DEFAULT NULL;
DECLARE nb INT DEFAULT 0;
DECLARE autoinc INT DEFAULT NULL;
DECLARE max_id INT DEFAULT NULL;
DECLARE data_type VARCHAR(48) DEFAULT NULL;
DECLARE is_nullable CHAR(3) DEFAULT NULL;
DECLARE comment VARCHAR(255) DEFAULT NULL;
DECLARE extra VARCHAR(48) DEFAULT NULL;
DECLARE requete VARCHAR(255);
SELECT COUNT(*) INTO nb
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = nom_bdd;
IF nb = 0 THEN
SIGNAL SQLSTATE VALUE '45000'
SET MYSQL_ERRNO = 10027, MESSAGE_TEXT = 'Base de données inexistante';
END IF;
DECLARE liste_tables CURSOR FOR
SELECT TABLE_NAME, AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = nom_bdd;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN liste_tables;
read_loop: LOOP
FETCH liste_tables INTO nom_table, autoinc;
IF done THEN
LEAVE read_loop;
END IF;
DECLARE liste_colonnes CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE, EXTRA, COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = nom_bdd;
DECLARE CONTINUE HANDLER FOR NOT SET done2 = TRUE;
OPEN liste_colonnes;
read_loop2: LOOP
FETCH liste_colonnes INTO nom_colonne, data_type, extra, comment;
IF done2 THEN
LEAVE read_loop2;
END IF;
IF extra = 'auto_increment' THEN
SET requete = CONCAT('SELECT MAX(', nom_colonne, ') INTO max_id FROM ', nom_bdd, '.', nom_table);
PREPARE stmt FROM requete;
EXECUTE stmt;
DEALLOCATE stmt;
IF max_id > autoinc THEN
SET requete = CONCAT('ALTER TABLE ', nom_bdd, '.', nom_table,
' MODIFY ', nom_colonne, ' ', data_type,
' NOT NULL AUTO_INCREMENT COMMENT ', "'", comment, "', AUTO_INCREMENT = ", max_id + 1);
PREPARE stmt FROM requete;
EXECUTE stmt;
DEALLOCATE stmt;
END IF;
END IF;
END LOOP;
CLOSE liste_colonnes;
END LOOP;
SET resultat = CONCAT('Base de données ', nom_bdd, 'traitée.';
END; //
DELIMITER ; |
Partager