Oracle变量定义的三种方式definevariabledeclare.docx

1、Oracle变量定义的三种方式definevariabledeclare1、define（即host变量）Host变量主要作用是起到一个替代变量的作用，是主机环境可以和oracle进行交互的一个媒介。 通过define定义host变量的时候必须指定变量名和变量的值，如果变量名已经存在于host变量中，则自动覆盖，这个值不可以指定类型，一律按char存储。 DEFINE 变量只在当前session环境中有效。(1).语法：define variable_name = value(2).声明和初始化DEFINE变量/声明define变量的时候必须同时初始化赋值变量SQL define num=1;

2、另外可以使用define命令显示单个(define variable_name，不能显示多个)或全部(define)的host变量的值和类型(类型都是char)。(3).显示指定的DEFINE变量值和类型/使用define def_name命令显示指定DEFINE变量/的值和类型(DEFINE变量类型都为char)SQL define num;DEFINE num = 1(CHAR)(4).显示所有的DEFINE变量值和类型/使用define显示所有DEFINE变量的值和类型(DEFINE变量类型都为char)SQL defineDEFINE _CONNECT_IDENTIFIER = TOP

3、PROD(CHAR)DEFINE _SQLPLUS_RELEASE = 902000100(CHAR)DEFINE _EDITOR = Notepad(CHAR)DEFINE _O_VERSION = Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductiWith the Partitioning, OLAP, Data Mining and Real Application Testing options(CHAR)DEFINE _O_RELEASE = 1102000300(CHAR)了解对于ho

4、st变量启用和关闭的命令是：set define on和set define off。(5).关闭DEFINE变量定义功能/关闭defineSQLsetdefine off;SQL define num=1;SQLselect* fromocc_file whererownum=#select* fromocc_file whererownum=&numORA-01008: 并非所有变量都已绑定(6).开启DEFINE变量定义功能/打开defineSQLsetdefine on;SQL define num=1;SQLselect* fromocc_file whererownum=#OCC0

5、1 OCC02- -020040 松荣五金-SRWJ(7).引用DEFINE变量这在sqlplus和plsql developer的command window中都可以使用，在sql或plsql中引用host变量，使用&符号，相当于一个简单的替换动作，比如/错误案例SQL define x=occ_file;SQLselect* from&xwhererownum=1;select* fromocc_filewhererownum=1ORA-00903: 表名无效SQL报错是因为x被替换为occ_file，语句变为select * from occ_file，occ_file表是不存在的，多了

6、个单引号。/正确案例SQL define x=occ_file;SQLselect* from&x whererownum=1;OCC01 OCC02- -020040 松荣五金-SRWJSQL(8).引用临时DEFINE变量可以不预先声明初始化define变量，直接在sql或plsql中在字符串前面加&符号，会提示你输入变量的值，然后替换。这种情况下是临时的变量，define命令查不到/不预先声明和初始化define变量直接使用&引用SQLselect* from&y whererownum=1;/弹框提示输入变量y的值，输入occ_file,后打印出一下信息OCC01 OCC02- -02

7、0040 松荣五金-SRWJ SQL经常使用host变量的场合是在写脚本的时候，脚本的有些地方经常变化，其他地方固定,那么可以使用&引用。2、Variable（即邦定变量）绑定变量主要是在sql中使用，达到sql解析一次，执行多次，从而提高效率的目的。绑定变量和host变量一样，也有定义的，也有临时性的。(临时性的在动态sql或动态plsql中通过占位符和using使用)，其他的如sql会自动使用绑定变量的情况，此处不专门讨论。定义的绑定变量也是在当前session中有效。 绑定变量以下特点：绑定变量在sql和plsql中直接引用必须加前缀 :。如要引用绑定变量a，则是 :a；在真正运行的PL

8、SQL程序中，比如自动运行，有自己的一套机制；初始化和应用绑定变量,初始化定义的绑定变量，可以使用过程和函数，其中过程可以内部给绑定变量赋值、也可以参数是绑定变量，参数模式为out返回。使用函数和过程差不多，还可以使用call函数来赋值。sqlplus中可以使用大于等于3个字符表示一个命令，这里我们用var，var命令是声明一个绑定变量，只能给予名称和类型，定义的时候不能赋值，赋值可以在plsql中或者采用函数赋值(而host变量定义的时候必须赋值)。(1).语法：var var_name type (2).声明绑定变量/使用var声明两number类型的变量num1、num2SQL var

9、num1 number;SQL var num2 number;(3).显示指定绑定变量/var num1命令显示指定绑定变量名称、数据类型SQL var num1variable num1datatype NUMBERSQL(4).显示所有绑定变量/var命令显示所有绑定变量的变量名称、数据类型SQL varvariable num1datatype NUMBERvariable num2datatype NUMBER(5).使用PL/SQL初始化绑定变量/给绑定变量赋值SQLbegin:num1:=1;:num2:=2;end;/PL/SQL proceduresuccessfully c

10、ompletednum1-1num2-2SQL(6).使用EXECUTE初始化绑定变量/使用execute初始化，初始化多个用分号隔开 SQLexec:num1:=1;:num2:=2PL/SQL proceduresuccessfully completednum1-1num2-2SQL(7).显示指定的绑定变量的值/使用print var_name命令输出指定的绑定变量值SQL print num1;num1-1SQL print num2;num2-2(8).显示所有绑定变量的值/print 命令输出所有绑定变量的值SQL printnum1-1num2-2SQL(9).引用绑定变量/使用:var_name引用绑定变量SQLselect:num1 fromdual;:NUM1-1num1-1SQL（10）.存储过程初始化绑定变量/声明绑定变量mSQL var m number;/创建一个带输出参数的存储过程test_proSQLcreateorreplaceproceduretest_pro(num outnumber)