Home > Database > Mysql Tutorial > body text

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

WBOY
Release: 2016-06-07 17:37:49
Original
1596 people have browsed it

在Oracle开发过程中,经常会遇到各种形式的报表展示,有些报表用sql语句直接是写不出来的,需要程序与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_diff0 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实现了根据动态日期展示指标数据,很实用哦。如果大家有其他更好的办法可以一起讨论。

    原文地址:

    本文出自 “IT独行者” 博客,,请务必保留此出处

    source:php.cn
    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Popular Tutorials
    More>
    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template