为了账号安全,请及时绑定邮箱和手机立即绑定

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

标签:
Oracle


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

思路:

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

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

[sql]view plaincopy在CODE上查看代码片派生到我的代码片

v_sql="select * from A";  

open ds for v_sql;  

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

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

先看一下最终效果:

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实现了根据动态日期展示指标数据,很实用哦。如果大家有其他更好的办法可以一起讨论。

原文地址:http://blog.csdn.net/itqiao/article/details/16841773

©著作权归作者所有:来自51CTO博客作者IT独行者的原创作品,如需转载,请注明出处,否则将追究法律责任

Oracle程序开发数据库技术


点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消