Oracle存储过程改造需求：将水平直方图改为垂直直方图

已实现一个打印水平直方图的Oracle存储过程，现在需要将其改造为打印垂直直方图，尝试将数据存入数组但未成功，附上水平版本代码，请求技术帮助。

水平直方图存储过程代码 #

CREATE OR REPLACE PROCEDURE print_histogram (p_owner varchar, p_table varchar, p_col varchar) IS
    max_occur_sql varchar(200):= 'select max(count(*)) from '|| p_owner ||'.'|| p_table ||' group by '|| p_col;
    row_cnt_sql varchar(200):= 'select count(count(*)) from '|| p_owner ||'.'|| p_table ||' group by ' || p_col;
    max_colLen_sql varchar(200):= 'select max(length('||p_col||')) from '||p_owner||'.'||p_table;
    col_infoTable_sql varchar(200):='select '|| p_col ||' ,count(*)  from '|| p_owner ||'.'|| p_table || ' where '|| p_col ||' is not null group by '|| p_col || ' order by '|| p_col;
    max_colLen number(10);
    max_occur number(10);
    row_cnt number(10);
    command varchar2(1000);
    type cur_ref  is ref CURSOR;
    col_name varchar(200);
    col_freq number(10);
    ijj integer := 1;
    c_temp_table cur_ref;
    few_tooMany_error EXCEPTION;
begin
    execute immediate max_colLen_sql INTO max_colLen;
    execute immediate max_occur_sql INTO max_occur;
    execute immediate row_cnt_sql INTO row_cnt;
    if row_cnt >= 100 or row_cnt <= 0 then
        raise few_tooMany_error;
        return;
    end if;
    open c_temp_table for col_infoTable_sql;
    loop
        fetch c_temp_table into col_name,col_freq;
        exit when c_temp_table%NOTFOUND;
        dbms_output.put( ijj || ':');
        ijj := ijj + 1;
        for i in 0..(col_freq / (max_occur/50)) loop
            dbms_output.put('*');
        end loop;
        DBMS_OUTPUT.NEW_LINE;
    end loop;
    close c_temp_table;
exception   
    when few_tooMany_error then
        dbms_output.put_line('Few or too many distinct values in column');
    when others then 
        dbms_output.put_line('Non-existing table or column');
end;
/
set serveroutput on
CALL print_histogram('nikovits','customers','cust_year_of_birth');

输出效果说明 #

• 当前水平直方图：每行对应一个列值，以数字序号开头，后面用星号表示该值的出现频率，星号越长频率越高。
• 期望垂直直方图：每列对应一个列值，星号从底部向上堆叠表示频率高度，底部显示列值，所有列的星号底部对齐。

以下是改造后的垂直直方图存储过程，核心通过PL/SQL集合存储数据，从最高频率行开始逐行打印，实现垂直堆叠效果：

CREATE OR REPLACE PROCEDURE print_vertical_histogram (p_owner varchar, p_table varchar, p_col varchar) IS
    max_occur_sql varchar(200):= 'select max(count(*)) from '|| p_owner ||'.'|| p_table ||' group by '|| p_col;
    row_cnt_sql varchar(200):= 'select count(count(*)) from '|| p_owner ||'.'|| p_table ||' group by ' || p_col;
    max_colLen_sql varchar(200):= 'select max(length('||p_col||')) from '||p_owner||'.'||p_table;
    col_infoTable_sql varchar(200):='select '|| p_col ||' ,count(*)  from '|| p_owner ||'.'|| p_table || ' where '|| p_col ||' is not null group by '|| p_col || ' order by '|| p_col;
    
    -- 定义集合类型存储列名与频率数据
    type freq_rec is record(
        col_name varchar2(200),
        col_freq number(10),
        scaled_freq number(10) -- 缩放后的频率，适配50行高度
    );
    type freq_table is table of freq_rec;
    v_freq_data freq_table := freq_table();
    
    max_colLen number(10);
    max_occur number(10);
    row_cnt number(10);
    scale_factor number(10);
    c_temp_table sys_refcursor;
    few_tooMany_error EXCEPTION;
begin
    execute immediate max_colLen_sql INTO max_colLen;
    execute immediate max_occur_sql INTO max_occur;
    execute immediate row_cnt_sql INTO row_cnt;
    
    if row_cnt >= 100 or row_cnt <= 0 then
        raise few_tooMany_error;
        return;
    end if;
    
    -- 计算缩放因子，确保最高频率对应50行显示高度
    scale_factor := ceil(max_occur / 50);
    scale_factor := nvl(scale_factor, 1);
    
    -- 将查询结果存入集合
    open c_temp_table for col_infoTable_sql;
    loop
        v_freq_data.extend;
        fetch c_temp_table into v_freq_data(v_freq_data.last).col_name, v_freq_data(v_freq_data.last).col_freq;
        exit when c_temp_table%NOTFOUND;
        -- 计算缩放后的频率（向上取整）
        v_freq_data(v_freq_data.last).scaled_freq := ceil(v_freq_data(v_freq_data.last).col_freq / scale_factor);
    end loop;
    close c_temp_table;
    
    -- 从最高行到最低行逐行打印
    for i in reverse 1..50 loop
        for j in 1..v_freq_data.count loop
            -- 当前行高度大于等于列的缩放频率则打印星号，否则打印空格
            if v_freq_data(j).scaled_freq >= i then
                dbms_output.put('*  ');
            else
                dbms_output.put('   ');
            end if;
        end loop;
        dbms_output.new_line;
    end loop;
    
    -- 打印底部对齐的列名
    dbms_output.new_line;
    for j in 1..v_freq_data.count loop
        dbms_output.put(rpad(v_freq_data(j).col_name, max_colLen) || ' ');
    end loop;
    dbms_output.new_line;
    
exception   
    when few_tooMany_error then
        dbms_output.put_line('列中唯一值过少或过多');
    when others then 
        dbms_output.put_line('表或列不存在');
end;
/
set serveroutput on
CALL print_vertical_histogram('nikovits','customers','cust_year_of_birth');

关键改动说明 #

1. 集合存储数据：用自定义记录和表类型存储所有列值与频率，解决原数组存储失败的问题，方便后续逐行遍历判断。
2. 频率缩放：保留原逻辑的50行显示上限，通过缩放因子将原始频率转换为适配的显示行数。
3. 垂直打印逻辑：从最高行（第50行）到第1行遍历，逐列判断是否需要打印星号，实现星号从底部向上堆叠的垂直效果。
4. 底部对齐：用rpad函数统一列名长度，保证底部列名对齐，提升可读性。

内容的提问来源于stack exchange，提问作者imp