CREATE OR REPLACE procedure TMS.select_push_user (v_servid varchar2)
is
vv_mid varchar2(14);
vv_isdnvarchar2(12);
vv_status varchar2(2);
v_count number(10);
v_sql
varchar2(300);
v_send_count number(4);
cursor c_Subscriber_TDR(v_servid varchar2) IS
SELECT b.subsid,a.mid,a.icpid,a.servid,b.status subscriber_status,a.status subscription_status
FROM NILS_SUBSCRIPTION a, NILS_SUBSCRIBER b
WHERE a.status = 'A' AND a.mid = b.mid
and a.servid=v_servid;
begin
For c1 in c_Subscriber_TDR(v_servid ) loop--？？？？？？？
if upper(c1.Subscriber_STATUS)='A' THEN
--？？？？？？？？？？A？？？？？？？？？？
select count(*)into v_count from get_data where mid=c1.mid; --？？？？？？？？？？？？？
ifv_count =0then--？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？
insert into get_data values(c1.subsid,c1.mid,c1.icpid,c1.servid,c1.Subscriber_STATUS,0);
--insert into tab_push_user values(c1.subsid,c1.mid,c1.icpid,c1.servid,c1.Subscriber_STATUS,0,0,SEQ_SMS_NO.nextval);
commit;
else--？？？？？？？？？？？？？,？？？？？？？？？？？
select status,mid,send_countinto vv_status,vv_mid,v_send_count from get_data where mid=c1.mid;
if vv_status='U' OR vv_status='N' then --？？？？？？？？？？？？？？？？？？？？？？？？？？？？PUSH？？
insert into tab_push_user values(c1.subsid,c1.mid,c1.icpid,c1.servid,c1.Subscriber_STATUS,v_send_count,1,SEQ_SMS_NO.nextval);
--else --？？？？？？？？？？？？？？？？？？？？？？PUSH？？
--
insert into tab_push_user values (c1.subsid,c1.mid,c1.icpid,c1.servid,c1.Subscriber_STATUS,0,0,SEQ_SMS_NO.nextval);
end if;
end if;
--？？？？？？？？？？？？？？？？？？？？？PUSH？？？？？？0.
update get_data set status=c1.Subscriber_STATUS,send_count=0 where mid=vv_mid;
commit;
else--？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？,？？？？？？？？？？？？？？？？？？？？
if upper(c1.Subscriber_STATUS)='U' or upper(c1.Subscriber_STATUS)='N' THEN
select count(*) into v_count from get_data where mid=c1.mid;
if v_count=0 then
insert into get_data values(c1.subsid,c1.mid,c1.icpid,c1.servid,c1.Subscriber_STATUS,1); --？？？？？？？？1
else--？？？？？？？？？？？？？？？？？？？？？？？？？
select status,send_count into vv_status,v_send_count from get_data where mid=c1.mid;
--？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？1
if upper(vv_status)='U' or upper(vv_status)='N' then
update get_data set send_count=send_count+1,status=c1.Subscriber_STATUS where mid=c1.mid;
else --？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？
update get_data set send_count=1,status=c1.Subscriber_STATUS where mid=c1.mid;
end if;
end if;
end if;
end if;
end loop;
commit;
--？？？？？？？？？？？PUSH？？
-- select count(*) into v_count from tab_push_user where servid=v_servid and flag=1;
--？？？？？？？？？？？？？？？？？？？？
-- insert into tab_proc_info values(to_date(sysdate,'yyyy-mm-dd hh24:mi:ss'),'11',v_servid,v_count);
-- commit;
--insert into tab_process values();
exception
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20009,'tet');
-- rollback;
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20009,'test');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20009,SQLERRM);
end;
/
|
