define,variable和declare的区别详解程序员

一、define(即host变量)
     Host变量主要作用是起到一个替代变量的作用,是主机环境可以和oracle进行交互的一个媒介 。通过define定义host变量的时候必须指定变量名和变量的值,如果变量名已经存在于host变量中,则自动覆盖,这个值不可以指定类型,一律按char存储。

1.定义的格式:

define variable_name = value

注意:必须定义的时候就赋值,否则define variable_name是显示已经存在的host变量值,不会报告未定义。另外可以使用define命令显示单个(define variable_name,不能显示多个)或全部(define)的host变量的值和类型(类型都是char)。
2.作用域

首先要了解host变量只是在当前session环境中有效。

host变量启用和关闭的命令是:set define on和set define off。

这在sqlplus和plsql developer的command window中都可以使用,在sql或plsql中引用host变量,使用&符号,只是做一个简单的替换动作,比如:
define x=TEST
Select * from ‘&x’;那么会报表找不到的错误,因为x被替换为test,那么’TEST’表是不存在的,TEST表存在,多了个单引号。
也可以不定义,直接在sql或plsql中在字符串前面加&符号,会提示你输入变量的值,然后替换。这种情况下是临时的变量,define命令查不到。

※经常使用host变量的场合是在写脚本的时候,脚本的有些地方经常变化,其他地方固定,那么可以使用&引用。

2,Variable(即绑定变量)  
     绑定变量主要是在sql中使用,达到sql解析一次,执行多次,从而提高效率的目的。绑定变量和host变量一样,也有定义的,也有临时性的。(临时性的在动态sql或动态plsql中通过占位符和using使用),其他的如sql会自动使用绑定变量的情况,此处不专门讨论。定义的绑定变量也是在当前session中有效。

1.定义格式

    Sqlplus中可以使用大于等于3个字符表示一个命令,这里我们用var,var命令是声明一个绑定变量,只能给予名称和类型,定义的时候不能赋值,赋值可以在plsql中或者采用函数赋值(而host变量定义的时候必须赋值)。
Var var_name type   声明一个指定类型的绑定变量。

对于var或var var_name只是显示所有的绑定变量或者指定名称的绑定变量的name和type,不显示其值,显示其值用print命令,可以采用help print查看。

Print是输出所有绑定变量的值,print name1 name2 ….是输出指定的绑定变量的值,多个用空格分割。对于host变量的define要么输出全部,要么输出单个,没有指定数目的。Print后面的绑定变量名也可以加前缀冒号,效果一样。

2.使用:
1.绑定变量在sql和plsql中直接引用必须加前缀 :。如要引用绑定变量a,则是 :a。
2.在真正运行的PLSQL程序中,比如自动运行,有自己的一套机制。
3.初始化和应用绑定变量,初始化定义的绑定变量,可以使用过程和函数,其中过程可以内部给绑定变量赋值、也可以参数是绑定变量,参数模式为out返回。使用函数和过程差不多,还可以使用call 函数来赋值,另外还可以使用SQLPLUS中的execute命令直接赋值,多个绑定变量用分号隔开。
注意:PL/SQL中NULL值可赋值给绑定变量。

例子:

(1)、使用过程和sqlplus命令初始化绑定变量:
variable a number;
begin
  :a := 1;
  dbms_output.put_line(:a);
end;
/

(2)、使用execute初始化,初始化多个用分号隔开
var x number; 
var y number;
exec :x :=1; :y :=2;

(3)当然也可以将函数或过程的参数定义为out模式来初始化绑定变量,我们这里使用call命令调用函数把结果传给绑定变量:

调用格式为:call function(参数列表) into :绑定变量。

pl/sql中的参数和定义的变量(包括全局变量和临时变量)都会内部转为绑定变量,所以尽量在pl/sql中尽量使用静态sql,而不要使用动态sql,如果使用动态sql,要尽量加上绑定变量。

绑定变量的基本作用 :

绑定变量主要是sql的执行过程中,在解析sql之后会进行共享池(SGA)的检查,看优化器有没有分析优化过这个sql,环境必须完全一致才可以(包括大小写的一致,session情况一致等)。那么可以达到一次分析,多次执行的目的,这就是软解析,否则要进过解析,优化,行资源生成等一系列sql执行的过程,因为sql优化需要耗费很多资源,如果硬解析,sql性能会下降。

看SGA中的sql是否是软解析被调用多次,可以查看v$sql或v$sqlarea视图,查看列sql_text,executions,如:
select sql_text,executions from v$sql where sql_text like ‘%trademark%’;

SQL_TEXT        EXECUTIONS<被执行的次数>
select * from trademark where id=:tid        6

三、declare

declare属于PL/SQL的语法

功能说明:SQL块中声明变量。只在PL/SQL块里有效!

语 法:declare变量名称 变量类型;

set serveroutput on;

declare

name varchar2(10);

begin

name := ‘jack’;

dbms_output.put_line(name);

end;

/

四、总结

    define,variable和declare都是用来定义变量的,作用域不同。其中define,variable定义的为全局变量,在整个session中都有效。declare定义的是局部变量,只在定义了该变量的PL/SQL块里有效!

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/tech/aiops/1813.html

(0)
上一篇 2021年7月15日 23:28
下一篇 2021年7月15日 23:28

相关推荐

发表回复

登录后才能评论