Résumé - Programmation de bases de données (PL-SQL) PDF

Preview

Summary

Ce résumé contient:
- Introduction générale
- Récupération de données - Curseurs
- Gestion des exceptions
- Séquence et Trigger
- Les types complexes (tableaux)
- Package
- Manipulations de grande quantité de données...

Description

Programmation de base de données (PL-SQL)

PROGRAMMATION DE BASES DE DONNÉES 1

Introduction générale.........................................................................................................................4 1.1 Variables d’environnement (SQL Developer).......................................................................................4 1.1.1 DBMS_OUTPUT.PUT_LINE (Afficher du texte comme WRITELN en PASCAL)...............................4 1.2 Définition SQL......................................................................................................................................4 1.3 Définition Langages procéduraux (C, Pascal, Modula, COBOL, PL/ SQL, …)........................................5 1.4 Définition SQL Procédural....................................................................................................................5 1.5 Définition SQL / PSM (PL/SQL).............................................................................................................5 1.6 Définition Langage PL/SQL (+lien documentation Oracle)..................................................................5 1.7 Domaines d’application........................................................................................................................5 1.8 Généralités PL/SQL...............................................................................................................................5 1.8.1 Mettre en commentaire...............................................................................................................5 1.8.2 Mettre en majuscule ≠ Mettre en minuscule...............................................................................5 1.9 Structure d’un bloc PL/SQL..................................................................................................................5 1.9.1 Déclaration d’objets.....................................................................................................................6 1.9.2 Catégorie de type de donnée Oracle............................................................................................6 1.9.2.1 Types scalaires............................................................................................................................................ 6 1.9.2.2 Types temporels.......................................................................................................................................... 6 1.9.2.3 Types composés.......................................................................................................................................... 7 1.9.2.3.1 Les collections...................................................................................................................................................7 1.9.2.3.2 Les enregistrements (records)...........................................................................................................................7 1.9.2.4

Types LOB................................................................................................................................................... 7

1.9.3 Conversion de types......................................................................................................................7 1.10 Opérateurs PL/SQL (= + - * / LIKE BETWEEN AND…).......................................................................7 1.11 Structure conditionnelle IF..................................................................................................................8 1.12 Instruction CASE...................................................................................................................................8 1.13 Structure de boucle..............................................................................................................................9 1.13.1 Boucle inconditionnelle – Boucle infinie.......................................................................................9 1.13.2 Instruction de sortie EXIT (comme REPEAT dans PASCAL)............................................................9 1.13.3 Boucle FOR...................................................................................................................................9 1.13.4 Boucle WHILE (tant que)..............................................................................................................9 1.13.5 Exemple de boucles – WHILE / FOR / EXIT WHEN........................................................................9 1.14 NULL - inaction.....................................................................................................................................9 1.15 Les commandes exécutables.............................................................................................................10 1.15.1 SELECT … INTO …........................................................................................................................10 1.16 Exécution d’un bloc PL/SQL anonyme...............................................................................................10 1.17 Nommage de bloc PL/SQL (procédure).............................................................................................10 1.18 Procédures.........................................................................................................................................10 1.18.1 Procédures paramétrées............................................................................................................10 1.18.2 Appels de procédures.................................................................................................................10 1.18.3 Exécution d’un bloc PL/SQL nommé...........................................................................................11 1.19 Fonction.............................................................................................................................................11 1.19.1 Retour de la valeur.....................................................................................................................11 1.19.2 Appels de fonctions....................................................................................................................11 1 / 36

Programmation de base de données (PL-SQL)

1.19.3 valeur par defaut en paramètre.................................................................................................11 2 Récupération de données - Curseurs.................................................................................................12 2.1 Récupération d’une seule ligne – SELECT … INTO …..........................................................................12 2.1.1 Unicité du retour........................................................................................................................12 2.1.1.1

Traitement de l’erreur................................................................................................................................ 12

2.2 Curseurs - Récupération de plusieurs lignes......................................................................................12 2.2.1 Concept de curseur.....................................................................................................................12 2.2.2 Navigation dans un curseur.......................................................................................................12 2.3 Curseurs explicites – OPEN / FETCH / CLOSE.....................................................................................12 2.3.1 Utilisation d’un curseur..............................................................................................................12 2.3.2 Déclaration / Ouverture / Extraction des données / Fermeture d’un curseur...........................12 2.3.3 Argument (Paramètres) d’un curseur.........................................................................................13 2.3.4 Statut d’un curseur.....................................................................................................................14 2.3.5 Boucle FOR pour curseur............................................................................................................14 2.4 Curseurs implicites – boucle for sans declaration.............................................................................14 2.5 Curseur for update.............................................................................................................................15 2.5.1 Modifier un enregistrement courant – WHERE CURRENT OF (update / delete)........................15 2.6 Curseur pointeur................................................................................................................................15 2.6.1 Syntaxe + Exemple......................................................................................................................15 2.6.2 Curseur faiblement typé ≠ curseur fortement typé....................................................................16 2.6.3 Erreur de type.............................................................................................................................16 3 Gestion des exceptions.....................................................................................................................16 3.1 Définition : Exception.........................................................................................................................16 3.2 Programmation et violation de contraintes.......................................................................................16 3.3 Traitement d’erreur dans un programme..........................................................................................16 3.4 Gestion des exceptions sur PL/SQL....................................................................................................16 3.5 Structure d’un bloc PL/SQL – gestion d’exception.............................................................................16 3.6 Les différentes exceptions prédéfinies..............................................................................................17 3.7 Traiter des exceptions........................................................................................................................17 3.8 Raise_Application_Error....................................................................................................................17 3.9 Exemple – ordre des exceptions........................................................................................................18 3.9.1 Exemple Raise_Application_Error – Pile (true, false).................................................................18 3.10 Plusieurs exceptions dans un bloc.....................................................................................................18 3.10.1 Imbrication de blocs PL/SQL.......................................................................................................19 3.10.1.1

Portée des variables.............................................................................................................................. 19

3.10.1.2

Localisation d’une erreur – Traiter de l’exception................................................................................... 19

3.11 Exceptions définies par l’utilisateur...................................................................................................20 3.12 Erreurs Oracle....................................................................................................................................20 3.13 Redirection d’une erreur sur une exception......................................................................................20 3.13.1 PRAGMA EXCEPTION_INIT.........................................................................................................21 3.14 Exceptions ≠ Erreurs...........................................................................................................................21 3.15 Traitement des erreurs sans exceptions nommées...........................................................................21 3.15.1 OTHERS – Exemple.....................................................................................................................21 4 Séquence et Trigger..........................................................................................................................21 2 / 36

Programmation de base de données (PL-SQL)

4.1 Séquence............................................................................................................................................21 4.1.1 Création de séquence.................................................................................................................21 4.1.2 Modification d’une séquence.....................................................................................................22 4.1.3 Afficher le numéro actuel de la séquence / le prochain numéro de la séquence.......................22 4.2 Triggers...............................................................................................................................................22 4.2.1 Contexte.....................................................................................................................................22 4.2.2 Les limites d’un SGBD statique...................................................................................................22 4.2.2.1

Exemples de lacunes des contraintes déclaratives...................................................................................... 22

4.2.2.2

Exemples de lacunes de sécurité................................................................................................................ 22

4.2.3 4.2.4 4.2.5 4.2.6 4.2.7 4.2.8 4.2.9 4.2.10

Les solutions pour résoudre les limites d’un SGBD statique.......................................................23 Définition : trigger......................................................................................................................23 Utilisations classiques de triggers..............................................................................................23 Normalisation.............................................................................................................................23 Les types de triggers (Oracle).....................................................................................................23 Simple trigger DML.....................................................................................................................23 Parties constitutives...................................................................................................................23 Syntaxe de trigger......................................................................................................................23

4.2.10.1 Exemple de déclenchement................................................................................................................... 24 4.2.10.2 Ordre de déclenchement (FOLLOW)....................................................................................................... 24 4.2.10.3 Pseudo colonnes (row level trigger) (Old.attribut / New.attribut)...........................................................24 4.2.10.3.1 Exemple : la journalisation............................................................................................................................25 4.2.10.3.2 Exemple : Alimentation de la clé primaire....................................................................................................25 4.2.10.3.3 Exemple : intégrité étendue..........................................................................................................................25 4.2.10.3.4 Exemple – Journalisation d’une table............................................................................................................25 4.2.10.4 Condition de déclenchement (WHEN).................................................................................................... 26 4.2.10.5 Contrôle de l’événement déclencheur (Inserting, Updating, Deleting)....................................................26

4.2.11 4.2.12 4.2.13

Erreur de compilation.................................................................................................................26 Trigger DML et transaction........................................................................................................26 Tables mutantes et contraignantes............................................................................................26

4.2.13.1

Tables mutantes et contraignantes........................................................................................................ 26

4.2.14 Gestion d’intégrité avec trigger (gérer uniquement les nouvelles données).............................27 4.2.15 Désactivation / Activation d’un trigger......................................................................................27 4.2.16 Supprimer un trigger..................................................................................................................27 4.2.17 Déclenchement des triggers en cascade....................................................................................27 4.2.18 Règle de développement de trigger...........................................................................................27 4.3 Trigger compound..............................................................................................................................28 4.3.1 Syntaxe et exemple.....................................................................................................................28 4.4 Triggers INSTEAD OF..........................................................................................................................28 4.4.1 Syntaxe et Exemple.....................................................................................................................28 4.5 Triggers System User..........................................................................................................................29 4.5.1 Syntaxe et Exemple.....................................................................................................................29 5 Les types complexes (tableaux)........................................................................................................29 5.1 Enregistrements.................................................................................................................................29 5.1.1 RECORD......................................................................................................................................29 5.1.1.1

RECORD – Manipulations........................................................................................................................... 30 3 / 36

Programmation de base de données (PL-SQL) 5.1.1.2 RECORD - Curseur...................................................................................................................................... 30

5.2 Types basés sur une relation ou un attribut......................................................................................30 5.2.1 %TYPE.........................................................................................................................................30 5.2.2 %ROWTYPE.................................................................................................................................30 5.3 Collections..........................................................................................................................................31 5.3.1 Création et utilisation de type....................................................................................................31 5.3.2 VARRAY.......................................................................................................................................32 5.3.2.1

5.3.3 5.3.3.1 5.3....