Oracle - PL / SQL (Procedural Language / Structured Query Language)

1. Oracle - PL / SQL (Procedural Language / Structured Query Language)

2. Problématique posée par SQL SQL est un langage non procédural, répondant à la question QUOI ? Mais pas au COMMENT ? Les développeurs ont besoin d’assembler des données et de les traiter ligne par ligne. Solution apportée par Oracle à partir de la version 6 Mise en place d’un langage de traitement procédural qui facilite la tâche des développeurs et qui permet de pallier aux carences du langage SQL : PL/SQL.

3. Oracle APPLICATION (PGI) DESIGNER DEVELOPPER SQL D DICTIONNAIRE DONNEES Noyau Présentation du langage PL/SQL PL/SQL

4. Présentation du langage PL/SQL PL/SQL PROCEDURAL STATEMENT EXECUTOR Intégré au noyau et aux outils (Forms, Rports, OAS, …) SQL STATEMENT EXECUTOR Intégré au noyau RDBMS. REGLES • Déclarations possibles des variables. • Intégration de la majorité des ordres SQL.. • Traitements séquentiels. • Exécution conditionnelle de blocs d’instructions. • Traitements répétitifs (boucles). • Gestion des exceptions.

5. SQL & PL/SQL LID LMD TRANSACTION FONCTIONS Intégrés SELECT INSERT UPDATE DELETE LOCK TABLE COMMIT ROLLBACK SAVEPOINT SET TRANSACTION SUM DECODE MOD UPPER SYSDATE TO_CHAR … Non intégrés CREATE ALTER DRPO GRANT REVOKE ALTER SESSION SET ROLE ALTER SYSTEM EXPLAIN PLAN

6. STRUCRURE D’UN BLOC PL/SQL BLOC PL/SQL Partie exécutable (obligatoire) Partie exception (optionnelle) Partie déclarative (optionnelle) DECLARE BEGIN … END EXCEPTION • Chaque instruction se termine par un point virgule. • Les commentaires sont possibles /* */. • Possibilité d’imbrication des blocs.

7. PARTIE DECLARATIVE BLOC PL/SQL • LES VARIABLES & LES TYPES • Les variables locales : Elles ne sont pas visibles dans tous les blocs. • Elles peuvent être simples ou structurées. • Les variables globales : elles sont déclarées dans la partie commune à tous • les blocs. Elles peuvent être simples ou structurées. • Les variables externes (bind variables) : elles sont externes à tous les blocs. • (variables sql+, variables pro*, champs dans les forms). • Remarque • Les variables externes sont précédées de :.

8. PARTIE DECLARATIVE BLOC PL/SQL Types classiques

9. PARTIE DECLARATIVE BLOC PL/SQL Types propres à PL/SQL Remarques Les types BINARY_INTEGER et PLS_INTEGER requièrent moins d’espaces que les types NUMBER.

10. PARTIE DECLARATIVE BLOC PL/SQL • Conversion de types • Conversion implicite : effectuée par PL/SQL • Conversion explicites par les fonctions. Conversion explicites : TO_CHAR, TO_NUMBER, TO_DATE, TO_LOB, CHARTOROWID TO_SINGLE_BYTE, …

11. PARTIE DECLARATIVE BLOC PL/SQL Dans la partie DECLARE Syntaxe1 : <nom_var> <type> <initialisation> ; Déclaration de variables Syntaxe2 : <nom_var> <nom_var_ref>%TYPE <init> ; Syntaxe2 : <nom_var> <nom_table.nom_var_ref>%TYPE <init> ; Avec opérateur d’affectation := Initialisation Par le mot clé DEFAULT (à la place de :=) Par la clause INTO de l’ordre SELECT Par le biais d’un curseur

12. PARTIE DECLARATIVE BLOC PL/SQL Exemples de déclarations -- Exemple 1 DECLARE num_emp NUMBER(10); date_jour DATE :=SYSDATE; -- Initialisée à la date du jour logique BOOLEAN; trouve logique%TYPE DEFAULT FALSE; n NUMBER :=-5; carre NUMBER :=n**2; -- Exemple 2 DECLARE nom_emp VARCHAR2(35) NOT NULL :="BENOIT’"; chaine VARCHAR2(25) DEFAULT 6; -- Conversion implicite nombre NUMBER(3) :=‘5’; -- Conversion implicite pi CONSTANT NUMBER :=3.14; -- Déclaration d'une constante -- Exemple 3 DECLARE sal employe.salaire%TYPE :=0; -- Même type que la colonne salaire de la table employe. Remarque La contrainte NOT NULL d’une colonne ne s’applique sur la variable.

13. PARTIE DECLARATIVE BLOC PL/SQL Les variables référencées à une table de la base • Elles sont liées à des tables au niveau de la base. • On les déclare par l’attribut : %ROWTYPE • Exemples • DECLARE • agent employe%ROWTYPE -- employe est la table employe de la base. • Au niveau traitement, on pourra écrire : • BEGIN • SELECT * -- Sélection de tous les champs • INTO agent • FROM employe • WHERE nom=‘DUMAS’; • END; • Ou • BEGIN • SELECT nom,dt_entree -- Sélection de certains champs • INTO agent.nom, agent.dt_entree • FROM employe • WHERE nom=‘DUMAS'; • END;

14. PARTIE DECLARATIVE BLOC PL/SQL Syntaxe de déclaration du type TYPE <nom_type_record> IS RECORD ( <membre1 > <type1>, <membre2 > <type2>, … <membren > <typen> ); LES TYPES STRUCTURES - RECORD 1 2 Déclaration des variables de type record <nom_type_record> <nom_variable>, … ; Exemple DECLARE TYPE emp_type_rec IS RECORD (num employe.empno%TYPE, service e_service.nom%TYPE, salaire NUMBER(11,2), date_jour DATE :=SYSDATE); var_emp1 emp_type_rec; var_emp2 emp_type_rec; v_emp1.num:=2550; v_emp1.nom:='LAROUSSI' v_emp2:=v_emp1; 4 3 Utilisation <nom_variable>.<membre> := <expression>;

15. PARTIE DECLARATIVE BLOC PL/SQL Syntaxe de déclaration du type TYPE <nom_type_tableau> IS TABLE OF <type_simple>| <type_record>|<Colonne_Table> … INDEX BY BINARY_INTEGER ; LES TYPES STRUCTURES - TABLEAUX 1 2 • Remarques • La valeur de l'indice varie de : • -2.147.483.647 à + 2.147.483.647. • Les lignes intermédiaires existent potentiellement • mais n'occupent aucun espace. Déclaration des variables de type tableau <nom_type_tableau> <var_tableau> ; 3 Accès aux données <var_tableau>(<indice>) := <expression> ;

16. PARTIE DECLARATIVE BLOC PL/SQL LES TYPES STRUCTURES - TABLEAUX Exemple DECLARE TYPE t_tab_salaire TABLE OF NUMBER(11,3); tab1 t_tab_salaire; BEGIN tab1(-100) := 2500; tab1(-50) := 1500; tab1(15) :=2000; END;

17. PARTIE DECLARATIVE BLOC PL/SQL TABLEAUX – Primitives de manipulation des indices -

18. PARTIE EXECUTABLE Opérateurs • Remarques • Un système de priorité est respecté pour l'évaluation des expressions • ( ) , ** , * / , + - .

19. PARTIE EXECUTABLE STRUCTURES DE CONTROLE STRUCTURES CONDITIONNELLES STRUCTURES REPETITIVES IF… THEN … END IF LOOP … END LOOP WHILE … LOOP … END LOOP IF … THEN … ELSE … END IF FOR … LOOP … END LOOP IF … THEN … ELSIF … ELSE … END IF

20. PARTIE EXECUTABLE STRUCTURES CONDITIONNELLES IF <condition1> THEN -- Séquence1 instructions; ELSIF <condition2> THEN -- Séquence2 Instructions; ELSIF <condition3> THEN -- Séquence3 Instructions; … ELSIF <conditionN> THEN -- SéquenceN Instructions; ELSE -- Séquence_else Instructions; END IF; IF <condition> THEN -- Séquence1 instructions; END IF; IF <condition> THEN -- Séquence1 instructions; ELSE -- Séquence2 instructions; END IF;

21. PARTIE EXECUTABLE STRUCTURES REPETITIVES • FOR <compteur> IN [REVERSE] b_inf..b_sup • LOOP • … ; • END LOOP; • Compteur est déclarée implicitement. • Interdiction de modification de compteur dans • la boucle. • La notion de pas n'existe pas (pas=1 ou -1). [<label<] LOOP … ; EXIT [label] [ WHEN <condition>] ; … END LOOP [label] ; WHILE <condition> LOOP … ; END LOOP ;

22. PARTIE EXECUTABLE LES CURSEURS • Une zone mémoire de taille fixe contenant le résultat d'une requête. • Utilisée pour interpréter et analyser les ordres SQL. • Le nombre de curseurs ouverts simultanément est défini par le paramètre OPEN_CURSORS . • dans le PFILE de la base. CURSEURS EXPLICITES Déclaré par l'user dans la section DECLARE d'un bloc PLS IMPLICITES générés et gérés par le noyau Oracle pour chaque ordre SQL

23. LES CURSEURS EXPLICITES Étapes de création d'un CURSEUR explicite DECLARATION DU CURSEUR CURSOR … IS OUVERTURE DU CURSEUR OPEN ACCES AU CURSEUR FETCH … INTO FERMETURE DU CURSEUR CLOSE

24. LES CURSEURS EXPLICITES • Syntaxe • DECLARE • CURSOR <nom_curseur> IS • SELECT col1, col2, col3, … • FROM tab1, tab2, tab3, … • WHERE <condition>; • … ; DECLARATION DU CURSEUR CURSOR … IS • Exemple • DECLARE • CURSOR c_employe IS • SELECT empno, ename, salaire • FROM employe • WHERE salaire BETWEEN 1000 AND 2500 ;

25. LES CURSEURS EXPLICITES Syntaxe BEGIN … ; OPEN <nom_curseur>; … ; END; OUVERTURE D'UN CURSEUR OPEN … • Exemple • DECLARE • CURSOR c_employe IS • SELECT empno, ename, salaire • FROM employe • WHERE salaire BETWEEN 1000 AND 2500 ; • BEGIN • OPEN c_employe; • … ; • END;

26. LES CURSEURS EXPLICITES Syntaxe BEGIN … ; FETCH <nom_cur> INTO {<liste_var> |<var_record>}; … ; END; TRAITEMENT DES LIGNES DU CURSEUR FETCH … INTO • Exemple • DECLARE • no employe.empno%TYPE; -- Variables programmes • name employe.ename%TYPE; • CURSOR c_employe IS -- Curseur • SELECT empno, ename • FROM employe • WHERE salaire BETWEEN 1000 AND 2500 ; • BEGIN • OPEN c_employe; • FETCH c_employe INTO no,ename; -- Accès aux lignes du cruseur • DBMS_OUTPUT.PUT_LINE(no,ename) ; • END;

27. LES CURSEURS EXPLICITES FERMETURE D'UN CURSEUR CLOSE … Syntaxe BEGIN … ; CLOSE <nom_curseur>; … ; END; • Exemple • DECLARE • CURSOR c_employe IS • SELECT empno, ename, salaire • FROM employe • WHERE salaire BETWEEN 1000 AND 2500 ; • BEGIN • OPEN c_employe; • … ; • CLOSE c_employe; • END;

28. LES CURSEURS EXPLICITES ATTRIBUTS DES CURSEURS

29. LES CURSEURS EXPLICITES Parcours des lignes d'un curseur En passant par la boucle LOOP … END LOOP BEGIN LOOP FETCH … ; INTO … ; IF <curseur>%FOUND THEN … ELSE EXIT ; END IF END LOOP; En utilisant la boucle FOR enreg IN <nom_curseur> LOOP … END LOOP; Exemple FOR enreg IN c_employe LOOP … END LOOP;

30. LES CURSEURS PARAMETRES Il est possible de paramétrer un curseur pour une utilisation commune à plusieurs traitements. Syntaxe déclaration DECLARE CURSOR <nom_curseur> (p1 type_p1 [,p2 type_p2 [, …]) IS SELECT … FROM … WHERE <condition qui utilisent les paramètres> …; Utilisation du curseur paramétrer BEGIN OPEN <nom_cur>(val1,[,val2 [,…]); FETCH <nom_cur> INTO …; CLOSE <nom_cur>; END;