Mostafa Hanoune 5 Travailler avec des Types de données Composés Mostafa Hanoune Objectifs A A la la fin fin de de ce ce chapitre, chapitre, vous vous saurez saurez :: •• Créer Créer des des Records Records PL/SQL PL/SQL utilisateur utilisateur •• Créer Créer des des Records Records avec avec l’attribut l’attribut %ROWTYPE %ROWTYPE •• Créer Créer des des Tables Tables PL/SQL PL/SQL •• Créer Créer des des Tables Tables de de Records Records PL/SQL PL/SQL •• Savoir Savoir faire faire la la distinction distinction entre entre Records, Records, Tables, Tables, et et Tables Tables de de Records Records 5-2 Objectifs Au cours de ce chapitre, vous allez découvrir les Types de données composés du PL/SQL et leurs utilisations. Les Bases PL/SQL 5-2 Mostafa Hanoune Types de données Composés •• Types Types :: –– PL/SQL PL/SQL RECORDS RECORDS –– PL/SQL PL/SQL TABLES TABLES •• Contiennent Contiennent des des composantes composantes internes internes •• Sont Sont réutilisables réutilisables 5-3 RECORDS et TABLES PL/SQL Tout comme les variables scalaires, les variables composées sont des Types de données. Les Types de données composés (également connus sous le nom de collections) peuvent être des RECORD, TABLE, NESTED TABLE, ou VARRAY. Vous utilisez le type RECORD pour manipuler des données hétérogènes mais formant une Unité Logique. Vous utilisez le type TABLE pour désigner et manipuler une collection de données formant un seul et même Objet. Les types NESTED TABLE et les VARRAY ne sont pas abordés dans ce cours. Un record est un groupe de données stockés en champs, chaque champ étant caractérisé par son nom et son type. Une table contient une colonne et une clé primaire donnant accès aux lignes correspondantes. Une fois définis, les tables et les records peuvent être réutilisés. Pour en savoir plus, consultez PL/SQL User’s Guide and Reference, Release 8, “Collections and Records.” Les Bases PL/SQL 5-3 PL/SQL Records Mostafa Hanoune •• Ils Ils sont sont composés composés d’un d’un ou ou plusieurs plusieurs Champs Champs de de types types :: scalaire, scalaire, RECORD, RECORD, ou ou PL/SQL PL/SQL TABLE TABLE •• Ils Ils ont ont une une structure structure semblable semblable aux aux Records Records rencontrés rencontrés en en L3G L3G •• Ils Ils sont sont différents différents des des lignes lignes d’une d’une table table de de la la base base de de données données •• Ils Ils traitent traitent un un ensemble ensemble de de Champs Champs comme comme une une Unité Unité Logique Logique •• Ils Ils sont sont pratiques pratiques pour pour extraire extraire et et manipuler manipuler une une ligne ligne d’une d’une table table de de la la base base 5-4 PL/SQL Records Un Record désigne un groupe de données stockés en champs, chaque champ étant caractérisé par son nom et son type. Par exemple, supposons que vous disposez de plusieurs renseignements au sujet d’un employé : son nom, son salaire, sa date d’embauche... Ces données sont de nature différente mais sont reliées entre elles. A l’aide d’un record , vous pouvez traiter ces données comme une Unité Logique. Quand vous déclarez un type record avec ses champs, ils peuvent être manipuler comme une unité. • Chaque record ainsi défini peut contenir autant de champs que nécessaires. • On peut affecter des valeurs initiales à un record et le définir comme NOT NULL. • Les champs sans valeur initiale, sont initialisés à NULL • Vous pouvez utiliser le mot-clé DEFAULT pour initialiser les champs. • Vous pouvez définir des types RECORD dans la partie déclarative des blocs PL/SQL, des sousprogrammes ou des packages. • Vous pouvez déclarer et utiliser des records imbriqués. Un record peut être le composant d’un autre record. Les Bases PL/SQL 5-4 Mostafa Hanoune Créer un Record PL/SQL Syntaxe Syntaxe TYPE type_name IS RECORD (field_declaration[, field_declaration]…); identifier type_name; O ù field_declaration Où field_declaration est est field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [[NOT NULL] {:= | DEFAULT} expr] 5-5 Définir et Déclarer un Record PL/SQL Pour créer un record, vous devez d’abord définir un type RECORD et ensuite déclarer des RECORDS utilisant ce type. Dans la syntaxe, type_name est le nom du type RECORD (Cet identifiant est utilisé pour déclarer records). field_name est le nom d’un champ composant la structure field_type est le type de données du champ (Ce peut être n’importe quel type de données PL/SQL sauf des REF CURSOR. Vous pouvez utiliser les attributs %TYPE et %ROWTYPE). expr C’est le field_type ou une valeur initiale des La contrainte NOT NULL évite de se retrouver avec des champs NULL et force leur initialisation. Les Bases PL/SQL 5-5 Mostafa Hanoune Créer un Record PL/SQL Utilisez ésigner le Utilisez des des variables variables pour pour d désigner le nom, ’un nouvel nom, le le travail travail et et le le salaire salaire d d’un nouvel employ é. employé. Exemple Exemple ... TYPE emp_record_type IS RECORD (ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2)); emp_record emp_record_type; ... 5-6 Créer un Record PL/SQL Les déclarations de champs sont similaires aux déclarations de variables. Chaque champ possède un nom unique et un type de donnée spécifique. Il n’existe pas de type prédéfini pour les records PL/SQL , comme pour les variables scalaires. Vous devez donc créer tout d’abord un type Record et ensuite déclarer un identifiant utilisant ce type Record. L’exemple suivant montre que vous pouvez utiliser l’attribut %TYPE pour spécifier le type de données d’un champ : DECLARE TYPE emp_record_type IS RECORD (empno NUMBER(4) NOT NULL := 100, ename emp.ename%TYPE, job emp.job%TYPE); emp_record emp_record_type; ... Note : Le mot-clé NOT NULL évite de se retrouver avec des champs NULL Les Bases PL/SQL 5-6 Mostafa Hanoune Structure d’un Record PL/SQL champ1 (type) champ2 (type) champ3 (type) champ1 (type) champ2 (type) champ3 (type) empno number(4) ename varchar2(10) Exemple Exemple job varchar2(9) 5-7 Utiliser et Initialiser un Record On accède aux champs d’un Record par leurs noms. Pour utiliser et initialiser un champ, on utilise la syntaxe suivante : record_name.field_name Par exemple, vous pouvez faire référence au champ job dans le record emp_record ainsi : emp_record.job ... Vous pouvez assigner une valeur au champ ainsi : emp_record.job := 'CLERK'; Dans un bloc ou un sous-programme, les records définis par l’utilisateur sont initialisés quand vous entrez dans ce bloc ou sous-programme et cessent d’exister quand vous en sortez. Assigner des Valeurs à des Records Vous pouvez assigner une liste de valeurs communes à un record en utilisant l’instruction SELECT ou FETCH . Assurez vous que les noms de colonnes apparaissent dans l’ordre des champs du record. Vous pouvez également assigner un record à un autre record si ils sont de même type. Un record défini par l’utilisateur et un record %ROWTYPE ne peuvent être de même type. Les Bases PL/SQL 5-7 Mostafa Hanoune L’attribut %ROWTYPE •• IlIl permet permet de de définir définir une une variable variable àà partir partir d’un d’un ensemble ensemble de de colonnes colonnes d’une d’une table table ou ou d’une d’une vue vue de de la la base base de de données. données. •• Préfixer Préfixer %ROWTYPE %ROWTYPE par par le le nom nom de de la la table table ou ou de de la la vue. vue. •• Les Les champs champs du du Record Record prennent prennent les les Noms Noms et et les les Types Types des des colonnes colonnes de de la la table table ou ou de de la la vue. vue. 5-8 Déclarer des Records avec l’attribut %ROWTYPE Il faut utiliser l’attribut %ROWTYPE pour définir un record à partir d’un ensemble de colonnes d’une table ou d’une vue de la base de données. Les champs du record prennent le nom des colonnes de la table ou de la vue. Le record peut également contenir une ligne complète de données chargée à l’aide d’un curseur ou d’une variable curseur. Dans l’exemple suivant, un record est déclaré %ROWTYPE pour spécifier son type. DECLARE emp_record emp%ROWTYPE; ... Le record emp_record sera constitué des champs suivants, chaque champ représentant une colonne de la table EMP. Note : Ce n’est pas du code, mais simplement la structure d’une variable composée. (empno ename job mgr hiredate sal comm deptno NUMBER(4), VARCHAR2(10), VARCHAR2(9), NUMBER(4), DATE, NUMBER(7,2), NUMBER(7,2), NUMBER(2)) Les Bases PL/SQL 5-8 Mostafa Hanoune Les avantages du %ROWTYPE •• Le Le nombre nombre et et le le type type des des colonnes colonnes de de la la base base de de données données n’ont n’ont pas pas besoin besoin d’être d’être précisés. précisés. •• Le Le nombre nombre et et le le type type des des colonnes colonnes de de la la base base de de données données peuvent peuvent être être modifiés modifiés en en cours cours d’exécution. d’exécution. •• L’attribut L’attribut est est intéressant intéressant pour pour récupérer récupérer une une ligne ligne avec avec l’instruction l’instruction SELECT. SELECT. 5-9 Déclarer un Record à l’aide de l’attribut %ROWTYPE (suite) Syntaxe: DECLARE identifier où: identifier reference%ROWTYPE; est le nom choisi pour la structure reference est le nom de la table, du curseur ou de la variable curseur sur lequel la structure est construite (assurez vous que cette référence existe quand vous déclarez la structure) Pour utiliser un champ précis : record_name.field_name Par exemple, pour utiliser le champ comm dans le record emp_record : emp_record.comm Pour affecter une valeur au champ d’un record : emp_record.comm := 750; Les Bases PL/SQL 5-9 Mostafa Hanoune L’Attribut %ROWTYPE Exemples Exemples D éclarez une Déclarez une variable variable qui qui permette permette de de stocker ’information relative stocker ll’information relative au au d épartement dans département dans la la table table DEPT. DEPT. dept_record dept_record dept%ROWTYPE; dept%ROWTYPE; D éclarez une Déclarez une variable variable qui qui permette permette de de stocker ’information relative stocker ll’information relative àà un un employ é dans employé dans la la table table EMP. EMP. emp_record emp_record emp%ROWTYPE; emp%ROWTYPE; 5-10 Exemples de la diapositive La première déclaration définit un record dont les champs ont les mêmes noms et même types qu’une ligne de la table DEPT. Ces champs se nomment DEPTNO, DNAME, et LOCATION. La deuxième déclaration définit un record dont les champs ont les mêmes noms et même types qu’une ligne de la table EMP. Ces champs se nomment EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, et DEPTNO. Dans l’exemple suivant, un employé prend sa retraite. Les informations relatives à cet employé sont transférées dans une table d’historisation. L’utilisateur choisit un numéro pour désigner l’employé. DECLARE emp_rec emp%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM emp . WHERE empno = &employee_number; INSERT INTO retired_emps(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno) VALUES (emp_rec.empno, emp_rec.ename, emp_rec.job, emp_rec.mgr, emp_rec.hiredate, SYSDATE, emp_rec.sal, emp_rec.comm, emp_rec.deptno); COMMIT; END; Les Bases PL/SQL 5-10 Mostafa Hanoune PL/SQL Tables •• Elles Elles se se composent composent de de 22 objets objets :: –– une une clé clé primaire primaire de de type type BINARY_INTEGER BINARY_INTEGER –– Une Une colonne colonne de de type type scalaire scalaire ou ou record record •• Elles Elles peuvent peuvent s’agrandir s’agrandir dynamiquement dynamiquement car car elles elles sont sont sans sans dimension dimension 5-11 Tables PL/SQL Les objets de type TABLE sont appelés PL/SQL table. Ils sont construits comme des tables de la base de données (mais ils ne sont pas identiques). Une clé primaire permet d’avoir accès aux lignes des tables PL/SQL. Une table PL/SQL : • s’utilise comme un tableau • est composée de 2 objets : • – Une clé primaire de Type BINARY_INTEGER – Une colonne de Type SCALAIRE ou RECORD, qui peut stocker les éléments de la TABLE PL/SQL Peut s’agrandir dynamiquement car elle est sans dimension. Les Bases PL/SQL 5-11 Mostafa Hanoune Créer une Table PL/SQL Syntaxe Syntaxe TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE} [NOT NULL] [INDEX BY BINARY_INTEGER]; identifier type_name; D éclarer une Déclarer une variable variable PL/SQL PL/SQL pour pour stocker stocker un un nom. nom. Exemple Exemple ... TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; ename_table ename_table_type; ... 5-12 Déclarer une Table PL/SQL Il ya deux étapes dans la création d’une table PL/SQL. 1. Déclarer un Type TABLE. 2. Déclarer une Variable utilisant ce Type TABLE. Dans la syntaxe: type_name column_type identifier est le nom du type TABLE (TABLE est un mot-clé utilisé pour déclarer les PL/SQL Tables). est une donnée de type scalaire (non composite) VARCHAR2, DATE, ou NUMBER (Vous pouvez utiliser l’attribut %TYPE pour définir le type de la colonne). est le nom de l’identifiant pour la TABLE PL/SQL La contrainte NOT NULL évite de se retrouver avec des tables PL/SQL à NULL. Vous ne pouvez pas initialiser une table PL/SQL au moment de sa déclaration. Déclarez une variable PL/SQL pour stocker une date. DECLARE TYPE date_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; date_table date_table_type; Les Bases PL/SQL 5-12 Mostafa Hanoune Structure d’une Table PL/SQL Clé Primaire Colonne ... ... 1 Jones 2 Smith 3 Maduro ... ... BINARY_INTEGER Scalaire 5-13 Structure d’une Table PL/SQL Comme pour une table de la base de données, la taille d’une table PL/SQL n’est pas limitée : le nombre de lignes d’une table PL/SQL peut augmenter dynamiquement. Les tables PL/SQL peuvent avoir une colonne et une clé primaire sans qu’ils aient de noms. La colonne peut être de type scalaire ou record mais la clé primaire doit être de type BINARY_INTEGER. Vous ne pouvez pas initialiser une table PL/SQL au moment de sa déclaration. Note au formateur ( pour le Page 5-12) Le PL/SQL version 2 nécessite, dans la définition de la table, la commande INDEX BY BINARY_INTEGER . Cette commande est facultative en PL/SQL version 8, c’est pourquoi elle est placée entre crochets page 5-12. Si cette commande est omise, alors le type Table est de type Table imbriqué (NESTED TABLE) alors d’autres méthodes sont utilisables. Le type Nested Table n’est pas abordé dans ce cours. Les exemples du cours n’utilisent que la version 2 du PL/SQL. Note au formateur Mieux vaut utiliser la clé primaire comme identifiant unique pour éviter que les étudiants ne fassent de confusion avec les clés primaires des tables de la base de données. Les Bases PL/SQL 5-13 Mostafa Hanoune Créer une Table PL/SQL DECLARE TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; ename_table ename_table_type; hiredate_table hiredate_table_type; BEGIN ename_table(1) := 'CAMERON'; hiredate_table(8) := SYSDATE + 7; IF ename_table.EXISTS(1) THEN INSERT INTO ... ... END; 5-14 Créer une Table PL/SQL Il n’existe pas de type prédéfini pour les tables PL/SQL, comme il y en a pour les variables scalaires. Vous devez d’abord créer le type puis déclarer l’identifiant utilisant ce type. Utiliser une Tbale PL/SQL Syntaxe pl/sql_table_name(primary_key_value) où: primary_key_value est de type BINARY_INTEGER. Pour faire référence à la troisième ligne d’une table PL/SQL : ename_table(3) ... – Le type BINARY_INTEGER peut être compris entre 2147483647 et 2147483647, la valeur de la clé primaire peut donc être négative. L’indexation ne commence pas nécessairement à 1. Note : L’instruction table.EXISTS(i) renvoie TRUE s’il existe une ligne indexée par i. Utilisez l’instruction EXISTS pour éviter de faire référence à un élément non-existant de la table. Les Bases PL/SQL 5-14 Mostafa Hanoune Fonctions utilisables avec des tables PL/SQL Ces ’utilisation des Ces fonctions fonctions rendent rendent ll’utilisation des tables tables PL/SQL PL/SQL plus plus facile facile :: •• NEXT NEXT •• EXISTS EXISTS •• EXTEND EXTEND •• COUNT COUNT •• TRIM TRIM •• FIRST FIRST et et LAST LAST •• DELETE DELETE •• PRIOR PRIOR 5-15 Une méthode concernant les tables PL/SQL, fournit des procédures et des fonctions agissant sur ces objets. Les fonctions signalées par un astérisque ne sont valables que pour la version 8 du PL/SQL. Syntaxe: table_name.method_name[ (paramétres) ] Fonction EXISTS(n) COUNT FIRST LAST PRIOR(n) NEXT(n) EXTEND(n, i)* TRIM* DELETE Description Renvoie TRUE si le nième élément de la table PL/SQL existe. Renvoie le nombre d’éléments contenus dans la table PL/SQL Renvoie le premier et le dernier (le plus petit et le plus grand) rang de la table PL/SQL . Renvoie NULL si la table est vide. Renvoie le nombre qui précède n dans la table. Renvoie le nombre qui succède à n dans la table. Augmente la taille de la table PL/SQL. EXTEND rajoute un élément nul à la table PL/SQL. EXTEND(n) rajoute n éléments nuls à la table PL/SQL. EXTEND(n, i) rajoute n copies de l’élément i à la table PL/SQL. TRIM supprime le dernier élément de la table PL/SQL. TRIM(n) supprime les n derniers éléments de la table PL/SQL. DELETE supprime tous les éléments de la table PL/SQL. DELETE(n) supprime l’élément n de la table PL/SQL. DELETE(m, n) supprime tous les éléments de m à n de la table PL/SQL. Les Bases PL/SQL 5-15 Mostafa Hanoune Table de Records PL/SQL •• Définir Définir une une variable variable TABLE TABLE avec avec l’attribut l’attribut %ROWTYPE. %ROWTYPE. •• Déclarer Déclarer une une variable variable PL/SQL PL/SQL pour pour stocker stocker les les informations informations d’un d’un département. département. Exemple Exemple DECLARE DECLARE TYPE TYPE dept_table_type dept_table_type IS IS TABLE TABLE OF OF dept%ROWTYPE dept%ROWTYPE INDEX BY BINARY_INTEGER; INDEX BY BINARY_INTEGER; dept_table dept_table dept_table_type;; dept_table_type;; -Chaque élément -- Chaque élément de de dept_table dept_table est est un un Record Record 5-16 PL/SQL Table de Records Seule une table PL/SQL est nécessaire pour stocker les informations contenues dans toutes les colonnes d’une table DB, c’est pourquoi une table de records accroît considérablement les possibilités d’utilisation des tables PL/SQL. Utiliser Table de Records Dans l’exemple donné sur la diapositive, vous pouvez faire référence aux champs du record dept_table car chaque élément de cette table est un record. Syntaxe table(index).field Exemple dept_table(15).loc := 'Atlanta'; LOC représente un champ dans DEPT_TABLE. Note : Vous pouvez utiliser l’attribut %ROWTYPE pour déclarer un record qui représente une ligne d’une table DB. La différence entre l’attribut %ROWTYPE et le type RECORD : le RECORD vous permet de spécifier le type de données de chaque champ ou bien de déclarer vous même les champs. Les Bases PL/SQL 5-16 Mostafa Hanoune Résumé •• Définir Définir et et utiliser utiliser des des variables variables PL/SQL PL/SQL de de Types Types composés composés :: –– PL/SQL PL/SQL records records –– PL/SQL PL/SQL tables tables –– PL/SQL PL/SQL table table de de records records •• Définir Définir un un Record Record PL/SQL PL/SQL utilisant utilisant l’attribut l’attribut %ROWTYPE %ROWTYPE .. 5-17 Les Bases PL/SQL 5-17 Mostafa Hanoune Présentation de l’Exercice •• Déclaration Déclaration de de Tables Tables PL/SQL PL/SQL •• Traitement Traitement de de données données utilisant utilisant des des tables tables PL/SQL PL/SQL •• Déclaration Déclaration de de Records Records PL/SQL PL/SQL •• Traitement Traitement de de données données utilisant utilisant des des Records Records PL/SQL PL/SQL 5-18 Aperçu de l’exercice Dans cet exercice, vous allez définir, créer, et utiliser des TABLES PL/SQL et des RECORDS PL/SQL. Les Bases PL/SQL 5-18 Exercice 5 1. Créez un bloc PL/SQL pour retrouver le nom de chaque département à partir de la table DEPT et affichez le nom de chaque département à l’écran, en utilisant une table PL/SQL. a. Déclarez une table PL/SQL, MY_DEPT_TABLE, pour stocker temporairement le nom de ces départements. b. En utilisant une boucle, retrouvez le nom de tous les départements de la table DEPT et stockezles dans la TABLE PL/SQL. Chaque département a un numéro multiple de 10. c. En utilisant une autre boucle, retrouvez les noms des départements dans la TABLE PL/SQL et affichez-les à l’écran en utilisant DBMS_OUTPUT.PUT_LINE. SQL> start p5_1 ACCOUNTING RESEARCH SALES OPERATIONS PL/SQL procedure successfully completed. 2. Ecrivez un bloc PL/SQL permettant d’afficher des informations d’une commande donnée. a. Déclarez un record PL/SQL basé sur la structure de la table ORD. b. Utilisez une variable de substitution SQL*Plus pour retrouver les informations relatives à une commande spécifique et stockez cette information dans le record PL/SQL. c. Utilisez DBMS_OUTPUT. PUT_LINE pour afficher les informations de cette commande. SQL> start p5_2 Entrer un numéro de commande : 614 La commande 614 a été saisie le 01-FEB-87 et livrée le 05-FEB-87 pour un total de $23,940.00 PL/SQL procedure successfully completed. Les Bases PL/SQL 5-19 Exercice 5 (suite) S’il vous reste du temps. 3. Modifiez le bloc PL/SQL que vous avez créé dans l’exercice 1, pour retrouvez toutes les informations relatives à chaque département de DEPT et affichez ces informations à l’écran en utilisant une Table de Records PL/SQL. a. Déclarez une table PL/SQL, MY_DEPT_TABLE, pour stocker temporairement les numéro, nom, et location de chaque département. b. En utilisant une boucle, retrouvez les informations pour chaque département situé dans la table DEPT, et stockez-les dans la table PL/SQL. Chaque département a un numéro multiple de 10. c. En utilisant une autre boucle, retrouvez les informations relatives aux départements, stockez-les dans la table PL/SQL et affichez-les à l’écran en utilisant DBMS_OUTPUT.PUT_LINE. SQL> start p5_3 Dept. 10, ACCOUNTING est situé à NEW YORK Dept. 20, RESEARCH est situé à DALLAS Dept. 30, SALES est situé à CHICAGO Dept. 40, OPERATIONS est situé à BOSTON PL/SQL procedure successfully completed. Les Bases PL/SQL 5-20