如何在同一个SQL脚本中声明和使用变量?(Oracle SQL)

I am doing some tests written in SQL. I want to write reusable code and therefore want to declare some variables at the beginning and reuse them in the script, like this:

我正在做一些用SQL编写的测试。我想编写可重用的代码，因此想在开始时声明一些变量，并在脚本中重用它们，如下所示:

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

How can I declare a variable and reuse it in statements that follow? (I am using SQLDeveloper to access an Oracle DB.)

如何声明一个变量并在下面的语句中重用它?(我正在使用SQLDeveloper访问Oracle DB。)

I tried so far:

我试着到目前为止:

• Use a DECLARE section and insert the following SELECT statement in BEGIN and END;. Acces the variable using &stupidvar.
• 在开始和结束时使用声明部分并插入下面的SELECT语句;使用& idiot dvar输入变量。
• Use the keyword DEFINE and access the variable.
• 使用关键字DEFINE并访问变量。
• Using the keyword VARIABLE and access the the variable.
• 使用关键字变量并访问变量。

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

但是在我尝试的过程中，我得到了各种各样的错误(未绑定变量、语法错误、expect SELECT INTO…)。

While searching the net I find a lot of different explanations, concerning SQL, PL/SQL.

在搜索网络时，我发现了许多关于SQL、PL/SQL的不同解释。

Thank you in advance!

提前谢谢你!

7 个解决方案

#1

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

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

The first is to use VAR. 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.

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

Alternativley we can use subsitution 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:

当我们编写一个调用其他脚本的脚本时，预先定义变量是有用的:

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>

#2

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

如果是char变量，请使用双引号:

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

#3

In PL/SQL v.10

在PL / SQL v.10

keyword declare is used to declare variable

关键字声明用于声明变量

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 INTO statement, however you need to wrap statement in BEGIN and END, also you need to make sure that only single value is returned, and don't forget semicolons.

或者，要在使用into语句的变量中选择一些东西，但是需要在开始和结束中包装语句，还需要确保只返回单个值，不要忘记分号。

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 BEGIN and END so if you want to use more than one you will have to do multiple BEGIN END wrappings

你的变量只能在开始和结束中使用，所以如果你想要使用多个开始结束包装，你需要做多个开始结束包装

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

希望这能节省你一些时间

#4

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

#5

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-END construction, so if you need to use it in some code later, you need to put this code inside the BEGIN-END block.

只是想加上Matas的答案。也许这是显而易见的，但是我已经搜索了很长一段时间来确定变量只能在开始端结构中访问，所以如果以后需要在某些代码中使用它，您需要将这些代码放在开始端块中。

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;

#6

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无法将值解析为number。

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解析为number，这样就可以了:

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.

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

The COL[UMN] has new_value option 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_var variable, so we can find a X.log file in the current directory has some log in it.

如你所见，X。将日志值设置为stupid_var变量，这样我们就可以找到一个X。当前目录中的日志文件中有一些日志。

#7

Here's your answer:

这是你的回答:

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