Langage PL/SQL
-
Upload
edward-goodwin -
Category
Documents
-
view
86 -
download
8
description
Transcript of Langage PL/SQL
Qu ’est ce que PL/SQL
• Extension procédurale du langage SQL
• Intègre des commandes SQL
• Utilisable dans :– Noyau Oracle– SQL*PLUS, SQL*DBA– Oracle Developper 2000– Oracle Pro*C, Pro*Cobol, PRO*...
Familles d'instruction
• Ordres SQL• Déclaration de variables• Structures itératives (FOR,WHILE,…)• Structures alternatives (IF,ELSE,…)• Traitements des curseurs• Traitements des erreurs (exception)• Traitements des erreurs (exception)• Gestion des Triggers• Appels de fonctions et procédures
PL/SQL et SQL
• Intègre :– LID, LMD : (Select / Update, Delete, Insert)
– Transactions (Commit, rollback, savepoint)
– Fonction SQL dans et en dehors des requêtes (to_char, upper, round, to_date, ...)
• N'intègre pas– LDD, LCD– Mots clés SQL*PLUS connect, desc, ...
Exécution de PL/SQL
• SQL toujours dans le noyau
• PL/SQL dans le noyau si :– Bloc PL/SQL– Trigger– Procédure Stockée
• Dans l'applicatif si :– Developper 2000
Le bloc PL/SQL
• Un bloc a trois parties :DECLARE
Déclaration des variables et constantesDéclaration des exceptions, curseurs [section facultative]
[<<nom_bloc>>]BEGIN
Corps du programme [section obligatoire]EXCEPTION
Traitements des erreurs [section facultative]
END [nom_bloc] ;
Le bloc PL/SQL
• Imbrication possible de blocs dans le BEGIN
• DECLARE et EXCEPTION facultatifs
• ; à la fin de chaque instruction
• -- Commentaires en ligne
• /* Commentaires sur plusieurs lignes*/
• Au moins une instruction dans le BEGIN
• null; signifie pas d'instruction
Comment communiquer ?
• Le noyau n'a pas d'interface graphique
• Passer des paramètres :– Procédures, fonctions – Tables
• Récupérer des résultats– Procédures, fonctions– Tables– DBMS_OUTPUT.put_line
Utiliser Put et Put_Line
• SET SERVEROUTPUT ON ;BEGIN
DBMS_output.Put( 'TEST') -- affiche ‘test’
DBMS_output.Put_line('TEST') -- affiche test et revient à la ligne
DBMS_output.new_line; -- retour à la ligneEND;
Les variables
• Stockage de résultat de requêtes
• Définition dans la section DECLARE
• 2 types de variables:– variables scalaires– variables composées
Les variables scalaires
• Types Oracle (Number, varchar2, Date, int,float, binary_integer,natural,positive,…)
• booléen• Type référençant le dictionnaire de données• Dans le bloc declareex: Nom VARCHAR2(20);
Num Number := 20;B boolean; -- true, false, null
Référençant le dictionnaire
• Variable "simple"num nom_table.nom_colonne%TYPE; même type que la colonne spécifiée.
• Même type qu'une variablenum2 num%TYPE;
Les variables composées
• Enregistrement:
- par référence : var nom_table%ROWTYPE; var est équivalente à une ligne de la table nom_table.
- par énumération des champs : type nom_type is record (
champ1 type1,champ2 type2,… );
Les variables composées
• Tableaux :type nom_type is table
champ type_champindex by binary_integer;
• Utilisation :ex: nom_table nom_type;
element type_champ; nom_table(i) := element;
Visibilité des variables• Dans le cas de blocs imbriqués, on voit la
variable déclarée le plus prés.DECLARE a number; ...;BEGIN
-- ici a est un numberDECLARE
a varchar2(10); ...;
BEGIN-- ici a est un char
END;-- ici a est un number-- ici b n'existe pas
END;
Structure AlternativeIF condition THEN instruction ;…; instruction;[ELSIF condition THEN Instruction; …; instruction;]…[ELSE instruction; … ; instruction;]END IF;• Chaque IF se termine par un END IF;• Conditions comme dans le WHERE
=, <>, >,>=,<,<=, IS NULL, BETWEEN, LIKE, AND, OR , NOT
Boucle LOOP
• Boucle sans fin, sortie par EXIT
[<<label>>]LOOP
instructions;
…
[EXIT [LABEL] [WHEN condition];]
Instructions;
…
END LOOP;
Exemple de LOOP
SET SERVEROUT ONDECLAREnbr number := 1;
BEGIN LOOP DBMS_OUTPUT.put_line(nbr); nbr:=nbr+1; Exit when nbr>10;
end loop;end;
La boucle FOR
[<<label>>]FOR indice in [reverse] v1..v2
LOOP
instructions;
[EXIT [label] [WHEN condition]; ]
Instruction
END LOOP;
La boucle WHILE
<<label>>WHILE Condition
LOOP
instructions;
[EXIT [label] [WHEN condition]; ]
Instruction
END LOOP;
Exemple de WHILE
DECLARE
nbr number := 1;
BEGIN
WHILE nbr<=10
LOOP
DBMS_OUTPUT.put_line(nbr);
nbr:=nbr+1;
END LOOP;
END;
Le Select dans PL/SQL
• Select fait partie du langage
• Même syntaxe qu'en SQL
• Clause INTO supplémentaire et obligatoire
SELECT EMPNO INTO num
FFROM EMP WHERE ename='JAMES';
!!!ATTENTION !!! Le select doit ramener une et une seule ligne
Les curseurs
• Permet de faire un SELECT ramenant plusieurs lignes.
• 4 étapes :– Déclaration– Ouverture– Parcours– Fermeture
Déclaration du curseur
• Dans la section DECLARE• CURSOR nom_curseur IS SELECT …;• Pas de INTO Exemple: DECLARE CURSOR nomcur IS
SELECT empno,sal FROM empORDER BY empno;
Ouverture du curseur
• C'est une instruction, donc dans le BEGIN
• Allocation réelle du curseur
• Evaluation des paramètres et des variables
• OPEN nom_curseur;
OPEN nom_curseur;
Parcours du curseur (FETCH)
• Ramène les lignes une par une
• Stocke les valeurs dans des variables
FETCH nom_curseur INTO
var1,var2;• Prévoir une boucle pour ramener toutes les
lignes.• EXIT WHEN nomcur%NOTFOUND;
Fermer le curseur
• Permet de libérer les ressources allouées au curseur.
• CLOSE nom;
CLOSE nomcur;
Exemple complet
DECLARE
CURSOR EMPCUR IS
SELECT EMPNO,ENAME From EMP ORDER BY EMPNO;
num emp.empno%type;
nom emp.ename%type;
BEGIN
open EMPCUR;
LOOP
fetch empcur into num,nom;
exit when empcur%NOTfound;
DBMS_OUTPUT.put_line(num||' '||nom);
end loop;
CLOSE empcur;
end;
Les Attributs de curseurs
• %FOUND, %NOTFOUNDEst-ce qu'une ligne a été ramenée ou traitée.
• %ISOPENEst-ce que le curseur est ouvert
• %ROWCOUNTNombre de ligne traité
• SQL%xxx pour les curseurs implicites
Variables de ligne
• Possibilité de déclarer une variable de ligne (structure) basée sur un curseur.
• Similaire aux variables basées sur le dictionnaire de données.
• CURSOR c1 IS Select ename,deptno … ;ligne c1%ROWTYPE;
• X := ligne.ENAME;
• FETCH c1 INTO ligne;
Simplification d'écriture
• OPEN + FETCH + CLOSE remplacé par
• FOR nom_rec IN nom_curseur LOOP…
END LOOP;
• Ne change rien aux performances
• Juste plus simple à écrire.
Exemple complet simplifié
DECLARE
CURSOR EMPCUR IS
SELECT EMPNO,ENAME From EMP ORDER BY EMPNO;
BEGIN
FOR rec IN EMPCUR LOOP
DBMS_OUTPUT.put_line(
rec.empno ||' '||rec.ename);
end loop;
end;
Simplification d'écriture
• La déclaration est dans le FOR
• FOR nom_rec IN (Select ... ) LOOP…
END LOOP;
Exemple complet simplifié
BEGIN
FOR rec IN (SELECT EMPNO,ENAME
From EMP
ORDER BY EMPNO) LOOP
DBMS_OUTPUT.put_line(
rec.empno ||' '||rec.ename);
end loop;
end;
Paramétrage de curseurs
• Permet de réutiliser un curseur avec des paramètres différents
• CURSOR nom_curseur(P1 type, p2 type) IS Select … = P1 … ;
• OPEN nom_curseur(val1,val2);
• FOR rec IN nom_curseur(val1,val2) LOOP
Exemple curseur ParamétréDECLARE
CURSOR EMPCUR(D number) IS
SELECT EMPNO,ENAME From EMP
Where deptno=d ORDER BY EMPNO;
BEGIN
FOR recd IN (select deptno from dept) LOOP
DBMS_OUTPUT.put_line('DEPT : '|| recd.deptno);
FOR rece IN EMPCUR(recd.deptno) LOOP
DBMS_OUTPUT.put_line(
rece.empno ||' '||rece.ename);
end loop; end loop;
end;
Paramétrage de curseurs
• On peut aussi paramétrer un curseur avec des variables ou des paramètres de fonctions.
• Les variables sont évalués lors de l'OPEN.
Le CURRENT OF
• Cette clause utilisable dans le Where permet de traiter la ligne courante d'un curseur.
Where Current Of C1
• Plus pratique qu'un Where sur la clé primaire.• Au préalable réservation des lignes
concernées lors de la déclaration du curseur:
For Update [of nom_colonne]
Exemple complet simplifié
DECLARE
CURSOR C1 IS SELECT EMPNO,ENAME
From EMP2
ORDER BY EMPNO
FOR UPDATE OF SAL;
BEGIN
FOR rec IN C1 LOOP
UPDATE EMP2 Set SAL = SAL * 1.1
Where CURRENT OF C1;
end loop;
end;
Gestion des erreurs
• Les erreurs s'appellent des EXCEPTIONS
• Exceptions ORACLE
• Exceptions Utilisateurs (comme C,ADA,…)
• Traitement des erreurs dans EXCEPTION.
• WHEN erreur THEN traitement ;
• RAISE erreur ;
• WHEN OTHERS THEN traitement ;
Erreurs Utilisateurs
• Déclarer l'erreur• La "lever" (RAISE)DECLARE
SAL_INF_500 Exception;SAL Number := 200;
BeginIf SAL < 500 then
Raise SAL_INF_500; end if;
EXCEPTION WHEN SAL_INF_500 THEN
DBMS_OUTPUT.put_line('SAL inf à 500');END;
Erreurs Oracle
• Déclarer l'erreurDECLARE
MAX_OPEN_CUR Exception;
PRAGMA EXCEPTION_INIT(MAX_OPEN_CUR ,-1000);
Begin
...
EXCEPTION
WHEN MAX_OPEN_CUR THEN
DBMS_OUTPUT.put_line('Trop de curseur');
END;
Erreurs PrédéfiniesEXCEPTION Erreur Oracle SQLCode
ACCESS_INTO_NULL ORA-06530 -6530 COLLECTION_IS_NULL ORA-06531 -6531 CURSOR_ALREADY_OPEN ORA-06511 -6511 DUP_VAL_ON_INDEX ORA-00001 -1 INVALID_CURSOR ORA-01001 -1001 INVALID_NUMBER ORA-01722 -1722 LOGIN_DENIED ORA-01017 -1017 NO_DATA_FOUND ORA-01403 +100 NOT_LOGGED_ON ORA-01012 -1012 PROGRAM_ERROR ORA-06501 -6501 ROWTYPE_MISMATCH ORA-06504 -6504 STORAGE_ERROR ORA-06500 -6500 SUBSCRIPT_BEYOND_COUNT ORA-06533 -6533 SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 -6532 TIMEOUT_ON_RESOURCE ORA-00051 -51 TOO_MANY_ROWS ORA-01422 -1422 VALUE_ERROR ORA-06502 -6502 ZERO_DIVIDE ORA-01476 -1476
Fonctions d'erreurs
• SQLCODE : Retourne le code d'erreur courant
• SQLERRM : Retourne le message d'erreur courant
• SQLERRM(code) : Retourne le message d'erreur du code spécifié.
Fonctions et procédures
• Déclaration d ’une procédure
PROCEDURE nom [(parametre1[, parametre2, ...])] IS [déclarations locales]BEGIN section_exécutable[section EXCEPTION]END [nom];
nom_paramètre [IN|OUT|IN OUT] TYPE_paramètre [{:= |DEFAULT} expression]
Fonctions et procédures
• Déclaration d ’une fonction
FUNCTION nom [(parametre1[, parametre2, ...])] RETURN type_donnée_fonction IS
[déclarations locale]BEGIN Section_exécutable
[Section EXCEPTION]END [nom];
nom_paramètre [IN|OUT|IN OUT] TYPE_paramètre [{:= |DEFAULT} expression]
Exemple de fonction
DECLARE
VAL Number := 200;
FUNCTION FF_TO_EURO (FF IN NUMBER) RETURN NUMBER IS
Begin
Return FF/6.55957;
END;
Begin
DBMS_OUTPUT.put_line(Val||'FF = '|| FF_TO_EURO(VAL)||'Euro');
END;
Procédure et Fonction Stockées
• Les fonctions et procédures deviennent des Objets du noyaux.
• Gestion de la sécurité (Grant EXECUTE)
• ALTER permet de recompiler
ALTER FUNCTION xxx COMPILE;• CREATE [or Replace] Function …
• Exécutées avec les droits du créateur.
Exemple de fonction Stockée
Create Or Replace FUNCTION
FF_TO_EURO (FF IN NUMBER) RETURN NUMBER IS
Begin
Return FF/6.55957;
END;
Par la suite on peut l'appeler de n'importe quel bloc PL/SQL (ou à partir d ’une ligne de commande) .
DECLARE
VAL Number := 200;
Begin
DBMS_OUTPUT.put_line(Val||'FF = '|| FF_TO_EURO(VAL)||'Euro');
END;
Voir les erreurs des Objets
• Lors de la création de :– Fonction stockée– Procédure stockée– Package– Triggers
• L'objet est toujours crée, mais il peut être crée en erreur.
Select * From user_errors;
Appel de fonction depuis SQLNouveauté Oracle 7.3
• Possibilité d'appeler une fonction PL/SQL depuis un Select
Select ENAME,FF_TO_EURO(SAL) FROM EMP;
• La fonction PL/SQL ne doit pas modifier la base de données.
Les Triggers• Les Triggers sont des procédures PL/SQL
exécutées après ou avant une opération de type INSERT, UPDATE, DELETE.
• Un trigger est associé à une seule table.
• Si une table est supprimée, les triggers qui lui sont associés sont automatiquement supprimés.
• :NEW et :OLD manipulent les anciennes et nouvelles valeurs des champs
Les Triggers
• Permettent de programmer des règles d'intégrités complexes.
• Permettent de garantir le systématique d'une opération. Ex : Stock.
• Permettent la mise en œuvre d’une politique de sécurité complexe.
Les Triggers
• Déclaration :
CREATE [OR REPLACE] TRIGGER nom_trigger {BEFORE|AFTER} événement_déclenchant ON nom_table
[FOR EACH ROW]
[WHEN (condition)]
Block PL/SQL
Les Packages
• Contient :– Procédures et fonctions – Curseurs et Variables
• Partie Spécification
• Partie Corps (BODY)
• Si seulement dans BODY local.
Les Packages
• Partie spécification:
CREATE [OR REPLACE] package nom_package IS
[Déclaration des objets]
END nom_package;
Les Packages
• Partie corps:
CREATE [OR REPLACE] PACKAGE BODY nom_package IS
[Déclaration des objets]
[corps_procédures;]
[corps_fonctions;]
end nom_package;
Exemple de Package
Create Or Replace Package Convertion AS
FUNCTION FF_TO_EURO (FF IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (FF_TO_EURO ,WNDS);
FUNCTION EURO_TO_FF (EURO IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (EURO_TO_FF ,WNDS);
END ;
Create Or Replace Package Body Convertion AS
FUNCTION FF_TO_EURO (FF IN NUMBER) RETURN NUMBER IS
Begin Return FF/6.55957; END;
FUNCTION EURO_TO_FF (EURO IN NUMBER) RETURN NUMBER IS
Begin Return EURO*6.55957; END;
END;