Langage Définition des Données LDD

1. Langage Définition des Données LDD ALTER CREATE DROP

2. TYPES DE DONNEES

3. TYPES DE DONNEES Les LOB (Large Objects) permettent le stockage de 4 Go de données dans la base, ils existent depuis la version 8 d'Oracle, il existe 3 types (BLOB, CLOB, NCLOB).

4. LES TABLES Création Syntaxe formelle CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} [,{ column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} ]...) [ [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage_clause] [ RECOVERABLE | UNRECOVERABLE ] [ PARALLEL ( [ DEGREE { integer | DEFAULT } ] [ INSTANCES { integer | DEFAULT } ] ) | NOPARALLEL ] [ CACHE | NOCACHE ] | [CLUSTER cluster (column [, column]...)] ] [ ENABLE enable_clause | DISABLE disable_clause ] ... [AS subquery]

5. LES TABLES Caractéristiques/Exemples • Chaque table est stockée dans un espace logique appelé TABLESPACE. • Il est possible de créer une table et de la renseigner par les résultats d’un ordre SELECT. • Les types de données sont les mêmes que les types des colonnes du SELECT • CREATE TABLE bonus1(nom,remuneration) • AS SELECT nom,salaire • FROM emp; • Une table ne peut contenir plus de 1000 colonnes (255 sous la version 7.x). • une table contient au plus 1 colonne de type LONG ou LONG RAW. • Exemple 1 • CREATE TABLE employes ( • emp_no NUMBER(3) CONSTRAINT pk_01 PRIMARY KEY, • nom VARCHAR2(25) NOT NULL, • prn VARCHAR2(35), • dt_nais DATE NOT NULL, • sal NUMBER(11,2) NOT NULL, • sup_no NUMBER(3) CONSTRAINT fk_01 REFERENCES superieur(sup_no) • TABLESPACE users • STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 10));

6. LES TABLES Caractéristiques/Exemples Modification • Exemple 2 • CREATE TABLE employes ( • no_emp NUMBER, • no_service NUMBER, • CONSTRAINT no_emp_pk PRIMARY KEY (no_emp), • CONSTRAINT no_service_fk FOREIGN KEY (no_service)REFERENCES service (no_service) • TABLESPACE tbs_emp; • Remarques • L’utilisation du TABLESPACE est très importante. • Les contraintes peuvent concernées, soit les colonnes, soit les tables. • Les noms des contraintes permettent l’activation ou la désactivation de la contrainte.

7. LES TABLES Modification Modification des tables ALTER TABLE Changer le type de données d’une colonne vide Modification de la structure des tables Marquer les colonnes comme inutilisables Augmenter ou diminuer la taille d’une colonne. Ajouter et supprimer des colonnes d’une table Modifier la contrainte NOT NULL. Créer ou supprimer une contrainte

8. LES TABLES Modification Syntaxe formelle ALTER TABLE [schema.]table [ADD { { column datatype [DEFAULT expr] [column_constraint] ...| table_constraint} | ( { column datatype [DEFAULT expr] [column_constraint] ...| table_constraint} [, { column datatype [DEFAULT expr] [column_constraint] ...| table_constraint} ] ... ) } ] [ MODIFY { column [datatype] [DEFAULT expr] [column_constraint] ... | (column [datatype] [DEFAULT expr] [column_constraint] ... [, column datatype [DEFAULT expr] [column_constraint] ...] ...) } ] [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [STORAGE storage_clause] [DROP drop_clause] ... [ALLOCATE EXTENT [( [SIZE integer [K|M] ] [DATAFILE 'filename'] [ INSTANCE integer] )] [ PARALLEL ( [ DEGREE { integer | DEFAULT } ] [ INSTANCES { integer | DEFAULT } ] ) | NOPARALLEL ] [ CACHE | NOCACHE ] [ ENABLE { enable_clause | TABLE LOCK } | DISABLE { disable_clause | TABLE LOCK } ] ...

9. LES TABLES Modification Syntaxe usuelle ALTER TABLE nom_table [MODIFY(col1 type1 [NOT NULL] [, col1 type1 [NOT NULL] , …])] [ADD(col1 type1 [NOT NULL] [, col1 type1 [NOT NULL] , …])] [DROP col1,col2,…)] [ADD CONSTRAINT clause_contrainte] [SET UNUSED COLUMN nom_col] [DROP UNUSED COLUMNS] Exemple 1 ALTER TABLE employes MODIFY dt_nais NULL; Exemple 2 ALTER TABLE employes ADD (dt_rec DATE NOT NULL,adresse VARCHAR2(50)); Exemple 3 ALTER TABLE employes ADD (age NUMBER(2) NOT NULL) DROP (dt_nais,dt_rec); Exemple 4 ALTER TABLE employes ADD CONSTRAINT emp_sup_no CHECK (sup_no!=no);

10. LES TABLES Changement de nom Syntaxe RENAME ancien_nom TO nouveau_nom ; Exemple RENAME employe TO emps; Remarque La syntaxe est la même pour tous objets du schéma : table, vue, séquence, synonyme, …

11. LES TABLES Suppression des lignes • Syntaxe • TRUNCATE TABLE [schema.]table • [ {DROP | REUSE} STORAGE] ; • Rôle • Supprime toutes les lignes d’une table. • Préserve la structure de la table. • Commande irréversible, car ne nécessitant pas de validation. • Cette commande est du ressort du DBA. • Exemple • TRUNCATE TABLE emps;

12. LES TABLES Suppression tables • Syntaxe • DROP TABLE [schema.]table • [CASCADE CONSTRAINTS] ; • Rôle • Supprime toutes les lignes d’une table. • Supprime la structure de la table. • L’espace de la table est libéré. • CASCADE CONSTRAINTS permet de supprimer la table même si l’une de ses colonnes • sont référencées par d’autres tables. • Exemple • DROP TABLE emps • CASCADE CONSTRAINTS;

13. LES TABLES Les partitions À diviser une grande table en plusieurs tables plus petites Partitionner une table revient à Avantages des partitions Améliorer les performances au niveau recherche pour les requêtes SQL. Faciliter les sauvegardes et les restaurations de la base. Prévenir contre la corruption des fichiers de la base.

14. LES TABLES Les partitions TYPES PARTITIONS Partitionnement par plage Partitionnement par hachage Création des partitions en fonction d'une plage de valeurs d'une colonne Partition en fonction de valeurs générées par hachage sur une clé donnée.

15. LES TABLES Les partitions • Syntaxe 1 : par plage • CREATE TABLE table • (col1 type_donnée1, …, coln type_donnéen) • PARTITION BY RANGE (col) • (PARTITION part1 VALUES THAN (val1) TABLESPACE tbs1, • … • (PARTITION partn VALUES THAN (MAXVALUE) TABLESPACE tbsn); • Syntaxe 1 : par Hachage • CREATE TABLE table • (col1 type_donnée1, …, coln type_donnéen) • PARTITION BY HASH (col) • PARTITION n • STORE IN (tbs1,tbs2,…tbsn);

16. LES VUES - VIEWS Définition - Syntaxe VUE - VIEW Table virtuelle basée sur un SELECT Données de la vue se trouvent dans les tables qui la sous-tendent Chaque appel de la vue invoque le SELECT • Cataloguer des ordres SQL complexes. • Restreindre l'accès à certaines des colonnes • d'une ou de plusieurs tables. • Cacher la structure physique de la base de données.

17. LES VUES - VIEWS Création - Syntaxe • Syntaxe • CREATE [OR REPLACE] VIEW [schema.]view [(alias [,alias]...)] • AS subquery • [WITH CHECK OPTION [CONSTRAINT constraint]] ; • Exemple • CREATE OR REPLACE VIEW vue_4145 AS • SELECT no,nom,titre,salaire*4*12 "SALAIRE ANNUEL" • FROM e_emp • WHERE service_no BETWEEN 41 AND 45; • Remarques • Le SELECT peut contenir toutes les clauses sauf : CONNECT BY/ORDERBY. • WITH CHECK OPTION permet que les UPDATE ou INSERT faits à travers la vue ne • produisent que des lignes correspondant à la définition de la vue. • Une view peut être créée par SELECTION, JOINTURE ou PROJECTION.

18. LES VUES - VIEWS Contraintes Ne pas Contenir toutes les colonnes NOT NULL. INSERT Contient une colonne calculée VUE VIEW UPDATE Renferme la clause GROUP BY DELETE Renferme la clause DISTINCT

19. Modification / Suppression LES VUES - VIEWS • Modification • ALTER VIEW [schema.]view COMPILE; • Utilisée si la structure des tables sous-tendant la view changent. • Suppression • DROP VIEW [schema.]view; • Exemple • DROP VIEW vue_4145;

20. INDEX Définition - Syntaxe INDEX Accélérateur d'accès sur une table ou un cluster. Peut être unique ou multiple, concaténé ou non. Transparent aux requête, mais utilisé par l'optimiseur Oracle. • Amélioration des temps d'accès. • Intervenir dans le tuning des bases Oracle.

21. INDEX Définition - Syntaxe • Syntaxe • CREATE [UNIQUE] INDEX [schema.]index • ON { [schema.]table (column [ASC|DESC][, column [ASC|DESC]] ...) | CLUSTER [schema.]cluster } • [INITRANS integer] [MAXTRANS integer] • [TABLESPACE tablespace] [STORAGE storage_clause] • [PCTFREE integer] [NOSORT | SORT] • [ RECOVERABLE | UNRECOVERABLE ] • Exemple • CREATE UNIQUE INDEX i_nom_emp • ON employes(nom DESC) • TABLESPACE tbs_idx;

22. INDEX Règles construction index • Utiliser un index UNIQUE pour créer une contrainte d'unicité sur une colonne. • Stocker les index dans un TABLESPACE différent de celui des données. • Le choix des colonnes d'index doit tenir compte de leurs fréquence de mise à jour. • Un index peut être partitionné. • Depuis la version 8i d'Oracle, on peut définir des index sur les fonctions : • Exemple • CREATE INDEX nom_idx • ON table(UPPER(col));

23. INDEX Modification - Suppression • Modification • ALTER INDEX [schema.]index REBUILD; • Utiliser cette commande pour reconstruire l'index. • Suppression • DROP INDEX [schema.]index; • Exemple • DROP INDEX vue_4145;

24. SEQUENCE Définition - Syntaxe • Rôle • Permet de générer séquentiellement des numéros attribués à des colonnes numériques ayant une contrainte de type PRIMARY KEY ou UNIQUE. • Syntaxe • CREATE SEQUENCE [schema.]sequence • [INCREMENT BY integer] • [START WITH integer] • [MAXVALUE integer | NOMAXVALUE] • [MINVALUE integer | NOMINVALUE] • [CYCLE | NOCYCLE] • [CACHE integer | NOCACHE] • [ORDER | NOORDER]

25. SEQUENCE Définition - Syntaxe

26. SEQUENCE Exemples - Fonctions Exemple 1 CREATE SEQUENCE seq_emp INCREMENT BY 1 START WITH 100 MAXVALUE 50000 MINVALUE 10 CYCLE CACHE 100 ORDER; Exemple 2 SELECT seq_emp.NEXTVAL // Valeur suivante de la séquence FROM dual; Exemple 3 SELECT seq_emp.CURRVAL //valeur courante FROM dual;

27. SEQUENCE Modification - Suppression • Modification • ALTER SEQUENCE [schema.]sequence • [INCREMENT BY integer] • [MAXVALUE integer | NOMAXVALUE] • [MINVALUE integer | NOMINVALUE] • [CYCLE | NOCYCLE] • [CACHE integer | NOCACHE] • [ORDER | NOORDER] ; • Suppression • DROP SEQUENCE [schema.]sequence; • Exemple • DROP SEQUENCE seq_emp;

28. SYNONYMES Définition - Syntaxe • Rôle • Permet de donner un nom ou un alias afin de le référencer différemment. • Un synonyme peut être créé sur les objets suivants : • TABLES, VUES, SEQUENCES, SYNONYMES, CLUSTERS, PROCEDURES, FONCTIONS, • PACKAGES. • Syntaxe • CREATE [PUBLIC] SYNONYM [schema.]synonym • FOR [schema.]object[@dblink] • Exemple • CREATE SYNONYM t_employes • FOR employes; • SELECT * from t_employes; • t_employes et employes référencent la même table.

29. SYNONYMES Nom - Suppression • Remarques • PUBLIC permet de créer des synonymes accessibles par tous les utilisateurs. • Par défaut le synonyme est privé, accessible uniquement par le propriétaire. • Un synonyme est mesure de protection des objets. • Changement de nom • RENAME SYNONYM ancien TO nouveau; • Suppression • DROP SYNONYM nom_syn;

30. UTILISATEURS Définition - Syntaxe • Définition • Un utilisateur est un objet au même titre que les autres (tables, vues, …). • Des tables spéciales entretenues par le DBA permettent de stocker toutes les • informations sur les utilisateurs. • Un synonyme est mesure de protection des objets. • Utilisateurs prédéfinis • SCOTT / TIGER : droits limités. • SYS / CHANGE_ON_INSTALL : superutilisateur. • SYSTEM / MANAGER : synonyme de SYS, ayant des droits, mais moins que SYS. • Opérations sur les utilisateurs • Création. • Modification. • Suppression. • Attribution des privilèges. • Attribution de rôles.

31. UTILISATEURS Création • Syntaxe • CREATE USER user • IDENTIFIED BY password • [DEFAULT TABLESPACE tablespace] • [TEMPORARY TABLESPACE tablespace] • [QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ... • [PROFILE profile]; • Exemple • CREATE USER rpig4 • IDENTIFIED BY rpig4 • DEFAULT TABLESPACE users • TEMPORAY TABLESPACE temp; • L'indication du DEFAULT TABLESPACE est importante. • Le quota permet d'attribuer à l'utilisateur un quota sur un TABLESPACE.

32. UTILISATEURS Modification – Suppression • Modification • ALTER USER user • [IDENTIFIED BY password] • [DEFAULT TABLESPACE tablespace] • [TEMPORARY TABLESPACE tablespace] • [QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ... • [PROFILE profile] • [DEFAULT ROLE { role [, role] ... | ALL [EXCEPT role [, role] ...] | NONE}] ; • Suppression • DROP USER user [CASCADE] ; • CASCADE signifie qu'il faut effacer tous les objets de cet utilisateur.

33. ROLES Définition - Syntaxe • Définition • Un objet qui encapsule plusieurs droits ou privilèges élémentaires. • Un rôle est rattaché à un ou plusieurs utilisateurs. • Un rôle est le reflet d'une typologie d'utilisateur (user final, administrateur, exploitant). • Le rôle des rôles est de simplifier le travail d'administration de la base. • Rôles prédéfinis • CONNECT : droits de connexion et d'exécution d'ordre LMD. • RESOURCE : droits de connexion et d'exécution d'ordre LDD non DBA. • DBA : Tous les droits. • Opérations sur les rôles • Création. • Modification. • Suppression.

34. CLUSTERS Définition - Syntaxe • Définition • Un cluster est organisation qui permet de rassembler dans un même bloc disque les • lignes de plusieurs tables, en se basant sur une valeur commune d'une ou de • plusieurs colonnes. • Un rôle est rattaché à un ou plusieurs utilisateurs. • Un rôle est le reflet d'une typologie d'utilisateur (user final, administrateur, exploitant). • Le rôle des rôles est de simplifier le travail d'administration de la base. • Clusters indexés • Stockage dans les blocs selon la valeur d'une clé fonctionnelle. • Accès aux blocs selon un index (obligatoire). • Hash clusters • Stockage dans les blocs selon la valeur de la clé de hachage. • Lors de la création du hach cluster, indiquer le nombre de valeurs de la clé.

35. CLUSTERS Création • Syntaxe • CREATE CLUSTER [schema.]cluster (column datatype [,column datatype] ... ) [PCTUSED integer] [PCTFREE integer] • [SIZE integer [K|M] ] • [INITRANS integer] [MAXTRANS integer] • [TABLESPACE tablespace] • [STORAGE storage_clause] • [ PARALLEL ( [ DEGREE { integer | DEFAULT } ] • [ INSTANCES { integer | DEFAULT } ] ) | NOPARALLEL ] • [ CACHE | NOCACHE ] • [INDEX | [HASH IS column] HASHKEYS integer];

36. CLUSTERS Création • Exemple • CREATE CLUSTER cl_01 • (deptno NUMBER(3)); • CREATE INDEX cl_01_idx ON CLUSTER cl_01; • CREATE TABLE salaire ( • … • ) • CLUSTER cl_01(dept_no); • CREATE TABLE departement ( • … • ) • CLUSTER cl_01(no);