Oracle数据库的绑定变量特性及应用
oracle中constraint用法
oracle中constraint用法Oracle中Constraint用法在Oracle数据库中，Constraint（约束）是用于限制表中数据的完整性的规则。
它可以帮助我们保持数据的准确性和一致性。
下面是一些常用的Constraint用法。
1. NOT NULL Constraint（非空约束）NOT NULL约束用于确保某一列的值不为空。
当试图插入或更新该列中的值时，如果为空，则会触发一个错误。
示例：CREATE TABLE students (id NUMBER PRIMARY KEY,name VARCHAR2(50) NOT NULL,age NUMBER);2. UNIQUE Constraint（唯一约束）UNIQUE约束用于确保某一列中的值是唯一的，即不重复。
这可以用于避免重复数据的插入。
示例：CREATE TABLE employees (id NUMBER PRIMARY KEY,email VARCHAR2(50) UNIQUE,phone VARCHAR2(20),...);3. PRIMARY KEY Constraint（主键约束）PRIMARY KEY约束用于定义一列或一组列作为表的主键，主键的值在表中必须是唯一的，并且不能为空。
示例：CREATE TABLE customers (id NUMBER PRIMARY KEY,name VARCHAR2(50),email VARCHAR2(50),...);4. FOREIGN KEY Constraint（外键约束）FOREIGN KEY约束用于定义与其他表之间的关联关系，确保数据的一致性。
一个表的外键关联到另一个表的主键。
示例：CREATE TABLE orders (id NUMBER PRIMARY KEY,customer_id NUMBER,...CONSTRAINT fk_orders_customers FOREIGN KEY (customer_i d)REFERENCES customers(id));5. CHECK Constraint（检查约束）CHECK约束用于限制一列中的值必须满足特定的条件。
DBMS_SQL使用
DBMS_SQL使用DBMS_SQL是Oracle数据库中一个用于动态SQL的包，用于在运行时构造和执行SQL语句。
它提供了一种灵活的方式来处理动态SQL，允许开发人员在程序中动态生成SQL语句，并在运行时执行这些语句。
使用DBMS_SQL的主要步骤如下：1.打开游标：首先，需要调用DBMS_SQL.OPEN_CURSOR方法来打开一个游标。
游标是用来执行SQL语句和返回结果的对象。
2.解析SQL语句：接下来，使用DBMS_SQL.PARSE方法将SQL语句解析成可执行的格式。
这个方法需要接收游标句柄、SQL语句和语句类型作为参数。
3.绑定变量：如果SQL语句中包含绑定变量，可以使用DBMS_SQL.BIND_VARIABLE方法将变量绑定到游标上。
绑定变量可以防止SQL注入攻击，并且提高了SQL语句的重用性和性能。
4.执行SQL语句：使用DBMS_SQL.EXECUTE方法来执行SQL语句。
这个方法只返回一个结果集，不需要接收任何参数。
5.获取结果：如果SQL语句返回结果集，可以使用DBMS_SQL.FETCH_ROWS方法来获取结果集的行。
可以使用DBMS_SQL.COLUMN_VALUE方法来获取行中的列值。
6.关闭游标：最后，使用DBMS_SQL.CLOSE_CURSOR方法关闭游标。
除了上述基本步骤外，DBMS_SQL还提供了其他一些方法和函数来处理动态SQL。
1.DBMS_SQL.VARIABLE_VALUE：用于获取和设置绑定变量的值。
2.DBMS_ST_ERROR_POSITION：用于获取最后一个错误的位置。
3.DBMS_ST_ERROR_TEXT：用于获取最后一个错误的文本。
4.DBMS_SQL.EXECUTE_AND_FETCH：用于一次性执行SQL语句并获取结果。
5.DBMS_SQL.GET_NEXT_RESULT：用于获取多个结果集。
使用DBMS_SQL的一个常见应用是在存储过程或函数中动态构造和执行SQL语句。
oracle绑定变量及举例
1.什么是绑定变量，及举例什么是绑定变量，问什么要进行绑定变量？Sql语句的执行要经过解析、执行、提取等几个阶段，其中解析最消耗资源，解析的过程中要进行语法、语义和权限的检查，如果这些检查都通过了，则进行执行，执行完成之后将sql语句的执行计划存储在共享池中，如果下一次有相同的sql语句要执行，则不需要解析，直接按照已经存在的执行计划进行执行，就可以节省资源当多个sql语句执行的时候大多数情况下是条件相同，只是条件里面的值不同。
例如：A 用户：select * from t where ID=1B 用户：select * from t where ID=2绑定变量就是将条件谓词中不同的值保存在一个中间变量中，Oracle对用户每次发起的sql语句做hash运算时，都产生相同的hash 值，使用相同的执行计划，作为一个sql语句来执行。
Select * from t where ID=:X下面是绑定变量和非绑定变量的性能比较1)绑定变量执行alter session set sql_trace=true;beginfor x in 1..10000 loopexecute immediate 'select * from t where object_name=:x' using x;end loop;end;alter session set sql_trace=false;trace文件中的执行计划和统计信息SQL ID: gdp68zfsdqrbcPlan Hash: 1601196873select *fromt where object_name=:xcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0 Execute 10000 0.15 0.17 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ----------total 10001 0.15 0.17 0 0 0 0Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 5 (SYSTEM) (recursive depth: 1)Rows Row Source Operation------- ---------------------------------------------------0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us cost=301 size=196 card=2)Rows Execution Plan------- ---------------------------------------------------0 SELECT STATEMENT MODE: ALL_ROWS0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.00 0 0 0 0 Execute 3 1.28 1.25 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ----------total 5 1.28 1.26 0 0 0 1Misses in library cache during parse: 2Misses in library cache during execute: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0 Execute 10000 0.15 0.17 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ----------total 10001 0.15 0.17 0 0 0 0 Misses in library cache during parse: 010003 user SQL statements in session.0 internal SQL statements in session.10003 SQL statements in session.1 statement EXPLAINed in this session.******************************************************************************** Trace file: D:\app\Administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6912.trcTrace file compatibility: 11.1.0.7Sort options: default1 session in tracefile.10003 user SQL statements in trace file.0 internal SQL statements in trace file.10003 SQL statements in trace file.4 unique SQL statements in trace file.1 SQL statements EXPLAINed using schema:SYSTEM.prof$plan_tableDefault table was used.Table was created.Table was dropped.60054 lines in trace file.99 elapsed seconds in trace file.上面是在有绑定变量的情况下的信息统计●执行时间=1.26+0.17●Cpu时间=1.28+0.15●分析次数=3●执行次数=100032)没有绑定变量执行在另外一个会话中执行alter session set sql_trace=true;beginfor x in 1..10000 loopexecute immediate 'select * from t where object_id='||x;end loop;end;alter session set sql_trace=false;trace文件中的执行计划和统计信息,在不绑定变量的情况下，每执行一次sql都有统计信息和执行计划，所以下面的信息是trace文件中的汇总信息********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.01 0.00 0 0 0 0 Execute 3 1.87 2.04 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ----------total 5 1.89 2.04 0 0 0 1Misses in library cache during parse: 2Misses in library cache during execute: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 10000 6.09 6.01 0 0 0 0 Execute 10000 0.28 0.19 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ----------total 20000 6.37 6.21 0 0 0 0 Misses in library cache during parse: 1000010003 user SQL statements in session.0 internal SQL statements in session.10003 SQL statements in session.10000 statements EXPLAINed in this session.******************************************************************************** Trace file: D:\app\Administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6360.trcTrace file compatibility: 11.1.0.7Sort options: default1 session in tracefile.10003 user SQL statements in trace file.0 internal SQL statements in trace file.10003 SQL statements in trace file.10003 unique SQL statements in trace file.10000 SQL statements EXPLAINed using schema:SYSTEM.prof$plan_tableDefault table was used.Table was created.Table was dropped.80063 lines in trace file.96 elapsed seconds in trace file.上面是在有绑定变量的情况下的信息统计●执行时间=2.04+6.21●Cpu时间=1.89+6.37●分析次数=10002执行次数=10003综上：绑定变量的sql消耗的资源要远远少于不绑定变量的sql语句，如果未绑定变量的sql数量越多,所消耗的资源就越多。
Oracle绑定变量窥视功能深度分析
个变量来代替原来 出现在 Ｓ Ｑ Ｌ语句里的字面值。 比如 ，Ｓ ｅ —
ｌ ｅ ｃ ｔ ｃ ｏ ｌ １ Ｆ ｒ ｏ ｍ ｔ Ｗｈ ｅ ｒ ｅ ｃ ｏ ｌ ２＝７ ．Ｏ ｒ ａ ｃ ｌ ｅ在 ｓ ｈ ａ ｒ ｅ ｄ ｐ ｏ ｏ ｌ 里 进 行 硬
Ｓ ｈ ａ ｒ ｉ ｎ ｇ ） 。该特性是一个非常复 杂的技术 ，用来平衡游标共享 和Ｓ Ｑ Ｉ 优化这两个矛 盾的 目标 。１ ｌ ｇ里不会盲 目地共享游标 ， 而是会 去查看每个 绑定变量 ，并 为不 同的绑定变 量来产 生不 同的执行计划 。而 Ｏ ｒ ａ ｃ ｌ ｅ这么做 的前提 是 ，使用 多个执 行计 划 的所带来 的收益 ．要 比产生多个执 行计划所引起 的 Ｃ Ｐ Ｕ开
电脑 编 程 技 巧 与 维 护
Ｏ ｒ ａ ｃ ｌ ｅ 绑 定变量窥视 功能深度分析
侯婉 颖
（ 中国惠普有 限公司 ，北 京 １ ０ ０ ０ ２ ５ ） 摘 要 ：为 了更好地解决 Ｓ Ｑ Ｌ执行效 率问题 ，建议在编写 Ｓ Ｑ Ｌ时或对于使 用频繁 的 Ｓ Ｑ Ｌ进行 索引 Ｈ ｉ ｎ ｔ 提 示，以利
ＨＯＵ Ｗ ａ ｎ － ｙ ｉ ｎ ｇ
（ Ｃ ｈ ｉ ｎ ａ Ｈ ｅ ｗｌ ｅ ｔ ｔ — Ｐ ａ ｃ ｋ ａ ｒ ｄ Ｃ ｏ ． ， Ｂ ｅ ｉ ｊ ｉ ｎ ｇ １ ０ ０ ０ ２ ５， Ｃ ｈ ｉ ｎ ａ ）
Ａｂ ｓ ｔ ｒ ａ ｃ ｔ ：Ｉ ｎ ｏ ｒ ｄ ｅ ｒ ｔ ｏ ｂ ｅ ｔ ｔ ｅ ｒ ａ ｄ ｄ ｒ ｅ ｓ ｓ Ｓ Ｑ Ｌ ｐ ｅ ｆ ｒ ｏ ｒ ｍａ ｎ ｃ ｅ ｉ ｓ ｓ ｕ ｅ ｓ ， Ｉ ｓ ｔ ｒ ｏ ｎ ｇ ｌ ｙ ｒ ｅ ｃ ｏ ｍ ｍ ｅ ｎ ｄ ｗ ｉ ｒ ｔ ｉ ｎ ｇ ａ Ｓ Ｑ Ｌ ｏ ｒ ｉ ｆ ｉ ｎ ｄ ｅ ｘ ｅ ｓ ｆ ｏ ｒ ｈ ｅ ａ ｖ ｉ ｌ ｙ ｕ ｓ ｅ ｄ Ｓ Ｑ Ｌ Ｈ ｉ ｎ ｔ ｔ ｉ ｐ ｓ ． Ｔ ｏ ｆ ａ ｃ ｉ ｌ ｉ ｔ ａ ｔ ｅ ｐ ｒ ｏ ｐ ｅ ｒ ｉ ｍ ｐ ｌ ｅ ｍ ｅ ｎ ｔ ａ ｔ ｉ ｏ ｎ Ｏ ｒ ａ ｃ ｌ ｅ Ｓ ｅ ｘ ｐ ｅ ｃ ｔ ｅ ｄ ｐ ｌ ａ ｎ ．
oracle=：：=和变量绑定oracle通配符和运算符
oracle=：：=和变量绑定oracle通配符和运算符这篇是7788凑的：":="是赋值语句如: l_name :='sky';..."=" 是判断是否相等. 如: if 1=1 then...":" 是变量绑定如: if :P_NAME ='sky' then...变量绑定是指在的条件中使⽤变量⽽不是常量。
⽐如⾥有两条，select * from tab1 where col1=1;select * from tab1 where col1=2;对oracle数据库来说，这是两条完全不同的，对这两条语句都需要进⾏hard parse。
因为oracle会根据的⽂本去计算每个字符在内存⾥的hash值，因此虽然上述两条只有⼀个字符不⼀样，oracle根据hash算法在内存中得到的hash地址就不⼀样，所以o 绑定变量是相对⽂本变量来讲的,所谓⽂本变量是指在SQL直接书写查询条件，这样的SQL在不同条件下需要反复解析,绑定变量是指使⽤变量来代替直接书写条件，查询bind value在运⾏时传递，然后绑定执⾏。
优点是减少硬解析,降低的争⽤,节省shoracle通配符和运算符⽤于where⽐较条件的有： 等于：=、<、<=、>、>=、<> 包含：in、not in exists、not exists 范围：between...and、not between....and 匹配测试：like、not like Null测试：is null、is not null 布尔链接：and、or、not通配符： 在where⼦句中，通配符可与like条件⼀起使⽤。
在中： %(百分号)：⽤来表⽰任意数量的字符，或者可能根本没有字符。
_(下划线)：表⽰确切的未知字符。
?(问号)：⽤来表⽰确切的未知字符。
ORACLE绑定变量用法总结
ORACLE绑定变量⽤法总结之前对ORACLE中的变量⼀直没个太清楚的认识，⽐如说使⽤：、&、&&、DEIFINE、VARIABLE……等等。
今天正好闲下来，上⽹搜了搜相关的⽂章，汇总了⼀下，贴在这⾥，⽅便学习。
==================================================================================在oracle 中，对于⼀个提交的sql语句,存在两种可选的解析过程, ⼀种叫做硬解析,⼀种叫做软解析.⼀个硬解析需要经解析,制定执⾏路径,优化访问计划等许多的步骤.硬解释不仅仅耗费⼤量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩⼤（即限制了系统的并发⾏），⽽且引起的问题不能通过增加内存条和cpu的数量来解决。
之所以这样是因为门闩是为了顺序访问以及修改⼀些内存区域⽽设置的，这些内存区域是不能被同时修改。
当⼀个sql语句提交后，oracle会⾸先检查⼀下共享缓冲池（shared pool）⾥有没有与之完全相同的语句，如果有的话只须执⾏软分析即可，否则就得进⾏硬分析。
⽽唯⼀使得oracle 能够重复利⽤执⾏计划的⽅法就是采⽤绑定变量。
绑定变量的实质就是⽤于替代sql语句中的常量的替代变量。
绑定变量能够使得每次提交的sql语句都完全⼀样。
1.sqlplus中如何使⽤绑定变量，可以通过variable来定义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 过程已成功完成。
Oracle在视图中使用变量
Oracle在视图中使用变量在Oracle中，可以使用变量来增强SQL查询的灵活性和可重用性。
视图是由一个或多个表中的数据组成的虚拟表，它是一个已命名的SQL查询，可以像表一样使用。
但视图无法直接使用变量，因为视图是静态的，它是从查询的结果集中生成的。
然而，可以使用子查询来模拟在视图中使用变量的效果。
下面将详细介绍如何在视图中使用变量。
一种常见的方法是使用关联子查询来模拟变量在视图中的使用。
例如，假设我们有一个名为`employees`的表，其中包含员工的姓名和工资信息。
我们想要创建一个视图，该视图显示具有大于一些特定工资阈值的所有员工。
我们可以使用关联子查询来实现这一目标。
以下是一个示例：```sqlCREATE VIEW high_salary_employees ASSELECT employee_id, employee_name, salaryFROM employeesWHERE salary >SELECT MAX(salary)FROM employeesWHERE hire_date < TO_DATE('2024-01-01', 'YYYY-MM-DD')```在这个例子中，`high_salary_employees`视图筛选出薪资高于在2024年之前入职的员工中最高薪资的员工。
另一种方法是使用`WITH`子句（也称为公用表表达式）来创建一个临时视图，并在其中定义变量。
`WITH`子句允许我们在查询中定义一个临时表，然后将其用于主查询。
以下是一个示例：```sqlWITHvars ASSELECT 5000 AS min_salary,TO_DATE('2024-01-01', 'YYYY-MM-DD') AS hire_dateFROM dualhigh_salary_employees ASSELECT employee_id, employee_name, salaryFROM employeesWHERE salary > (SELECT min_salary FROM vars)AND hire_date < (SELECT hire_date FROM vars)SELECT * FROM high_salary_employees;```在这个例子中，`vars`子查询定义了两个变量，分别是`min_salary`和`hire_date`。
oracle variable语法
Oracle Variable语法一、什么是Oracle VariableOracle Variable是Oracle数据库中的一种特殊对象，它用于存储和传递数据。
在Oracle数据库中，变量是一个命名的内存位置，用于存储特定类型的数据。
通过使用变量，可以在PL/SQL代码中存储和操作数据，从而实现更灵活和可重用的代码。
二、Oracle Variable的定义和声明在Oracle数据库中，变量的定义和声明是通过使用DECLARE关键字来完成的。
在DECLARE块中，可以定义一个或多个变量，并为每个变量指定数据类型。
2.1 变量的定义和声明语法变量的定义和声明语法如下所示：DECLAREvariable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expression]; BEGIN-- 变量赋值和使用...END;/其中，各个部分的含义如下：•variable_name：变量的名称，用于在代码中引用该变量。
•CONSTANT：可选关键字，用于指定变量为常量，即不可更改的值。
•datatype：变量的数据类型，可以是Oracle内置的数据类型，也可以是用户自定义的数据类型。
•NOT NULL：可选关键字，用于指定变量不允许为空。
•:=或DEFAULT：用于为变量赋初值的操作符。
•expression：用于指定变量的初值，可以是一个常量、一个表达式或一个查询结果。
2.2 变量的赋值和使用在BEGIN和END之间的代码块中，可以通过赋值操作符(:=)为变量赋值，并在代码中使用这些变量。
例如：DECLAREnum1 NUMBER := 10;num2 NUMBER := 20;sum NUMBER;BEGINsum := num1 + num2;DBMS_OUTPUT.PUT_LINE('The sum is ' || sum);END;/在上述代码中，我们定义了两个变量num1和num2，并分别赋值为10和20。
使用绑定变量减少硬解析
使用绑定变量减少硬解析1、引言ORACLE数据库在执行SQL语句时,如果在共享池中找不到一样的SQL语句则会进行硬解析,硬解析会花费CPU和内存资源。
在做硬解析时，共享池中的类库缓存只能被不同的进程串行访问,ORACLE为了保证SGA数据结构不被破坏而增加的控制;进程在使用SGA 前必需先获取相应的栓，只有拿到栓的进程才能访问相应的内存区。
使用绑定变量可以减少硬解析、提高SQL共享，减少硬解析，同时也可以减少共享池的争用。
2、现象描述数据库运行过程中，相同的SQL语句经常要反复执行。
如果不使用绑定变量，每次执行时都需要进行硬解析(硬解析会占用CPU的时间片）。
当数据库在高峰期时，花在重复解析相同的语句时间就会变长同时共享池争用也很严重；3、处理过程（1）把需要进行绑定变量的SQL脚本写成字符串；（2）字符串中有条件的地方换成:<variable>的形式；（3）通过动态游标(OPEN FOR)来打开字符串所对应的SQL语句，同时传入相应的参数；（4）通过动态SQL(EXECUTE IMMEDIATE)来执行相应的字符串并串入相应的参数；（5）执行脚本并返回结果；（6）通过（3）执行的需要关闭游标；（4）会自动关闭游标；4、举例说明在PL/SQL中，可以通过EXECUTE IMMEDIATE 语句、OPEN ..FOR ..语句来实现SQL语句的绑定变量，下面举两个简单例子分别进行说明。
通过OPEN..FOR语句实现DECLAREc sys_refcursor;v varchar2(100):='select to_char(sysdate,''yyyy-mm-dd'')from dualwhere dummy=:1';r varchar2(10);BEGINopen c for v using'X';loopfetch c into r;exit when c%notfound;dbms_output.put_line(r);end loop;close c;END;通过EXECUTE IMMEDIATE语句实现DECLAREc sys_refcursor;v varchar2(100):='select to_char(sysdate,''yyyy-mm-dd'')from dualwhere dummy=:1';r varchar2(10);BEGINexecute immediate v into r using'X' ;dbms_output.put_line(r);END;5、经验总结（1）绑定变量可以减少SQL语句执行时间，降低共享池争用；（2）使用绑定变量优化器得到的执行计划可能不是最优的；（3）使用绑定变量可以使程序书写更加灵活，更易维护；。
Oracle绑定变量在C#.NET中的应用及意义
String sql="select * from t_child where childid =: childid";
parmlist.Add("childid");
valuelist.Add(entity.Childid);
但是际应用中经常是查询编号为001的儿童一次以后,有可能再也不用;接着你有可能查询儿童’002’,然后查询’003’等等。这样每次查询都是新的查询，都需要硬解析;
而第二个查询语句提供了绑定变量: childid,它的值在查询执行时提供,查询经 过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;
}
finally
{
adoHelper.Close();
}
}
四、 绑定变量使用限制条件
在对建有索引的字段（包括字段集），且字段（集）的集的势非常大时，使用绑定变量可能会导致无法选择最优的查询计划，因而会使查询效率非常低。
对于隔相当一段时间才执行一次的ｓｑｌ语句，利用绑定变量的好处会被不能有效利用优化器而抵消。
? 数据仓库的情况下。
? 很多表关联查询。
{
AdoHelper adoHelper = null;
try{
adoHelper = DatabaseConnectEntity.CreateHelper(dataBaseName);
IDataParameter[] parameters = new IDataParameter[bllist.Count];
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle 数据库的绑定变量特性及应用在开发一个数据库系统前,有谁对Oracle 系统了解很多,尤其是它的特性,好象很少吧;对初学者来讲,这更是不可能的事情;仅仅简单掌握了SQL的写法,就开始了数据库的开发,其结果只能是开发一个没有效率,也没有可扩展的系统;因此,我写这个主题也是希望让大家更多地掌握Oracle数据库的特性,从而在架构一个新系统时,能考虑系统的可扩展,可伸缩性,也兼顾系统的效率和稳定;使用绑定变量是Oracle数据库的特性之一;于是大家要问,为什么使用,怎样使用,它的使用限制条件是什么?我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题;1.为什么使用绑定变量?这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量;如果想要Oracle 运行减慢,甚至完全终止,那就可以不用绑定变量;这里举例说明上述问题;为了查询一个员工代号是123,你可以这样查询:select * from emp where empno=’123’;你也可以这样查询:select * from emp where empno=:empno;象我们往常一样,你查询员工’123’一次以后,有可能再也不用;接着你有可能查询员工’456’,然后查询’789’等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询(我们叫它硬编码的查询方法);因此,Oracle每次必须分析,解析,安全检查, 优化等等;第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低; 而执行绑定变量,提交相同对象的完全相同的查询的用户(这句话,大家听起来比较难理解,随后我会给出详细的解释),一次性使用就可重复使用,其效率不言耳语; 打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源;下面举例子去详细论证上述的问题,不使用绑定变量为生病状况:这是一个未使用的绑定变量(吃药前):set echo on;(把执行结果显示出来)alter system flush shared_pool;这条语句是清空共项池,每次都必须使用,确保共享池是空的,以提高执行效率;set timing on(打开记时器.)declaretype rc is ref cursor;l_rc rc;l_dummy all_objects.object_name%type;l_start number default dbms_utility.get_time;beginfor i in 1 .. 1000loopopen l_rc for'select object_namefrom all_objectswhere object_id = ' || i;fetch l_rc into l_dummy;close l_rc;end loop;dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 ) ||' seconds...' );end;/PL/SQL 过程已成功完成。
执行时间: 已用时间: 00: 00: 07.03这是一个使用的绑定变量(吃药后):set echo onalter system flush shared_pool;declaretype rc is ref cursor;l_rc rc;l_dummy all_objects.object_name%type;l_start number default dbms_utility.get_time;beginfor i in 1 .. 1000loopopen l_rc for'select object_namefrom all_objectswhere object_id = :x'using i;fetch l_rc into l_dummy;close l_rc;end loop;dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 ) ||' seconds...' );end;PL/SQL 过程已成功完成。
执行时间: 已用时间: 00: 00: 00.75大家自己比较结果,相差就是一个数量级;使用绑定变量不仅仅是运行快,而且允许多个用户同时使用;上述绑定变量的另一种写法供大家参考;set echo onalter system flush shared_pool;declaretype rc is ref cursor;l_rc rc;l_dummy all_objects.object_name%type;l_start number default dbms_utility.get_time;beginfor i in 1 .. 1000loopopen l_rc forselect object_namefrom all_objectswhere object_id = I;fetch l_rc into l_dummy;close l_rc;end loop;dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 ) ||' seconds...' );end;上述的环境是在数据哭Oracle 8.1.7, DB OS: Windows Server 2003, 1G Memory, P4 3.4GHZ CPU; 电脑配置不同,执行的结果是有差异的;2.怎样使用绑定变量?下面举例说明:2.1.让Oracle自己绑定变量(也叫静态绑定变量)set serverout on;set timing on;declarel_sql varchar2(2000);l_count number;l_param1 varchar2(100);l_param2 varchar2(100);beginl_param1:='a';l_param2:='b';select count(*) into l_count from table1 where col_1=l_param1and col_2=l_param2;dbms_output.put_line(l_count);end;/在上面的情况，Oracle会自己绑定变量，即，如果参数保存在一个数组中，select语句放在一个循环中，select 语句只会编译一次。
2.2 .动态绑定变量set serverout on;set timing on;declarel_sql varchar2(2000);l_count number;l_param1 varchar2(100);l_param2 varchar2(100);beginl_param1:='a';l_param2:='b';l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z ';Execute Immediate l_sql into l_count using l_param1,l_param2;dbms_output.put_line(l_count);end;/2.3. dbms_output的绑定变量使用Set echo on;Set serveroutput on;Set timming on;declarecursor_id integer;i number;xSql Varchar2(200);xOut varchar2(200);l_start number default dbms_utility.get_time;xRow integer;Begincursor_id:=DBMS_Sql.open_cursor;For i in 1..1000 LoopDBMS_Sql.parse(cursor_id,'insert into tvalues(:username,:user_id,Sysdate)',DBMS_SQL.V7);DBMS_Sql.bind_variable(cursor_id,'username','test'||to_char(i));DBMS_Sql.bind_variable(cursor_id,'user_id',i);xRow:=DBMS_Sql.execute(cursor_id);--insert into t values('test'||to_char(i),i,Sysdate);--xSql:='insert into t values(:username,:user_id,Sysdate)';--execute immediate xSql using 'test'||to_char(i),i;End loop;DBMS_sql.close_cursor(cursor_id);dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2) ||'seconds...');--xOut:=to_char(round((dbms_utility.get_time-l_start)/100,2)) ||'seconds...';--xOut:='seconds...';--return xout;end;这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较;3.我怎样知道正在使用绑定变量的方法;下面举例说明;创建一个Table;Create table t (xx int);执行下面的语句;BeginFor I in 1..100 loopExecute immediate’insert into t values(‘|| t ||’)’;End loop;end;现在准备好了脚本,开始创建一个字符串中删除常数的一个函数,它采用的是SQL语句为:insert into t values(‘hello’,55);insert into t values(‘world’,56);将其转换为insert into t values(‘#’,@);所有相同的语句很显然是可见的(使用绑定变量);上述两个独特的插入语句经过转换后变成同样的语句; 完成的转换函数为:Create or replace function remove_constants(p_query in varchar2) return varchar2 asl_query long;l_char varchar2(1);l_in_quates boolean default false;beginfor i in 1..length(p_query)loopl_char:=substr(p_query,i,1);if l_char='''' and l_in_quates thenl_in_quates:=False;elsif l_char='''' and not l_in_quates thenthenl_in_quates:=true;l_query=:l_query||'#';end ifif not l_in_quates thenl_query=:l_query||l_char;end if;end loop;l_query:=tranlate(l_query,'0123456789','@@@@@@@@@');for i in 1..8 loopl_query:=replace(l_query,lpad('@',10-i,'@'),'@');l_query:=replace(l_query,lpad('',10-i,''),'');end loop;return upper(l_query);end;/接着我们建立一个临时表去保存V$SQLAREA里的语句,所有 Sql的执行结果都写在这里;建立临时表;create global temporary table sql_area_tmp on commit preserve rows asselect sql_text,sql_text sql_text_wo_constants fromv$sqlarea where 1=0;保存数据到临时表上;insert into sql_area_tmp(sql_text) select sql_text from v$sqlarea;对临时表中的数据进行更新;删除掉常数;Update sql_area_tmp set SQL_TEXT_WO_CONSTANTS=remove_constants(sql_text);现在我们要找到哪个糟糕的查询select SQL_TEXT_WO_CONSTANTS,count(*) from sql_area_tmpgroup by SQL_TEXT_WO_CONSTANTShaving count(*)>10order by 2;SQL_TEXT_WO_CONSTANTS count(*)- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - -INSERT INTO T VALUES(@) 100另外, 设定如下参数Alter session set sql_trace=true;Alter session set timed_statictics=True;Alter session set events ‘10046 trace name contextforever,level <N>’;这里的’N’表示的是1,4,8,12,详细内容请参考相关文档Alter session set events ‘10046 trace name context off’;可以用 TKPROF 工具查看绑顶变量执行的结果,如例子:declarel_number number;l_text varchar2(5);beginfor i in 1 .. 1000loopl_number := i;l_text := 'test'||to_char(i);insert into t values(i,l_text);end loop;commit;end;call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.10 0 0 0 0Execute 1009 0.09 0.21 0 4 1035 1009Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 1011 0.09 0.31 0 4 1035 10094.绑定变量在应用开发环境下的使用;4.1 在 or and VB中的的使用建议用Oracleclient DB的连接方法,OleDB不支持;下面是使用OracleClient连接的使用例子(这个代码执行只需要2秒不到);BeginDim cn01 As New OracleConnectionDim CMD01 As New OracleCommandDim Cmd As New OleDbCommandDim i As IntegerTry<add key="DBCONN_SFCFA" value="UserID=sfcfa;password=SFCFA;Data Source=CIM;" />xConnStr =System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA")'cn01.ConnectionString()cn01.ConnectionString = xConnStrcn01.Open()TextBox1.Text = NowApplication.DoEvents()xSql = "insert into t values(:username,:userid,sysdate) "For i = 1 To 1000CMD01 = New OracleClient.OracleCommand(xSql,cn01)mandType = CommandType.TextCMD01.Parameters.Add("username", "test" +CStr(i))CMD01.Parameters.Add("userid", i)CMD01.ExecuteNonQuery()CMD01.Parameters.Clear()Next iTextBox2.Text = NowCatch ex As OleDbExceptionMsgBox(ex.Message)Catch ex As ExceptionMsgBox(ex.HelpLink + ex.Message)End TryEnd.OleDB(VB,ASP等)不支持绑定变量,或者我没有找到更好的方法去实现它;它有变量的概念但不支持绑定;网络上,有很多帖子说;他实现了绑定变量用VB or ASP;我按照他们的方法去试,发现他们与单纯传参数没有什么区别,请看下面的内容;OleDB(这个执行需要5秒;) :Dim xConnStr, xSql As StringDim Cn As New OleDbConnectionDim cn01 As New OracleConnectionDim CMD01 As New OracleCommandDim Cmd As New OleDbCommandDim i As IntegerTry<add key="DBCONN_SFCFA" value="Provider=MSDAORA.1;UserID=sfcfa;password=SFCFA;Data Source=CIM;"/>xConnStr =System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA") 'Cn.ConnectionString()Cn.ConnectionString = xConnStrCn.Open()TextBox1.Text = NowApplication.DoEvents()xSql = "insert into t values(?,?,sysdate) "For i = 1 To 1000Cmd = New OleDbCommand(xSql, Cn)mandType = CommandType.TextCmd.Parameters.Add("username", "test" + CStr(i)) Cmd.Parameters.Add("userid", i)Cmd.ExecuteNonQuery()Cmd.Parameters.Clear()Next iTextBox2.Text = NowCatch ex As OleDbExceptionMsgBox(ex.Message)Catch ex As ExceptionMsgBox(ex.HelpLink + ex.Message)End TryVB or ASP(耗时也是5秒左右…):Private Sub Command1_Click()Dim sConn As StringDim BVCS_CN As ADODB.Connection'Dim BVCS as ADODB.Dim xCMD As mandDim xPre As ADODB.ParameterDim xSql As StringDim xSql01 As StringDim xRS As ADODB.RecordsetOn Error GoTo 1SetDBConnection = TrueSet BVCS_CN = New ADODB.Connection'BVCS_CN.Provider = "MSDAORA"'sConn = "DATA SOURCE=" & ServerName & ";"sConn = "Provider=MSDAORA.1;Password=sfcfa;User ID=sfcfa;Data Source=cim;"With BVCS_CN.Open sConnEnd WithIf BVCS_CN.State = 0 ThenMsgBox "DB Connection is error"Exit SubEnd IfText1.Text = NowDoEventsSet xCMD = New mandDim xTest As StringSet xPre = New ADODB.Parameter'BVCS_CNFor i = 1 To 1000With xCMD.ActiveConnection = BVCS_CN.CommandText = " Insert into TT(username,userid) values(?,?) ".CommandType = adCmdText.Parameters.Append .CreateParameter("username", adBSTR, adParamInput, 30, "test" + CStr(i)).Parameters.Append .CreateParameter("userid", adInteger, adParamInput, 4, i).Prepared = True.ExecuteEnd WithxCMD.Parameters.Delete 1xCMD.Parameters.Delete 0Next iSet xCMD = NothingText2.Text = NowExit Sub1:Set xCMD = NothingMsgBox Error$For Each objErr In BVCS_CN.ErrorsMsgBox objErr.DescriptionNextBVCS_CN.Errors.ClearExit SubResume NextEnd Sub4.2 在Delphi中的使用情况;这里特殊说明, Borland Delphi 4.0以上的版本已经开始完全支持绑定变量的概念,因此,它执行数据库的查询效率要好于其他开发工具;执行的结果不到2秒;procedure TForm1.Button1Click(Sender: TObject);Vari :Integer;beginedit1.text:=DatetimeToStr(now);For i := 1 to 1000 do//BeginWith Query1 doBeginclose;Sql.clear;Sql.add('Insert into t Values(:username,:user_id,sysdate) ');ParamByName('username').AsString :='test' ;ParamByName('user_id').AsInteger :=i ;execSql;End;//end;//edit2.text:=DateToStr(now);edit2.text:=DatetimetoStr(now);end;4.3. 在Java中的使用绑定变量String v_id = 'xxxxx';String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量stmt = con.prepareStatement( v_sql );stmt.setString(1, v_id ); //为绑定变量赋值stmt.executeQuery();在Java中，结合使用setXXX 系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL 语句的性能。