24/12/2009

ENSAO MYSQL++

Quelques concepts avancées de MySQL| Benabdallah Mohammed

MySQL ++

Benabdallah Mohammed

2

Le but de ce document est de tester quelque nouveaux concepts avancées de MySQL (à

partir de 5.0.2), pour cela vous devez installer MySQL (voir mes documents « Configuration de

MySQL Sous Ubuntu », ou « Configuration-de-MySQL Sous Windows » ).

Table des matières Environnement ........................................................................................................................................ 3

Création de la base de données .......................................................................................................... 3

Création de la table Etudiant : ............................................................................................................ 3

Déclencheurs (TRIGGER) ......................................................................................................................... 4

Exemple : ............................................................................................................................................. 4

Tester l’exemple : ................................................................................................................................ 4

Comparaison avec les Trigger Oracle : .................................................................................................... 5

Procédures et Fonctions .......................................................................................................................... 6

Exemple de procédure: ....................................................................................................................... 6

Exemple de fonction: ........................................................................................................................... 6

Invocation depuis JAVA ....................................................................................................................... 7

Curseurs ................................................................................................................................................... 8

Exemple de curseur : ........................................................................................................................... 8

Conclusion ............................................................................................................................................... 9

MySQL ++

Benabdallah Mohammed

3

Environnement

Création de la base de données

On se connecter à MySQL :

Veuillez vérifier que votre version de MySQL est supérieure ou égale à 5.1.0

On crée une base de données « test » sur laquelle nous allons travailler.

Création de la table Etudiant :

Nos exemples vont porter sur cette table :

CREATE TABLE IF NOT EXISTS `etudiant` (

`ID` INT(11) NOT NULL AUTO_INCREMENT ,

MySQL ++

Benabdallah Mohammed

4

`nom` VARCHAR (30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`prenom` VARCHAR (30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`age` VARCHAR (11) DEFAULT NULL ,

PRIMARY KEY (`ID`),

KEY `ID` (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMEN T=4 ;

Déclencheurs (TRIGGER) La création de déclencheurs nécessite le privilège SUPER, pour faire simple je me connecte avec le

super-utilisateur root.

Exemple : delimiter // CREATE TRIGGER ins_check BEFORE INSERT ON etudiant

FOR EACH ROW BEGIN

IF NEW.age < 0 THEN SET NEW.age = 0;

END IF; END //

delimiter ;

Tester l’exemple :

Il suffit d’insérer un enregistrement avec un âge négatif pour activer le trigger :

MySQL ++

Benabdallah Mohammed

5

Vous remarquer que l’âge est remis à zéro, c’est la preuve que le trigger s’est bien exécuté.

Comparaison avec les Trigger Oracle :

La syntaxe des Trigger sous MySQL est semblable à celle de Oracle :

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt DECLARE …… BEGIN …… …… END

Gère aussi les objets NEW et OLD ainsi que les curseurs.

La gestion d’exception est plus difficile en MySQL, en effet il faut déclarer un Handler pour la gérer

(voir Handler).

ATTENTION :

Vue que la définition des trigger utilise des « ; » points-virgules, penser à changer de

délimiteur avant d’écrire votre trigger (delimiter //) et n’oubliez pas de le restaurer après

(delimiter ;).

Pour plus d’informations consultez le site officiel Déclencheurs

Une comparaison entre les Trigger MySQL et Oracle a été publié par Oracle sur ce lien.

MySQL ++

Benabdallah Mohammed

6

Procédures et Fonctions

Exemple de procédure:

Dans la ligne de commande créez la procédure suivante.

delimiter |

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM etudiant;

END

|

delimiter ;

Pour tester la procédure

Exemple de fonction: SET GLOBAL log_bin_trust_function_creators = 1; delimiter |

CREATE FUNCTION bonjour (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Bonjour, ',s,'!'); |

SELECT bonjour('le monde')| delimiter ;

MySQL ++

Benabdallah Mohammed

7

Et pour tester la fonction :

Invocation depuis JAVA

Sous Eclipse créez un projet Java, ajouter la librairie mysql-connector-java-X.Y.Z-bin.jar et configurer

le (a l’aide de l’assistant c’est plus rapide)

Ensuite Exécuter le code suivant :

/** import java.sql.CallableStatement; /**

MySQL ++

Benabdallah Mohammed

8

* @author Mohammed Benabdallah * */ public class SimpleTestProcedure { /** * @param args * @throws ClassNotFoundException * @throws SQLException */ public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root",""); ResultSet rs; CallableStatement smt = con.prepareCall("call simpleproc(?)"); smt.registerOutParameter(1, java.sql.Types.INTEGER); rs = smt.executeQuery(); int x = smt.getInt(1); smt.execute(); System.out.println("Nombres de lignes affecé : "+x); } } Le résultat donne bien le nombre de lignes dans la table étudiant. L’appel de fonctions est exactement le même.

Curseurs

Exemple de curseur :

Nous allons ecrire une procédure qui parcourt la table « etudiant » et copie toutes les lignes dans la

table « etudiant2 » pour cela créer la table « etudiant2 ».

CREATE PROCEDURE DemoCurs() BEGIN

DECLARE d INT DEFAULT 0; DECLARE id, age INT; DECLARE nom,prenom VARCHAR (20); DECLARE cur CURSOR FOR SELECT * FROM test.etudiant; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1; DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1; OPEN cur;

lbl: LOOP IF d=1 THEN LEAVE lbl; END IF; IF NOT d=1 THEN FETCH cur INTO id,nom,prenom,age;

MySQL ++

Benabdallah Mohammed

9

INSERT INTO test.etudiant2 VALUES (id,nom,prenom,age); END IF; END LOOP;

CLOSE cur; END;

//

Pour tester notre procédure :

CALL DemoCurs() ;

Et on sélectionne tout les enregistrements de la table « etudiant2 » :

Conclusion

MySQL se reproche de quelques concepts de PL/SQL mais reste loin d’être un

concurrent à Oracle ou à PostgreSQL, personnellement je pense que MySQL est SGBDR facile

à mettre en place pour les étudiants qui souhaitent réviser leurs cours de base de données

(mais pas d’Administration Oracle !) sans pour autant faire souffrir leurs postes avec les

processus lourd d’Oracle.