Ceci est une ancienne révision du document !
Table des matières
PL/SQL
- Langage procédural d'Oracle
- Une extension de SQL
- Permet de mélanger la puissance du SQL avec la souplesse d'un langage procédural
Avantages
- Intégration du SQL : on peut intégrer dans un bloc PL/SQL :
- Des instructions du MD
- Gestion des curseurs
- Traitement des erreurs
- Amélioration des performances : un bloc PL/SQL avec plusieurs instructions SQL ne causera qu'un seul accès à la base de données
- Incorporation aux produits Oracle : blocs et procédures PL/SQL sont compilés et exécutés par le moteur PL/SQL intégré au moteur de la base de données ainsi qu'à des outils tels que Oracle*Forms et Oracle*Reports
Bloc PL/SQL
- La partie déclaration n'est présente que si le bloc a besoin de déclarer des variables
[DECLARE] declaration_statements BEGIN execution_statements [EXCEPTION] exception_handling_statements END;
- toute variable doit être déclarée avant d'être utilisée
identificateur [CONSTANT]
type [[NOT NULL] := expression];
- Deux types de variables :
- Variables locales : déclarées à l'intérieur du bloc PL/SQL
- Variables définies dans un environnement extérieur à PL/SQL
DECLARE x NUMBER := 16; anniversaire DATE; nom VARCHAR(20); pi CONSTANT NUMBER := 3.1416;
- visibilité → une variable locale est visible dans un bloc ou elle a été déclarée et dans les blocs imbriquées si elle n'a pas été redéfinie
- variables définies dans un environnement extérieur à PL/SQL :
- Déclarées en dehors du bloc et utilisables dans le bloc
- Représentent des camps d'écran Oracle*Forms ou des variables définies dans SQL*Plus ou dans un langage hôte
- Préfiée par : dans un bloc PL/SQL
SQL>varibale x NUMBER
Utilisation de scripts
Les blocs PL/SQL peuvent être placés dans des fichiers textes avec l'extension .sql
qui seront alors chargés dans SQL*Plus. Une fois dans dans le programme SQL*Plus, insérer la ligne : SET serverouput on
. Les affichages seront alors activés et on pourra utiliser la commande DBMS_OUTPUT.PUT_LINE();
.
Ensuite, charger le script à l'aide de la commande ouvrir, une fois le script affiché, insérer une barre oblique (/
) puis Entrée
.
Types
Les mêmes types utilisables dans la définition des colonnes des tables. Facilitent les échanges entre tables et blocs. Attention ! Les intervalles de valeurs peuvent être différents entre le type SQL et le type PL/SQL. Par exemple, une données de type VARCHAR2
possède 32767 octets dans PL/SQL et 4000 octets dans SQL.
Types propres à PL/SQL. Surtout pour gérer des données numériques. Possibilité de définir des types complexes basés sur les structures des tables ou des descriptions fournies par l'utilisateur.
Caractères | Numériques | Grands objets | |
---|---|---|---|
<html><ul><li>CHAR[(n)]</li><li>VARCHAR2[(n)]</li><li>LONG (deprecated)</li><li>RAW[(n)]</li><li>LONG RAW</li><li>NCHAR[(n)]</li><li>NVARCHAR2[(n)]</li><li>UROWID, ROWID</li></ul></html> | <html><ul><li>NUMBER[(p,s)]</li><li>BINARY_INTEGER</li><li>PLS_INTEGER</li></ul></html> | <html><ul><li>BFILE</li><li>BLOB</li><li>CLOB</li><li>NCLOB</li></ul></html> | |
Composés | Références | Dérivés | Autres |
<html><ul><li>RECORD</li><li>TABLE</li><li>VARRAY</li></ul></html> | <html><ul><li>REF CURSOR</li><li>REF type_objet</li></ul></html> | <html><ul><li>%TYPE</li><li>%ROWTYPE</li></ul></html> | <html><ul><li>BOOLEAN</li><li>DATE</li><li>TIMESTAMP</li></ul></html> |
Compatibilité
Ce sont des synonymes pour assurer une compatibilité avec les types standards ANSI/ISO et IBM.
- NUMBER → DEC, DECIMAL, NUMERIC, DOUBLE PRECISION, FLOAT, REAL, INTEGER, INT, SMALLINT.
- BINARY_INTEGER → NATURAL, NATURALN, POSITIVE, POSITIVN, SIGNTYPE.
- VARCHAR2 → STRING, VARCHAR.
- CHAR → CHARACTER.
Opérateurs
Type | Symbole(s) |
---|---|
Affectation | := |
Addition | + |
Soustraction | - |
Multiplication | * |
Division | / |
Exponentiation | ** |
Concaténation | || |
La clause INTO
S'utilise dans un SELECT
ou un FETCH
. Permet d’affecter à une ou plusieurs variables des valeurs à partir d'une ligne résultant d'une requête.
SELECT {*|liste d’expressions} INTO liste de VARIABLES FROM ...; FETCH nom de curseur INTO liste de VARIABLES;
Exemples
DECLARE numMenbre MEMBRE.NUMBER%TYPE; unDocument DOCUMENT%ROWTYPE; BEGIN numMembre := 465; SELECT nom, tel FROM Membre WHERE Numero = numMembre; unDocument.ISBN := '12255' /* pas modifier en temps normal (clé primaire) */ unDocument.Titre := 'Intro à PL/SQL'; unDocument.Auteur := 'Pierre Dupont'; unDocument.TYPE := 'Livre'; unDocument.Editeur := 'Aw'; unDocument.Annee := '12-06-2005'; INSERT INTO Document VALUES unDocument; UPDATE Document SET ROW = unDocument WHERE ISBN = "123765'; END;
DECLARE TYPE Etudiant IS RECORD ( no NUMBER(10); nom CHAR(20); note NUMBER ); etud Etudiant; BEGIN etud.no := 4422; etud.nom := 'Pierre Dupont'; etud.note := 79; etud.note := etud.note * 1.1; END;
Structures de contrôles
Les principes des structures de contrôles sont les mêmes que dans la majorité des langages de programmation.
Séquence
Suite d'instructions exécutées dans l'ordre.
Alternative
L'instruction IF-ELSE
IF condition THEN instruction1; ELSE intruction2; END IF;
IF condition1 THEN instruction1; ELSEIF condition2 THEN intruction2; ELSE instuction3; END IF;
Condition : expression logique qui utilise les opérateurs =
, <>
, <
, >
, <=
, >=
, IS NULL
,IS NOT NULL
, BETWEN
, LIKE
, AND
, OR
.
IF NUM= 444 THEN UPDTE MEMBRE SET NOM='Dupont' WHERE NUMERO = NUM; COMMIT; ELSE ROLLBACK; END IF;
L'instruction CASE
La structure alternative CASE
prend deux formes. La première forme est générique et la seconde est recherchée.
/* Premier cas de CASE */ [<<etiquette>>] /* Les guillemets sont nécessaires */ CASE choix WHEN valeur1 THEN instruction1; WHEN valeur2 THEN instruction2; WHEN valeur3 THEN instruction3; [ELSE instructionx;] END CASE [etiquette]; /* deuxieme forme de CASE */ [<<etiquette>>] CASE TRUE WHEN condition1 THEN instruction1; WHEN condition2 THEN instruction2; WHEN condition3 THEN instruction3; [ELSE instructionx;] END CASE [etiquette];
SET serveroutput ON; DECLARE age NUMBER := 35; msg VARCHAR(80); BEGIN CASE WHEN age < 12 THEN msg := 'enfant'; WHEN age < 18 THEN msg := 'adolescent'; WHEN age < 40 THEN msg := 'adulte'; WHEN age < 50 THEN msg := 'quadragénaire'; ELSE msg := 'vieux'; END CASE; DBMS_OUTPUT.put_line('Cette personne est un : ' || msg); END;
Notez la ligne SET serveroutput on;
qui permet l'affichage avec dbms_output.put_line();
.
Répétitives
L'insctruction LOOP
/* Premère forme */ [<<etiquette>>] LOOP intructions; END LOOP [etiquette];
On sort de a boucle avec une instruction EXIT.
EXIT [etiquette] [WHEN condition];
L'instruction FOR
L'instruction WHILE
Boucle simple
OPEN cursor_name [(parameter1,parameter(n+1))]; LOOP FETCH cursor_name INTO row_structure_variable | column_variable1 [,column_variable(n+1)]; EXIT WHEN cursor_name%NOTFOUND; statement; END LOOP; CLOSE cursor_name;
Les curseur
Zone mémoire de taille fixe utilisée par le moteur de la base de données pour analyser et interpréter toute requête SQL. Les status d'exécution se trouvent dans le curseur.
Deux types de curseurs :
- Implicite : qui sont générés et géré par Oracle pour toute requête SQL
- Explicite : généré et géré par l'utilisateur pour traiter une requête
SELECT
qui retourne plusieurs lignes.
La déclaration des curseurs explicites est obligatoire. On donne :
- Le nom du curseur
- La requête
SELECT
associée
La syntaxe :
CURSOR nom_curseur IS requete_SQL;
Ouverture et fermeture
Dans la section de traitement (après BEGIN
) :
OPEN nom_curseur;
CLOSE nom_curseur;
Effets
- Allocation de la mémoire
- Analyse syntaxique et sémantique de la requête
- Exécution de la requête
- Positionnement des verrous éventuels (si
SELECT.. FOR UPDATE
)
Traitement des lignes (FETCH)
Les lignes du résultat de la requête sont traités une par une. La valeur de chaque colonne doit être stockée dans une variable de réception.
L'instruction FETCH
retrouve les enregistrements dans l'ensemble, un à la fois. Après chaque fetch, le curseur avance au prochain enregistrement de l'ensemble. Pour traiter toutes les lignes, on utilise une boucle.
FETCH nom_curseur INTO [variable1, variable2, ...] | [record_name];
- nom_curseur → Nom du curseur utilisé, déclaré précédemment
- variable → une variable pour garder les résultats
- record_name → Le nom de l'enregistrement dans lequel les données sont emmagasinées. La variable d'enregistrement peut être déclarée avec
%ROWTYPE
.
L'instruction FETCH
fait deux choses :
- Lit les données de l'enregistrement courant et les stocke dans les variables PL/SQL
- Avance le pointeur au prochain enregistrement de l'ensemble
Règles à suivre
- Inclure le même nombre de variable dans la clause
INTO
de l'instructionFETCH
qu'il y a de colonnes dans l'instructionSELECT
et s'assurer que les types de données sont compatibles. - Respecter la position des colonnes, qui seront associés aux variables
- tester si le curseur contient des enregistrements. Si le fetch n'acquis pas de valeur, il n'y a pas d'enregistrement à traiter dans l'ensemble et aucune erreur est enregistrée.
Exemple de FETCH
Retrouve les 10 premiers employés un par un1)
SET SERVEROUTPUT ON DECLARE v_empno employees.employee_id%ROWTYPE; v_ename employees.last_name%ROWTYPE; CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; BEGIN OPEN emp_cursor; FOR i IN 1..10 LOOP FETCH emp_cursor INTO v_empno, v_name; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno) ||' '|| v_ename); END LOOP; END;
Fermeture
CLOSE nom_curseur;
Forme simplifiée
Qui déclare implicitement la variable de parcours, ouvre le cuseur, réalise les FETCH
successifs et ferme le curseur.
FOR variable IN nom_curseur LOOP instruction; END LOOP;
Mise à jour avec les curseurs
On peut utiliser un curseur pour modifier les données de la base de donnée.
DECLARE CURSOR c IS SELECT * FROM Document FOR UPDATE OF Titre; BEGIN -- ... UPDATE Document SET Titre = '...' WHERE CURRENT OF c;
Attributs
Des attrributs peuvent être spécifiés sur un curseur.
- Curseur explicite →
nom_curseur%attrubut
- Curseur implicite →
SQL%attribut
Attribut | Type | Description |
---|---|---|
%ISOPEN | BOOLEAN | Retourne TRUE si le curseur est ouvert |
%NOTFOUND | BOOLEAN | Retourne TRUE si le plus récent FETCH ne retourne aucun enregistrement. |
%FOUND | BOOLEAN | Retourne TRUE si le plus récent FETCH retourne un enregistrement. Complément de %NOTFOUND . |
%ROWCOUNT | NUMBER | C'est le nombre de lignes traitées par la dernière requête de modification (INSERT , DELETE ou UPDATE ). |
Paramètres
CURSOR nom_curseur(param1 type1, param2 typ2, ...);
Puis, à l'ouverture du curseur, on donne les valeurs des paramètres :
OPEN nom_curseur(val1, val2, ...);
Exemple
DECLARE CURSOR docs IS SELECT ISBN, Titre FROM Document; i NUMBER(10); t VARCHAR2(); BEGIN FOR d IN docs LOOP i := d.ISBN; t := d.Titre; /* ... */ END LOOP; END;
Gestion des exceptions
En Oracle il y a deux types d'erreurs:
- Erreurs internes d'Oracle
- Erreurs dûe au programme
DECLARE ... BEGIN ... EXCEPTION // traitement des exceptions WHEN type1 THEN END;
Règles
- On peut définir et donner un om à l'exception
- Lui assigner une entrée dans la section
EXCEPTION
Les erreurs prédéfinies
Toutes les erreurs Oracles ont un numéro d'identification unique, mais pour les intercepter, elles ont besoin d'un nom. Les erreurs les plus courantes ont un nom prédéfini.
Nom | Numéro (SQLCode) |
---|---|
DUP_VAL_ON_INDEX | -1 |
NO_DATA_FOUND | 100 |
CURSOR_ALREADY_OPEN | -6511 |
ZERO_DIVIDE | -1476 |
TOO_MANY_ROWS | -1422 |
Pour les exceptions avec les nom connus, on peut les utiliser de cette façon :
BEGIN ... EXCEPTION WHEN TOO_MANY_ROWS THEN ... WHEN DUP_VAL_ON_INDEX THEN ... END;
Ils font partie du portage standard, donc inutile de les déclarer.
Pour les exceptions qui n'ont pas de nom :
Erreurs dûes au programme
Le programmeur gère une siuation exceptionnelle, comme les erreurs de logique, erreurs de données, en les prévoyant et en leur définissant un traitement spécifique supplémentaire.
Ces exceptions qui être déclarés explicitement et doivent être déclenchées avec RAISE
.
DECLARE nom_exception EXCEPTION; BEGIN -- déclanchement IF probleme THEN RAISE nom_exception; EXCEPTION WHEN nom_exception THEN ... END;
Erreurs Oracle non-prédéfinies
Deux façon de les gérer
- On la traite dans la clause OTHERS de la section
EXCEPTION
. Pour connaître l'erreur on utilise les fonctionsSQLCODE
etSQLERRM
. - Associer un nom au numéro d'exception à l'aide de la directive
PRAGMA EXCEPTION_INIT
.
PRAGMA EXCEPTION_INIT (nom_exception, numero_dexception);
Exemple : verrou mortel ou interblocage. Les verrous mortels sont détectés automatiquement par Oracle, qui décide de libérer une transaction pour lui permettre d'exécuter un ROLLBACK
et libérer les autres.
DECLARE verrou_mortel EXCEPTION PRAGMA EXCEPTION_INIT(verrou_mortel, -60); BEGIN ... EXCEPTION WHEN verrou_mortel THEN ROLLBACK; END;
DECLARE bad_emp_id EXCEPTION; bad_acct_no EXCEPTION; ... BEGIN ... EXCEPTION WHEN bad_emp_id OR bad_acct_no THEN -- user-defined ROLLBACK; WHEN ZERO_DIVIDE THEN -- predefined INSERT INTO inventory VALUES (part_number, quantity); COMMIT; END;
RAISE_APPLICATION_ERROR
Permet de déclancher des exceptions en leur attribuant un numéro et un message.
RAISE_APPLICATION_ERROR(numero, message [,TRUE|FALSE]);
- Le numéro doit être entre -20999 et -20000.
- Le message c'est 2048 caractères maximum
- Optionnellement, le
TRUE
ouFALSE
,FALSE
par défaut.FALSE
→ L'erreur remplace les autresTRUE
→ L'erreur est placée dans la pile des erreurs
Procédures et fonctions
On peut imbriquer des blocs PL/SQL :
DECLARE ... BEGIN ... DECLARE ... BEGIN ... EXCEPTION ... END; ... END;
Mais évidemment, ce type de programmation n'est pas souhaitable. On utilise alors des procédures et/ou des fonctions.
Procédure
La syntaxe d'une procédure ressemble à :
PROCEDURE p[(arg1 [IN|OUT] TYPE, arg2, argN)] IS -- Ici la déclaration de variable locales BEGIN ... EXCEPTION ... END P;
Fonctions
FUNCTION F[(arg1 [IN|OUT] TYPE, arg2, argN)] RETURN type_de_retour IS -- Ici la déclaration de variable locales BEGIN ... RETURN resultat; EXCEPTION ... END f;
Passage des arguments
Trois méthodes (qui sont optionnelles) :
IN
→ La procédure/fonction ne peut pas le modifierOUT
→ La procédure/fonction ne peut pas s'y référer, mais uniquement lui affecter une valeurIN OUT
→ Les deux en même temps
Comme c'est optionnel, ne pas spécifier revient à mettre IN OUT
.
Appel
Procédures et fonctions stockées
C'est une procédure/fonction qui est stockée dans la base de données et qui peut être exécutée à partir des applications, des blocs PL/SQL ou de SQL*Plus.
Dans un bloc PL/SQL, on a juste à invoquer le nom de la procédure/fonction comme les procédures et fonctions prédéfinies d'Oracle. Dans SQL*Plus, il faut utiliser la commande EXECUTE
SQL>EXECUTE nom_procedure(parametres);
Définition
Pour définir les procédures ou les fonctions :
CREATE [OR REPLACE] PROCEDURE p(...) IS ... CREATE [OR REPLACE] FUNCTION f(...) IS ...
Packages
Objet du schéma qui regroupe logiquement les éléments du PL/SQL (types de données, fonctions, procédures, curseurs,…).
Structure
- Entête → décrit le contenu, entêtes des procédures/fonctions et curseurs
- Corps → définition des procédures/fonctions et curseurs, plus les éléments locaux au package
Entête
CREATE PACKAGE nom_package AS -- définition de types -- déclaration de varaibles publiques -- prototypes des curseurs -- prototypes des procedures/ fonctions END [nom_package];
Pour la définition d'un prototype de curseur dans le package :
CURSOR nom_curseur RETURN type_resultat;
Pour la définition d'un prototype de fonction/procédure dans le package :
FUNCTION nom_fonction(parmetres) RETURN type_retour; PROCEDURE nom_procedure(parmetres);
Corps
CREATE PACKAGE BODY nom_package AS -- déclarations de types et variables locales au package -- définition procedures/fonctions/curseurs locaux au package -- corps des procedures/fonctions publiques -- implémentation des curseurs publics END [nom_package];
L'implémentation du curseur dans le corps du package :
CURSOR nom_curseur RETURN type_resultat IS SELECT ...;
Faire précéder l'élément du package par le nom du packge séparés par un point :
nom_package.nom_element;
Les déclencheurs
Bloc PL/SQL associé à une table qui va s'exécuter à des requêtes LMD (insert
, DELETE
ou UPDATE
) et à des moments précis. L'exécution peut se faire avant ou après la vérification des contraintes d'intégrités. C'est une solution procédurale pour définit des contraintes complexes ou qui prennent en compte des données issues de plusieurs lignes ou plusieurs tables. Les déclencheurs ne doivent pas remplacer les contraintes d'intégrité, car ces derniers sont plus rapides. Les déclencheurs ne tiennent pas compte des données déjà présentes dans la base de données au moment où ils sont définis. Le bloc PL/SQL associé au déclancheur peut être exécuté pour chaque ligne (FOR EACH ROW
) ou une seule fois pour chaque requête du LMD (option par défaut).
Par exemple, garantir qu'un membre de la bibliothèque ne peut pas emprunter plus de cinq documents. Un déclencheur sera exécuté lors d'une insertion d'emprunt.
Syntaxe de la définition d'un déclencheur
CREATE [OR REPLACE] TRIGGER nom_du_declencheur { BEFORE / AFTER / INSTEAD OF } -- peuvent être combinés avec l'opérateur OR { INSERT / DELETE / UPDATE [OF col1, col2, ...]} -- peuvent être combinés avec l'opérateur OR ON nom_de_la_table [FOR EACH ROW] [WHEN (condition)] -- Ensuite le bloc PL/SQL
BEFORE
→ Le bloc PL/SQL est exécuté avant la vérification des contraintes et la mise à jour (INSERT
,UPDATE
ouDELETE
) de la table.AFTER
→ Le bloc PL/SQL est exécuté après la vérification des contraintes et la mise à jour de la table.INSTEAD OF
→ Le bloc PL/SQL remplace le traitement standard associé à la requête associé au déclencheur.INSERT / DELETE / UPDATE [OF col1, col2, …]
→ Requête associée au déclencheur.FOR EACH ROW
→ Le bloc PL/SQL s'éxécute pour chaque ligne traitée par la requête.WHEN
→ La condition doit être vraie pour que le bloc s'exécute.
Accès aux données de la table
Dans le bloc PL/SQL, les données de la table sont inaccessibles, sauf la ligne en cours de modification à l'aide de RECORD
, OLD
et NEW
qui ont la même structure que la table/vue associée. Ces trois variables peuvent être utilisés dans la clause WHEN
du déclancheur et dans le bloc PL/SQL. Dans ce dernier cas, ces variables sont considérés comme des variables externes. On doit donc les préfixer par deux-point (:
).
OLD
désigne la ligne avant suppression (DELETE
) ou la ligne avant modification (UPDATE
)NEW
désigne la ligne insérée (INSERT
) ou la ligne après modification (UPDATE
)