带游标的存储过程

create or replace procedure msg_pro1(accessNum in number,upCode in varchar2) is

--定义游标变量

type sms_cursor_type is ref cursor;

sms_cursor sms_cursor_type;

--bc_message中的最新时间

v_maxSeq_Id number;

--smsrecord

type sms_record_type is record(

phone sms_morecord.vc2feetermid%type,

content sms_morecord.vc2content%type,

insertTime date,

accessNumber sms_morecord.vc2svccode%type,

seq_Id sms_morecord.seq_id%type

);

sms_record sms_record_type;

begin

select max(msg.seq_id) into v_maxSeq_Id from bc_message msg where msg.accesscode=accessNum and msg.uporder=upCode;

if v_maxSeq_Id is null then

open sms_cursor for select sms.vc2feetermid,sms.vc2content,sms.vc2recvtime,sms.vc2svccode,sms.seq_id from sms_morecord sms where sms.vc2content like upCode||'%' and sms.vc2svccode=accessNum and sms.vc2feetermid not in(select fp.phonenumber from bc_filterphone fp);

loop

fetch sms_cursor into sms_record;

exit when sms_cursor%notfound;

insert into bc_message(useit,messageid,uporder,phone,content,inserttime,accesscode,seq_id)

values(0,hibernate_sequence.nextval,upCode,to_number(sms_record.phone),sms_record.content,sms_record.insertTime,to_number(sms_record.accessNumber),sms_record.seq_Id);

end loop;

commit;

close sms_cursor;

else

open sms_cursor for select sms.vc2feetermid,sms.vc2content,sms.vc2recvtime,sms.vc2svccode,sms.seq_id from sms_morecord sms where sms.vc2content like upCode||'%' and sms.vc2svccode=accessNum and sms.seq_id>v_maxSeq_Id and sms.vc2feetermid not in(select fp.phonenumber from bc_filterphone fp);

loop

fetch sms_cursor into sms_record;

exit when sms_cursor%notfound;

insert into bc_message(useit,messageid,uporder,phone,content,inserttime,accesscode,seq_id)

values(0,hibernate_sequence.nextval,upCode,to_number(sms_record.phone),sms_record.content,sms_record.insertTime,to_number(sms_record.accessNumber),sms_record.seq_Id);

end loop;

commit;

close sms_cursor;

end if;

-- open pac_cursor for select * from bc_message m where (m.inserttime between bt and et) and m.accesscode=accessNum and m.content like upCode||'%' and m.useit=useIt;

end;

/


--游标类型的包

create or replace package pac as

type msg_cursor_type is ref cursor;

end pac;

/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值