- 浏览: 275724 次
文章分类
最新评论
-
羽风之扬:
crazydayu 写道您好,我在启动的时候也遇到了这种错误, ...
linux tomcat日志错误Cannot run without an instance id & java.net.UnknownHostExceptio -
羽风之扬:
我的也[是这用错误,改过后没效果。这是我的截图,麻烦帮我看下吧 ...
linux tomcat日志错误Cannot run without an instance id & java.net.UnknownHostExceptio -
hamizhong:
...
Linux和Python脚本自动部署应用一例(2) -
crazydayu:
您好,我在启动的时候也遇到了这种错误,但是加上127.0.0. ...
linux tomcat日志错误Cannot run without an instance id & java.net.UnknownHostExceptio -
lg327969713:
wx_hello 写道你好,请教下,这个链接要配置ODBC数据 ...
Java连接FoxPro6.0数据库(测试)
实现SQL语句
Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='04')-1,'')
over(Order By m.app_no) appno,
m.feature_seq||'' feature_seq,m.month||'' month,m.pointfee,m.discount
From app_mtfeature m Where m.app_no='04'
Union All
Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='04'
Union All
Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='06')-1,'')
over(Order By m.app_no) appno,
m.feature_seq||'',m.month||'',m.pointfee,m.discount
From app_mtfeature m Where m.app_no='06'
Union All
Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='06'
zxbxiaobo@sina.com pwd:xiaobo
看看首项,第二项效果怎么样![/color]
[color=olive]创建包,包体,使用存储过程 返回游标集合
CREATE OR REPLACE PACKAGE Acc_Card1 ---创建一包
As
Type Acc_Card_cursor Is Ref Cursor;
Procedure transact_data_query1(p_cursor Out Acc_Card_cursor,v_merchant_no varchar2,v_point_no varchar2,v_terminal_no varchar2,v_card_type varchar2,v_account_date1 date,v_account_date2 date,v_transact_time1 date,v_transact_time2 date,v_card_logical_number varchar2,v_card_face_no varchar2);
END Acc_Card1;
CREATE OR REPLACE Package Body Acc_Card1 As --创建一包体
Procedure transact_data_query1(p_cursor Out Acc_Card_cursor,v_merchant_no varchar2,v_point_no varchar2,v_terminal_no varchar2,v_card_type varchar2,v_account_date1 date,v_account_date2 date,v_transact_time1 date,v_transact_time2 date,v_card_logical_number varchar2,v_card_face_no varchar2) Is
Begin
Open p_Cursor For
Select
to_char(v1.TRANSACT_TIME-4/24,'yyyy-mm-dd') as transact_date
,v1.merchant_no as merchant_no
,substr(v1.POINT_NO,0,2) point_no
,substr(v1.POINT_NO,3,2) terminal_no
,v1.psam_no,v1.card_type,v1.card_type_name,v1.card_face_no,v1.card_logical_number,v1.tran_merchant_name,v1.tran_merchant_no,
v1.app_no,v1.transact_name,v1.transact_type,v1.mid_transact_value,v1.mid_voucher_value,v1.transact_time,v1.account_date,v1.customer_id
From v_card_tran_query v1
where
('-1'=v_card_face_no or card_face_no=v_card_face_no)
and ('-1'=v_card_logical_number or card_logical_number=v_card_logical_number)
and ('-1'=v_merchant_no or merchant_no=v_merchant_no)
and ('-1'=v_point_no or substr(v1.POINT_NO,0,2)=v_point_no)
and ('-1'=v_terminal_no or substr(v1.POINT_NO,3,2)=v_terminal_no)
and ('-1'=v_card_type or card_type=v_card_type)
and v1.account_date between v_account_date1 and v_account_date2
and v1.transact_time between v_transact_time1 and v_transact_time2
and
( v1.partition_field='00' or
exists
(
select * from sys_partition a where
a.start_date between v_account_date1 and v_account_date2
or a.end_date between v_account_date1 and v_account_date2
or v_account_date1 between a.start_date and a.end_date
or v_account_date2 between a.start_date and a.end_date
)
)
union all
select
decode(grouping(transact_date),1,' 合計',transact_date) as transact_date
,decode(grouping(merchant_no),1,decode(grouping(transact_date),1,'','小計'),merchant_no) as merchant_no
,decode(grouping(point_no),1,decode(grouping(merchant_no),1,'','小計'),point_no) as point_no
,decode(grouping(terminal_no),1,decode(grouping(point_no),1,'','小計'),terminal_no) as terminal_no
,null as psam_no,null as card_type,null as card_type_name,null as card_face_no,null as card_logical_number,null as tran_merchant_name,null as tran_merchant_no
,null as app_no,null as transact_name,null as transact_type,sum(mid_transact_value) as mid_transact_value,sum(mid_voucher_value) as mid_voucher_value
,null as transact_time,null as account_date,null customer_id
from
(
Select to_char(v1.TRANSACT_TIME-4/24,'yyyy-mm-dd') as transact_date,v1.merchant_no,substr(v1.POINT_NO,0,2) point_no,substr(v1.POINT_NO,3,2) terminal_no,v1.psam_no,
v1.card_type,v1.card_type_name,v1.card_face_no,v1.card_logical_number,v1.tran_merchant_name,v1.tran_merchant_no,
v1.app_no,v1.transact_name,v1.transact_type,v1.mid_transact_value,v1.mid_voucher_value,v1.transact_time,v1.account_date,v1.customer_id
,1 as order_no
From v_card_tran_query v1
where
('-1'=v_card_face_no or card_face_no=v_card_face_no)
and ('-1'=v_card_logical_number or card_logical_number=v_card_logical_number)
and ('-1'=v_merchant_no or merchant_no=v_merchant_no)
and ('-1'=v_point_no or substr(v1.POINT_NO,0,2)=v_point_no)
and ('-1'=v_terminal_no or substr(v1.POINT_NO,3,2)=v_terminal_no)
and ('-1'=v_card_type or card_type=v_card_type)
and v1.account_date between v_account_date1 and v_account_date2
and v1.transact_time between v_transact_time1 and v_transact_time2
and
( v1.partition_field='00'
or exists
(
select * from sys_partition a where
a.start_date between v_account_date1 and v_account_date2
or a.end_date between v_account_date1 and v_account_date2
or v_account_date1 between a.start_date and a.end_date
or v_account_date2 between a.start_date and a.end_date
)
)
) v1
group by rollup(transact_date,merchant_no,point_no,terminal_no)
having terminal_no is null
order by transact_date desc,merchant_no asc,point_no asc ,terminal_no asc ;
End transact_data_query1;
End Acc_Card1;
Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='04')-1,'')
over(Order By m.app_no) appno,
m.feature_seq||'' feature_seq,m.month||'' month,m.pointfee,m.discount
From app_mtfeature m Where m.app_no='04'
Union All
Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='04'
Union All
Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='06')-1,'')
over(Order By m.app_no) appno,
m.feature_seq||'',m.month||'',m.pointfee,m.discount
From app_mtfeature m Where m.app_no='06'
Union All
Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='06'
zxbxiaobo@sina.com pwd:xiaobo
看看首项,第二项效果怎么样![/color]
[color=olive]创建包,包体,使用存储过程 返回游标集合
CREATE OR REPLACE PACKAGE Acc_Card1 ---创建一包
As
Type Acc_Card_cursor Is Ref Cursor;
Procedure transact_data_query1(p_cursor Out Acc_Card_cursor,v_merchant_no varchar2,v_point_no varchar2,v_terminal_no varchar2,v_card_type varchar2,v_account_date1 date,v_account_date2 date,v_transact_time1 date,v_transact_time2 date,v_card_logical_number varchar2,v_card_face_no varchar2);
END Acc_Card1;
CREATE OR REPLACE Package Body Acc_Card1 As --创建一包体
Procedure transact_data_query1(p_cursor Out Acc_Card_cursor,v_merchant_no varchar2,v_point_no varchar2,v_terminal_no varchar2,v_card_type varchar2,v_account_date1 date,v_account_date2 date,v_transact_time1 date,v_transact_time2 date,v_card_logical_number varchar2,v_card_face_no varchar2) Is
Begin
Open p_Cursor For
Select
to_char(v1.TRANSACT_TIME-4/24,'yyyy-mm-dd') as transact_date
,v1.merchant_no as merchant_no
,substr(v1.POINT_NO,0,2) point_no
,substr(v1.POINT_NO,3,2) terminal_no
,v1.psam_no,v1.card_type,v1.card_type_name,v1.card_face_no,v1.card_logical_number,v1.tran_merchant_name,v1.tran_merchant_no,
v1.app_no,v1.transact_name,v1.transact_type,v1.mid_transact_value,v1.mid_voucher_value,v1.transact_time,v1.account_date,v1.customer_id
From v_card_tran_query v1
where
('-1'=v_card_face_no or card_face_no=v_card_face_no)
and ('-1'=v_card_logical_number or card_logical_number=v_card_logical_number)
and ('-1'=v_merchant_no or merchant_no=v_merchant_no)
and ('-1'=v_point_no or substr(v1.POINT_NO,0,2)=v_point_no)
and ('-1'=v_terminal_no or substr(v1.POINT_NO,3,2)=v_terminal_no)
and ('-1'=v_card_type or card_type=v_card_type)
and v1.account_date between v_account_date1 and v_account_date2
and v1.transact_time between v_transact_time1 and v_transact_time2
and
( v1.partition_field='00' or
exists
(
select * from sys_partition a where
a.start_date between v_account_date1 and v_account_date2
or a.end_date between v_account_date1 and v_account_date2
or v_account_date1 between a.start_date and a.end_date
or v_account_date2 between a.start_date and a.end_date
)
)
union all
select
decode(grouping(transact_date),1,' 合計',transact_date) as transact_date
,decode(grouping(merchant_no),1,decode(grouping(transact_date),1,'','小計'),merchant_no) as merchant_no
,decode(grouping(point_no),1,decode(grouping(merchant_no),1,'','小計'),point_no) as point_no
,decode(grouping(terminal_no),1,decode(grouping(point_no),1,'','小計'),terminal_no) as terminal_no
,null as psam_no,null as card_type,null as card_type_name,null as card_face_no,null as card_logical_number,null as tran_merchant_name,null as tran_merchant_no
,null as app_no,null as transact_name,null as transact_type,sum(mid_transact_value) as mid_transact_value,sum(mid_voucher_value) as mid_voucher_value
,null as transact_time,null as account_date,null customer_id
from
(
Select to_char(v1.TRANSACT_TIME-4/24,'yyyy-mm-dd') as transact_date,v1.merchant_no,substr(v1.POINT_NO,0,2) point_no,substr(v1.POINT_NO,3,2) terminal_no,v1.psam_no,
v1.card_type,v1.card_type_name,v1.card_face_no,v1.card_logical_number,v1.tran_merchant_name,v1.tran_merchant_no,
v1.app_no,v1.transact_name,v1.transact_type,v1.mid_transact_value,v1.mid_voucher_value,v1.transact_time,v1.account_date,v1.customer_id
,1 as order_no
From v_card_tran_query v1
where
('-1'=v_card_face_no or card_face_no=v_card_face_no)
and ('-1'=v_card_logical_number or card_logical_number=v_card_logical_number)
and ('-1'=v_merchant_no or merchant_no=v_merchant_no)
and ('-1'=v_point_no or substr(v1.POINT_NO,0,2)=v_point_no)
and ('-1'=v_terminal_no or substr(v1.POINT_NO,3,2)=v_terminal_no)
and ('-1'=v_card_type or card_type=v_card_type)
and v1.account_date between v_account_date1 and v_account_date2
and v1.transact_time between v_transact_time1 and v_transact_time2
and
( v1.partition_field='00'
or exists
(
select * from sys_partition a where
a.start_date between v_account_date1 and v_account_date2
or a.end_date between v_account_date1 and v_account_date2
or v_account_date1 between a.start_date and a.end_date
or v_account_date2 between a.start_date and a.end_date
)
)
) v1
group by rollup(transact_date,merchant_no,point_no,terminal_no)
having terminal_no is null
order by transact_date desc,merchant_no asc,point_no asc ,terminal_no asc ;
End transact_data_query1;
End Acc_Card1;
发表评论
-
mongodb在Java环境下简单使用
2015-09-16 18:42 1298mongodb在Java环境下简单使用,简单记录,简单增删查 ... -
Redis在java中批量操作数据的更新
2014-12-20 21:39 3291背景:同事说Redis在插入数据时,同样存在插入速度慢,只 ... -
WindowXP与Window2003设置MYSQL主从备份
2014-09-16 10:24 937公司要求mysql备份机制,最近结合项目和网上学习资料,实现 ... -
mysql的collation区分大小写设置
2013-03-18 00:03 2753... -
Oracle 存储过程处理存储过程游标集
2013-02-05 10:34 847好久没有写任何东西了,今天补充随便写一点,刚好以前同事问到 ... -
DB2 嵌套动态游标存储过程_记录
2012-06-15 16:45 1896DB2(V9.7) 嵌套动态游标存储过程,已经学习使用DB2近 ... -
Oracle 10g Dynamic report column(eg)
2012-05-14 18:59 935今日(2012-05-14)在群信息里(Oracle ... -
获取指定日期间隔简易一例
2011-12-27 17:58 962今日(2011-12-27)一哥们(Oracle 10. ... -
Installing Oracle 9i on RedHat Linux 7.1, 7.2, 7.3, 8.0, 9, Red Hat Advanced Ser
2011-11-24 10:01 0Werner Puschitz Sr. AIX/Li ... -
Oracle 行自动转动态列一种实现
2011-11-15 19:41 6820环境(Oracle Database 10g En ... -
数据库连续区间汇总(求和)一例,rownum伪列利用(原创)
2011-07-18 13:34 1582环境(Oracle Database 10g Ent ... -
oracle job使用详解及job不运行的检查方法(转载)
2011-02-14 10:11 1747oracle job使用 ... -
Oracle 字符集的查看和修改(转)
2011-01-28 12:46 1037一、什么是Oracle字符集 Oracle字 ... -
EXP命令:参数、举例、注意事项、问题与解决
2010-05-12 11:09 0(http://space.itpub.net/2365085 ... -
oracle 物化视图(转载)
2010-04-13 23:20 0(http://www.blogjava.net/beauty ... -
Oracle SYS_CONTEXT Function
2010-03-15 21:19 1495Oracle SYS_CONTEXT Function Ver ... -
Rman学习笔记记录
2010-02-25 17:15 1857记录前段时间学习ORACLE10g (10.2.0 )RMA ... -
Oracle错误:ORA-00604处理
2009-11-11 17:40 4412ORACLE性能调优篇---Oracle错误:ORA-0060 ... -
oracle中表数据横向转纵向显示(再一题)
2009-09-25 17:22 0最近工作又碰到了在Oracle(10.2.0)使用数 ... -
ORACLE查询笔记(mysql)
2009-06-09 09:30 1597好记性当不得烂笔头,把工作、学习中一点东西作个笔记... ...
相关推荐
本文档将深入解析几个经典的Oracle报表SQL语句,这些语句涉及到了Oracle中的高级功能,如窗口函数、联合查询等,并通过实际案例展示了如何构造报表。 #### 二、SQL语句分析 ##### 1. 分类汇总与总计 此段代码展示...
Oracle SQL语句教学涵盖了SQL语言的基础知识点,特别适合初学者入门。Oracle是全球领先的大型数据库系统之一,而SQL(Structured Query Language,结构化查询语言)是用于管理关系数据库的标准编程语言。本篇文档...
本文将探讨如何利用SQL语句实现这样的功能,特别是通过WITH AS语句和构建相应的数据结构。 首先,WITH AS语句是SQL中的一种高级查询技术,它允许我们为子查询命名,从而在后续的查询中重复使用。这种语法在处理复杂...
在实际应用中,这些SQL语句源码可能涉及到复杂的业务逻辑,比如通过触发器实时更新报表,或者通过存储过程处理大量数据的批处理任务。这些源码对于理解Oracle数据库如何高效地处理数据,以及如何利用其高级特性来...
本资源“实用总结SQL语句大全”涵盖了SQL的基本概念、语法以及高级特性,旨在帮助用户全面理解和掌握SQL。 首先,SQL的核心在于查询。基本的查询语句`SELECT`用于从数据库中提取数据,可以配合`FROM`指定数据来源,...
### Oracle中SQL语句行列之间的相互转换 在Oracle数据库中,有时我们需要将表格中的行转换为列或将列转换为行,这种操作被称为行列转换。这种转换对于数据分析、报表制作等场景非常有用。本文将详细介绍Oracle中...
以下是对各部分提到的SQL语句及其功能的详细解析: ### 1. 假数据插入:`SELECT * INTO b FROM a WHERE 1<>1` 这一语句看似简单,实则巧妙地用于创建一个空表结构而不插入任何数据。由于`WHERE 1<>1`条件永远不...
SQL的过程中对调用者传进的SQL进行转换后再执行,就基本完成了整个系统的SQL语法从SQL Server到Oracle的 兼容,呵呵,听起来有点不可思议。系统已经于3个月前上线,目前运行很稳定。 如果有人用得着或发现什么...
生成SQL语句的工具是为了帮助程序员和数据库管理员更高效、准确地编写和管理SQL查询而设计的。这些工具通常具备多种功能,包括代码自动生成、查询优化、错误检查等,大大提升了数据库操作的效率。 首先,我们要理解...
它能够实时监控数据库中的SQL语句执行情况,包括执行频率、执行时间、资源消耗等关键指标。通过这些数据,管理员可以快速定位到影响系统性能的关键SQL,从而进行针对性优化。 其次,SQLTracker具备详细的性能分析...
**Oracle 特有的 SQL 语句**:这部分内容涵盖了 Oracle9i 中独有的 SQL 语句,这些语句能够帮助用户更高效地管理和查询数据。 **iSQL*Plus**:iSQL*Plus 是一个强大的工具,用于执行 SQL 语句以及格式化和报告数据...
SQL语句生成工具是数据库管理工作中的一大利器,它极大地简化了数据库操作,特别是对于复杂的查询、更新和数据处理任务。这种工具通常具有图形用户界面,允许用户通过直观的界面来构建SQL语句,而无需手动编写复杂的...
【SQL语句基础】 SQL(Structured Query Language)是用于管理和处理关系数据库的标准语言,它包含了数据查询、数据更新、数据插入和数据删除等操作。SQL并非一种完整的编程语言,而是一种特殊的、高度结构化的查询...
**标题:** Oracle PL/SQL语句大全 **描述:** Oracle PL/SQL是Oracle数据库中一种强大的编程语言,它结合了SQL数据操作能力和过程化控制结构,使得开发者能够编写复杂的应用程序。 ### SQL*PLUS 命令介绍 在Oracle...
另外,确保查询能够有效利用索引也很重要,这可能涉及到SQL语句的编写方式,比如避免全表扫描,合理使用WHERE子句等。除了索引,还有其他优化策略,如物化视图、分区表、并行查询等,它们都是提高数据库性能的重要...