Cours langage PL/SQL fonctions et procédures

Cours langage PL/SQL fonctions et procédures, tutoriel & guide de travaux pratiques en pdf.

Utilisation de collections

• Un élément non initialisé est NULL de façon atomique.
• Pour initialiser un élément, il est possible d’utiliser le constructeur implicite de son type, d’effectuer une affectation (copier un autre élément) ou de faire une extraction de la base de données (par un FETCH).
• Dans un bloc PL/SQL, un VARRAY (ou une table imbriquée) déclaré(e) demeure NULL jusqu’à ce qu’il (elle) soit initialisé(e).
• Il est impossible de faire quoi que ce soit avec une collection NULL. L’exception COLLECTION_IS_NULL survient alors.
• Avant de le (la) manipuler, il est prudent d’effectuer le test IF nom_collection IS NULL sur un VARRAY (ou une table imbriquée).
• Il n’est pas permis de comparer deux collections pour une égalité ou une inégalité. Par conséquent, une collection ne peut pas apparaître dans une clause DISTINCT, GROUP BY ou ORDER BY.
• Dans PL/SQL, pour référer à un élément d’une collection, on utilise le construit ou la forme nom_collection (indice) ; noter que l’exception VALUE_ERROR surviendra si indice est NULL ou non convertible en INTEGER, sauf le cas des tableaux associatifs dont l’indice est de type VARCHAR2.
• Dans SQL, pour manipuler (SELECT, INSERT, UPDATE, DELETE) les éléments individuels d’une collection, il faut utiliser l’opérateur TABLE.
• Il n’est pas possible de référer à un élément de VARRAY dans un INSERT, UPDATE ou DELETE, présentement.
• Il faut donc utiliser un énoncé impératif de PL/SQL sur sa copie locale et employer cette dernière pour effectuer un UPDATE dans la base de données.
• La forme TABLE(CAST(coll_locale AS SQL_coll_type)) permet de manipuler en SQL une collection locale comme si elle était une table de la base de données.
• Dans PL/SQL on peut manipuler les collections locales à l’aide des méthodes suivantes :
– EXISTS(n IN INT) retourne TRUE si le ne élément existe.
– COUNT retourne le nombre d’éléments.
– LIMIT retourne le nombre maximal d’éléments d’un VARRAY ou NULL (s’il s’agit d’une table imbriquée).
– FIRST et LAST retournent les premier et dernier indices ou NULL.
– PRIOR(n IN INT) et NEXT(n IN INT) retournent les indices précédant et succédant l’indice n ou NULL.
– EXTEND et EXTEND(n IN INT) ajoutent à la fin de la collection, un ou plusieurs éléments tous NULL.
– EXTEND(n IN INT, i IN INT) ajoute à la fin de la collection, n copies de l’élément i.
– TRIM et TRIM(n IN INT) suppriment un ou plusieurs éléments à la fin de la collection.
– DELETE supprime tous les éléments d’une table.
– DELETE(n IN INT) supprime l’élément n d’une table.
– DELETE(m IN INT, n IN INT) supprime les éléments de m à n.
• La manipulation d’une collection en PL/SQL peut causer les erreurs suivantes :
– COLLECTION_IS_NULL, VALUE_ERROR, SUBSCRIPT_OUTSIDE_LIMIT, SUBSCRIPT_BEYOND_COUNT, NO_DATA_FOUND.
• DELETE ne s’applique pas à un VARRAY.
• L’interaction entre DELETE et TRIM est complexe.
• Il est préférable de traiter les tables imbriquées comme les tableaux de taille fixe et d’utiliser seulement DELETE (sans TRIM !).
• Il est possible de gérer une collection comme une pile en utilisant les méthodes EXTEND (pour faire un PUSH) et TRIM (pour faire un POP).
• Un constructeur de collection acceptent les éléments d’une collection comme arguments.
• Une collection vide s’obtient par la forme constructeur( ).

Fonctions et procédures

• Les fonctions et procédures sont des sous-programmes considérées comme des blocs PL/SQL nommés.
• Les fonctions et procédures cataloguées sont des sous-programmes PL/SQL autonomes qui sont compilés et stockées dans le dictionnaire de données à l’aide d’un
CREATE OR REPLACE FUNCTION / PROCEDURE souvent exécuté en mode dialogue à travers SQL*PLUS.
• Une fonction ou procédure non autonome peut être déclarée dans un bloc PL/SQL, un sous-programme ou un paquetage sans utiliser la clause CREATE OR REPLACE.
• Une déclaration de fonction ou procédure doit alors se trouver à la fin d’une section de déclaration.
• La définition ou déclaration d’une fonction ou procédure comprend 2 parties : le corps et la spécification.
• Une fonction et procédure doit être définie ou déclarée avant d’être appelée dans une autre
• Dans le cas des fonctions ou procédures mutuellement récursive, il est nécessaire de faire appel au mécanisme d’annonce (a forward declaration) en ne donnant que la spécification d’une fonction ou procédure.
• Pour pouvoir être appelée en SQL, une fonction cataloguée doit obéir aux règles de « pureté » ci-dessous, qui servent à contrôler les effets de bord (side effects).
• Si elle est appelée par un SELECT ou un INSERT, UPDATE ou DELETE rendu parallèle, la fonction ne peut modifier aucune table de la BD.
• Si elle est appelée dans un INSERT, UPDATE ou DELETE, la fonction ne peut interroger ni modifier aucune table modifiée par cet énoncé.
• Si elle est appelée dans un SELECT, INSERT, UPDATE ou DELETE, la fonction ne peut pas exécuter d’énoncés de contrôle de transaction, de séance de travail ou de système. Elle ne peut pas non plus exécuter d’énoncés du LDD.
• Pour vérifier les violations de ces règles, il est possible d’utiliser la directive au compilateur (the) RESTRICT_ REFERENCES (pragma) pour assurer que la fonction ne lit aucune table de BD (RNDS) ni aucune variable de paquetage (RNPS), n’écrit dans aucune table de la BD (WNDS) ni aucune variable de paquetage (WNPS).
• Mode IN de paramètres/arguments
– Choix implicite : disponible
– Passage de valeur : de l’appelant à l’appelé
– Sémantique : une constante
– Affectation de valeur : défendue
– Argument : une constante, une variable initialisée, un littéral ou une expression
– Passage d ’argument : par référence
– Particularité : possibilité de valeur initialisée implicitement
• Mode OUT de paramètres/arguments
– Choix implicite : non disponible
– Passage de valeur : de l’appelé à l’appelant
– Sémantique : une variable locale
– Affectation de valeur : obligatoire
– Argument : une variable
– Passage d’argument : par valeur sauf quand NOCOPY est spécifié (une demande au compilateur d’utiliser le passage par référence)
– Particularités : argument initialisé à NULL, sans valeur affectée dans le cas d’une exception non traitée.