好好利用Oracle存储过程返回结果集开发报表

　　在Oracle开发过程中，经常会遇到各种形式的报表展示，有些报表用sql语句直接是写不出来的，需要程序与sql配合着实现，但是这样写出来的报表查询速度慢，而且不利于维护，这里给大家分享一下UncleToo经常用的报表开发方式。

　　思路：

　　利用存储过程，将需要的格式、字段以结果集的形式直接返回到页面，这样在程序里不需要关心报表需要哪些字段、如何排序等，只需要将报表条件传入写好的存储过程，将结果集展示就可以了，以后需要对报表格式、字段调整时，只需要调整Oracle存储过程，页面代码不需要更改。

　　此方法sql的整体结构其实很简单：

　　[sql] view plaincopy

v_sql="select * from A";

　　open ds for v_sql;

　　这里重点就是v_sql变量，将所有需要的逻辑关系写好，整体赋值给v_sql，最后打开输出游标(ds)即可。

　　下面是一个完整的Oracle存储过程返回结果集的例子，大家可以参考：

　　先看一下最终效果：

　　

　　这里同时也实现了动态列、层级展示的效果。

　　SQL代码：

　　[sql] view plaincopy

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;