表结构信息,并不是用oracle描述的,但是后面的存储过程是针对oracle的
----------------个人交易流水表-----------------------------------
create table epc_flog (
mainid int primary key,自增
senddt smalldatetime null , --交易时间
brchno varchar(20) null , --机构号
custid varchar(20) null , --客户号
trancd varchar(20) null , --交易码 (其实是一种类型,,暂时用1表示行内,2表示跨行)
tranam decimal(15,2) null , --交易金额
tranfe decimal(15,2) null, --交易手续费
)
-----------------个人网银交易统计--------------------------------
create table dbo.emp_trpt (
mainid int primary key,自增
rprtdt smalldatetime null ,
intrnm int NULL , --新增行内转账笔数
intram decimal(15,2) null, --新增行内转账金额
ontrnm int NULL , --新增跨行转账笔数
ontram decimal(15,2) null, --新增跨行转账金额
rptpfg char(1) null , -- 0:日报表 1:月报表 2:季报表 3:年报表
)
--功能: --1、可按日、月维度统计转账交易的统计 --2、eb_rp_pertrancereportday查询epc_flog表,统计出当天的交易量,并写入到emp_trpt表 --3、eb_rp_pertrancereportmonth从emp_trpt表查询该月每天的交易量并进行统计,统计结果也写入到emp_trpt表
---创建存储过程.(日统计)
create or replace procedure p_pertrancereportday(pdate in date)
as
--定义游标,其实就是一个结果集
Cursor my_cursor
is
select TRANCD ,count(1) pcount,sum(TRANAM) pamount
from EPC_FLOG
where SENDDT between (pdate-interval '24' hour) and pdate
group by TRANCD ;
--定义游标变量
c_row my_cursor%rowtype ;
begin
--使用for in loop迭代游标
for c_row in my_cursor loop
if c_row.TRANCD='1' then
insert into EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)
values(pdate,c_row.pcount,c_row.pamount,0,0.00,'0'); --行内数据
else
insert into EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)
values(pdate,0,0.00,c_row.pcount,c_row.pamount,'0'); --跨行数据
end if;
end loop;
end;
----存储过程的调用
declare
pdate date:= to_date('2013-07-08 17:00:00','yyyy-MM-dd hh24:mi:ss') ;
begin
p_pertrancereportday(pdate);
end;
/
----创建存储过程(月统计,可用)
--本来不想用游标,因为只有一条数据,但是聚合函数在这里用了多个,普通的查询sum(INTRNM) into pintrnm,sum(INTRAM) into pintram等 不行
create or replace procedure p_pertrancereportmonth(pdate in date)
as
Cursor my_cursor
is
select sum(INTRNM) pintrnm, sum(INTRAM) pintram, sum(ONTRNM) pontrnm, sum(ONTRAM) pontram
from EMP_TRPT
where RPTPFG='0' and RPRTDT between trunc(pdate,'MON') and pdate ; --用到了oracle的时间函数
c_row my_cursor%rowtype ;
begin
for c_row in my_cursor loop
insert into EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)
values(pdate,c_row.pintrnm,c_row.pintram,c_row.pontrnm,c_row.pontram,'1');
end loop;
end;
Oracle表主键自动生成,需要使用序列和触发器来完成
--创建序列 drop sequence emp_trpt_tb_seq; create sequence emp_trpt_tb_seq increment by 1 start with 1; --创建序列 create or replace trigger emp_trpt_tb_tri before insert on emp_trpt for each row begin select emp_trpt_tb_seq.nextval into :new.MAINID from dual; end;