Linux/Unix shell 参数传递到SQL脚本

男娘i 2022-03-17 01:44 472阅读 0赞

在数据库运维的过程中，Shell 脚本在很大程度上为运维提供了极大的便利性。而shell 脚本参数作为变量传递给SQL以及SQL脚本也是DBA经常碰到的情形之一。本文主要讨论了如何将shell脚本的参数传递到SQL脚本之中并执行SQL查询。  
　　有关shell与SQL之间的变量传递，请参考: [Linux/Unix shell sql 之间传递变量][Linux_Unix shell sql]

1、启动sqlplus时执行脚本并传递参数

robin@SZDB:~/dba_scripts/custom/awr> more tmp.sh#!/bin/bash# ----------------------------------------------#  Set environment here#  Author : Robinson Cheng#  Blog   : http://blog.csdn.net/robinson_0612# ----------------------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiif [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"    read -p "please input begin ORACLE_SID:" ORACLE_SID    read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date    read -p "please input end date and time(e.g. yyyymmddhh24):" end_dateelse    ORACLE_SID=${
        1}    begin_date=${
        2}    end_date=${
        3}fiexport ORACLE_SID begin_date end_date#Method 1: pass the parameter to script directly after script namesqlplus -S gx_adm/gx_adm @/users/robin/dba_scripts/custom/awr/tmp.sql $begin_date $end_date exitrobin@SZDB:~/dba_scripts/custom/awr> more tmp.sqlSELECT snap_id, dbid, snap_level  FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time, 'yyyymmddhh24') = '&1'       AND TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&2';exit;

2、在SQL提示符下传递参数

robin@SZDB:~/dba_scripts/custom/awr> more tmp2.sh#!/bin/bash# ----------------------------------------------#  Set environment here#  Author : Robinson Cheng#  Blog   : http://blog.csdn.net/robinson_0612# ----------------------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiif [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"    read -p "please input begin ORACLE_SID:" ORACLE_SID    read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date    read -p "please input end date and time(e.g. yyyymmddhh24):" end_dateelse    ORACLE_SID=${
        1}    begin_date=${
        2}    end_date=${
        3}fiexport ORACLE_SID begin_date end_date#Method 2: pass the parameter in SQL prompt. Using the same method with method 1sqlplus -S " / as sysdba" <<EOF@/users/robin/dba_scripts/custom/awr/tmp.sql $begin_date $end_dateexit;EOFexit

3、通过定义变量的方式来传递参数

robin@SZDB:~/dba_scripts/custom/awr> more tmp3.sh #!/bin/bash# ----------------------------------------------#  Set environment here#  Author : Robinson Cheng#  Blog   : http://blog.csdn.net/robinson_0612# ----------------------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiif [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"    read -p "please input begin ORACLE_SID:" ORACLE_SID    read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date    read -p "please input end date and time(e.g. yyyymmddhh24):" end_dateelse    ORACLE_SID=${
        1}    begin_date=${
        2}    end_date=${
        3}fiexport ORACLE_SID begin_date end_date#Method 3: pass the parameter to global variable firstly.sqlplus -S " / as sysdba" <<EOFdefine begin_date=$begin_date        define end_date=$end_dateprompt "variable value for begin_date is: &begin_date"prompt "variable value for end_date id : &end_date"@/users/robin/dba_scripts/custom/awr/tmp3.sql begin_date end_dateexit;EOFexitrobin@SZDB:~/dba_scripts/custom/awr> more tmp3.sqlSELECT snap_id, dbid, snap_level  FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time, 'yyyymmddhh24') = '&begin_date'       AND TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&end_date';exit;

4、测试脚本

robin@SZDB:~/dba_scripts/custom/awr> ./tmp.shUsage:       tmp.sh <ORACLE_SID> <begin_dat> <end_date>please input begin ORACLE_SID:CNMMBOplease input begin date and time(e.g. yyyymmddhh24):2013030709please input end date and time(e.g. yyyymmddhh24):2013030710   SNAP_ID       DBID SNAP_LEVEL---------- ---------- ----------     13877  938506715          1robin@SZDB:~/dba_scripts/custom/awr> ./tmp2.sh MMBOTST 2013030709 2013030710   SNAP_ID       DBID SNAP_LEVEL---------- ---------- ----------     36262 3509254984          1robin@SZDB:~/dba_scripts/custom/awr> ./tmp3.sh MMBOTST 2013030710 2013030711"variable value for begin_date is: 2013030710""variable value for end_date id : 2013030711"   SNAP_ID       DBID SNAP_LEVEL---------- ---------- ----------     36263 3509254984          1

5、小结  
a、本文主要描述了将shell的参数传递给SQL脚本  
b、方式1的用法是直接将shell变量跟在脚本之后, sqlplus userid/pwd @script\_name $para1 $para2  
c、方式2是启动sqlplus后在SQL提示符下来传递参数, SQL>@script\_name $para1 $para2  
d、方式3则是将shell变量的值先传递给define定义的变量，然后再传递给SQL脚本 SQL>@script\_name var1 var2  
e、注意方式3中SQL脚本的替代变量与define定义的变量名相同

更多参考:

有关Oracle RAC请参考  
[使用crs\_setperm修改RAC资源的所有者及权限  
][crs_setperm_RAC_] [使用crs\_profile管理RAC资源配置文件  
][crs_profile_RAC_] [RAC 数据库的启动与关闭  
][RAC _] [再说 Oracle RAC services  
][Oracle RAC services] [Services in Oracle Database 10g  
][Services in Oracle Database 10g] [Migrate datbase from single instance to Oracle RAC  
][Migrate datbase from single instance to Oracle RAC] [Oracle RAC 连接到指定实例  
][Oracle RAC _] [Oracle RAC 负载均衡测试(结合服务器端与客户端)  
][Oracle RAC _ 1] [Oracle RAC 服务器端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_] [Oracle RAC 客户端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_ 1] [ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)][ORACLE RAC _listener.ora tnsnames.ora]  
[ORACLE RAC 监听配置 (listener.ora tnsnames.ora)  
][ORACLE RAC _ _listener.ora tnsnames.ora_] [配置 RAC 负载均衡与故障转移  
][RAC _ 1] [CRS-1006 , CRS-0215 故障一例][CRS-1006 _ CRS-0215]  
[基于Linux (RHEL 5.5) 安装Oracle 10g RAC][Linux _RHEL 5.5_ _Oracle 10g RAC]  
[使用 runcluvfy 校验Oracle RAC安装环境][runcluvfy _Oracle RAC]

有关Oracle 网络配置相关基础以及概念性的问题请参考：  
[配置非默认端口的动态服务注册][Link 1]  
[配置sqlnet.ora限制IP访问Oracle  
][sqlnet.ora_IP_Oracle] [Oracle 监听器日志配置与管理][Oracle]  
[设置 Oracle 监听器密码(LISTENER)  
][Oracle _LISTENER_] [配置ORACLE 客户端连接到数据库][ORACLE]

有关基于用户管理的备份和备份恢复的概念请参考  
[Oracle 冷备份  
][Oracle _] [Oracle 热备份  
][Oracle _ 1] [Oracle 备份恢复概念  
][Oracle _ 2] [Oracle 实例恢复  
][Oracle _ 3] [Oracle 基于用户管理恢复的处理  
][Oracle _ 4] [SYSTEM 表空间管理及备份恢复  
][SYSTEM _] [SYSAUX表空间管理及恢复  
][SYSAUX_] [Oracle 基于备份控制文件的恢复(unsing backup controlfile)][Oracle _unsing backup controlfile]

有关RMAN的备份恢复与管理请参考  
[RMAN 概述及其体系结构  
][RMAN _] [RMAN 配置、监控与管理  
][RMAN _ 1] [RMAN 备份详解  
][RMAN _ 2] [RMAN 还原与恢复  
][RMAN _ 3] [RMAN catalog 的创建和使用  
][RMAN catalog _] [基于catalog 创建RMAN存储脚本  
][catalog _RMAN_] [基于catalog 的RMAN 备份与恢复  
][catalog _RMAN _] [RMAN 备份路径困惑  
][RMAN _ 4] [使用RMAN实现异机备份恢复(WIN平台)  
][RMAN_WIN_] [使用RMAN迁移文件系统数据库到ASM  
][RMAN_ASM] [linux 下RMAN备份shell脚本  
][linux _RMAN_shell_] [使用RMAN迁移数据库到异机][RMAN]

有关ORACLE体系结构请参考  
[Oracle 表空间与数据文件  
][Oracle _ 5] [Oracle 密码文件  
][Oracle _ 6] [Oracle 参数文件  
][Oracle _ 7] [Oracle 联机重做日志文件(ONLINE LOG FILE)  
][Oracle _ONLINE LOG FILE_] [Oracle 控制文件(CONTROLFILE)  
][Oracle _CONTROLFILE_] [Oracle 归档日志  
][Oracle _ 8] [Oracle 回滚(ROLLBACK)和撤销(UNDO)  
][Oracle _ROLLBACK_UNDO_] [Oracle 数据库实例启动关闭过程  
][Oracle _ 9] [Oracle 10g SGA 的自动化管理  
][Oracle 10g SGA _] [Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[Linux_Unix shell sql]: http://blog.csdn.net/robinson_0612/article/details/7978179
[crs_setperm_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8219242
[crs_profile_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8192571
[RAC _]: http://blog.csdn.net/robinson_0612/article/details/8129506
[Oracle RAC services]: http://blog.csdn.net/robinson_0612/article/details/8124232
[Services in Oracle Database 10g]: http://blog.csdn.net/robinson_0612/article/details/8089812
[Migrate datbase from single instance to Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/8090095
[Oracle RAC _]: http://blog.csdn.net/robinson_0612/article/details/8073620
[Oracle RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/8072367
[Oracle RAC _Load Balance_]: http://blog.csdn.net/robinson_0612/article/details/8064599
[Oracle RAC _Load Balance_ 1]: http://blog.csdn.net/robinson_0612/article/details/8060634
[ORACLE RAC _listener.ora tnsnames.ora]: http://blog.csdn.net/robinson_0612/article/details/8053007
[ORACLE RAC _ _listener.ora tnsnames.ora_]: http://blog.csdn.net/robinson_0612/article/details/8020800
[RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/6791667
[CRS-1006 _ CRS-0215]: http://blog.csdn.net/robinson_0612/article/details/7926241
[Linux _RHEL 5.5_ _Oracle 10g RAC]: http://blog.csdn.net/robinson_0612/article/details/7906282
[runcluvfy _Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/6533094
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/5925526
[sqlnet.ora_IP_Oracle]: http://blog.csdn.net/robinson_0612/article/details/6629141
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6629043
[Oracle _LISTENER_]: http://blog.csdn.net/robinson_0612/article/details/6629249
[ORACLE]: http://blog.csdn.net/robinson_0612/article/details/5917103
[Oracle _]: http://blog.csdn.net/robinson_0612/article/details/5791597
[Oracle _ 1]: http://blog.csdn.net/robinson_0612/article/details/5816012
[Oracle _ 2]: http://blog.csdn.net/robinson_0612/article/details/5791585
[Oracle _ 3]: http://blog.csdn.net/robinson_0612/article/details/5768233
[Oracle _ 4]: http://blog.csdn.net/robinson_0612/article/details/6041217
[SYSTEM _]: http://blog.csdn.net/robinson_0612/article/details/6041207
[SYSAUX_]: http://blog.csdn.net/robinson_0612/article/details/5810732
[Oracle _unsing backup controlfile]: http://blog.csdn.net/robinson_0612/article/details/7962187
[RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6032525
[RMAN _ 1]: http://blog.csdn.net/robinson_0612/article/details/6032597
[RMAN _ 2]: http://blog.csdn.net/robinson_0612/article/details/6032739
[RMAN _ 3]: http://blog.csdn.net/robinson_0612/article/details/6041247
[RMAN catalog _]: http://blog.csdn.net/robinson_0612/article/details/6043649
[catalog _RMAN_]: http://blog.csdn.net/robinson_0612/article/details/6073020
[catalog _RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6073069
[RMAN _ 4]: http://blog.csdn.net/robinson_0612/article/details/6414338
[RMAN_WIN_]: http://blog.csdn.net/robinson_0612/article/details/6639064
[RMAN_ASM]: http://blog.csdn.net/robinson_0612/article/details/6453954
[linux _RMAN_shell_]: http://blog.csdn.net/robinson_0612/article/details/8029245
[RMAN]: http://blog.csdn.net/robinson_0612/article/details/8076841
[Oracle _ 5]: http://blog.csdn.net/robinson_0612/article/details/5611738
[Oracle _ 6]: http://blog.csdn.net/robinson_0612/article/details/5611672
[Oracle _ 7]: http://blog.csdn.net/robinson_0612/article/details/5559174
[Oracle _ONLINE LOG FILE_]: http://blog.csdn.net/robinson_0612/article/details/5749556
[Oracle _CONTROLFILE_]: http://blog.csdn.net/robinson_0612/article/details/5737527
[Oracle _ 8]: http://blog.csdn.net/robinson_0612/article/details/5784713
[Oracle _ROLLBACK_UNDO_]: http://blog.csdn.net/robinson_0612/article/details/5731158
[Oracle _ 9]: http://blog.csdn.net/robinson_0612/article/details/5542983
[Oracle 10g SGA _]: http://blog.csdn.net/robinson_0612/article/details/5534832
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/article/details/5529239
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485