630 likes | 780 Views
Langage PL/SQL. T.ELHAMDANI. 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
E N D
Langage PL/SQL T.ELHAMDANI
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 constantes Dé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 ligne END;
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 declare ex: 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_champ index 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 number DECLARE a varchar2(10); ...; BEGIN -- ici a est un char END; -- ici a est un number -- ici b n'existe pas END;
Structure Alternative IF 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
Structures Itératives • 4 Types de boucles • LOOP EXIT • FOR • WHILE • FOR CURSOR
Boucle LOOP • Boucle sans fin, sortie par EXIT [<<label>>]LOOP instructions; … [EXIT [LABEL] [WHEN condition];] Instructions; … END LOOP;
Exemple de LOOP SET SERVEROUT ON DECLARE nbr 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;
Exemple de FOR BEGIN FOR nbr in 1..10 LOOP DBMS_OUTPUT.put_line(nbr); END LOOP; END;
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 emp ORDER 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; Begin If 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'erreur DECLARE 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;
EXCEPTION 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 Erreurs Prédéfinies
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.