VIEW

Une vue est une table virtuelle, qui est essentiellement une requête SELECT nommée pour sélectionner des données d’une complexité arbitraire. Les données peuvent être extraites d’une ou plusieurs tables, d’autres vues et de procédures stockées sélectives.

Contrairement aux tables de base de données relationnelles ordinaires, une vue n’est pas un ensemble de données autonome stocké dans une base de données. Le résultat sous forme de jeu de données est créé dynamiquement lors de l’accès à une vue.

Les métadonnées de vue sont disponibles pour générer du code binaire pour les procédures stockées, les fonctions, les paquets et les déclencheurs comme s’il s’agissait d’une table ordinaire stockant des données persistantes.

`CREATE VIEW`

Objectif :

Création d’une nouvelle vue.

Disponible en

DSQL

Syntaxe

CREATE VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Paramètres de la commande `CREATE VIEW`
Paramètre	Description
viewname	Nom de la vue. Peut contenir jusqu’à 63 caractères.
select_statement	Instruction SELECT.
full_column_list	La liste des colonnes de la vue
colname	Le nom de la colonne de la vue. Les noms de colonnes en double ne sont pas autorisés.

L’instruction CREATE VIEW crée une nouvelle vue. Le nom de la vue doit être unique parmi les noms de toutes les vues, tables et procédures stockées de la base de données.

Le nom de la vue en cours de création peut être suivi d’une liste de noms de colonnes résultant de l’accès à la vue. Les noms de la liste peuvent être sans rapport avec les noms des colonnes des tables sous-jacentes. Ce faisant, leur nombre doit correspondre exactement au nombre de colonnes de la liste de sélection de l’instruction SELECT principale de la vue.

Si la liste des colonnes de la vue est absente, les noms des colonnes de la table de base ou les alias des champs de l’opérateur SELECT seront utilisés. Si les noms de champs sont répétés ou si des expressions de colonnes sans alias sont présentes, ce qui rend impossible l’obtention d’une liste de colonnes valide, la création de la vue échouera.

Le nombre de colonnes dans la liste de colonnes doit correspondre au nombre de colonnes spécifié dans la liste de choix de l’instruction SELECT spécifiée dans la définition de la vue.

Note

Points supplémentaires

Si une liste complète de colonnes est spécifiée, spécifier des alias dans l’instruction SELECT n’a aucun sens car ils seront remplacés par les noms de la liste de colonnes ;
La liste des colonnes est facultative à condition que toutes les colonnes de l’instruction SELECT aient un nom explicite et que ces noms soient uniques dans la liste des colonnes.

Vues modifiables

Une vue peut être mise à jour ou en lecture seule. Si une vue peut être mise à jour, les données auxquelles elle donne accès peuvent être modifiées à l’aide des opérateurs DML INSERT, UPDATE, DELETE, UPDATE OR INSERT, MERGE. Les modifications effectuées sur la vue sont appliquées à la ou aux tables sous-jacentes.

Une vue en lecture seule peut être mise à jour à l’aide de déclencheurs auxiliaires. Une fois qu’un ou plusieurs déclencheurs sont définis sur une vue, les changements ne sont pas automatiquement propagés à la table de base, même si la vue pouvait être mise à jour auparavant. Dans ce cas, la responsabilité de la mise à jour (suppression ou insertion) des entrées de la table de base incombe au programmeur qui a défini les triggers.

Les conditions suivantes doivent être remplies pour qu’une vue puisse être mise à jour :

L’instruction de sélection SELECT n’accède qu’à une seule table ou une seule vue qui peut être modifiée ;
L’instruction de sélection SELECT ne doit pas accéder aux procédures stockées ;
Toutes les colonnes de la table de base ou de la vue actualisable qui ne sont pas présentes dans une vue donnée doivent satisfaire à l’une des conditions suivantes
- autoriser la valeur "NULL
- Les colonnes NOT NULL doivent avoir une valeur par défaut.
- Les colonnes NOT NULL doivent être initialisées dans les triggers de la table de base.
L’instruction de sélection SELECT ne contient pas de champs définis par des sous-requêtes ou d’autres expressions ;
L’instruction de sélection SELECT ne contient pas les champs définis par les fonctions d’agrégation (MIN, MAX, AVG, COUNT, LIST), les fonctions statistiques (CORR, COVAR_POP, COVAR_SAMP etc.), les fonctions de régression linéaire (REGR_AVGX, REGR_AVGY etc.) et toutes sortes de fonctions de fenêtre ;
L’instruction de sélection SELECT ne contient pas de phrases ORDER BY, GROUP BY, HAVING ;
L’instruction de sélection SELECT ne contient pas le mot-clé DISTINCT et les restrictions sur le nombre de lignes utilisant ROWS, FIRST/SKIP, OFFSET/FETCH.

`WITH CHECK OPTIONS`

La clause facultative WITH CHECK OPTIONS exige que la vue modifiée vérifie les données nouvellement insérées ou modifiées par rapport à la condition spécifiée dans la clause WHERE de l’instruction de sélection SELECT. Lorsqu’on tente d’insérer un nouvel enregistrement ou de modifier un enregistrement, on vérifie si la condition de la clause WHERE est remplie pour cet enregistrement ; si la condition n’est pas remplie, l’insertion/modification n’est pas effectuée et un message de diagnostic correspondant est émis.

La clause WITH CHECK OPTION ne peut être spécifiée dans une instruction de création de vue que si la clause WHERE est spécifiée dans l’instruction principale SELECT de la vue. Sinon, un message d’erreur s’affiche.

Note

Si la clause WITH CHECK OPTIONS est utilisée, le système vérifie les valeurs d’entrée par rapport à la condition de la clause WHERE avant de les transmettre à la table de base. Ainsi, si les valeurs d’entrée ne passent pas le contrôle, les phrases DEFAULT ou les triggers sur la table de base, ne peuvent pas corriger les valeurs d’entrée car les actions ne seront jamais exécutées.

De plus, les champs de vue non spécifiés dans l’instruction INSERT sont transmis à la table de base en tant que valeurs NULL, indépendamment de leur présence ou absence dans l’instruction WHERE. Par conséquent, les valeurs par défaut définies sur ces champs de la table de base ne seront pas appliquées. D’autre part, les déclencheurs seront appelés et fonctionneront comme prévu.

Pour les vues sans clause WITH CHECK OPTIONS, les champs manquants dans l’instruction INSERT ne sont pas transmis du tout, donc toutes les valeurs par défaut seront appliquées.

Droits d’exécution

L’exécution du code de vue SQL se fait toujours avec les privilèges de l’utilisateur qui le définit (propriétaire).

Qui peut créer une vue ?

L’instruction CREATE VIEW peut être exécutée :

Administrateurs.
Les utilisateurs ayant le privilège de CREATE VIEW.

L’utilisateur qui crée une vue devient le propriétaire de cette vue.

Pour les utilisateurs qui n’ont pas les privilèges d’administration pour créer une vue, ils doivent également avoir les privilèges de lecture (SELECT) sur les tables et vues sous-jacentes, et d’exécution (EXECUTE) des procédures stockées sélectives utilisées.

Pour permettre l’insertion, la mise à jour et la suppression par le biais d’une vue, le créateur (propriétaire) de la vue doit disposer des privilèges INSERT, UPDATE et DELETE sur les objets de métadonnées sous-jacents.

Il n’est possible d’accorder des privilèges sur une vue à d’autres utilisateurs que si le propriétaire de la vue dispose lui-même de ces privilèges sur les objets sous-jacents. Ce sera toujours le cas si le propriétaire de la vue est le propriétaire des objets de métadonnées sous-jacents.

Exemples

Example 1. Création d’une vue

CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000;

Example 2. Création d’une vue avec contrôle des conditions de filtrage

Créez une vue renvoyant les colonnes JOB_CODE et JOB_TITLE uniquement pour les emplois dont le MAX_SALARY est inférieur à 15 000 $. Lors de l’insertion d’une nouvelle entrée ou de la modification d’une entrée existante, la condition MAX_SALARY < 15000 sera vérifiée, si la condition n’est pas remplie, l’insertion/modification sera rejetée.

CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000
WITH CHECK OPTIONS;

Example 3. Créer une vue en utilisant une liste de colonnes

CREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1
FROM PRICE;

Example 4. Créer une vue en utilisant des alias de champs

CREATE VIEW PRICE_WITH_MARKUP AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1 AS COST_WITH_MARKUP
FROM PRICE;

Example 5. Création d’une vue non renouvelable à l’aide d’une procédure stockée

CREATE VIEW GOODS_PRICE AS
SELECT
    goods.name AS goodsname,
    price.cost AS cost,
    b.quantity AS quantity
FROM
    goods
    JOIN price ON goods.code_goods = price.code_goods
    LEFT JOIN sp_get_balance(goods.code_goods) b ON 1 = 1;

Example 6. Création d’une vue actualisable à l’aide de déclencheurs

-- tables de base
RECREATE TABLE t_films(id INT PRIMARY KEY, title VARCHAR(100));
RECREATE TABLE t_sound(id INT PRIMARY KEY, audio BLOB);
RECREATE TABLE t_video(id INT PRIMARY KEY, video BLOB);
COMMIT;

-- création d'une vue non renouvelable
RECREATE VIEW v_films AS
  SELECT f.id, f.title, s.audio, v.video
  FROM t_films f
  LEFT JOIN t_sound s ON f.id = s.id
  LEFT JOIN t_video v ON f.id = v.id;

/* Pour que la vue puisse être mise à jour, créez un déclencheur qui manipulera les données sous-jacentes */

SET TERM ^;
CREATE OR ALTER TRIGGER v_films_biud FOR v_films
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0 AS
BEGIN
  IF (INSERTING) THEN
    new.id = COALESCE(new.id, GEN_ID(g_films, 1));
  IF (NOT DELETING) THEN
  BEGIN
    UPDATE OR INSERT INTO t_films(id, title)
    VALUES(new.id, new.title)
    MATCHING(id);

    UPDATE OR INSERT INTO t_sound(id, audio)
    VALUES(new.id, new.audio)
    MATCHING(id);

    UPDATE OR INSERT INTO t_video(id, video)
    VALUES(new.id, new.video)
    MATCHING(id);
 END
 ELSE
 BEGIN
   DELETE FROM t_films WHERE id = old.id;
   DELETE FROM t_sound WHERE id = old.id;
   DELETE FROM t_video WHERE id = old.id;
 END
END^
SET TERM ;^

/* Nous pouvons maintenant manipuler cette vue comme si nous travaillions avec une table */

INSERT INTO v_films(title, audio, video)
VALUES('007 coordinates skyfall', 'pif-paf!', 'oh! waw!');

Voir aussi :

ALTER VIEW, CREATE OR ALTER VIEW, RECREATE VIEW, DROP VIEW.

`ALTER VIEW`

Objectif :

Modification d’une vue existante.

Disponible en

DSQL

Syntaxe

ALTER VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Paramètres de la commande `ALTER VIEW`
Paramètre	Description
viewname	Le nom d’une vue existante.
select_statement	Instruction `SELECT`.
full_column_list	Liste des colonnes de la vue.
colname	Le nom de la colonne de la vue. Les noms de colonnes en double ne sont pas autorisés.

L’instruction ALTER VIEW modifie la définition d’une vue existante ; les permissions et les dépendances de la vue existante sont conservées. La syntaxe de l’instruction ALTER VIEW est totalement similaire à celle de l’instruction CREATE VIEW.

Warning

Soyez prudent lorsque vous modifiez le nombre de colonnes d’affichage. Le code d’application existant peut devenir inapplicable. En outre, les modules PSQL qui utilisent la vue modifiée peuvent devenir invalides. Vous trouverez des informations sur la manière de détecter ce phénomène dans l’annexe Champ RDB$VALID_BLR.

Qui peut changer la performance ?

L’opérateur ALTER VIEW peut être exécuté :

Administrateurs
Le propriétaire de la vue ;
Les utilisateurs ayant le privilège de ALTER ANY VIEW.

Exemples

Example 1. Modification d’une vue

ALTER VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

Voir aussi :

CREATE VIEW, CREATE OR ALTER VIEW, RECREATE VIEW.

`CREATE OR ALTER VIEW`

Objectif :

Créez une nouvelle vue ou modifiez une vue existante.

Disponible en

DSQL

Syntaxe

CREATE OR ALTER VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Paramètres de la commande `CREATE OR ALTER VIEW`
Paramètre	Description
viewname	Nom de la vue. Peut contenir jusqu’à 63 caractères.
select_statement	Instruction `SELECT`.
full_column_list	Liste des colonnes de la vue.
colname	Le nom de la colonne de la vue. Les noms de colonnes en double ne sont pas autorisés.

La commande CREATE OR ALTER VIEW crée une vue si elle n’existe pas. Sinon, il modifiera la vue tout en conservant les dépendances existantes.

Exemples

Example 1. Création d’une nouvelle vue ou modification d’une vue existante

CREATE OR ALTER VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

Voir aussi :

CREATE VIEW, ALTER VIEW, RECREATE VIEW.

`DROP VIEW`

Objectif :

Suppression d’une vue existante.

Disponible en

DSQL

Syntaxe

DROP VIEW viewname

Table 1. Paramètres de la commande `DROP VIEW`
Paramètre	Description
viewname	Le nom de la vue.

La commande DROP VIEW supprime une vue existante. Si la vue a des dépendances, elle ne sera pas supprimée.

Qui peut supprimer une vue ?

L’instruction DROP VIEW peut être exécutée :

Administrateurs.
Le propriétaire de la vue ;
Les utilisateurs ayant le privilège `DROP ANY VIEW'.

Exemples

Example 1. Suppression d’une vue

DROP VIEW PRICE_WITH_MARKUP;

Voir aussi :

CREATE VIEW, RECREATE VIEW.

`RECREATE VIEW`

Objectif :

Créez une nouvelle vue ou recréez une vue existante.

Disponible en

DSQL

Syntaxe

RECREATE VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Paramètres de la commande `RECREATE VIEW`
Paramètre	Description
viewname	Nom de la vue. Peut contenir jusqu’à 63 caractères.
select_statement	Instruction `SELECT`.
full_column_list	Liste des colonnes de la vue.
colname	Le nom de la colonne de la vue. Les noms de colonnes en double ne sont pas autorisés.

Crée ou recrée une vue. Si une vue portant ce nom existe déjà, l’instruction RECREATE VIEW essaiera de la supprimer et d’en créer une nouvelle. L’instruction RECREATE VIEW ne sera pas exécutée si une vue existante a des dépendances.

Exemples

Example 1. Créer une nouvelle vue ou recréer une vue existante

RECREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

Voir aussi :

CREATE VIEW, CREATE OR VIEW, DROP VIEW.