需求描述：
支持A3版本定购关系割接到A4及其以上版本
运行一个shell脚本(支持suselinux),完成功能:读取订购关系文件(A3版本中导出的)，并解析文件中字段调用pro入A4库
要求A3能够把定购关系导入到A4版本的sms业务中，导入范围是正常的定购关系，暂停的定购关系
spms_sub_2007112910_*.all 购关系文件格式：
neusoft_files_sync|13951683342|srv13|32041|20071124145057|20071124145057|3
neusoft_files_sync|13951683342|srv12|32041|20071126154239|20071126154239|3
neusoft_files_sync|13012345678|srv32|32043|20071123161142|20071123161142|3
neusoft_files_sync|13951683342|srv42|sp4|20071122200155|20071122200155|3
neusoft_files_sync|13751683342|srv42|sp4|20071122192932|20071122192932|3
7个字段分别对应 subscriber usercode payuser serviceid cpid createtime endtime subscribechannel其中，usercode和payuser相同.
实现思路:
首先根据提供的订购关系文件,利用shell将其sqlload到A4库中的V3_SUB(自定义表,段和文件字段一致)
shell:
#**********************************************************************
# 订购关系文件数据导入
#**********************************************************************
# global variables
SELF_NAME=`basename $0`
SQLLDR_CONTROL_DIR="ctl"
SQLLDR_LOG_DIR="log"
SQLLDR_BAD_DIR="bad"
SQLLDR_DATA_DIR="data"
TNSNAME="@zx10_40_43_133"
create_customer_file()
{
echo "LOAD DATA
INFILE '$SQLLDR_DIR/$SQLLDR_DATA_DIR/$1'
INTO TABLE ZXDBM_ISMP.V3_SUB
APPEND
FIELDS TERMINATED BY \"|\"
TRAILING NULLCOLS
(SUBSCRIBER,USERCODE,SERVICEID,CPID,CREATETIME,ENDTIME,SUBSCRIBECHANNEL)
" >$SQLLDR_DIR/$SQLLDR_CONTROL_DIR/$1.ctl
}
#表示命令后面带的参数个数1
if [ $# -lt 1 ] # min 1 options.
then
SQLLDR_DIR=$(pwd)
else
SQLLDR_DIR=$1
fi
env |grep ORACLE_HOME 1>/dev/null 2>&1
if [ $? -ne 0 ]
then
echo "ERROR:ORACLE_HOME not set." >&2
exit 2
fi
if [ ! -d $SQLLDR_DIR/$SQLLDR_CONTROL_DIR ]
then
mkdir $SQLLDR_DIR/$SQLLDR_CONTROL_DIR
fi
if [ ! -d $SQLLDR_DIR/$SQLLDR_BAD_DIR ]
then
mkdir $SQLLDR_DIR/$SQLLDR_BAD_DIR
fi
if [ ! -d $SQLLDR_DIR/$SQLLDR_LOG_DIR ]
then
mkdir $SQLLDR_DIR/$SQLLDR_LOG_DIR
fi
if [ ! -d $SQLLDR_DIR/$SQLLDR_DATA_DIR ]
then
mkdir $SQLLDR_DIR/$SQLLDR_DATA_DIR
fi
cd $SQLLDR_DIR/$SQLLDR_DATA_DIR
echo "开始导入v3_sub表："
date
for file in $( ls *.all 2>/dev/null )
do
if [ -s $file ]
then
echo "正在导入文件[$file] 
"
create_customer_file $file
sqlldr zxdbm_ismp/zxin_smap$TNSNAME control="$SQLLDR_DIR/$SQLLDR_CONTROL_DIR/$file.ctl" log="$SQLLDR_DIR/$SQLLDR_LOG_DIR/$file.log" bad="$SQLLDR_DIR/$SQLLDR_BAD_DIR/$file.bad" errors=50000000 >/dev/null 2>&1
fi
done
echo "导入结束。"
date
#**********************************************************************
# shell调用存储过程迁移数据
#**********************************************************************
DB_USER=zxdbm_ismp #DB USER
DB_PWD=zxin_smap #DB PASSWORD
DB_SERV=zx10_40_43_133 #DB SERVICE NAME
run_sub()
{
sqlplus $DB_USER/$DB_PWD@$DB_SERV <<EOF
echo "
set serveroutput on
DECLARE
ret number :=0;
BEGIN
V3_SUB_FETCH(ret);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Result='||ret); --返回1成功,0失败
DBMS_OUTPUT.NEW_LINE;END;
/
exit;
"
EOF
}
run_sub;
# end.
shell执行过程如下:
此shell命名为user.sh,先执行user.sh(./user.sh),其会在当前suse的目录,形如:zxin10@linux:~/shell>,shell为新建目录
将spms_sub_2007112910_*.all 文件FTP到A4所在suse的shell/data中(data是执行user.sh生成的目录)
再次执行./user.sh 调用Oracle存储过程将A4下的V3_SUB表中的数据迁移到s200_user_subscription订购关系表中
存储过程实现：
***************************************************************************************************************************
V3_SUB_FETCH功能：
V3_SUB表中记录迁移到s200_user_subscription表中，同时根据V3_SUB表中的cpid和serviceid找到s200_user_subscription表中外键所需值
***************************************************************************************************************************
CREATE OR REPLACE PROCEDURE V3_SUB_FETCH
(
v_retvalue OUT VARCHAR2
)
AS
v_subscriptionindex NUMBER;
V_SUBSCRIBER VARCHAR2(100);
V_USERCODE VARCHAR2(100);
V_SERVICEID VARCHAR2(100);
V_CPID VARCHAR2(100);
V_CREATETIME VARCHAR2(100);
V_ENDTIME VARCHAR2(100);
V_SUBSCRIBECHANNEL NUMBER;
--fore
v_servicetype NUMBER;
v_subcapability NUMBER;
v_serviceindex NUMBER;
v_productindex NUMBER;
v_productid VARCHAR2(100);
v_cpindex NUMBER;
v_i number;
v_message VARCHAR2(100);
type ref_cursor_type is REF CURSOR;
cursor_select ref_cursor_type;
select_cname varchar2(1000);
countnum number := 1;
begin
select_cname:='select SUBSCRIBER,USERCODE,SERVICEID,CPID,CREATETIME,ENDTIME,SUBSCRIBECHANNEL from zxdbm_ismp.v3_sub';
Open cursor_select For select_cname;
Fetch cursor_select into V_SUBSCRIBER,V_USERCODE,V_SERVICEID,V_CPID,V_CREATETIME,V_ENDTIME,V_SUBSCRIBECHANNEL;
While cursor_select%Found --当循环结束后直接转到Close cursor_select
Loop
--针对v3_sub表中的记录数，当循环到大于10000时，commit掉缓冲区中的数据,同时countnum归1
if countnum > 10000 then
begin
commit;
countnum := 1;
end;
end if;
begin
--获得subscriptionindex以及subscriptionid, subscriptionindex = subscriptionid
zxdbm_ismp.sp_getmaxvalue ('slp_order_prdindex',1,v_subscriptionindex);
end;
begin
--获得servicetype和subcapability
select servicetype, subcapability into v_servicetype, v_subcapability from zxdbm_200.s200_sp_service_type where cpid=V_CPID;
exception when others then
v_message := sqlcode ||':'|| sqlerrm;
--准确定位是v3_sub中哪条记录没有导入到V4的订购关系表中去，是针对哪个关的关联查询失败的，是什么时候
insert into v3_sub_log values('zxdbm_200.s200_sp_service_type',v_message,sysdate,V_CPID,V_SERVICEID,V_USERCODE);
commit;
goto next_home;
end;
begin
--获得serviceindex
select serviceindex into v_serviceindex from zxdbm_200.s200_service t where serviceid = V_SERVICEID;
exception when others then
v_message := sqlcode ||':'|| sqlerrm;
--准确定位是v3_sub中哪条记录没有导入到V4的订购关系表中去，是针对哪个关的关联查询失败的，是什么时候
insert into v3_sub_log values('zxdbm_200.s200_service',v_message,sysdate,V_CPID,V_SERVICEID,V_USERCODE);
commit;
goto next_home;
end;
begin
--获得productindex和productid
select productindex, productid into v_productindex, v_productid from zxdbm_200.s200_product where serviceid = V_SERVICEID;
exception when others then
v_message := sqlcode ||':'|| sqlerrm;
--准确定位是v3_sub中哪条记录没有导入到V4的订购关系表中去，是针对哪个关的关联查询失败的，是什么时候
insert into v3_sub_log values('zxdbm_200.s200_product',v_message,sysdate,V_CPID,V_SERVICEID,V_USERCODE);
commit;
goto next_home;
end;
begin
--获得cpindex
select cpindex into v_cpindex from zxdbm_ismp.scp_basic where cpid = V_CPID;
exception when others then
v_message := sqlcode ||':'|| sqlerrm;
--准确定位是v3_sub中哪条记录没有导入到V4的订购关系表中去，是针对哪个关的关联查询失败的，是什么时候
insert into v3_sub_log values('zxdbm_ismp.scp_basic',v_message,sysdate,V_CPID,V_SERVICEID,V_USERCODE);
commit;
goto next_home;
end;
begin
--usercode = payuser字段,subscriber为文件第一个字段
insert into zxdbm_200.s200_user_subscription(subscriptionindex,SUBSCRIPTIONID,servicetype,subcapability,serviceindex,serviceid,productindex,productid,cpindex,cpid,SUBSCRIBECHANNEL,SUBSCRIBER,USERCODE,PAYUSER,ENDTIME,CREATETIME) values(v_subscriptionindex,v_subscriptionindex,v_servicetype,v_subcapability,v_serviceindex,V_SERVICEID,v_productindex,v_productid,v_cpindex,V_CPID,V_SUBSCRIBECHANNEL,V_SUBSCRIBER,V_USERCODE,V_USERCODE,V_ENDTIME,V_CREATETIME);
commit;
exception when others then
v_message := sqlcode ||':'|| sqlerrm;
--准确定位是v3_sub中哪条记录没有导入到V4的订购关系表中去，是针对哪个关的关联查询失败的，是什么时候
insert into v3_sub_log values('zxdbm_200.s200_user_subscription',v_message,sysdate,V_CPID,V_SERVICEID,V_USERCODE);
commit;
goto next_home;
end;
countnum := countnum + 1;
<<next_home>>
Fetch cursor_select into V_SUBSCRIBER,V_USERCODE,V_SERVICEID,V_CPID,V_CREATETIME,V_ENDTIME,V_SUBSCRIBECHANNEL;
v_i := v_i+1;
End Loop;
Close cursor_select;
begin
loop
--每次将中间表v3_sub_1删除10000条记录
delete from zxdbm_ismp.v3_sub where rownum < 10000;
commit;
exit when sql%rowcount =0;
end loop;
exception when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--准确定位是v3_sub中哪条记录没有导入到V4的订购关系表中去，是针对哪个关的关联查询失败的，是什么时候
insert into v3_sub_log values('zxdbm_200.s200_user_subscription',v_message,sysdate,V_CPID,V_SERVICEID,V_USERCODE);
commit;
end;
v_retvalue := 'the end of importing user_subscription';--为1表示此次数据操作成功,PRO的值取决于循环中最后一条记录的操作成功与否
--exception
--when others then
--v_retvalue := 'the end';--为1表示此次数据操作成功,PRO的值取决于循环中最后一条记录的操作成功与否
end V3_SUB_FETCH;
*****************************************************************************************************************************************
sp_getmaxvalue 生成各表所需的index索引号
zxdbm_ismp.sp_getmaxvalue ('slp_order_prdindex',1,v_subscriptionindex);就是通过sequencename = slp_order_prdindex 来生成subscriptionindex
*****************************************************************************************************************************************
create or replace procedure sp_getmaxvalue (
i_sequencename in ssys_sequence.sequencename%type,--表字段类型
i_step in ssys_sequence.curval%type,
o_retvalue out ssys_sequence.curval%type
)
as
pragma autonomous_transaction;
v_maxvalue ssys_sequence.curval%type;
v_maxval ssys_sequence.maxval%type;
v_cancyc ssys_sequence.cancyc%type;
begin
v_maxvalue := 0;
v_cancyc := 0;
v_maxval := 2140000000;
update ssys_sequence set curval = curval + i_step where sequencename = i_sequencename returning curval,maxval,cancyc into v_maxvalue,v_maxval,v_cancyc;
if (sql%rowcount = 0) then
o_retvalue := 0;
else
if (v_maxvalue >= v_maxval) then
if (v_cancyc = 1) then
update ssys_sequence set curval = minval + i_step where sequencename = i_sequencename returning curval into v_maxvalue;
o_retvalue := v_maxvalue - i_step;
else
o_retvalue := 0;
end if;
else
o_retvalue := v_maxvalue - i_step;
end if;
end if;
commit;
return;
end;
SSYS_SEQUENCE表结构：
create table SSYS_SEQUENCE
(
SEQUENCENAME VARCHAR2(100) not null,
CURVAL NUMBER(10) default 1 not null,
MINVAL NUMBER(10) default 1 not null,
MAXVAL NUMBER(10) default 2140000000 not null,
CANCYC NUMBER(3) default 0 not null,
DESCRIPTION VARCHAR2(255) default ' ' not null
)
posted on 2007-12-01 11:27
cheng 阅读(938)
评论(0) 编辑 收藏 所属分类:
Oracle 、
Unix/Linux