如何将表达式的结果分配给SQL替换变量？

Can you evaluate an expression and assign the result to a substitution variable?

您可以评估表达式并将结果分配给替换变量吗?

In my case, I need to call an old script which contains a substitutions variable. I need to calculate a value for that variable before calling the script. I'm using Oracle SQL and SQL*Plus

在我的情况下,我需要调用一个包含替换变量的旧脚本。我需要在调用脚本之前计算该变量的值。我正在使用Oracle SQL和SQL * Plus

Here's the basic problem:

这是基本问题:

def this_num = 2+2
@old_script

Inside old_script.sql

select '&this_num' from dual;  -- Probably shouldn't change this

Yields: '2+2'

Is there a way to force evaluation so that the substitution variable gets the result of an expression rather than the expression itself?

有没有办法强制进行评估,以便替换变量得到表达式的结果而不是表达式本身?

2 个解决方案

#1

def this_num = 2+2
@old_script

In the old_script, you can say

在old_script中,你可以说

select &this_num from dual;

You don't need to use '' around the variable name. This should work.

您不需要在变量名称周围使用''。这应该工作。

#2

The Working Solution

I found a working answer here: https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_5

我在这里找到了一个有效的答案:https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_5

Here's the relevant text.

这是相关的文字。

2.5 Storing a Query Column Value in a Substitution Variable

2.5在替换变量中存储查询列值

Data stored in the database can be put into substitution variables:

存储在数据库中的数据可以放入替换变量中:

SQL> column last_name new_value mynv
SQL> select last_name from employees where employee_id = 100;

The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called "mynv". The variable is not physically created until a query references the column LAST_NAME. When the query finishes, the variable "mynv" holds the last retrieved value from column "last_name":

COLUMN命令中的NEW_VALUE选项隐式创建名为“mynv”的替换变量。在查询引用LAST_NAME列之前,不会物理创建该变量。查询完成后,变量“mynv”保存“last_name”列中最后一次检索到的值:

SQL> define mynv
DEFINE mynv      = "King" (CHAR)

So you do it like this:

所以你这样做:

column DUMMY_COLUMN_NAME new_value THIS_NUM
select 2+2 DUMMY_COLUMN_NAME from dual;

select '&&THIS_NUM' from dual;

'4'
------------
           4

Tada!

The Evil Solution

For entertainment value, here's a really evil workaround which would break if the variable ever happens to be used outside of quotes:

对于娱乐价值,这是一个非常邪恶的解决方法,如果变量曾经恰好在引号之外使用,它将会破坏:

define this_num = "' || 2+2 ||'" (CHAR)

Then:

select '&&this_num' from dual;

evaluates to:

select '' || 2+2 ||'' from dual;

Which yields:

'

4