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.
Percentual da coluna preenchida
Resolvi galera. Resolvi assim:
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;
-
- Informação
-
Quem está online
Usuários navegando neste fórum: Nenhum usuário registrado e 10 visitantes