如何在同一Oracle SQL脚本中声明变量并使用它? [英] How to declare variable and use it in the same Oracle SQL script?

问题描述

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

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;

如何声明变量并在随后的语句中重用它，例如在使用SQLDeveloper时.

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

尝试

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

• Use a DECLARE section and insert the following SELECT statement in BEGIN and END;. Acces the variable using &stupidvar.
• Use the keyword DEFINE and access the variable.
• Using the keyword VARIABLE and access the the variable.

但是我在尝试过程中遇到各种错误(未绑定变量，语法错误，预期的SELECT INTO ...).

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

推荐答案

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

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

第一个是使用VAR来声明绑定变量.为VAR分配值的机制是通过EXEC调用:

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

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

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

或者，我们可以使用替换变量.这些对于互动模式非常有用:

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>

最后有一个匿名PL/SQL块.如您所见，我们仍然可以交互地将值分配给已声明的变量:

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

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>

这篇关于如何在同一Oracle SQL脚本中声明变量并使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！