Overblog Suivre ce blog
Administration Créer mon blog
15 août 2011 1 15 /08 /août /2011 11:41

 

SQL ALTER VIEW.

Comment compiler une Vue dans Oracle ?
Comment ajouter une contrainte dans une Vue ?
Comment supprimer une contrainte dans une Vue ?

  • La commande ALTER VIEW est utilisée pour compiler une vue invalide.
  • ‡
  • La commande ALTER VIEW est utilisée pour ajouter des contraintes déclaratives sur une Vue.
  • ‡
  • La commande ALTER VIEW est utilisée pour supprimer des contraintes déclaratives sur une Vue.

Une Vue est une table logique pointant sur une ou plusieurs Tables ou Vues et ne contient physiquement pas de données.

 

Modifier une Vue dans Oracle avec ALTER VIEW.

 

Syntaxe ALTER VIEW.

 
 ALTER VIEW [ schema. ]view { ADD CONSTRAINT constraint DISABLE NOVALIDATE; | DROP CONSTRAINT constraint | COMPILE } ; 

 

ALTER VIEW COMPILE.

Parfois il est nécessaire de compiler une Vue si la structure de la table de base à été modifiée.

 
 SQL> CREATE VIEW v_alter_view AS SELECT ename, job, sal FROM emp WHERE deptno = 20; Vue créée. SQL> COL object_name FORMAT A20; SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name = UPPER('v_alter_view'); OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- V_ALTER_VIEW VIEW VALID SQL> 

 

Nous allons modifier la structure de la table de base, et notre Vue sera alors invalide.

 
 SQL> ALTER TABLE scott.emp MODIFY(job VARCHAR2(15 BYTE)); Table modifiée. SQL> COL object_name FORMAT A20; SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name = UPPER('v_alter_view'); OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- V_ALTER_VIEW VIEW INVALID SQL> 

 

Nous allons alors compiler la Vue avec ALTER VIEW COMPILE afin de la rendre VALIDE.

 
 SQL> ALTER VIEW v_alter_view COMPILE; Vue modifiée. SQL> COL object_name FORMAT A20; SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name = UPPER('v_alter_view'); OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- V_ALTER_VIEW VIEW VALID SQL> 

 

ALTER VIEW ADD CONSTRAINT.

Ce sont des contraintes déclaratives seulement. Non actives.

 
 SQL> ALTER VIEW v_alter_view ADD CONSTRAINT pk_alter_view PRIMARY KEY (ename) DISABLE NOVALIDATE; Vue modifiée. 

 

ALTER VIEW DROP CONSTRAINT.

 
 SQL> ALTER VIEW v_alter_view DROP CONSTRAINT pk_alter_view; Vue modifiée. SQL> 

 

 

DROP VIEW CASCADE CONSTRAINT.

 
 SQL> DROP VIEW v_alter_view CASCADE CONSTRAINT; Vue supprimée. SQL> 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• CREATE VIEW
• SQL INSERT INTO TABLE ORACLE
• CREATE TABLE ORACLE
• Update Table avec MERGE INTO
• SQL INSERT INTO plusieurs TABLE ORACLE
Repost 0
Published by Daniel Roesch - dans SQL VIEW
commenter cet article
12 août 2011 5 12 /08 /août /2011 19:45

 

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> 

 

Haut de Page www.dba-ora.fr

 

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
Repost 0
Published by Daniel Roesch - dans SQL VIEW
commenter cet article