Langage SQL et utilisation des vues, progression de requêtes

RCI Informatique
Jean-Pierre LOISON
Mars 2009


1 - Introduction

Le langage SQL a été conçu comme un moyen d'interrogation de données, dans un langage "proche du langage naturel" (en anglais).

En quelques heures, des utilisateurs non informaticiens peuvent maîtriser la dizaine des mots principaux du langage en ce qui concerne les interrogations :  Select,  from,  where,  group by,  having, ....

La complexité de la formulation de requêtes n'est pas liée au langage SQL mais aux structures de données, qui sont en général complexes, et souvent peu documentées.

Les logiciels d' infocentre, également appelés outils de "Business Intelligence", sont basés sur des mécanismes de "simplification et clarification des données".

Or, existe, en standard et sans coût supplémentaire, dans les principales bases de données, le "mécanisme des VUES" qui est parfois méconnu ou peu utilisé.

L'objectif de cet article est de présenter comment l'utilisation des VUES permet de décomposer une question complexe en une suite de questions simples.


2 - Avantages des vues

- Masquer certaines colonnes ou certaines lignes, non utiles
- Masquer certaines colonnes ou certaines lignes, pour des raisons de confidentialité
- Décomposer une requête SQL complexe en une suite de requêtes SQL simples
- Masquer les "jointures" entre tables, en présentant des données "à plat"
- Renommer les tables et colonnes de tables, avec des noms plus simples.
- Renommer  les tables et colonnes de tables, avec des noms dans la langue de l'utilisateur.
- Faciliter la documentation des requêtes, en permettant de comprendre les différentes "étapes intermédiaires"
- En finir avec les requêtes SQL de plusieurs pages, en remplaçant une "grande" requête, complexe et ingérable, par plusieurs requêtes "raisonnables", compréhensibles.
- En ce qui concerne Oracle, l'utilisation conjointe de vues et des "états interactifs" d' Oracle Application Express (APEX), permet de proposer aux utilisateurs une solution simple, ergonomique et performante d'accès aux données. Et ce, sans coûts supplémentaires de licences d'utilisation.
 

3 - Exemple de progression de requêtes, basé sur des vues successives

Pour présenter une progression concrète, témoignant des différents avantages listés ci-desuus, voici un exemple basé sur les tables EMP et DEPT d'Oracle.

Table EMP

Table DEPT

Masquage de jointure, définition de la vue Emp1

On veut accéder aux données des tables EMP et DEPT, sans se préoccuper de la "jointure" entre ces deux tables :

Sélection de quelques informations, définition de la vue Emp2

La vue EMP1 comporte un maximum d'informations, dont celles de salaire et des codes (mgr, deptno), ainsi que les dates de recrutement.
On souhaite masquer ces informations :

Sélection de quelques informations, obtention des noms "en clair" des managers, définition de la vue Emp3

La vue EMP1 comporte un maximum d'informations, en particulier les codes "manager" correspondant aux responsables de chacun des employés.
Mais c'est un code, or on souhaite avoir les noms "en clair" des diférents managers :

Totaux de salaires, par département, définition de la vue Emp4

On souhaite cumuler les salaires par département. On "repart directement" de la table Emp.
On obtient des cumuls de salaires par n°s de départements :

Employés classés par salaires décroissants , définition de la vue Emp5

On s'appuie sur la vue emp3 en sélectionnant une partie des colonnes :

Les six plus "hauts" salaires , définition de la vue Emp6

Le mécanisme des vues Oracle, dans la version 10,  permet de mémoriser également les critères de tri.
On s'appuie sur la vue emp5, qui comporte l'ensemble des employés, en ne sélectionnant que les six premières lignes :

Les six plus "hauts" salaires, mais sans que les salaires ne soient affichés, définition de la vue Emp7

On s'appuie sur la vue emp6, en affichant toutes les rubriques, sauf celle du salaire :

Totaux de salaires, par département, avec affichage des noms de départements, définition de la vue Dept1

On effectue une jointure entre la vue Emp4 et la table des départements,
avec la clause de jointure semi-exhaustive (+) qui permet d'afficher également les départements n'ayant aucun total de salaires :

Totaux de salaires, par localisation des départements, définition de la vue Dept2

On utilise la vue Emp3, avec l'opérateur "group by":

4 - Définitions des différentes vues ci-dessus

Sous forme d'un script SQL :

CREATE OR REPLACE FORCE VIEW EMP1 (EMPNO, ENAME, JOB, DNAME, LOC, MGR, HIREDATE, SAL, DEPTNO) AS
select empno, ename, job, dname, loc, mgr, hiredate, sal, EMP.deptno
from EMP, DEPT
where EMP.DeptNo = DEPT.DeptNo
/
CREATE OR REPLACE FORCE VIEW EMP2 (EMPNO, NL, C1, C2, C3, C4, C5, C6) AS
select empno, 1 as nL, ename as c1, job as c2, mgr as c3, to_char(hiredate,'DD/MM/YYYY') as c4, to_char(sal) as c5, to_char(comm) as c6 from emp1
union
select empno, 2 as nL, null as c1, null as c2, null as c3, dname as c4, loc as c5, to_char(deptno) as c6 from emp1
/
CREATE OR REPLACE FORCE VIEW EMP3 (EMPNO, ENAME, JOB, DNAME, LOC, MGR, HIREDATE, SAL, DEPTNO, MGRNAME) AS
select A.empno, A.ename, A.job, A.dname, A.loc, A.mgr, A.hiredate, A.sal, A.deptno,
B.ename as mgrName
from emp1 A, emp B
where A.mgr = B.empno (+)
/
CREATE OR REPLACE FORCE VIEW EMP4 (DEPTNO, TOTSAL) AS
select deptno, sum(sal) as TotSal from EMP
group by deptno
/
CREATE OR REPLACE FORCE VIEW EMP5 (ENAME, JOB, DNAME, LOC, SAL) AS
select ename, job, dname, loc, sal
from emp3
order by sal desc
/
CREATE OR REPLACE FORCE VIEW EMP6 (ENAME, JOB, DNAME, LOC, SAL) AS
select ENAME,JOB,DNAME,LOC,SAL from emp5
where rownum <= 6
/
CREATE OR REPLACE FORCE VIEW EMP7 (ENAME, JOB, DNAME, LOC) AS
select ename, job, dname, loc
from emp6
/
CREATE OR REPLACE FORCE VIEW DEPT1 (DEPTNO, DNAME, TOTSAL) AS
select dept.deptNo, dName, TotSal
from dept, emp4
where dept.deptNo = emp4.deptNo (+)
/
CREATE OR REPLACE FORCE VIEW DEPT2 (LOC, TOTSAL) AS
select loc, sum(sal) as TotSal
from emp3
group by Loc
/

 

5 - Utilisation d'APEX (Oracle Application Express) pour présenter de façon arborescente les dépendances entre vues

Toutes les images, les résultats de requêtes et les arborescences ci-dessus ont été réalisés, de façon simple, avec APEX.

La gestion des requêtes, et la génération (sous forme de documents PDF) ont été effectués avec le développement APEX, nommé RciTools HTML

Voici le source PL/SQL  de la procédure de génération de l'arborescence des vues.
On utilise le dictionnaire Oracle, qui fournit les dépendances entre vues et tables.

create or replace PROCEDURE "CALCUL_ORDRE_VUES" (xView in varchar2, xOwner in varchar2)is
  w varchar2(32000);
  nSession number := nv ('APP_SESSION');
  nb integer := 0;

  nFeuille integer := 0;
  Cursor C is select tv_name from temp_vues where tv_session = nSession order by tv_order;

  Function GetLink (xVueCible in varchar2) return varchar2 is
  BEGIN
    return 'f?p=' || nv('APP_ID') || ':1:' || v('APP_SESSION') || '::NO::P1_VUE:' || xVueCible || ':';
  END ;

  Function EstVue (xVueTable in varchar2) return boolean is
    Cursor C is select name as d from temp_dependencies
      where referenced_name = xVueTable and referenced_owner = xOwner and referenced_type = 'VIEW';
  begin
    for m in C loop return true; end loop;
    return false;
  end;

  Function getComments (xVue in varchar2) return varchar2 is
    Cursor C is select comments from all_tab_comments
      where owner = xOwner and table_name = xVue;
  begin
    for m in C loop
      return substr (m.comments,1,100);
    end loop;
    return '';
  end; -- getComments

  Procedure Aj_Feuille (xVue in varchar2, xPere in integer) is
    NumeroCetteFeuille integer;
    wName varchar2 (255);
    wLink varchar2 (255) := getLink (xVue);

    Cursor C_Vue_utilise (xView in varchar2, xOwner in varchar2) is
      select referenced_owner, referenced_name
      from temp_dependencies where name = xView and owner = xowner
      order by 1,2;

  begin
    nb := nb + 1;
    if nb > 50 then return; end if;

    -- on ajoute une feuille
    nFeuille := nfeuille + 1;
    NumeroCetteFeuille := nFeuille;
    wName := xVue || '&nbsp;&nbsp;<font color="blue">' || getComments (xVue) || '</font>';
    if not EstVue (xVue) then
      wLink := null;
      wName := '<font color="red"><b>' || xVue || '</b></font>';
    end if;
    Insert into temp_vues (TV_Name,tv_session, tv_order, tv_filsde, tv_link) VALUES
      (wName, nSession, nFeuille, xPere, wLink);
    -- puis on examine si cette feuille a des "filles" 
    for m in C_Vue_utilise (xVue, xOwner) loop
      -- et on ajoutera à nouveau des feuilles pour les vues et tables trouvées
      Aj_Feuille (m.referenced_name, NumeroCetteFeuille);
    end loop;
  end;


BEGIN -- Calcul_ORDRE_VUES
  if xView is null then return; end if;
  if xView = '' then return; end if;
  for m in C loop
    if m.tv_name = v('P1_VUE') then return; end if;
    exit; -- loop
  end loop;
  delete from TEMP_VUES where tv_session = nSession;
  delete from temp_dependencies;
  insert into temp_dependencies select * from all_dependencies
    where type = 'VIEW' and owner = v('P1_SCHEMA');
  commit;
  nFeuille := nSession * 100000 + 1;

  -- Création de la feuille initiale
  Aj_feuille (xView, NULL);

  commit;
END;

Cet exemple, est l'un de ceux abordés dans les sessions de formation PL/SQL et  perfectionnement APEX  organisées par RCI Informatique

5 - Pour aller plus loin avec Oracle, APEX, PL/SQL et le développement Web...
 

BLOG de RCI Informatique sur Oracle XE et Application Express

Sélection de sites Internet sur Oracle Database XE

RciTools RPDF, bibliothèque de génération par programmation PL/SQL de documents PDF

RciTools RXLS, bibliothèque de génération par programmation PL/SQL de documents Microsoft Excel et Calc Open Office

Dossier technique Oracle Application Express (HTML DB-APEX)

Sélection de sites Internet réalisés avec Oracle Application Express

Sessions de formation à  Oracle Application Express (HTML DB-APEX)

Installation d'Oracle 11g et activation d'APEX (intégré en standard)