支持A3版本定购关系割接到A4及其以上版本
运行一个shell脚本(支持suselinux),完成功能:读取订购关系文件(A3版本中导出的)，并解析文件中字段调用pro入A4库
需求描述：
输入参数为spid，productid，定购还是退定（1定购，0退定）:1表示将数据放到订购表中，0表示将数据放到历史订购表中（根据当前的实际月份来放到对应的历史月份表中），用户文件路径[须放到执行的本地目录]
处理：
1.把用户文件路径中的用户号码（不带86），导入到用户基本信息表（susr_basic）中，usercode使用文件中的字段，其它字段使用数据库的默认值。如果用户号码存在则不处理。
2.根据usercode(从用户基本信息表中得)，spid，productid生成正常的初始批量定购关系(针对单个产品)。
插入s200_user_subscription表，不触发业务，不通知用户，不通知sp。
用户号码文件格式：（对应字段是usercode）
13951683342
13951683342
13012345678
13951683342
13751683342
shell的执行过程：
此shell命名为user.sh,先执行user.sh(./user.sh),其会在当前suse的目录,形如:zxin10@linux:~/shell_1>,shell_1为新建目录将外部提供的文件FTP到A4所在suse的shell_1/data中(data是执行user.sh生成的目录).接着执行./user_1.sh 用Oracle存储过程完成整个流程.
shell实现
#**********************************************************************
# 用户号码文件sqlpload到A4下的V3_SUB_1表中(自定义表，和文件字段一致)
#**********************************************************************
# 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函数生成ctl文件
create_customer_file()
{
echo "LOAD DATA
INFILE '$SQLLDR_DIR/$SQLLDR_DATA_DIR/$1'
INTO TABLE ZXDBM_ISMP.V3_SUB_1
APPEND
FIELDS TERMINATED BY \"\"
TRAILING NULLCOLS
(USERCODE)
" >$SQLLDR_DIR/$SQLLDR_CONTROL_DIR/$1.ctl
}
#表示命令后面带的参数个数1
if [ $# -lt 1 ]
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_1表："
date
for file in $( ls *.all 2>/dev/null )
do
if [ -s $file ]
then
echo "正在导入文件[$file] 
"
create_customer_file $file
#调用Oracle的sqlload函数导入数据
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#*****************************************************************************
# 调用V3_SUB_FETCH_1存储过程将A4下V3_SUB_1表中的数据迁移到A4下susr_basic表中
#*****************************************************************************
#!/bin/sh
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_1(ret);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Result='||ret); --返回1即成功
DBMS_OUTPUT.NEW_LINE;
END;
/
exit;
"
EOF
}
run_sub;
# end.
#**************************************************************************************************
# 调用V3_SUB_FETCH_2存储过程根据入参以及V3_SUB_1表中的用户号码批量插入订购关系表生成原始订购关系
#**************************************************************************************************
#!/bin/sh
DB_USER=zxdbm_ismp #DB USER
DB_PWD=zxin_smap #DB PASSWORD
DB_SERV=zx10_40_43_133 #DB SERVICE NAME
run_sub_1()
{
CPID=$v_cpid
PRODUCTID=$v_productid
FLAG=$v_flag
sqlplus $DB_USER/$DB_PWD@$DB_SERV <<EOF
echo "
set serveroutput on
DECLARE
ret number :=0;
BEGIN
V3_SUB_FETCH_2('${CPID}','${PRODUCTID}','${FLAG}',ret);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Result='||ret); --返回*即成功
DBMS_OUTPUT.NEW_LINE;
END;
/
exit;
"
EOF
}
v_cpid=$1
v_productid=$2
v_flag=$3
run_sub_1;
# end.
存储过程执行流程：
中间表
zxdbm_ismp.v3_sub_1 存储外部文件sqlload后的数据对应的中间表
zxdbm_ismp.v3_sub_2 存储针对批量生成订购记录时需要的数据
zxdbm_ismp.v3_sub_3 存储针对批量生成退定记录时需要的数据，是v3_sub_2的副本
执行user.sh调用V3_SUB_FETCH_1将符合条件的号码入用户表，不符合条件的入log表，最后后将v3_sub_1数据清空。
执行user_1.sh调用V3_SUB_FETCH_2后，如果是订购记录生成操作，生成批量的订购记录，执行完该存储过程后将v3_sub_2清空;如果是退定记录生成操作，同样，生成完批量的退定记录后将v3_sub_3数据清空。
存储过程实现
[一]游标扫描用户号码中间表
******************************************************************************
V3_SUB_FETCH_1
实现：将用户文件中的用户号码不带86的号导入用户基本信息表，用户号码存在则不处理
******************************************************************************
CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_1
-- 完成功能：将用户文件中字段存储到temp表，再将temp表数据转到用户表
(
v_retvalue OUT VARCHAR2
)
AS
v_usercode varchar2(100);
v_sub varchar2(40);
v_userindex number;
v_exist number;
v_message varchar2(100);
countnum number := 1;
--执行insert语句时返回出来的usercode值
v_new varchar2(100);
type ref_cursor_type is REF CURSOR;
cursor_select ref_cursor_type;
select_cname varchar2(1000);
BEGIN
select_cname:='select usercode from zxdbm_ismp.v3_sub_1';
Open cursor_select For select_cname;
Fetch cursor_select into v_usercode;
While cursor_select%Found
Loop
--针对v3_sub_1表中的记录数，当循环到大于10000时，commit掉缓冲区中的数据,同时countnum归1
if countnum > 10000 then
begin
commit;
countnum := 1;
end;
end if;
begin
--获得userindex
zxdbm_ismp.sp_get_next_seq('susr_basic',v_userindex);
--获得customerindex
--zxdbm_ismp.sp_getmaxvalue('susr_info',1,v_customerindex);
--不带86且用户号码存在则不处理
v_sub := substr(v_usercode, 1, 2);
if v_sub <> '86' then
--处理重复号码
begin
select count(*) into v_exist from susr_basic where usercode = v_usercode;
if(v_exist >0) then--号码已经存在用表中
v_message := sqlcode ||':'|| sqlerrm;
--将重复号码进日志表
insert into v3_sub_1_log values('zxdbm_ismp.susr_basic',v_message,sysdate,v_usercode);
commit;
else
--新号码则入用户表,同时将此号码信息返回出来
insert into zxdbm_ismp.susr_basic(userindex,usercode,customerindex) values(v_userindex,v_usercode,v_userindex) returning usercode into v_new;
--将上一句返回出来的值实时insert到v3_sub_2中间表和v3_sub_2表中,供下一个存储过程订购和退定操作用
insert into zxdbm_ismp.v3_sub_2 values(v_new);--存储订购操作需要调用的号码
insert into zxdbm_ismp.v3_sub_3 values(v_new);--存储订购操作后，再针对生成的订购关系记录再退定操作时调用的号码副本
--同时添加客户表信息
insert into zxdbm_ismp.susr_info(customerindex,customerid,userpwd,provid) values(v_userindex,v_userindex,'111111','074');
--将上面5个sql语句做事务处理
commit;
end if;
end;
else
--带86的号码进日志表
v_message := sqlcode ||':'|| sqlerrm;
insert into v3_sub_1_log values('zxdbm_ismp.susr_basic',v_message,sysdate,v_usercode);
commit;
end if;
exception when others then --号码不存在抛出异常
v_message := sqlcode ||':'|| sqlerrm;--记录错误日志
insert into v3_sub_1_log values('zxdbm_ismp.susr_basic',v_message,sysdate,v_usercode);
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.v3_sub_1 where rownum < 5000;
commit;
exit when sql%rowcount =0;
end loop;
exception when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--将某个时间delete中间表中的某个用户号码记录时出现异常
insert into v3_sub_1_log values('zxdbm_ismp.v3_sub_2',v_message,sysdate,v_usercode);
commit;
end;
v_retvalue :='the end of importing userinfo';
end V3_SUB_FETCH_1;
[二]批量数据insert into select .. from
CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_1
-- 完成功能：将用户文件中字段存储到temp表，再将temp表数据转到用户表
(
v_retvalue out varchar2
)
AS
v_message varchar2(100);
--v_exist number;
begin
begin
--先将v3_sub_1表中的不带86以及是新号码的insert到v3_sub_2
insert into zxdbm_ismp.v3_sub_2(usercode)
select t1.usercode from v3_sub_1 t1
where not exists (select t2.usercode from susr_basic t2
where t1.usercode = t2.usercode ) and substr(t1.usercode,1,2) <> '86';
--将带86以及已经存在的号码插到log表
--distinct过滤掉重复的86号码记录
--'zxdbm_ismp.susr_basic','fail',sysdate做特定值insert 到v3_sun_1_log表中
insert into v3_sub_1_log
select distinct 'zxdbm_ismp.susr_basic','fail',sysdate,t1.usercode from v3_sub_1 t1,susr_basic t2
where t1.usercode = t2.usercode or substr(t1.usercode,1,2) = '86';
--将v3_sub_2中数据copy一份到v3_sub_3表,在退定操作时用到
insert into zxdbm_ismp.v3_sub_3 select * from zxdbm_ismp.v3_sub_2;
--将v3_sub_2表中数据insert到v3_sub_4用户表(只有userindex,usercode两字段)中
insert into zxdbm_ismp.v3_sub_4
select zxdbm_200.sp_getmaxvalue_func('susr_basic',1),usercode from zxdbm_ismp.v3_sub_2;
--将v3_sub_4表中数据copy一份到zxdbm_ismp.susr_basic表中
insert into zxdbm_ismp.susr_basic(userindex,usercode)
select * from zxdbm_ismp.v3_sub_4;
--同时添加客户表信息,保证插入的记录数和v3_sub_2用户表中号码记录数一致,并且susr_info.customerindex = susr_basic.userindex
--'111111','074'做常数定值insert 到 zxdbm_ismp.susr_info中
insert into zxdbm_ismp.susr_info(customerindex,customerid,userpwd,provid)
select userindex,userindex,'111111','074' from zxdbm_ismp.v3_sub_4;
commit;
exception when others then --执行sql异常情况
rollback;
v_message := sqlcode ||':'|| sqlerrm;--记录异常日志
insert into v3_sub_1_log values('sql',v_message,sysdate,'commit sqls fail');
commit;
end;
--每次将中间表v3_sub_1和v3_sub_4数据清空
begin
loop
delete from zxdbm_ismp.v3_sub_1 where rownum < 5000;
commit;
exit when sql%rowcount =0;
end loop;
exception when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--将某个时间delete中间表中的某个用户号码记录时出现异常
insert into v3_sub_1_log values('zxdbm_ismp.v3_sub_2',v_message,sysdate,'clear v3_sub_1 exception');
commit;
end;
begin
loop
delete from zxdbm_ismp.v3_sub_4 where rownum < 5000;
commit;
exit when sql%rowcount =0;
end loop;
exception when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--将某个时间delete中间表中的某个用户号码记录时出现异常
insert into v3_sub_1_log values('zxdbm_ismp.v3_sub_4',v_message,sysdate,'clear v3_sub_4 exception');
commit;
end;
v_retvalue :='the end of importing userinfo';
end V3_SUB_FETCH_1;
sp_getmaxvalue_func的函数原型：
create or replace function zxdbm_200.sp_getmaxvalue_func (
i_sequencename in zxdbm_ismp.ssys_sequence.sequencename%type,
i_step in zxdbm_ismp.ssys_sequence.curval%type
)
return number
as
pragma autonomous_transaction;
v_maxvalue zxdbm_ismp.ssys_sequence.curval%type;
v_maxval zxdbm_ismp.ssys_sequence.maxval%type;
v_cancyc zxdbm_ismp.ssys_sequence.cancyc%type;
v_retvalue number(10);
begin
v_maxvalue := 0;
v_cancyc := 0;
v_maxval := 2140000000;
update zxdbm_ismp.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
v_retvalue := 0;
else
if (v_maxvalue >= v_maxval) then
if (v_cancyc = 1) then
update zxdbm_ismp.ssys_sequence set curval = minval + i_step where sequencename = i_sequencename returning curval into v_maxvalue;
v_retvalue := v_maxvalue - i_step;
else
v_retvalue := 0;
end if;
else
v_retvalue := v_maxvalue - i_step;
end if;
end if;
commit;
return v_retvalue;
end;
*****************************************************************************************************
V3_SUB_FETCH_2
功能：根据外部的cpid,productid,flag[订购or退定]批量生成原始订购或退定记录
*****************************************************************************************************
CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_2
(
v_cpid VARCHAR2,
v_productid VARCHAR2,
v_flag NUMBER, --1为订购，0为退订
v_retvalue OUT VARCHAR2
)
AS
v_curdate varchar(40) := to_char(sysdate ,'yyyymmddhh24miss'); --当前时间
v_curmonth NUMBER;
v_subscriptionindex NUMBER;
v_usercode varchar2(40);
v_servicetype NUMBER;
v_serviceindex NUMBER;
v_serviceid varchar2(40);
v_productindex NUMBER;
v_cpindex NUMBER;
v_subscribemode NUMBER;
v_subscribechannel NUMBER;
v_userindex NUMBER;
v_message varchar2(100);
v_exist number;
countnum number := 1;
type ref_cursor_type is REF CURSOR;
cursor_select ref_cursor_type;
select_cname varchar2(1000);
BEGIN
v_subscribemode := 5;--营运商定购
v_subscribechannel := 4;--管理员
v_curmonth := to_number(substr(v_curdate,5,2));--月份
begin
--1 短信 2 彩信 从zxdbm_ismp.v_ssrv_product视图查询servicetype
--执行下面这句sql语句查询后，v_servicetype为null时，会转向异常区域
select servicetype into v_servicetype from zxdbm_ismp.v_ssrv_product where productid = v_productid;
exception when others then
--将cpid,productid和flag以及当前操作的视图 入日志表 以便跟踪是哪个入参没有查询到servicetype
v_message := sqlcode ||':'|| sqlerrm;
insert into v3_sub_2_log values('zxdbm_ismp.v_ssrv_product',v_message,sysdate,v_cpid,v_productid,v_flag);
commit;
v_retvalue :='not find servicetype with productid ';
return;
end;
--订购操作
if v_flag =1 then
--从中间表v3_sub_2中取符合条件的用户号码
select_cname := 'select usercode from zxdbm_ismp.v3_sub_2';
--退定操作
elsif v_flag =0 then
--从中间表v3_sub_3中取符合条件的用户号码
select_cname := 'select usercode from zxdbm_ismp.v3_sub_3';
else
v_retvalue :='please append 0 or 1';
return;--跳出循环结束
end if;
--游标打开失败后的异常扑获写法?
Open cursor_select For select_cname;
Fetch cursor_select into v_usercode;
While cursor_select%Found
Loop
--针对v3_sub_1表中的记录数，当循环到大于10000时，commit掉缓冲区中的数据,同时countnum归1
if countnum > 10000 then
begin
commit;
countnum := 1;
end;
end if;
begin
--获得订购Subscriptionindex ,Subscriptionid = Subscriptionindex
zxdbm_ismp.sp_getmaxvalue ('slp_order_prdindex',1,v_subscriptionindex);
end;
begin
--短信
if(v_servicetype = 1) then
begin
--获得serviceindex,serviceid和productindex, 业务和产品此时是1对1关系
select serviceindex,serviceid,productindex into v_serviceindex,v_serviceid,v_productindex from zxdbm_200.s200_product where productid = v_productid;
exception when others then
v_message := sqlcode ||':'|| sqlerrm;
--将cpid,productid和flag以及当前操作的产品表入日志表以便跟踪是哪条记录没有成功生成订购关系
insert into v3_sub_2_log values('zxdbm_200.s200_product',v_message,sysdate,v_cpid,v_productid,v_flag);
commit;
v_retvalue :='not find serviceindex serviceid productindex values with productid';
return;--找不到入参的关联键直接return
end;
--彩信
elsif(v_servicetype = 2) then
begin
--获得serviceindex,serviceid和productindex, 业务和产品此时是1对1关系
select serviceindex,serviceid,productindex into v_serviceindex,v_serviceid,v_productindex from zxdbm_500.s500_product whe