- 浏览: 1425657 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (409)
- Java (48)
- Spring (29)
- struts2 (17)
- hibernate (4)
- 设计模式 (24)
- jbpm (1)
- JavaScript (5)
- 统计报表 (7)
- ExtJS_3.0 (35)
- struts1 (1)
- 分析设计 (3)
- Flex3 (24)
- UML (2)
- 数据库 (18)
- PowerDesigner (2)
- 应用服务器 (3)
- WebService (5)
- ActiveMQ_5.3.2 (6)
- Java通信技术 (11)
- GWT (6)
- OSGi (15)
- android (11)
- liferay6.0.6 (13)
- jquery (13)
- Linux (3)
- java.util.concurrent (16)
- guava (9)
- 开发模式 (1)
- 大数据 (2)
- 互联网金融 (4)
- treegrid-3.0 (7)
- 分布式 (8)
- GO语言 (4)
- maven (1)
- 缓存技术 (6)
- 其他 (2)
- 前端页面 (1)
- heasy (1)
- spring cloud(F版) (21)
- springboot (12)
- springmvc (5)
- mybatis (3)
- dubbo (1)
- 物联网 (0)
最新评论
-
raymond.chen:
谢谢您的分享
使用Ngrok解决通过外网访问内网web应用 -
wangyudong:
速度有点慢,不过在也找到了一个开源的holer,配置一个key ...
使用Ngrok解决通过外网访问内网web应用 -
a1006458222:
...
Axis2的部署和应用 -
偷师来了:
不好意思 这样的博客我觉得就灭有必要分享出来了 命令大家都会看 ...
Consul框架介绍 -
lliiqiang:
怎么直接删除文件夹啊?固定的几个文件可以删除,不固定的呢?需要 ...
Flex AIR —— 文件读写
一、包规范
1、report包
CREATE OR REPLACE package report as type cursorType is ref cursor; type CommInfo is record( id_ varchar2(60), name_ varchar2(200), comm_category_name_ varchar2(60), city_company_name_ varchar2(200), district_name_ varchar2(100), county_company_name_ varchar2(200), contact_ varchar2(60), mobile_phone_ varchar2(60), person_id_ varchar2(60), bank_num_ varchar2(60) ); type CardType is record( id_ varchar2(60), type_ varchar2(60), card_brand_ varchar2(60), card_brand_name_ varchar2(60) ); type QuantityBean is record( commId number, pkl number := 0, jhl number := 0, jfl number := 0, zsl number := 0 ); function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2; end;
2、reportFunc包
CREATE OR REPLACE package reportFunc as function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2; function getCardTypeSQL return varchar2; function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean; end;
二、包体
1、report包体
CREATE OR REPLACE package body report as --生成数据集 function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is c1 cursorType; c2 cursorType; v_CommInfo CommInfo; v_CardType CardType; pkl_ number := 0; -- 值 jhl_ number := 0; jfl_ number := 0; zsl_ number := 0; jhRate number := 0; jfRate number := 0; v_pk_total_ number := 0; -- 值合计 v_jh_total_ number := 0; v_jf_total_ number := 0; v_zs_total_ number := 0; v_jhl_total_ number := 0; v_jfl_total_ number := 0; pk_sum number := 0; -- 值合计的合计 jh_sum number := 0; jf_sum number := 0; zs_sum number := 0; jhl_sum number := 0; jfl_sum number := 0; bean QuantityBean; result varchar2(200); lsh number := 0; createDate date := sysdate; begin delete from R_PIKA_STAT_ where create_date_<createDate-1; commit; select SEQ_LSH.nextval into lsh from dual; --代办点 open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm); loop v_pk_total_ := 0; v_jh_total_ := 0; v_jf_total_ := 0; v_zs_total_ := 0; v_jhl_total_ := 0; v_jfl_total_ := 0; fetch c1 into v_CommInfo; exit when c1%notfound; --卡类型 open c2 for reportFunc.getCardTypeSQL; loop fetch c2 into v_CardType; exit when c2%notfound; bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status); --批卡量 pkl_ := bean.pkl; v_pk_total_ := v_pk_total_ + pkl_; insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_, county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_, card_brand_,card_brand_name_,data_type_,amount_,create_date_) values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_, v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_, v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''), v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate); --激活量 jhl_ := bean.jhl; v_jh_total_ := v_jh_total_ + jhl_; insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_, county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_, card_brand_,card_brand_name_,data_type_,amount_,create_date_) values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_, v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_, v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''), v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate); --成功积分登记量 jfl_ := bean.jfl; v_jf_total_ := v_jf_total_ + jfl_; insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_, county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_, card_brand_,card_brand_name_,data_type_,amount_,create_date_) values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_, v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_, v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''), v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate); --激活率 jhRate := 0; if pkl_ > 0 then jhRate := (1.0 * jhl_) / pkl_; else if jhl_ > 0 then jhRate := 1.0; end if; end if; insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_, county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_, card_brand_,card_brand_name_,data_type_,amount_,create_date_) values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_, v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_, v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''), v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate); --积分率 = 已积分数量 / (已激活数量-赠送号激活量) zsl_ := bean.zsl; v_zs_total_ := v_zs_total_ + zsl_; jfRate := 0; if (jhl_ - zsl_) > 0 then jfRate := (1.0 * jfl_) / (jhl_ - zsl_); else if jfl_ > 0 then jfRate := 1.0; end if; end if; insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_, county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_, card_brand_,card_brand_name_,data_type_,amount_,create_date_) values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_, v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_, v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''), v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate); end loop; close c2; --去掉值为0的代办点 if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_; else --批卡合计 update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_; pk_sum := pk_sum + v_pk_total_; --激活合计 update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_; jh_sum := jh_sum + v_jh_total_; --积分量合计 update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_; jf_sum := jf_sum + v_jf_total_; --激活率合计 v_jhl_total_ := 0; if v_pk_total_ > 0 then v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_; else if v_jh_total_ > 0 then v_jhl_total_ := 1.0; end if; end if; update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_; --积分率合计 v_jfl_total_ := 0; if (v_jh_total_ - v_zs_total_) > 0 then v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_); else if v_jf_total_ > 0 then v_jfl_total_ := 1.0; end if; end if; update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_; zs_sum := zs_sum + v_zs_total_; end if; end loop; close c1; --合计的合计 if pk_sum > 0 then jhl_sum := (1.0 * jh_sum) / pk_sum; else if jh_sum > 0 then jhl_sum := 1.0; end if; end if; if (jh_sum - zs_sum) > 0 then jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum); else if jf_sum > 0 then jfl_sum := 1.0; end if; end if; result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum; commit; return result; end; end;
2、reportFunc包体
CREATE OR REPLACE package body reportFunc as --返回获取代办点信息的SQL function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2 is sql_1 varchar2(2000); begin sql_1 := 'select a.id_,a.name_,e.name_ comm_category_name_,b.name_ city_company_name_,d.name_ district_name_,c.name_ county_company_name_,'; sql_1 := sql_1 || 'case when (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.contact_ else (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end contact_,'; sql_1 := sql_1 || 'case when (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.mobile_phone_ else (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end mobile_phone_,'; sql_1 := sql_1 || 'case when (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.person_id_ else (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end person_id_,'; sql_1 := sql_1 || 'case when (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.bank_num_ else (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end bank_num_'; sql_1 := sql_1 || ' from commission_info_ a'; sql_1 := sql_1 || ' left join organization_ b on a.city_company_id_=b.party_id_'; sql_1 := sql_1 || ' left join organization_ c on a.county_company_id_=c.party_id_'; sql_1 := sql_1 || ' left join district_ d on a.district_=d.id_'; sql_1 := sql_1 || ' left join commission_category_ e on a.comm_cagegory_=e.id_'; sql_1 := sql_1 || ' where a.id_ is not null'; if p_fgs is not null then sql_1 := sql_1 || ' and a.city_company_id_=''' || p_fgs || ''''; end if; if p_fwt is not null then sql_1 := sql_1 || ' and a.county_company_id_=''' || p_fwt || ''''; end if; if p_comm is not null then sql_1 := sql_1 || ' and a.id_ in(' || p_comm || ')'; end if; sql_1 := sql_1 || ' order by a.id_ asc'; return sql_1; end; --返回获取卡类型信息的SQL function getCardTypeSQL return varchar2 is sql_2 varchar2(1000); begin sql_2 := 'select a.id_,a.type_,a.card_brand_,b.description_ card_brand_name_'; sql_2 := sql_2 || ' from sim_card_type_ a inner join enumeration_ b'; sql_2 := sql_2 || ' on a.card_brand_=b.enum_id_ order by a.id_ asc'; return sql_2; end; -- 数量Bean function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean is s varchar2(2000); bean report.QuantityBean; c1 report.cursorType; begin s := 'select a.id_,'; s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and status_ in(' || p_status || ') and card_type_id_=' || p_type; if p_date1 is not null then s := s || ' and status_date_>=' || p_date1; end if; if p_date2 is not null then s := s || ' and status_date_<' || p_date2; end if; s := s || ') pkl,'; s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and card_type_id_=' || p_type; if p_date1 is not null then s := s || ' and active_date_>=' || p_date1; end if; if p_date2 is not null then s := s || ' and active_date_<' || p_date2; end if; s := s || ') jhl,'; s := s || '(select count(id_) from sim_card_detail_ where is_integral_=1 and commission_id_=a.id_ and card_type_id_=' || p_type; if p_date1 is not null then s := s || ' and active_date_>=' || p_date1; end if; if p_date2 is not null then s := s || ' and active_date_<' || p_date2; end if; s := s || ') jfl,'; s := s || '(select count(id_) from sim_card_detail_ where status_=''RESOURCE_STATUS_13'' and commission_id_=a.id_ and card_type_id_=' || p_type; if p_date1 is not null then s := s || ' and active_date_>=' || p_date1; end if; if p_date2 is not null then s := s || ' and active_date_<' || p_date2; end if; s := s || ') zsl'; s := s || ' from commission_info_ a where a.id_=' || p_comm; open c1 for s; loop fetch c1 into bean; exit when c1%notfound; end loop; close c1; return bean; end; end;
发表评论
-
Oracle的表分区
2019-11-25 14:11 380当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的 ... -
Oracle使用汇总
2015-12-31 16:51 7741、使用CLOB字段作为过滤条件 select ... -
mysql的安装和配置
2015-11-28 15:44 834安装配置步骤如下: 1、下载mysql-5.6. ... -
oracle lead分析函数
2015-11-23 21:17 1703Lag和Lead分析函数可以在同一次查询中取出同一字段的前 ... -
Oracle存储过程
2015-11-23 13:58 755new_time(sysdate,'GMT',' ... -
Oracle11g安装过程遇到的问题
2015-10-17 21:58 1831Oracle 11g安装成功后,在DOS窗口输入 emctl ... -
在oracle中使用scheduler的范例
2013-04-16 17:09 2104范例源码: -- 创建新表 create table ... -
在Oracle中使用job的范例
2013-04-16 15:17 1546范例源码: -- 创建新 ... -
数据库优化设计方案(转)
2010-06-12 14:29 1509本文首先讨论了 ... -
数据库理论知识
2010-06-10 14:40 20471、范式 第一范式(1NF): 字段 ... -
Sqlserver多表更新
2010-04-16 20:56 14481、sql update dbo.new_user set ... -
Oracle触发器的使用范例
2010-03-18 13:56 812create or replace trigger TG_MS ... -
常用数据库连接配置
2009-12-16 13:37 1674Oracle 10G oracle.jdbc.dri ... -
Sqlserver存储过程及游标的使用
2009-04-23 16:34 1849SET ANSI_NULLS ON GO SET QUOT ... -
SqlServer触发器的应用
2009-03-04 11:13 2159更新事务类似于在删除之后执行插入;首先旧行被复制到 delet ... -
Oracle游标(Cursor)的应用(一)
2008-07-01 10:17 1837功能:将某个入库批次中号码数量等于40的札拆分成两札 d ... -
Oracle多表关联update字段值
2008-07-01 10:02 39521、sql更新: UPDATE NUM_ACTIVE_INT ...
相关推荐
在本文中,我们将详细探讨Oracle中的打包方法,以及如何创建和管理自己的数据库包。 一、Oracle数据库包的基本概念 Oracle数据库包是一种高级的PL/SQL编程结构,它允许将相关的变量、过程和函数封装在一起,形成一...
oracle不使用oracleasm的包配置ASM磁盘配置方法
在使用Oracle JDBC Jar包时,开发者需要确保引入了正确的驱动版本,以匹配运行的Oracle数据库版本。同时,需要了解如何配置数据源,创建`DataSource`对象,以及如何使用`Connection`、`Statement`和`ResultSet`等...
oracle包调试.wmv
oracle19C驱动包(ojdbc8.jar;ojdbc10.jar两个包)
最新Oracle的rpm包最新Oracle的rpm包最新Oracle的rpm包
ORACLE的JDBC JAR包ORACLE的JDBC JAR包ORACLE的JDBC JAR包ORACLE的JDBC JAR包
用户通过这个程序启动和使用Oracle包生成参数工具,它整合了所有组件,提供图形用户界面,并处理用户的输入和命令。 Readme.txt:这是标准的说明文件,通常包含软件的安装指南、使用方法、注意事项等信息。对于...
gradle导入本地oracle包连接数据库,内含oracle6.jar包
各版本Oracle驱动包下载
创建Oracle包的基本语法如下: ```sql CREATE OR REPLACE PACKAGE package_name AS -- 规范:声明过程和函数 PROCEDURE procedure_name (parameter_list); FUNCTION function_name (parameter_list) RETURN ...
包含如下oracleasm包: kmod-oracleasm-2.0.6.rh1-3.el6.x86_64.rpm oracleasm-2.0.8-4.el6_6.src.rpm oracleasm-2.0.8-6.el6_7.src.rpm oracleasm-2.0.8-8.el7.src.rpm oracleasm-2.0.8-15.el7.centos.src.rpm ...
java连接oracle数据库jar包
Oracle 9i Client (Oracle 9i 客户端) 简化版 (不安装Oracle客户端,也可以使用PLSQL Developer 不用安装Oracle客户端也可以使用PLSQL Developer 绿色! 安全! 轻便! 可靠! 1、本软件可作为简单的Oracle9i客户端...
oracle toad汉化包
ORACLE 包和包体的创建和使用,深入浅出。
Java Oracle的JAR包是Java应用程序与Oracle数据库交互的关键组件,它们提供了Java Database Connectivity (JDBC)驱动程序,使得开发者可以使用Java语言编写程序来访问和操作Oracle数据库。Oracle JDBC驱动程序主要有...
Oracle内置包是Oracle数据库系统提供的一系列预定义的PL/SQL包,这些包极大地扩展了数据库的功能,并简化了开发者在数据库层面的操作。...掌握这些内置包的使用,能极大地提高开发效率和数据库管理能力。
我上传的是项目中使用SSH和Oracle数据库的jar包
SSH是三个流行Java Web开发框架的缩写,包括Spring、Struts和Hibernate。这些框架分别负责应用程序的依赖...通过合理的配置和jar包的使用,我们可以实现SSH框架与Oracle数据库的无缝对接,提高开发效率并降低维护成本。