↓ Skip to main content
Technology and Me

Oracle et les fonctions analytiques et autre fonctions à connaitre

·262 words·2 mins
Oracle
Table of Contents

Oracle et les fonctions analytiques et autre fonctions à connaitre
#

Les fonctions analytiques
#

Je me suis rendu compte qu’elles étaient assez peu connues (bien que très pratiques) et comme j’ai trouvé un très bon tutoriel sur les fonctions du style AVG, LAG et LEAD, CORR, MAX et MIN, RATIO_TO_REPORT, STDDEV et VARIANCE, NTILE, ROW_NUMBER, RANK, DENSE_RANK et COUNT, FIRST et LAST, FIRST_VALUE et LAST_VALUE, PERCENTILE_CONT et PERCENTILE_DISC et le partitionnement (PARTITION). Lisez avec attention :

http://lalystar.developpez.com/fonctionsAnalytiques/

La concaténation de chaine de caractères
#

Source
#

Avant la 10g
#

Le plus simple était de passer par une fonction.

en 10g : COLLECT
#

cela passe par trois étapes :

  • Création d’un type
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
  • Création d’une fonction
CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/
  • La requête
SELECT deptno,
       tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM   emp
GROUP BY deptno;

en 11g : LISTAGG
# 

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

Les tables virtuelles
#

http://www.oracle-developer.net/display.php?id=207

Transformer les colonnes en lignes ou le pivot
#

Le problème
#

https://forums.oracle.com/forums/thread.jspa?threadID=305252

En 10g
#

L’extension MODEL permet de s’en sortir. Voir http://technology.amis.nl/blog/300/pivoting-in-sql-using-the-10g-model-clause.

En 11g
#

Cette nouvelle version amène une nouvelle fonction de PIVOT.

Accès direct à un fichier CSV
#

http://oracle.developpez.com/guide/architecture/tables/?page=Chap1#L1.5.1