procedure_sendmessage2.sql

网通网关Cngp

create or replace procedure procedure_sendmo(
												   IfHaveData out number,
												   Mobileid out varchar2,
												   Content  out varchar2,
												   Feetype  out varchar2,
												   Feecode  out  varchar2,
												   ServiceId out varchar2,
												   SourceId   out varchar2,
												   Priority  out  number
												   )
as
/*-------------------
Cnc使用的获取数据过程
2006-02-05
----------------------*/
  cursor c is select mobileid,content,feetype,feecode,serviceid,sourceid,priority,rowid 
    from sendmessage2 where processflag=0 and rownum=1 for update;
  v_rowid       rowid;
  v_count       number(7);
  v_procedure   varchar2(40);
  v_sql         varchar2(100);
  v_sqlerrm     varchar2(200);
begin
  select count(*) into v_count from sendmessage2 where processflag=0;
  if(v_count>0) then
    IfHaveData:=1;
	open c;
	loop
		fetch c into Mobileid,Content,Feetype,feecode,serviceid,sourceid,Priority,v_rowid;
		exit when c%notfound;
		update sendmessage2 set processflag=1 where rowid=v_rowid;
	end loop;
	close c;
	commit;
  elsif(v_count=0) then
    IfHaveData:=0;
  end if;
exception										 
  when others then 
    rollback;
    v_procedure:='procedure_sendmo';
    v_sql:=to_char(sqlcode);
    v_sqlerrm:=substr(sqlerrm,1,200);
    	insert into db_error_log values(sysdate,v_procedure,null,v_sql,v_sqlerrm);			
    commit;
end;
/