Remerciez-le!

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

Programmation Orientée Objet IG2I, L4 - GI – 2015-2016 TP 1 – PL/SQL : Banque !

Programmation Orientée Objet IG2I, L4 - GI – 2015-2016 TP 1 – PL/SQL : Banque ! Diego Cattaruzza, Maxime Ogier Contexte et objectif du TP Ce TP propose de mettre en place la gestion des comptes de dépôt et des cartes bleues dans une banque. Il s’agit de gérer les limites de montant de retrait, les essais du code conﬁdentiel pour la carte, ainsi que le potentiel blocage du compte. Ces opérations de gestion devront être implémentées directement au niveau du serveur de base de données. Ce TP permet d’illustrer les notions suivantes : • la programmation côté serveur ; • le langage PL/SQL (Procedural Language/Structured Query Language) ; • la notion de déclencheur. Pour réaliser ce TP, nous utiliserons le logiciel SQL Developer. Si ce logiciel n’est pas encore installé sur votre machine, vous pouvez le télécharger à l’adresse http://www.oracle.com/technetwork/developer-tools/sql-developer/ downloads/index.html. Bonnes pratiques à adopter N’oubliez pas de tester votre code au fur et à mesure de votre avancement. Par ailleurs, il est important que votre code soit commenté. En langage PL/SQL, un commentaire sur une ligne commence par - -, et un commentaire sur plusieurs lignes est encadré par /* et */. 1 Introduction Vous êtes chargé de mettre en place la gestion des comptes de dépôt et des cartes bleues de la banque "IG2I Direct". Pour cela, le responsable informatique, Monsieur Fric, vous demande dans un premier temps de vous charger de la mise en place de la partie donnée de l’application de gestion des comptes et des cartes bleues. Monsieur Fric vous explique qu’un client est caractérisé par son numéro de client (qui doit être unique), son nom (qui est supposé unique) et le découvert autorisé (qui est positif et vaut 0 par défaut). Un compte de dépôt est déﬁni par son numéro (qui doit être unique), son titulaire (qui doit être un client de la banque), son solde (positif) et un indicateur précisant s’il est ouvert ou bloqué (ouvert par défaut). En outre, un compte de dépôt est aussi caractérisé par le nombre d’essais infructueux de retrait par carte bancaire ainsi que le code conﬁdentiel de la carte (un code à 4 chiﬀres). Une opération par carte bleue est caractérisée par son numéro d’opération (qui doit être unique), une date (la date du jour), le numéro du compte, le code conﬁdentiel proposé (à 4 chiﬀres) et le montant (positif). Ainsi, une opération correspond bien à une tentative de retrait par carte bleue, mais rien ne garantit que celle-ci aboutisse. La banque garde donc aussi un historique des retraits eﬀectués sur un compte. Cet historique est caractérisé par le numéro du compte, la date et le montant retiré (positif). Par ailleurs, la banque souhaite aussi garder tous les incidents qui ont eu lieu lors d’une opération. Un incident est caractérisé par le numéro de l’opération et le message d’incident. Par ailleurs, comme la banque gère des montants qui correspondent à des opérations monétaires, les valeurs sont des nombres réels avec 2 chiﬀres après la virgule. 2 Mise en place de la base de donnée 2.1 Modélisation Question 1. A partir des informations fournies, proposez un modèle conceptuel de données, puis un modèle logique de données pour représenter le système d’informations de gestion des comptes et des cartes bleues. Dans le modèle logique de données, pensez bien à préciser les types de données, les clés primaires et secondaires, les données non nulles. Vous pouvez réaliser ces modèles sur papier, ou bien utiliser des logiciels adaptés. 2.2 Connexion à une base Oracle Question 2. Démarrez SQL Developer. Créez une nouvelle connexion de base de données avec les paramètres suivants : • nom de connexion : L4_GI_connect ; • nom utilisateur : L4_GIXX (avec XX entre 01 et 29) ; • mot de passe : iLoveSQL ; • nom d’hôte : oracle-edu.ec-lille.Fr ; • port : 1521 ; • SID : ecli4. Après la création de la connexion, une feuille de calcul s’est ouverte. C’est sur cette feuille que vous allez pouvoir écrire et exécuter les requêtes et les scripts. Le langage utilisé est PL/SQL, dont nous verrons les particularités par la suite. Pour le moment, il faut juste savoir qu’il s’agit d’un extension de SQL. Ainsi, la création des séquences (pour les identiﬁants uniques) et des tables peut se faire en utilisant le langage SQL que vous connaissez bien. 2.3 Création des séquences Une séquence est un objet virtuel qui ne contient aucune donnée mais qui s’utilise pour générer automatiquement des valeurs (de type NUMBER). Elles sont utiles pour composer des clés primaires de tables quand on ne dispose pas de colonnes adéquates à cet eﬀet. Il est possible de créer plusieurs séquences dans un schéma. Les séquences sont gérées indépendamment des tables. La syntaxe de création d’une séquence avec ses options principale est la suivante : CREATE SEQUENCE [schéma.]nomSéquence [INCREMENT BY entier ] [START WITH entier ] [MAXVALUE entier ] [MINVALUE entier ] [CYCLE ] ; Si aucune option n’est précisée, la séquence créée commencera à 1 et aug- mentera sans ﬁn (la limite réelle d’une séquence est de 1029 −1). En spéciﬁant seulement INCREMENT BY –1 la séquence créée commencera à -1 et sa valeur diminuera sans limites (la borne inférieure réelle d’une séquence est de −1027−1). La signiﬁcation des options est donnée par la suite. • INCREMENT BY donne l’intervalle entre deux valeurs de la séquence (en- tier positif ou négatif mais pas nul). La valeur absolue de cet intervalle doit être plus petite que MAXVALUE - MINVALUE. L’intervalle par défaut est 1. • START WITH précise la première valeur de la séquence à générer. Pour les séquences ascendantes, la valeur par défaut est égale à la valeur minimale de la séquence. Pour les séquences descendantes la valeur par défaut est égale à la valeur maximale de la séquence. • MAXVALUE donne la valeur maximale de la séquence (ne pas dépasser 1029 −1). Cette limite doit être supérieure ou égale à l’entier déﬁni dans START WITH et supérieure à MINVALUE. • MINVALUE précise la valeur minimale de la séquence (ne pas dépasser la valeur −1027 −1). Cette limite doit être inférieure ou égale à l’entier déﬁni dans START WITH et inférieure à MAXVALUE. • CYCLE indique que la séquence doit continuer de générer des valeurs même après avoir atteint sa limite. Au-delà de la valeur maximale, la séquence générera la valeur minimale et incrémentera comme cela est déﬁni dans la clause concernée. Après la valeur minimale, la séquence produira la valeur maximale et décrémentera comme cela est déﬁni dans la clause concernée. Une fois créée, une séquence seq ne peut se manipuler que via deux directives : • seq.CURRVAL qui retourne la valeur courante de la séquence ; • seq.NEXTVAL qui incrémente la séquence et retourne la nouvelle valeur de celle-ci. Le premier appel à NEXTVAL retourne la valeur initiale de la séquence (déﬁnie dans START WITH). Les appels suivants augmentent la séquence de la valeur déﬁnie dans INCREMENT WITH. Chaque appel à CURRVAL retourne la valeur courante de la séquence. Il faut utiliser au moins une fois NEXTVAL avant d’appeler CURRVAL dans une même session. L’instruction DROP SEQUENCE supprime une séquence. Remarque : depuis la version 12c d’Oracle, l’utilisation des séquences pour la génération des clés primaires peut être remplacée par la déﬁnition d’une colonne auto-incrémentée (GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY [(options_sequence)]). Question 3. Créez les séquences qui serviront à générer les clés primaires pour les diﬀérentes tables. La création des séquence peut être écrite directement dans la feuille de calcul, ou bien il est possible avec SQL Developer d’utiliser l’assistant Nouvelle Séquence en faisant un clic-droit sur le nœud Séquences lié à la connexion. Vous pouvez ensuite rajouter au début du script la suppression des séquences, ce qui permettra de ré-exécuter plusieurs fois le script. 2.4 Création des tables Une table est créée en SQL par l’instruction CREATE TABLE, modiﬁée au niveau de sa structure par l’instruction ALTER TABLE et supprimée par la commande DROP TABLE. La syntaxe SQL pour la création d’une table est la suivante : CREATE TABLE [schéma.]nomTable ( colonne1 type1 [DEFAULT valeur1] [NOT NULL] [, colonne2 type2 [DEFAULT valeur2] [NOT NULL] ] [CONSTRAINT nomContrainte1 typeContrainte1] ...) ; 2 nomTable peut comporter des lettres majuscules ou minuscules (accentuées ou pas), des chiﬀres et les symboles, par exemple : _, $ et #. Oracle est insensible à la casse et convertira au niveau du dictionnaire de données les noms de tables et de colonnes en majuscules. colonnei typei déﬁnit le nom d’une colonne (avec les mêmes caractéristiques que pour les noms des tables) et son type (NUMBER, VARCHAR2, DATE, etc.). La directive DEFAULT ﬁxe une valeur par défaut. La directive NOT NULL interdit que la valeur de la colonne soit nulle. nomContraintei typeContraintei déﬁnit le nom de la contrainte et son type (clé primaire, clé étrangère, etc.). Les contraintes de colonnes ont pour but de programmer des règles de ges- tion au niveau des colonnes des tables. Elles peuvent alléger un développement côté client (si on déclare uploads/Voyage/ poo4-tp1.pdf