shell脚本中通过sqlplus操作oracle数据库

如果当前服务器安装的有oracle数据库，配置环境变量后可以直接使用sqlplus,如果没有则需要安装客户端和sqlplus包。shell脚本中通过sqlplus -S dbuser/dbpass@host/dbname连接上数据库后，一般所做的操作就是在脚本中下载表中的数据到本地或者是在脚本中调用oracle存储过程，再通过crontab启动定时任务调用shell脚本去跑数据，下文将详细介绍这两种的使用方法：

sqlplus常用参数设置

set feedback off;      --回显本次sql命令处理的记录条数，缺省为on

set verify off;            --是否显示替代变量被替代前后的语句

set heading off;        --是否显示字段的名称

set echo off;             --显示sqlplus中的每个sql命令本身，缺省为on

set pagesize 0;         --输出每页行数，缺省为24(每24行产生一个空行)，为了避免分页，设定为0

set linesize 200;       --可以设置的大点，防止一行长度不够

set trimspool on;      --去除重定向（spool）输出每行的拖尾空格，缺省为off

set colsep ',';           --设置分隔符为逗号，这样csv文件里才不会冗余到一个单元格里

spool用法

spool是sqlplus中用来保存或打印查询结果，主要把sql查询结果保存到本地文件中，

格式为：spool 文件路径 参数(参数可省略，不添加参数默认为replace)

参数为：

1. create： 创建指定文件名的新文件；如指定文件存在，则报文件存在错误
2. replace：如果指定文件存在则覆盖替换；不存在，则创建，replace为spool默认选项
3. append：向指定文件名中追加内容；如指定文件不存在,则创建

用法：

spool /opt/proc_log/table_name.csv append

sql查询脚本

spool off

shell脚本连接sqlplus,导出数据库里的数据到本地

举例导出文件为csv文件,其它文件一样

方法一：设置分隔符set colsep ','，表字段之间以逗号为分隔符

#!/bin/bash
source ~/.bash_profile

#设置ORACLE的相关环境,如果在bash_profile已经添加了环境变量，不需要再添加以下两行
export ORACLE_HOME=/安装路径下的/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

dbuser=appuser
dbpass=$(echo "VGVzdDIwMjJfcHcK"|base64 -d)
dbinfo=192.168.23.01/orcl

#定义变量存放返回信息，避免回显
msg=`
#通过sqlplus连接数据库
sqlplus -S $dbuser/$dbpass@$dbinfo << eof
#设置分隔符
  set colsep ',';
  set pagesize 0;
  set trimspool on;
  set linesize 200;
  set feedback off;
  set verify off;
  set heading on;
  set echo off;
#打印数据到csv文件
  spool /opt/proc_log/table_name.csv
#spool无法打印字段名，特添加此操作在文件中增加字段名称
  select 'TABLE_ID'||','||
         'TABLE_NAME'||','||
         'TMP_TABLE_NAME'||','||
         'LOAD_MODE'||','||
         'EFFECTIVE_DATE'||','||
         'EXPIRY_DATE'||','||
         'MD5_TABLE_NAME'
    from dual;
#查询sql
  select * from Table_List;
#关闭打印
  spool off
#退出
  exit;
eof
`
exit 0

方法二：采用拼接手工控制输出格式，可以对各种字段进行预处理，常使用该方法

#!/bin/bash
source ~/.bash_profile

#设置ORACLE的相关环境,如果在bash_profile已经添加了环境变量，不需要再添加以下两行
export ORACLE_HOME=/安装路径下的/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

dbuser=appuser
dbpass=$(echo "VGVzdDIwMjJfcHcK"|base64 -d)
dbinfo=192.168.23.01/orcl

msg=`
sqlplus -S $dbuser/$dbpass@$dbinfo << eof
  set pagesize 0;
  set trimspool on;
  set linesize 200;
  set feedback off;
  set verify off;
  set heading on;
  set echo off;
  spool /opt/proc_log/table_name.csv
  select 'TABLE_ID'||','||
         'TABLE_NAME'||','||
         'TMP_TABLE_NAME'||','||
         'LOAD_MODE'||','||
         'EFFECTIVE_DATE'||','||
         'EXPIRY_DATE'||','||
         'MD5_TABLE_NAME'
    from dual;
  select TABLE_ID||','||
         TABLE_NAME||','||
         TMP_TABLE_NAME||','||
         LOAD_MODE||','||
         EFFECTIVE_DATE||','||
         EXPIRY_DATE||','||
         MD5_TABLE_NAME 
    from Table_List;
  spool off
  exit;
eof
`
exit 0

shell脚本连接sqlplus,调用存储过程抓取返回值

#!/bin/bash
source ~/.bash_profile

#设置ORACLE的相关环境,如果在bash_profile已经添加了环境变量，不需要再添加以下两行
export ORACLE_HOME=/安装路径下的/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

dayno=date +%Y-%m-%d
dbuser=appuser
dbpass=$(echo "VGVzdDIwMjJfcHcK"|base64 -d)
dbinfo=192.168.23.01/orcl

msg=`
sqlplus -S $dbuser/$dbpass@$dbinfo <<eof
  set feedback off;
  set verify off;
  set heading off;
  set echo off;
#定义存储过程返回值
  var vo_code number;
#定义存储过程返回信息
  var vo_msg  varchar2(400);
#调用存储过程，使用变量获取返回信息
  call procname($dayno, :vo_code, :vo_msg);
#返回存储过程代码给msg
  select :vo_code from dual;
  exit;
eof
`
echo ${msg}
exit 0