shell

分享一波用shell写的脚本语言，从数据库导文件并且按照一定格式

由于银行要求格式是:
字段A|字段B 然后是gbk编码的，我们数据库是utf8 ，文件名以.uml结尾

#!/bin/bash
test(){

//创建文件夹把五张表都放进去
mkdir customerExport 

db2 connect to sunpls user db2icomm using db2icomm
db2 "export to customerExport/a.txt of del select  customer_no,'|', '303100|' , custom_name,'|', '|' , '|' , '|' , '|' , id_type,'|' , id_no,'|' , id_start_date,'|' , id_end_date,'|' , '|' , '|' , '|' ,'|' , '|' , id_sign_org,'|' , id_address,'|' ,'|' , '|' , '|' , sex,'|' , ethnic,'|' , country_code,'|' , '|' , birthday,'|' , '|' , bemarried,'|' , certificate,'|' , degree,'|' , occupation,'|' , post,'|' , '|' , '|' , '|' , '|' , '|' , '|' , '|' , req_bank_no,'|' ,     register_time,'|' , '|' , '|' , '|' , company,'|' , income,'|' , '|' , user_no  from T_CUSTOMER "
db2 "export to customerExport/b.txt of del select customer_no ,'|' , '303100|' , '1|' , country_code,'|' , '|','|','|','|'  ,family_address as address , '|' ,family_zip as code  ,'|'  from t_customer where family_address != '' and family_address is not null union select customer_no ,'|' , '303100|' , '2|' , country_code,'|' , '|','|','|','|'  ,unit_addr as address      , '|' , null as code      , '|'  from t_customer where unit_addr != '' and unit_addr is not null union select customer_no ,'|' , '303100|' , '6|' , country_code,'|' , '|','|','|','|'  ,id_address as address     , '|' , null as code      , '|'  from t_customer where id_address != '' and id_address is not null union select customer_no ,'|' , '303100|' , '9|' , country_code,'|' , '|','|','|','|'  ,comm_address as address   , '|' ,comm_zip  as code   ,'|'  from t_customer where comm_address != '' and comm_address is not null order by customer_no   " 
db2 "export to customerExport/c.txt of del select customer_no ,'|', '303100|' , '1|' , '|'  ,  unit_tel as tell ,'|' , '|'  from t_customer where unit_tel != '' and unit_tel is not null  union select customer_no ,'|', '303100|' , '1|' , '|'  ,  fax as tell      ,'|' , '|'  from t_customer      where fax != '' and unit_tel is not null union select customer_no ,'|', '303100|' , '1|' , '|'  ,  mobile as tell   ,'|' , '|'  from t_customer   where mobile != '' and mobile is not null order by customer_no  " 
db2 "export to customerExport/d.txt of del select customer_no ,'|', '303100|' , '1|' , email as net_tel from t_customer     where email != '' and email is not null union select customer_no ,'|', '303100|' , '3|' ,qq as net_tel from t_customer        where qq != '' and qq is not null union select customer_no ,'|', '303100|' , '6|' , apply_ip as net_tel from t_customer  where apply_ip != '' and apply_ip is not null union select customer_no ,'|', '303100|' , '8|' , wx as net_tel from t_customer        where wx != '' and wx is not null order by customer_no  "
db2 "export to customerExport/e.txt of del select customer_no,'|' , '303100|' , '1001|' , '|' , spouse_name,'|' , spouse_id_type,'|' , spouse_id_no,'|' , '|' , spouse_mobile,'|' from t_customer "

//去掉a.txt里面的"
sed -i 's/"//g' customerExport/a.txt
//去掉a.txt里面的,
sed -i 's/,//g' customerExport/a.txt

sed -i 's/"//g' customerExport/b.txt
sed -i 's/,//g' customerExport/b.txt

sed -i 's/"//g' customerExport/c.txt
sed -i 's/,//g' customerExport/c.txt

sed -i 's/"//g' customerExport/d.txt
sed -i 's/,//g' customerExport/d.txt

sed -i 's/"//g' customerExport/e.txt
sed -i 's/,//g' customerExport/e.txt

//把utf8转gbk
iconv  -f utf-8 -t gbk customerExport/a.txt > customerExport/对私客户基本信息.uml
iconv  -f utf-8 -t gbk customerExport/b.txt > customerExport/对私客户地址信息.uml
iconv  -f utf-8 -t gbk customerExport/c.txt > customerExport/对私客户电话信息.uml
iconv  -f utf-8 -t gbk customerExport/d.txt > customerExport/对私客户网络地址信息.uml
iconv  -f utf-8 -t gbk customerExport/e.txt > customerExport/对私关系人信息.uml

//把原来临时生成的a.txt删掉
rm -rf  customerExport/a.txt
rm -rf  customerExport/b.txt
rm -rf  customerExport/c.txt
rm -rf  customerExport/d.txt
rm -rf  customerExport/e.txt



}

//前面是方法  这里才是运行这个方法
test

把这个.sh文件丢到服务器之后，可能会出现三个问题
1.没有执行.sh的权限，也就是这个文件执行不了
解决办法：chmod +x 文件名

2.报错说这个文件格式不对，因为我们写这个在window下创建一个txt然后改成的.sh这是不能直接运行的
解决办法：sed -i “s/\r//” 文件名

3.没有导出数据库到txt的权限
解决办法：su - 用户//切换用户