`
IT独行者
  • 浏览: 12509 次
  • 性别: Icon_minigender_1
社区版块
存档分类
最新评论

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

阅读更多

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

思路:

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

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

 

1
2
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://www.uncletoo.com/html/oracle/660.html

分享到:
评论

相关推荐

    delphi中调用oracle的存储过程.pdf

    在 Delphi 中调用 Oracle 存储过程是数据库应用程序开发中的常见操作,特别是在报表解决方案中。本篇将详细介绍如何在 Delphi 中利用 ADODataSet 调用 Oracle 的存储过程,涉及分带返回游标和不返回值两种情况。 ...

    delphi中调用oracle的存储过程.docx

    在 Delphi 中调用 Oracle 存储过程是数据库应用程序开发中的常见操作,特别是在报表解决方案中。本篇将详细介绍如何在 Delphi 中利用 ADODataSet 调用 Oracle 存储过程,尤其是涉及到带有返回游标的情况。 首先,...

    Oracle 查询存储过程做横向报表的方法

    在本场景中,我们需要利用Oracle的存储过程来生成这样的报表。下面将详细解释如何使用存储过程实现横向报表的生成。 首先,我们看到创建了一个名为`CHEN_TEST_PACKGE`的包,它包含一个名为`p_list_bulletin_report`...

    Oracle存储过程

    Oracle存储过程是数据库管理...总之,Oracle存储过程是数据库开发中的强大工具,通过合理利用,可以提升应用的性能、安全性和可维护性。了解并熟练掌握存储过程的创建、调用和优化,将对提升数据库管理技能大有裨益。

    精通Oracle核心技术和项目实战之存储过程.pptx

    存储过程分为过程和函数,过程无返回值,而函数则能返回结果给调用者。在首次执行时,存储过程会被编译并存储在高速缓存中,后续调用可快速执行。 14.1.1 认识存储过程 Oracle的存储过程用于集中的数据库操作,创建...

    应用PL/SQL存储过程实现动态报表.pdf

    通过存储过程,可以封装逻辑处理过程,优化数据库操作,并且还可以进行参数传递和返回结果,是实现动态报表的重要手段。 5. PL/SQL块结构: PL/SQL块由三个基本部分组成:声明部分(DECLARE)、执行部分(BEGIN......

    易语言连接ORACLE数据库查询报表

    6. **生成报表**:在易语言中,你可以利用内置的窗口部件或自定义控件显示查询结果,构建报表。这可能包括创建表格、图表,或者通过“文本输出”命令打印数据。 7. **关闭连接**:完成所有操作后,记得使用“关闭...

    Oracle EBS 开发笔记

    ### Oracle EBS 开发基础知识概览 #### 一、Oracle EBS 入门与概述 - **Oracle EBS (Enterprise Business Suite)**:是Oracle公司提供的一套完整的业务管理解决方案,适用于企业的财务管理、供应链管理、人力资源...

    Oracle 程序员开发指南

    - 函数类似于存储过程,但通常返回一个值。 ##### 3. Oracle Workspace Manager 的安装与配置 - **安装:** - 确保Oracle数据库环境已正确安装。 - 使用适当的命令或工具安装Workspace Manager 组件。 - **配置...

    Oracle-ERP开发笔记

    ### Oracle-ERP开发笔记知识点概览 #### 一、Oracle Forms基础 **1. 设置ITEM为必填项** - 在Oracle Forms中,可以为特定的ITEM(字段)设置必填属性,确保用户在提交表单之前必须填写这些字段。这通常通过属性...

    ORACLE PL/SQL命令集

    Oracle SQL Developer 是一款免费的集成开发环境,支持PL/SQL的编写、调试和测试,还有许多其他功能,如数据库连接、数据模型设计和报表生成。 9. **学习资源** "ORACLE命令集.pdf"这个文档很可能是对PL/SQL命令...

    java报表API源代码(程序数据集、自定义函数和导出API)

    总之,通过理解和利用这些开源的Java报表API源代码,开发者不仅可以增强对FineReport的理解,还能更高效地进行二次开发,满足各种复杂和个性化的报表需求。如果有需要报表调用的相关代码,可以单独下载以供参考。

    ORACLE EBS 开发基础

    ### ORACLE EBS 开发基础知识点详解 #### 一、Oracle EBS简介 Oracle E-Business Suite (EBS) 是一套全面的企业管理解决方案,涵盖了财务管理、供应链管理、项目管理、人力资源管理和客户服务等多个方面。对于EBS...

    oracle 分析函数学习笔记

    这些函数在处理大数据集时尤其有用,可以高效地完成复杂的统计任务,而无需编写额外的存储过程或应用程序逻辑。 总的来说,熟悉和掌握Oracle的分析函数对于开发人员来说非常重要,特别是在开发需要高效数据分析的...

    数据库元数据资料,关于oracle的

    数据库元数据是关于数据库结构和内容的信息,包括但不限于表、列、索引、视图、存储过程等的详细描述。在Oracle数据库中,这些信息存储在数据字典中,可以通过数据库元数据API来访问和查询。 在Java编程中,我们...

    润乾报表V4.0高级教程

    在润乾报表V4.0高级教程中,我们将会深入探讨该软件的各种高级特性和应用,帮助用户充分利用其功能来提升数据处理与分析能力。 一、润乾报表V4.0核心特性 1. **灵活的数据源接入**:润乾报表支持多种数据源,包括...

    ssh和jasperreports加oracle结合小例子

    总结来说,"ssh和jasperreports加oracle结合小例子"这个项目展示了如何利用SSH框架的灵活性和JasperReports的报表能力,结合Oracle数据库,实现数据的动态查询、处理和展示。这样的组合在实际的Web应用开发中非常...

    oracle实验报告

    实验3 Oracle数据库开发环境下PL/SQL编程 【实验目的】 (1)掌握 PL/SQL 的基本使用方法。 (2)在SQL*PLUS环境下运行PL/SQL的简单程序。 (3)应用 PL/SQL 解决实际问题 【实验内容与步骤】 PL/SQL块中的可执行部分...

    oracle分析函数.doc

    - PL/SQL是Oracle数据库的编程语言,与分析函数紧密相连,用于编写存储过程、触发器等数据库逻辑。 - 在PLSQL中使用分析函数可以增强业务逻辑的复杂性,同时保持代码的简洁性和可维护性。 总之,Oracle分析函数是...

    jdbc(oracle,mysql)

    3. **Statement**:用于执行静态SQL语句,返回结果集。 4. **PreparedStatement**:预编译的SQL语句,可以提高执行效率,防止SQL注入攻击。 5. **CallableStatement**:用于调用存储过程。 在使用JDBC时,要注意...

Global site tag (gtag.js) - Google Analytics