END - Maroua Bouzid

Vues
Déclencheurs et
procédures en Postgres
Maroua Bouzid
Département Informatique
Université de Caen
LES VUES
Définition
• Une vue est une table virtuelle au
sens où ses instances n’existent pas
physiquement.
• Une vue est une table logique
pointant sur une table physique
• Un utilisateur peut suivre l’évolution
d’une table physique via une vue.
Création d’une vue
• CREATE or REPLACE view as <requête>
• CREATE VIEW nom [ ( nom_colonne
[, ...] ) ]
AS requête
[ WITH [ CASCADED | LOCAL ] CHECK
OPTION ]
 check option : les conditions de la création
doivent rester respectées lors des INSERT,
UPDATE
 Local ; que de la vue
 Cascade : sur toutes les vues issues de cette
vue
Exemples
CREATE VIEW comedies AS
SELECT *
FROM films
WHERE genre = 'Comédie'
PL/SQL
Introduction générale
• Pour l’aide à la décision on a besoin
d’étendre SQL pour :
 Regrouper un bloc de commandes et le
soumettre au noyau.
• Extension de SQL consiste en :
 Manipulation
 Opérations et analyses
 traitements
De SQL à PL/SQL
• Introduction de quelques principes
de la programmation ;
• Introduction de structures de
contrôle
 Branchement conditionnel,
 Itération,
 Affectations ;
• Introduire les fonctions et les
procédures
Procédures et fonctions
• Bloc PL/SQL à trois parties :
DECLARE
Déclarations de constantes et de variables
BEGIN
Commandes exécutables
END;
Bloc PL/SQL
partie declare
 DECLARE
 Déclaration d’un type d’un attribut
• Variable table.attribut%type
 Déclaration d’un type n-uplet
• Variable table%rowtype
• Variable record
 Déclaration d’une date
• Variable date
 Déclaration d’une constante
• Variable CONSTANT := constante
Bloc pl/SQL
partie BEGIN …END;
• Partie BEGIN … END :
 Affectation
 Traitement conditionnel
 Traitement itératif
PL/SQL
Conditionnel
• Analyse conditionnelle :
IF <condition> THEN
ELSE IF <condition> THEN
ELSE IF <condition> THEN
ELSE
END IF;
END IF;
END IF;
PL/SQL
Itératif
FOR compteur DOMAINE
LOOP
INSTRUCTION
END LOOP;
PL/SQL
Itératif
FOR i IN select nocli, count(qtité*PU) into total
from commande group by nocli
LOOP
IF total > 10000 THEN
INSERT i into table_TB_CLIENT;
END IF;
END LOOP;
Procédures et fonctions
• Offrir
aux
programmeurs
la
possibilité de créer des blocs de
traitements.
• Introduire quelques bases de la
programmation dans les moteurs
SQL
• Les procédures et les fonctions sont
stockées dans la base de données
comme les autres objets (tables,
requêtes, …)
Création d’une fonction ou
procédure sans paramètres
CREATE FUNCTION gen_cle_client () RETURNS OPAQUE AS
'
DECLARE
nocli integer;
BEGIN
select into nocli max(no_client) from client;
IF nocli ISNULL THEN
nocli:=0;
END IF;
NEW.no_client:=nocli+1;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql';
CREATION avec paramètres
CREATE FUNCTION double (integer)
RETURNS integer
AS
'BEGIN
RETURN 2*$1;
END; '
LANGUAGE 'plpgsql';
• Les paramètres sont utilisés via les macros
$1, $2, …$x pour les paramètres 1, 2 …
xème
Remplacement
• REPLACE permet de changer le code d’une
fonction existante
• En générale lors de la création on peut
aussi mettre la primitive REPLACE.
CREATE OR REPLACE FUNCTION double (integer)
RETURNS integer
AS
'BEGIN
RETURN 2*$1;
END; '
LANGUAGE 'plpgsql';
Exemple
CREATE OR REPLACE FUNCTION gen_cle_client () RETURNS void AS
'DECLARE
i RECORD;
total real;
BEGIN
FOR i IN select nocli, count(qtité*PU) into total from commande
group by nocli
LOOP
IF total > 10000 THEN
INSERT i into table_TB_CLIENT;
END IF;
END LOOP;
END; ' LANGUAGE 'plpgsql';
LES TRIGGERS
Définition
• Qu'est-ce qu'un trigger ?
 Un déclencheur est un programme
stocké dans une base de données.
 Un trigger est associé à une table de la
base de donnée.
 Un trigger est associé à un événement
qui se produit sur cette table.
 Le trigger est exécuté
automatiquement lorsque l'événement
auquel il est attaché se produit sur la
table
Intérêt
•
•
•
•
•
•
Gestion événementiel transparente
Une mise à jour automatique cohérente
Une sécurité renforcée
Une maintenabilité des tables facile
Gestion d’un historique
Facilite l’analyse de données pour la
décision
• En général : implémentation des MCT
Les événements
déclenchants
• Le programme associé au trigger se
déclenche lorsque l’un des
événements se produit :
 Insertion dans la table : insert
 Mise à jour : update
 Suppression : delete
Caractéristique
• Un trigger est associé à une seule
table
• S’exécute à l’arrivée de l’événement
• Déclenche un bloc PL/SQL (fonction)
• Détruit avec la destruction d’une
table
• Peut être désactivé.
Description
• L’événement : DELETE, UPDATE, INSERT
• Le type : une seule fois ou pour toutes les
lignes
• Séquencement : avant ou après prise en
compte de l’événement
• Les restrictions : condition
 On utilise aussi les prédicats inserting,
deleting, updating
SYNTAXE
CREATE TRIGGER <nom_trig>
BEFORE | AFTER
DELETE OR INSERT OR UPDATE
FOR EACH ROW
ON <nom_table>
EXECUTE PROCEDURE <nom_func>
Exemple
CREATE FUNCTION gen_cle_client () RETURNS OPAQUE AS
'DECLARE
nocli integer;
BEGIN
select into nocli max(no_client) from client;
IF nocli ISNULL THEN
nocli:=0;
END IF;
NEW.no_client:=nocli+1;
RETURN NEW;
END; '
LANGUAGE 'plpgsql';
CREATE TRIGGER trig_bef_ins_client
BEFORE INSERT
ON client
FOR EACH ROW
EXECUTE PROCEDURE gen_cle_client();
Les références des
TRIGGER
• Dans les blocs PL/SQL :
 On fait référence aux nouvelles et
anciennes valeurs issues des
événements.
 Les Références sont :
 OLD : ancienne valeur
 NEW : nouvelle valeur
• Lien entre OLD, NEW et événement
OLD, NEW dans INSERT,
DELETE et UPDATE
OLD
NEW
INSERT
NULL
Valeur
créée
DELETE
Valeur
supprimée
NULL
UPDATE
Valeur
Valeur
avant
après
modification modification
Exemple
CREATE FUNCTION gerehisto () RETURNS OPAQUE AS
'DECLARE
nocli integer;
BEGIN
IF NEW.salaire<>OLD.salaire THEN
INSERT into hist_sal
values(NEW.nom,NEW.prenom,''NOW'',OLD.salaire,NEW.salaire);
END IF;
RETURN NEW;
END; ' LANGUAGE 'plpgsql';
CREATE TRIGGER trig_bef_update_employe BEFORE update ON
salarie
FOR EACH ROW
EXECUTE PROCEDURE gerehisto();
Complément
• VOIR
http://www.btsinfogap.org/cours/s2/ig2
/triggers_postgres_plpgsql.html#id27
51696
AUTO-FORMATION
EXEMPLE ET CORRECTION
ACCESSIBLE