Linux/unix passing variables between shell SQL

Flexible combination of Linux/unix shell and SQL variable transfer, greatly improve the efficiency of the DBA, this article for Linux/unix Shell SQL passed between the variables given a few simple examples for reference.

Linux/unix cut with Sql,ramn please refer to: Sql,rman script in Linux/unix shell script

One, example

[Python]View PlainCopyprint?

2. 1, the shell variable accepts the SQL return value the way one
4. [Email protected]:~> more./retval.sh
6. #!/bin/bash
8. Retval= ' Sqlplus-silent Scott/tiger <<eof
10. SET PAGESIZE 0 FEEDBACK off VERIFY off HEADING off ECHO off
12. SELECT * from emp WHERE ename=' SCOTT ';
14. EXIT;
16. EOF '
18. If [-Z "$RETVAL"]; Then
20. echo "No rows returned from database"
22. Exit 0
24. Else
26. Echo $RETVAL
28. Fi
32. [Email protected]:~> chmod u+x retval.sh
34. [Email protected]:~>./retval.sh
36. 7788 SCOTT ANALYST 7566 19-apr- 34171.88
40. 2, the shell variable accepts the SQL return value the way two
42. [Email protected]:~> more./retval_2.sh
44. #!/bin/bash
46. # Author:robinson Cheng
48. # blog:http://blog.csdn.net/robinson_0612
50. Sqlplus-s "Scott/tiger" <<eof
52. SET PAGESIZE 0 FEEDBACK off VERIFY off HEADING off ECHO off
54. Col CNT New_value v_cnt
56. SELECT COUNT (*) CNT from emp WHERE deptno=10;
58. Exit v_cnt
60. Eof
62. Value="$?"
64. echo "Show rows for Deptno: $VALUE"
68. 3. Pass the shell variable as a parameter to the SQL script
70. [Email protected]:~> more./retval_3.sh
72. #!/bin/bash
74. v_empno="$"
76. Sqlplus-s "Scott/tiger" <<eof
78. SET PAGESIZE 0 FEEDBACK off VERIFY off HEADING off ECHO off
80. SELECT ename from emp WHERE empno= $v _empno;
82. Exit
84. Eof
86. Exit
90. [Email protected]:~>./retval_3.sh 7788
92. SCOTT
94. Ext.: http://blog.csdn.net/leshami/article/details/7978179

Linux/unix passing variables between shell SQL