Catégorie : SQL

Ce que je veux faire

Dans mon post précédent, j’ai créé une fonction qui me permet d’afficher sous forme de tableau à deux dimensions diverses informations tirées d’une table. Nommée pompeusement transposition, le requête permet entre autres d’afficher en ligne ou en colonne, le topn sql_id consommateurs sur un wait event et leur évolution sur un certain nombre de clichés.

Maintenant, je voudrais exploiter ces données dans du code PL/SQL, j’ai donc besoin de créer du code qui saura s’adapter au nombre de colonnes remontées. Un premier code permet d’afficher les valeurs

declare
  requete  clob;
  nb_cols  number;
  tab_cols t_varchar;
  auto_plsql clob;
  ttext    varchar2(32767);
begin
  p_trsp('sql_id'                               
       , 'snap_id'
       , 'dba_hist_sqlstat'
       , 'EXECUTIONS_DELTA'
       , 'sys'
       , '834'
       , '840'
       , 18
       , 'yes'
       , requete
       , nb_cols
       , tab_cols);
  auto_plsql :='begin 
for c in (';
  dbms_lob.append(auto_plsql,requete);
  ttext:=')
loop 
dbms_output.put(to_char(c.snap_id)||'' '');
';
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  for i in 1..nb_cols
  loop
    ttext:='dbms_output.put(to_char(c.c_'||trim(to_char(i))||',''9999'')||'' '');
'; 
    dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  end loop;
  ttext:='dbms_output.put_line('' '');
end loop;
end;'; 
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  execute immediate auto_plsql;
end;
/

Ce dui donne :

834     0  1803  1730  1430  1024     0   414     0     0   339   312   298   269     0   318   213   209   256  
835  2085  1493  1316   905   952   723   291   714     0   229   234   233   232   241   184   187   181   161   
836  2068  1490  1308   862   944   720   291   708   708   229   228   228   228   241   175   184   180   134   
837  1962  1466  1265   870   892   712   292     0     0   228   228   228   228   237   170   181   176   133   
838  1644  1490  1096   700   732   718   291     0     0   229   231   231   231   241   175   184   172   132   
839  1909  1494  1275  1064   864   721   328   648   648   229   235   233   231   241   193   184   178   154   
840  2028  1490  1288   858   924   723   291     0   693   230   231   231   231   241   175   184   180   133   

Et si on limite topn à 4 (donc 4 colonnes + snap id) on obtient :

834     0  1803  1730  1430
835  2085  1493  1316   905
836  2068  1490  1308   862
837  1962  1466  1265   870
838  1644  1490  1096   700
839  1909  1494  1275  1064
840  2028  1490  1288   858

Et si on va un poil plus loin pour traiter le résultat des colonnes dans du PL/SQL …

CREATE OR REPLACE
PACKAGE OJO_RPT
AS
PROCEDURE report(
    debut IN varchar2,
    fin   IN varchar2,
    topn  IN NUMBER);
END OJO_RPT;
/

CREATE OR REPLACE
PACKAGE body OJO_RPT
AS
-- private proc
type t_varchar is table of varchar2(1000);

procedure p_trsp   ( lig   in varchar2         -- Colonne qui sera utilisee en tete de ligne 
         , col    in varchar2         -- Colonne qui sera utilisee en tete de colonne
         , ltab   in varchar2         -- Table source 
         , pivot  in varchar2         -- Colonne qui sera utilisee pour remplir le tabeau
         , owner  in varchar2         -- Proprietaire de la table
         , mini   in varchar2         -- Valeur minimale pour col
         , maxi   in varchar2         -- Valeur maximale pour col
         , topn   in number           -- Nombre de lig affichees ordonnes par cumul de pivot descendant
         , trsp  in varchar2 := 'No'  -- Transposition de la table (lig en tete de colonne et col en tete de ligne)
                  , req   out clob
                  , ncol  out number
                  , t_ncl out t_varchar ) is
 v_col    varchar2(32767);  
 v_where  varchar2(32767);  -- clause where pour mini et maxi
 v_wtopn  varchar2(1000);   -- clause where pour topn
 v_owner  varchar2(255);    
 c1  sys_refcursor;    
 v_poreq  varchar2(32767);  
  i               number;
begin
        t_ncl:=t_varchar();
 -- Gestion de la variable topn, si null on prend tout
 if topn is null
 then
   v_wtopn:=' ';
 else
   v_wtopn:=' where rownum <= '||topn;
 end if;

 -- Gestion de la variable owner, si null on prend ls schema appelant 
 if owner is null
 then 
  select sys_context('USERENV','CURRENT_SCHEMA') into v_owner from sys.dual;
 else
  v_owner:=owner;
 end if;
 
 -- Gestion des variables mini et maxi, si null on prend tout
 if ( mini is not null or maxi is not null )
 then
  v_where:=' where ';
  if mini is not null 
  then
   v_where:=v_where||col||' >
= '||mini ;
   if maxi is not null
   then
       v_where:=v_where||' and '||col||' <= '||maxi ;
   end if;
  else
      v_where:=v_where||col||' <= '||maxi ;
  end if;
 end if;
 
    -- Si on ne transpose pas lig et col  
 if upper(trsp) = 'NO'
 then
   req:='select * from (Select '||lig||' c_0
, sum('||pivot||') total ';
          i:=1;
   open c1 for 'select distinct to_char('||col||') from '||v_owner||'.'||ltab||v_where||' order by 1' ;
   loop
    fetch c1 into v_col;
    exit when c1%notfound;
    v_poreq:='
   , sum(decode(to_char('||col||'),'''||v_col||''','||pivot||',0)) c_'||trim(to_char(i)) ;
    dbms_lob.writeappend(req,length(v_poreq),v_poreq);
                  t_ncl.extend();
                  t_ncl(i):=trim(to_char(v_col));
                  i:=i+1;
   end loop;
          ncol:=c1%rowcount;
          close c1;
   v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||lig||'
order by 2 desc)'||v_wtopn ;
   dbms_lob.writeappend(req,length(v_poreq),v_poreq);

    -- Si on transpose lig et col  
 else
   req:='Select '||col||' c_0';
          i:=1;
   open c1 for 'select ent from (select distinct to_char('||lig||') ent, sum('||pivot||') tot from '||v_owner||'.'||ltab||v_where||' group by to_char('||lig||') order by 2 desc)'||v_wtopn ;
   loop
     fetch c1 into v_col;
     exit when c1%notfound;
     v_poreq:='
   , sum(decode(to_char('||lig||'),'''||v_col||''','||pivot||',0)) c_'||trim(to_char(i)) ;
     dbms_lob.writeappend(req,length(v_poreq),v_poreq);
                          t_ncl.extend();
                          t_ncl(i):=trim(to_char(v_col));
                          i:=i+1;
   end loop;
          ncol:=c1%rowcount;
   close c1;
   v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||col||'
order by 1 asc';
   dbms_lob.writeappend(req,length(v_poreq),v_poreq);
 end if;        
end p_trsp;

procedure trt( t in t_varchar ) is
  vt varchar2(32767);
  c  sys_refcursor;
begin
  open c for 'select * from table(t)';
  loop
    fetch c into vt;
    exit when c%notfound;
    dbms_output.put_line(vt);
  end loop;
end trt;


-- public proc
PROCEDURE report(
    debut IN varchar2,
    fin   IN varchar2,
    topn  IN NUMBER) is
  requete  clob;
  nb_cols  number;
  tab_cols t_varchar;
  auto_plsql clob;
  ttext varchar2(32767);
begin
  for c0 in (select column_name from dba_tab_columns where table_name='DBA_HIST_SQLSTAT' and owner='SYS' and column_name like '%DELTA')
  loop
  p_trsp('sql_id'                               
       , 'snap_id'
       , 'dba_hist_sqlstat'
       , c0.column_name
       , 'sys'
       , debut
       , fin
       , topn
       , 'no'
       , requete
       , nb_cols
       , tab_cols);
  auto_plsql :='begin 
for c in (';
  dbms_lob.append(auto_plsql,requete);
  ttext:=')
loop 
dbms_output.put(to_char(c.c_0)||'' '');
';
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  for i in 1..nb_cols
  loop
    ttext:='dbms_output.put(to_char(c.c_'||trim(to_char(i))||',''99999999'')||'' '');
'; 
    dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  end loop;
  ttext:='dbms_output.put_line('' '');
end loop;
end;'; 
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  dbms_output.put_line(c0.column_name);
  execute immediate auto_plsql;
  end loop;
end report;

END OJO_RPT;
/

Le résultat de cette requête étant une succession de tableaux, dont je donne ici les trois premiers :

FETCHES_DELTA
cm5vu20fhtnq1      6549      5494      5490      5401      5490      5497      5490
8t43xdhf4d9x2         0      2085      2068      1962      1644      1909      2028
0k8522rmdzg4k      1976      1604      1600      1575      1600      1606      1600
089dbukv1aanh      1730      1316      1308      1265      1096      1275      1288
aykvshm7zsabd         0         0      2640      2618         0         0         0
5ms6rbzdnq16t         0       723       720       712       718       721       723
83taa7kaw59c1      3587         0         0         0         0         0         0
5kyb5bvnu3s04       414       291       291       292       291       328       291
7qqnad1j615m7         0       714       708         0         0       648         0
5hfunyv38vwfp         0         0       708         0         0       648       693
END_OF_FETCH_COUNT_DELTA
8t43xdhf4d9x2         0      2085      2068      1962      1644      1909      2028
cm5vu20fhtnq1      1803      1493      1490      1466      1490      1494      1490
089dbukv1aanh      1730      1316      1308      1265      1096      1275      1288
8vwv6hx92ymmm      1430       905       862       870       700      1064       858
6v7n0y2bq89n8      1024       952       944       892       732       864       924
5ms6rbzdnq16t         0       723       720       712       718       721       723
5kyb5bvnu3s04       414       291       291       292       291       328       291
7qqnad1j615m7         0       714       708         0         0       648         0
5hfunyv38vwfp         0         0       708         0         0       648       693
43c5ykm1mcp2a       312       234       228       228       231       235       231
SORTS_DELTA
5ms6rbzdnq16t         0       723       720       712       718       721       723
fsbqktj5vw6n9       213       187       184       181       184       184       184
4jrfrtx4u6zcx         0       122       122       128       122       150       122
aykvshm7zsabd         0         0       360       357         0         0         0
db78fxqxwxt7r       447         0         0         0         0         0         0
btwkwwx56w4z6         0        60        60        59        60        60        60
75u022kdra1fz         0        60        60        59        60        60        60
7ng34ruy5awxq       338         0         0         0         0         0         0
grb1cf30q5r7m       314         0         0         0         0         0         0
gvt8zu1k1tsff         0       122         0         0         0       150         0