sqlplus与shell互相传值的几种情况
情况一：在shell中最简单的调用sqlplus
$cat test.sh
#!/bin/sh
sqlplus oracle/oracle@oracle>file.log <<EOF
select * from test;
exit
EOF #注意EOF要顶格写
$sh test.sh
$cat file.log
--省略若干系统提示信息-------
SQL>
EMPNO EMPNAME SAL DEPTNO
----- ------------- ----- ------
10002 Frank Naude 500 20
10001 Scott Tiger 1000 40
--省略若干系统提示信息-------
将执行过程重定向入文件file.log，可通过cat file.log查看
情况二：直接将sqlplus的值赋值给shell变量
$cat test.sh
#!/bin/sh
# 将sqlplus的结果输出给变量VALUE
# set命令的使用可查询手册
#注意shell中等号两边不能有空格
VALUE=`sqlplus -S /nolog <<EOF
set heading off feedback off pagesize 0 verify off echo off
conn oracle/oracle@oracle
select count(*) from test;
exit
EOF`
#输出记录数
echo "The number of rows is $VALUE."
$sh test.sh
The number of rows is 2.
显示结果正确，表test共2条记录
情况三：间接将sqlplus的值赋值给shell变量
$cat test.sh
#!/bin/sh
#利用COL column NEW_VALUE variable定义变量
#sqlplus执行完后最后返回值为v_coun
#利用$?将最后返回值赋值给VALUE,也即为test的记录数
sqlplus -S /nolog <<EOF
set heading off feedback off pagesize 0 verify off echo off
conn oracle/oracle@oracle
col coun new_value v_coun
select count(*) coun from test;
exit v_coun
EOF
VALUE="$?"
echo "The number of rows is $VALUE."
$sh test.sh
2
The number of rows is 2.
脚本执行结果中第一个2为sqlplus返回值，第二个2为VALUE的值
情况四：将shell变量的值传给sqlplus使用
$cat test.sh
#!/bin/sh
#sqlplus引用shell变量TABLENAME的值
#注意赋值时，等号两边不能有空格
TABLENAME="test"
sqlplus -S oracle/oracle@oracle <<EOF
select * from ${TABLENAME};
exit
$sh test.sh
EMPNO EMPNAME SAL DEPTNO
----- -------------------------------------------------- ---------- ------
10002 Frank Naude 500 20
10001 Scott Tiger 1000 40
脚本执行结果为：select * from test；的结果
情况五：通过交互方式手工输入shell变量值
$cat test.sh
#!/bin/sh
#将手工输入变量值读入变量TABLENAME
echo "Enter the tablename you want to select:"
read TABLENAME
sqlplus -S oracle/oracle@oracle <<EOF
select * from ${TABLENAME};
exit
$sh test.sh
#按提示输入表名test
Enter the tablename you want to select:
test
EMPNO EMPNAME SAL DEPTNO
----- -------------------------------------------------- ---------- ------
10002 Frank Naude 500 20
10001 Scott Tiger 1000 40
脚本执行结果为select * from test的执行结果
chapte 2:
有时因工作需要，得写一些脚本，都是shell和sqlplus混合的。
一般情况下，shell变量带入到sql脚本，比较方便，但是把sql的一些结果，输出给shell，就比较麻烦一些了。以前用的方法比较土一点，就是在sqlplus里面，spool到一个临时文件，然后在shell里面用grep，awk一类的来分析这个输出文件。后来在网上看到一篇介绍，受益匪浅啊。在此表示感谢。 http://hi.baidu.com/edeed/blog/item/291698228a5694f4d7cae2c1.html/cmtid/e87926977f74636155fb968f 我试了三种，一个是退出sqlplus时顺带返回值。这个方法有限制，只能是数字，不能是字符串。 SQL> col global_name new_value xxx SQL> select global_name from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- XXX.XXX.XXX SQL> exit xxx SP2-0584: EXIT variable "XXX" was non-numeric 第二个就是直接select语句的输出。 脚本如下(test1.sh)： #!/bin/bash VALUE=`sqlplus -S user/pass@tns <<EOF set heading off feedback off pagesize 0 verify off echo off numwidth 4 select * from global_name; exit EOF` echo $VALUE 测试结果如下： [root@xxx tmp]# sh test1.sh XXX.XXX.COM [root@xxx tmp]# 第三个是定义一个变量，然后在sqlplus里面print。 脚本如下（test2.sh）： #!/bin/bash VALUE=`sqlplus -S user/pass@tns <<EOF set heading off feedback off pagesize 0 verify off echo off numwidth 4 var username varchar2(30) begin select user into :username from dual; :username := 'username '|| :username; end; / print username exit EOF` echo $VALUE 测试结果如下： [root@xxxtmp]# sh test2.sh username USER_A [root@xxxtmp]#
sample 6: sqlplus 传变量到.sql 文件
SET DEF ^ TERM OFF ECHO ON AUTOP OFF VER OFF SERVEROUT ON SIZE 1000000;
####################################
在中默认的"&"表示替代变量，也就是说，只要在命令中出现该符号，就会要你输入替代值。这就意味着你无法将一个含有该符号的字符串输入数据库或赋给变量，如字符串“SQL&Plus”系统会理解为以“SQL”打头的字符串，它会提示你输入替代变量 Plus的值，如果你输入ABC，则最终字符串转化为“SQLABC”。 set define off 则关闭该功能，“&”将作为普通字符，如上例，最终字符就为“SQL&Plus” set define off关闭替代变量功能 set define on 开启替代变量功能 set define ^ 将默认替代变量标志符该为“^”(也可以设为其它字符) ###########################
DEF health_checks = 'Y';
DEF shared_cursor = 'N';DEF sql_monitor_reports = '12';REMDEF script = 'sqlhc';DEF method = 'SQLHC';DEF mos_doc = '1366133.1';DEF doc_ver = '12.1.06';DEF doc_date = '2014/01/30';-- sqldx_output: HTML/CSV/BOTH/NONEDEF sqldx_output = 'CSV';/**************************************************************************************************/
EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => '^^method. ^^doc_ver.', action_name => '^^script..sql');
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => '^^method.');VAR health_checks CHAR(1);EXEC :health_checks := '^^health_checks.';VAR shared_cursor CHAR(1);EXEC :shared_cursor := '^^shared_cursor.';SET TERM ON ECHO OFF;PROPRO Parameter 1:PRO Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)PRODEF input_license = '^1';PROSET TERM OFF;COL license NEW_V license FOR A1;SELECT UPPER(SUBSTR(TRIM('^^input_license.'), 1, 1)) license FROM DUAL;
############
COL license NEW_V license FOR A1; ; --定义 license列值保存到变量 license
SELECT UPPER(SUBSTR(TRIM('^^input_license.'), 1, 1)) license FROM DUAL;
--查看表 DUAL的列 license字段值，此时 license的值将被赋予给变量 licenseVAR license CHAR(1); --定义变量 license 类型
EXEC :license := '^^license.'; ; --此处可以使用变量license 来赋值license
print license -察看license 值
###########VAR license CHAR(1);
EXEC :license := '^^license.';COL unique_id NEW_V unique_id FOR A15;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') unique_id FROM DUAL;SET TERM ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;BEGIN
IF '^^license.' IS NULL OR '^^license.' NOT IN ('T', 'D', 'N') THEN RAISE_APPLICATION_ERROR(-20100, 'Oracle Pack License (Tuning, Diagnostics or None) must be specified as "T" or "D" or "N".'); END IF;END;/WHENEVER SQLERROR CONTINUE;
PRO
PRO Parameter 2:PRO SQL_ID of the SQL to be analyzed (required)PRODEF input_sql_id = '^2';DEF input_parameter = '^^input_sql_id.';PROPRO Values passed:
PRO License: "^^input_license."PRO SQL_ID : "^^input_sql_id."PRO--SET TERM OFF;-- get dbid
COL dbid NEW_V dbid;SELECT dbid FROM v$database;COL sql_id NEW_V sql_id FOR A13;
SELECT sql_id
FROM gv$sqlarea WHERE sql_id = TRIM('^^input_sql_id.') UNIONSELECT sql_id FROM dba_hist_sqltext WHERE :license IN ('T', 'D') AND dbid = ^^dbid. AND sql_id = TRIM('^^input_sql_id.');VAR sql_id VARCHAR2(13);
EXEC :sql_id := '^^sql_id.';SET TERM ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;BEGIN
IF '^^sql_id.' IS NULL THEN IF :license IN ('T', 'D') THEN RAISE_APPLICATION_ERROR(-20200, 'SQL_ID "^^input_sql_id." not found in memory nor in AWR.'); ELSE RAISE_APPLICATION_ERROR(-20200, 'SQL_ID "^^input_sql_id." not found in memory.'); END IF; END IF;END;/WHENEVER SQLERROR CONTINUE;
SET ECHO ON TIMI ON;/*******************************************