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

Question

我想编写可重用的代码，并需要在脚本开头声明一些变量，然后在整个脚本中重复使用，例如：

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

我该如何在SQLDeveloper中声明一个变量，并在后续语句中重复使用它？

---

尝试过的方法

• 使用DECLARE部分，在BEGIN和END语句块内插入以下SELECT语句。可以使用&stupidvar访问变量。
• 使用关键字DEFINE并访问变量。
• 使用关键字VARIABLE并访问变量。

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

- bl4ckb0l7

---

注意，接受的答案中@APC提供的方法可以在不使用PL/SQL的情况下使用，例如在SQL Developer工作表中根据您的问题。只需在一行上声明变量（无分号），然后在exec行上设置其值（以分号结束），然后执行选择语句。最后，将其作为脚本运行（F5），而不是作为语句运行（F9）。 - Amos M. Carpenter

Answer 1

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

第一种方法是使用 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>

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

或者我们可以使用替代变量。这些对于交互模式很有用：

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>

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

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>

最后是匿名的 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>

- APC

---

除了您使用了"绑定变量"这个术语外，一切都不错。VAR声明创建一个绑定变量，而ACCEPT或DEFINE则创建一个替换变量。 - Dave Costa

能否将变量和字符串连接起来？ - Ecropolis

@Ecropolis - 是的，在SQL Plus中默认使用句点。使用SET CONCAT来定义替换变量名称与紧随其后的字母数字字符之间分隔符的字符。在PL/SQL或SQL中使用双竖线 || 进行连接。 - Laszlo Lugosi

如果SQL是一种标准语言，那么为什么很难找到一个在任何地方都能正常工作的规范参考？WTF??? - jww

@jww - SQL是一种标准，但它并不总是指定确切的语法，因此不同的RDBMS产品可以以不同的方式实现；日期算术就是一个很好的例子。此外，像Oracle这样的旧数据库产品通常在标准覆盖之前引入了功能：例如分层CONNECT BY语法。但在这种情况下，我们讨论的是SQL*Plus，它是一个客户端工具，因此无论如何都不受ANSI标准的覆盖。 - APC

仅供澄清。使用此方法，您无法创建所有类型的数据类型。例如，无法创建日期或时间戳类型的变量。 - D. Lohrsträter

Answer 2

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

DEFINE stupidvar = "'stupidvarcontent'";

或者

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata  
FROM stupidtable  
WHERE stupidcolumn = '&stupidvar'

更新：

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

- Kirill Leontev

---

谢谢您的回答，但是如果我将变量包含在双引号中，我会得到一个“ORA-01008：未绑定所有变量”的错误。 - bl4ckb0l7

当执行 DEFINE num = 1; SELECT &num FROM dual; 时，会出现以下错误信息：ORA-01008: not all variables bound。 - bl4ckb0l7

@bl4ckb0l7 - 我敢打赌你并不是在 SQL*Plus 中尝试这个。 - Laszlo Lugosi

这个答案真是救命稻草！我在报告撰写中经常使用DEFINE，从未遇到过问题。但在一个重要的项目中，我一直收到错误提示，我可以看出这是因为变量被传递为数字而不是字符串值。谢谢！ - SherlockSpreadsheets

Answer 3

在PL/SQL v.10中，使用关键字“declare”来声明变量。

DECLARE stupidvar varchar(20);

你可以在声明时设置变量的值。

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

如果要将某个内容选择为变量，您需要使用INTO语句，但是您需要将语句包装在BEGIN和END中，并确保仅返回单个值，并且不要忘记使用分号。

因此完整的语句如下所示：

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

你的变量只能在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;

希望这能为您节省一些时间

- Matas Vaitkevicius

Answer 4

如果您想声明日期并在SQL开发人员中使用它。

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

SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT

- SVK

---

在Oracle SQL Developer 24.1.349中，你的工作适配会弹出一个提示，要求输入PROPp_START_DT的值。有什么想法吗？ DEFINE id_num = '00022'SELECT * FROM proposal WHERE PLAN_N = &id_num; - Rick Pack

Answer 5

这个问题是关于在脚本中使用变量，意味着它将在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

还有一种在SQL*Plus中存储变量的方法，可以使用查询列值。

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;

正如您所看到的，X.log的值被设置为stupid_var变量，因此我们可以在当前目录中找到一个包含一些日志的X.log文件。

- Laszlo Lugosi

Answer 6

我想补充Matas的回答。 也许很明显，但我搜索了很长时间才发现变量只能在BEGIN-END结构内访问，因此如果你需要在稍后某些代码中使用它，你需要将这些代码放在BEGIN-END块内。

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

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;

- Katia Savina

Answer 7

在 Toad 中，我使用以下方法：

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;

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

参考这里和这里2。

- yu yang Jian

Answer 8

这是你的答案：

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

- stephen meckstroth

---

和我一样。我使用ODT并运行：DEFINE num := 1; SELECT num FROM dual;但是我得到的是：ORA-00904：“NUM”：无效标识符00904. 00000 - “%s：无效标识符”原因：操作：错误发生在第2行第8列。 - toha

Answer 9

您可以使用with子句，将过滤条件从where移动到join中。

这里有帮助：Oracle SQL替代DEFINE的方法。

with
 mytab as (select 'stupidvarcontent' as myvar from dual)
SELECT
 stupiddata
FROM
  stupidtable a 
 inner join
  mytab b
 on
  a.stupidcolumn = b.myvar
WHERE ...;

它适用于Oracle 12R2。
它仅适用于一个SQL命令。
这是标准的ANSI表示法。
我在使用SQL Developer。

- David Lukas

Answer 10

如果您只需要在多个地方指定一次参数并复制它，则可以采用以下方法之一：

SELECT
  str_size  /* my variable usage */
  , LPAD(TRUNC(DBMS_RANDOM.VALUE * POWER(10, str_size)), str_size, '0') rand
FROM
  dual  /* or any other table, or mixed of joined tables */
  CROSS JOIN (SELECT 8 str_size FROM dual);  /* my variable declaration */

这段代码生成一个包含8个随机数字的字符串。

请注意，我创建了一个名为str_size的别名，它保存常量8。它被交叉连接以便在查询中多次使用。

- Diego Queiroz