technologies oracle
Post on 24-Feb-2016
50 Views
Preview:
DESCRIPTION
TRANSCRIPT
TECHNOLOGIES ORACLE
Création et gestion des objets
© sebvita.com
ObjectifsEn 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
© sebvita.com
Création de tables
© sebvita.com
Objets de baseObjet Description
Table Unité de stockage basique, composée de lignes
View Représentation logique des données d’une ou plusieurs tables
Génère des valeurs numériquesSequence
Index Améliore les performances de certaines requêtes
Synonym Donne des noms alternatifs aux objets
© sebvita.com
Types de donnéesType Description
VARCHAR2(size) Chaîne de caractères de longueur variable
CHAR(size)
NUMBER(p,s)
DATE
LONG
CLOB
Chaîne de caractères de longueur fixe
Données numériques de longueur variable
Date et temps
Chaîne de caractères (jusqu’à 2 GB)
Chaîne de caractères (jusqu’à 4 GB)
© sebvita.com
Types de données
Type Description
RAW et LONG RAW Données binaires
BLOB
BFILE
ROWID
Données binaires (jusqu’à 4 GB)
Données binaires stockées dans un fichier externe (jusqu’à 4 GB)
Système numérique de base 64 identifiant de manière unique une ligne
© sebvita.com
Types de données temporels
Type Description
TIMESTAMP De longueur variable
INTERVAL YEAR TOMONTH
INTERVAL DAY TOSECOND
Stocké en tant qu’interval d’années et de mois
Stocké en tant qu’interval de jours, heures, minutes et secondes
© sebvita.com
Types de données temporels• Le type TIMESTAMP est une extension du
type DATE• Il stocke les années, mois, jour de DATE
ainsi que les heures, minutes, secondes et fractions de secondes
• Il est possible de préciser un décalage horaireTIMESTAMP[(fractional_seconds_precision)]
TIMESTAMP[(fractional_seconds_precision)]WITH TIME ZONE
TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE
© sebvita.com
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)]
© sebvita.com
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
© sebvita.com
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][, ...]);
© sebvita.com
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;
© sebvita.com
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.
© sebvita.com
Créer une table
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), create_date DATE DEFAULT SYSDATE );
DESCRIBE dept
Table created.
• Exemple
© sebvita.com
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
© sebvita.com
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.
© sebvita.com
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;
© sebvita.com
Utiliser une sous-requête
DESCRIBE dept80
CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;
Table created.
© sebvita.com
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
© sebvita.com
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
© sebvita.com
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, ...),
© sebvita.com
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));
© sebvita.com
NOT NULL• Permet d’interdire les valeurs nulles
Contrainte NOT NULL(Valeurs nulles impossibles)
…20 rows selected
Contrainte NOT NULL
Absence de contrainte NOT NULL(Valeurs nulles possibles)
© sebvita.com
UNIQUE
EMPLOYEES
…
Contrainte UNIQUE
INSERT INTO
Non autorisé :la valeur existe déjà
Possible
© sebvita.com
UNIQUE• Définie au niveau de la table ou de la
colonneCREATE 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));
© sebvita.com
PRIMARY KEYDEPARTMENTS
…
PRIMARY KEY
INSERT INTO
Non autorisé :50 existe déjà
Non autorisé :valeur nulle
© sebvita.com
FOREIGN KEYDEPARTMENTS
…
PRIMARY KEY
INSERT INTO
EMPLOYEES
…
FOREIGN KEY
Autorisé
Non autorisé :9 n’existe pas
© sebvita.com
FOREIGN KEY• Définie au niveau de la table ou de la
colonneCREATE 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));
© sebvita.com
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
© sebvita.com
CHECK• Définie une condition que chaque ligne
doit respecter• Les expressions suivantes ne sont pas
autorisées :– Pseudocolonnes CURRVAL, NEXTVAL, LEVEL et ROWNUM
– Appels aux fonctions SYSDATE, UID, USER et USERENV
– Requêtes..., salary NUMBER(2)CONSTRAINT emp_salary_minCHECK (salary > 0),...
© sebvita.com
ExempleCREATE 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));
© sebvita.com
Violation de contraintesUPDATE employeesSET department_id = 55WHERE department_id = 110;
Le département 55 n’existe pas.
UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK)violated - parent key not found
© sebvita.com
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 departmentsWHERE department_id = 60;
DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK)violated - child record found
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
Création de tables
© sebvita.com
Création d’autres objets
© sebvita.com
Les vues
© sebvita.com
Avantages d’une vue
Restreindre l’accès aux données
Fournir l’indépendance des données
Simplifier certaines requêtes
Présenter les mêmes données sous plusieurs formes
© sebvita.com
Vues simples ou complexesDésignation
Nombre de tables
Contient des fonctions
Contient des groupesPossibilité de faire des DML
Vues simples
Une
Non
Non
Oui
Vues complexes
Une ou plus
Oui
Oui
Pas toujours
© sebvita.com
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]];
© sebvita.com
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;
DESCRIBE empvu80
View created.
© sebvita.com
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.
© sebvita.com
Récupérer les données• Sélectionner les colonnes de la vue
SELECT *FROM salvu50;
© sebvita.com
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.
© sebvita.com
Créer une vue complexe• Créer une vue complexe avec des
fonctions de groupe et une jointureCREATE 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.
© sebvita.com
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– Pseudocolonne ROWNUM
© sebvita.com
DML sur les vues• Impossible de modifier une ligne d’une
vue si– Fonction de groupe– Clause GROUP BY– Mot clé DISTINCT– Pseudocolonne ROWNUM– Colonne définie par une expression
© sebvita.com
DML sur les vues• Impossible d’ajouter une ligne à une vue
si– Fonction de groupe– Clause GROUP BY– Mot clé DISTINCT– Pseudocolonne ROWNUM– Colonne définie par une expression– Colonne NOT NULL dans les tables, non
présente dans la vue
© sebvita.com
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
CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.
© sebvita.com
Interdire les DML• Possibilité d’ajouter l’option WITH READ ONLY
• Toute tentative de DML sur la vue retournera une erreurCREATE 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.
© sebvita.com
Supprimer une vue• Supprimer une vue ne supprime aucune
donnéesDROP VIEW view;
DROP VIEW empvu80;
View dropped.
© sebvita.com
Les séquences• Génère des suites de nombres• Est partageable entre objets• Peut servir à générer une clé primaire• Évite l’utilisation de code dans
l’application• Permet l’utilisation du cache
© sebvita.com
Ordre CREATE SEQUENCE• Syntaxe
CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
© sebvita.com
Créer une séquence• Créer une séquence DEPT_DEPTID_SEQ
pour générer la clé primaire de la table• Ne pas utiliser l’option CYCLE
CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;
Sequence created.
© sebvita.com
Pseudocolonnes• NEXTVAL retourne la valeur suivante d’une
séquence. La valeur est unique même si utilisée par différents utilisateurs.
• CURRVAL retourne la valeur courante.
© sebvita.com
Utiliser une séquence• Insérer un nouveau département
« Support »
• Voir la valeur courante de la séquence
INSERT INTO departments(department_id, department_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);
SELECT dept_deptid_seq.CURRVALFROM dual;
1 row created.
© sebvita.com
Mettre en cache• Mettre les valeurs en cache permet un
accès plus rapide• Des « trous » dans les séquences peuvent
arriver lorsque :– Un rollback est utilisé– Le système plante– La séquence est utilisée pour plusieurs
tables
© sebvita.com
Modifier une séquence• Syntaxe
ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;
Sequence altered.
© sebvita.com
Modifier une séquence• Obligation d’être propriétaire ou avoir le
privilège ALTER sur la séquence• Seules les futures valeurs de la séquence
sont affectées• La séquence doit être supprimée puis
recréée pour changer la valeur de départ• Pour supprimer une séquence :
DROP SEQUENCE dept_deptid_seq;
Sequence dropped.
© sebvita.com
Les index• Un index
– Est un objet du schéma– Permet d’accélérer certaines requêtes
avec un pointeur– Réduit les I/O sur le disque avec un
accès direct aux données– Est indépendant de la table– Est utilisé et maintenu
automatiquement par le serveur Oracle
© sebvita.com
Création des index• Automatiquement : lors de la définition
d’une clé primaire ou d’une clé unique
• Manuellement : les utilisateurs peuvent créer des index judicieusement
© sebvita.com
Créer un index• Créer un index sur une ou plusieurs
colonnes
• Améliorer la vitesse d’accès à la colonne LAST_NAME de la table EMPLOYEES
CREATE INDEX indexON table (column[, column]...);
CREATE INDEX emp_last_name_idxON employees(last_name);
Index created.
© sebvita.com
Règles sur les index
Créer un index lorsque…
Une colonne contient beaucoup de valeurs différentes
Une colonne contient beaucoup de valeurs nulles
Une ou plusieurs colonnes sont souvent utilisées ensemble dans une clause WHERE ou une jointure
La table est importante et les requêtes sont supposées impacter moins de 2 à 4 % des lignes
© sebvita.com
Règles sur les indexNe pas créer d’index lorsque…
La colonne n’est pas souvent utilisée comme condition dans une requête
La table est petite ou les requêtes retournent plus de 2 à 4 % des lignes
La table est mise à jour très fréquemment
Les colonnes sont utilisées comme expression
XXXX
© sebvita.com
Supprimer un index• Syntaxe
• Supprimer l’index UPPER_LAST_NAME_IDX
• Pour supprimer un index, vous devez en être propriétaire ou avoir le privilège DROP ANY INDEX
DROP INDEX index;
DROP INDEX emp_last_name_idx;
Index dropped.
© sebvita.com
Les synonymes• Simplifie l’accès à certains objets en leur
donnant un synonyme.CREATE [PUBLIC] SYNONYM synonymFOR object;
© sebvita.com
Les synonymes• Créer un nom plus court pour la vue DEPT_SUM_VU
• Supprimer un synonyme
CREATE SYNONYM d_sumFOR dept_sum_vu;
DROP SYNONYM d_sum;
Synonym created.
Synonym dropped.
© sebvita.com
Le dictionnaire de données
Tables contenant les données :EMPLOYEESDEPARTMENTSLOCATIONSJOB_HISTORY…
Vues du dictionnaire de données :DICTIONARYUSER_OBJECTSUSER_TABLESUSER_TAB_COLUMNS…
© sebvita.com
Structure du dictionnaire
Consiste en :■ des tables de base
■ des vues
© sebvita.com
Convention de nommage
Préfixe Utilisation
USER Ce qui est dans votre schéma, vous appartient
ALL
DBA
V$
Ce à quoi vous pouvez accéder
Tous les objets (réservées aux DBA)
Données de performance
© sebvita.com
Le dictionnaire de données• Commencez avec la vue DICTIONARY
• Exemple
DESCRIBE DICTIONARY
SELECT *FROM dictionaryWHERE table_name = 'USER_OBJECTS';
© sebvita.com
Le dictionnaire de données• USER_OBJECTS
– Tous les objets appartenant à l’utilisateur
– Permet d’obtenir les noms, types ainsi que :
• Date création• Date de dernière modification• Statut (valide ou non)
• ALL_OBJECTS– Tous les objets auxquels l’utilisateur a
accès
Les vues USER_OBJECTS et ALL_OBJECTS
© sebvita.com
Le dictionnaire de donnéesLa vue USER_OBJECTS
SELECT object_name, object_type, created, statusFROM user_objectsORDER BY object_type;
…
© sebvita.com
Le dictionnaire de donnéesLa vue USER_TABLES
DESCRIBE user_tables
SELECT table_nameFROM user_tables;
…
© sebvita.com
Le dictionnaire de donnéesLes informations sur les colonnes
DESCRIBE user_tab_columns
…
© sebvita.com
Le dictionnaire de donnéesLes informations sur les colonnes
SELECT column_name, data_type, data_length,data_precision, data_scale, nullable
FROM user_tab_columnsWHERE table_name = 'EMPLOYEES';
© sebvita.com
Le dictionnaire de données• USER_CONSTRAINTS montre les définitions
de contraintes sur vos tables• USER_CONS_COLUMNS montre vos colonnes
soumises à des contraintes
Informations sur les contraintes
DESCRIBE user_constraints
…
© sebvita.com
Le dictionnaire de donnéesInformations sur les contraintes
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, statusFROM user_constraintsWHERE table_name = 'EMPLOYEES';
© sebvita.com
Le dictionnaire de donnéesInformations sur les contraintes
DESCRIBE user_cons_columns
SELECT constraint_name, column_nameFROM user_cons_columnsWHERE table_name = 'EMPLOYEES';
…
© sebvita.com
Le dictionnaire de donnéesInformations sur les vues
DESCRIBE user_views
SELECT DISTINCT view_name FROM user_views;
SELECT text FROM user_viewsWHERE view_name = 'EMP_DETAILS_VIEW';
© sebvita.com
Le dictionnaire de donnéesInformations sur les séquences
DESCRIBE user_sequences
© sebvita.com
Le dictionnaire de données• Vous pouvez vérifier les valeurs de votre
séquence
• La colonne LAST_NUMBER affiche la prochaine valeur disponible si NOCACHE est spécifié
Informations sur les séquences
SELECT sequence_name, min_value, max_value, increment_by, last_numberFROM user_sequences;
© sebvita.com
Le dictionnaire de donnéesInformations sur les synonymes
DESCRIBE user_synonyms
SELECT *FROM user_synonyms;
© sebvita.com
Le dictionnaire de données• Ajout de commentaires sur une table ou une
colonne avec COMMENT
• Les commentaires sont visibles dans le dictionnaire de données :– ALL_COL_COMMENTS– USER_COL_COMMENTS– ALL_TAB_COMMENTS– USER_TAB_COMMENTS
Commentaires sur les tables
COMMENT ON TABLE employees IS 'Employee Information';
Comment created.
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
Création d’autres objets
© sebvita.com
Résumé
Les vues et les index
Les ordres DDL
Le dictionnaire de données
© sebvita.com
Les contraintes
et les séquences
Ressources• http://www.oracle.com• http://otn.oracle.com
© sebvita.com
top related