Oracle利用預存程序返回結果集開發報表

在Oracle開發過程中，經常會遇到各種形式的報表展示，有些報表用sql語句直接是寫不出來的，需要程式與sql配合著實現，但是這樣寫出來的報表查詢速度慢，而且不利於維護，這裡給大家分享一下UncleToo經常用的報表開發方式。

思路：

利用預存程序，將需要的格式、欄位以結果集的形式直接返回到頁面，這樣在程式裡不需要關心報表需要哪些欄位、如何排序等，只需要將報表條件傳入寫好的預存程序，將結果集展示就可以了，以後需要對報表格式、欄位調整時，只需要調整Oracle預存程序，頁面代碼不需要更改。

此方法sql的整體結構其實很簡單：

v_sql="select * from A";
open ds for v_sql;

這裡重點就是v_sql變數，將所有需要的邏輯關係寫好，整體賦值給v_sql，最後開啟輸出遊標（ds）即可。

下面是一個完整的Oracle預存程序返回結果集的例子，大家可以參考：

先看一下最終效果：

這裡同時也實現了動態列、層級展示的效果。

SQL代碼：

procedure pro_tj_getDate_normal_year(v_sDate varchar2,ds out ds_cur) as
    d_date date;
    d_eDate date;
    n_diff number :=0; --年份差
    n_item number :=0; --是否儲存有指標
    v_sql varchar2(10000);
    v_sql_t varchar2(5000);
    v_sql_where varchar2(5000);
    v_itemCode varchar2(100);--指標類別
    n_i number;
    v_fields varchar2(1000);
    v_year varchar(5);
  begin
    --計算年份差
    select substr(v_eDate,1,4) - substr(v_sDate,1,4)
    into n_diff
    from dual;
    --判斷目前使用者在所選公司是否儲存指標
    select count(*) into n_item from BP_J_TJZH_ITEM_LIST l
    where l.org_code=v_complany and l.work_code=v_user;
    if n_item>0 then
      v_sql_where := ' and i.item_code in(
                select l.item_code from BP_J_TJZH_ITEM_LIST l
                where l.org_code='''|| v_complany || ''' and l.work_code=''' || v_user || ''')';
    else
      v_sql_where := '';
    end if;
    --判斷指標類別是否為空白
    if v_type='' or v_type is null then
      v_itemCode := v_complany || '-0-000001';
    else
      v_itemCode := v_type;
    end if;
                                                                                                                                                         
    if n_diff<=0 then  --以截止接月份為準
      v_year := substr(v_eDate,1,4);
      v_sql := 'select i.item_code_prefix,
                lpad('' '',2 * level - 2) || i.item_name as item_name,
                u.unit_name,
                t.data_value "'||v_year||'" from
                bp_c_stat_item i
                left join bp_c_measure_unit u
                on i.unit_id=u.unit_id
                left join
                (
                select n.data_date,n.item_code_prefix,n.data_value,
                row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
                from bp_j_stat_ntz n
                where n.org_code='''||v_complany||'''
                and to_char(n.data_date,''yyyy-mm'') = '''||v_eDate||'''
                ) t
                on i.item_code_prefix=t.item_code_prefix and t.rn=1
                where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where || '
                start with i.item_code_prefix = '''||v_itemCode || '''
                connect by prior i.item_code_prefix=i.parent_item_code
                order SIBLINGS BY i.order_by asc';
    else --時間段查詢
      d_date := to_date(v_sDate,'yyyy-mm');
      d_eDate := to_date(v_eDate,'yyyy-mm');
      n_i:=1;
      v_fields := '';
      v_sql := '';
      while d_date<=d_eDate loop
        v_year := to_char(d_date,'yyyy');
        if EXTRACT(year FROM d_eDate) - EXTRACT(year FROM d_date)>0 then
          v_sql := v_sql||'
                  left join (select t.item_code_prefix,t.data_value from (
                  select n.data_date,n.item_code_prefix,n.data_value,
                  row_number() over(partition by to_char(n.data_date,''yyyy''),n.item_code_prefix order by n.data_date desc) rn
                  from bp_j_stat_ntz n
                  where n.org_code='''||v_complany||'''
                  and to_char(n.data_date,''yyyy'') = '''||to_char(d_date,'yyyy') ||'''
                  ) t where t.rn=1) t'||n_i||'
                  on i.item_code_prefix=t'||n_i||'.item_code_prefix
                  ';
        else
          v_sql := v_sql||'
                  left join (select t.item_code_prefix,t.data_value from (
                  select n.data_date,n.item_code_prefix,n.data_value,
                  row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
                  from bp_j_stat_ntz n
                  where n.org_code='''||v_complany||'''
                  and to_char(n.data_date,''yyyy-mm'') = '''||to_char(d_date,'yyyy-mm') ||'''
                  ) t where t.rn=1) t'||n_i||'
                  on i.item_code_prefix=t'||n_i||'.item_code_prefix
                  ';
        end if;
        v_fields := v_fields || 't' || n_i || '.data_value as "'||v_year||'",';
                                                                                                                                                             
        n_i := n_i+1;
        d_date :=add_months(d_date,12);
      end loop;
                                                                                                                                                           
      v_sql_t := 'select i.item_code_prefix,
                  lpad('' '',2 * level - 2) || i.item_name as item_name,
                  u.unit_name,
                  '||v_fields||'
                  null
                  from bp_c_stat_item i
                  left join bp_c_measure_unit u
                  on i.unit_id=u.unit_id
                  ';
                                                                                                                                                                       
      v_sql := v_sql_t || v_sql || '
                                  where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where ||
                                  'start with i.item_code_prefix = '''||v_itemCode || '''
                                  connect by prior i.item_code_prefix=i.parent_item_code
                                  order SIBLINGS BY i.order_by asc';
    end if;
                                                                                                                                                         
    --開啟遊標
    open ds for v_sql;
                                                                                                                                                         
  end pro_tj_getDate_normal_year;

這個sql實現了根據動態日期展示指標資料，很實用哦。如果大家有其他更好的辦法可以一起討論。

相關閱讀： Java中用JDBC調用Oracle預存程序和函數