CREATE OR REPLACE PROCEDURE sp_msuser_ext (v_msisdn varchar2,v_imsi varchar2,v_hlr varchar2,v_vlr varchar2,v_time date) IS v_exist int; v_vlr_city varchar2(30); v_hlr_city varchar2(30); v_hlr_province varchar2(40); v_vlr_province varchar2(40); BEGIN BEGIN --城市/省份 select city,province into v_hlr_city,v_hlr_province from sys_isdn_segment where start_isdn<=v_msisdn and end_isdn>=v_msisdn; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN v_hlr_city :=''; v_hlr_province :=''; END; END; BEGIN select city,province into v_vlr_city,v_vlr_province from sys_vlr where vlr=v_vlr; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN v_vlr_city :=''; v_vlr_province :=''; END; END;
IF v_hlr_province=v_vlr_province THEN --屏蔽省内漫游 return; END IF; select count(*) into v_exist from sys_filter where state>0 and type=1 and value=v_msisdn; IF v_exist>0 THEN --过滤掉拒绝用户 return ; END IF; select count(*) into v_exist from sys_filter where state>0 and type=2 and value=v_vlr_city; IF v_exist>0 THEN --过滤掉拒绝用户 return ; END IF; select count(*) into v_exist from MSUSER_QT00 where msisdn=v_msisdn; IF v_exist>0 OR v_hlr_province ='' THEN update MSUSER_QT00 set vlr=v_vlr,lasttime=v_time where msisdn=v_msisdn and vlr_city=v_vlr_city;--到省内不同城市漫游只算一次 IF SQL%NOTFOUND THEN dispatch_smelog(v_msisdn,v_vlr,v_hlr_city,v_vlr_city);--发送短信存储过程 insert into MSUSER_QT00(msisdn,vlr,hlr_city,vlr_city,updatetime,lasttime) values(v_msisdn,v_vlr,v_hlr_city,v_vlr_city,v_time,v_time); END IF; ELSE update MSUSER set vlr=v_vlr,lasttime=v_time where msisdn=v_msisdn and vlr_city=v_vlr_city; IF SQL%NOTFOUND THEN dispatch_smelog(v_msisdn,v_vlr,v_hlr_city,v_vlr_city);--发送短信存储过程 insert into MSUSER(msisdn,vlr,hlr_city,vlr_city,updatetime,lasttime) values(v_msisdn,v_vlr,v_hlr_city,v_vlr_city,v_time,v_time); END IF; END IF; commit; EXCEPTION WHEN OTHERS THEN return; END;
sql*plus中设置: SET SERVEROUTPUT ON;你可以在关键位置加几个 dbms_output.put_line('time01:'||to_char(sysdate,'hh24:mi:ss')); 看实际运行到此处的时间 试试看
(v_msisdn varchar2,v_imsi varchar2,v_hlr varchar2,v_vlr varchar2,v_time date)
IS
v_exist int;
v_vlr_city varchar2(30);
v_hlr_city varchar2(30);
v_hlr_province varchar2(40);
v_vlr_province varchar2(40);
BEGIN
BEGIN --城市/省份
select city,province into v_hlr_city,v_hlr_province from sys_isdn_segment where start_isdn<=v_msisdn and end_isdn>=v_msisdn;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
v_hlr_city :='';
v_hlr_province :='';
END;
END; BEGIN
select city,province into v_vlr_city,v_vlr_province from sys_vlr where vlr=v_vlr;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
v_vlr_city :='';
v_vlr_province :='';
END;
END;
IF v_hlr_province=v_vlr_province THEN --屏蔽省内漫游
return;
END IF; select count(*) into v_exist from sys_filter where state>0 and type=1 and value=v_msisdn;
IF v_exist>0 THEN --过滤掉拒绝用户
return ;
END IF;
select count(*) into v_exist from sys_filter where state>0 and type=2 and value=v_vlr_city;
IF v_exist>0 THEN --过滤掉拒绝用户
return ;
END IF; select count(*) into v_exist from MSUSER_QT00 where msisdn=v_msisdn;
IF v_exist>0 OR v_hlr_province ='' THEN
update MSUSER_QT00 set vlr=v_vlr,lasttime=v_time where msisdn=v_msisdn and vlr_city=v_vlr_city;--到省内不同城市漫游只算一次
IF SQL%NOTFOUND THEN
dispatch_smelog(v_msisdn,v_vlr,v_hlr_city,v_vlr_city);--发送短信存储过程
insert into MSUSER_QT00(msisdn,vlr,hlr_city,vlr_city,updatetime,lasttime) values(v_msisdn,v_vlr,v_hlr_city,v_vlr_city,v_time,v_time);
END IF;
ELSE
update MSUSER set vlr=v_vlr,lasttime=v_time where msisdn=v_msisdn and vlr_city=v_vlr_city;
IF SQL%NOTFOUND THEN
dispatch_smelog(v_msisdn,v_vlr,v_hlr_city,v_vlr_city);--发送短信存储过程
insert into MSUSER(msisdn,vlr,hlr_city,vlr_city,updatetime,lasttime)
values(v_msisdn,v_vlr,v_hlr_city,v_vlr_city,v_time,v_time);
END IF;
END IF;
commit;
EXCEPTION
WHEN OTHERS THEN
return;
END;
SET SERVEROUTPUT ON;你可以在关键位置加几个
dbms_output.put_line('time01:'||to_char(sysdate,'hh24:mi:ss'));
看实际运行到此处的时间
试试看
查查9i的新特性资料,应当可以解决的,也就是说
oracle 9i可以批量提交,应当也可以设置为与8i兼容。
或修改(增加)你的init.ora中的参数compatible = 8.1.6
让他兼容8i,看看怎么样
如果你通过程序调用,是不可以的,不过你可以用
insert into的办法写到数据库中
我没有见过9i