0. 前言
作为一个习惯于使用终端进行运维的DBA,手上肯定需要一批SQL脚本,用于日常的运维和优化工作。这些脚本中,往往需要用户输入一些变量。那么,在 MogDB 数据库中,如何执行脚本?如何给脚本传递变量呢?
1. 执行不带变量的SQL脚本
不带变量的SQL脚本执行相对比较简单。我们分为两种情况讨论。一种是当前输入界面已经在gsql中,去调用SQL脚本;另一种是在SHELL里面,命令行调用gsql并给它传递SQL脚本。
1.1 准备一个测试用的SQL脚本(不含变量)
1.2 在gsql里面调用SQL脚本:\i 脚本文件名
gsql提供一个元命令,\i,用于调用SQL脚本。
比如:
\i 有几个变种,其中 \ir 是用于SQL脚本中嵌套 \i 时,使用的路径是以被调用的SQL脚本为当前路径的。而 \i+ 和 \ir+ 则是允许脚本进行加密。关于脚本嵌套和脚本加密,这里就不展开了。
1.3 在Shell环境执行gsql + SQL脚本
在Shell环境执行gsql + SQL脚本则有三种用法:
(1)标准用法:-f 脚本文件名
最标准的用法是使用gsql的命令行选项 -f,比如:
(2)SHELL输入重定向:< 脚本文件名
另一种用法是把 SQL脚本中的内容模仿标准输入,传给gsql命令,也就是使用 输入重定向符号 < + 脚本文件名。比如:
(3)利用gsql -c 选项:-c “\i 脚本文件名”
这个其实利用gsql里面的\i元命令,其实是绕了一圈,用这种方法应该比较少。比如:
2. 执行带变量的SQL脚本
调用不带变量的SQL脚本还是比较简单的。下面进入困难模式,调用的SQL需要特定的变量。比如,刚才那个示例SQL脚本,输出的是status=‘active’,可是如果希望根据变量来输出不同status的结果,就会相对麻烦很多。
2.1 gsql中的变量使用
幸运的是,gsql的确是支持变量的。内部称为"Variables"。
变量的设置有几种方式,分别是:
gsql命令行选项: -v 变量名称=变量值
gsql内赋值: \set 变量名称 变量值
gsql内交互式输入: \prompt 变量名称
而调用的时候,使用 :变量名 形式进行调用。但是,这里面有个非常重要的点,:变量名 不能放在引号里面,或者说,:变量名 不能用引号括起来,因为对于gsql而言,无法判断引号里的冒号是字符串内容的一部分还是变量名的前缀,所以,统一当作字符串内容的一部分来处理。
这个处理逻辑和 Oracle 不一样,Oracle 使用&符号来作为变量的前缀,在sqlplus里set define on(默认选项)时,无论&符号是否被放在引号里面,都会进行替换。
2.2 gsql中的数字类型变量使用
如果需要传入的字符串,这个问题处理起来会很复杂,因此,我们循序渐进,先通过相对简单的传入数字的形式来熟悉变量的使用。
假设现在我们需要检查数据库中空间大小大于x MB的对象。
SQL类似于这样:
2.2.1 gsql命令行选项: -v 变量名称=变量值
从SHELL中调用。
检查大于10MB的:
检查大于30MB的:
2.2.2 gsql内元命令: \set 变量名称 变量值
从gsql中调用,需先设置变量值。
检查大于10MB的:
30MB同理,这里不再占用篇幅。
2.2.3 gsql内元命令等待输入: \prompt 变量名称
检查大于30MB的:
10MB同理,不再占用篇幅。
2.2.4 让脚本包含输入提示: \prompt 变量名称
除了以上3种,其实,还可以在SQL脚本中包含输入提示部分。
脚本可以写成(给个新的名字relsize_p.sql):
然后可以SHELL调用 gsql:
或者在gsql中调用该SQL:
之所以特意把脚本改了个名字,是因为这是两种不同的思路,relsize.sql是可以SHELL直接后台调用,无需交互的;而relsize_p.sql是强制交互的。如果大家有需要准备常用带变量的脚本,建议准备两套,交互或者不交互。
2.3 gsql中的字符串类型变量使用
字符串类型变量使用会复杂一些。
2.3.1 字符串变量的限制
在gsql的体系里面,变量是无法被引号括起来,下面做个不成功的尝试。
假设,我们需要检查某个数据库参数的值。计划使用类似这样的SQL进行查询:
计划使用变量,按照Oracle的习惯,带变量的SQL会写成:
尝试在gsql中跑一下:
返回的0行:
显然不符合要求。这就是我前面所说的:变量是无法被引号括起来的。所以,我们只能在给变量的赋值时候,给它配套好引号。
尝试这样写:
失败了:
\set 里的引号被吃掉了,这也好理解,毕竟有时候要传带空格或者其他特殊字符的情况,gsql可以用一对引号来区分变量值的开始与结束位置。
2.3.2 gsql中字符串变量正确定义方法
继续尝试,根据MogDB的规则,单引号里带单引号,需要把用到的单引号写两遍。类似于这样:
\set name ‘’‘index’‘’
也就是前3后3的格局。
测试一下:
成功了:
2.3.3 通过\prompt输入时的正确用法
知道了需要用单引号后,\propmt的用法就很好理解了。这里直接前后各一个单引号就好了。因为它用回车符来确定输入终止位置,不需要用单引号来确定起始位置。
成功:
2.3.4 命令行 调用 gsql -v的正确用法
命令行调用的时候,需要考虑的是SHELL对单引号的处理。
先准备好SQL脚本:
SHELL对付单引号,一般有两种做法,一种是反斜杠,一种是双引号。反斜杠在对付没有空格的变量值可以用,如果带上变量值,则会失败。
反斜杠测试。
不带空格的调用:
没问题!
带空格的测试:
失败了。SHELL把空格前后当作两个参数来处理。
所以,还是老老实实用双引号稳一点:
3. 总结
gsql调用SQL脚本有三种方法:
gsql输入界面中 \i 文件名
SHELL中 gsql -f 文件名
SHELL中 gsql -c “\i 文件名”
SQL中变量符号为 :变量名
传递变量有三种方法:
gsql输入界面中 \set 变量名 变量值
SHELL中 gsql -v 变量名=变量值
SQL中或者gsql中使用 \prmopt 变量名进行交互式输入
字符串值变量使用:
不能在SQL中直接用单引号括起 :变量名
\set var 时变量值前后各三个单引号
\prompt 时前后各一个单引号
gsql -v时变量值里层单引号,外层双引号
是否交互性输入是两种不同的用法,建议使用不同的脚本文件
关于作者
罗海雄,云和恩墨数据库研发架构师,性能优化专家,2012年 ITPUB 全国SQL大赛冠军。他拥有超十年企业级系统设计与优化经验,对SQL优化理解尤其深入,曾服务于甲骨文公司。