create or replace procedure test(pi_yyyy_mm in varchar2) is
l_ct number;
function get_continuouscreditcardnumber(pi_cardno varchar2,
pi_date varchar2) return number is
l_cont_ct number;
begin
select nvl(max(continuouscreditcardnumber), 0) + 1
into l_cont_ct
from t
where t.cardno = pi_cardno
and to_char(t.slotcarddate + 1, 'yyyymmdd') = pi_date;
return l_cont_ct;
exception
when others then
l_cont_ct := 1;
return l_cont_ct;
end;
begin
for c1 in (select s.cardno,
to_char(s.slotcarddate, 'yyyymmdd') dt,
sum(s.slotcardmoney) tot_money,
count(s.cardno) cnt
from s
where to_char(s.slotcarddate, 'yyyymm') = pi_yyyy_mm
group by s.cardno, to_char(s.slotcarddate, 'yyyymmdd')
order by s.cardno, to_char(s.slotcarddate, 'yyyymmdd')) loop
l_ct := get_continuouscreditcardnumber(c1.cardno, c1.dt);
insert into t
values
(to_date(c1.dt, 'yyyymmdd'), c1.cardno, c1.cnt, c1.tot_money, l_ct);
end loop;
commit;
end test;
解决方案 »
- 在solaris下用root与用oracle用户登陆oracle的问题
- 求助--一个比较诡异的SQL查询的写法
- oracle9i 查询优化
- 问个个ORACLE的SQL语句
- plsql索引表 批量绑定问题
- 难道就没人做过用存储过程将xml文档导入oracle数据库了吗?
- ora-01219数据库未打开,查看日志文件后说是,系统找不到指定的文件oracle\admin\orcl\udump\orcl_ora_3000.trc
- 查询中CASE问题
- 为什么在外编辑器写好的SQL语句,在SQL PLUS 中执行出现错误?
- 请教高手:我把两个带排序的视图A,B用UNION联合生成新视图C发现记录不是A->B顺序,而是自动重新排序!为什么?
- current redo异常恢复
- select语句通过dblink查询出重复数据
表可是有1-3月的信息的