Technologies Oracle

1. Technologies Oracle Création et gestion des objets

2. Objectifs En suivant ce cours, vous serez capable : • De classer les principaux objets de bases de données • De gérer la structure d’une table • De créer des vues simples ou complexes

3. Création de tables

4. Objets de base Objet Description Table Unité de stockagebasique, composée de lignes Représentationlogique des donnéesd’uneouplusieurs tables View Sequence Génère des valeursnumériques Index Améliore les performances de certainesrequêtes Synonym Donne des nomsalternatifs aux objets

5. Types de données Type Description VARCHAR2(size) Chaîne de caractères de longueur variable CHAR(size) Chaîne de caractères de longueur fixe NUMBER(p,s) Donnéesnumériques de longueur variable DATE Date et temps LONG Chaîne de caractères (jusqu’à 2 GB) CLOB Chaîne de caractères (jusqu’à 4 GB)

6. Types de données Type Description RAWet LONGRAW Donnéesbinaires BLOB Donnéesbinaires (jusqu’à 4 GB) BFILE Donnéesbinairesstockéesdans un fichierexterne (jusqu’à 4 GB) ROWID Systèmenumérique de base 64 identifiant de manière unique uneligne

7. Types de données temporels Type Description TIMESTAMP De longueur variable INTERVALYEARTO MONTH Stocké en tantqu’intervald’années et de mois INTERVALDAYTO SECOND Stocké en tantqu’interval de jours, heures, minutes et secondes

8. Types de données temporels • Le type TIMESTAMP est une extension du type DATE • Il stocke les années, mois, jour de DATEainsi que les heures, minutes, secondes et fractions de secondes • Il est possible de préciser un décalage horaire TIMESTAMP[(fractional_seconds_precision)] TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE

9. Types de données temporels • Le type INTERVAL YEAR TO MONTH • Le type INTERVAL DAY TO SECOND INTERVAL YEAR [(year_precision)] TO MONTH INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

10. Règles de nommage • Les noms de tables ou colonnes : • Commencent par une lettre • Font 1 à 30 caractères • Contiennent A-Z, a-z, 0-9, _, $, # • Sont uniques dans le schéma • Ne sont pas des noms réservés à Oracle

11. L’ordre CREATE TABLE • Vous devez avoir : • Le privilège CREATE TABLE • Un espace de stockage • Vous choisissez : • Un nom de table • Le nom des colonnes, leur type et leur taille CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

12. Tables d’autres utilisateurs • Les tables des autres utilisateurs ne sont pas dans notre propre schéma • Utiliser le nom d’utilisateur en préfixe USER A USER B SELECT * FROM userB.employees; SELECT * FROM userA.employees;

13. Option DEFAULT • Choisir une valeur par défaut pour une colonne • Valeurs littérales, expressions ou fonctions SQL • Pas le nom d’une autre colonne ou pseudocolonne • Le type de données doit correspondre à la colonne ... hire_date DATE DEFAULT SYSDATE, ... CREATE TABLE hire_dates (id NUMBER(8), hire_date DATE DEFAULT SYSDATE ); Table created.

14. Créer une table • Exemple CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), create_date DATE DEFAULT SYSDATE ); Table created. DESCRIBE dept

15. Modifier une table • Utiliser l’ordre ALTER TABLE pour : • Ajouter une colonne • Modifier une colonne existante • Définir une valeur par défaut • Supprimer une colonne

16. Supprimer une table • Toutes les données et la structure sont supprimées • Les transactions sont validées • Les index sont supprimés • Les contraintes sont supprimées • Pas de ROLLBACK possible DROP TABLE dept80; Table dropped.

17. Utiliser une sous-requête • Syntaxe • Faire correspondre le nombre et le type des colonnes si spécifiées CREATE TABLE table [(column, column...)] AS subquery;

18. Utiliser une sous-requête CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; Table created. DESCRIBE dept80

19. Contraintes : présentation • Ajoute des règles sur la table • Évite les suppressions en cas de dépendances • Types de contraintes : • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK

20. Contraintes : présentation • Vous pouvez les nommer ; sinon Oracle les nomme SYS_Cn • Création de la contrainte • À la création de la table • Après la création de la table • Au niveau d’une colonne • Au niveau de la table • Contraintes visibles dans le dictionnaire de données

21. Définir une contrainte • Syntaxe • Au niveau de la colonne • Au niveau de la table CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]); column [CONSTRAINT constraint_name] constraint_type, column,... [CONSTRAINT constraint_name] constraint_type (column, ...),

22. Définir une contrainte • Exemples : CREATE TABLE employees( employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name VARCHAR2(20), ...); CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));

23. NOT NULL • Permet d’interdire les valeurs nulles … 20 rows selected Contrainte NOTNULL (Valeurs nulles impossibles) Contrainte NOTNULL Absence de contrainteNOTNULL (Valeursnullespossibles)

24. UNIQUE ContrainteUNIQUE EMPLOYEES … INSERTINTO Possible Non autorisé :la valeurexiste déjà

25. UNIQUE • Définie au niveau de la table ou de la colonne CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... CONSTRAINT emp_email_uk UNIQUE(email));

26. PRIMARY KEY DEPARTMENTS PRIMARYKEY … Non autorisé :valeur nulle INSERTINTO Non autorisé :50 existe déjà

27. FOREIGN KEY DEPARTMENTS PRIMARY KEY … EMPLOYEES FOREIGN KEY … INSERTINTO Non autorisé : 9 n’existe pas Autorisé

28. FOREIGN KEY • Définie au niveau de la table ou de la colonne CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email));

29. FOREIGN KEY • FOREIGN KEY : Définie la colonne de la table enfant • REFERENCES : Identifie la table et la colonnes parentes • ON DELETE CASCADE : Supprime les lignes dépendantes lorsqu’une ligne de la table parent est supprimée • ON DELETE SET NULL : Convertie les clés étrangères en valeurs nulles

30. CHECK • Définie une condition que chaque ligne doit respecter • Les expressions suivantes ne sont pas autorisées : • PseudocolonnesCURRVAL, NEXTVAL, LEVEL et ROWNUM • Appels aux fonctions SYSDATE, UID, USER et USERENV • Requêtes ..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),...

31. Exemple CREATE TABLE employees (employee_id NUMBER(6) CONSTRAINT emp_employee_id PRIMARY KEY, first_name VARCHAR2(20), last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL, email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE, phone_number VARCHAR2(20), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL, job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL, salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary>0), commission_pct NUMBER(2,2), manager_id NUMBER(6), department_id NUMBER(4) CONSTRAINT emp_dept_fk REFERENCES departments(department_id));

32. Violation de contraintes UPDATE employees SET department_id = 55 WHERE department_id = 110; UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found Le département 55 n’existe pas.

33. Violation de contraintes • Une ligne ayant une clé primaire utilisée dans une autre table en tant que clé étrangère ne peut être supprimée. DELETE FROM departments WHERE department_id = 60; DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

34. Vous avez des questions ?

35. Création de tables

36. Création d’autres objets

37. Les vues

38. Avantages d’une vue Simplifier certaines requêtes Restreindre l’accès aux données Fournir l’indépendance des données Présenter les mêmes données sous plusieurs formes

39. Vues simples ou complexes Désignation Vues simples Vues complexes Nombre de tables Une Uneou plus Contient des fonctions Non Oui Contientdes groupes Non Oui Possibilité de faire des DML Oui Pas toujours

40. Créer une vue • Avec une sous-requête dans le CREATE VIEW • La sous-requête peut-être complexe CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

41. Créer une vue • Créer une vue EMPVU80 qui contient des détails des employés du département 80 • Décrire la structure de la vue CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; View created. DESCRIBE empvu80

42. Créer une vue • Créer une vue avec des alias de colonnes CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50; View created.

43. Récupérer les données • Sélectionner les colonnes de la vue SELECT * FROM salvu50;

44. Modifier une vue • Modifier EMPVU80 avec l’ordre CREATE OR REPLACE VIEW • Les alias de colonnes sont dans le même ordre que les colonnes dans la sous-requête CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80; View created.

45. Créer une vue complexe • Créer une vue complexe avec des fonctions de groupe et une jointure CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e JOIN departments d ON (e.department_id = d.department_id) GROUP BY d.department_name; View created.

46. DML sur les vues • DML généralement possible sur les vues simples • Impossible de supprimer une ligne d’une vue si • Fonction de groupe • Clause GROUP BY • Mot clé DISTINCT • PseudocolonneROWNUM

47. DML sur les vues • Impossible de modifier une ligne d’une vue si • Fonction de groupe • Clause GROUP BY • Mot clé DISTINCT • PseudocolonneROWNUM • Colonne définie par une expression

48. DML sur les vues • Impossible d’ajouter une ligne à une vue si • Fonction de groupe • Clause GROUP BY • Mot clé DISTINCT • PseudocolonneROWNUM • Colonne définie par une expression • Colonne NOT NULL dans les tables, non présente dans la vue

49. Clause WITH CHECK OPTION • Pour s’assurer que les DML réalisés sur la vue restent sur le domaine de la vue • Impossible de changer le numéro de département à travers la vue CREATEOR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ; View created.

50. Interdire les DML • Possibilité d’ajouter l’option WITH READ ONLY • Toute tentative de DML sur la vue retournera une erreur CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY ; View created.