Oracle变量定义的三种方式(define,variable,declare)学习笔记
来源:互联网 发布:c语言在线编译运行 编辑:程序博客网 时间:2024/06/07 07:17
1、define(即host变量)
Host变量主要作用是起到一个替代变量的作用,是主机环境可以和oracle进行交互的一个媒介。 通过define定义host变量的时候必须指定变量名和变量的值,如果变量名已经存在于host变量中,则自动覆盖,这个值不可以指定类型,一律按char存储。 DEFINE 变量只在当前session环境中有效。
(1).语法:
define variable_name = value
(2).声明和初始化DEFINE变量
//声明define变量的时候必须同时初始化赋值变量SQL> define num=1;
另外可以使用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 = "TOPPROD" (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)
了解对于host变量启用和关闭的命令是:set define on和set define off。
(5).关闭DEFINE变量定义功能
//关闭defineSQL> set define off;SQL> define num=1;SQL> select * from occ_file where rownum=#select * from occ_file where rownum=&numORA-01008: 并非所有变量都已绑定
(6).开启DEFINE变量定义功能
//打开defineSQL> set define on;SQL> define num=1;SQL> select * from occ_file where rownum=#OCC01 OCC02---------- ---------------020040 松荣五金-SRWJ
(7).引用DEFINE变量
这在sqlplus和plsql developer的command window中都可以使用,在sql或plsql中引用host变量,使用&符号,相当于一个简单的替换动作,比如
//错误案例SQL> define x=occ_file;SQL> select * from '&x' where rownum=1;select * from 'occ_file' where rownum=1ORA-00903: 表名无效SQL>
报错是因为x被替换为occ_file,语句变为select * from ’occ_file’,’occ_file’表是不存在的,多了个单引号。
//正确案例SQL> define x=occ_file;SQL> select * from &x where rownum=1;OCC01 OCC02---------- ---------------020040 松荣五金-SRWJSQL>
(8).引用临时DEFINE变量
可以不预先声明初始化define变量,直接在sql或plsql中在字符串前面加&符号,会提示你输入变量的值,然后替换。这种情况下是临时的变量,define命令查不到。
//不预先声明和初始化define变量直接使用&引用SQL> select * from &y where rownum=1;//弹框提示输入变量y的值,输入occ_file,后打印出一下信息OCC01 OCC02---------- ---------------020040 松荣五金-SRWJ SQL>
经常使用host变量的场合是在写脚本的时候,脚本的有些地方经常变化,其他地方固定,那么可以使用&引用。
2、Variable(即邦定变量)
绑定变量主要是在sql中使用,达到sql解析一次,执行多次,从而提高效率的目的。绑定变量和host变量一样,也有定义的,也有临时性的。(临时性的在动态sql或动态plsql中通过占位符和using使用),其他的如sql会自动使用绑定变量的情况,此处不专门讨论。定义的绑定变量也是在当前session中有效。 绑定变量以下特点:
绑定变量在sql和plsql中直接引用必须加前缀 :。如要引用绑定变量a,则是 :a;
在真正运行的PLSQL程序中,比如自动运行,有自己的一套机制;
初始化和应用绑定变量,初始化定义的绑定变量,可以使用过程和函数,其中过程可以内部给绑定变量赋值、也可以参数是绑定变量,参数模式为out返回。使用函数和过程差不多,还可以使用call
函数来赋值。
sqlplus中可以使用大于等于3个字符表示一个命令,这里我们用var,var命令是声明一个绑定变量,只能给予名称和类型,定义的时候不能赋值,赋值可以在plsql中或者采用函数赋值(而host变量定义的时候必须赋值)。
(1).语法:
var var_name type
(2).声明绑定变量
//使用var声明两number类型的变量num1、num2SQL> var 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初始化绑定变量
//给绑定变量赋值SQL> begin :num1:=1; :num2:=2; end; /PL/SQL procedure successfully completednum1---------1num2---------2SQL>
(6).使用EXECUTE初始化绑定变量
//使用execute初始化,初始化多个用分号隔开 SQL> exec :num1:=1;:num2:=2PL/SQL procedure successfully 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引用绑定变量SQL> select :num1 from dual; :NUM1---------- 1num1---------1SQL>
(10).存储过程初始化绑定变量
//声明绑定变量mSQL> var m number;//创建一个带输出参数的存储过程test_proSQL> create or replace procedure test_pro(num out number) as begin num:=10; end; /Procedure created//使用存储过程返回变量(引用绑定变量记得带上:)SQL> exec test_pro(num=>:m);PL/SQL procedure successfully completedm---------10//输出绑定变量m的值SQL> print mm---------10
(11).游标初始化绑定变量
绑定变量是REFCURSOR类型。一个参数使用refcursor,通过绑定变量类型定义为REFCURSOR,然后传入过程,打印绑定变量(效果和查询语句一样)。
//声明游标类型的绑定变量//注意:在plsql dev的command window无法使用以下声明,需要再sqlplus中才有效SQL> var curinfo refcursor//创建带输出参数,参数类型为sys_refcursor的存储过程SQL> create or replace procedure cur_pro(cur_msg out sys_refcursor) as begin open cur_msg for select occ01,occ02 from occ_file where rownum<3; end; /Procedure created//exec执行存储过程SQL> exec cur_pro(cur_msg=>:cur_info);PL/SQL 过程已成功完成。//输出绑定变量cur_info的值SQL> print cur_infoOCC01 OCC02---------------------------020040 松荣五金-SRWJ---------------------------020041 路得记-LDJ
由上面可以知道,print可以直接把refcursor的结果打印出来,不需要迭代查找。
(12).函数初始化绑定比那里
//创建函数SQL> create or replace function test_fun(a in varchar2,b in varchar2) return varchar2 as c varchar2(255); begin c:=a||b; return c; end; /Function created//声明绑定变量fun_infoSQL> var fun_info varchar2(255);//使用call调用函数将返回值赋值给绑定变量SQL> call test_fun('a','b') into :fun_info;Method calledfun_info---------ab//显示绑定变量的值SQL> print fun_infofun_info---------ab
当然也可以将函数的参数定义为out模式来初始化,我们这里使用call命令调用函数把结果传给绑定变量,
语法:
call function(参数列表) into :绑定变量
pl/sql中的参数和定义的变量(包括全局变量和临时变量)都会内部转为绑定变量,所以尽量在pl/sql中尽量使用静态sql,而不要使用动态sql,如果使用动态sql,要尽量加上绑定变量。
(13).绑定变量的基本作用
绑定变量主要是sql的执行过程中,在解析sql之后会进行共享池(SGA)的检查,看优化器有没有分析优化过这个sql,环境必须完全一致才可以(包括大小写的一致,session情况一致等)。那么可以达到一次分析,多次执行的目的,这就是软解析,否则要经过解析,优化,行资源生成等一系列sql执行的过程,因为sql优化需要耗费很多资源,如果硬解析,sql性能会下降。
如果查询一跑几个小时,根本没必要做绑定变量,因为解析的消耗微乎其微,而且绑定变量对优化器判断执行路径也有负面影响。
看SGA中的sql是否是软解析被调用多次,可以查看v
select sql_text,executions from v$sql where sql_text like '%trademark%'; SQL_TEXT EXECUTIONS<被执行的次数> select * from trademark where id=:tid 6
3、declare
查看Oracle PL/SQL语句基础学习笔记(上)-3、声明部分
- Oracle变量定义的三种方式(define,variable,declare)学习笔记
- oracle中的define,declare,variable的区别
- oracle中的define,declare,variable的区别
- define,variable和declare的区别
- PL/SQL中,declare定义变量和variable定义变量的区别?
- shell脚本的作用,shell脚本变量的定义和三种赋值方式【shell脚本的学习笔记一】
- angular学习笔记--服务的三种定义方式
- sqlplus中define定义的常量和variable定义的变量已经column给变量赋值的区别
- RequireJS的几种define定义方式
- RequireJS的几种define定义方式
- SQL定义变量DECLARE
- 《C++ primer》学习笔记之四:用define指令定义的常量的值做变量名
- How to define global variable MFC 中全局变量的定义
- php学习笔记(三十五)smarty模板中使用变量的三种方式
- oracle &,&&,&1...&9三种变量的常处理方式
- C++学习笔记之define的多行定义
- JavaScript函数笔记-函数定义的三种方式
- TensorFlow学习笔记(六)Variable变量
- .NET 委托与事件
- MySQL分页查询时获得总条目数
- 第五次作业:开源项目“网页正文提取”的理解
- 进程退出后,malloc分配的资源会被系统回收
- 如何优化软件发布延迟问题?
- Oracle变量定义的三种方式(define,variable,declare)学习笔记
- POJ 2823 Sliding Window(单调队列)
- MongoDB架构——sharding分片
- Linux中more的用法详解
- 一张图看懂surface框架
- ajax核心技术---XMLHttpRequset对象的使用
- EditText不自动弹出软键盘
- OpenCV之十一 形态学图像处理(二):开运算、闭运算、形态学梯度、顶帽、黑帽合辑
- uva573 The Snail