--03 修改用户状态(开通、欠费、报停等) 用户营业要求停机
--FA4H 停机保号
if (v_ywlb='FA4H')
then
select Ydhhm into v_dhhm from GDB_DH where slbh=v_slbh ;
insert into MODFLAG_INTERFACE@to_xlt (psnum,newflag,commandtype,priority)
values ('86715'||v_dhhm ,'1','03',2);
end if ;
--FA4I 停机后复话
if (v_ywlb='FA4I')
then
select Ydhhm into v_dhhm from GDB_DH where slbh=v_slbh ;
insert into MODFLAG_INTERFACE@to_xlt (psnum,newflag,commandtype,priority)
values ('86715'||v_dhhm ,'0','03',2);
end if ;--04 用户换机(更换PSID) 前台换机--用户换号
if (v_ywlb='FA55')
THEN
SELECT YDHHM ,XDHHM INTO V_DHHM, V_XDHHM FROM GDB_DH WHERE SLBH=V_SLBH ;
INSERT INTO MODPSNUM_INTERFACE@TO_XLT(OLDPSNUM,NEWPSNUM,COMMANDTYPE,PRIORITY )
VALUES ('86715'||v_dhhm,'86715'||V_Xdhhm,'07','05');
UPDATE XLT_JQM SET DHHM=V_XDHHM WHERE DHHM=V_DHHM;
END IF ;--暂时不写
if (v_ywlb='FA42') --如果是增减程控服务用户
then
select Ydhhm into v_dhhm from GDB_DH where slbh=v_slbh ;
select ckfwxm into v_ckfwxm from gdb_dh where slbh=v_slbh ;
--05 修改ODB数据 长途限制和呼入限制
select decode(substr(v_ckfwxm,1,2),'13','2','33','3','30','3','12','0','20','2','22','0','5') into v_hcxz from dual ;
if v_hcxz!='5' then
insert into mododb_interface@to_xlt (PSNUM,BAROUT,COMMANDTYPE, BARAUTH,PRIORITY)
values ('86715'||v_dhhm,v_hcxz,'05','0',2);
end if ;
--来电显示
select decode(substr(v_ckfwxm,17,1),'3','0','2','1','5') into v_ldxs from dual ;
if v_ldxs!='5' then
insert into modss_interface@to_xlt ( psnum ,clip,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_ldxs,'06',2);
end if ;
--无条件转移
select decode(substr(v_ckfwxm,4,1),'3','0','2','1','5') into v_wtjzy from dual ;
if v_wtjzy!='5' then
insert into modss_interface@to_xlt (psnum,cfu,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_wtjzy,'06',2);
end if ;
--遇忙转移
select decode(substr(v_ckfwxm,24,1),'3','0','2','1','5') into v_ymzy from dual ;
if v_ymzy!='5' then
insert into modss_interface@to_xlt (psnum,cfb,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_ymzy,'06',2);
end if ;
--无应答转移
select decode(substr(v_ckfwxm,23,1),'3','0','2','1','5') into v_wydzy from dual ;
if v_wydzy!='5' then
insert into modss_interface@to_xlt (psnum,cfnry,cfnrc,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_wydzy,v_wydzy,'06',2);
end if ;
--呼叫等待
select decode(substr(v_ckfwxm,7,1),'3','0','2','1','5') into v_hjdd from dual ;
if v_hjdd!='5' then
insert into modss_interface@to_xlt (psnum,cw,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_hjdd,'06',2);
end if ;
--免打扰服务
select decode(substr(v_ckfwxm,11,1),'3','0','2','1','5') into v_mdrfw from dual ;
if v_mdrfw!='5' then
insert into modss_interface@to_xlt (psnum,ffi,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_mdrfw,'06',2);
end if ;
--缺席用户服务
select decode(substr(v_ckfwxm,12,1) ,'3','0','2','1','5') into v_qxyhfw from dual ;
if v_qxyhfw!='5' then
insert into modss_interface@to_xlt (psnum,ass,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_qxyhfw,'06',2);
end if ;
--闹钟服务
select decode(substr(v_ckfwxm,8,1),'3','0','2','1','5') into v_nzfw from dual ;
if v_nzfw!='5' then
insert into modss_interface@to_xlt (psnum,asnz,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_nzfw,'06',2);
end if ;
--短信服务
select decode(substr(v_ckfwxm,26,1),'3','00','2','11','5') into v_dxxfw from dual ;
select decode(substr(v_ckfwxm,26,1),'3','0','2','0','0') into v_misscall from dual ;
if v_dxxfw!='5' then
INSERT INTO MODTEL_INTERFACE@TO_XLT(PSNUM,SPEECH,SM,SMTYPE,SCADDR,MISSCALL,MISSCALLTYPE,WRITEDATE,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,'11' ,v_dxxfw,'2','86710003000',v_misscall,'1',sysdate,'08',4) ;
end if ;
end if ;
--如果是其他业务
insert into dlb_znw_log select * from dlb_znw where rowid=v_rowid;
-- 处理掉dlb_znw 里面的数据
DELETE FROM dlb_znw where rowid=v_rowid;
--delete from dlb_znw where slbh=row_znw.SLbh and gdbh=row_znw.gdbh;
commit ;
end loop ;
close cur_znw;
COMMIT;
exception
when others then
begin
rollback;
close CUR_ZNW ;
return ;
end ;
end pro_xlttoznw ;
--FA4H 停机保号
if (v_ywlb='FA4H')
then
select Ydhhm into v_dhhm from GDB_DH where slbh=v_slbh ;
insert into MODFLAG_INTERFACE@to_xlt (psnum,newflag,commandtype,priority)
values ('86715'||v_dhhm ,'1','03',2);
end if ;
--FA4I 停机后复话
if (v_ywlb='FA4I')
then
select Ydhhm into v_dhhm from GDB_DH where slbh=v_slbh ;
insert into MODFLAG_INTERFACE@to_xlt (psnum,newflag,commandtype,priority)
values ('86715'||v_dhhm ,'0','03',2);
end if ;--04 用户换机(更换PSID) 前台换机--用户换号
if (v_ywlb='FA55')
THEN
SELECT YDHHM ,XDHHM INTO V_DHHM, V_XDHHM FROM GDB_DH WHERE SLBH=V_SLBH ;
INSERT INTO MODPSNUM_INTERFACE@TO_XLT(OLDPSNUM,NEWPSNUM,COMMANDTYPE,PRIORITY )
VALUES ('86715'||v_dhhm,'86715'||V_Xdhhm,'07','05');
UPDATE XLT_JQM SET DHHM=V_XDHHM WHERE DHHM=V_DHHM;
END IF ;--暂时不写
if (v_ywlb='FA42') --如果是增减程控服务用户
then
select Ydhhm into v_dhhm from GDB_DH where slbh=v_slbh ;
select ckfwxm into v_ckfwxm from gdb_dh where slbh=v_slbh ;
--05 修改ODB数据 长途限制和呼入限制
select decode(substr(v_ckfwxm,1,2),'13','2','33','3','30','3','12','0','20','2','22','0','5') into v_hcxz from dual ;
if v_hcxz!='5' then
insert into mododb_interface@to_xlt (PSNUM,BAROUT,COMMANDTYPE, BARAUTH,PRIORITY)
values ('86715'||v_dhhm,v_hcxz,'05','0',2);
end if ;
--来电显示
select decode(substr(v_ckfwxm,17,1),'3','0','2','1','5') into v_ldxs from dual ;
if v_ldxs!='5' then
insert into modss_interface@to_xlt ( psnum ,clip,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_ldxs,'06',2);
end if ;
--无条件转移
select decode(substr(v_ckfwxm,4,1),'3','0','2','1','5') into v_wtjzy from dual ;
if v_wtjzy!='5' then
insert into modss_interface@to_xlt (psnum,cfu,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_wtjzy,'06',2);
end if ;
--遇忙转移
select decode(substr(v_ckfwxm,24,1),'3','0','2','1','5') into v_ymzy from dual ;
if v_ymzy!='5' then
insert into modss_interface@to_xlt (psnum,cfb,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_ymzy,'06',2);
end if ;
--无应答转移
select decode(substr(v_ckfwxm,23,1),'3','0','2','1','5') into v_wydzy from dual ;
if v_wydzy!='5' then
insert into modss_interface@to_xlt (psnum,cfnry,cfnrc,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_wydzy,v_wydzy,'06',2);
end if ;
--呼叫等待
select decode(substr(v_ckfwxm,7,1),'3','0','2','1','5') into v_hjdd from dual ;
if v_hjdd!='5' then
insert into modss_interface@to_xlt (psnum,cw,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_hjdd,'06',2);
end if ;
--免打扰服务
select decode(substr(v_ckfwxm,11,1),'3','0','2','1','5') into v_mdrfw from dual ;
if v_mdrfw!='5' then
insert into modss_interface@to_xlt (psnum,ffi,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_mdrfw,'06',2);
end if ;
--缺席用户服务
select decode(substr(v_ckfwxm,12,1) ,'3','0','2','1','5') into v_qxyhfw from dual ;
if v_qxyhfw!='5' then
insert into modss_interface@to_xlt (psnum,ass,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_qxyhfw,'06',2);
end if ;
--闹钟服务
select decode(substr(v_ckfwxm,8,1),'3','0','2','1','5') into v_nzfw from dual ;
if v_nzfw!='5' then
insert into modss_interface@to_xlt (psnum,asnz,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,v_nzfw,'06',2);
end if ;
--短信服务
select decode(substr(v_ckfwxm,26,1),'3','00','2','11','5') into v_dxxfw from dual ;
select decode(substr(v_ckfwxm,26,1),'3','0','2','0','0') into v_misscall from dual ;
if v_dxxfw!='5' then
INSERT INTO MODTEL_INTERFACE@TO_XLT(PSNUM,SPEECH,SM,SMTYPE,SCADDR,MISSCALL,MISSCALLTYPE,WRITEDATE,COMMANDTYPE,PRIORITY)
values ('86715'||v_dhhm,'11' ,v_dxxfw,'2','86710003000',v_misscall,'1',sysdate,'08',4) ;
end if ;
end if ;
--如果是其他业务
insert into dlb_znw_log select * from dlb_znw where rowid=v_rowid;
-- 处理掉dlb_znw 里面的数据
DELETE FROM dlb_znw where rowid=v_rowid;
--delete from dlb_znw where slbh=row_znw.SLbh and gdbh=row_znw.gdbh;
commit ;
end loop ;
close cur_znw;
COMMIT;
exception
when others then
begin
rollback;
close CUR_ZNW ;
return ;
end ;
end pro_xlttoznw ;
不过你的过程怎么没有捕捉异常?还有异常处理呢?
begin
insert into mododb_interface@to_xlt (PSNUM,BAROUT,COMMANDTYPE, BARAUTH,PRIORITY)
values ('86715'||v_dhhm,v_hcxz,'05','0',2);
end if ;
。
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR( -20024, SQLERRM) ;
end;
BEGIN
select bwzx,bwzy INTO bwzx,bwzy from oa_pvt03 where bbh = bbbh and hxh = j ;
EXCEPTION
when no_data_found then
raise_application_error( -20563, '提示:归属定义出错: '||key||', 没有找到该行该列的报表数据数据,bbh='||TO_CHAR(bbbh)||',hxh='||TO_CHAR(j)||',lxh='||TO_CHAR(i)||'@' );
when others then
raise_application_error( -20564, sqlerrm );
END;
Cause: An update occurred at a coordinated database without the coordinator
beginning a distributed transaction. This may happen if a stored procedure
commits and then performs updates, and the stored procedure is invoked
remotely. It could also happen if an external transaction monitor violates the XA
protocol.
Action: If the cause is the former, check that any commit is not followed by an
update.check that any commit is not followed by an update.樓主我幫你檢查了一下,在過程的最後有點問題
commit ;
end loop ;
close cur_znw;
COMMIT; --這兒
exception
when others then
begin
rollback; --這兒
close CUR_ZNW ;
return ;
end ;
end pro_xlttoznw ;你最好把過程執行按照LGQDUCKY(飘)的說法,將錯誤的對應程序段找出來