Vendredi 12 août 2011 5 12 /08 /Août /2011 19:45

Oracle SQL CREATE VIEW

SQL CREATE VIEW.

Comment créer une Vue dans Oracle ?
A quoi sert une Vue ?
Combien de colonnes dans une Vue Oracle ?

Une Vue permet de limiter l'accès à des données dans la base de données.
Une Vue peut faciliter la création de requêtes complexes.
Une Vue permet de présenter des données issues d'une table sous de différents formats.

Une Vue est une table logique pointant sur une ou plusieurs tables ou vues et ne contient physiquement pas de données.
La structure d'une Vue est stockée dans le dictionnaire de données et peut contenir 1000 colonnes.

Créations de Vues dans Oracle.

Syntaxe CREATE VIEW.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 
view [(alias[,alias]...)] AS
SELECT ...
WITH { READ ONLY | CHECK OPTION [ CONSTRAINT constraint ] }

GRANT CREATE VIEW.

Vous devez avoir le privilège CREATE VIEW pour exécuter les création de vues.

C:\>SET ORACLE_SID=DBTEST

C:\>SQLPLUS / AS sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Jeu. Aout 11 23:01:44 2011

Connecté à :
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> GRANT CREATE VIEW TO SCOTT;

Autorisation de privilèges (GRANT) acceptée.

CREATE VIEW Simple.

C:\>SET ORACLE_SID=DBTEST
C:\>SQLPLUS scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Jeu. Aout 11 23:29:03 2011
Connecté à :
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> CREATE VIEW v_emp_dept_10 AS
     SELECT      ename, job, sal, deptno
     FROM        emp
     WHERE       deptno = 10;

Vue créée.

SQL> DESC v_emp_dept_10;
 Nom                     NULL ?   Type
 ----------------------- -------- --------------
 ENAME                            VARCHAR2(10)
 JOB                              VARCHAR2(9)
 SAL                              NUMBER(7,2)
 DEPTNO                           NUMBER(2)

SQL> SELECT column_name, updatable
       FROM user_updatable_columns
      WHERE table_name = 'V_EMP_DEPT_10';

COLUMN_NAME                    UPD
------------------------------ ---
ENAME                          YES
JOB                            YES
SAL                            YES
DEPTNO                         YES

SQL>

CREATE VIEW avec des Opérations Numériques.

Si des opérations numériques sont effectuées, la colonne SAL sera de type FLOAT NUMBER.

C:\>SET ORACLE_SID=DBTEST
C:\>SQLPLUS scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Jeu. Aout 11 23:29:03 2011
Connecté à :
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> CREATE VIEW v_emp_dept_10 AS
     SELECT      ename, job, sal*1.33 SAL, deptno
     FROM        emp
     WHERE       deptno = 10;

Vue créée.

SQL> DESC v_emp_dept_10;
 Nom                     NULL ?   Type
 ----------------------- -------- --------------
 ENAME                            VARCHAR2(10)
 JOB                              VARCHAR2(9)
 SAL                              NUMBER
 DEPTNO                           NUMBER(2)

SQL> SELECT column_name, updatable
       FROM user_updatable_columns
      WHERE table_name = 'V_EMP_DEPT_10';

COLUMN_NAME                    UPD
------------------------------ ---
ENAME                          YES
JOB                            YES
SAL                            NO
DEPTNO                         YES

SQL>

CREATE VIEW avec Noms de colonnes Prédéfinies.

C:\>SET ORACLE_SID=DBTEST
C:\>SQLPLUS scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Jeu. Aout 11 23:29:03 2011
Connecté à :
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> CREATE VIEW v_emp_dept_10 
                (nom, metier, salaire, dpt) AS
     SELECT      ename, job, sal*1.33, deptno
     FROM        emp
     WHERE       deptno = 10;

Vue créée.

SQL> DESC v_emp_dept_10;
 Nom                         NULL ?   Type
 --------------------------- -------- ---------------
 NOM                                  VARCHAR2(10)
 METIER                               VARCHAR2(9)
 SALAIRE                              NUMBER
 DPT                                  NUMBER(2)

SQL>

CREATE FORCE VIEW.

FORCE VIEW permet de créer des vues lorsque la table ou les tables utilisées ne sont pas disponibles dans votre environnement,
alors la Vue sera INVALIDE mais existante.
Avec NOFORCE (valeur par défaut), si les tables n'existent pas, la vue n'est pas créée.
Les DBA ou développeurs PL/SQL peuvent se retrouver dans des situations d'urgences de développement, ou ils sont obligés de FORCER la création de vue car des GRANT sont manquants sur les Objets ou alors le DBA n'a pas encore eu le temps de créer les tables...

C:\>SET ORACLE_SID=DBTEST
C:\>SQLPLUS scott/tiger

Connecté à :
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> CREATE VIEW v_emp_dept_10
  2                  (nom, metier, salaire, dpt) AS
  3       SELECT      ename, job, sal*1.33, deptno
  4       FROM        new_emp
  5       WHERE       deptno = 10;
     FROM        new_emp
                 *
ERREUR à la ligne 4 :
ORA-00942: Table ou vue inexistante

SQL> CREATE FORCE VIEW  v_emp_dept_10
  2                  (nom, metier, salaire, dpt) AS
  3       SELECT      ename, job, sal*1.33, deptno
  4       FROM        new_emp
  5       WHERE       deptno = 10;

Avertissement : Vue créée avec erreurs de compilation.

La vue est créée avec l'option FORCE, mais l'utilisation de DESCRIBE renvoit une erreur et nous voyons que le STATUS de la VIEW est à INVALID.

SQL> DESC v_emp_dept_10;
ERROR:
ORA-24372: l'objet à décrire n'est pas valide

SQL> COL object_name FORMAT A15;
SQL> SELECT object_name, object_type, status
     FROM user_objects
     WHERE object_name = UPPER('v_emp_dept_10');

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
V_EMP_DEPT_10   VIEW                INVALID

Maintenant nous créons la table new_emp, le STATUS de la VIEW est encore à INVALIDE, mais la commande DESCRIBE est opérationnelle.
On s’aperçoit que l'utilisation de la commande DESCRIBE sur la VIEW à implicitement compilée la Vue. Même effet si on avait fait un SELECT sur la VIEW car Oracle compile les VIEWS des lors qu'on y accéde.

SQL> CREATE TABLE SCOTT.NEW_EMP
     (
       EMPNO     NUMBER(4)                               NULL,
       ENAME     VARCHAR2(10 BYTE)                       NULL,
       JOB       VARCHAR2(9 BYTE)                        NULL,
       MGR       NUMBER(4)                               NULL,
       HIREDATE  DATE                                    NULL,
       SAL       NUMBER(7,2)                             NULL,
       COMM      NUMBER(7,2)                             NULL,
       DEPTNO    NUMBER(2)                               NULL
     )
     TABLESPACE USERS;

Table créée.

SQL> COL object_name FORMAT A15;
SQL> SELECT object_name, object_type, status
     FROM user_objects
     WHERE object_name = UPPER('v_emp_dept_10');

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
V_EMP_DEPT_10   VIEW                INVALID

SQL> DESC v_emp_dept_10
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- -------------------------
 NOM                                                VARCHAR2(10)
 METIER                                             VARCHAR2(9)
 SALAIRE                                            NUMBER
 DPT                                                NUMBER(2)

SQL> COL object_name FORMAT A15;
SQL> SELECT object_name, object_type, status
     FROM user_objects
     WHERE object_name = UPPER('v_emp_dept_10');

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
V_EMP_DEPT_10   VIEW                VALID

SQL>

CREATE VIEW WITH READ ONLY.

Les vues créées avec l'option WITH READ ONLY peuvent être interrogées mais aucune opérations DML (UPDATE, DELETE, INSERT) peut être effectuées sur la Vue.

SQL> CREATE VIEW v_emp_dept_10
                     (nom, metier, salaire, dpt) AS
          SELECT      ename, job, sal, deptno
          FROM        emp
          WHERE       deptno = 10
     WITH READ ONLY;

Vue créée.

SQL> SELECT column_name, updatable
            FROM user_updatable_columns
           WHERE table_name = 'V_EMP_DEPT_10';

COLUMN_NAME                    UPD
------------------------------ ---
NOM                            NO
METIER                         NO
SALAIRE                        NO
DPT                            NO

SQL>

CREATE VIEW WITH CHECK OPTION CONSTRAINT.

L'option WITH CHECK OPTION ou WITH CHECK OPTION CONSTRAINT crée une contrainte de vérification sur la vue à partir de la clause WHERE.
Les vues créées avec l'option WITH CHECK OPTION CONSTRAINT empêche toutes mises à jour de la Vue si les conditions de la clause WHERE ne sont pas respectées. (Visible dans USER_CONTRAINTS en type V).

SQL> CREATE VIEW v_emp_dept_10
                     (nom, metier, salaire, dpt) AS
          SELECT      ename, job, sal, deptno
          FROM        emp
          WHERE       deptno = 10
     WITH CHECK OPTION CONSTRAINT check_10;

Vue créée.

SQL> INSERT INTO v_emp_dept_10
                 (nom, metier, salaire, dpt)
           VALUES('Daniel','DBA',4000,78);
INSERT INTO v_emp_dept_10
            *
ERREUR à la ligne 1 :
ORA-01402: vue WITH CHECK OPTION - violation de clause WHERE


SQL> INSERT INTO v_emp_dept_10
                 (nom, metier, salaire, dpt)
           VALUES('Daniel','DBA',4000,10);

1 ligne créée.

SQL>

CREATE OR REPLACE VIEW.

L'option OR REPLACE permet de changer la définition d'une vue sans faire un DROP/CREATE. L'avantage de l'option OR REPLACE, c'est la non suppression des privilèges accordés à la vue, cependant les objets dépendant de la vue deviennent invalides et doivent être compilés.

SQL> CREATE OR REPLACE VIEW v_emp_dept_10
                 (nom, metier, salaire, dpt)
     AS SELECT   ename, job, sal, deptno
          FROM   emp
         WHERE   deptno = 10;

Vue créée.

SQL>

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser

• SQL INSERT INTO TABLE ORACLE
• CREATE TABLE ORACLE
• Update Table avec MERGE INTO
• SQL INSERT INTO plusieurs TABLE ORACLE

Plus
- Email
- Lien
- Imprimer

Par Daniel Roesch - Publié dans : SQL VIEW
Ecrire un commentaire - Voir les 0 commentaires

Précédent : Oracle SQL LOADER / SQLLDR Load Data Retour à l'accueil Suivant : Importer CSV dans Oracle avec SQL loader

Oracle SQL CREATE VIEW

SQL CREATE VIEW.

Créations de Vues dans Oracle.

Syntaxe CREATE VIEW.

GRANT CREATE VIEW.

CREATE VIEW Simple.

CREATE VIEW avec des Opérations Numériques.

CREATE VIEW avec Noms de colonnes Prédéfinies.

CREATE FORCE VIEW.

CREATE VIEW WITH READ ONLY.

CREATE VIEW WITH CHECK OPTION CONSTRAINT.

CREATE OR REPLACE VIEW.

Derniers Articles Oracle

À lire aussi…

DBA Online

Download Ressources Oracle

Rechercher

Thèmes

Derniers Articles Oracle

Tutoriel - Cours

SCRIPTS SQL

Paramètres Instance

Les Vues V$ ...

ORA ERROR LIST

Syndication

Oracle SQL CREATE VIEW

SQL CREATE VIEW.

Créations de Vues dans Oracle.

Syntaxe CREATE VIEW.

GRANT CREATE VIEW.

CREATE VIEW Simple.

CREATE VIEW avec des Opérations Numériques.

CREATE VIEW avec Noms de colonnes Prédéfinies.

CREATE FORCE VIEW.

CREATE VIEW WITH READ ONLY.

CREATE VIEW WITH CHECK OPTION CONSTRAINT.

CREATE OR REPLACE VIEW.

Derniers Articles Oracle

À lire aussi…

DBA Online

Download Ressources Oracle

Newsletter

Rechercher

Thèmes

Derniers Articles Oracle

Tutoriel - Cours

SCRIPTS SQL

Paramètres Instance

Les Vues V$ ...

ORA ERROR LIST

Syndication