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

我想编写可重用的代码，需要在开始声明一些变量并在脚本中重用它们，例如:

DEFINE stupidvar = 'stupidvarcontent';


SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

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

尝试

  • 使用 DECLARE 节并在 BEGIN和 END;中插入以下 SELECT 语句。
  • 使用关键字 DEFINE并访问变量。
  • 使用关键字 VARIABLE并访问变量。

但是在我尝试的过程中出现了各种各样的错误(未绑定变量、语法错误、期望 SELECT INTO...)。

916747 次浏览
小开

如果它是一个 char 变量，尝试使用双引号:

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
小开
最佳答案

在 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>
小开

这就是你的答案:

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

在 PL/SQL v. 10中

关键字声明用于声明变量

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;

希望这能为你节省点时间

小开

如果要声明日期，然后在 SQLDeveloper 中使用该日期。

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


SELECT *
FROM proposal
WHERE prop_start_dt = &PROPp_START_DT
小开

只是想加上 马塔斯的答案。 也许这是显而易见的，但我已经搜索了很长时间，以找出 变量只能在 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;
小开

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

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

由于自动类型转换(或者不管它叫什么) ，这个示例工作得很好。

如果通过在 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 解析为 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

因此，为了回答最初的问题，应该这样做:

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 中存储变量还有另一种方法。

上校[ 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 值被设置为 笨蛋变量，所以我们可以在工作目录中找到一个有一些日志的 X.log 文件。

小开

在《蟾蜍》中，我使用了这样的作品:

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。

小开

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

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

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

小开

如果只需要指定一个参数并在几个地方复制它，一种可能的方法是这样做:

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。它是交叉连接的，可以在查询中多次使用。

小开

您可以使用 with子句并将筛选条件从 where移动到 join。

这里有帮助: 使用 DEFINE 的 OracleSQL 替代方案。

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 开发器中使用它。


提交答案