欢迎访问 生活随笔！
TAG聚合

生活随笔

当前位置： 首页 >

ORACLE 绑定变量用法总结

发布时间：2024/4/17 36 豆豆
生活随笔 收集整理的这篇文章主要介绍了 ORACLE 绑定变量用法总结 小编觉得挺不错的,现在分享给大家,帮大家做个参考.

http://blog.csdn.net/wh62592855/article/details/4778343

之前对ORACLE中的变量一直没个太清楚的认识，比如说使用：、&、&&、DEIFINE、VARIABLE……等等。今天正好闲下来，上网搜了搜相关的文章，汇总了一下，贴在这里，方便学习。

 

==================================================================================

 

 在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.

一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

 而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

 

1.

sqlplus中如何使用绑定变量，可以通过variable来定义

[c-sharp] view plaincopyprint?
  • SQL> select * from tt where id=1;  
  •   
  • ID NAME  
  • ---------- ----------------------------------------  
  • 1 test  
  •   
  • SQL> select * from tt where id=2;  
  •   
  • ID NAME  
  • ---------- ----------------------------------------  
  • 2 test  
  •   
  • SQL> variable i number;  
  • SQL> exec :i :=1;  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> select *from tt where id=:i;  
  •   
  • ID NAME  
  • ---------- ----------------------------------------  
  • 1 test  
  •   
  • SQL> exec :i :=2;  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> select *from tt where id=:i;  
  •   
  • ID NAME  
  • ---------- ----------------------------------------  
  • 2 test  
  •   
  • SQL> print i;  
  •   
  • I  
  • ----------  
  • 2  
  •   
  • SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t  
  • t where id=%';  
  •   
  • SQL_TEXT PARSE_CALLS  
  • ------------------------------------------------------------ -----------  
  • select * from tt where id=2 1  
  • select * from tt where id=1 1  
  • select * from tt where id=:i 2  
  • SQL>  

    • SQL> select * from tt where id=1;ID NAME---------- ----------------------------------------1 testSQL> select * from tt where id=2;ID NAME---------- ----------------------------------------2 testSQL> variable i number;SQL> exec :i :=1;PL/SQL 过程已成功完成。SQL> select *from tt where id=:i;ID NAME---------- ----------------------------------------1 testSQL> exec :i :=2;PL/SQL 过程已成功完成。SQL> select *from tt where id=:i;ID NAME---------- ----------------------------------------2 testSQL> print i;I----------2SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from tt where id=%';SQL_TEXT PARSE_CALLS------------------------------------------------------------ -----------select * from tt where id=2 1select * from tt where id=1 1select * from tt where id=:i 2SQL>
    从上面试验发现绑定变量i的使用使查询id=1和id=2的sqlselect *from tt where id=:i得以重复
    使用，从而避免了hard parse，这里的PARSE_CALLS＝2包括了一次soft parse

    2.

    前两天看到有人在pub上问在sqlplus中通过define和variable定义的变量的区别。其实define定义的我

    理解不是变量而是字符常量，通过define定义之后，在通过&或者&&引用的时候不需要输入了，仅此而已。

    oracle在执行的时候自动用值进行了替换；而variable定义的是绑定变量,上面已经提到。

    [c-sharp] view plaincopyprint?
  • C:>sqlplus xys/manager  
  • SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008  
  • Copyright (c) 1982, 2007, Oracle. All rights reserved.  
  •   
  • 连接到:  
  • Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production  
  • With the Partitioning, OLAP, Data Mining and Real Application Testing options  
  • SQL> define  
  • DEFINE _DATE = "01-4月 -08" (CHAR)  
  • DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)  
  • DEFINE _USER = "XYS" (CHAR)  
  • DEFINE _PRIVILEGE = "" (CHAR)  
  • DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)  
  • DEFINE _EDITOR = "Notepad" (CHAR)  
  • DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.  
  • 6.0 - Production  
  • With the Partitioning, OLAP, Data Mining and Real Application Testing options" (  
  • CHAR)  
  • DEFINE _O_RELEASE = "1101000600" (CHAR)  
  • SQL> select *from tt;  
  • ID NAME  
  • ---------- ----------  
  • 1 a  
  • 2 a  
  • 3 "abc"  
  • SQL> define a  
  • SP2-0135: 符号 a 未定义  
  • SQL> define a=1  
  • SQL> define  
  • DEFINE _DATE = "01-4月 -08" (CHAR)  
  • DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)  
  • DEFINE _USER = "XYS" (CHAR)  
  • DEFINE _PRIVILEGE = "" (CHAR)  
  • DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)  
  • DEFINE _EDITOR = "Notepad" (CHAR)  
  • DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.  
  • 6.0 - Production  
  • With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)  
  • DEFINE _O_RELEASE = "1101000600" (CHAR)  
  • DEFINE A = "1" (CHAR)  
  • --通过上面显示define定义的应该是字符（串）常量。  
  • SQL> select * from tt where id=&a;  
  • 原值 1: select * from tt where id=&a  
  • 新值 1: select * from tt where id=1  
  • ID NAME  
  • ---------- ----------  
  • 1 a  
  • SQL> select * from tt where id=&&a;  
  • 原值 1: select * from tt where id=&&a  
  • 新值 1: select * from tt where id=1  
  • ID NAME  
  • ---------- ----------  
  • 1 a  
  • SQL> define b='a';  
  • SQL> define  
  • DEFINE _DATE = "01-4月 -08" (CHAR)  
  • DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)  
  • DEFINE _USER = "XYS" (CHAR)  
  • DEFINE _PRIVILEGE = "" (CHAR)  
  • DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)  
  • DEFINE _EDITOR = "Notepad" (CHAR)  
  • DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.  
  • 6.0 - Production  
  • With the Partitioning, OLAP, Data Mining and Real Application Testing options" (  
  • CHAR)  
  • DEFINE _O_RELEASE = "1101000600" (CHAR)  
  • DEFINE A = "1" (CHAR)  
  • DEFINE B = "a" (CHAR)  
  •   
  • --如果是字符类型那么在引用时别忘了加上单引号，另外通过define定义之后在引用时不需要输入了。  
  • SQL> select * from tt where name=&&b;  
  • 原值 1: select * from tt where name=&&b  
  • 新值 1: select * from tt where name=a  
  • select * from tt where name=a  
  • *  
  • 第 1 行出现错误:  
  • ORA-00904: "A": 标识符无效  
  •   
  • SQL> select * from tt where name='&&b';  
  • 原值 1: select * from tt where name='&&b'  
  • 新值 1: select * from tt where name='a'  
  • ID NAME  
  • ---------- ----------  
  • 1 a  
  • 2 a  
  • SQL> select * from tt where name='&b';  
  • 原值 1: select * from tt where name='&b'  
  • 新值 1: select * from tt where name='a'  
  • ID NAME  
  • ---------- ----------  
  • 1 a  
  • 2 a  
  • --执行sql时进行了替换  
  • SQL> select sql_text from v$sql where sql_text like 'select * from tt where name  
  • =%';  
  • SQL_TEXT  
  • --------------------------------------------------------------------------------  
  • select * from tt where name=1  
  • select * from tt where name='a'  
  • SQL>  

    • C:>sqlplus xys/managerSQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.连接到:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> defineDEFINE _DATE = "01-4月 -08" (CHAR)DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)DEFINE _USER = "XYS" (CHAR)DEFINE _PRIVILEGE = "" (CHAR)DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)DEFINE _EDITOR = "Notepad" (CHAR)DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)SQL> select *from tt;ID NAME---------- ----------1 a2 a3 "abc"SQL> define aSP2-0135: 符号 a 未定义SQL> define a=1SQL> defineDEFINE _DATE = "01-4月 -08" (CHAR)DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)DEFINE _USER = "XYS" (CHAR)DEFINE _PRIVILEGE = "" (CHAR)DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)DEFINE _EDITOR = "Notepad" (CHAR)DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)DEFINE A = "1" (CHAR)--通过上面显示define定义的应该是字符（串）常量。SQL> select * from tt where id=&a;原值 1: select * from tt where id=&a新值 1: select * from tt where id=1ID NAME---------- ----------1 aSQL> select * from tt where id=&&a;原值 1: select * from tt where id=&&a新值 1: select * from tt where id=1ID NAME---------- ----------1 aSQL> define b='a';SQL> defineDEFINE _DATE = "01-4月 -08" (CHAR)DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)DEFINE _USER = "XYS" (CHAR)DEFINE _PRIVILEGE = "" (CHAR)DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)DEFINE _EDITOR = "Notepad" (CHAR)DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)DEFINE A = "1" (CHAR)DEFINE B = "a" (CHAR)--如果是字符类型那么在引用时别忘了加上单引号，另外通过define定义之后在引用时不需要输入了。SQL> select * from tt where name=&&b;原值 1: select * from tt where name=&&b新值 1: select * from tt where name=aselect * from tt where name=a*第 1 行出现错误:ORA-00904: "A": 标识符无效SQL> select * from tt where name='&&b';原值 1: select * from tt where name='&&b'新值 1: select * from tt where name='a'ID NAME---------- ----------1 a2 aSQL> select * from tt where name='&b';原值 1: select * from tt where name='&b'新值 1: select * from tt where name='a'ID NAME---------- ----------1 a2 a--执行sql时进行了替换SQL> select sql_text from v$sql where sql_text like 'select * from tt where name=%';SQL_TEXT--------------------------------------------------------------------------------select * from tt where name=1select * from tt where name='a'SQL>

    3.

    oracle在解析sql时会把plsql中定义的变量转为为绑定变量

    [c-sharp] view plaincopyprint?
  • SQL> create table tt(id int , name varchar2(10));  
  •   
  • 表已创建。  
  •   
  • SQL> alter session set sql_trace=true;  
  •   
  • 会话已更改。  
  •   
  • SQL> declare  
  • 2 begin  
  • 3 for i in 1..100 loop  
  • 4 insert into tt values(i,'test');  
  • 5 end loop;  
  • 6 commit;  
  • 7 end;  
  • 8 /  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> alter session set sql_trace=false;  
  • --trace file:  
  • =====================  
  • PARSING IN CURSOR #3 len=90 dep=0 uid=31 oct=47 lid=31 tim=7109565004 hv=962259239   
  •   
  • ad='668ec528'  
  • declare  
  • begin  
  • for i in 1..100 loop  
  • insert into tt values(i,'test');  
  • end loop;  
  • commit;  
  • end;  
  • END OF STMT  
  • PARSE #3:c=15625,e=5678,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=7109564996  
  • =====================  
  • PARSING IN CURSOR #5 len=34 dep=1 uid=31 oct=2 lid=31 tim=7109565520 hv=1299226876   
  •   
  • ad='66869934'  
  • INSERT INTO TT VALUES(:B1 ,'test')  
  • END OF STMT  
  • PARSE #5:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7109565513  
  • =====================  

    • SQL> create table tt(id int , name varchar2(10));表已创建。SQL> alter session set sql_trace=true;会话已更改。SQL> declare2 begin3 for i in 1..100 loop4 insert into tt values(i,'test');5 end loop;6 commit;7 end;8 /PL/SQL 过程已成功完成。SQL> alter session set sql_trace=false;--trace file:=====================PARSING IN CURSOR #3 len=90 dep=0 uid=31 oct=47 lid=31 tim=7109565004 hv=962259239 ad='668ec528'declarebeginfor i in 1..100 loopinsert into tt values(i,'test');end loop;commit;end;END OF STMTPARSE #3:c=15625,e=5678,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=7109564996=====================PARSING IN CURSOR #5 len=34 dep=1 uid=31 oct=2 lid=31 tim=7109565520 hv=1299226876 ad='66869934'INSERT INTO TT VALUES(:B1 ,'test')END OF STMTPARSE #5:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7109565513=====================
    另外从hard parse的数据量上其实也可以大致猜测oracle会把plsql中定义的变量转换为绑定变量处理

    [c-sharp] view plaincopyprint?
  • SQL> connect /as sysdba  
  • 已连接。  
  • SQL> shutdown immediate  
  • 数据库已经关闭。  
  • 已经卸载数据库。  
  • ORACLE 例程已经关闭。  
  • SQL> startup  
  • ORACLE 例程已经启动。  
  •   
  • Total System Global Area 167772160 bytes  
  • Fixed Size 1247900 bytes  
  • Variable Size 67110244 bytes  
  • Database Buffers 96468992 bytes  
  • Redo Buffers 2945024 bytes  
  • 数据库装载完毕。  
  • 数据库已经打开。  
  • SQL> connect xys/manager  
  • 已连接。  
  • SQL> drop table tt;  
  •   
  • 表已删除。  
  •   
  • SQL> create table tt(id int , name varchar2(10));  
  •   
  • 表已创建。  
  • SQL> col name format a30  
  • SQL> select a.*,b.name  
  • 2 from v$sesstat a , v$statname b  
  • 3 where a.statistic#=b.statistic#  
  • 4 and a.sid=(select distinct sid from v$mystat)  
  • 5 and b.name like '%parse%';  
  •   
  • SID STATISTIC# VALUE NAME  
  • ---------- ---------- ---------- ------------------------------  
  • 159 328 39 parse time cpu  
  • 159 329 74 parse time elapsed  
  • 159 330 339 parse count (total)  
  • 159 331 165 parse count (hard)  
  • 159 332 0 parse count (failures)  
  •   
  • SQL> declare  
  • 2 begin  
  • 3 for i in 1..100 loop  
  • 4 insert into tt values(i,'test');  
  • 5 end loop;  
  • 6 commit;  
  • 7 end;  
  • 8 /  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> select a.*,b.name  
  • 2 from v$sesstat a , v$statname b  
  • 3 where a.statistic#=b.statistic#  
  • 4 and a.sid=(select distinct sid from v$mystat)  
  • 5 and b.name like '%parse%'  
  • 6 /  
  •   
  • SID STATISTIC# VALUE NAME  
  • ---------- ---------- ---------- ------------------------------  
  • 159 328 39 parse time cpu  
  • 159 329 74 parse time elapsed  
  • 159 330 345 parse count (total)  
  • 159 331 167 parse count (hard)  
  • 159 332 0 parse count (failures)  
  •   
  • SQL>  

    • SQL> connect /as sysdba已连接。SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startupORACLE 例程已经启动。Total System Global Area 167772160 bytesFixed Size 1247900 bytesVariable Size 67110244 bytesDatabase Buffers 96468992 bytesRedo Buffers 2945024 bytes数据库装载完毕。数据库已经打开。SQL> connect xys/manager已连接。SQL> drop table tt;表已删除。SQL> create table tt(id int , name varchar2(10));表已创建。SQL> col name format a30SQL> select a.*,b.name2 from v$sesstat a , v$statname b3 where a.statistic#=b.statistic#4 and a.sid=(select distinct sid from v$mystat)5 and b.name like '%parse%';SID STATISTIC# VALUE NAME---------- ---------- ---------- ------------------------------159 328 39 parse time cpu159 329 74 parse time elapsed159 330 339 parse count (total)159 331 165 parse count (hard)159 332 0 parse count (failures)SQL> declare2 begin3 for i in 1..100 loop4 insert into tt values(i,'test');5 end loop;6 commit;7 end;8 /PL/SQL 过程已成功完成。SQL> select a.*,b.name2 from v$sesstat a , v$statname b3 where a.statistic#=b.statistic#4 and a.sid=(select distinct sid from v$mystat)5 and b.name like '%parse%'6 /SID STATISTIC# VALUE NAME---------- ---------- ---------- ------------------------------159 328 39 parse time cpu159 329 74 parse time elapsed159 330 345 parse count (total)159 331 167 parse count (hard)159 332 0 parse count (failures)SQL>

    这里发现hard parse只增加了2，如果没有使用绑定变量的话，相信hard parse会更多

    4.

    过程中的参数会自动转化为绑定变量

    [c-sharp] view plaincopyprint?
  • SQL> edit  
  • 已写入 file afiedt.buf  
  •   
  • 1 create or replace procedure proc_test(p_id int, p_name varchar2)  
  • 2 is  
  • 3 begin  
  • 4 insert into tt values(p_id , p_name);  
  • 5 commit;  
  • 6* end;  
  • SQL> /  
  •   
  • 过程已创建。  
  •   
  • SQL> alter session set sql_trace=true;  
  •   
  • 会话已更改。  
  •   
  • SQL> exec proc_test(200,'test');  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> alter session set sql_trace=false;  
  •   
  • 会话已更改。  
  • --trace file：  
  • alter session set sql_trace=true  
  • END OF STMT  
  • EXEC #3:c=0,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7393908487  
  • =====================  
  • PARSING IN CURSOR #1 len=35 dep=0 uid=31 oct=47 lid=31 tim=7403000735 hv=526484776   
  •   
  • ad='6687b0b8'  
  • BEGIN proc_test(200,'test'); END;  
  • END OF STMT  
  • PARSE #1:c=0,e=2584,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7403000727  
  • =====================  
  • PARSING IN CURSOR #6 len=33 dep=1 uid=31 oct=2 lid=31 tim=7403001293 hv=2874748229   
  •   
  • ad='668e9cd8'  
  • INSERT INTO TT VALUES(:B2 , :B1 )  
  • END OF STMT  
  • PARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286  
  • =====================  

    • SQL> edit已写入 file afiedt.buf1 create or replace procedure proc_test(p_id int, p_name varchar2)2 is3 begin4 insert into tt values(p_id , p_name);5 commit;6* end;SQL> /过程已创建。SQL> alter session set sql_trace=true;会话已更改。SQL> exec proc_test(200,'test');PL/SQL 过程已成功完成。SQL> alter session set sql_trace=false;会话已更改。--trace file：alter session set sql_trace=trueEND OF STMTEXEC #3:c=0,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7393908487=====================PARSING IN CURSOR #1 len=35 dep=0 uid=31 oct=47 lid=31 tim=7403000735 hv=526484776 ad='6687b0b8'BEGIN proc_test(200,'test'); END;END OF STMTPARSE #1:c=0,e=2584,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7403000727=====================PARSING IN CURSOR #6 len=33 dep=1 uid=31 oct=2 lid=31 tim=7403001293 hv=2874748229 ad='668e9cd8'INSERT INTO TT VALUES(:B2 , :B1 )END OF STMTPARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286=====================

    另外也可以直观的观察：

    [c-sharp] view plaincopyprint?
  • SQL> exec proc_test(200,'test');  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> select sql_text from v$sql where sql_text like '%proc_test%';  
  •   
  • SQL_TEXT  
  • --------------------------------------------------------------------------------  
  • BEGIN proc_test(200,'test'); END;  
  •   
  • SQL>  

    • SQL> exec proc_test(200,'test');PL/SQL 过程已成功完成。SQL> select sql_text from v$sql where sql_text like '%proc_test%';SQL_TEXT--------------------------------------------------------------------------------BEGIN proc_test(200,'test'); END;SQL>

    在sqlplus里执行过程不能观察出来
    下面在plsql developer执行一次过程之后再来看执行的情况

    [c-sharp] view plaincopyprint?
  • SQL> select sql_text from v$sql where sql_text like '%proc_test%';  
  •   
  • SQL_TEXT  
  • --------------------------------------------------------------------------------  
  • begin -- Call the procedure proc_test(p_id =>:p_id, p_name =>:p_name); end;  
  •   
  • SQL>  

    • SQL> select sql_text from v$sql where sql_text like '%proc_test%';SQL_TEXT--------------------------------------------------------------------------------begin -- Call the procedure proc_test(p_id =>:p_id, p_name =>:p_name); end;SQL>

    很显然oracle在执行过程时把参数转化为绑定变量了，其实从plsql developer中执行过程时的语法就能

    看出来：

    [c-sharp] view plaincopyprint?
  • begin  
  • -- Call the procedure  
  • proc_test(p_id => :p_id,  
  • p_name => :p_name);  
  • end;  

    • begin-- Call the procedureproc_test(p_id => :p_id,p_name => :p_name);end;
    在输入参数列表框上面的执行语法就是这样的。

    5.

    在动态sql中使用绑定变量，动态sql中使用绑定变量非常明显也容易理解，下面给出2个简单的例子

    [c-sharp] view plaincopyprint?
  • SQL> set serveroutput on  
  • SQL> declare  
  •    2   v_string varchar2(100);  
  •    3   v_id tt.id%type ;  
  •    4   v_name tt.name%type ;  
  •    5   begin  
  •    6   v_string:='select * from tt where id=:v_id';  
  •    7   execute immediate v_string into v_id , v_name using &a;  
  •    8   dbms_output.put_line(v_id||' '||v_name) ;  
  •    9   end;  
  • 10   /  
  • 输入 a 的值:   1  
  • 原值 7: execute immediate v_string into v_id , v_name using &a;  
  • 新值 7: execute immediate v_string into v_id , v_name using 1;  
  • 1 test  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> declare  
  •    2   v_string varchar2(100);  
  •    3   v_id tt.id%type;  
  •    4   v_name tt.name%type ;  
  •    5   begin  
  •    6   v_string:='insert into tt values(:id,:name)';  
  •    7   execute immediate v_string using &id,&name ;  
  •    8   end;  
  •    9   /  
  • 输入 id 的值:   1000  
  • 输入 name 的值:   'test'  
  • 原值 7: execute immediate v_string using &id,&name ;  
  • 新值 7: execute immediate v_string using 1000,'test' ;  
  •   
  • PL/SQL 过程已成功完成。  
  •   
  • SQL> select * from tt where id=1000;  
  •   
  •        ID NAME  
  • ---------- ----------  
  •    1000 test  
  •   
  • SQL>  

    • SQL> set serveroutput onSQL> declare 2 v_string varchar2(100); 3 v_id tt.id%type ; 4 v_name tt.name%type ; 5 begin 6 v_string:='select * from tt where id=:v_id'; 7 execute immediate v_string into v_id , v_name using &a; 8 dbms_output.put_line(v_id||' '||v_name) ; 9 end;10 /输入 a 的值: 1原值 7: execute immediate v_string into v_id , v_name using &a;新值 7: execute immediate v_string into v_id , v_name using 1;1 testPL/SQL 过程已成功完成。SQL> declare 2 v_string varchar2(100); 3 v_id tt.id%type; 4 v_name tt.name%type ; 5 begin 6 v_string:='insert into tt values(:id,:name)'; 7 execute immediate v_string using &id,&name ; 8 end; 9 /输入 id 的值: 1000输入 name 的值: 'test'原值 7: execute immediate v_string using &id,&name ;新值 7: execute immediate v_string using 1000,'test' ;PL/SQL 过程已成功完成。SQL> select * from tt where id=1000; ID NAME---------- ---------- 1000 testSQL> 

    =============================下面加上一些其他变量的使用方法========================= 

    eg001（&替换变量）

    [c-sharp] view plaincopyprint?
  • SQL> select xh,xm from system.xs where zym='&zym';  
  • 输入 zym 的值:  计算机  
  • 原值    1: select xh,xm from system.xs where zym='&zym'  
  • 新值    1: select xh,xm from system.xs where zym='计算机'  
  •   
  • XH     XM  
  • ------ --------  
  • 061101 王林  
  • 061102 程明  
  • 061103 王燕  
  • 061104 韦严平  
  • 061106 李方方  
  • 061107 李明  
  • 061108 林一帆  
  • 061109 张强民  
  • 061110 张蔚  
  • 061111 赵琳  
  • 061113 严红  
  •   
  • 已选择11行。  
  •   
  • SQL> edit  
  • 已写入 file afiedt.buf  
  •   
  •   1  select xh 学号,xm 姓名,avg(cj) as 平均成绩  
  •   2* from system.xs_xkb group by xh,xm  
  • SQL> /  
  •   
  • 学号   姓名       平均成绩  
  • ------ -------- ----------  
  • 061103 王燕             71  
  • 061210 李红庆           76  
  • 061110 张蔚     91.3333333  
  • 061220 吴薇华           82  
  • 061104 韦严平   79.6666667  
  • 061101 王林             78  
  • 061204 马林林           91  
  • 061106 李方方           72  
  • 061218 孙研             70  
  • 061102 程明             78  
  • 061241 罗林琳           90  
  •   
  • 学号   姓名       平均成绩  
  • ------ -------- ----------  
  • 061111 赵琳           80.5  
  • 061109 张强民         76.5  
  • 061216 孙祥欣           81  
  • 061221 刘燕敏           79  
  •   
  • 已选择15行。  
  •   
  • SQL> select * from system.xs_xkb where cj>=&cj; /*替换变量可以使用WHERE子句；ORDER BY子句；列表达式；表名；整个SELECT语句*/  
  • 输入 cj 的值:  90  
  • 原值    1: select * from system.xs_xkb where cj>=&cj  
  • 新值    1: select * from system.xs_xkb where cj>=90  
  •   
  • SQL> select xs.xh,&name,kcm,&column  
  •   2  from system.xs,&kc,system.xs_kc  
  •   3  where xs.xh=xs_kc.xh and &condition  
  •   4  and kcm=&kcm  
  •   5  order by & column;  
  • 输入 name 的值:  xm  
  • 输入 column 的值:  cj  
  • 原值    1: select xs.xh,&name,kcm,&column  
  • 新值    1: select xs.xh,xm,kcm,cj  
  • 输入 kc 的值:  system.kc  
  • 原值    2: from system.xs,&kc,system.xs_kc  
  • 新值    2: from system.xs,system.kc,system.xs_kc  
  • 输入 condition 的值:  kc.kch=xs_kc.kch  
  • 原值    3: where xs.xh=xs_kc.xh and &condition  
  • 新值    3: where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch  
  • 输入 kcm 的值:  '离散数学'  
  • 原值    4: and kcm=&kcm  
  • 新值    4: and kcm='离散数学'  
  • 输入 column 的值:  cj  
  • 原值    5: order by & column  
  • 新值    5: order by cj  
  •   
  • XH     XM       KCM                      CJ  
  • ------ -------- ---------------- ----------  
  • 061104 韦严平   离散数学                 65  
  • 061109 张强民   离散数学                 70  
  • 061101 王林     离散数学                 76  
  • 061102 程明     离散数学                 78  
  • 061106 李方方   离散数学                 80  
  • 061103 王燕     离散数学                 81  
  • 061110 张蔚     离散数学                 89  

    • SQL> select xh,xm from system.xs where zym='&zym';输入 zym 的值: 计算机原值 1: select xh,xm from system.xs where zym='&zym'新值 1: select xh,xm from system.xs where zym='计算机'XH XM------ --------061101 王林061102 程明061103 王燕061104 韦严平061106 李方方061107 李明061108 林一帆061109 张强民061110 张蔚061111 赵琳061113 严红已选择11行。SQL> edit已写入 file afiedt.buf 1 select xh 学号,xm 姓名,avg(cj) as 平均成绩 2* from system.xs_xkb group by xh,xmSQL> /学号 姓名 平均成绩------ -------- ----------061103 王燕 71061210 李红庆 76061110 张蔚 91.3333333061220 吴薇华 82061104 韦严平 79.6666667061101 王林 78061204 马林林 91061106 李方方 72061218 孙研 70061102 程明 78061241 罗林琳 90学号 姓名 平均成绩------ -------- ----------061111 赵琳 80.5061109 张强民 76.5061216 孙祥欣 81061221 刘燕敏 79已选择15行。SQL> select * from system.xs_xkb where cj>=&cj; /*替换变量可以使用WHERE子句；ORDER BY子句；列表达式；表名；整个SELECT语句*/输入 cj 的值: 90原值 1: select * from system.xs_xkb where cj>=&cj新值 1: select * from system.xs_xkb where cj>=90SQL> select xs.xh,&name,kcm,&column 2 from system.xs,&kc,system.xs_kc 3 where xs.xh=xs_kc.xh and &condition 4 and kcm=&kcm 5 order by & column;输入 name 的值: xm输入 column 的值: cj原值 1: select xs.xh,&name,kcm,&column新值 1: select xs.xh,xm,kcm,cj输入 kc 的值: system.kc原值 2: from system.xs,&kc,system.xs_kc新值 2: from system.xs,system.kc,system.xs_kc输入 condition 的值: kc.kch=xs_kc.kch原值 3: where xs.xh=xs_kc.xh and &condition新值 3: where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch输入 kcm 的值: '离散数学'原值 4: and kcm=&kcm新值 4: and kcm='离散数学'输入 column 的值: cj原值 5: order by & column新值 5: order by cjXH XM KCM CJ------ -------- ---------------- ----------061104 韦严平 离散数学 65061109 张强民 离散数学 70061101 王林 离散数学 76061102 程明 离散数学 78061106 李方方 离散数学 80061103 王燕 离散数学 81061110 张蔚 离散数学 89  

    eg002（&&替换变量）

    [c-sharp] view plaincopyprint?
  • --&&替换变量系统一直用同一个值处理，清除用undefine 变量名清除  
  • SQL> edit  
  • 已写入 file afiedt.buf  
  •   
  •   1  select xs.xh,&name,kcm,&&column   /*清除替换变量（undefine column）*/  
  •   2  from system.xs,&kc,system.xs_kc  
  •   3  where xs.xh=xs_kc.xh and &condition  
  •   4  and kcm=&kcm  
  •   5* order by &column  
  • SQL> /  
  • 输入 name 的值:  xm  
  • 输入 column 的值:  cj  
  • 原值    1: select xs.xh,&name,kcm,&&column  
  • 新值    1: select xs.xh,xm,kcm,cj  
  • 输入 kc 的值:  system.kc  
  • 原值    2: from system.xs,&kc,system.xs_kc  
  • 新值    2: from system.xs,system.kc,system.xs_kc  
  • 输入 condition 的值:  kc.kch=xs_kc.kch  
  • 原值    3: where xs.xh=xs_kc.xh and &condition  
  • 新值    3: where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch  
  • 输入 kcm 的值:  '离散数学'  
  • 原值    4: and kcm=&kcm  
  • 新值    4: and kcm='离散数学'  
  • 原值    5: order by &column             /*使用&&替换变量的好处,相同变量只输第一次就OK*/  
  • 新值    5: order by cj  
  •   
  • XH     XM       KCM                      CJ  
  • ------ -------- ---------------- ----------  
  • 061104 韦严平   离散数学                 65  
  • 061109 张强民   离散数学                 70  
  • 061101 王林     离散数学                 76  
  • 061102 程明     离散数学                 78  
  • 061106 李方方   离散数学                 80  
  • 061103 王燕     离散数学                 81  
  • 061110 张蔚     离散数学                 89  
  •   
  • 已选择7行。  

    • --&&替换变量系统一直用同一个值处理，清除用undefine 变量名清除SQL> edit已写入 file afiedt.buf 1 select xs.xh,&name,kcm,&&column /*清除替换变量（undefine column）*/ 2 from system.xs,&kc,system.xs_kc 3 where xs.xh=xs_kc.xh and &condition 4 and kcm=&kcm 5* order by &columnSQL> /输入 name 的值: xm输入 column 的值: cj原值 1: select xs.xh,&name,kcm,&&column新值 1: select xs.xh,xm,kcm,cj输入 kc 的值: system.kc原值 2: from system.xs,&kc,system.xs_kc新值 2: from system.xs,system.kc,system.xs_kc输入 condition 的值: kc.kch=xs_kc.kch原值 3: where xs.xh=xs_kc.xh and &condition新值 3: where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch输入 kcm 的值: '离散数学'原值 4: and kcm=&kcm新值 4: and kcm='离散数学'原值 5: order by &column /*使用&&替换变量的好处,相同变量只输第一次就OK*/新值 5: order by cjXH XM KCM CJ------ -------- ---------------- ----------061104 韦严平 离散数学 65061109 张强民 离散数学 70061101 王林 离散数学 76061102 程明 离散数学 78061106 李方方 离散数学 80061103 王燕 离散数学 81061110 张蔚 离散数学 89已选择7行。
    eg003

    DEFINE[variable[=value]]
    UNDEFINE    清除定义的变量

    [c-sharp] view plaincopyprint?
  • SQL> define specialty=通信工程  
  • SQL> define specialty  
  • DEFINE SPECIALTY       = "通信工程" (CHAR)  
  • SQL> select xh,xm,xb,cssj,zxf from system.xs  
  •   2  where zym='&specialty';  
  •   
  • XH     XM       XB CSSJ                  ZXF  
  • ------ -------- -- -------------- ----------  
  • 061202 王林     男 29-10月-85             40  
  • 061210 李红庆   女 01-5月 -85             44  
  • 061201 王敏     男 10-6月 -84             42  
  • 061203 王玉民   男 26-3月 -86             42  
  • 061204 马林林   女 10-2月 -84             42  
  • 061206 李计     女 20-9月 -85             42  
  • 061216 孙祥欣   女 09-3月 -84             42  
  • 061218 孙研     男 09-10月-86             42  
  • 061220 吴薇华   女 18-3月 -86             42  
  • 061221 刘燕敏   女 12-11月-85             42  
  • 061241 罗林琳   女 30-1月 -86             50  
  •   
  • 已选择11行。  

    • SQL> define specialty=通信工程SQL> define specialtyDEFINE SPECIALTY = "通信工程" (CHAR)SQL> select xh,xm,xb,cssj,zxf from system.xs 2 where zym='&specialty';XH XM XB CSSJ ZXF------ -------- -- -------------- ----------061202 王林 男 29-10月-85 40061210 李红庆 女 01-5月 -85 44061201 王敏 男 10-6月 -84 42061203 王玉民 男 26-3月 -86 42061204 马林林 女 10-2月 -84 42061206 李计 女 20-9月 -85 42061216 孙祥欣 女 09-3月 -84 42061218 孙研 男 09-10月-86 42061220 吴薇华 女 18-3月 -86 42061221 刘燕敏 女 12-11月-85 42061241 罗林琳 女 30-1月 -86 50已选择11行。

    eg004
    ACCEPT variable[datatype[NUMBER|CHAR|DATE]][FORMAT format][PROMPT text][HIDE]    /*variable：指定接收值的变量。该名称的变量不存在，那么SQL重建该变量；datatype:变量数据类型，默认为CHAR*/

    [c-sharp] view plaincopyprint?
  • SQL> accept num prompt'请输入课程号:'  
  • 请输入课程号:101  
  • SQL> set verify on  
  • SQL>   
  •   1  select xh,kcm,cj from system.xs_kc,system.kc  
  •   2  where xs_kc.kch=kc.kch and kc.kch='&num'  
  •   3* order by cj  
  • SQL> /  
  • 原值    2: where xs_kc.kch=kc.kch and kc.kch='&num'  
  • 新值    2: where xs_kc.kch=kc.kch and kc.kch='101'  
  •   
  • XH     KCM                      CJ  
  • ------ ---------------- ----------  
  • 061103 计算机基础               62  
  • 061106 计算机基础               65  
  • 061218 计算机基础               70  
  • 061210 计算机基础               76  
  • 061221 计算机基础               79  
  • 061101 计算机基础               80  
  • 061216 计算机基础               81  
  • 061220 计算机基础               82  
  • 061241 计算机基础               90  
  • 061104 计算机基础               90  
  • 061111 计算机基础               91  
  •   
  • XH     KCM                      CJ  
  • ------ ---------------- ----------  
  • 061204 计算机基础               91  
  • 061110 计算机基础               95  
  •   
  • 已选择13行。  
  •   
  • SQL>  

    •  

    总结

    以上是生活随笔为你收集整理的ORACLE 绑定变量用法总结的全部内容，希望文章能够帮你解决所遇到的问题。

    如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。