sqlplus参数的范围 [英] scope of sqlplus parameters

问题描述

以下行为使我感到困惑.调用另一个脚本后，将更改主脚本的(位置)参数.

The following behavior puzzles me. The (positional) parameter of the main script is changed after calling another script.

r1.sql

prompt script r1 : &1

@r2 &1._xxx

prompt script r1 : &1

r2.sql

prompt script r2 : &1

调用脚本:

SQL>sqlplus un/pw @r1 bla

赠予:

script r1 : bla
script r2 : bla_xxx
script r1 : bla_xxx

此行为附近还有吗?将位置参数分配给另一个变量也有同样的问题.当在脚本中的某处使用相同的变量名时，该值将被调用的脚本覆盖.是否有一些有关sqlplus变量范围或如何控制范围的文档?

Is there anyway around this behaviour? Assigning the positional parameters to another variable has the same problem. When the same variable name is used somewhere in the scripts the value is overwritten by a called script. Is there some documentation about the scope of sqlplus variables or how to control the scope?

推荐答案

似乎没有任何方法可以更改此行为.我能找到的唯一模糊的参考文献散布在替换变量文档中，其中最连贯的(但很难确定)在

There doesn't seem to be any way to change this behaviour. The only vague references I can find are scattered across the substitution variable documentation, with perhaps the most coherent (but hardly definitive) being in the START docs, though it's pretty similar in @ and @@:

START命令使用参数的值定义参数. 论点如果您在此会话中再次启动脚本，则可以 输入新参数或忽略参数以使用旧值.

The START command defines the parameters with the values of the arguments; if you START the script again in this session, you can enter new arguments or omit the arguments to use the old values.

忽略参数"部分表示在上一次调用中设置的位置参数仍然可用，这意味着它们也必须在父脚本中设置，并且只能覆盖任何原始位置值.这也有点误导-如果再次START脚本，但是使用不同的参数运行了不同的脚本，那么您将不会从措辞中得到相同的旧值；否则，可能会产生误解.将r3.sql放入混音中，并将r1.sql修改为:

The 'omit the arguments' part implies the positional parameters set in the previous call are still available, which means they have to be set in the parent script as well, and can only have overridden any original positional values. It's also a bit misleading - if you START the script again but have run a different script with different parameters in between, you won't get the same old values you might expect from how that's worded; with an r3.sql thrown into the mix and r1.sql amended to:

prompt script r1 : &1
@r2 xxx
prompt script r1 : &1
@r3 yyy
prompt script r1 : &1
@r2
prompt script r1 : &1

...文档中的措辞意味着对r2的第二次调用仍会看到xxx，但当然不会:

... the wording in the docs implies that the second call to r2 would still see xxx, but of course it won't:

script r1 : bla
script r2 : xxx
script r1 : xxx
script r3 : yyy
script r1 : yyy
script r2 : yyy
script r1 : yyy

没有任何像子shell一样运行r2的概念.您启动的脚本刚刚加载到位，有点像包含在.表示法的Shell脚本中的文件.再次对位置参数(或相关的命名参数)进行DEFINE d，因此没有作用域.绑定变量的行为方式相同.在使用START调用单独的脚本并将其内容嵌入主脚本之间没有行为上的区别.

There isn't any concept of running r2 in the equivalent of a sub-shell. The script your start is just loaded in place, kind of like a file included in a shell script with the . notation. The positional (or if relevant the named) parameters are DEFINEd again, and so there is no scope. Bind variables behave the same way. There is no behavioural difference between calling a separate script with START and just embedding its contents in your main script.

还有趣的是，仅重新定义了传递的位置变量，因此，如果使用较少的参数调用内部脚本，则某些原始文件会保留下来；而且您无需参考它们即可看到效果.实际上，r2可以是一个空文件，并且如果您将r1设置为:

It's also interesting to note that only the passed positional variables are redefined, so if you call your inner script with fewer parameters then some of the originals are preserved; and you don't need to refer to them for the effect to be seen. In fact r2 can be an empty file, and if you have r1 as:

prompt script r1 &1 &2
@r2 xxx
prompt script r1 &1 &2

...并以以下方式运行它:

... and run it as:

sqlplus x/yy @r1 foo bar

...您得到:

script r1 foo bar
script r1 xxx bar

没有一个对您有很大帮助，但是评论太久了...

None of which helps you very much, but it's too long for a comment...

唯一的解决方法似乎是在每个脚本中定义分配了位置参数的唯一命名变量，但这是一个痛苦且并非万无一失的事情.

The only workaround seems to be to define uniquely-named variables in each script that are assigned the positional parameters, but that's a pain and hardly foolproof.

这篇关于sqlplus参数的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！