shell SQL 变量 Oracle shell调用SQL操作DB

注意 ：

v\\\$  用法, “v\\\$session ”   ""不能用 

sqlplus -S  / as sysdba << EOF
set pagesize 0
set verify off
set feedback off
set echo off
col coun new_value v_coun
select count(*) coun from dual;
EOF
value="$?"
 
 
 
 
 
 
VALUE=`sqlplus -s / as sysdba <<EOF
        set pagesize 0 feedback off verify off heading off echo off numwidth 5
        select count(*)    from v\\\$session;
        exit;
EOF`
 
 
if [ "$VALUE" -gt 0 ]
then
        echo "The number of rows is $VALUE."
 else
        echo "There is no row in the table."
fi
if [ $value == 0 ];
then
echo  "222222222"
else
echo "1111111111"
fi
 
 

Oracle shell调用SQL操作DB
 
操作Oracle数据库可以使用sqlplus连接数据库之后，再交互式的使用数据库。另一种非交互的方式就是通过shell直接执行sql命令，可以直接在shell CLI端口执行命令，或者是通过shell脚本的方式。从sql命令的输入方式上，这种非交互的方式又可以分为两种，一种是命令行直接输入，另一种是sql文件输入。
 

 
1. 命令行直接输入方式
 
这种方式就是把要执行的命令直接传给sqlplus，-S是指silent模式。注意此处的反斜杠转义。
sqlplus -S '/ as sysdba' << EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT value FROM v\$parameter WHERE name = 'background_dump_dest';
exit
EOF
使用脚本的话，如下所示，注意反斜杠。
if test $# -lt 1
        then
 echo You must pass a SID
        exit
fi
  
ORACLE_SID=$1; export ORACLE_SID
DUMP_DIR=`sqlplus -S '/ as sysdba' << EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT value FROM v\\$parameter WHERE name = 'background_dump_dest';
exit
EOF`
 
echo ${DUMP_DIR}
 
2. 通过文件输入方式
 
这种方式是先把sql语句存储在一个文件中，这时就不需要反斜杠了，而且输入文件必须要以.sql为后缀。
[oracle@node ~]$ cat /tmp/sqllines.sql
set pagesize 0 feedback off verify off heading off echo off
SELECT value FROM v$parameter WHERE name = 'background_dump_dest';
exit
 
[oracle@node ~]$ sqlplus -s "/ as sysdba" @/tmp/sqllines
/u01/app/oracle/diag/rdbms/live/live/trace
这种方式同样可以写成一个shell脚本。
[oracle@node ~]$ cat /tmp/sql
if test $# -lt 1
        then
 echo You must pass a SID
        exit
fi
 
ORACLE_SID=$1; export ORACLE_SID
echo "
set pagesize 0 feedback off verify off heading off echo off
SELECT value FROM v\$parameter WHERE name = 'background_dump_dest';
exit
">/tmp/plsql_scr.sql
 
# --------------------------------
# Execute plsql script
# --------------------------------
 
if [ -s /tmp/plsql_scr.sql ]; then
    echo -e "Running SQL script to find out bdump directory... \n"
    $ORACLE_HOME/bin/sqlplus -s "/ as sysdba" @/tmp/plsql_scr.sql >/tmp/plsql_scr_result.log
fi
 
echo " Check the reslut "
echo "------------------------"
cat /tmp/plsql_scr_result.log
 
exit

 
sqlplus -s  / as sysdba <<eof
@test.sql
EOF
 
第二个EOF前面有没有exit效果都一样。 也就是说缺省就是exit
test.sql里最后加不加commit效果都一样，exit缺省的时候就是提交（这个可以控制）
 
test.sql 名字如果是带空格t est.sql，怎么办？
(下面是好多sql文件进行遍历)
【1】
cat database.sh
ls *sql | while read line;do
sqlplus -s / as sydsba <<eof
@"$line"
EOF
 
 
 
 
【2】
cat database.sh
for line in `ls *sql` ;do
sqlplus -s / as sydsba <<eof
@"$line"
EOF
done
 
 
用while循环，而不用for in 是因为如果文件名有空格，ls *sql出来以后，line取值是按照空格或者换行符作为间隔符号，
所以一个文件名会被空格分成为2个值使用;而用while read 则是只按照换行符作为间隔符号，所以一个文件名不会被分割。这就是这两种方式的区别。
 
 
 
 
下面的sqlplus 下面执行@"line",变量line需要加双引号，防止文件名被空格分割解析
 
 
 
sqlplus 的两种方式对比对比：
【1】
#cat test.sql
insert into test values(sysdate);
commit;
 
 
#cat database.sh
ls *sql | while read line;do
sqlplus -s / as sydsba <<eof
@"$line"
EOF
 
上面的exit退出动作是由EOF完成的
 
 
【2】
#cat test.sql
insert into test values(sysdate);
commit;
exit;
 
 
#cat database.sh
ls *sql | while read line;do
sqlplus -s / as sydsba @"$line"
 
 

上面的exit退出动作，只能在test.sql中完成。
在这种情况下如果不在test.sql中加exit,那么循环会在第一次sqlplus 执行的时候阻塞，直到被手工处理以后，才能进入到下一次循环。

如果当前服务器安装的有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)
 
参数为：
 
create： 创建指定文件名的新文件；如指定文件存在，则报文件存在错误
replace：如果指定文件存在则覆盖替换；不存在，则创建，replace为spool默认选项
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