Oracle 变量 之 define variable declare 用法及区别

时间:2023-03-08 17:31:29

Oracle 变量 之 define variable declare 用法及区别

1 扯蛋

Oracle 提供了几种不同的定义变量的方式: def(ine) ,var(iable), declare 分别适用于不同的环境.

define
sqlplus 环境(command窗口) 中用于定义变量, 适用于人机交互处理,或者sql脚本。
variable
plsql 匿名块中使用。非匿名块中不能使用。
declare
plsql 块中使用,适用于匿名块或者非匿名块。

2 define和accept

define 可以定义一个变量,在调用该变量时使用符号 & . 人机交互给变量赋值时使用acc(ecpt)命令。

  • 作为查询值示例

    define abc=1;
    select * from dual where rownum=&abc;

    执行结果如下:

    SQL>   define abc=1;
    SQL> select * from dual where rownum=&abc;
    old 1: select * from dual where rownum=&abc
    new 1: select * from dual where rownum=1 D
    -
    X
  • 作为变量传入匿名块示例
    • 与declare配合使用

      set serveroutput on
      define abc=1;
      declare abc varchar(2);
      begin
      select &abc into abc from dual;
      dbms_output.put_line(abc);
      end;
      /

      执行结果如下:

       SQL>  set serveroutput on
      SQL> define abc=1;
      SQL> declare abc varchar(2);
      2 begin
      3 select &abc into abc from dual;
      4 dbms_output.put_line(abc);
      5 end;
      6 /
      old 3: select &abc into abc from dual;
      new 3: select 1 into abc from dual;
      1
    • 与variable 配合使用

            set serveroutput on
      define abc=1;
      variable abc varchar2(10);
      begin
      :abc := &abc;
      dbms_output.put_line(:abc);
      end;
      /

      执行结果如下:

            SQL>  set serveroutput on
      SQL> define abc=1;
      SQL> variable abc varchar2(10);
      SQL> begin
      2 :abc := &abc;
      3 dbms_output.put_line(:abc);
      4 end;
      5 /
      old 2: :abc := &abc;
      new 2: :abc := 1;
      1 PL/SQL procedure successfully completed.
  • 人机交互赋值示例 通过人机交互给变量赋值,需要使用acc(ept)

        SQL> acc i number prompt "Please input a number:"
    Please input a number:4
    SQL> select &i from dual;
    old 1: select &i from dual
    new 1: select 4 from dual 4
    ----------
    4

3 variable

3.1 variable 特点

通过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

3.2 variable 与define配合使用

col scn for 9999999999999999999999
var a number; -- 通过variable 定义变量
begin
-- 在匿名块中给 :a 变量赋值
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER into :a from dual;
end;
/
define current_scn = :a
select &current_scn as scn from dual;
  • 执行结果

      SQL> col scn for 9999999999999999999999
    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> define current_scn = :a
    SQL> select &current_scn as scn from dual;
    old 1: select &current_scn as scn from dual
    new 1: select :a as scn from dual SCN
    -----------------------
    13946807321486

4 declare

declare 定义变量后,变量标识符在整个块结构内部都代表变量,在结构块外部不可用, 也就是说 declare的作用域只是结构体内部。这点与variable定义变量不同。变量的调用方式也不一样, declare定义的变量, 不需要添加任何额外的标记,而variable 定义的变量需要和冒号配合使用。

  • 示例

    set serveroutput on
    declare current_scn number;
    begin
    -- 注意: into 后面的变量就是declare定义的变量
    select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER into current_scn from dual;
    dbms_output.put_line(current_scn);
    end;
    /
  • 执行结果

    SQL> set serveroutput on
    SQL> declare current_scn number;
    2 begin
    3 -- 注意: into 后面的变量就是declare定义的变量
    4 select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER into current_scn from dual;
    5 dbms_output.put_line(current_scn);
    6 end;
    7 /
    13946807319270 PL/SQL procedure successfully completed.

Author: halberd.lee

Created: 2019-07-04 Thu 15:03

Validate