数据库管理员在写SQL命令的时候总希望SQL语句能够满足不同环境的需要为此可以通过交互式命令来提高SQL语句的灵活性当在一个SQL语句中加入了交互式变量之后在语句运行之时数据库就会提醒用户输入相关的变量如下图
不过在实际工作中往往把这个SQL语句保存为脚本如此的话可以进一步提高脚本文件的可重用性有时会在数据库管理的时候结合替换变量写一些有用的脚本文件可以起到画龙点睛的作用笔者在平时数据库开发与管理中就喜欢采用这种方式来简化工作在这篇文章中笔者就结合自己的工作经验给大家谈谈如何在Oracle数据库中利用可替换变量来提高SQL语句的灵活性以及在使用替换变量时的一些注意点
一 定义与使用替换变量
在Oracle提供的相关工具中如SQL PLUS管理平台中允许数据库管理员采用替换变量来提高SQL语句的灵活性替换变量的定义跟普通变量的定义类似只需要在变量名前面加入&符号即可如上面例子中的&name_filed就表示一个替换变量当运行一段有替换变量的SQL语句时数据库系统会自动提醒用户要输入哪些变量值
如在如下这条语句中&name_filed表示用户想要显示的字段值若用户想要显示某个表中的多个字段的话则可以在这里定义多个替换变量而条件语句中的&NAME则表示一个限制条件或者说一个查询参数这条语句的大意就是用户输入员工的名字然后让系统查询出用户想了解的信息如用户的身份证号码等等
SQL> select &name_filed from dtmfgad_user t where tname like &NAME;
细心的读者或许会发现在查询条件中的替换变量为什么要用单引号括起来呢?这是因为我们在写SQL语句的时候若字段时字符型的话就需要用单引号括起来所以在定义替换变量的时候跟平时写SQL语句类型若数据类型是字符或者日期型的则需要在定义替换变量的时候用单引行括起来否则的话在用户执行这条语句输入变量的时候每次都需要把括号添加进去那就非常的麻烦相比起来还不如在定义这条SQL语句的时候就加入单引号如此在输入对应值的时候就不用用单引号括起来了可以直接输入
另外替换变量不但在Select语句中可以使用在Update等语句中也可以普遍应用
二 定义默认值
有时候对于替换变量我们可能会有默认值如果数据库管理员已经知道替换变量的具体值又想避免运行脚本文件时反复出现则可以给替换变量定义一个默认值在SQL Plus平台中数据库管理员可以利用Define命令来定义替换变量的默认值
在定义替换变量默认值的时候需要注意几个问题
一是要合理使用单引号该用单引号括起来的就要使用单引号;不需要用的时候坚决不用否则的话会造成SQL语句的运行错误那么什么时候该用单引号呢?这主要是根据SQL语句来的平时在写SQL语句的过程中要用到单引号的则在定义默认值的时候也要用单引号括起来如果平时不用的话则就可以不用如在定义字段名称的默认值时就不需要用单引号括起来画蛇添足反而会导致SQL语句的执行错误
二是这只是定义了一个默认值当定义来一个默认值之后系统就不会提示用户输入相关的参数而直接采用默认值如在上面这个例子中若利用define NAME=SuperUser这条语句定义了NAME的默认值之后在执行这条Select语句的时候就不会再提示用户输入NAME值我们可以利用Define命令来查看当前环境中所有的可替换变量的值也可以利用Define NAME来查看可替换变量NAME的默认值
三是若用户采用的是脚本文件的话则可以直接把这个定义默认值的语句放在脚本文件的一开始如此的话这个默认值的定义就会随着脚本文件而走用户不用每次使用脚本文件的时候都先去定义默认值这个这个默认值的作用域只是适用于当前的会话当数据库管理员结束当前的会话后这个变量的默认值也就失效了为了在下次重新会话的时候仍然可以利用这个默认值故在脚本文件一开始定义可替换变量的默认值也是非常有必要的
四是利用Define命令来定义可替换变量的时候有一个限制条件即可替换变量必须是字符型的数据若用户想定义的可替换变量是数字型的如员工的薪水此时就需要采用ACCEPT命令否则的话就是一个无效的默认值
若我们有时候不需要这个默认值需要取消他改如何处理呢?此时我们就可以利用undefine命令来取消某个替换变量的默认值当然我们也可以利用Define命令来给某个替换变量设置一个新的值在实际工作中可能还是直接采用Define命令来为其设置一个默认值来的方便
三 测试完毕后保存为脚本文件
为了增强这个SQL语句的可重用性我们往往会把写好的SQL语句保存为脚本文件如此的话下次需要用到的时候直接执行这个脚本语句即可在保存的时候为了有更好的可读性笔者有一些小小的建议
一是最好在脚本文件的开头注释一些这条语句的具体用途俗话说好记性不如烂笔头在几个月后很难确保你还记得这条语句的用途
二是给可替换变量取一些比较直观的名字否则的话当使用数据库管理员可能直接都不知道某个替换变量所代表的含义另外列的替换变量跟查询语句中的替换变量最好进行区分如可以利用前缀进行区别如此做的话这个语句的可阅读性就会增强
三是可利用Prompt命令来输出一些提示信息在编写替换变量的时候要像编写其他程序一样养成为每个可替换变量做好注释的习惯这时作为数据库管理员一个必备的技能之一
四 其他解决方案
针对Oracle数据库SQL语句的交互性而言它还提供了一些其他的解决方案
如利用ACCEPTPAUSEPROMPT三个命令结合使用提供一个模拟的用户输入界面ACCEPT命令可以让数据库管理员指定替换变量的类型因为Define定义替换变量默认值的时候只能够接受字符型数据的默认值若是其他数据类型的默认值的话就要用ACCEPT来定义而PAUSE顾名思义可以让脚本语句暂时运行以提供一个更加友好的用户界面如可以让用户通过输入回车键来执行剩下的脚本语句等等如此就给用户一个喘息核对数据的机会
如可以通过绑定变量的方式来实现替换变量的功能绑定变量只在当前会话中存在同名的绑定变量一最后一个定义为准绑定变量在赋值的时候比较麻烦需要采用Excute命令来给绑定变量进行赋值所以在实际应用中不是很广泛
笔者在这集中解决方案中比较喜欢采用前面两种方式即利用Define与Accept语句来定义替换变量同时采用Prompt与Pause命令来给用户以及自己设计一个人性化的输入界面
