BD – PL/pgSQL BD – PL/pgSQL

Conception de bases de données relationnelles

Langage PL/(pg)SQL

Marie Szafranski

2016-2017 ensiie 1 2016-2017 ensiie 1

BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Aperçu de PL/(pg)SQL Aperçu de PL/(pg)SQL

PL/SQL : Procedural Language for SQL → (Oracle)

Langage (initialement) interprété syntaxiquement proche d’Ada ou Pascal

• Oracle PL/SQL
• PostgreSQL PL/pgSQL

SQL/PSM : Persistant Stored Modules → (extension de SQL-2)

• MySQL SQL/PSM
• DB2 SQL-PL
• Sybase et SQLServer Transact-SQL
• PostgreSQL PL/pgPSM

2016-2017 ensiie 2 2016-2017 ensiie 2

 BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Utilisation Utilisation

• Contrôle de l’intégrité des données

→ procédures ou fonctions déclenchées sur des événements spécifiques

(insertion, suppression, mise-à-jour, etc.)

• Définition de traitements complexes

→ combinaison de requêtes et d’instructions procédurales

→ manipulation de données liées au résultat d’une ou plusieurs requêtes

• Optimisation des traitements fréquents

→ stockage de procédures ou de fonctions

(temps d’execution minimisé vs. opération au niveau applicatif)

2016-2017 ensiie 3 2016-2017 ensiie 3

BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Éléments de syntaxe Éléments de syntaxe (Structure d’un block)

Structure d’un block

[DECLARE]
-- section pour déclarer les variables, les curseurs, etc.

BEGIN
-- section contenant les instructions SQL et PL/SQL

[EXCEPTION]
-- section permettant de gérer les erreurs

END;

2016-2017 ensiie 4 2016-2017 ensiie 4

 BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Éléments de syntaxe Éléments de syntaxe (Affichage)

Affichage avec Oracle

DBMS [Link] LINE(<message>);

Affichage avec PostgreSQL

RAISE NOTICE ’<message>’;

Exemple

BEGIN
RAISE NOTICE ’Hello IBD’;
END;

2016-2017 ensiie 5 2016-2017 ensiie 5

BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Éléments de syntaxe Éléments de syntaxe (Types des variables)

nom v [CONSTANT] type v [NOT NULL] [{DEFAULT | :=} expression];

Variables de type simple

Types numériques, chaı̂nes, date, etc. (Cf. cours SQL et the Fine Manual)

Variables de type composé

• Type tableau
prenoms VARCHAR(50) ARRAY[3] := {’Sherlock’, ’Conan’, NULL};

• Type enregistrement
-- structure du résultat d’une ligne d’une commande SELECT
ligne RECORD := SELECT * FROM Detective WHERE nom = ’Holmes’;

• Types plus complexes CREATE TYPE (Again, the FM)

2016-2017 ensiie 6 2016-2017 ensiie 6

 BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Éléments de syntaxe Éléments de syntaxe (Types référencés)

nom v [CONSTANT] type v [NOT NULL] [{DEFAULT | :=} expression];

Référence à un objet existant

-- Référence au type d’une variable définie

nom v1 nom v ref%TYPE;
age INTEGER(3); majeur depuis age%TYPE;

-- Référence au type de champ d’une table

nom v2 nom table [Link] champ ref%TYPE;
age super [Link]%TYPE;

Référence à une  ligne  existante

-- Référence au type d’un tuple d’une table (ou d’un curseur)

nom v3 nom table ref%ROWTYPE;

2016-2017 ensiie 7 2016-2017 ensiie 7

BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Éléments de syntaxe Éléments de syntaxe (Traitements conditionnels)

Traitements conditionnels

• IF
IF THEN instructions
[ELSIF THEN instructions]
[ELSE instructions]
END IF;

• CASE
CASE
WHEN instructions pour le cas 1 THEN
[WHEN instructions pour le cas 2 THEN]
ELSE instructions pour le cas par défaut
END CASE;

2016-2017 ensiie 8 2016-2017 ensiie 8

 BD – PL/pgSQL Généralités BD – PL/pgSQL Généralités

Éléments de syntaxe Éléments de syntaxe (Traitements répétitifs)

Traitements répétitifs
• FOR
FOR compteur IN [REVERSE] inf... sup LOOP
instructions
END FOR;

• WHILE
WHILE condition LOOP
instructions
END LOOP;

• REPEAT
LOOP
instructions
[EXIT WHEN condition];
END LOOP;

2016-2017 ensiie 9 2016-2017 ensiie 9

BD – PL/pgSQL Programmes BD – PL/pgSQL Programmes

Types de programmes Types de programmes (Principe)

Fonctions et procédures stockées

• Enregistrées dans le noyau du SGBD

→ pas besoin de compilation (pré-compilé)

• Accessibles à tous les utilisateurs

→ modulo les droits accordés

• Plusieurs langages procéduraux possibles

→ LANGUAGE plpgsql

• Procédure : cas particulier de fonction sous PostgreSQL

→ CREATE PROCEDURE spécifique à Oracle

2016-2017 ensiie 10 2016-2017 ensiie 10

 BD – PL/pgSQL Programmes BD – PL/pgSQL Programmes

Types de programmes Types de programmes (Procédure)

Procédure (Oracle)
CREATE [OR REPLACE] PROCEDURE nom proc
[(liste de parametres {IN | OUT})]
IS
BEGIN
instructions
END;

Exemple : affichage des nombres de n à 1

CREATE PROCEDURE a l envers (n NUMBER) IS
BEGIN
IF n >= 0 THEN
DBMS [Link] LINE (’nombre: ’ || n);
a l envers(n-1);
END IF;
END;
2016-2017 ensiie 11 2016-2017 ensiie 11

BD – PL/pgSQL Programmes BD – PL/pgSQL Programmes

Types de programmes Types de programmes (Fonction)

Fonction (PostgreSQL)
CREATE [OR REPLACE] FUNCTION nom fun [(liste de parametres)]
RETURNS type de retour AS
$fun$
BEGIN
instructions
END;
$fun$ LANGUAGE plpgsql; -- CREATE LANGUAGE plpgsql;

Exemple : affichage des nombres de n à 1

CREATE FUNCTION a l envers (n NUMBER) RETURNS void AS $fun$
BEGIN
IF n >= 0 THEN RAISE NOTICE ’nombre: (%)’, n; a l envers(n-1);
END IF;
END;
$fun$ LANGUAGE plpgsql;
2016-2017 ensiie 12 2016-2017 ensiie 12
 BD – PL/pgSQL Exceptions BD – PL/pgSQL Exceptions

Caractéristiques des exceptions Caractéristiques des exceptions (Principe)

Principe

• Prendre en compte les erreurs possibles

→ insertion de doublons, erreur d’allocation mémoire, etc.

• Rattraper les erreurs en cours d’execution

→ doublons : prévenir l’utilisateur en donnant des détails (au minimum)

• Traiter les erreurs à part

→ lisibilité

2016-2017 ensiie 13 2016-2017 ensiie 13

BD – PL/pgSQL Exceptions BD – PL/pgSQL Exceptions

Caractéristiques des exceptions Caractéristiques des exceptions (Type)

Quelques exceptions PL/pgSQL

• Problèmes validité des données

→ invalid escape sequence, division by zero, etc.

• Violation de contraintes d’intégrité

→ integrity constraint violation, not null violation, etc.

• Large spectre d’exceptions possibles The FM !

→ pas de possibilité de définir ses propres exceptions

2016-2017 ensiie 14 2016-2017 ensiie 14

 BD – PL/pgSQL Exceptions BD – PL/pgSQL Exceptions

Utilisation d’exception Utilisation d’exception

Syntaxe

RAISE [niveau]
[{’format’ [, expression [, ... ]] | condition | SQLSTATE ’etat sql’}]
[USING option = expression [, ... ]];

Exemple

RAISE unique violation; RAISE SQLSTATE ’23505’;

RAISE ’identifiant dupliqué: %’, id utilisateur

USING ERRCODE = ’unique violation’;
RAISE ’identifiant dupliqué: %’, id utilisateur
USING ERRCODE = ’23505’;

RAISE unique violation

USING MESSAGE = ’identifiant dupliqué:’ || id utilisateur;

2016-2017 ensiie 15 2016-2017 ensiie 15

BD – PL/pgSQL Exceptions BD – PL/pgSQL Exceptions

Exemple d’exception Exemple d’exception

Affichage d’informations
Super heros(#nom super: VARCHAR(20), nb victoires: INTEGER)

CREATE FUNCTION print victoires super(VARCHAR(20)) RETURNS void AS

$print victoires$
DECLARE
victoires Super [Link] victoires%TYPE;
BEGIN
-- Victoires du super dont l’identifiant est passé en paramètre
SELECT nb victoires INTO victoires
FROM Super heros WHERE nom super = $1;
RAISE NOTICE ’Nombre de victoires de %: %’, $1, victoires;
EXCEPTION
WHEN no data found THEN
RAISE no data found
USING MESSAGE = ’super % inexistant dans la base:’ || $1;
END; $print victoires$ LANGUAGE plpgsql;

2016-2017 ensiie 16 2016-2017 ensiie 16

 BD – PL/pgSQL Curseurs BD – PL/pgSQL Curseurs

Caractéristiques des curseurs Caractéristiques des curseurs

Principe

• Résultat d’une requête dans une variable

SELECT ... INTO variable
→ une seule ligne !

• Parcours d’une requête dont le résultat contient plusieurs lignes

→ curseur

• Analogie
→ pointeur sur le résultat d’une requête

2016-2017 ensiie 17 2016-2017 ensiie 17

BD – PL/pgSQL Curseurs BD – PL/pgSQL Curseurs

Déclaration de curseurs Déclaration de curseurs

Syntaxe

nom c [arguments] [{FOR | IS} requete];

Exemple

DECLARE
-- Curseur utilisé avec n’importe quelle requ^
ete
curseur 1 REFCURSOR;

-- Curseur lié à une requ^

ete
curseur 2 CURSOR {FOR | IS} SELECT * FROM Detective;

-- Curseur lié à une requ^

ete paramétrée
curseur 3 CURSOR {FOR | IS} SELECT * FROM Detective WHERE nom = param;

2016-2017 ensiie 18 2016-2017 ensiie 18

 BD – PL/pgSQL Curseurs BD – PL/pgSQL Curseurs

Ouverture de curseurs Ouverture de curseurs

• Curseur lié

OPEN curseur lie [(arguments)];

-- Exemple
OPEN curseur 2;
OPEN curseur 3(’Holmes’);

• Curseur non-lié

OPEN curseur non lie FOR requete;

-- Exemple
OPEN curseur 1 FOR SELECT * FROM Detective WHERE nom = ’Holmes’;

• Curseur non lié et requête paramétrée

OPEN curseur non lie FOR EXECUTE chaine requete;

-- Exemple
OPEN curseur 1 FOR EXECUTE ’SELECT * FROM’ || quote ident($1);

2016-2017 ensiie 19 2016-2017 ensiie 19

BD – PL/pgSQL Curseurs BD – PL/pgSQL Curseurs

Utilisation de curseurs Utilisation de curseurs

• Rapatriement du curseur dans une variable cible

→ RECORD, variable ligne, liste de variables simples
FETCH curseur INTO cible;
-- Exemple
FETCH curseur INTO un record;
FETCH curseur INTO var 1, var 2, var 3;

• Repositionnement du curseur
MOVE [direction {FROM | IN}] curseur;
-- Exemple
MOVE LAST FROM curseur;
MOVE RELATIVE -2 FROM curseur;
MOVE FORWARD 2 FROM curseur;

• Fermeture d’un curseur

CLOSE curseur;

2016-2017 ensiie 20 2016-2017 ensiie 20

 BD – PL/pgSQL Curseurs BD – PL/pgSQL Curseurs

Exemple de curseur Exemple de curseur

Affichage d’informations
Super heros(#nom super: VARCHAR(20), nb victoires: INTEGER)

CREATE FUNCTION print super() RETURNS void AS $print$

DECLARE
curseur super CURSOR FOR SELECT * FROM Super heros;
super Super [Link] super%TYPE;
victoires Super [Link] victoires%TYPE;
BEGIN
RAISE NOTICE ’** Liste des super héros **’;
-- Parcours d’un curseur avec FOR: OPEN, MOVE et CLOSE automatiques
FOR ligne courante IN curseur super LOOP
super := ligne [Link] super;
victoires := ligne [Link] victoires;
RAISE NOTICE ’%: victoires %’ super, victoires;
END LOOP;
END; $print$ LANGUAGE plpgsql;

2016-2017 ensiie 21 2016-2017 ensiie 21

BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Caractéristiques des déclencheurs (Triggers, SQL-3) Caractéristiques des déclencheurs (Principe)

Principe

• Programme actif événement → condition → action

• Programme stocké déclenché automatiquement suite à un événement

INSERT, DELETE, UPDATE, DROP, ALTER, CONNECT, etc.
pour une condition vérifiée
WHEN
→ une seule fois pour toute l’instruction
→ à chaque modification d’une ligne

• Base de données dynamique

→ cascade de déclencheurs
→ risque de boucle infinie

2016-2017 ensiie 22 2016-2017 ensiie 22

 BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Caractéristiques des déclencheurs (Triggers, SQL-3) Caractéristiques des déclencheurs (Utilisation)

Utilisation
• Contrôle de l’intégrité des données
→ si impossible à formuler de façon déclarative
→ sinon contraintes classiques CHECK, UNIQUE, etc.

• Spécification de contraintes liées à l’évolution des données

→ un salaire ne peut qu’augmenter

• Spécification de règles complexes liées à l’environnement

→ restriction sur des horaires en fonction d’une profession

• Calcul d’attributs dérivés pour d’autres champs d’une table

• ...
2016-2017 ensiie 23 2016-2017 ensiie 23

BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Exécution du déclencheur Exécution du déclencheur

Sur instruction
Traite l’ensemble des lignes concernées
→ avant exécution de l’événement considéré BEFORE
→ après exécution de l’événement considéré AFTER

Sur ligne FOR EACH ROW

• INSERT OLD → NULL

INSERT a NEW → valeur insérée
• DELETE OLD → valeur supprimée
DELETE a NEW → NULL
• UPDATE OLD → valeur avant modification
UPDATE a NEW → valeur après modification

2016-2017 ensiie 24 2016-2017 ensiie 24

 BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Création de déclencheurs Création de déclencheurs

Syntaxe

CREATE [OR REPLACE] TRIGGER nom trigger

{BEFORE | AFTER | INSTEAD OF} {evenement [OR ... ]}
ON nom table
[FOR [EACH] {ROW | STATEMENT}]
[WHEN (condition)]
EXECUTE PROCEDURE nom fonction (arguments)
-- evenement
INSERT
UPDATE [OF nom colonne [, ... ]]
DELETE

Valeur retournée

• BEFORE → NULL ou RECORD (par exemple NEW)

• AFTER → ignoré

2016-2017 ensiie 25 2016-2017 ensiie 25

BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Exemples de déclencheurs Exemples de déclencheurs (Évolution des données)

Contrôle sur l’évolution des données

→ le nombre de victoires d’un vrai super héros ne devrait que croı̂tre
Super heros(#nom super, nb victoires)

CREATE FUNCTION f is hero or zero() RETURNS TRIGGER AS $f hoz$

BEGIN
IF [Link] victoires < [Link] victoires THEN
RAISE NOTICE ’% est-il un vrai super héros ?’, nom super;
END IF;
RETURN NULL;
END; $f hoz$ LANGUAGE plpgsql;

CREATE TRIGGER t hero or zero

BEFORE UPDATE OF nb victoires ON Super heros
FOR EACH ROW EXECUTE PROCEDURE f is hero or zero();

2016-2017 ensiie 26 2016-2017 ensiie 26

 BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Exemples de déclencheurs Exemples de déclencheurs (Archivage des données)

Archivage des données

→ table  miroir  pour conserver les données supprimées
Super heros(#nom super, nb victoires)
Archive super(#nom super, nb victoires)

CREATE FUNCTION f archive super() RETURNS TRIGGER AS $f as$

BEGIN
IF (TG OP = ’DELETE’) THEN
INSERT INTO Archive super VALUES
([Link] super, [Link] victoires)
ELSIF (TG OP = ’INSERT’) THEN
DELETE FROM archive s WHERE nom super = [Link] super ;
END IF;
END; $f as$ LANGUAGE plpgsql;

CREATE TRIGGER t archive super AFTER DELETE OR INSERT ON Super heros

FOR EACH ROW EXECUTE PROCEDURE f archive super();
2016-2017 ensiie 27 2016-2017 ensiie 27

BD – PL/pgSQL Déclencheurs BD – PL/pgSQL Déclencheurs

Exemples de déclencheurs Exemples de déclencheurs (Cohérence de la base)

Mise à jour et maintien de la cohérence sur la base

→ gestion des victoires d’un super héros
Super heros(#nom super, nb victoires)
Duel(#gentil=>Super heros, #mechant, #date, score = {-1, 0, +1})

CREATE FUNCTION f update victoire() RETURNS TRIGGER AS $f uv$

DECLARE
victoires Super [Link] victoires%TYPE;
BEGIN
SELECT nb victoires + score INTO victoires
FROM Super heros WHERE nom super = [Link];
UPDATE Super heros
SET nb victoires = victoires WHERE nom super = [Link];
END; $f uv$ LANGUAGE plpgsql;

CREATE TRIGGER t up victoire AFTER INSERT OR UPDATE OF score ON Duel

FOR EACH ROW EXECUTE PROCEDURE f update victoire();
2016-2017 ensiie 28 2016-2017 ensiie 28
 BD – PL/pgSQL Pour finir

Pour le TP de vendredi prochain (Et en règle générale aussi. . .)

• Se préparer
→ avoir une base de données vins opérationnelle sur son compte
→ reprendre le TP précédent (SQL LDM)
réviser (voire finir) la partie sur les jointures
réviser (voire finir) la partie sur les agrégats
entraı̂nement supplémentaire : syntaxe et fonctions de calculs
[Link]
→ finir le TD sur le SQL LDD et LCD
→ consulter la doc PostgreSQL et les exemples associés concernant le
cours d’aujourd’hui

• En cas de difficulté
→ arriver avec des questions précises pour les chargés de TD/TP

Ce qui a été vu en cours et proposé en TD ou TP est considéré comme

acquis
2016-2017 ensiie 29