Percentual da coluna preenchida

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
Responder
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Senhores, estou querendo pegar o percentual utilizado na coluna.

ex: tenho uma coluna do tipo NUMBER(6) e quero saber quanto porcento essa coluna já foi preenchida.

Estou fazendo um trabalho de prevenção. Não quero deixar que estoure o tamanho de 6 bytes, como já aconteceu aqui uma vez.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Resolvi galera. Resolvi assim:

Selecionar tudo

set serveroutput on
declare

  cursor c_1 is

      select TABLE_NAME,COLUMN_NAME,DATA_PRECISION,AVG_COL_LEN
      from all_tab_columns
      where owner='FLXODS'
      AND COLUMN_NAME LIKE '%_KEY_NR'
      AND DATA_PRECISION IS NOT NULL
      ORDER BY TABLE_NAME;

   v_sql    varchar2(32000);
   v_max    number(20);
   v_conta  number(20);
   v_precision number (38);

begin
   dbms_output.put_line('..............ESTATÍSTICAS..............');
   dbms_output.put_line('TABLE_NAME                     '||'  COLUMN_NAME                        '||'  DATA_PRECISION  ');   
   dbms_output.put_line('----------                     '||'  -----------                        '||'  --------------  ');      
   DBMS_OUTPUT.PUT_LINE(chr(09));
   for rec in c_1 loop  
     if rec.DATA_PRECISION = 1 then
        v_precision := 9;
     elsif rec.DATA_PRECISION = 2 then
        v_precision := 99;
     elsif rec.DATA_PRECISION = 3 then
        v_precision := 999;
     elsif rec.DATA_PRECISION = 4 then
        v_precision := 9999;
     elsif rec.DATA_PRECISION = 5 then
        v_precision := 99999;
     elsif rec.DATA_PRECISION = 6 then
        v_precision := 999999;
     elsif rec.DATA_PRECISION = 7 then
        v_precision := 9999999;
     elsif rec.DATA_PRECISION = 8 then
        v_precision := 99999999;
     elsif rec.DATA_PRECISION = 9 then
        v_precision := 999999999;
     elsif rec.DATA_PRECISION = 10 then
        v_precision := 9999999999;
     elsif rec.DATA_PRECISION = 11 then
        v_precision := 99999999999;
     elsif rec.DATA_PRECISION = 12 then
        v_precision := 999999999999;
     elsif rec.DATA_PRECISION = 13 then
        v_precision := 9999999999999;
     elsif rec.DATA_PRECISION = 14 then
        v_precision := 99999999999999;
     elsif rec.DATA_PRECISION = 15 then
        v_precision := 999999999999999;
     elsif rec.DATA_PRECISION = 16 then
        v_precision := 9999999999999999;
     elsif rec.DATA_PRECISION = 17 then
        v_precision := 99999999999999999;
     elsif rec.DATA_PRECISION = 18 then
        v_precision := 999999999999999999;
     elsif rec.DATA_PRECISION = 19 then
        v_precision := 9999999999999999999;
     elsif rec.DATA_PRECISION = 20 then
        v_precision := 99999999999999999999;
     elsif rec.DATA_PRECISION = 21 then
        v_precision := 999999999999999999999;
     elsif rec.DATA_PRECISION = 22 then
        v_precision := 9999999999999999999999;
     elsif rec.DATA_PRECISION = 23 then
        v_precision := 99999999999999999999999;
     elsif rec.DATA_PRECISION = 24 then
        v_precision := 999999999999999999999999;
     elsif rec.DATA_PRECISION = 25 then
        v_precision := 9999999999999999999999999;
     elsif rec.DATA_PRECISION = 26 then
        v_precision := 99999999999999999999999999;
     elsif rec.DATA_PRECISION = 27 then
        v_precision := 999999999999999999999999999;
     elsif rec.DATA_PRECISION = 28 then
        v_precision := 9999999999999999999999999999;
     elsif rec.DATA_PRECISION = 29 then
        v_precision := 99999999999999999999999999999;
     elsif rec.DATA_PRECISION = 30 then
        v_precision := 999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 31 then
        v_precision := 9999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 32 then
        v_precision := 99999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 33 then
        v_precision := 999999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 34 then
        v_precision := 9999999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 35 then
        v_precision := 99999999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 36 then
        v_precision := 999999999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 37 then
        v_precision := 9999999999999999999999999999999999999;
     elsif rec.DATA_PRECISION = 38 then
        v_precision := 99999999999999999999999999999999999999;        
     end if;
        
        
      v_sql := 'select max('||rec.COLUMN_NAME||') from flxods.'||rec.TABLE_NAME;
      execute immediate v_sql into v_max;      
      v_conta := round(((v_max / v_precision) * 100));

      dbms_output.put_line(rec.TABLE_NAME||'   '||rec.COLUMN_NAME||'                                '||NVL(v_conta,0)||'%');   
      null;
   end loop;
   DBMS_OUTPUT.PUT_LINE(chr(09));
   dbms_output.put_line('........................................');
   DBMS_OUTPUT.PUT_LINE(chr(09));
   
   
end;
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 10 visitantes