Message warn for oracle

--===================================================================
-- create type
--===================================================================
create or replace type ty_str_split is table of varchar2 (4000);
/

--===================================================================
-- create function
--===================================================================
create or replace function fn_split (
p_str in varchar2, --
p_delimiter in varchar2) --
return ty_str_split
is
j int := 0;
i int := 1;
len int := 0;
len1 int := 0;
str varchar2 (4000);
str_split ty_str_split := ty_str_split ();
begin
len := length (p_str);
len1 := length (p_delimiter);

while j < len
loop
j := instr (p_str, p_delimiter, i);

if j = 0
then
j := len;
str := substr (p_str, i);
str_split.extend;
str_split (str_split.count) := str;

if i >= len
then
exit;
end if;
else
str := substr (p_str, i, j - i);
i := j + len1;
str_split.extend;
str_split (str_split.count) := str;
end if;
end loop;

return str_split;
end fn_split;
/
--================================================================
-- pr_callreasonwarn
--================================================================
create or replace procedure pr_callreasonwarn
as
v_phones_cfg varchar(2000);
v_hour_cfg int;
v_hour_sendmsg_cfg int;
v_sendmsgnum_every_cfg int;
v_sendmsgnum_all_cfg int;
v_sms_warn_content varchar(4000);
v_rownum_atype int;
v_rownum_alltype int;
v_intsessionid int;
v_vcid int;
begin
v_vcid:=4;
--Get config info.
begin
select strvalue into v_phones_cfg
from cc_systemparam
where strkey = 'CallReasonWarnPhones';
-- scan cr_slavelog4sms table N hour
select strvalue into v_hour_cfg
from cc_systemparam
where strkey = 'CallReasonScanHour';
exception
when no_data_found then
begin
return;
end;
end;

begin
select strvalue into v_hour_sendmsg_cfg
from cc_systemparam
where strkey='SendMsgLogHourInterval';
exception
when no_data_found then
begin
v_hour_sendmsg_cfg:=24;
end;
end;

begin
select strvalue into v_sendmsgnum_every_cfg
from cc_systemparam
where strkey='SendMsgCountEvery';
exception
when no_data_found then
begin
v_sendmsgnum_every_cfg:=10;
end;
end;

begin
select strvalue into v_sendmsgnum_all_cfg
from cc_systemparam
where strkey='SendMsgCountAll';
exception
when no_data_found then
begin
v_sendmsgnum_all_cfg:=50;
end;
end;


for cur_warndata in (
select a.titleid,c.strname,b.reasonid,b.sms_warn_content
from (
select a.titleid,
count(1) cnt
from cr_slavelog4sms a
where a.callingtime>=sysdate-v_hour_cfg/24
and a.callingtime<=sysdate
group by a.titleid
) a,cr_title_cfg b,cc_systemdict c
where b.titleid=c.dictid
and c.dictid = a.titleid
and b.sms_warn_val is not null
and a.cnt>nvl(b.sms_warn_val,0)
) loop

for cur_phones in(
select *
from table(cast(fn_split(v_phones_cfg, ',') as
ty_str_split))
)
loop
--each call reason
select count(1) into v_rownum_atype
from cr_warn_smslog
where send_time>=sysdate-v_hour_sendmsg_cfg/24
and send_time<=sysdate
and receiver=cur_phones.column_value
and titleid=cur_warndata.titleid;
--all call reason
select count(1) into v_rownum_alltype
from cr_warn_smslog
where send_time>=sysdate-v_hour_sendmsg_cfg/24
and send_time<=sysdate
and receiver=cur_phones.column_value;

if v_rownum_atype>= v_sendmsgnum_every_cfg then
null;
else
if v_rownum_alltype >=v_sendmsgnum_all_cfg then
null;
else
v_sms_warn_content:='The '||cur_warndata.strname||' call reason exceeds the default times, the remark:'||cur_warndata.sms_warn_content;
zxdb_kf.PR_GetSessionID('HF_FAXMISSION',v_intsessionid);

insert into zxdb_kf.hf_faxmission(intsessionid, strcustomerno, strsender, strfaxnotice, intstatus, strnotice)
values (v_intsessionid, cur_phones.column_value, '994', '03', 0, v_sms_warn_content);
--Create log
insert into cr_warn_smslog(
vcid,
titleid,
reasonid,
receiver
)
values(
v_vcid,
cur_warndata.titleid,
cur_warndata.reasonid,
cur_phones.column_value
);

end if;

end if;
end loop;
commit;
end loop;
end;
/