1 / 63

Langage PL/SQL

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

edward-goodwin
Download Presentation

Langage PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Langage PL/SQL T.ELHAMDANI

  2. 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*...

  3. 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

  4. 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, ...

  5. 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

  6. 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] ;

  7. 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

  8. 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

  9. 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;

  10. Les variables • Stockage de résultat de requêtes • Définition dans la section DECLARE • 2 types de variables: • variables scalaires • variables composées

  11. 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

  12. 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;

  13. 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, … );

  14. 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;

  15. 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;

  16. 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

  17. Structures Itératives • 4 Types de boucles • LOOP EXIT • FOR • WHILE • FOR CURSOR

  18. Boucle LOOP • Boucle sans fin, sortie par EXIT [<<label>>]LOOP instructions; … [EXIT [LABEL] [WHEN condition];] Instructions; … END LOOP;

  19. 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;

  20. La boucle FOR [<<label>>]FOR indice in [reverse] v1..v2 LOOP instructions; [EXIT [label] [WHEN condition]; ] Instruction END LOOP;

  21. Exemple de FOR BEGIN FOR nbr in 1..10 LOOP DBMS_OUTPUT.put_line(nbr); END LOOP; END;

  22. La boucle WHILE <<label>>WHILE Condition LOOP instructions; [EXIT [label] [WHEN condition]; ] Instruction END LOOP;

  23. Exemple de WHILE DECLARE nbr number := 1; BEGIN WHILE nbr<=10 LOOP DBMS_OUTPUT.put_line(nbr); nbr:=nbr+1; END LOOP; END;

  24. 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

  25. Les curseurs • Permet de faire un SELECT ramenant plusieurs lignes. • 4 étapes : • Déclaration • Ouverture • Parcours • Fermeture

  26. 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;

  27. 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;

  28. 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;

  29. Fermer le curseur • Permet de libérer les ressources allouées au curseur. • CLOSE nom; CLOSE nomcur;

  30. 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;

  31. 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

  32. 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;

  33. 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.

  34. 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;

  35. Simplification d'écriture • La déclaration est dans le FOR • FOR nom_rec IN (Select ... ) LOOP … END LOOP;

  36. 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;

  37. 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

  38. 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;

  39. 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.

  40. 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]

  41. 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;

  42. 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 ;

  43. 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;

  44. 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;

  45. 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

  46. 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é.

  47. 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]

  48. 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]

  49. 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;

  50. 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.

More Related