首页 文章

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

提问于
浏览 次
93

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

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

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

Attempts

  • 使用DECLARE部分并在 BEGIN 和 END; 中插入以下SELECT语句 . 使用 &stupidvar 访问变量 .

  • 使用关键字 DEFINE 并访问该变量 .

  • 使用关键字 VARIABLE 并访问该变量 .

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

sql oracle variables declaration

7 回答

  • 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特别有用 .

    Alternativley我们可以使用替换变量 . 这些适用于交互模式：

    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>
    回复于
  • 115

    如果它是char变量，请尝试使用双引号：

    DEFINE stupidvar = "'stupidvarcontent'";

    要么

    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
    回复于
  • 9

    在PL / SQL v.10中

    keyword 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;

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

    回复于
  • 3

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

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

SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT
    回复于
  • 4

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

    还有另一种方法可以使用 Query Column Value 在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文件中有一些日志 .

    回复于
  • 19

    只是想添加Matas ' answer. Maybe it'显而易见，但我已经搜索了很长时间才弄明白 variable is accessible only inside the BEGIN-END 构造，所以如果你以后需要在某些代码中使用它，你需要 put this code inside the BEGIN-END block .

    请注意这些 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;
    回复于
  • 2

    这是你的答案：

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

相关问题