Support cours PL SQL vc1

Support de cours Base de données - Le PL/SQL 1
Le PL/SQL
Généralité .............................................................................................................................................................................................................................. 1
La notion de bloc PL/SQL (anonyme ou non) ................................................................................................................................................................... 1
Les types ................................................................................................................................................................................................................................ 1
Les variables et leur affectation ..................................................................................................................................................................................... 2
Les constantes ..................................................................................................................................................................................................................... 2
Les structures de contrôle ............................................................................................................................................................................................... 2
Les procédures et les fonctions ....................................................................................................................................................................................... 3
Les packages ......................................................................................................................................................................................................................... 5
La restitution de l’information ......................................................................................................................................................................................... 6
La récupération des valeurs d’un requête récupérant un seul enregistrement ..................................................................................................... 7
Les curseurs ......................................................................................................................................................................................................................... 7
Les triggers basés .............................................................................................................................................................................................................. 9
Les exceptions ..................................................................................................................................................................................................................... 9
L’auto indendation de la clé primaire .............................................................................................................................................................................. 11
L’utilisation d’une fonction PL/SQL dans une requête SQL ...................................................................................................................................... 11
Le SQL dynamique .............................................................................................................................................................................................................. 11
Généralité
Le PL/SQL est un langage procédural, compilé, intégré au noyau Oracle. Il est intégré à la base de données (procédure ou fonction
stockées dans la base) et le code est exécuté sur le serveur. Comme le langage Ada, les procédures et les fonctions peuvent être
dans des packages constituant un ensemble logique (par exemple, le pakage codage va contenir toutes les fonctionnalités des
codages).
Ce langage est non case sensitive, l’indentation est libre et les commentaires sont placés entre /* …. */ ou -– commentaire sur 1 ligne.
Il est possible d’y intégrer les instructions de manipulation de données dans le code PL/SQL. Cependant, il est possible de
contourner le problème en utilisant le SQL dynamique.
La notion du curseur permet de lire enregistrement par enregistrement le résultat d’une requête.
Ce langage étant très riche, nous ne verrons que les principaux aspects de celui-ci.
La notion de bloc PL/SQL (anonyme ou non)
DECLARE
… (optionnel)
BEGIN
…
EXCEPTION
… (optionnel)
END;
/ pour l’activer et me compiler
Afficher les erreurs de compilation SHOW ERRORS ou SHO ERR en SQL*Plus
Voici, le plus petit bloc PL/SQL anonyme : BEGIN NULL; END;
Les types
Ils sont identiques aux types d’Oracle
plus (liste non exhaustive):
- BOOLEAN (opérateur AND OR NOT)
- le type d’un champ (prend en compte les éventuelles modifications de type du champ d’où réduction du coût de maintenance)
ex : DEBIT CREDIT%TYPE; OU DEBIT USER.CREDIT%TYPE;
- le record
ex : TYPE MEETINGTYP IS RECORD (DAY DATE, NAME VARCHAR2(20), PURPOSE VARCHAR2(80));

Support de cours Base de données - Le PL/SQL 2
- tous les types d’une table (prend en compte les éventuelles modifications de la table d’où réduction du coût de maintenance)
ex : EMP_REC EMP%ROWTYPE;
MY_DEPTNO := DEPT_REC.DEPTNO; -- POUR ACCÉDER AU CHAMP DEPTNO
Les variables et leur affectation
Dans le bloc de déclaration
BLOOD_TYPE CHAR := 'O';
VALID BOOLEAN := FALSE;
ou
BLOOD_TYPE CHAR DEFAULT 'O';
VALID BOOLEAN DEFAULT FALSE;
L’affectation
BONUS := CURRENT_SALARY * 0.10;
VALID := FALSE;
la non présence du non null
EMP_COUNT NATURAL NOT NULL := 0;
L’opérateur de concaténation de chaîne de caractères : ||
Les constantes
credit_limit CONSTANT REAL := 5000.00;
Les structures de contrôle
Le si
IF CHOICE = 1 THEN
…
END IF;
IF CHOICE <> ‘FIN’ THEN -- OU != OU ~=
…
ELSE
…
END IF;
IF VARIABLE IS NULL THEN ...
Une variante du si pour les si imbriqués
IF CHOICE = 1 THEN
…
ELSIF CHOICE = 4
…
END IF;
La structure algorithmique “selon » est disponible à partir de la version 9i.
Les boucles (itératives)
LOOP

Support de cours Base de données - Le PL/SQL 3
…
END LOOP;
Quitter une boucle
LOOP
...
TOTAL := TOTAL + SALARY;
EXIT WHEN TOTAL > 25000; -- EXIT LOOP IF CONDITION IS TRUE
END LOOP;
Le tant que
WHILE SALARY < 4000 LOOP
SELECT SAL, MGR, ENAME INTO SALARY, MGR_NUM, LAST_NAME FROM EMP WHERE EMPNO = MGR_NUM;
END LOOP;
La boucle
FOR COUNTER IN [REVERSE] MIN..MAX LOOP – MIN <= MAX
SEQUENCE_OF_STATEMENTS;
END LOOP;
avec MIN < MAX
ex1 : Affichage de 1 à 5
FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP;
ex2 : Affichage de 5 à 1
FOR i IN REVERSE 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP;
Le branchement inconditionnel ( déconseillé !!!)
IF RATING > 90 THEN
GOTO BRANCHEMENT; -- BRANCH TO LABEL
END IF;
<<BRANCHEMENT>>
…
Les procédures et les fonctions
Les procédures
Création d’une procédure
CREATE PROCEDURE NOM_PROC IS [bloc PL/SQL sans define] ; -- LE IS PEUT ETRE REMPLACE PAR AS
Ou
CREATE PROCEDURE NOM_PROC IS … [déclaration des variables] BEGIN … END NOM_PROC;
Modification d’une procédure : CREATE OR REPLACE PROCEDURE NOM_PROC IS IS [bloc PL/SQL sans define];
Destruction d’une procédure : DROP PROCEDURE NOM_PROC;
CREATE PROCEDURE NOM_PROC(EMP_ID NUMBER)
IS
BONUS REAL;
BEGIN

Support de cours Base de données - Le PL/SQL 4
...
EXCEPTION
...
END NOM_PROC;
Passage de paramètres par valeur, référence (en lecture écriture ou uniquement en écriture) : IN (valeur par défaut), OUT, IN OUT
CREATE PROCEDURE NOM_PROC(I OUT NUMBER; B IN FLOAT ) IS [bloc PL/SQL sans define] ; -- LE IS PEUT ETRE REMPLACE
PAR AS
Les valeurs par défaut des paramètres
PROCEDURE CREATE_DEPT (P_NAME VARCHAR DEFAULT 'TEMP') IS [bloc PL/SQL sans define] ;
L’appel de la procédure
NOM(VAL);
NOM;
CREATE_DEPT;
CREATE_DEPT('MARKETING');
CREATE_DEPT('MARKETING', 'NEW YORK');
CREATE_DEPT( , 'NEW YORK'); -- INTERDIT
CREATE_DEPT(NEW_LOC => 'NEW YORK');
Sous SQL*Plus
EXECUTE CREATE_DEPT('FINANCE', 'NEW YORK');
ou
BEGIN CREATE_DEPT('FINANCE', 'NEW YORK'); END;
/
La notion de procédure externe existe. Celle-ci peut être codé en C par exemple.
Les fonctions
CREATE FUNCTION NOM_FONCTION (X FLOAT, Y IN OUT FLOAT) RETURN FLOAT
IS
-- DÉCLARATION
BEGIN
…
RETURN VAL1;
EXCEPTION
...
END NOM_FONCTION;
Modification d’une fonction : CREATE OR REPLACE FUNCTION NOM_PROC IS [bloc PL/SQL sans define];
Destruction d’une fonction: DROP FUNCTION NOM_PROC;
L’appel de la fonction
VAL := NUM_FONC(…);
La récursivité
FUNCTION FAC (N POSITIVE) RETURN INTEGER IS -- RETURNS N!
BEGIN

Support de cours Base de données - Le PL/SQL 5
IF N = 1 THEN -- TERMINATING CONDITION
RETURN 1;
ELSE
RETURN N * FAC(N - 1); -- RECURSIVE CALL
END IF;
END FAC
La surcharge ou l’overloading
Il est possible d’utiliser le même nom de fonction (ou de porocédure) si la signature de celle-ci i.e. le nombre de paramètres et leurs
types est différente.
La portée des procédures et des fonctions
Elle reste classique. Il est possible d’encapsuler des fonctions ou des procédures dans des fonctions ou des procédures. Ci dessous,
un exemple de fonction (la fonction B) encapsulée et donc utilisable que dans la procédure A.
PROCEDURE A (HIREDATE DATE) IS
I INTEGER;
FUNCTION B (HIREDATE DATE) RETURN INTEGER IS
BEGIN
...
END;
BEGIN
I := NEW_HIRES('10-NOV-96');
...
END;
Le prototypage des procédures et des fonctions
La déclaration des fonctions ou des procédures permet de spécifier le code après l’avoir appelé. Dans ce cas, il convient en premier
lieu de donner la signature de la fonction, i.e de prototyper la fonction ou la procédure.
Ex:
DECLARE
PROCEDURE CALC_RATING ( ... ); -- FORWARD DECLARATION
...
PROCEDURE AWARD_BONUS ( ... ) IS
BEGIN
CALC_RATING( ... );
...
END;
PROCEDURE CALC_RATING ( ... ) IS
BEGIN
...
END;
Les packages
Les packages correspondent à une encapsulation logique d’un ensemble de procédures et de fonctions. Certaines procédures et
fonctions du package peuvent être visibles ou non, i.e. utilisables ou non de l’extérieur.
Les procédures et fonctions visibles sont prototypées dans la spécifications du package, tandis que celles privés ne seront
spécifiées que dans le corps du package.
Deux notions existent donc : les spécifications et le corps
CREATE PACKAGE NAME AS – SPECIFICATION OU DECLARATION (VISIBLE PART)
-- PUBLIC TYPE AND ITEM DECLARATIONS
-- SUBPROGRAM SPECIFICATIONS