LE LANGAGE PROCEDURAL D'ORACLE :

PARTIE 1 - LE LANGAGE PROCEDURAL D'ORACLE :
LE LANGAGE PL/SQL
I - INTRODUCTION Le PL/SQL est le langage procédural d'ORACLE, c'est une extension du
langage SQL qui est un langage ensembliste. PL/SQL = Procédural Language / SQL L'intérêt du PL/SQL est de pouvoir dans un même traitement allier la
puissance des instructions SQL et la souplesse d'un langage procédural.
Le fonctionnement de PL/SQL est basé sur l'interprétation d'un "bloc" de
commandes. Ce mode de fonctionnement permet d'obtenir des gains de
transmission et des gains de performances : Dans l'environnement SQL, les ordres du langage sont transmis et exécutés
les uns à la suite des autres
Dans l'environnement PL/SQL ; les ordres SQL et PL/SQL sont regroupés en
BLOCs ; un bloc ne demande qu'un seul transfert et une seule exécution de
l'ensemble des commandes contenues dans le bloc.
II - LE BLOC PL/SQL PL/Sql n'interprète pas une commande, mais un ensemble de commandes contenu
dans un programme ou "bloc" PL/Sql.
Un bloc est composé de trois sections : DECLARE
BEGIN [] EXCEPTION
END ; ou [END nom_bloc ;] Chaque instruction de n'importe quelle section doit se terminer par un ';'.
Possibilité de placer des commentaires : -- commentaire sur une ligne
ou /* commentaire sur
plusieurs lignes */ EXEMPLE sous SQL*PLUS de Personnal Oracle 7 ( Ecriture du programme PL/SQL sous le bloc notes nommé « PLSQL_EX1.sql » DECLARE -- Début du programme
sal_emp number(7,2); -- variable locale au bloc BEGIN /* Sélectionner le salaire de l'employé saisi au préalable dans
SQL*PLUS (num_emp) ,
l'augmenter de 10% si ce salaire est inférieur à 1000 */
SELECT sal into sal_emp FROM emp
where empno = '&num_emp';
If sal_emp < 1000 Then
UPDATE emp SET sal = sal * 1.1
WHERE empno = '&num_emp';
end if;
commit;
END;
/ -- Ne pas oublier ce slash qui termine le fichier
Test de notre premier programme :
( Sous SQL*PLUS, visualisation de la table emp
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
--------- ---------- --------- --------- -------- --------- --------- --
-------
7369 SMITH CLERK 7902 17/12/80 800
20
7499 ALLEN SALESMAN 7698 20/02/81 1600
300 30
.............
( Sous SQLPLUS définir la variable num_emp
SQL> define num_emp=7369
ou
SQL> PROMPT " Numéro du salarié désiré ?" -- ou écrire ces 2 lignes
directement
SQL> ACCEPT num_emp -- dans le programme PL/SQl avant
DECLARE
( Appel du programme Plsql_ex1.sql écrit précédemment et sauvegardé sous
c:\orawin95\gautier:
SQL> start ..\gautier\PLSQL_EX1 (start ou @)
ancien 8: where empno = '&num_emp';
nouveau 8: where empno = '7369';
ancien 12: WHERE empno = '&num_emp';
nouveau 12: WHERE empno = '7369';
Procédure PL/SQL terminée avec succès.
( Vérification de la modification sur la table emp
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
--------- ---------- --------- --------- -------- --------- --------- --
-------
7369 SMITH CLERK 7902 17/12/80 880
20
7499 ALLEN SALESMAN 7698 20/02/81 1600
300 30
..............
III - DECLARATION DES VARIABLES 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és 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.
Les types de variables utilisées en PL/Sql sont les suivantes : 1. variables locales > de type Oracle : reconnu par Oracle > faisant référence au dictionnaire de données 2. variables de l'environnement extérieur à PL/SQL > champs d'écran de Forms > variables hôtes définies en langage hôte dans Pro* > variables définies dans Sql*Plus (préfixées de &)
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.
1. Variables ou constantes locales de type Oracle et PL/Sql Nom-de-variable [CONSTANT] type [[NOT NULL] := expression] ;
Type Char(n), Number(n[,m]), date, boolean
Expression peut être une constante ou un calcul faisant
éventuellement référence à une
variable précédemment déclarée
DECLARE
Nom_du client Char(30) ;
X number / + 1 ; -- initialisation
PI constant number(7,5) := 3.14159 ;
Rayon number := 1 ;
Surface number(15,5) := pi * Rayon **2 ;
Reponse boolean ;
2. Variables faisant référence au même type qu'une colonne d'une table ou
même type qu'une autre variable Nom-de-variable nom_table.nom-colonne %type;
ou
Nom-de-variable1 Nom-de-variable2%type ;
DECLARE
Emp_Nom EMP.Empno%type ; -- Même type que la propriété Empno
X number(10,3) ;
Y X%type ; -- Du même type que x donc
number(10,3)
Ceci est intéressant pour des raisons de simplification d'écriture et
d'évolution .
IV- VALORISATION DES VARIABLES PL/SQL
Trois possibilités de valorisation de variables sont disponibles : 1. par l'opérateur d'affectation : ':=', 2. par la clause Select ... Into ... . 3. par le traitement d'un curseur dans la section Begin. (que nous
aborderons par la suite)
a) affectation de valeur Nom_Variable := Expression ;
Expression peut-être :
- une constante, une variable, un calcul
Les opérateurs de calcul sont :
- + ; - ; * ; / ;** ; || BEGIN
X := 0 ;
Vnom := 'Monsieur' || Vnom ; -- concaténation
Y := (X+5) * Y ; b) 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 ( test « nom_variable IS
NULL »)
Si le Select retourne plusieurs lignes : TOO_MANY_ROWS , une erreur
PL/SQL est générée.
SELECT {*/Liste d'expression} INTO Liste de variables FROM ... ;
DECLARE
VRef CHAR(10) ;
VPrix Articles.Prix%TYPE ;
Clt Clients.%ROWTYPE
BEGIN
SELECT RefArt, PrixArt INTO Vref, Vprix
FROM Articles WHERE DesArt = 'Cadeau' ;
SELECT * INTO Clt
FROM Clients WHERE NoClt = 10 ;
END ;
V - STRUCTURES DE CONTRÔLES a) Structure alternative IF condition Then
Instructions ;
[Else instructions ; ] [ELSIF condition Then
instructions ;
[Else commandes ;] ]
END IF;
Seules les clauses IF, THEN, END IF sont obligatoires.
La condition peut utiliser les variables définies ainsi que tous les
opérateurs présents dans SQL =, , =, , IS NULL, IS NOT
NULL, BETWEEN, LIKE, AND, OR, etc..
b) La boucle POUR FOR compteur IN exp_debut .. exp_fin
LOOP
...
instructions ;
...
END LOOP ;
Règles :
o Déclaration implicite de la variable compteur
o exp_debut, exp_fin : sont des constantes, expressions ou variables
o compteur : est une variable de type entier, locale à la boucle. Elle
s'incrémente de 1, après chaque traitement du contenu de la boucle,
jusqu'à ce qu'il atteigne la valeur de droite
c) La boucle TANT QUE
WHILE condition
LOOP ...
instructions ;
...
END LOOP;
La condition est une expression définie en combinant les opérateurs :
, = , !=, =; and, or, like, etc... Expression est une
constante, une variable, le résultat d'une fonction.
VI - ECHANGES AVEC L'EXTERIEUR A priori il n'existe pas d'instruction d'affichage et de saisie dans le
langage PL/SQL. Sous SQL*Plus : > on peut définir une variable ( réservation d'une zone mémoire), et
l'afficher à la fin du programme PL/SQL SQL> variable x number
SQL> start ../gautier/plsql_ex2
Procédure PL/SQL terminée avec succès.
SQL> print x
X
---------
5
Remarque : X est préfixée par : ( variable hôte.
> on peut saisir une valeur à rechercher dans SQLPLUS (ou sous le fichier
avant le DECLARE) SQL >@ ../gautier/plsql_ex3
prompt "nom du département désiré" -- ou ces 2 lignes sous SQL*PLUS
accept dept_nom
-- Exemple PLSQL_EX3.sql
DECLARE
res dept%rowtype; -- Même type qu'une ligne de la table
BEGIN
SELECT * into res from dept
where dname = '&dept_nom';
END;
/
Mais le résultat de la requête ne s'affiche pas
> on peut définir une constante dans SQL*PLUS ou dans le fichier SQL>define dept_nom = 'SALES'
SQL>@ ../gau