Utilisation des déclarations DDL

Utilisation des déclarations DDL pour créer et gérer des tables

Un schéma est la collection de plusieurs objets de base de données,qui sont connus comme des objets de schéma.Ces objets ont un accès direct par leur schéma propriétaire.Le tableau ci-dessous énumère les objets de schéma.

• Table – pour stocker les données

• Vue – pour projeter les données dans un format désiré à partir d’une ou plusieurs tables

• Séquence – pour générer des valeurs numériques

• Index – pour améliorer les performances des requêtes sur les tables. pour améliorer les performances des requêtes sur les tables

• Synonyme – nom alternatif d’un objet

L’une des premières étapes de la création d’une base de données consiste à créer les tables qui stockeront les données d’une organisation.La conception de la base de données implique l’identification des besoins des utilisateurs du système pour divers systèmes organisationnels tels que la saisie des commandes, la gestion des stocks et les comptes clients. Indépendamment de la taille et de la complexité de la base de données, chaque base de données est composée de tables.

Création de la table

Pour créer une table dans la base de données,un DBA doit avoir certaines informations en main – le nom de la table, le nom de la colonne, les types de données de la colonne et la taille de la colonne. Toutes ces informations peuvent être modifiées ultérieurement à l’aide de commandes DDL.

Conventions de dénomination des tables –

• Le nom que vous choisissez pour une table doit suivre ces règles standard :

• Le nom doit commencer par une lettre A-Z ou a-.z

• Peut contenir des chiffres et des traits de soulignement

• Peut être en majuscules ou en minuscules

• Peut comporter jusqu’à 30 caractères

.

• Ne peut pas utiliser le même nom d’un autre objet existant dans votre schéma

• Ne doit pas être un mot réservé SQL

Suivant les directives ci-dessus, ‘EMP85’ peut être un nom de table valide.Mais 85EMP ne l’est pas.De même, UPDATE ne peut pas être un choisi comme nom de table puisqu’il s’agit d’un mot-clé réservé SQL.

L’instruction CREATE TABLE

La CREATE TABLE est une instruction DDL qui est utilisée pour créer des tables dans la base de données.La table se crée dès que le script CREATE TABLE est exécuté et est prête à contenir les données à partir de.L’utilisateur doit avoir le privilège système CREATE TABLE pour créer la table dans son propre schéma.Mais pour créer une table dans le schéma de n’importe quel utilisateur, celui-ci doit avoir le schéma CREATE ANY TABLE.

Voici la syntaxe d’une instruction CREATE TABLE de base.Il peut y avoir de nombreuses clauses supplémentaires pour fournir explicitement les spécifications de stockage ou les valeurs de segment.

CREATE TABLE table ( { column datatype ... | table_constraint} ... | table_constraint} ]...) 

Dans la syntaxe ci-dessus, DEFAULT spécifie la valeur par défaut qui peut être utilisée pendant l’instruction INSERT si la colonne est ignorée. Elle ne peut pas contenir de références à d’autres colonnes ou pseudo-colonnes de table (CURRVAL, NEXTVAL, LEVEL et ROWNUM), à l’exception de SYSDATE et USER, ou de constantes de date qui ne sont pas entièrement spécifiées.

Les contraintes sont les règles définies facultativement au niveau de la colonne ou de la table (abordées plus loin dans ce chapitre).Ces règles sont vérifiées lors de toute action de données (Insertion, mise à jour) sur la table et soulèvent une erreur pour interrompre l’action lors de sa violation.

Par exemple, l’instruction CREATE TABLE ci-dessous crée une table EMP_TEST. Notez les spécifications des colonnes, le type de données et la précision.

CREATE TABLE SCOTT.EMP_TEST(EMPID NUMBER,ENAME VARCHAR2(100),DEPARTMENT_ID NUMBER,SALARY NUMBER,JOB_ID VARCHAR2(3),HIREDATE DATE,COMM NUMBER);

Un utilisateur peut référencer les tables du schéma d’un autre utilisateur en faisant précéder le nom d’utilisateur ou le schéma du nom de la table.Par exemple, un utilisateur GUEST souhaite interroger le nom et le salaire de l’employé à partir de la table EMP_TEST qui appartient à SCOTT. Il peut émettre la requête suivante –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Une colonne peut contenir une valeur par défaut au moment de la création de la table, ce qui permet de limiter les valeurs NULL qui entrent dans la colonne. La valeur par défaut peut être déduite soit d’un littéral, d’une expression ou d’une fonction SQL qui doit retourner un type de données compatible avec la colonne. Dans l’instruction CREATE TABLE ci-dessous, notez que la colonne LOCATION_ID a la valeur par défaut 100.

CREATE TABLE SCOTT.DEPARTMENT(DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – Créer une table à l’aide d’une sous-requête

Une table peut être créée à partir d’une table existante dans la base de données en utilisant une option de sous-requête.Elle copie la structure de la table ainsi que les données de la table. Les données peuvent également être copiées en fonction de conditions.Les définitions des types de données des colonnes, y compris les contraintes NOT NULL imposées explicitement, sont copiées dans la nouvelle table.

Le script CTAS ci-dessous crée une nouvelle table EMP_BACKUP. Les données des employés du département 20 sont copiées dans la nouvelle table.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Types de données

Les types de données sont utilisés pour spécifier le comportement de base d’une colonne dans la table.Sur une base plus large, le comportement de la colonne peut soit appartenir à la famille des nombres, des caractères ou des dates.Il existe de multiples autres sous-types qui appartiennent à ces familles.

Type de données nombre

Le type de données NUMBER englobe à la fois les valeurs numériques entières, à virgule fixe et à virgule flottante.Les premières versions d’Oracle définissaient différents types de données pour chacun de ces différents types de nombres,mais aujourd’hui le type de données NUMBER sert à tous ces objectifs.Choisissez le type de données NUMBER lorsqu’une colonne doit stocker des données numériques qui peuvent être utilisées dans des calculs mathématiques.Occasionnellement,le type de données NUMBER est utilisé pour stocker des numéros d’identification lorsque ces numéros sont générés par le SGBD sous forme de numéros séquentiels.

NUMBER (p, s), où p est la précision jusqu’à 38 chiffres et s est l’échelle (nombre de chiffres à droite du point décimal).L’échelle peut être comprise entre -84 et 127.

NUMBER (p),est un nombre à virgule fixe avec une échelle de zéro et une précision de p.

FLOAT ,où p est la précision binaire qui peut être comprise entre 1 et 126. Si p n’est pas spécifié, la valeur par défaut est binaire 126.

Type de données DATE

Pour chaque type de données DATE, le siècle, l’année, le mois, le jour, l’heure, la minute, la seconde sont stockés dans la base de données. Chaque système de base de données a un format de date par défaut qui est défini par le paramètre d’initialisation NLS_DATE_FORMAT. Ce paramètre est généralement défini sur DD-MON-YY.Si vous ne spécifiez pas d’heure, l’heure par défaut est 12:00:00 a.m.

Type de données de caractères

Oracle prend en charge trois types de données de caractères prédéfinis, notamment CHAR, VARCHAR, VARCHAR2 et LONG.VARCHAR et VARCHAR2 sont en fait synonymes, et Oracle recommande d’utiliser VARCHAR2 au lieu de VARCHAR.Utilisez le type de données CHAR lorsque la colonne stocke des valeurs de caractères de longueur fixe. Par exemple, aux États-Unis, un numéro de sécurité sociale (SSN) est attribué à chaque citoyen et comporte toujours 9 caractères (même si un SSN est strictement composé de chiffres, les chiffres sont traités comme des caractères) ; il est spécifié sous la forme CHAR(9). Utilisez le type de données VARCHAR2 pour stocker des données alphanumériques de longueur variable.Par exemple, un nom ou une adresse de client variera considérablement en termes de nombre de caractères à stocker.La taille maximale d’une colonne VARCHAR2 est de 4 000 caractères.

Type de données LOB

Oracle fournit plusieurs types de données LOB différents, notamment CLOB (character large object) et BLOB (binary large object).Les colonnes de ces types de données peuvent stocker des données non structurées, notamment du texte, des images, des vidéos et des données spatiales.Les colonnes de ces types de données peuvent stocker des données non structurées, notamment du texte, des images, des vidéos et des données spatiales. Le type de données CLOB peut stocker jusqu’à huit téraoctets de données de caractères en utilisant le jeu de caractères de la base de données CHAR. Le type de données BLOB est utilisé pour stocker de grands objets binaires non structurés, tels que ceux associés aux données d’image et de vidéo, où les données sont simplement un flux de valeurs « bit ».Le type de données BLOB peut stocker jusqu’à huit téraoctets de données binaires. Le type de données NCLOB peut stocker des objets volumineux en caractères nationaux multi-octets jusqu’à 8 à 128 To. La valeur du type de données BFILE fonctionne comme un localisateur de fichier ou un pointeur vers un fichier du système de fichiers du serveur. La taille maximale de fichier prise en charge est de 8TB à 128TB.

Constraints

Les contraintes sont l’ensemble des règles définies dans les tables Oracle pour assurer l’intégrité des données.Ces règles sont appliquées placées pour chaque colonne ou ensemble de colonnes.Chaque fois que la table participe à une action sur les données, ces règles sont validées et soulèvent une exception en cas de violation. Les types de contraintes disponibles sont NOT NULL, Primary Key, Unique, Check et Foreign Key.

La syntaxe ci-dessous peut être utilisée pour imposer une contrainte au niveau de la colonne.

Syntaxe :

column constraint_type

Toutes les contraintes, sauf NOT NULL, peuvent également être définies au niveau de la table. Les contraintes composites ne peuvent être spécifiées qu’au niveau de la table.

Contrainte NOT NULL

Une contrainte NOT NULL signifie qu’une ligne de données doit avoir une valeur pour la colonne spécifiée comme NOT NULL.Si une colonne est spécifiée comme NOT NULL,le SGBDR Oracle ne permettra pas de stocker dans la table des employés des lignes qui violent cette contrainte.Elle ne peut être définie qu’au niveau de la colonne, et non au niveau de la table.

Syntaxe :

COLUMN 

Contrainte UNIQUE

Il est parfois nécessaire d’appliquer l’unicité pour une valeur de colonne qui n’est pas une colonne de clé primaire.La contrainte UNIQUE peut être utilisée pour appliquer cette règle et Oracle rejettera toutes les lignes qui violent la contrainte unique.La contrainte unique garantit que les valeurs de colonne sont distinctes, sans aucun doublon.

Syntaxe:

Niveau de colonne :

COLUMN 

Niveau de la table : CONSTRAINT UNIQUE (nom de la colonne)

Note : Oracle crée en interne un index unique pour éviter la duplication des valeurs de la colonne.Les index seraient abordés plus tard dans PL/SQL.

CREATE TABLE TEST( ... , NAME VARCHAR2(20) CONSTRAINT TEST_NAME_UK UNIQUE, ... );

En cas de clé unique composite,elle doit être définie au niveau de la table comme ci-dessous.

CREATE TABLE TEST( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

Clé primaire

Chaque table doit normalement contenir une colonne ou un ensemble de colonnes qui identifie de manière unique les rangées de données qui sont stockées dans la table.Cette colonne ou cet ensemble de colonnes est appelé clé primaire.La plupart des tables ont une seule colonne comme clé primaire.Les colonnes de clé primaire sont restreintes contre les NULL et les valeurs dupliquées.

Points à noter –

• Une table ne peut avoir qu’une seule clé primaire.

• Plusieurs colonnes peuvent être regroupées sous une clé primaire composite.

• Oracle crée en interne un index unique pour empêcher la duplication des valeurs des colonnes.Les index seraient discutés plus tard dans PL/SQL.

Syntaxe:

Niveau colonne:

COLUMN 

Niveau table:

CONSTRAINT PRIMARY KEY 

L’exemple suivant montre comment utiliser la contrainte PRIMARY KEY au niveau colonne.

CREATE TABLE TEST( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... ); 

L’exemple suivant montre comment définir une clé primaire composite en utilisant la contrainte PRIMARY KEY au niveau de la table.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) ); 

Clé étrangère

Lorsque deux tables partagent la relation parent-enfant basée sur une colonne spécifique, la colonne de jonction dans la table enfant est connue sous le nom de clé étrangère.Cette propriété de la colonne correspondante dans la table parent est connue sous le nom d’intégrité référentielle.Les valeurs de la colonne de clé étrangère dans la table enfant peuvent être nulles ou doivent être les valeurs existantes de la table parent.Veuillez noter que seules les colonnes de clé primaire de la table référencée sont éligibles pour appliquer l’intégrité référentielle.

Si une clé étrangère est définie sur la colonne de la table enfant, alors Oracle ne permet pas la suppression de la ligne parent, si elle contient des lignes enfants.Cependant, si l’option ON DELETE CASCADE est donnée au moment de la définition de la clé étrangère, Oracle supprime toutes les lignes enfants pendant la suppression de la ligne parent.De même,ON DELETE SET NULL indique que lorsqu’une ligne de la table parent est supprimée, les valeurs de la clé étrangère sont définies comme nulles.

Syntaxe:

Niveau de colonne:

COLUMN 

Niveau de table:

CONSTRAINT 

L’exemple suivant montre comment utiliser la contrainte FOREIGN KEY au niveau de la colonne.

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ...);

Utilisation de la clause ON DELETE CASCADE

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ...);

Contrainte de contrôle

Parfois, les valeurs des données stockées dans une colonne spécifique doivent se situer dans une certaine plage de valeurs acceptable.Une contrainte de contrôle exige que la condition de contrôle spécifiée soit vraie ou inconnue pour chaque ligne stockée dans la table.La contrainte de contrôle permet d’imposer une règle conditionnelle sur une colonne, qui doit être validée avant que les données soient insérées dans la colonne. La condition ne doit pas contenir une sous-requête ou une pseudo-colonne CURRVAL NEXTVAL, LEVEL, ROWNUM ou SYSDATE.

Oracle permet à une seule colonne d’avoir plus d’une contrainte CHECK. En fait, il n’y a pas de limite pratique au nombre de contraintes CHECK qui peuvent être définies pour une colonne.

Syntaxe :

Niveau colonne:

COLUMN CONSTRAINT 

Niveau table:

CONSTRAINT CHECK (condition)

L’exemple suivant montre comment utiliser la contrainte CHECK au niveau colonne.

CREATE TABLE TEST( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in ('A','B','C')), ...);

L’exemple suivant montre comment utiliser la contrainte CHECK au niveau table.

CREATE TABLE TEST( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate),);

Instruction ALTER TABLE

Un DBA peut apporter des modifications à la structure de la table ou aux définitions des colonnes après que la table ait été créée dans la base de données.La commande DDL ALTER TABLE est utilisée pour effectuer de telles actions.La commande Alter fournit plusieurs utilitaires exclusifs pour les objets de schéma.L’instruction ALTER TABLE est utilisée pour ajouter, supprimer, renommer et modifier une colonne dans une table.

L’instruction ALTER TABLE ci-dessous renomme la table EMP en EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

L’instruction ALTER TABLE ci-dessous ajoute une nouvelle colonne TESTCOL à la table EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

L’instruction ALTER TABLE ci-dessous renomme la colonne TESTCOL en TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

L’instruction ALTER TABLE ci-dessous supprime la colonne TESTNEW de la table EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

L’instruction ALTER TABLE ci-dessous ajoute une clé primaire sur la colonne EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

L’instruction ALTER TABLE ci-dessous supprime la clé primaire.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

L’instruction ALTER TABLE ci-dessous fait passer le mode de la table en lecture seule.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Les tables en lecture seule sont apparues comme une amélioration dans Oracle 11g.Elle permet aux tables d’être utilisées à des fins de lecture seule. Dans les versions précédentes d’oracle, les tables étaient rendues en lecture seule en accordant le privilège SELECT aux autres utilisateurs, mais le propriétaire avait toujours le privilège de lecture écriture.Mais maintenant, si une table est définie comme Read only, même le propriétaire n’a pas accès sur la manipulation des données.

Syntaxe:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Illustration

SQL>CREATE TABLE ORATEST (id NUMBER)SQL>INSERT INTO ORATEST VALUES (1);SQL>ALTER TABLE ORATEST READ ONLY;SQL> INSERT INTO ORATEST VALUES (2);INSERT INTO ORATEST VALUES (2) *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> UPDATE ORATEST SET id = 2;UPDATE ORATEST SET id = 2 *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> DELETE FROM ORATEST;DELETE FROM ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> TRUNCATE TABLE ORATEST;TRUNCATE TABLE ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));ALTER TABLE ORATEST ADD (description VARCHAR2 (50))*ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST READ WRITE;Table altered.SQL> DELETE FROM ORATEST;1 row deleted.

L’instruction DROP TABLE

L’instruction DROP TABLE est utilisée pour supprimer une table de la base de données. La table supprimée et ses données ne restent plus disponibles pour la sélection.La table supprimée peut être récupérée à l’aide de l’utilitaire FLASHBACK,s’il est disponible dans la recyclebin.La suppression d’une table supprime l’index et les triggers qui lui sont associés.

Syntaxe:

DROP TABLE 

L’instruction ci-dessous abandonne la table et la place dans la recyclebin.

DROP TABLE emp_new;

L’instruction ci-dessous abandonne la table et la chasse de la recyclebin également.

DROP TABLE emp_new PURGE;

.