SGBD Oracle: Introduction to PL/SQL

END ;

4. Les déclarations PL/SQL

La partie déclarative dans un bloc PL/SQL, peut comporter trois types de déclarations. Elle est délimitée par les mots-clé § DECLARE, qui spécifie le début et § BEGIN, qui signifie la fin de la déclaration et le début de la partie des

commandes.

Les types de déclarations possibles dans cette partie sont les suivants : § déclaration des variables et des constantes, § déclaration de curseurs, § déclaration des exceptions.

M. WALID MELIANI

PAGE 7

SGBD ORACLE

4.1. Types de données

Chaque variable ou constante utilisée dans un bloc PL/SQL, possède un type de données qui détermine son format de stockage, sa contrainte et son champ valide de valeurs.

PL/SQL offre deux variétés de types de données prédéfinies : § scalaire, § composé.

Types de données PL/SQL

Types Scalaires

Types Composés

record

table

binary_integer natural positive decimal float integer real smallint number char long varchar varchar2 boolean date rowid raw long raw

binary_integer : entier de 2(p-31) à 2(p+31)+1 natural : entiers naturels positive : entiers positifs decimal, float, integer, real et sallint : sous-types de number

char : chaîne de caractères jusqu'à 32767 caractères (au lieu de 255 dans la définition d'une colonne de table) varchar2 : chaîne de caractères jusqu'à 32767 caractères (au lieu de 2000 dans la définition d'une colonnne de table) long : équivalent à varchar2 sauf 2go dans la base.

4.2. Conversion des types de données

Les conversions des types de données en PL/SQL, sont regroupées en deux familles : §

les conversions explicites avec les fonctions définies dans Sql telles que to_date, to_char, etc.

§

les conversions implicites sont réalisées automatiquement par PL/SQL et fonctionnent selon les trois règles suivantes :

M. WALID MELIANI

PAGE 8

SGBD ORACLE

1. évaluation d'expressions, 2. affectation des variables, 3. affectation d'arguments.

4.3. Variables et constantes

La déclaration d'une variable consiste à allouer un espace pour stocker et modifier une valeur. Elle est typée et peut recevoir une valeur par défaut et/ou un statut NOT NULL. Une constante est définie comme une variable, mais l'utilisateur ne peut pas modifier son contenu.

4.3.1. La définition des variables en PL/SQL

Les variables se définissent dans la partie DECLARE du bloc PL/SQL en utilisant la syntaxe suivante :

nomvariable [CONSTANT] {type | variable%TYPE | table.%ROWTYPE } [NOT NULL] [{:= | DEFAULT } expression PL/SQL]

Remarques : § L'attribut constant permet de figer l'affectation d'une variable. § L'attribut 'Not Null' rend obligatoire d'initialiser la variable lors de sa définition. § On peut faire référence à une colonne d'une table par l'instruction : nom_variable

TABLE.COLONNE%TYPE

§ On peut faire référence à une ligne d'une table par l'instruction nom_variable

TABLE%ROWTYPE

§ On peut faire référence à une variable précedemment définie par l'instruction :

nom_variable Pnom_variable%TYPE

L'initialisation d'une variable de fait par l'opérateur : ':=' suivi : § d'une constante, § d'une expression PL/SQL, § d'une fonction PL/SQL.

Une expression boolénne donne comme résultat TRUE, FALSE ou NULL. Les variables peuvent également être définies dans l'environnement extérieur au Bloc PL/SQL : - champs de l'écran en Sql*Forms, - variables définies en langage hôte dans Pro*. Ces variables seront utilisées préfixées de ':'.

4.3.2. Exemples de déclaration de variables

number(9,2);

Total Nom Longeur Date_Creation Date; Numero

char(10):='Fischer'; number not null := length (Nom) * 2;

EMPLOYE.EMPNO%TYPE;

M. WALID MELIANI

PAGE 9

SGBD ORACLE

Dpt Prenom Pi

DEPARTEMENT%ROWTYPE; Nom%TYPE; Constant Number:=3.14;

4.3.3. L'assignement des variables PL/SQL

Deux possibilités d'assignement ou d'affectation sont disponibles : 1. par l'opérateur d'affectation : ':=', 2. par la clause Select ... Into ... .

La difficulté dans l'utilisation de la clause Select résulte du nombre de lignes ou d'occurrences retourné.

Si le Select retourne une et une seule ligne l'affectation s'effectue correctement.

Par contre, Si le Select retourne 0 ligne : NO_DATA_FOUND ou Si le Select retourne plusieurs lignes : TOO_MANY_ROWS une erreur PL/SQL est générée.

4.4. Les tableaux (table PL/SQL)

Nous avons vu dans le tableau des types de données que le langage PL/SQL fournit deux types d'objets composés : § §

les tables (TABLE), les enregistrements (RECORD).

Les tableaux sont conçus comme les tables de la base de données. Ils possèdent une clé primaire (index) pour accéder aux lignes du tableau. Un tableau, comme une table, ne possède pas de limite de taille. De cette façon, le nombre d'éléments d'un tableau va croître dynamiquement.

Les tableaux peuvent posséder une colonne et une clé primaire. Par contre ni la colonne, ni la clé primaire (index) ne peut être nommé.

La colonne peut être de n'importe quel type scalaire, mais la clé primaire doit être du type BINARY_INTEGER. Remarque : Les prochaines versions de PL/SQL pourront gérer des tableaux avec plusieurs colonnes nommées et possédant des clés primaires composés et de n'importe quel type.

4.4.1. La déclaration d'un tableau

Les tableaux PL/SQL doivent être déclarés en deux étapes : 1. Déclaration du type de la TABLE 2. Déclaration d'une table de ce type.

La syntaxe pour déclarer un type TABLE dans la partie déclarative d'un bloc est :

TYPE nom_type IS TABLE OF {typecolonne | variable%TYPE | table.column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER ;

M. WALID MELIANI

PAGE 10

SGBD ORACLE

nomtype : utilisé ensuite dans la déclaration des tables PL/SQL. typecolonne : type de données comme CHAR, DATE ou NUMBER.

Lorsque le type est déclaré, vous pouvez déclarer des tableaux de ce type, ainsi :

nom_tab nom_type ;

nom_tab : correspond à un tableau PL/SQL.

4.4.2. L'accès aux éléments d'un tableau

Pour accéder à un élément du tableau, vous devez spécifier une valeur de clé primaire en respectant la syntaxe suivant :

nom_tab(valeur_cle_primaire) ;

valeur_cle_primaire : doit être du type BINARY_INTEGER ( -231 -1 à 231 -1)

Pour affecter la valeur d'une expression PL/SQL à un élément du tableau utiliser la syntaxe suivante :

nom_tab(valeur_cle_primaire) := expression_plsql;

4.4.3. Exemple

DECLARE TYPE nom_tabtype IS TABLE OF CHAR(25) INDEX BY BINARY_INTEGER ; ... tnom nom_tabtype ; ... BEGIN ... tnom(1):='Dupont Marc' ; ... END ;

4.5. Les enregistrements prédéfinis (record PL/SQL)

La restriction posée par l'utilisation du type %ROWTYPE pour déclarer un enregistrement réside dans le manque de spécification des types de données au niveau de l'enregistrement.

L'implémentation du nouveau type composé nommé RECORD a permis la levée de cette restriction.

4.5.1. La déclaration d'un enregistrement

Par analogie aux tableaux PL/SQL, la déclaration d'un enregistrement se fait également en en deux étapes :

1. Déclaration du type de l'enregistrement 2. Déclaration de la variable sur le type défini.

M. WALID MELIANI

PAGE 11

SGBD ORACLE

Vous pouvez déclarer un type RECORD dans la partie déclarative d'un bloc, d'un sous-programme ou d'un package en utilisant la syntaxe suivante :

TYPE nom_type IS RECORD (champ {typechamp | table.column%TYPE } [NOT NULL], champ {typechamp | table.column%TYPE } [NOT NULL],...)

nomtype : utilisé ensuite dans la déclaration des tables PL/SQL. typecolonne : type de données comme CHAR, DATE ou NUMBER.

Lorsque le type est déclaré, vous pouvez déclarer des enregistrements de ce type, ainsi :

nom_enr nom_type ;

nom_enr : correspond à un Record PL/SQL.

4.5.2. L'accès aux champs d'un enregistrement

Pour accéder à un élément d'une variable de type record, il suffit d'utiliser la syntaxe suivante :

nom_enr.nom_champ

Pour affecter la valeur d'une expression PL/SQL à un élément de l'enregistrement utiliser la syntaxe suivante :

nom_enr.nom_champ := expression_plsql;

4.5.3. Exemple

DECLARE TYPE ADRESSE IS RECORD ( Numero smallint, Rue char(35), CodePost char(5), Ville char(25), Pays char(30) ); TYPE CLIENT IS RECORD ( NumCli smallint, NomCli char(40), AdrCli ADRESSE, CA number) ; monclient CLIENT; BEGIN ... monclient.NumCli:=1234; monclient.NomCli:='Dupont SARL'; monclient.AdrCli.Numero:=10; END ;

M. WALID MELIANI

PAGE 12

SGBD ORACLE

5. Les structures de contrôles

5.1.

Affectation

5.1.1. Affectation simple

L’opérateur d’affectation directe est “:=”

numero := 0; numero := numero + 1;

5.1.2. Valeurs issues d'une base de données

On utilise l’opérateur INTO pour affecter une ou plusieurs valeurs issues d’une requête à une ou plusieurs variables.

SELECT codc, nomc INTO v_code, v_nom FROM client WHERE codc=100;

5.2.

Instruction d’Entrée/Sortie

5.2.1. Instructions d’entrée

La saisie d'une chaîne de caractères ou d’un nombre peut se faire par l’opérateur &. Oracle substitue la suite des caractères saisis à '&var' ou '%&var%'. Pour marquer la fin du paramètre de saisie, on peut mettre un point.

SELECT nomc, ville FROM CLIENT WHERE nom LIKE '%M&chaine.ET%';

On peut également saisir la valeur d’une variable globale avec l’instruction accept et cela en dehors du bloc PL/SQL. Cette commande suivie d’un nom de variable et la touche entrer vous donne la main pour saisir.

Pour utiliser la valeur de cette variable dans un bloc il suffit de la faire précéder par & uniquement si elle est numérique sinon si c’est une chaîne ou date il faut en plus l’encadrer par des côtes. SQL>accept Vnom SQL>Ben Salah

5.2.2. Instructions de Sortie

Pour afficher un message à l’écran il suffit d’utiliser la commande Prompt suivie d’un message. Cette commande est utilisée en dehors des blocs.

SQL>prompt Bonjour à tous ! SQL> Bonjour à tous !

Pour afficher des résultats on utilise le package (ensemble de procédures et fonctions) DBMS_OUTPUT. Tout d'abord, avant le bloc PL/SQL, on doit utiliser l'instruction : Set serveroutput on

Puis pour chaque affichage : dbms_output.put_line('texte'||X); -- (remarque : la concaténation de chaînes de caractères avec : ||)

M. WALID MELIANI

PAGE 13

SGBD ORACLE

5.2.3. Exemple:

SQL>Accept code SQL>600 SQL>SET SERVEROUTPUT ON

DECLARE V_ville varchar2(20) ; BEGIN SELECT ville INTO V_ville FROM Client WHERE codc=&code; DBMS_OUTPUT.PUT_LINE(‘La ville du client ‘|| ‘&code’||’:‘|| V_ville); END ; /

5.3. Les Traitements Conditionnels

Les instructions conditionnelles doivent permettre de contrôler le moment où des instructions sont exécutées.

Syntaxe :

IF condition_plsql Then commandes [Else commandes ] [ELSIF condition_plsql Then commandes [Else commandes ] ] END IF;

La condition peut utiliser les variables définies ainsi que tous les opérateurs présents dans SQL : =, <, >, <=, >=, <>, IS NULL, IS NOT NULL.

Else est utilisé si les instructions qui suivent ne possèdent pas de conditions. ELSIF est utilisé si les instructions qui suivent possèdent des conditions.

Exemple :

M. WALID MELIANI

PAGE 14

SGBD ORACLE

DECLARE vjob char(10); vnom emp.ename%type:='Miller'; message char(30);

BEGIN Select job into vjob from emp where ename=vnom;

-- contrôle de la valeur de vjob --

If vjob is NULL then message:= vnom || 'pas de travail'; elsif vjob='Vendeur' then update emp set comm=1000 where ename=vnom; message:= vnom || 'a 1000 Frs de commission'; else update emp set comm=0 where ename=vnom; message:= vnom || 'pas de commission '; end if; insert into resultat values (NULL,NULL,message); commit; END ; / select * from resultat;

5.4. Les Traitements Répétitifs

Les traitements répétitifs permettre de répéter une suite de comandes, sans en répéter les instructions.

PL/SQL nous offre la possibilité d'effectuer des traitements répétitifs grâce à trois types d'instructions.

5.4.1. L'instruction LOOP

Acronyme de boucle, LOOP permet de répéter une séquence de commandes. Cette séquence est comprise entre le mot-clé LOOP, indiquant le début d'une boucle et END LOOP, spécifiant sa fin.

Syntaxe :

BEGIN [<<Label>>] LOOP ... instructions ... END LOOP [<<Label>>]; END ;

M. WALID MELIANI

PAGE 15

Les commandes EXIT, EXIT WHEN condition et GOTO permettent de sortir de la Boucle.

SGBD ORACLE

Exemple :

DECLARE nombre number; BEGIN nombre:=0; LOOP nombre:=nombre+1; if nombre > 10 then exit; end if; END LOOP ; END ;

5.4.2. L'instruction FOR ... LOOP

La limitation des traitements répétitifs peut se faire en utilisant la clause FOR. Cette clause permet également d'incrémenter une variable.

Syntaxe :

[<<Label>>] FOR compteur IN [REVERSE] var_debut .. var_fin LOOP ... instructions ... END LOOP [<<Label>>];

compteur : est une variable de type entier, locale à la boucle. Sa valeur de départ est égale par défaut à la valeur de l'expression entière de gauche (var_debut). Elle s'incrémente de 1, après chaque traitment du contenu de la boucle, jusqu'à ce qu'il atteigne la valeur de droite (var_fin).

Le mot clé REVERSE permet d'utiliser une décrémentation de 1, en prenant comme valeur initiale celle de l'expression entière de droite et comme valeur de fin celle de l'expression entière de gauche.

Il est possible d'arréter la boucle avant sa fin normale par une commande EXIT conditionnelle.

5.4.3. L'instruction WHILE ... LOOP

La clause While permet d'exécuter le contenu d'une boucle tant que la condition est vérifiée. Syntaxe :

M. WALID MELIANI

PAGE 16

SGBD ORACLE

WHILE condition_plsql LOOP ... instructions ... END LOOP ;

La condition est une expression définie en combinant les opérateurs : <, >, = , !=, <=, >=; and, or ... Expression est une constante, une variable, le résultat d'une fonction.

Exemple : DECLARE salaire emp.sal%type; manager emp.mgr%type; nom emp.ename%type; num_debut constant number(4):=7902; BEGIN select sal, mgr, ename into salaire, manager, nom from emp where empno=num_debut; WHILE salaire < 4000 LOOP select sal, mgr, ename into salaire, manager, nom from emp where empno=manager; END LOOP ; insert into resultat values (NULL,Salaire,Nom); commit; END ;

6. La Gestion des erreurs

Le mécanisme de gestion d'erreurs dans PL/SQL est appelé gestionnaire des exceptions. Il permet au développeur de planifier sa gestion et d'abandonner ou de continuer le traitement en présence d'une erreur.

Il faut affecter un traitement approprié aux erreurs apparues dans un bloc PL/SQL.

C'est pourquoi on distingue 2 types d'erreurs ou d'exceptions : § Erreur interne Oracle (Sqlcode = 0) : dans ce cas la main est rendue directement

au système environnant.

§ Anomalie déterminée par l'utilisateur.

La solution :

1. Donner un nom à l'erreur (si elle n'est pas déjà prédéfinie), 2. Définir les anomalies utilisateurs, leur associer un nom, 3. Définir le traitement à effectuer.

6.1.

Les exceptions internes

Une erreur interne est produite quand un bloc PL/SQL viole une règle d'Oracle ou dépasse une limite dépendant du système d'exploitation.

M. WALID MELIANI

PAGE 17

SGBD ORACLE

Les erreurs Oracle générées par le noyau sont numérotées, or le gestionnaire des exceptions de PL/SQL, ne sait que gérer des erreurs nommées. Pour cela PL/SQL a redéfini quelques erreurs Oracle comme des exceptions. Ainsi, pour gérer d'autres erreurs Oracle, l'utilisateur doit utiliser le gestionnaire OTHERS ou EXCEPTION_INIT pour nommer ces erreurs.

Les exceptions fournies par Oracle sont regroupées dans ce tableau :

Nom d'exception

Valeur SqlCode

CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE TOO_MANY_ROWS TRANSACTION_BACKED_OUT VALUE_ERROR ZERO_DIVIDE

-6511 -1 -1001 -1722 -1017 -1403 -1012 -6501 -6500 -51 -1422 -61 -6502 -1476

Erreur Oracle ORA-06511 ORA-00001 ORA-01001 ORA-01722 ORA-01717 ORA-01413 ORA-01012 ORA-06501 ORA-06500 ORA-00051 ORA-01422 ORA-00061 ORA-06502 ORA-01476

OTHERS : toutes les autres erreurs non explicitement nommées.

Pour gérer les exceptions, le développeur doit écrire un gestionnaire des exceptions qui prend le contrôle du déroulement du bloc PL/SQL en présence d'une exception.

Le gestionnaire d'exception fait partie du bloc PL/SQL et se trouve après les commandes. Il commence par le mot clé EXCEPTION et se termine avec le même END du bloc.

Chaque gestion d'exception consiste à spécifier son nom d'erreur après la clause WHEN et la séquence de la commande à exécuter après le mot clé THEN, comme le montre l'exemple suivant :

Exemple : Utilisation des erreurs prédéfinies

M. WALID MELIANI

PAGE 18

SGBD ORACLE

DECLARE wsal emp.sal%type;

BEGIN select sal into wsal from emp;

EXCEPTION WHEN TOO_MANY_ROWS then ... ; -- gérer erreur trop de lignes WHEN NO_DATA_FOUND then ... ; -- gérer erreur pas de ligne WHEN OTHERS then ... ; -- gérer toutes les autres erreurs END ;

Remarques : L'exception optionnelle OTHERS est toujours située à la fin des exceptions. Pour rattacher une séquence de commandes à plus d'une exception, l'utilisateur peut utiliser l'opérateur booléen OR comme suit : WHEN erreur1 OR erreur2 THEN -- gérer erreur12

6.2. Les exceptions utilisateurs (externes)

PL/SQL permet à l'utilisateur de définir ses propres exceptions. La gestion des anomalies utilisateur peut se faire dans un bloc PL/SQL en effectuant les opérations suivantes :

1. Nommer l'anomalie (type exception) dans la partie Declare du bloc.

DECLARE Nom_ano Exception;

2. Déterminer l'erreur et passer la main au traitement approprié par la commande Raise.

BEGIN ... If (condition_anomalie) then raise Nom_ano ;

3. Effectuer le traitement défini dans la partie EXCEPTION du Bloc.

EXCEPTION WHEN (Nom_ano) then (traitement);

Exemple : Utilisation des erreurs prédéfinies et nommées

M. WALID MELIANI

PAGE 19

SGBD ORACLE

DECLARE wsal emp.sal%type; sal_zero Exception; BEGIN select sal into wsal from emp; if wsal=0 then raise sal_zero end if;

EXCEPTION WHEN sal_zero then -- gérer erreur salaire WHEN TOO_MANY_ROWS then ... ; -- gérer erreur trop de lignes WHEN NO_DATA_FOUND then ... ; -- gérer erreur pas de ligne WHEN OTHERS then ... ; -- gérer toutes les autres erreurs END ;

Le développeur peut utiliser les fonctions Sqlcode et Sqlerrm pour coder les erreurs Oracle en Exception. Sqlcode est une fonction propre à PL/SQL qui retourne le numéro (généralement négatif) de l'erreur courante. Sqlerrm reçoit en entrée le numéro de l'erreur et renvoie en sortie le message de l'erreur codé sur 196 octets.

7. Les curseurs en PL/SQL

Pour traiter une commande SQL, PL/SQL ouvre une zone de contexte pour exécuter la commande et stocker les informations.

7.1. Définition

Le curseur permet de nommer cette zone de contexte, d'accéder aux informations et éventuellement de contrôler le traitement. Cette zone de contexte est une mémoire de taille fixe, utilisée par le noyau pour analyser et interpréter tout ordre Sql. Les statuts d'exécution de l'ordre se trouve dans le curseur.

7.2. Les types de curseurs

§ Le curseur explicite Il est créé et géré par l'utilisateur pour traiter un ordre Select qui ramène plusieurs lignes. Le traitement du select se fera ligne par ligne.

M. WALID MELIANI

PAGE 20

SGBD ORACLE

§ Le curseur implicite Il est généré et géré par le noyau pour les autres commandes Sql.

7.3. Les étapes d'utilisation d'un curseur explicite

Pour traiter une requête qui retourne plusieurs lignes, l'utilisateur doit définir un curseur qui lui permet d'extraire la totalité des lignes sélectionnées.

L'utilisation d'un curseur pour traiter un ordre Select ramenant plusieurs lignes, nécessite 4 étapes :

1. Déclaration du curseur 2. Ouverture du curseur 3. Traitement des lignes 4. Fermeture du curseur.

7.3.1. La déclaration d'un curseur

La déclaration du curseur permet de stocker l'ordre Select dans le curseur.

La syntaxe de définition : Le curseur se définit dans la partie DECLARE d'un bloc PL/SQL.

Cursor nomcurseur [(nomparam type [,nomparam type, ...)] IS commande SELECT ;

Exemple :

DECLARE Cursor DEPT10 is select ename, sal from emp where depno=10;

7.3.2. L'ouverture et la fermeture du curseur

L'étape d'ouverture permet d'effectuer : 1. l'allocation mémoire du curseur ; 2. l'analyse sémantique et syntaxique de l'ordre (parsing) ; 3. le positionnement de verrous éventuels (si select for update...)

L'étape de fermeture permet d'effectuer : la libération de la place mémoire.

La syntaxe : Dans la partie traitement du bloc PL/SQL Avoir préalablement déclaré le curseur pour l'ouvrir Avoir préalablement ouvert le curseur pour le fermer

M. WALID MELIANI

PAGE 21

OPEN nomcurseur [(nomparam type [,nomparam type, ...)]

SGBD ORACLE

/* traitement des lignes */

CLOSE nomcurseur

Exemple :

Begin ... OPEN DEPT10 /* traitement des lignes */ CLOSE DEPT10

7.3.3. Le traitement des lignes

Il faut traiter les lignes une par une et renseigner les variables réceptrices définies dans la partie Declare du bloc.

Syntaxe : Dans la partie traitement du bloc Pl/Sql Avoir préalablement ouvert le curseur puis

FETCH nomcurseur INTO { nomvariable [,nomvariable] ... | nomrecord }

L'ordre fetch ne ramène qu'une seule ligne à la fois.

De ce fait il faut recommencer l'ordre pour traiter la ligne suivante. Exemple :

Begin OPEN DEPT10 LOOP FETCH DEPT10 into vnom, vsalaire; /* traitement ligne */ END LOOP;

7.4. Les attributs d'un curseur

Les attributs d'un curseur nous fournissent des informations quant à l'exécution de l'ordre. Elles sont conservées par Pl/Sql après l'exécution du curseur (explicite ou implicite).

Ces attributs permettent de tester directement le résultat de l'exécution.

Tous les attributs ont un nom.

M. WALID MELIANI

PAGE 22

SGBD ORACLE

Curseur implicite

Curseur explicite :

Sql%Found Sql%Notfound Sql%Isopen Sql%Rowcount

Nomcurseur%Found Nomcurseur%Notfound Nomcurseur%Isopen Nomcurseur%Rowcount Nomcurseur%Rowtype

7.4.1. L'attribut %Found

Signification Cet attribut est de type booléen : soit vrai, soit faux. Le curseur implicite est vrai si les instructions insert, update, delete traitent au moins une ligne. Le curseur explicite est vrai si le Fe