oracle 赋值给define,Oracle 变量 之 define variable declare 用法及区别

通过variable 定义变量，变量作用域为当前sqlplus环境。需要通过 : 来标记为变量。比如:

variable a varchar2(10); -- 此时定义了变量，:a 代表了变量，而 a 并不是变量，只是字母 a

var current_scn number;

begin

select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER into :current_scn from dual;

dbms_output.put_line(' '||:current_scn);

end;

/

执行结果如下：

SQL> var current_scn number;

SQL> begin

2 select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER into :current_scn from dual;

3 dbms_output.put_line(' '||:current_scn);

4 end;

5 /

13934393999029

PL/SQL procedure successfully completed.

下面的示例表明variable定义的变量在当前sqlplus环境中都可用。

SQL> var a number;

SQL> begin

2 select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER into :a from dual;

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> select :a from dual;

:A

----------

1.3947E+13