Create or replace Package SendPackage
as
/*----
發送信息
2006-06-21

---*/
   Type mobile_array is table of varchar2(15) index by binary_integer;
   Type content_array is table of varchar2(400) index by binary_integer;
   Type feetype_array is table of varchar2(4) index by binary_integer;
   Type feecode_array is table of varchar2(10) index by binary_integer;
   Type serviceid_array is table of varchar2(10) index by binary_integer;
   Type sourceid_array is table of varchar2(21) index by binary_integer;
   Type priority_array is table of varchar2(1) index by binary_integer;
   
   procedure Send1( 
					inmobile    out mobile_array,
					incontent   out content_array,
					infeetype   out feetype_array,
					infeecode   out feecode_array,
					inserviceid out serviceid_array,
					insourceid  out sourceid_array,
					inpriority  out priority_array					
					);
    procedure Send2( 
					inmobile    out mobile_array,
					incontent   out content_array,
					infeetype   out feetype_array,
					infeecode   out feecode_array,
					inserviceid out serviceid_array,
					insourceid  out sourceid_array,
					inpriority  out priority_array				
					);
			
	procedure SendFee(
						inMobile     out  mobile_array,
						inContent    out  content_array,
						inFeetype    out  feetype_array,
						inFeecode    out  feecode_array,
						inServiceid  out  serviceid_array,
						inSourceid   out  sourceid_array
					
						);


end SendPackage;
/
/*------------------------------------------------------------------------*/
Create or replace Package Body SendPackage 
as
/*-----
|2006-06-21
|獲取發送信息
|減少最后一個參數，標記有效信息條數用的
------------------------------*/
   cursor c1 is select trim(mobileid),
					   trim(content),
					   trim(feetype),
					   trim(feecode),
					   trim(serviceid),
					   trim(sourceid),
					   trim(priority),
					   trim(rowid) 
     from sendmessage2 where processflag=0 order by priority desc;
   cursor c2 is select trim(mobileid),
					   trim(content),
					   trim(feetype),
					   trim(feecode),
					   trim(serviceid),
					   trim(sourceid),
					   trim(priority),
					   trim(rowid) 
     from sendmessage4 where processflag=0 order by priority desc;
   Cursor c3 is select trim(mobileid),
						trim(content),
						trim(feetype),
						trim(feecode),
						trim(serviceid),
						trim(sourceid),
						trim(rowid)
	  from realtime_Sendmessage where processflag=0;
    /* --------------------------------Send1------------------------------------------*/
    procedure Send1(
					inmobile    out mobile_array,
					incontent   out content_array,
					infeetype   out feetype_array,
					infeecode   out feecode_array,
					inserviceid out serviceid_array,
					insourceid  out sourceid_array,
					inpriority  out priority_array					
					)
	as
	    v_procedure   varchar2(40);
		v_sql         varchar2(100);
		v_sqlerrm     varchar2(200);
		v_rowid       rowid;
		v_count       integer;
		
	begin
	  select count(*) into v_count from Sendmessage2 where processflag=0;
	  if (v_count>0) then	  
	    
	    
		if not c1%isopen then
			open c1;
		end if;
		  
		for i in 1.. v_Count 
		loop
			fetch c1 into inmobile(i),
						incontent(i),
						infeetype(i),
						infeecode(i),
						inserviceid(i),
						insourceid(i),
						inpriority(i),
						v_rowid;
			if(c1%notfound) then
			  close c1;
			  exit;
			else
			  update sendmessage2 set processflag=1 where rowid=v_rowid;
			end if;
		end loop;
		commit;
	  end if;
	exception
	  when others then
	    if c1%ISOPEN then
          close c1;
        end if;
        rollback;
        v_procedure:='SendPackage.Send1';
        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 Send1;
    /*-------------------------------Send2-------------------------------------------*/

   procedure Send2(
					inmobile    out mobile_array,
					incontent   out content_array,
					infeetype   out feetype_array,
					infeecode   out feecode_array,
					inserviceid out serviceid_array,
					insourceid  out sourceid_array,
					inpriority  out priority_array					
					)
	as
	    v_procedure   varchar2(40);
		v_sql         varchar2(100);
		v_sqlerrm     varchar2(200);
		v_rowid       rowid;
		v_Count       integer;
	begin
	  select count(*) into v_count from Sendmessage4 where processflag=0;
	  if (v_count>0) then	   
	    
		if not c2%isopen then
			open c2;
		end if;
		  
		for i in 1.. v_Count 
		loop
			fetch c2 into inmobile(i),
						incontent(i),
						infeetype(i),
						infeecode(i),
						inserviceid(i),
						insourceid(i),
						inpriority(i),
						v_rowid;
			if(c2%notfound) then
			  close c2;
			  exit;
			else
			  update sendmessage4 set processflag=1 where rowid=v_rowid;
			end if;
		end loop;
		commit;
	  end if;
	exception
	  when others then
	    if c2%ISOPEN then
          close c2;
        end if;
        rollback;
        v_procedure:='SendPackage.Send2';
        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 Send2;
    /*---------------------------------------------------------*/
    procedure SendFee(
						inMobile     out  mobile_array,
						inContent    out  content_array,
						inFeetype    out  feetype_array,
						inFeecode    out  feecode_array,
						inServiceid  out  serviceid_array,
						inSourceid   out  sourceid_array
						
						)
	as
	    v_procedure   varchar2(40);
		v_sql         varchar2(100);
		v_sqlerrm     varchar2(200);
		v_rowid       rowid;
		v_Count       integer;
	begin
	  select count(*) into v_count from realtime_sendmessage where processflag=0;
	  if (v_count>0) then
	    
	    
		if not c3%isopen then
			open c3;
		end if;
		  
		for i in 1.. v_Count 
		loop
			fetch c3 into inmobile(i),
						incontent(i),
						infeetype(i),
						infeecode(i),
						inserviceid(i),
						insourceid(i),						
						v_rowid;
			if(c3%notfound) then
			  close c3;
			  exit;
			else
			  update realtime_sendmessage set processflag=1 where rowid=v_rowid;
			end if;
		end loop;
		commit;
	  end if;
	exception
	  when others then
	    if c3%ISOPEN then
          close c3;
        end if;
        rollback;
        v_procedure:='SendPackage.Sendfee';
        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 Sendfee;
	
end SendPackage;
/