oracle 脚本 赋值给变量,如何声明变量并在同一个Oracle SQL脚本中使用它?

问题是要在脚本中使用一个变量,这意味着它将在SQL*PLUS中使用。

问题是您遗漏了引号,Oracle无法将值解析为数字。

SQL> DEFINE num = 2018

SQL> SELECT &num AS your_num FROM dual;

old 1: SELECT &num AS your_num FROM dual

new 1: SELECT 2018 AS your_num FROM dual

YOUR_NUM

----------

2018

Elapsed: 00:00:00.01

由于自动类型转换(或它被称为什么),此示例工作正常。

如果您通过在SQL*PLUS中键入define进行检查,它将显示num变量是char。

SQL>define

DEFINE NUM = "2018" (CHAR)

在这种情况下,这不是问题,因为如果字符串是一个有效的数字,Oracle可以处理从字符串到数字的解析。

当字符串不能解析为数字时,Oracle就不能处理它。

SQL> DEFINE num = 'Doh'

SQL> SELECT &num AS your_num FROM dual;

old 1: SELECT &num AS your_num FROM dual

new 1: SELECT Doh AS your_num FROM dual

SELECT Doh AS your_num FROM dual

*

ERROR at line 1:

ORA-00904: "DOH": invalid identifier

有了引号,所以不要强制Oracle解析为数字,这样就可以了:

17:31:00 SQL> SELECT '&num' AS your_num FROM dual;

old 1: SELECT '&num' AS your_num FROM dual

new 1: SELECT 'Doh' AS your_num FROM dual

YOU

---

Doh

因此,要回答最初的问题,应该这样做:

SQL> DEFINE stupidvar = 'X'

SQL>

SQL> SELECT 'print stupidvar:' || '&stupidvar'

2 FROM dual

3 WHERE dummy = '&stupidvar';

old 1: SELECT 'print stupidvar:' || '&stupidvar'

new 1: SELECT 'print stupidvar:' || 'X'

old 3: WHERE dummy = '&stupidvar'

new 3: WHERE dummy = 'X'

'PRINTSTUPIDVAR:'

-----------------

print stupidvar:X

Elapsed: 00:00:00.00

通过使用

查询列值

.

这个

科尔

[NUM]

纽尔值

按字段名存储查询值的选项。

SQL> COLUMN stupid_column_name new_value stupid_var noprint

SQL> SELECT dummy || '.log' AS stupid_column_name

2 FROM dual;

Elapsed: 00:00:00.00

SQL> SPOOL &stupid_var.

SQL> SELECT '&stupid_var' FROM DUAL;

old 1: SELECT '&stupid_var' FROM DUAL

new 1: SELECT 'X.log' FROM DUAL

X.LOG

-----

X.log

Elapsed: 00:00:00.00

SQL>SPOOL OFF;

如您所见,x.log值被设置为

笨拙的

变量,因此我们可以在当前目录中找到一个x.log文件,其中包含一些日志。