Utilisation des instructions DDL

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

Un schéma est la collection de plusieurs objets de base de données, appelés objets de schéma. Ces objets ont un accès direct par leur schéma propriétaire. Le tableau ci-dessous répertorie les objets de schéma.

Table - pour stocker des données
Afficher - pour projeter des données dans un format souhaité à 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
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 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éer 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 colonne et les tailles de colonne. Toutes ces informations peuvent être modifiées ultérieurement à l'aide des 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 AZ ou az
Peut contenir des nombres et des traits de soulignement
Peut être en HAUT ou en minuscule
Peut contenir jusqu'à 30 caractères
Impossible d'utiliser le même nom d'un autre objet existant dans votre schéma
Ne doit pas être un mot réservé SQL

En suivant les instructions ci-dessus, 'EMP85' peut être un nom de table valide, mais 85EMP ne l'est pas. De même, UPDATE ne peut pas être choisi comme nom de table puisqu'il s'agit d'un mot clé réservé SQL.

Instruction CREATE TABLE

CREATE TABLE est une instruction DDL utilisée pour créer des tables dans la base de données.La table est créée dès que le script CREATE TABLE est exécuté et est prête à contenir les données.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, l'utilisateur 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 [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

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. Il ne peut pas contenir de références à d'autres colonnes de table ou pseudo colonnes (CURRVAL, NEXTVAL, LEVEL et ROWNUM) sauf SYSDATE et USER, ou des 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 au niveau de la table (traité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 déclenchent une erreur pour abandonner l'action en cas de violation.

Par exemple, l'instruction CREATE TABLE ci-dessous crée une table EMP_TEST. Notez les spécifications de la colonne, 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 faire référence aux tables du schéma d'un autre utilisateur en préfixant le nom d'utilisateur ou le schéma avec le 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 ci-dessous -

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

Une colonne peut contenir une valeur par défaut lors de la création de la table, ce qui permet de limiter l'entrée des valeurs NULL dans la colonne. La valeur par défaut peut être déduite d'un littéral, d'une expression ou d'une fonction SQL qui doit renvoyer un type de données compatible à 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 à l'aide d'une option de sous-requête qui copie la structure de la table ainsi que les données de la table. Les données peuvent également être copiées en fonction des conditions. Les définitions de type de données de colonne, y compris les contraintes NOT NULL explicitement imposées, 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 service 20 sont copiées dans la nouvelle table.

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE 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.De manière plus générale, le comportement de la colonne peut appartenir à un nombre, un caractère ou une famille de dates.Il existe plusieurs autres sous-types qui appartiennent à ces familles.

Type de données numérique

Le type de données NUMBER comprend à la fois des 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 maintenant le type de données NUMBER sert à tous ces objectifs. Choisissez le type de données NUMBER lorsque une colonne doit stocker des données numériques qui peuvent être utilisées dans des calculs mathématiques. Parfois, le type de données NUMBER est utilisé pour stocker des numéros d'identification où ces numéros sont générés par le SGBD sous forme de nombres séquentiels.

NOMBRE (p, s), où p est la précision jusqu'à 38 chiffres et s est l'échelle (nombre de chiffres à droite de la virgule décimale). L'échelle peut être comprise entre -84 et 127.

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

FLOAT [(p)], où p est la précision binaire pouvant aller de 1 à 126. Si p n'est pas spécifié, la valeur par défaut est 126 binaire.

Type de données de date

Pour chaque type de données DATE, Siècle, Année, Mois, Jour, Heure, Minute, 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

Type de données de caractère

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 stockera des valeurs de caractères de longueur fixe. Par exemple, un numéro de sécurité sociale (SSN) aux États-Unis est attribué à chaque citoyen et a toujours une taille de 9 caractères (même si un SSN est strictement composé de chiffres, les chiffres sont traités comme des caractères), et serait spécifié comme CHAR (9). Utilisez le type de données VARCHAR2 pour stocker des données alphanumériques de longueur variable.Par exemple, le nom ou l'adresse d'un client varie considérablement en termes de nombre de caractères à stocker.La taille maximale d'une colonne VARCHAR2 est de 4000 caractères.

Type de données LOB

Oracle fournit plusieurs types de données LOB différents, y compris CLOB (objet grand caractère) et BLOB (objet grand binaire). 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 à l'aide du jeu de caractères de la base de données CHAR. Le type de données BLOB est utilisé pour stocker des objets binaires volumineux non structurés tels que ceux associés à des données image et vidéo où les données sont simplement un flux de valeurs "bit". Un 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 de grande taille dans un jeu de caractères national multi-octets allant de 8 To à 128 To.La valeur du type de données BFILE fonctionne comme un localisateur de fichiers ou un pointeur vers un fichier sur le système de fichiers du serveur La taille de fichier maximale prise en charge est de 8 To à 128 To.

Contraintes

Les contraintes sont l'ensemble de règles définies dans les tables Oracle pour garantir l'intégrité des données. Ces règles sont appliquées pour chaque colonne ou ensemble de colonnes. Chaque fois que la table participe à l'action de données, ces règles sont validées et déclenchent 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 [data type] [CONSTRAINT constraint_name] 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 n'autorisera pas le stockage des lignes dans la table des employés qui violent cette contrainte. ne peut être défini qu'au niveau de la colonne et non au niveau de la table.

Syntaxe:

COLUMN [data type] [NOT NULL]

Contrainte unique

Parfois, il est 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 ne respectent pas la contrainte d'unicité.La contrainte unique garantit que les valeurs de colonne sont distinctes , sans aucun doublon.

Syntaxe:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [nom de la contrainte] UNIQUE (nom de la colonne)

Remarque: Oracle crée en interne un index unique pour éviter la duplication dans les valeurs de la colonne.

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 lignes de données stockées dans la table.Cette colonne ou ensemble de colonnes est appelé clé primaire.La plupart des tables ont une seule colonne comme clé primaire. les colonnes clés sont limitées aux valeurs NULL et aux valeurs en double.

Points à noter -

Une table ne peut avoir qu'une seule clé primaire.
Plusieurs colonnes peuvent être matraquées sous une clé primaire composite.
Oracle crée en interne un index unique pour éviter la duplication dans les valeurs de la colonne.

Syntaxe:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

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

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

L'exemple suivant montre comment définir une clé primaire composite à l'aide de 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 jointure dans la table enfant est appelée clé étrangère. Cette propriété de la colonne correspondante dans la table parent est appelée intégrité référentielle. soit être null ou doit ê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, Oracle n'autorise pas la suppression de la ligne parente, 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 parente. De même, ON DELETE SET NULL indique que lorsqu'une ligne de la table parent est supprimée, les valeurs de clé étrangère sont définies sur null.

Syntaxe:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

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),
   ...
);

Usage of ON DELETE CASCADE clause

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

Vérifier la contrainte

Parfois, les valeurs de données stockées dans une colonne spécifique doivent être comprises dans une plage de valeurs acceptable.Une contrainte CHECK nécessite 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 à un colonne, qui doit être validée avant l'insertion des données dans la colonne. La condition ne doit pas contenir de sous-requête ou de 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:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

L'exemple suivant montre comment utiliser la contrainte CHECK au niveau de la 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 de la 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 de colonne après la création de la table dans la base de données.La commande DDL ALTER TABLE est utilisée pour effectuer ces 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 la clé primaire dans 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 table en lecture seule.

ALTER TABLE EMP_NEW READ ONLY;

Tableaux en lecture seule

Les tables en lecture seule ont été apportées en tant qu'amélioration d'Oracle 11g et permettent d'utiliser les tables à des fins de lecture seule. Dans les versions antérieures 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 en lecture et en écriture.Mais maintenant, si une table est définie en lecture seule, même le propriétaire n'a pas accès à la manipulation des données .

Syntaxe:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] 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.

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 sont plus disponibles pour la sélection.La table supprimée peut être récupérée à l'aide de l'utilitaire FLASHBACK, si disponible dans la corbeille.La suppression d'une table supprime l'index et les déclencheurs qui lui sont associés.