Oracle PL SQL Procedural Language Structured Query Language

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

Problématique posée par 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.

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

Présentation du langage 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.

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

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

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

PARTIE DECLARATIVE BLOC PL/SQL Types classiques Type Syntaxe Remarques / Exemple NUMBER [(total [ , Décimal])] Total est max égal à 38. Salaire NUMBER(7, 2) CHAR [(longueur)] Nom CHAR(15) VARCHAR 2 [(longueur)] Le type chaîne le plus efficace sous Oracle DATE Date_Naiss DATE RAW (longueur) Géré comme le VARCHAR 2, mais pas affichable. Seq RAW(100) LONG Permet de stocker des chaînes d'une longueur max de 2 Go. Texte LONG RAW Stockage de fichiers binaires comme les images, son et de la vidéo. Longueur max est 2 Go Photo LONG RAW

PARTIE DECLARATIVE BLOC PL/SQL Types propres à PL/SQL Type Syntaxe Remarques / Exemple BINARY_INTEGER -2 E 31 -1 à +2 E 31 -1 -2. 147. 483. 647 à + 2. 147. 483. 647 PLS_INTEGER -2. 147. 483. 647 à + 2. 147. 483. 647 BOOLEAN Les valeurs : TRUE, FALSE, NULL Remarques Les types BINARY_INTEGER et PLS_INTEGER requièrent moins d’espaces que les types NUMBER.

PARTIE DECLARATIVE BLOC PL/SQL Conversion de types Conversion implicite : effectuée par PL/SQL Conversion explicites par les fonctions. BINARY_ INTEGER CHAR OUI VARCHAR 2 OUI LONG CHAR VARCHAR 2 LONG NUMBER PLS_INT EGER OUI OUI OUI OUI NUMBER OUI OUI PLS_INTEGER OUI OUI DATE OUI OUI RAW OUI OUI ROWID OUI DATE RAW ROWID OUI OUI OUI Conversion explicites : TO_CHAR, TO_NUMBER, TO_DATE, TO_LOB, CHARTOROWID TO_SINGLE_BYTE, …

PARTIE DECLARATIVE BLOC PL/SQL Dans la partie DECLARE Déclaration de variables Syntaxe 1 : <nom_var> <type> <initialisation> ; Syntaxe 2 : <nom_var> <nom_var_ref>%TYPE <init> ; Syntaxe 2 : <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

PARTIE DECLARATIVE BLOC PL/SQL Exemples de déclarations -- Exemple 1 DECLARE num_emp date_jour logique trouve n carre NUMBER(10); DATE : =SYSDATE; -- Initialisée à la date du jour BOOLEAN; logique%TYPE DEFAULT FALSE; NUMBER : =-5; NUMBER : =n**2; -- Exemple 2 DECLARE nom_emp VARCHAR 2(35) NOT NULL : ="BENOIT’"; chaine VARCHAR 2(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.

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 * INTO agent FROM employe WHERE nom=‘DUMAS’; -- Sélection de tous les champs END; Ou BEGIN SELECT nom, dt_entree -- Sélection de certains champs INTO agent. nom, agent. dt_entree FROM employe WHERE nom=‘DUMAS'; END;

PARTIE DECLARATIVE BLOC PL/SQL LES TYPES STRUCTURES - 1 RECORD 2 Syntaxe de déclaration du type TYPE <nom_type_record> IS RECORD ( <membre 1 > <type 1>, <membre 2 > <type 2>, … <membren > <typen> ); Déclaration des variables de type record <nom_type_record> <nom_variable>, … ; 3 Utilisation <nom_variable>. <membre> : = <expression>; 4 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_emp 1 emp_type_rec; var_emp 2 emp_type_rec; v_emp 1. num: =2550; v_emp 1. nom: ='LAROUSSI' v_emp 2: =v_emp 1;

PARTIE DECLARATIVE BLOC PL/SQL Syntaxe de déclaration du type LES TYPES STRUCTURES - 1 TYPE <nom_type_tableau> IS TABLE OF <type_simple>| <type_record>|<Colonne_Table> … INDEX BY BINARY_INTEGER ; TABLEAUX 2 Remarques Déclaration des variables de type tableau <nom_type_tableau> <var_tableau> ; - 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. 3 Accès aux données <var_tableau>(<indice>) : = <expression> ;

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

PARTIE DECLARATIVE BLOC PL/SQL TABLEAUX – Primitives de manipulation des indices Primitives Rôle EXISTS(n) Retourne TRUE si l'élément d'indice n existe. COUNT Retourne le nombre d'éléments du tableau. FIRST & LAST La plus petite et la plus grande valeur de l'indice du tableau. PRIOR(n) Valeur de l'indice qui précède n. NEXT(n) Valeur de l'indice qui suit n. DELETE Supprime tous les éléments du tableau. DELETE(n) Supprime le nième élément du tableau. DELETE(m, n) Supprime les éléments de m à n.

PARTIE EXECUTABLE Opérateurs Type Rôle ARITHMETIQUES + , - , ** , / RELATIONNELS <> , != , < , > , = , IS NOT NULL , IN , LIKE , AND , OR , BETWEEN , … AUTRES OPERATEURS ( ) , ; , , , . , : = , || , /*. . */ , Remarques - Un système de priorité est respecté pour l'évaluation des expressions ( ) , ** , * / , + -.

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

PARTIE EXECUTABLE STRUCTURES CONDITIONNELLES IF <condition> THEN -- Séquence 1 instructions; END IF; IF <condition> THEN -- Séquence 1 instructions; ELSE -- Séquence 2 instructions; END IF; IF <condition 1> THEN -- Séquence 1 instructions; ELSIF <condition 2> THEN -- Séquence 2 Instructions; ELSIF <condition 3> THEN -- Séquence 3 Instructions; … ELSIF <condition. N> THEN -- Séquence. N Instructions; ELSE -- Séquence_else Instructions; END IF;

PARTIE EXECUTABLE STRUCTURES REPETITIVES FOR <compteur> IN [REVERSE] b_inf. . b_sup LOOP …; END LOOP; [<label<] LOOP …; EXIT [label] [ WHEN <condition>] ; … END LOOP [label] ; WHILE <condition> 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).

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 IMPLICITES EXPLICITES générés et gérés par le noyau Oracle pour chaque ordre SQL Déclaré par l'user dans la section DECLARE d'un bloc PLS

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

LES CURSEURS EXPLICITES DECLARATION DU CURSEUR Syntaxe DECLARE CURSOR … IS CURSOR <nom_curseur> IS SELECT col 1, col 2, col 3, … FROM tab 1, tab 2, tab 3, … WHERE <condition>; …; Exemple DECLARE CURSOR c_employe IS SELECT empno, ename, salaire FROM employe WHERE salaire BETWEEN 1000 AND 2500 ;

LES CURSEURS EXPLICITES OUVERTURE D'UN CURSEUR OPEN … Syntaxe BEGIN …; OPEN <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; …; END;

LES CURSEURS EXPLICITES Syntaxe TRAITEMENT DES LIGNES DU CURSEUR BEGIN …; FETCH <nom_cur> INTO {<liste_var> |<var_record>}; …; END; 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; DBMS_OUTPUT. PUT_LINE(no, ename) ; END; -- Accès aux lignes du cruseur

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;

LES CURSEURS EXPLICITES ATTRIBUTS DES CURSEURS Attribut Type Syntaxe %NOTFOUND Booléen Retourne TRUE si la dernière commande FETCH n'a pas ramené de lignes. %FOUND Booléen Retourne TRUE si la dernière commande FETCH a ramené une ligne. {<nom_cursor>}%ATTRIBUT %ROWCOUNT %ISOPEN Rôle Numérique Retourne le nombre de lignes ramenées par la commande FETCH. Booléen Retourne TRUE si le curseur est ouvert et FALSE sinon.

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

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