需求描述
输入参数：cpid，业务能力
根据用户号码文件把指定sp的业务能力(本工具只针对SMS)下定购关系删除，不管定购关系处于什么状态。并记录日志，以便以后备查。如果用户信息不存在则不处理。
工具支持aix，linux上的oracle数据库, 执行者：运营商管理员。
程序流程：执行linux for suse下的shell脚本调用sqlload将用户文件数据导入临时表zxdbm_ismp.file_usercode，再利用shell调用Oracle存储过程，根据外部入参和临时表记录对订购关系相关表记录进行批量删除。删除过程涉及到的表有：zxdbm_ismp.s200_user_subscription,zxdbm_ismp.ssrv_user_totalamount和zxdbm_ismp.ssrv_user_totalamount01到zxdbm_ismp.ssrv_user_totalamount12(共12张表，根据当前月份删除对应月份表)。
技术实现：采用游标对临时表zxdbm_ismp.file_usercode循环扫描，并结合入参cpid,srvtypeshortname(业务能力)对相关业务表进行记录删除。
shell实现在此省略，具体实现可以参考: http://www.blogjava.net/cheneyfree/archive/2007/12/01/164476.html
存储过程实现如下：(针对当月的累计记录删除运用动态SQL大大减少了工具的代码量)
CREATE OR REPLACE PROCEDURE delete_user_subscription
(
v_spid varchar2,
v_srvtypeshortname varchar2, --业务能力名
v_retvalue out varchar2
)
AS
v_userindex number;
v_curdate varchar(40) := to_char(sysdate ,'yyyymmddhh24miss'); --当前时间
v_servicetype number;
v_cpindex number;
v_table_totalamount varchar2(100); --累计表名
str_sql varchar2(1000);
v_curmonth number;
v_message varchar2(1000);
v_usercode varchar2(100);
countnum number := 1;
type ref_cursor_type is REF CURSOR;
cursor_select ref_cursor_type;
select_cname varchar2(1000);
begin
v_curmonth := lpad(to_number(substr(v_curdate,5,2)),2,'0');--月份2位,不足前补0,lpad函数转换大写
v_table_totalamount := 'zxdbm_ismp.ssrv_user_totalamount' || v_curmonth; --动态获得累计表名
--SMS
if(upper(v_srvtypeshortname) = 'SMS') then
v_servicetype := 1;
--srvtypeshortname为空或不存在
else
v_retvalue :='srvtypeshortname is not exist,please try again!';
return;
end if;
begin
--获得cpindex
select cpindex into v_cpindex from zxdbm_ismp.scp_basic where cpid = v_spid;
exception when others then
--入参数spid为空或无效（查询不到cpindex）
v_message := sqlcode ||':'|| sqlerrm;
insert into zxdbm_ismp.log_delete values(v_spid,v_usercode,v_srvtypeshortname,sysdate,v_message||'------'||'not find cpindex from scp_basic with spid!');
commit;
v_retvalue :='not find cpindex from scp_basic with spid,please try again!';
return;
end;
select_cname := 'select usercode from zxdbm_ismp.file_usercode';
Open cursor_select For select_cname;
Fetch cursor_select into v_usercode;
While cursor_select%Found Loop
--当循环到大于10000时，commit掉缓冲区中的数据,同时countnum归1
if countnum > 10000 then
begin
commit;
countnum := 1;
end;
end if;
begin
--获得userindex select条件不满足转异常
select userindex into v_userindex from zxdbm_ismp.susr_basic where usercode = v_usercode;
exception when others then
--不存在的号码insert到log_delete
v_message := sqlcode ||':'|| sqlerrm;
insert into zxdbm_ismp.log_delete values(v_spid,v_usercode,v_srvtypeshortname,sysdate,v_message||'------'||'this usercode is invalid!');
commit;
--异常后定位至下一个号码
goto next_home;
end;
begin
delete from zxdbm_ismp.ssrv_user_totalamount where userindex = v_userindex and cpindex = v_cpindex and servicetype = v_servicetype and amounttype <> 8;
--订购关系记录先备份后删除
if(v_servicetype = 1) then
--当select条件不满足转异常
insert into zxdbm_ismp.log_sms_user_subscription (select * from zxdbm_200.s200_user_subscription where cpid = v_spid and usercode = v_usercode and servicetype = v_servicetype);
--delete条件不满足时不转异常
delete from zxdbm_200.s200_user_subscription where cpid = v_spid and usercode = v_usercode and servicetype = v_servicetype;
--删除当前月份的累计记录
str_sql:='delete from'||' '||v_table_totalamount||' '||'where userindex =:userindex and cpindex =:cpindex and servicetype =:servicetype and amounttype <> 8';
execute immediate str_sql using v_userindex,v_cpindex,v_servicetype;
elsif(v_servicetype = 2) then
null;
else
null;
end if;
commit;
exception when others then
rollback;
v_message := sqlcode ||':'|| sqlerrm;
insert into zxdbm_ismp.log_delete values(v_spid,v_usercode,v_srvtypeshortname,sysdate,v_message||'------'||'sql exception!');
commit;
goto next_home;
end;
countnum := countnum + 1;
<<next_home>>
Fetch cursor_select into v_usercode;
End Loop;
Close cursor_select;
begin
loop
--每次将中间表v3_sub_1删除10000条记录
delete from zxdbm_ismp.file_usercode where rownum < 5000;
commit;
exit when sql%rowcount =0;
end loop;
exception when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--将某个时间delete中间表中的用户号码记录时出现异常
insert into zxdbm_ismp.log_delete values(' ',' ',v_srvtypeshortname,sysdate,v_message||'------'||'delete temp table log_delete exception!');
commit;
end;
v_retvalue :='produce is end!';
end ;
posted on 2007-12-19 15:04
cheng 阅读(965)
评论(0) 编辑 收藏 所属分类:
Oracle 、
Unix/Linux