shell语言调用数据库

最新推荐文章于 2024-04-18 09:18:35 发布

原创最新推荐文章于 2024-04-18 09:18:35 发布 · 502 阅读

0 ·

CC 4.0 BY-SA版权

SHELL 专栏收录该内容

16 篇文章

订阅专栏

这篇博客展示了如何使用Shell脚本调用数据库执行SQL查询，并通过sed命令对查询结果进行格式化处理。脚本包括对物理库和内存库的循环调用，将查询结果追加到文件中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/ksh
>33333.txt
sqlplus -s 数据库连接串<<!>>33333.txt
set colsep |
set pagesize 0
set linesize 1000
set serveroutput on size 10000
set pagesize 0
set head off
select '#############' a from dual;
select * from usr_134.bc_ist_record where result_code='118185044';
!

输出结果进行规整

sed "s/ *//g" 33333.txt

sed "s/\t//g" 33333.txt

sed -i '1i\date be_id domain proc_type error_code count(*)' /onip/app/billsharedb/lyy/error.txt

sed "s/ *//g" | sed "s/\t//g" >> 1234.txt

循环调用物理库

#!/usr/bin/ksh

sql_mod="set line 300\n set head off\n set feed off\n set pagesize 0\n set colsep \"|\" \n col acct_id for 9999999999999999999\n alter session set nls_date_format='yyyymmddhh24miss'; \n"
>1234.txt

echo "cust_id|be_id|tp_cust_key">>1234.txt

cat link1.cfg|grep "usrdb"|awk -F "|" '{print $2,$3}'|while read conn_str pdbuser
do

sql_str="select t.cust_id,t.be_id,t.tp_cust_key from ${pdbuser}.v_bc_customer t where t.cust_code in ('111000002641000','11000002458000','11000001493001','11000002502000');"
echo -e "${sql_mod}${sql_str}" | sqlplus -s $conn_str |sed "s/ *//g" | sed "s/\t//g" >> 1234.txt

done
循环调用内存库

#!/bin/sh
>1111.txt
tdbyd=$(date -d"`date +%y%m01` last day" +%Y%m%d160000)

echo "----数量----">>1111.txt

cat link2.cfg|grep "cbpmdb"|awk -F "|" '{print $2,$3}'|while read conn_str mdbuser

do

sql_rentevent="select count(*) from $mdbuser.e_sub_apn_status where STATUS_TYPE=1 and EFF_DATE>=to_date('${tdbyd}','YYYYMMDDHH24MISS');"

gmsql ${conn_str} "${sql_rentevent}" |sed 's/ */ /g'| head -n -4 | tail -n +6 >>1111.txt

done