procedure_sendmessage4.sql

来自「网通网关Cngp」· SQL 代码 · 共 50 行

create or replace procedure procedure_sendmt(
												   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 sendmessage4 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 sendmessage4 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 sendmessage4 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_sendmt';
    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;
/