Oracle存储过程 一个具体实例

表结构信息，并不是用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;