420-5D3 : BD – Vues
Définition
Dans une base de données, une vue (ou « view » en anglais) est une représentation virtuelle d’une ou plusieurs tables de la base de données. Elle est utilisée pour simplifier l’accès aux données en permettant aux utilisateurs de récupérer, manipuler et interroger les données de manière plus conviviale, sans avoir besoin de connaître la structure interne des tables sous-jacentes. Les vues offrent plusieurs avantages, notamment la sécurité, la simplification des requêtes et la réduction de la complexité pour les utilisateurs.
Voici le rôle principal des vues dans une base de données Oracle :
- Simplification de l’accès aux données : Les vues permettent de regrouper des données de plusieurs tables en une seule entité logique. Cela facilite l’accès aux informations et rend les requêtes plus simples à écrire et à comprendre.
- Sécurité : Les vues peuvent être utilisées pour limiter l’accès aux données sensibles en ne permettant aux utilisateurs d’accéder qu’aux données autorisées. Par exemple, vous pouvez créer une vue qui masque certaines colonnes ou lignes de données confidentielles.
- Abstraction de la structure sous-jacente : Les vues masquent la complexité de la structure sous-jacente de la base de données, ce qui permet aux développeurs d’applications de travailler avec des données de manière plus intuitive, sans avoir besoin de connaître les détails de la base de données.
- Réutilisation : Vous pouvez créer des vues réutilisables pour simplifier les opérations courantes. Par exemple, une vue peut être créée pour afficher uniquement les données des employés actifs, que vous pouvez utiliser dans de nombreuses requêtes sans avoir à spécifier les critères de sélection à chaque fois.
Syntaxe
CREATE [OR REPLACE] VIEW view_name [(column_aliases)] AS
defining-query
[WITH READ ONLY]
[WITH CHECK OPTION]
Exemple d’utilisation d’une vue
Supposons que vous ayez une base de données contenant deux tables : une table « Employés » et une table « Départements ».
Table « Employes » :
CREATE TABLE Employes (
ID NUMBER(5) PRIMARY KEY,
Nom VARCHAR2(50),
Prenom VARCHAR2(50),
Salaire NUMBER(10, 2),
IDDepartement NUMBER(5),
DateEmbauche DATE
);
Dans cette table, nous avons les colonnes suivantes :
- « ID » : Une clé primaire numérique unique pour chaque employé.
- « Nom » : Le nom de l’employé.
- « Prenom » : Le prénom de l’employé.
- « Salaire » : Le salaire de l’employé avec deux décimales.
- « IDDepartement » : Une référence vers le département auquel l’employé appartient.
- « DateEmbauche » : La date à laquelle l’employé a été embauché.
Table « Departements » :
CREATE TABLE Departements (
ID NUMBER(5) PRIMARY KEY,
Nom VARCHAR2(50),
Localisation VARCHAR2(50)
);
Dans la table « Departements », nous avons les colonnes suivantes :
- « ID » : Une clé primaire numérique unique pour chaque département.
- « Nom » : Le nom du département.
- « Localisation » : La localisation ou l’emplacement du département.
Ces deux tables sont liées par la colonne « IDDepartement » dans la table « Employes », qui fait référence à la clé primaire « ID » dans la table « Departements ». Cette relation permet de relier chaque employé à un département spécifique dans la base de données.
Vous pouvez créer une vue appelée « VueEmployés » qui regroupe des informations sur les employés et leurs départements respectifs.
Voici comment utiliser cette vue :
-- Créer la vue VueEmployés CREATE VIEW VueEmployes AS SELECT E.Nom, E.Prenom, E.Salaire, D.Nom AS Departement FROM Employes E INNER JOIN Departements D ON E.IDDepartement = D.ID; -- Sélectionner des données à partir de la vue SELECT * FROM VueEmployes; -- Sélectionner uniquement les employés d'un département spécifique SELECT * FROM VueEmployes WHERE Departement = 'Ventes'; -- Mettre à jour les informations sur les employés via la vue UPDATE VueEmployes SET Salaire = Salaire * 1.10 WHERE Departement = 'Ventes';
Dans cet exemple, la vue « VueEmployes » regroupe les données des employés et des départements, ce qui permet aux utilisateurs de récupérer facilement des informations sur les employés et de mettre à jour leur salaire, le tout sans avoir à accéder directement aux tables sous-jacentes « Employes » et « Departements« .
Expérimentation avec la base de données démo
NOTE: À partir de cette étape, la base de données DEMO doit être installée.
-- Avec Alias
CREATE OR REPLACE VIEW employee_yos AS
SELECT
employee_id,
first_name || ' ' || last_name full_name,
FLOOR( months_between( CURRENT_DATE, hire_date )/ 12 ) yos,
EMAIL courriel
FROM
employees;
-- Sans Alias
CREATE VIEW employee_yos (employee_id, full_name, yos) AS
SELECT
employee_id,
first_name || ' ' || last_name,
FLOOR( months_between( CURRENT_DATE, hire_date )/ 12 )
FROM
employees;
Utilisation de la vue
column full_name format a30
SELECT
*
FROM
employee_yos
WHERE
yos > 6
ORDER BY
full_name;
Création d’une vue en lecture seule
CREATE OR REPLACE VIEW customer_credits(
customer_id,
name,
credit
) AS
SELECT
customer_id,
name,
credit_limit
FROM
customers WITH READ ONLY;
Exemple d’un vue avec une jointure
Nous allons ici créer une vue qui lit trois tables ensembles: orders, order_items, and products. Elle permettra d’obtenir les commandes qui sont en attentes de traitement.
--- Création d'une vue à partir de trois tables.
CREATE OR REPLACE VIEW backlogs AS
SELECT
product_name,
EXTRACT(
YEAR
FROM
order_date
) YEAR,
SUM( quantity * unit_price ) amount
FROM
orders
INNER JOIN order_items
USING(order_id)
INNER JOIN products
USING(product_id)
WHERE
status = 'Pending'
GROUP BY
EXTRACT(
YEAR
FROM
order_date
),
product_name;
---
select TRIM(product_name) as Nom, amount Montant from backlogs where amount > 200000;
DROP VIEW
Updatable View
JSON-Relational Duality Views
La version 23c d’Oracle introduit un nouveau type de vue qui permet d’utiliser le format JSON pour le traitement des données. Il sera possible soit d’obtenir les enregistrements dans ce format ou d’utiliser JSON pour leur création ou leur mise à jour.
Il est possible d’utiliser deux syntaxes pour représenter la structure de données dans la vue:
1 – JSON, par exemple:
--- Représentation de la table COUNTRIES en utilisant JSON:
{
'idPays' : COUNTRY_ID,
'pNom' : COUNTRY_NAME,
'idRegion' : REGION_ID
}
2 – GraphQL, par exemple
--- Représentation de la table COUNTRIES en utilisant GraphQL:
{
idPays : COUNTRY_ID
pNom : COUNTRY_NAME
idRegion : REGION_ID
};
NOTE: Étant donné que GraphQL propose une syntaxe moins rigide, c’est ce que nous utiliserons dans les exemples suivants.
Vue JS – Exemple GraphQL
create or replace json relational duality view pays_dv as
countries @insert @update @delete
{
idPays : COUNTRY_ID
pNom : COUNTRY_NAME
idRegion : REGION_ID
};
--- Exemple d'utilisation
select * from pays_dv fetch first 5 rows only;
select * from pays_dv fetch first 5 rows only;
--- Résultat:
{"idPays":"AR","pNom":"Argentina","idRegion":2,"_metadata":{"etag":"27952F8C7AB11845D42FCFA0440B9B69","asof":"0000000000A628E2"}}
-- Avec json_serialize(d.data pretty)
select json_serialize(d.data pretty)from pays_dv d;
--- Résultat:
"{
"_metadata" :
{
"etag" : "27952F8C7AB11845D42FCFA0440B9B69",
"asof" : "0000000000A6292F"
},
"idPays" : "AR",
"pNom" : "Argentina",
"idRegion" : 2
}"
NOTE: Voir la section 3.2 de ce document pour comprendre le rôle du champ ‘etag’. Le ‘asof’ est le numéro de la dernière modification.
Utilisation de la fonction json_serialize(d.data pretty), pour représenter la structure JSON sur plusieurs ligne:
-- Sélectionner les champs à retourner:
-- Exemple 01
SELECT dv.DATA.pNom FROM pays_dv dv
SELECT dv.DATA.pNom FROM pays_dv dv where dv.data.idPays LIKE 'C%';
-- Exemple 02
SELECT json_transform(data, KEEP '$.pNom', '$.idRegion') FROM pays_dv;
SELECT json_serialize(json_transform(data, KEEP '$.pNom', '$.idRegion') PRETTY) FROM pays_dv;
-- Résultat:
"{
"pNom" : "Argentina",
"idRegion" : 2
}"
"{
"pNom" : "Australia",
"idRegion" : 3
}"
...
Vue JS – Exemple JSON
create or replace json relational duality view pays_dv2 as
select json {
'idPays' : c.COUNTRY_ID,
'pNom' : c.COUNTRY_NAME,
'idRegion' : c.REGION_ID
}
from COUNTRIES c with insert update delete;
--- Utilisation
Insertion avec une Duality View
INSERT INTO pays_dv VALUES ('
{ idPays : "QC",
pNom : "Québec",
idRegion : 2
}
');
Mise à jour d’un enregistrement
UPDATE pays_dv dv SET data = json_transform(data, SET '$.pNom' = 'Quebec n''est pas vraiment un pays') WHERE dv.data.idPays = 'QC'; COMMIT; SELECT json_serialize(data PRETTY) FROM pays_dv WHERE json_value(data, '$.pNom') LIKE 'Qu%';
Expérimentation avec la BD d’Oracle
Construire le schéma
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET LONG 20000
prompt
prompt ** Working with JSON Relational Duality Views using SQL **
prompt
-- Do cleanup for previous run (if any).
--
drop view if exists team_dv;
drop view if exists race_dv;
drop view if exists driver_dv;
drop table if exists driver_race_map;
drop table if exists race;
drop table if exists driver;
drop table if exists team;
--------------------------------------------------
-- Step 1: Create JSON Relational Duality Views --
--------------------------------------------------
-- Create base tables for the duality views.
--
CREATE TABLE team
(team_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
points INTEGER NOT NULL,
CONSTRAINT team_pk PRIMARY KEY(team_id));
CREATE TABLE driver
(driver_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
points INTEGER NOT NULL,
team_id INTEGER,
CONSTRAINT driver_pk PRIMARY KEY(driver_id),
CONSTRAINT driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id));
CREATE TABLE race
(race_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
laps INTEGER NOT NULL,
race_date DATE,
podium JSON,
CONSTRAINT race_pk PRIMARY KEY(race_id));
CREATE TABLE driver_race_map
(driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
race_id INTEGER NOT NULL,
driver_id INTEGER NOT NULL,
position INTEGER,
CONSTRAINT driver_race_map_uk UNIQUE (race_id, driver_id),
CONSTRAINT driver_race_map_pk PRIMARY KEY(driver_race_map_id),
CONSTRAINT driver_race_map_fk1 FOREIGN KEY(race_id) REFERENCES race(race_id),
CONSTRAINT driver_race_map_fk2 FOREIGN KEY(driver_id) REFERENCES driver(driver_id));
-- Create a trigger on the driver_race_map table to populate
-- the points fields in team and driver based on race results.
--
--- NOTE: Les déclencheurs seront couverts plus tard.
CREATE OR REPLACE TRIGGER driver_race_map_trigger
BEFORE INSERT ON driver_race_map
FOR EACH ROW
DECLARE
v_points INTEGER;
v_team_id INTEGER;
BEGIN
SELECT team_id INTO v_team_id FROM driver WHERE driver_id = :NEW.driver_id;
IF :NEW.position = 1 THEN
v_points := 25;
ELSIF :NEW.position = 2 THEN
v_points := 18;
ELSIF :NEW.position = 3 THEN
v_points := 15;
ELSIF :NEW.position = 4 THEN
v_points := 12;
ELSIF :NEW.position = 5 THEN
v_points := 10;
ELSIF :NEW.position = 6 THEN
v_points := 8;
ELSIF :NEW.position = 7 THEN
v_points := 6;
ELSIF :NEW.position = 8 THEN
v_points := 4;
ELSIF :NEW.position = 9 THEN
v_points := 2;
ELSIF :NEW.position = 10 THEN
v_points := 1;
ELSE
v_points := 0;
END IF;
UPDATE driver SET points = points + v_points
WHERE driver_id = :NEW.driver_id;
UPDATE team SET points = points + v_points
WHERE team_id = v_team_id;
END;
/
NOTE: Les déclencheurs seront couverts plus tard.
https://oracle-base.com/articles/23c/json-relational-duality-views-23c