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;
--sms的record
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;
/