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

问题描述

你能计算一个表达式并将结果分配给一个替换变量吗?

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

就我而言，我需要调用一个包含替换变量的旧脚本.我需要在调用脚本之前计算该变量的值.我正在使用 Oracle SQL 和 SQL*Plus

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

这是基本问题:

def this_num = 2+2
@old_script

在 old_script.sql 中

Inside old_script.sql

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

产量:'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?

推荐答案

The Working Solution

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

这是相关的文字.

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;

COLUMN 命令中的 NEW_VALUE 选项隐式地创建了一个称为mynv"的替换变量.变量不是物理的创建直到查询引用列 LAST_NAME.当查询完成后，变量mynv"保存最后检索到的值列姓氏":

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":

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

所以你这样做:

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

select '&&THIS_NUM' from dual;

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

多田！

为了娱乐价值，这里有一个非常邪恶的解决方法，如果变量碰巧在引号之外使用，它就会中断:

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)

那么:

select '&&this_num' from dual;

评估为:

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

产生的结果:

4

这篇关于如何将表达式的结果分配给 SQL 替换变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！