如何声明变量并在同一个 Oracle SQL 脚本中使用它？

How to declare variable and use it in the same Oracle SQL script?

提问by bl4ckb0l7

I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as:

我想写可复用的代码，需要在开头声明一些变量，在脚本中复用，比如：

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

How can I declare a variable and reuse it in statements that follow such as in using it SQLDeveloper.

如何声明一个变量并在后面的语句中重用它，例如在 SQLDeveloper 中使用它。

---

---

Attempts

尝试

• Use a DECLARE section and insert the following SELECT statement in BEGINand END;. Acces the variable using &stupidvar.
• Use the keyword DEFINEand access the variable.
• Using the keyword VARIABLEand access the the variable.

• 使用DECLARE部分和插入下面的SELECT语句BEGIN和END;。使用 访问变量&stupidvar。
• 使用关键字DEFINE并访问变量。
• 使用关键字VARIABLE并访问变量。

But I am getting all kinds of errors during my tries (Unbound variable, Syntax error, Expected SELECT INTO...).

但是我在尝试期间遇到了各种错误（未绑定变量、语法错误、预期SELECT INTO......）。

回答by APC

There are a several ways of declaring variables in SQL*Plus scripts.

在 SQL*Plus 脚本中有多种声明变量的方法。

The first is to use VAR, to declare a bind variable. The mechanism for assigning values to a VAR is with an EXEC call:

首先是使用VAR，来声明一个绑定变量。为 VAR 赋值的机制是通过 EXEC 调用：

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.

当我们想要调用具有 OUT 参数或函数的存储过程时，VAR 特别有用。

Alternatively we can use substitution variables. These are good for interactive mode:

或者，我们可以使用替换变量。这些适用于交互模式：

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

When we're writing a script which calls other scripts it can be useful to DEFine the variables upfront. This snippet runs without prompting me to enter a value:

当我们编写一个调用其他脚本的脚本时，预先定义变量会很有用。此代码段运行时不提示我输入值：

SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

Finally there's the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:

最后是匿名 PL/SQL 块。如您所见，我们仍然可以交互地为声明的变量赋值：

SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

SQL>

回答by Kirill Leontev

Try using double quotes if it's a char variable:

如果它是一个字符变量，请尝试使用双引号：

DEFINE stupidvar = "'stupidvarcontent'";

or

或者

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata  
FROM stupidtable  
WHERE stupidcolumn = '&stupidvar'

upd:

更新：

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 25 17:13:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn od/od@etalon
Connected.
SQL> define var = "'FL-208'";
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = 'FL-208'

CODE
---------------
FL-208

SQL> define var = 'FL-208';
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = FL-208
select code from product where code = FL-208
                                      *
ERROR at line 1:
ORA-06553: PLS-221: 'FL' is not a procedure or is undefined

回答by Matas Vaitkevicius

In PL/SQL v.10

在 PL/SQL v.10 中

keyword declare is used to declare variable

关键字declare用于声明变量

DECLARE stupidvar varchar(20);

to assign a value you can set it when you declare

要分配一个值，您可以在声明时设置它

DECLARE stupidvar varchar(20) := '12345678';

or to select something into that variable you use INTOstatement, however you need to wrap statement in BEGINand END, also you need to make sure that only single value is returned, and don't forget semicolons.

或者要在您使用INTO语句的变量中选择某些内容，但是您需要将语句包装在BEGINand 中END，还需要确保仅返回单个值，并且不要忘记分号。

so the full statement would come out following:

所以完整的声明如下：

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;
END;

Your variable is only usable within BEGINand ENDso if you want to use more than one you will have to do multiple BEGIN ENDwrappings

您的变量仅在内部可用BEGIN，END因此如果您想使用多个变量，则必须进行多次BEGIN END包装

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;

    DECLARE evenmorestupidvar varchar(20);
    BEGIN
        SELECT evenmorestupid into evenmorestupidvar FROM evenmorestupiddata CCC 
        WHERE evenmorestupidid = 42;

        INSERT INTO newstupiddata (newstupidcolumn, newevenmorestupidstupidcolumn)
        SELECT stupidvar, evenmorestupidvar 
        FROM dual

    END;
END;

Hope this saves you some time

希望这可以为您节省一些时间

回答by SVK

If you want to declare date and then use it in SQL Developer.

如果要声明日期，然后在 SQL Developer 中使用它。

DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')

SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT

回答by Katia Savina

Just want to add Matas' answer. Maybe it's obvious, but I've searched for a long time to figure out that the variable is accessible only inside the BEGIN-ENDconstruction, so if you need to use it in some code later, you need to put this code inside the BEGIN-END block.

只想添加Matas的答案。也许很明显，但是我搜索了很长时间才发现该变量只能在BEGIN-END构造内部访问，因此如果您以后需要在某些代码中使用它，则需要将此代码放在BEGIN内部-END 块。

Note that these blocks can be nested:

请注意，这些块可以嵌套：

DECLARE x NUMBER;
  BEGIN
    SELECT PK INTO x FROM table1 WHERE col1 = 'test';

    DECLARE y NUMBER;
    BEGIN
    SELECT PK INTO y FROM table2 WHERE col2 = x;

    INSERT INTO table2 (col1, col2)
      SELECT y,'text'
      FROM dual
      WHERE exists(SELECT * FROM table2);
    COMMIT;
  END;
END;

回答by Laszlo Lugosi

The question is about to use a variable in a script means to me it will be used in SQL*Plus.

问题是在脚本中使用变量对我来说意味着它将在 SQL*Plus 中使用。

The problem is you missed the quotes and Oracle can not parse the value to number.

问题是您错过了引号，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

This sample is works fine because of automatic type conversion (or whatever it is called).

由于自动类型转换（或任何名称），此示例工作正常。

If you check by typing DEFINE in SQL*Plus, it will shows that num variable is CHAR.

如果通过在 SQL*Plus 中键入 DEFINE 来检查，它将显示 num 变量是 CHAR。

SQL>define
DEFINE NUM             = "2018" (CHAR)

It is not a problem in this case, because Oracle can deal with parsing string to number if it would be a valid number.

在这种情况下这不是问题，因为 Oracle 可以处理将字符串解析为数字（如果它是有效数字）。

When the string can not parse to number, than Oracle can not deal with it.

当字符串无法解析为数字时，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

With a quote, so do not force Oracle to parse to number, will be fine:

带引号，所以不要强迫 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

So, to answer the original question, it should be do like this sample:

因此，要回答原始问题，应该像这个示例一样：

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

There is an other way to store variable in SQL*Plus by using Query Column Value.

还有另一种方法可以使用Query Column Value在 SQL*Plus 中存储变量。

The COL[UMN] has new_valueoption to store value from query by field name.

在COL[UMN]有NEW_VALUE从查询的字段名选项储存价值。

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;

As you can see, X.log value was set into the stupid_varvariable, so we can find a X.log file in the current directory has some log in it.

如您所见，X.log 值被设置到了愚蠢的变量变量中，因此我们可以在当前目录中找到一个 X.log 文件，其中有一些日志。

回答by stephen meckstroth

Here's your answer:

这是你的答案：

DEFINE num := 1;       -- The semi-colon is needed for default values.
SELECT &num FROM dual;

回答by yu yang Jian

In Toad I use this works:

在蟾蜍我使用这个作品：

declare 
    num number;
begin 
    ---- use 'select into' works 
    --select 123 into num from dual;

    ---- also can use :=
    num := 123;
    dbms_output.Put_line(num);
end;

Then the value will be print to DBMS OutputWindow.

然后该值将被打印到DBMS Output窗口。

Reference to hereand here2.

参考这里和这里2。

回答by user12273401

Sometimes you need to use a macro variable without asking the user to enter a value. Most often this has to be done with optional script parameters. The following code is fully functional

有时您需要使用宏变量而不要求用户输入值。大多数情况下，这必须使用可选的脚本参数来完成。以下代码功能齐全

column 1 noprint new_value 1
select '' "1" from dual where 2!=2;
select nvl('&&1', 'VAH') "1" from dual;
column 1 clear
define 1

Similar code was somehow found in the rdbms/sql directory.

在 rdbms/sql 目录中以某种方式找到了类似的代码。