shell脚本编写存储过程(mysql）

#!/bin/sh
--三个变量   数据库名   生成的作业ID  生成数据量

--如果变量输等于3 跳出

if [ $# -ne 3 ];then
    echo "usage: $0 <db_name> <id_mod> <job_cnt> "
    echo " sh $0 dc c_test 10 "
    exit 1
fi
--三个变量
db_name=$1
id_mod=$2
cnt=$3
#job_id=${id_mod}_${cnt}_xxxx; job_code=${id_mod}_xxxx_${cnt}
--生成数据前先把原始数据删除
echo "use ${db_name};
delete from DC_JOB     where job_id like '${id_mod}_%';
delete from DC_JOB_PRM where job_id like '${id_mod}_%';
delete from DC_JOB_REF where job_id like '${id_mod}_%' or ref_job_id like '${id_mod}_%';
#select  FROM_UNIXTIME(1435027500000 /1000 + $cnt *1000) ;
" >test.sql

--循环   如果cnt>0就继续生成语句
while [ $cnt -gt 0 ]
do
    
echo "
-- ${cnt}.
delete from DC_JOB where job_id in ('${id_mod}_${cnt}_touch','${id_mod}_${cnt}_scan','${id_mod}_${cnt}_rm');
commit;
INSERT INTO DC_JOB (JOB_ID, JOB_NAME, GROUP_ID, USER_ID, START_TIME_MODEL, RUN_LIMIT, JOB_CODE, JOB_PACKAGE, JOB_PATH       , JOB_VERSION, JOB_CMD, STATUS,CREATE_TIME, EXPIRED_TIME, HAVE_REF, RUN_REF_JOB_NUM, JOB_TYPE) VALUES
                          ('${id_mod}_${cnt}_touch', 'touch_1_per5min', 'group_month_t1',   11,  '0 0/5 * * * ?'  , 0, '${id_mod}_touch_${cnt}' , 'touch_0' , '/home/uschd/test_jobs', 'v1.00' , ''     , 0,NOW(), '2015-07-28 17:32:32', 0, 0, 2);
/*
INSERT INTO DC_JOB (JOB_ID, JOB_NAME, GROUP_ID, USER_ID, START_TIME_MODEL, RUN_LIMIT, JOB_CODE, JOB_PACKAGE, JOB_PATH       , JOB_VERSION, JOB_CMD, STATUS,CREATE_TIME, EXPIRED_TIME, HAVE_REF, RUN_REF_JOB_NUM, JOB_TYPE) VALUES
                          ('${id_mod}_${cnt}_scan', 'scan_1_per5min', 'group_month_t1',   11,  '0 0/5 * * * ?'  , 0, '${id_mod}_scan_${cnt}' , 'scan.sh' , '/home/uschd/test_jobs', 'v1.00' , 'sh'     , 0,NOW() ,'2015-07-28 17:32:32', 1, 1, 2);
INSERT INTO DC_JOB (JOB_ID, JOB_NAME, GROUP_ID, USER_ID, START_TIME_MODEL, RUN_LIMIT, JOB_CODE, JOB_PACKAGE, JOB_PATH       , JOB_VERSION, JOB_CMD, STATUS,CREATE_TIME, EXPIRED_TIME, HAVE_REF, RUN_REF_JOB_NUM, JOB_TYPE) VALUES
                          ('${id_mod}_${cnt}_rm', 'rm_1_per5min', 'group_month_t1',   11,  '0 0/5 * * * ?'  , 0, 'c_rm_${cnt}' , 'rm_data_tmp.sh' , '/home/uschd/test_jobs', 'v1.00' , 'sh'     , 0,NOW(), '2015-07-28 17:32:32', 1, 1, 2);
*/
-- ${cnt}
delete from DC_JOB_PRM where job_id in ('${id_mod}_${cnt}_touch','${id_mod}_${cnt}_scan','${id_mod}_${cnt}_rm');
 commit;
INSERT INTO DC_JOB_PRM (P_KEY, P_VALUE, USER_ID, JOB_ID, CREATE_TIME, SEQUENCE, PRM_TYPE, JOB_CODE) VALUES
        ('FILE_NAME', 'ttouch_1_per5min_${cnt}', 11, '${id_mod}_${cnt}_touch', '2015-05-28 10:03:17', 1,  'in', '${id_mod}_touch_${cnt}'),
    ('Data_Dir', '/home/uschd/data/tmp/_EXF', 11, '${id_mod}_${cnt}_touch', '2015-05-28 10:03:17', 2,  'in', '${id_mod}_touch_${cnt}');

/*
INSERT INTO DC_JOB_PRM (P_KEY, P_VALUE, USER_ID, JOB_ID, CREATE_TIME, SEQUENCE, PRM_TYPE, JOB_CODE) VALUES
        ('FILE_NAME', 'ttouch_1_per5min_${cnt}', 11, '${id_mod}_${cnt}_scan', '2015-05-28 10:03:17', 1,  'in', '${id_mod}_scan_${cnt}'),
    ('Data_Dir', '/home/uschd/data/tmp/_EXF', 11, '${id_mod}_${cnt}_scan', '2015-05-28 10:03:17', 2,  'in', '${id_mod}_scan_${cnt}');

INSERT INTO DC_JOB_PRM (P_KEY, P_VALUE, USER_ID, JOB_ID, CREATE_TIME, SEQUENCE, PRM_TYPE, JOB_CODE) VALUES
    ('Data_Dir', '/home/uschd/data/tmp/_TEST_EXF', 11, '${id_mod}_${cnt}_rm', '2015-05-28 10:03:17', 1,  'in', '${id_mod}_rm_${cnt}');

-- ${cnt}
                          delete from DC_JOB_REF where JOB_ID = '${id_mod}_${cnt}_scan' and REF_JOB_ID = '${id_mod}_${cnt}_touch' or
         JOB_ID = '${id_mod}_${cnt}_rm' and REF_JOB_ID = '${id_mod}_${cnt}_scan' ;
commit;
INSERT INTO DC_JOB_REF (JOB_ID, REF_JOB_ID) VALUES
    ('${id_mod}_${cnt}_scan','${id_mod}_${cnt}_touch'),
    ('${id_mod}_${cnt}_rm','${id_mod}_${cnt}_scan');
*/

" >> test.sql  


cnt=`expr $cnt - 1`

done



--向里面导入test.sql
mysql -uroot -p+password  -Ddc<test.sql

#rm -f test.sq