Programmation Impérative et SGBD :

PL/SQL et JDBC
Procedural Language / Structured Query Language
(Langage propriétaire Oracle)
Java Database Connectivity
(Open Source)
4A STI 2014/2015
B.NGUYEN
(certains transparents traduits de Silberchatz et al.)
Introduction
 Langages procéduraux dédiés (1)
• Comment passer une commande dans la base telle que :
PROCEDURE COMM
Si le client n’est pas encore dans la base
Insérer les informations du client dans la table client
Si l’article est disponible dans la quantité commandée
ET le livreur disponible à la date de livraison désirée
Insérer sa commande dans la base
Sinon abandonner la commande

• Impossible en SQL pur (SQL n’est pas un langage Turing

complet : son pouvoir d’expressivité se limité à la logique du 1er
ordre)
– Pas de structure de contrôle : itérations, tests …
– Besoin d’un langage complet pour programmer des actions sur les BD

• Avec un langage compréhensible sur le client (C, Java, …)

– Qui puisse parler avec la BD (avec SQL grâce à ODBC, JDBC, …)

• Avec un langage dédié au serveur (PL/SQL serveur Oracle)

– Types identiques aux types SQL
– Facilités de récupération/lecture des résultats de requêtes
Langages procéduraux dédiés (2)
• Exécution client (envoie de SQL par JDBC, etc…)

Client
Moteur Serveur
JDBC BD
SQL

…
COMM
Java
Serveur Oracle

• Exécution serveur (envoie de procédure anonyme)

COMM
Client PL/SQL
Moteur Serveur
BD
PL/SQL SQL
Résultat exécution
Serveur Oracle
Langages procéduraux dédiés (3)
• Stockage de procédures sur le serveur
– Chargement et compilation de la procédure sur le serveur
– Lancement à distance par appel de procédure (EXECUTE)

COMM
Client PL/SQL
Execute COMM
Moteur Serveur
BD
PL/SQL SQL
Résultat exécution

Serveur Oracle

– Stocké comme un objet base de données

• Le créateur à les droits d’exécuter, modifier, re-compiler la procédure
• Partage les droits avec d’autres (GRANT/REVOKE EXECUTE)
 Base Exemple
create table employe
(nom VARCHAR2(32),
salaire NUMBER(10,2),
service VARCHAR2(32),
PRIMARY KEY (nom))
 Structure d’un programme
• DECLARE /* Partie déclarative : variables, types et
fonctions locales. */
• BEGIN /* Partie Exécution : la partie procédurale et
SQL se trouve ici. C’est la seule section obligatoire.
*/
• EXCEPTION /* Gestion des Exceptions. */
• END;
Types PL/SQL
 Types des variables PL/SQL (1)

• Types de base
– Types Oracle (CHAR, NUMBER, DATE...)
– Type Booléen : boolean
– Types référençant le dictionnaire de données : table.col%TYPE

• Types complexes (à placer au début du DECLARE)

– Record
• TYPE monType IS RECORD (champ1 NUMBER, champ2 VARCHAR2);
– Table
• TYPE maListe IS TABLE OF NUMBER IDENTIFIED BY
VARCHAR2(32);
– Curseurs (cf. plus loin)
• Type Exception
 Types et variables PL/SQL (2)
• Déclaration des variables dans le bloc declare
DECLARE
maVar VARCHAR2 DEFAULT ‘ROUGE’;
maVar Personne.nom%TYPE; -- type de l’attribut concerné
maVar Personne%ROWTYPE; -- type RECORD
maVar MonCurseur%ROWTYPE; -- type RECORD

• Affectation de valeur
– Dans toutes les sections avec l’opérateur :=
maVar := 2;
– Dans la section begin end avec l’opérateur into
Select max(table.col) into maVar from table;

• Appel de variables extérieures dans le bloc begin end

– Variables SQL*FORMS préfixées par :
– Variables SQL*PLUS préfixées par &
 Types et variables PL/SQL (3)
• Exemple :
• Exemple :
DECLARE v_emp Employe%ROWTYPE;
BEGIN DECLARE
v_emp.salaire := 15000; probleme exception;
v_emp.nom := 'Tartempion'; BEGIN
dbms_output.put_line('Nom : raise probleme;
'||v_emp.nom);
END; EXCEPTION
when probleme then
• Exemple : dbms_output.put_line('Il y a un
DECLARE problème...');
maVar DATE; when others then
BEGIN dbms_output.put_line('Ce
maVar := to_date('01-12-1976', 'DD- message ne devrait pas apparaitre...');
MM-YYYY');
END;
dbms_output.put_line('Date Naiss :
'||maVar);
END;
Structures de contrôle
 Structures de contrôle PL/SQL
• Traitement conditionnel
IF condition THEN traitement1
[ELSIF condition THEN traitement2]
[ELSE traitement3]
END IF;

• Itérations WHILE condition LOOP

traitement
END LOOP;

FOR i IN 1..n LOOP

traitement
END LOOP;

LOOP
traitement
EXIT WHEN condition END LOOP;
Curseurs
 Curseurs PL/SQL : déclaration
• Résultat d’une requête SQL géré comme un fichier
séquentiel
• Déclaration
DECLARE
CURSOR monCurseur IS requête_SQL;

• Curseurs avec paramètres

– Paramètre fixés à l’ouverture du curseur (paramètre passé
lors de l’instruction OPEN)
DECLARE
CURSOR monCurseur (nomRecherche IN VARCHAR2) IS
SELECT nom, salaire FROM Employe
WHERE nom = nomRecherche;
Curseurs PL/SQL : manipulation (1)
• Attributs de curseur
– %NOTFOUND, %FOUND, %ISOPEN, %ROWCOUNT
– S’évaluent à true, false, null, ou au numéro de tuple
• commandes classiques d’ouverture, lecture, fermeture
• OPEN, FETCH, CLOSE
DECLARE
CURSOR dept_compta IS
SELECT nom, salaire FROM Employe WHERE service = 'compta';
tuple dept_compta%ROWTYPE;
BEGIN
OPEN dept_compta;
LOOP
FETCH dept_compta INTO tuple;
EXIT WHEN(dept_compta%NOTFOUND);
dbms_output.put_line(tuple.nom);
END LOOP;
CLOSE dept_compta;
END;
Curseurs PL/SQL : manipulation (2)
• Manipulation simplifiée
– Pas de déclaration de l’enregistrement récepteur
– Ouverture et fermeture du curseur implicites
– Ordre de lecture pas à pas fetch implicite
– Variable de type implicite
DECLARE
CURSOR dept_compta IS
SELECT nom, salaire FROM Employe
WHERE service = 'compta';
BEGIN
FOR tuple IN dept_compta LOOP
dbms_output.put_line(tuple.nom);
END LOOP;
END;
Curseurs PL/SQL : manipulation (3)
• Clause for update of
– réservation des lignes lors de la déclaration du curseur par un verrou
d ’intention indispensable à la modification
– Permet d’ensuite modifier les lignes du curseur
• Clause current of
– Accès direct en modification à la ligne ramenée par fetch
– Utilisable seulement avec update of

DECLARE
CURSOR c1 IS
SELECT nom, salaire FROM employe FOR UPDATE OF salaire;
BEGIN
FOR c1_enr IN c1
LOOP
IF c1_enr.salaire > 15000 THEN
UPDATE employe SET salaire = salaire*1.05 WHERE CURRENT OF c1;
END IF;
END LOOP;
END;
 Curseurs PL/SQL : exemple
• Augmente de 5% le salaire des employés du service compta…
DECLARE
CURSOR Compta IS
SELECT nom, salaire FROM Employe WHERE service = ‘comptabilité’;
Emp Compta%ROWTYPE;
BEGIN
OPEN Compta;
FETCH Compta INTO Emp;
WHILE Compta%FOUND LOOP
IF Emp.salaire IS NOT NULL AND Emp.Salaire < 30.000 THEN
UPDATE Employe SET salaire = salaire*1,05 WHERE nom = Emp.nom;
END IF;
FETCH Compta INTO Emp;
END LOOP;
CLOSE Compta;
END;
Exceptions
 Exceptions prédéfinies (1)
• Levées automatiquement par le moteur PL/SQL
– CURSOR_ALREADY_OPEN, INVALID_CURSOR
– NO_DATA_FOUND, LOGIN_DENIED,
PROGRAM_ERROR
– ZERO_DIVIDE, DUP_VAL_ON_INDEX…

• Traitées par la procédure PL/SQL section begin end

BEGIN
…
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
BEGIN Affiche_Err (-20000, ‘problème…’) ; END;
END;
 Exceptions prédéfinies (2)
• Exemple de traitement lors de la levée de l’exception
oracle DUP_VAL_ON_INDEX
– Détecte les doublons sur la clé primaire dans la table

BEGIN
INSERT INTO employes VALUES (num, nom,
salaire);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
INSERT INTO doublons VALUES (num, nom);
END;
Exceptions définies par l’utilisateur
(1)
• Déclaration dans la section declare
• Levée dans la section begin end par l’instruction raise
• Traitement de l’exception dans la section begin end
– Dans la sous section exception
– Lors de la détection dans la clause when
• La clause when others traite les exceptions non traitées
DECLARE
sal_nul EXCEPTION;
…
BEGIN
…
IF … THEN RAISE sal_nul;
END IF;
EXCEPTION
WHEN sal_nul THEN …
WHEN OTHERS THEN …
END;
Exceptions définies par l’utilisateur
(2)
• Exemple
– Lève une exception quand la température du four dépasse 1000°…

DECLARE
Err_Temp EXCEPTION;
MaxTemp NUMBER;
BEGIN
SELECT Max(temp) FROM Four INTO MaxTemp ;
IF MaxTemp > 1000 THEN RAISE Err_Temp END IF;
EXCEPTION
WHEN Err_Temp THEN
BEGIN Affiche_Err (-200, ‘le four va exploser’) ; END;
END;
Procedures et Fonctions
Procédures et fonctions PL/SQL (1)
• Stockées sous forme compilée dans la base de données,
de la même façon qu’un objet de la base
– soumise aux mécanismes de sécurité ou de confidentialité
• Partagées par plusieurs applications et utilisateurs
– à condition d’avoir le privilège EXECUTE
• Procédure
– unité de traitement qui peut contenir des instructions
• SQL (sauf DDL), PL/SQL, variables, constantes, curseurs et
gestionnaire d’erreurs

• Fonction
– procédure qui retourne une valeur
Procédures et fonctions PL/SQL (2)
• Création de procédure
CREATE [OR REPLACE] PROCEDURE nom_procédure [(liste_arguments)]
<IS|AS> declaration_variables
bloc_PLSQL
liste_arguments ::= nom_argument_1 {IN | OUT | IN OUT} type,
nom_argument_2 {IN | OUT | IN OUT} type,
…...
nom_argument_n {IN | OUT | IN OUT} type

• Création de fonction
CREATE [OR REPLACE] FUNCTION nom_fonction [(liste_arguments)]
RETURN type {IS | AS} declaration_variables
bloc_PLSQL

• Re-compilation de procédure et fonction en cas de modification

du schéma de la BD
ALTER <PROCEDURE | FUNCTION> nom COMPILE;

• Suppression de procédure et fonction

DROP {PROCEDURE | FUNCTION} nom;
Procédures et fonctions PL/SQL (3)
• Exemple de procédure
– Modifie le prix d’un article d’un certain taux
CREATE PROCEDURE modif_prix (id IN NUMBER, taux IN NUMBER)
IS
BEGIN
UPDATE article a
SET a.prix_unitaire = a.prix_unitaire*(1+taux)
WHERE a.id_article = id;
END;

• Exemple de fonction
– Calcule le chiffre d’affaire
CREATE FUNCTION chiffre_affaire (id IN NUMBER) RETURN NUMBER
IS
ca NUMBER;
BEGIN
SELECT SUM(montant) INTO ca FROM vendeurs WHERE id_vendeur = id;
RETURN ca;
END;
Packages
 Package PL/SQL
• Package
– regroupement de programmes dans un objet de la BD
CREATE [OR REPLACE] PACKAGE nom_Package
<IS|AS>
déclaration_variables Visible par
déclaration_exceptions l’application
déclaration_procédures
déclaration_fonctions
(public)
END nom_Package;

CREATE [OR REPLACE] PACKAGE BODY nom_Package

Interne au
<IS|AS>
package
déclaration_variables_globales
corps_procédures
(privé)
corps_fonctions
END nom_Package
 Package PL/SQL : exemple
CREATE PACKAGE traitements_vendeurs IS
FUNCTION chiffre_affaire (id_Vendeur IN NUMBER) RETURN NUMBER;
PROCEDURE modif_com (id IN NUMBER, tx IN NUMBER);
END traitements_vendeurs;

CREATE PACKAGE BODY traitements_vendeurs IS

FUNCTION chiffre_affaire (id_Vendeur IN NUMBER) RETURN NUMBER
IS
ca NUMBER;
BEGIN
SELECT SUM(montant) INTO ca FROM vendeurs WHERE id_vendeur = id;
RETURN ca;
END;
PROCEDURE modif_com (id IN NUMBER, taux IN NUMBER)
IS
BEGIN
UPDATE vendeur v
SET v.com = v.com*(1+taux)
WHERE v.id_vendeur = id;
END;
END traitements_vendeurs;
JBDC
 JDBC et ODBC
• Ce sont des API (Application-Program Interface) pour qu’un
programme écrit dans un langage impératif (Java, C, …) puisse
interagir avec un SGBD.
• L’application effectue des appels de fonction pour
– Se connecter au SGBD (peut être fait via le Web)
– Envoyer des commandes SQL au SGBD
– Récupérer et traiter les tuples (se fait tuple par tuple)
• ODBC (Open Database Connectivity) marche avec le C, C++,
C#, VB
– Utilise potentiellement d’autres APIS telles que ADO.NET au dessus de
ODBC
– Pas présenté dans ce cours.
• JDBC (Java Datases Connectivity) fonctionne avec Java
 JDBC
• JDBC est une API Java pour la communication avec les
SGBD utilisant SQL (D’autres API similaires existent pour
les BDs XML par exemple).
• JDBC a de nombreuse fonctionnalités pour faire des
requêtes, mises à jour et récupération de données.
• JDBC permet également la récupération de meta données
telles que les tables présentes dans le SGBD, les types des
attributs etc.
• Fonctionne sur le schéma suivant :
– Ouvrir une connection
– Créer un objet “statement”
– Exécuter la requête en utilisant l’objet Statement pour envoyer les
requêtes et récupérer les résultats.
– Les Exceptions Java sont utilisées pour gérer les erreurs.
Exemple de code JDBC simple 1/2
public static void JDBCexample()
{
String username = "test";
String password = "test";
Connection connection = null;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
// ou bien : com.mysql.jdbc.Driver pour MySQL
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "XE"; // parfois orcl
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;

connection = DriverManager.getConnection(url, username, password);

Statement stmt = conn.createStatement();
//faire les requêtes
stmt.close();
conn.close();
}
catch (SQLException sqle) {
System.out.println("SQLException : " + sqle);
}
}
Exemple de code JDBC simple 2/2
• Update to database
try {
stmt.executeUpdate(
"insert into instructor values(’77987’, ’Kim’, ’Physics’,
98000)");
} catch (SQLException sqle)
{
System.out.println("Could not insert tuple. " + sqle);
}
• Execute query and fetch and print results
ResultSet rset = stmt.executeQuery(
"select dept_name, avg (salary)
from instructor
group by dept_name");
while (rset.next()) {
System.out.println(rset.getString("dept_name") + " " +
rset.getFloat(2));
}
 Explication de code
• Récupération de champs:
– rs.getString(“dept_name”) et rs.getString(1)
sont équivalents si dept_name est le premier
argument dans la clause select.
• Valeurs nulles : /!\ il faut lire une colonne
PUIS tester si elle est nulle.
– int a = rs.getInt(“a”);
if (rs.wasNull())
Systems.out.println(“Valeur nulle”);
 Prepared Statement : un peu plus de sécurité
• PreparedStatement pStmt = conn.prepareStatement(
"insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877"); pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance"); pStmt.setInt(4, 125000);
pStmt.executeUpdate();
pStmt.setString(1, "88878");
pStmt.executeUpdate();
• Pour les requêtes d’interrogation (qui retournent donc un
ResultSet), il faut utiliser la méthode : pStmt.executeQuery()
• /!\ IMPORTANT : Toujours utiliser des prepared statements lorsqu’on
utilise une valeur produite par un utilisateur.
– UNE MAUVAISE PRATIQUE : créer une requête en concaténant des
chaines de charactères.
– Exemple : "insert into instructor values(’ " + ID + " ’, ’ " + name + " ’, " +
" ’ + dept name + " ’, " ’ balance + ")“
– Et si le nom était “D’Alembert”?
Prepared Statement : injection de code SQL
• Supposons que la requête est construite en
utilisant :
– "select * from instructor where name = ’" +
name + "’"
• Supposons qu’au lieu d’entrer le nom,
l’utilisateur entre :
– X’ or ’1’ = ’1
• Dans ce cas, la requête devient:
– "select * from instructor where name = ’" + "X’
or ’1’ = ’1" + "’"
– C’est-à-dire :
• select * from instructor where name = ’X’ or ’1’ = ’1’
– L’utilisateur aurait même pu entrer :
 Fonctions de meta-données 1/2
• Meta données ResultSet :
– ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.println(rsmd.getColumnName(i));

System.out.println(rsmd.getColumnTypeName(i
));
}
 Fonctions de meta-données 2/2
• Meta Données de Schema de base
• DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, “test", "department",
"%");
// Arguments de getColumns: Catalog, Schema-pattern, Table-
pattern, et Column-Pattern
// Retourne: un tuple poru chaque colonne et chaque tuple a un
certain nombre d’attributs comme : COLUMN_NAME,
TYPE_NAME
while( rs.next()) {
System.out.println(rs.getString("COLUMN_NAME")+
rs.getString("TYPE_NAME"));
}
 Transactions avec JDBC
• Par défaut, chaque statement est traitée comme une
transaction séparée qui est commit automatiquement
– Ce n’est donc pas adapté pour des transactions avec des
mises à jour multiples.
• On peut éteindre l’auto commit :
– conn.setAutoCommit(false);
• Les transactions doivent ensuite être commit ou
rollback explicitement
– conn.commit(); ou
– conn.rollback();
• conn.setAutoCommit(true) rallume l’auto commit.
 Autres fonctionnalités JDBC
• Appel de fonctions et procédures CallableStatement
cStmt1 = conn.prepareCall("{? = call some
function(?)}");
– CallableStatement cStmt2 = conn.prepareCall("{call some
procedure(?,?)}");
• Manipulation de Large Objects
– Methodes getGlob() et getClob(), similaires à getString.
– Consommation de ces données avec getBytes();
– Utilisation de streams
• blob.setBlob(int parameterIndex, InputStream inputStream).
 SQL Embarqué
• JDBC est très dynamique. De nombreuses erreurs ne peuvent être
détectés au moment de la compilation problème
• Le standard SQL définit la possibilité d’embarquer du SQL dans divers
langages comme le C, Java, etc. (plus ancien que JDBC).
• Le langage dans lequel les requêtes SQL sont embarqués est appelé
langage hôte et les structures autorisées dans ce langage sont étendues
aux structures de SQL embarqué.
• Initialement fait par System R dans PL/I (PL/SQL)
• La commande EXEC SQL est utilisée pour identifier le SQL embarqué
et envoyer son traitement au préprocesseur.
EXEC SQL <embedded SQL statement > END_EXEC

Note: cette commande dépend en fait du langage (par exemple en java il

s’agit de # SQL { …. }; )
Exemple SQL Embarqué PRO*C 1/3
Depuis un langage hôte, trouver les IDs et noms des
étudiants qui ont complété plus que le nombre de crédits
indiqués dans la variable credit_amount du langage hote.
• Sortir directement les valeurs dans une variable du langage hote
int main(){
char n[20];
EXEC SQL
select name INTO :n
from student
where ID = 1;
printf(name);
}
• Ne marche que s’il n’y a qu’un seul tuple !!
• Spécifier une requête en SQL et déclarer un curseur.
int credit_amount = 1000;
EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount;
 SQL embarqué PRO*C 2/3
• L’instruction open démarre l’évaluation de la requête
EXEC SQL open c ;
• L’instruction fetch stocke les valeurs d’un tuple de la
requête dans les variables du langage hôte.
EXEC SQL fetch c into :si, :sn ;
Des appels successifs à la commande fetch récupèrent les
tuples successifs
• Une variable appellée SQLSTATE prend la valeur 02000
lorsqu’il n’y a plus de données
• L’instruction close cause l’effacement de la table
temporaire qui contenait le résultats de la requête.
EXEC SQL close c ;
Note: les détails au dessus dépendent des langages (au
dessus c’est du C)
Mises à jour au travers de curseurs 3/3
Des mises à jour peuvent être effectuées si le curseur est défini
comme étant for update
declare c cursor for
select *
from instructor
where dept_name = ‘Music’
for update
On peut mettre à jour la valeur se trouvant à la position courrante
du curseur c
update instructor
set salary = salary + 100
where current of c
 JDBC vs Embedded (Oracle)
• Embedded : moins de lignes de code, programmes plus courts donc plus
faciles à débugguer.
• Embedded : peut faire des vérifications syntaxiques et sémantiques, en
utilisant une connection au SGBD au moment de la compilation.
• Embedded : possède un système de vérification de typage fort pour les
paramètres et valeurs de retour de requêtes. En JBCD ces valeurs sont passées
au moment de l’exécution, et n’ont pas été validées au moment de la
compilation.
• Embedded : permet de faire directement les bindings de variables depuis java,
au lieu de devoir écrire plein de méthodes.
• JDBC donne un grain plus fin de contrôle sur l’exécution des requêtes SQL et
offre la possibilité de faire du SQL dynamique. Si l’application requiere des
capacités de découverte dynamique de meta données, c’est souvent plus
simple d’utiliser JDBC.
 JDBC vs Stored Procedures
JDBC
• La plupart des programmeurs connaissent mal les SGBD et SQL
• Cache la complexité du modèle de données
Avantages des procédures stockées
• Controle d’acces natif
• Optimisations sur le calcul de données
• Ne retourne pas de données inutiles, gain de mémoire
Beaucoup de problèmes de performance dans les applications
viennent d’un mauvais usage de JDBC !
Discutable : mieux d’exécuter sur le serveur (centralisé) ou le client
?
Conclusion
 Conclusion langages dédiés
• Les langages de type PL/SQL constituent le mode
d'utilisation le plus courant des bases de données
relationnelles
– Utilisé pour programmer des procédures stockées ou des
triggers
– performance (réduit le transfert réseau)
– bien adapté aux clients légers (ex: PDA …) car déporte des
traitements sur le serveur

• Utilisé par les outils de développement de plus haut

niveau (SQLforms)