Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 1 Nic

Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 1 Nicolas Travers Équipe Vertigo - Laboratoire CEDRIC Conservatoire National des Arts & Métiers, Paris, France Procédures Stockées Bases de Données Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 2 Contenu du cours • PL/SQL ▫ Variables ▫ Structures de contrôle ▫ Interaction avec la base et Curseurs ▫ Sous-programmes, paquetages ▫ Exceptions ▫ Transactions (Daprès les supports de Michel Crucianu, Cédric du Mouza et Philippe Rigaux) Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 3 Bibliographie Bales, D.K. Java programming with Oracle JDBC. OReilly, 2002. Bizoi, R. PL/SQL pour Oracle 10g, Eyrolles. 2006. Date, C. Introduction aux bases de données. Vuibert, 2004 (8ème édition). Gardarin, G. Bases de données, Eyrolles. 2003. Reese, G. JDBC et Java : guide du programmeur. OReilly, 2001. Soutou, C. SQL pour Oracle. Eyrolles, 2008 (3ème édition). Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 4 PL/SQL • Procedural Language / Structured Query Language ▫ PL/SQL : langage propriétaire Oracle ▫ Language procédural : ! Variables, boucles, tests, curseurs, fonctions/procédures, exceptions • Syntaxe de PL/SQL inspirée du langage Ada (Pascal) ▫ Avantages de SQL ▫ Programmation en plus • PL/SQL nest pas très éloigné du langage normalisé Persistent Stored Modules (PSM) Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 5 PL/SQL • Qui ? ▫ DBA ▫ Programmeur d’application de BD • Existe dans dautres SGBDR ▫ MySQL : PL/SQL like ▫ Sybase et Microsoft SQL server : Transact-SQL ▫ PostgreSQL : PL/pgSQL ▫ DB2 (IBM) : SQL Procedural Language • Documentation Oracle (en anglais) http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm • Documentation MySQL http://dev.mysql.com/doc/refman/5.0/fr/stored-procedure-syntax.html Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 6 Quel est lintérêt de PL/SQL ? • SQL est déclaratif ▫ Requêtes naturelles ▫ Mais les applications complexes exigent plus, ! Pour la facilité et l’efﬁcacité de développement : ! gérer le contexte, ! lier plusieurs requêtes entre elles, ! créer des librairies de procédures cataloguées réutilisables ! Pour l’efﬁcacité de l’application : ! factoriser les traitements proches des données ! réduire les échanges client et serveur (un programme PL/SQL est exécuté sur le serveur) ⇒ Besoin détendre SQL : PL/SQL est une extension procédurale Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 7 PL/SQL - Modes • Interactif : ▫ Exécution de code ! par exemple, contrôler ou corriger des données • Stocké : ▫ Procédures, fonctions ou de triggers ▫ Appel interne • Programme : ▫ Appel depuis langages généralistes (JDBC) Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 8 Architecture Connexion + Plusieurs requêtes SQL Analyse SQL + Compilation requête BD Connexion + Appel de procédure PL/SQL BD Accès Accès Appel Requête Résultats 1 2 3 4 1 2 3 Appels : (1, 2, 3, 4), (1, 2, 3, 4) … Appels : 1, (2, 3), (2, 3), (2, 3) … Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 9 Structure dun programme • Programme PL/SQL = bloc (procédure anonyme, procédure nommée, fonction nommée) : DECLARE -- section de déclarations -- section optionnelle … BEGIN -- traitement, avec déventuelles directives SQL -- section obligatoire … EXCEPTION -- gestion des erreurs retournées par le SGBDR -- section optionnelle … END; / ← lance lexécution sous SQL*Plus Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 10 Exemple DECLARE -- Quelques variables v_nbFilms INTEGER; v_nbArtistes INTEGER; BEGIN -- Compte le nombre de films SELECT COUNT(*) INTO v_nbFilms FROM Film; -- Compte le nombre d'artistes SELECT COUNT(*) INTO v_nbArtistes FROM Artiste; -- Affichage des résultats DBMS_OUTPUT.PUT_LINE ('Nombre de films: ' || v_nbFilms); DBMS_OUTPUT.PUT_LINE ('Nombre d''artistes: ' || v_nbArtistes) EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Problème rencontré dans StatsFilms'); END; Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 11 Imbrication des blocs PL/SQL • Blocs imbriqués : • Portée dun identiﬁcateur : ▫ un descendant peut accéder aux identiﬁcateurs déclarés par un parent, pas l’inverse • Un bloc est compilé pour être ensuite exécuté DECLARE … BEGIN … EXCEPTION … END; DECLARE … BEGIN … EXCEPTION … END; DECLARE … BEGIN … EXCEPTION … END; DECLARE … BEGIN … EXCEPTION … END; Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 12 Identiﬁcateurs, commentaires • Identiﬁcateur : ▫ Variable, curseur, exception, etc. ▫ Commence par une lettre ▫ Peut contenir : lettres, chiffres, $, #, _ ▫ Interdits : &, -, /, espace ▫ Jusqu’à 30 caractères ▫ Insensible à la casse ! (nompilote = NomPILOTE) • Commentaires : -- Commentaire sur une seule ligne /* Commentaire sur plusieurs lignes */ Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 13 Variables • Toute variable PL/SQL : ▫ Obligatoirement déﬁni dans DECLARE avant utilisation • Types de variables PL/SQL : ▫ Types Scalaires (Oracle) : ! NUMBER(5,2), ! VARCHAR2, ! DATE, ! BOOLEAN, … ▫ Composites : ! %TYPE (schéma d’un attribut), ! %ROWTYPE (schéma d’une table ou résultat de requête), ! RECORD (type complexe dérivé), ! TABLE (tables dynamiques) ▫ Référence : ! REF ▫ Large Object : ! LOB (jusqu’à 4 Go ; pointeur si externe) Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 14 Variables scalaires • Syntaxe de déclaration : Identificateur [CONSTANT] type [[NOT NULL] {:= | DEFAULT} expression]; ▫ CONSTANT : c’est une constante (doit être initialisée) ▫ NOT NULL : on ne peut pas lui affecter une valeur nulle (sinon exception VALUE_ERROR) ▫ Initialisation : := (affectation) DEFAULT • Pas de déclaration multiple dans PL/SQL ! number1, number2 NUMBER;← déclaration incorrecte ! Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 15 Variables scalaires : Exemples DECLARE nom varchar2 (10) not null; adresse varchar2 (20); x INT := 1; pi constant FLOAT := 3.14159; rayon FLOAT DEFAULT 1; surface DOUBLE := pi * rayon ** 2; Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 16 Variables et SQL • Possibilité daffecter une valeur grâce à une requête SQL SELECT titre INTO mon_film FROM FILM WHERE id_film = mon_id_film ; Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 17 Variables composites • TYPE adresse IS RECORD (no INTEGER, rue VARCHAR(40), ville VARCHAR(40), codePostal VARCHAR(10) ; • titre Film.titre%TYPE; ▫ Même type qu’un attribut ou autre variable ; ▫ Préserve des modiﬁcations de tables ; • artiste Artiste%ROWTYPE ▫ Contraintes NOT NULL de la table non transmises ; ▫ un seul tuple affecter à une variable %ROWTYPE ! • Possibilité de dériver des types à partir du retour des requêtes (cf. curseurs) Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 18 Variables composites : Exercice • Créer une procédure : ▫ Pour un id de Film (mon_id_ﬁlm) donné ▫ Récupère le titre du ﬁlm correspondant ▫ Récupère le nom et le prénom du metteur en scène (id_mes) dans la table Artiste ▫ Afﬁche le titre et le nom à laide de DBMS_OUPUT.PUT_LINE • Schéma : ▫ Film (id_ﬁlm, titre, id_mes, année, coût, recette) ▫ Artiste (id, nom, prenom, date_naiss) Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 20 Nouveaux types PL/SQL • Nouveaux types prédéﬁnis : BINARY_INTEGER : entiers signés entre –231 et 231 PLS_INTEGER : entiers signés entre –231 et 231 plus performant en opérations arithmétiques • Sous-types PL/SQL : ▫ Restriction d’un type de base ! CHARACTER, INTEGER, NATURAL, POSITIVE, FLOAT, SMALLINT, SIGNTYPE, etc. ▫ Restriction : précision ou taille maximale SUBTYPE nomSousType IS typeBase [(contrainte)] [NOT NULL]; ▫ Exemple de sous-type utilisateur : SUBTYPE numInsee IS NUMBER(13) NOT NULL; Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 21 Conversions implicites ▫ Lors du calcul d’une expression ou d’une affectation ▫ Exception si conversion non autorisée De A CHAR VARCHAR2 BINARY_ INTEGER NUMBE R LONG DATE RAW ROWID CHAR OUI OUI OUI OUI OUI OUI OUI VARCHAR2 OUI OUI OUI OUI OUI OUI OUI BINARY_ INTEGER OUI OUI OUI OUI NUMBER OUI OUI OUI OUI LONG OUI OUI OUI DATE OUI OUI OUI RAW OUI OUI OUI ROWID OUI OUI Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 22 De A CHAR NUMBER DATE RAW ROWID CHAR TO_NUMBER TO_DATE HEXTORAW CHARTOROWID NUMBER TO_CHAR TO_DATE DATE TO_CHAR RAW RAWTOHEX ROWID ROWIDTOHEX Conversions explicites Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 23 Variables TABLE • Tableaux dynamiques, composé de : ▫ Clé primaire ▫ Colonne de type scalaire ! %TYPE, %ROWTYPE ou RECORD • Fonctions PL/SQL dédiées aux tableaux : EXISTS(x), PRIOR(x), NEXT(x), DELETE(x,…), COUNT, FIRST, LAST, DELETE Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 24 Variables TABLE : exemple DECLARE TYPE FilmSF IS TABLE OF Film%ROWTYPE INDEX BY BINARY_INTEGER; tabFilms FilmSF; tmpIndex BINARY_INTEGER; BEGIN … tmpIndex := tabFilms.FIRST; tabFilms(4).Titre := Star Wars - Ep 4; tabFilms(4).MES := 54; tabFilms.DELETE(5); … END; Procédures stockées : PL/SQL N. Travers Introduction aux Bases de Données 25 Affectation de Variables : ligne uploads/s1/ fip-pl-sql.pdf