Example create user tables

Fiche express
Type	Exemple DDL Oracle
Contexte	Schéma multi-tables lié + procédure PL/SQL de peuplement en masse
Voir aussi	Example create table · Example create primary foreign key

Exemple plus complet qu'une simple création de table : un petit schéma de trois tables liées (une table principale et deux tables de détail rattachées par clé étrangère), suivi d'une procédure PL/SQL qui génère un nombre paramétrable d'enregistrements de test, avec gestion des points de commit intermédiaires.

Schéma

• user_names : table principale (identifiant, nom, âge, date de mise à

 jour).

• user_names_phone : numéros de téléphone associés (clé étrangère vers

 user_names, clé primaire composite identifiant+numéro pour autoriser plusieurs
 numéros par identifiant).

• user_names_company : société associée (pas de contrainte de clé dans le

 script d'origine — à ajouter en production, voir remarque plus bas).

Procédure de peuplement

insert_user_names(num_records) repart du plus grand identifiant existant, insère num_records lignes de test dans les trois tables, et valide (commit) tous les 100 enregistrements pour éviter de saturer les segments d'annulation sur un gros volume.

Script

-- Préalable (à exécuter en tant que DBA, une seule fois) :
--
--   CREATE TABLESPACE users DATAFILE '/u10/app/oradata/ORCL/users01.dbf' SIZE 10M;
--   CREATE TABLESPACE idx   DATAFILE '/u09/app/oradata/ORCL/idx01.dbf'   SIZE 10M;
--
--   CREATE USER demo_user IDENTIFIED BY <mot_de_passe>
--     DEFAULT TABLESPACE users
--     TEMPORARY TABLESPACE temp;
--
--   GRANT dba, resource, connect TO demo_user;

CONNECT demo_user/<mot_de_passe>

/* --------------------------------------------------------
 * Table principale
 * -------------------------------------------------------- */

DROP TABLE user_names CASCADE CONSTRAINTS
/

CREATE TABLE user_names (
    name_intr_no     NUMBER(15)
  , name             VARCHAR2(30)
  , age              NUMBER(3)
  , update_log_date  DATE
)
TABLESPACE users
STORAGE (
  INITIAL     64K
  NEXT        64K
  MINEXTENTS  1
  MAXEXTENTS  100
  PCTINCREASE 0
)
/

ALTER TABLE user_names
  ADD CONSTRAINT user_names_pk PRIMARY KEY (name_intr_no)
  USING INDEX
  TABLESPACE idx
  STORAGE (
    INITIAL     28K
    NEXT        28K
    MINEXTENTS  1
    MAXEXTENTS  100
    PCTINCREASE 0
  )
/

ALTER TABLE user_names
  MODIFY (
    name            CONSTRAINT user_names_nn1 NOT NULL
  , age              CONSTRAINT user_names_nn2 NOT NULL
  , update_log_date  CONSTRAINT user_names_nn3 NOT NULL
)
/

/* --------------------------------------------------------
 * Table de détail : téléphones (1..n par identifiant)
 * -------------------------------------------------------- */

DROP TABLE user_names_phone CASCADE CONSTRAINTS
/

CREATE TABLE user_names_phone (
    name_intr_no  NUMBER(15)
  , phone_number  VARCHAR2(12)
  , country_code  VARCHAR2(15)
)
TABLESPACE users
STORAGE (
  INITIAL     64K
  NEXT        64K
  MINEXTENTS  1
  MAXEXTENTS  100
  PCTINCREASE 0
)
/

ALTER TABLE user_names_phone
  ADD CONSTRAINT user_names_phone_pk PRIMARY KEY (name_intr_no, phone_number)
  USING INDEX
  TABLESPACE idx
  STORAGE (
    INITIAL     28K
    NEXT        28K
    MINEXTENTS  1
    MAXEXTENTS  100
    PCTINCREASE 0
  )
/

ALTER TABLE user_names_phone
  MODIFY (country_code CONSTRAINT user_names_phone_nn1 NOT NULL)
/

ALTER TABLE user_names_phone
  ADD CONSTRAINT user_names_phone_fk1 FOREIGN KEY (name_intr_no)
  REFERENCES user_names (name_intr_no)
/

/* --------------------------------------------------------
 * Table de détail : société (pas de contrainte dans l'exemple
 * d'origine — à ajouter une clé étrangère vers user_names en
 * production, sur le même modèle que user_names_phone ci-dessus)
 * -------------------------------------------------------- */

DROP TABLE user_names_company CASCADE CONSTRAINTS
/

CREATE TABLE user_names_company (
    name_intr_no  NUMBER(15)
  , company_code  VARCHAR2(15)
)
TABLESPACE users
STORAGE (
  INITIAL     64K
  NEXT        64K
  MINEXTENTS  1
  MAXEXTENTS  100
  PCTINCREASE 0
)
/

/* --------------------------------------------------------
 * Procédure de peuplement en masse, avec commit intermédiaire
 * -------------------------------------------------------- */

CREATE OR REPLACE PROCEDURE insert_user_names (
  num_records IN NUMBER
)
IS

  CURSOR csr1 IS
    SELECT MAX(name_intr_no)
    FROM   user_names;

  max_intr_no  NUMBER;

BEGIN

  DBMS_OUTPUT.ENABLE;

  OPEN csr1;
  FETCH csr1 INTO max_intr_no;
  CLOSE csr1;

  max_intr_no := NVL(max_intr_no, 0) + 1;

  FOR loop_index IN max_intr_no .. (max_intr_no + (num_records - 1))
  LOOP

    INSERT INTO user_names
    VALUES (loop_index, 'Test User', 30, SYSDATE);

    INSERT INTO user_names_phone
    VALUES (loop_index, '555-0100', 'FR');

    INSERT INTO user_names_company
    VALUES (loop_index, 'DEMO');

    IF MOD(loop_index, 100) = 0 THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Commit point reached at: ' || loop_index || '.');
    END IF;

  END LOOP;

  COMMIT;
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('Successfully inserted ' || num_records ||
                        ' records into table: user_names');

END;
/

SHOW ERRORS;

Le script d'origine faisait explicitement pointer une transaction vers un segment d'annulation nommé (SET TRANSACTION USE ROLLBACK SEGMENT rbs2), une pratique héritée des segments d'annulation manuels (Oracle < 9i) — obsolète depuis l'automatic undo management (paramètre UNDO_MANAGEMENT = AUTO, standard depuis longtemps) et retirée ici. Les identifiants de connexion et les données de test du script original ont été génériciés (compte, numéro de téléphone).

Voir aussi

• Example create table — exemple de création de table plus simple (DEPT/EMP)
• Example create primary foreign key — détail de l'ajout de clé primaire/étrangère utilisé ici
• Example create sequence — alternative à un identifiant géré manuellement (MAX+1) pour peupler la clé primaire