Outils pour utilisateurs

Outils du site


bases_de_donnees:oracle:pl_sql

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

bases_de_donnees:oracle:pl_sql [2015/07/31 19:42] (Version actuelle)
Ligne 1: Ligne 1:
 +====== 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
 +
 +<code plsql>
 +[DECLARE] ​
 +  declaration_statements ​
 +BEGIN 
 +  execution_statements ​
 +[EXCEPTION] ​
 +  exception_handling_statements ​
 +END; 
 +</​code>​
 +
 +
 +  - 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
 +
 +
 +
 +<code plsql>
 +DECLARE
 +  x NUMBER := 16;
 +  anniversaire DATE;
 +  nom VARCHAR(20);​
 +  pi CONSTANT NUMBER := 3.1416;
 +</​code>​
 +
 +  - 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
 +<code plsql>
 +SQL>​varibale x NUMBER
 +</​code>​
 +
 +===== 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 ======
 +{{  :​bd:​sql:​pl_sql_datatype_conversion.jpg?​200|}}
 +
 +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. ​
 +
 +<code sql>
 +SELECT {*|liste d’expressions} ​
 +INTO liste de variables ​
 +FROM ...; 
 +
 +FETCH nom de curseur ​
 +INTO liste de variables; ​
 +</​code>​
 +
 +====== Exemples ======
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +====== 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 ====
 +<code plsql>
 +IF condition THEN
 +  instruction1;​
 +ELSE
 +  intruction2;​
 +END IF;
 +</​code>​
 +
 +<code plsql>
 +IF condition1 THEN
 +  instruction1;​
 +ELSEIF condition2 THEN
 +  intruction2;​
 +ELSE
 +  instuction3;​
 +END IF;
 +</​code>​
 + 
 +Condition : expression logique qui utilise les opérateurs ''​='',​ ''<>'',​ ''<'',​ ''>'',​ ''​%%<​=%%'',​ ''>​='',​ ''​IS NULL'',''​IS NOT NULL'',​ ''​BETWEN'', ​ ''​LIKE'',​ ''​AND'',​ ''​OR''​.
 +
 +<code plsql>
 +IF NUM= 444 THEN
 +  UPDTE MEMBRE SET NOM='​Dupont'​
 +  WHERE  NUMERO = NUM;
 +  COMMIT;
 +ELSE
 +  ROLLBACK;
 +END IF;
 +</​code>​
 +
 +==== L'​instruction CASE ====
 +La structure alternative ''​CASE''​ prend deux formes. ​ La première forme est générique et la seconde est //​recherchée//​.
 +
 +<code plsql>
 +/* 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];​
 +</​code>​
 +<​note>​Dans les deux cas, seule la première condition vraie est exécutée.</​note>​
 +
 +<code plsql>
 +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;
 +</​code>​
 +Notez la ligne ''​SET serveroutput on;''​ qui permet l'​affichage avec ''​dbms_output.put_line();''​.
 +
 +===== Répétitives =====
 +
 +==== L'​insctruction LOOP ====
 +<code plsql>
 +/* Premère forme */
 +[<<​etiquette>>​]
 +LOOP
 +  intructions;​
 +END LOOP [etiquette];​
 +</​code>​
 +On sort de a boucle avec une instruction EXIT.
 +<code plsql>
 +  EXIT [etiquette] [WHEN condition];
 +</​code>​
 +
 +==== L'​instruction FOR ====
 +
 +<code plsql>
 +[<<​etiquette>>​]
 +FOR indice IN [REVERSE] exp1..exp2 LOOP
 +  instructions;​
 +END LOOP [etiquette];​
 +</​code>​
 +
 +==== L'​instruction WHILE ====
 +<code plsql>
 +[<<​etiquette>>​]
 +WHILE condition LOOP
 +  instructions;​
 +END LOOP [eiquette];
 +</​code>​
 +==== Boucle simple ====
 +
 +<code plsql>
 +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; ​
 +</​code>​
 +
 +====== 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 :
 +<code plsql>
 +CURSOR nom_curseur IS requete_SQL;​
 +</​code>​
 +
 +===== 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.
 +
 +<code plsql>
 +FETCH nom_curseur INTO [variable1, variable2, ...] | [record_name];​
 +</​code>​
 +  * //​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'​instruction ''​FETCH''​ qu'il y a de colonnes dans l'​instruction ''​SELECT''​ 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 un((Introduction to Oracle 9i PL/SQL Volume 1 (PDF), Oracle Press))
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== Fermeture =====
 +<code plsql>​CLOSE nom_curseur;</​code>​
 +
 +===== Forme simplifiée =====
 +Qui __déclare implicitement__ la variable de parcours, ouvre le cuseur, réalise les ''​FETCH''​ successifs et ferme le curseur.
 +<code plsql>
 +FOR variable IN nom_curseur LOOP
 +  instruction;​
 +END LOOP;
 +</​code>​
 +===== Mise à jour avec les curseurs =====
 +On peut utiliser un curseur pour modifier les données de la base de donnée.
 +<code plsql>
 +DECLARE
 +  CURSOR c IS
 +    SELECT * FROM Document
 +    FOR UPDATE OF Titre;
 +BEGIN
 +  -- ...
 +  UPDATE Document
 +  SET Titre = '​...'​
 +  WHERE CURRENT OF c;
 +</​code>​
 +
 +
 +===== 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 =====
 +<code plsql>
 +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;
 +</​code>​
 +
 +====== Gestion des exceptions ======
 +En Oracle il y a deux types d'​erreurs:​
 +  - Erreurs internes d'​Oracle
 +  - Erreurs dûe au programme
 +
 +<code plsql>
 +DECLARE
 +  ...
 +BEGIN
 +...
 +EXCEPTION
 +  // traitement des exceptions
 +  WHEN type1 THEN
 +  ​
 +END;
 +</​code>​
 +===== 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 :
 +<code plsql>
 +BEGIN
 +  ...
 +EXCEPTION
 +  WHEN TOO_MANY_ROWS THEN
 +    ...
 +  WHEN DUP_VAL_ON_INDEX THEN
 +    ...
 +END;
 +</​code>​
 +Ils font partie du portage standard, donc inutile de les déclarer.
 +
 +Pour les exceptions qui n'ont pas de nom :
 +<code plsql>
 +</​code>​
 +
 +===== 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''​.
 +<code plsql>
 +DECLARE
 +  nom_exception EXCEPTION;
 +BEGIN
 +  -- déclanchement
 +  IF probleme THEN
 +    RAISE nom_exception;​
 +
 +EXCEPTION
 +  WHEN nom_exception THEN
 +    ...
 +END;
 +
 +</​code>​
 +
 +===== 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 fonctions ''​SQLCODE''​ et ''​SQLERRM''​.
 +  - Associer un nom au numéro d'​exception à l'aide de la directive ''​PRAGMA EXCEPTION_INIT''​.
 +<code plsql>
 +  PRAGMA EXCEPTION_INIT (nom_exception,​ numero_dexception);​
 +</​code>​
 +
 +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.
 +<code plsql>
 +DECLARE
 +   ​verrou_mortel EXCEPTION
 +   ​PRAGMA EXCEPTION_INIT(verrou_mortel,​ -60);
 +BEGIN
 +  ...
 +EXCEPTION
 +  WHEN verrou_mortel THEN
 +    ROLLBACK;
 +END; 
 +</​code>​
 +
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== RAISE_APPLICATION_ERROR =====
 +Permet de déclancher des exceptions ​ en leur attribuant un numéro et un message.
 +<code plsql>
 +RAISE_APPLICATION_ERROR(numero,​ message [,​TRUE|FALSE]);​
 +</​code>​
 +  * Le numéro doit être entre -20999 et -20000.
 +  * Le message c'est 2048 caractères maximum
 +  * Optionnellement,​ le ''​TRUE''​ ou ''​FALSE'',​ ''​FALSE''​ par défaut.
 +    * ''​FALSE''​ -> L'​erreur remplace les autres
 +    * ''​TRUE''​ -> L'​erreur est placée dans la pile des erreurs
 +
 +
 +
 +
 +
 +====== Procédures et fonctions ======
 +On peut imbriquer des blocs PL/SQL :
 +<code plsql>
 +DECLARE
 +  ...
 +BEGIN
 +  ...
 +  DECLARE
 +    ...
 +  BEGIN
 +    ...
 +  EXCEPTION
 +    ...
 +  END;
 +  ...
 +END;
 +</​code>​
 +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 à :
 +<code plsql>
 +PROCEDURE p[(arg1 [IN|OUT] type, arg2, argN)] IS
 +    -- Ici la déclaration de variable locales
 +  BEGIN
 +    ...
 +  EXCEPTION
 +    ...
 +END P;
 +</​code>​
 +
 +===== Fonctions =====
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== Passage des arguments =====
 +Trois méthodes (qui sont optionnelles) :
 +  - ''​IN''​ -> La procédure/​fonction ne peut pas le modifier
 +  - ''​OUT''​ -> La procédure/​fonction ne peut pas s'y référer, mais uniquement lui affecter une valeur
 +  - ''​IN 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 :
 +<code plsql>
 +  CREATE [OR REPLACE] PROCEDURE p(...) IS ...
 +  CREATE [OR REPLACE] FUNCTION f(...) IS ...
 +</​code>​
 +
 +
 +
 +====== 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 ====
 +<code plsql>
 +  CREATE PACKAGE nom_package AS
 +    -- définition de types
 +    -- déclaration de varaibles publiques
 +    -- prototypes des curseurs
 +    -- prototypes des procedures/ fonctions
 +  END [nom_package];​
 +</​code>​
 +
 +Pour la définition d'un prototype de curseur dans le package :
 +<code plsql>
 +  CURSOR nom_curseur RETURN type_resultat;​
 +</​code>​
 +
 +Pour la définition d'un prototype de fonction/​procédure dans le package :
 +<code plsql>
 +  FUNCTION nom_fonction(parmetres) RETURN type_retour;​
 +  PROCEDURE nom_procedure(parmetres);​
 +</​code>​
 +
 +==== Corps ====
 +
 +<code plsql>
 +  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];​
 +</​code>​
 +L'​implémentation du curseur dans le corps du package :
 +<code plsql>
 +  CURSOR nom_curseur RETURN type_resultat
 +    IS SELECT ...;
 +</​code>​
 +
 +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 =====
 +
 +<code plsql>
 +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
 +
 +</​code>​
 +
 +  * ''​BEFORE''​ ->  Le bloc PL/SQL est exécuté **avant** la vérification des contraintes et la mise à jour (''​INSERT'',​ ''​UPDATE''​ ou ''​DELETE''​) 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''​)
 +
 +
 +
 +====== Exemple into ======
 +
 +
 +
 +<code plsql>
 +DECLARE
 +  doc document%ROWTYPE
 +  ...
 +BEGIN
 +  SELECT *
 +  INTO doc
 +  FROM document
 +  WHERE isbn='​101002';​
 +END;
 +</​code>​
  
bases_de_donnees/oracle/pl_sql.txt · Dernière modification: 2015/07/31 19:42 (modification externe)