procedure_getfee.sql

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

create or replace procedure procedure_getfee(Mobileid out varchar2,
											 Content  out varchar2,
											 FeeType  out varchar2,
											 FeeCode  out varchar2,
											 ServiceId out varchar2,
											 SourceId   out varchar2)
as
  cursor c is select mobileid,content,feetype,feecode,serviceid,sourceid,rowid 
    from realtime_sendmessage 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 realtime_sendmessage where processflag=0;
  if(v_count!=0) then
	open c;
	loop
		fetch c into Mobileid,Content,Feetype,Feecode,serviceid,sourceid,v_rowid;
		exit when c%notfound;
		update realtime_sendmessage set processflag=1 where rowid=v_rowid;
	end loop;
	close c;
	commit;
  end if;
exception										 
  when others then 
    rollback;
    v_procedure:='procedure_getfee';
    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;
/