Oracle 变量之definevariabledeclare
⽤法及区别
1 扯蛋
Oracle 提供了⼏种不同的定义变量的⽅式: def(ine) ,var(iable), declare 分别适⽤于不同的环境.
define sqlplus 环境(command 窗⼝) 中⽤于定义变量, 适⽤于⼈机交互处理，或者sql 脚本。variable plsql 匿名块中使⽤。⾮匿名块中不能使⽤。declare plsql 块中使⽤，适⽤于匿名块或者⾮匿名块。2 define 和accept
define 可以定义⼀个变量，在调⽤该变量时使⽤符号 & . ⼈机交互给变量赋值时使⽤acc(ecpt)命令。
作为查询值⽰例
执⾏结果如下：
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 配合使⽤
执⾏结果如下：
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 配合使⽤
执⾏结果如下:Table of Contents
define abc=1;
select  * from  dual where  rownum=&abc;
set  serveroutput on
define abc=1;
declare  abc varchar (2);
begin
select  &abc into  abc from  dual;
dbms_output.put_line(abc);
end ;/
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;
/
执⾏结果如下：
define的基本用法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 ¤t_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 ¤t_scn as scn from dual;
old  1: select ¤t_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