oracle存储过程硬解析,【PLSQL】绑定变量，动态SQL，硬解析和软解析

************************************************************************

****原文：blog.csdn.net/clark_xu徐长亮的专栏

************************************************************************

1.1 变量

在匿名块或者存储过程中定义的变量为局部变量，及作用域在整个匿名块或存储过程中。运行结束，则该变量就不存在了；

绑定变量

在PLSQL的SQL中直接私有绑定变量：bv_name，不需要定义

1.1.1  绑定变量的定义

--绑定变量的定义

variable I number

begin

for I in 1 .. 1000 loop

execute immediate ‘insert into test values(:i)’ using I;

:I :=I; ---绑定变量的赋值

end loop

dbms_output.put_line(:i);

commit;

end;

print i

exec :i=:=10000 ---绑定变量的赋值

print：I；

3.3 PLSQL的SQL分类

静态SQL：

--在PLSQL块中使用的SQL语句在编译的时候是明确的，SQL语句在PLSQL编辑阶段编译；

动态SQL：

--PLSQL编译时SQL是不确定的，如根据用户输入参数的不同而执行不同的操作，

编译程序对动态语句不处理；在运行的时候，动态创建SQL语句

3.3.1静态SQL

--一次硬分析，一次软分析，1000次执行

create or replace procedure proc1

ls

begin

for I in 1 .. 1000 loop

insert into test values(I);

end loop;

commit;

end;

begin proc1 end;

3.3.2本地动态SQL(未使用绑定变量)

1000此硬分析，1000此软分析,1000次执行

create procedure proc1 ls

begin

for I In 1  ..1000 loop

execute immediate ‘insert into test values(‘||I||’)’;

end loop;

commit;

end;

---编译过程的时候，不编译sql语句

begin proc1 end;

3.3.3本地动态SQL(使用绑定变量)

--1次硬分析，1000次软分析，1000次执行

create procedure proc1 ls

begin

for I In 1  ..1000 loop

execute immediate ‘insert into test values(：i)’ using i;

end loop;

commit;

begin proc1 end;

3.4 SQL语句的处理过程

语法检查：syntax check

语义检查：semantic check

--诸如检查sql语句中的访问对象是否存储，该用户是否具备相应的权限；

对sql语句进行解析parse

--利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)

执行sql,返回结果：execute and  return;

3.4.1硬解析和软解析

oracle利用内部hash算法来获得该sql的hash值，然后在library

cache里查找是否存在该hash值；

假设存在，则将此sql与cache中的进行比较；假设“相同”，就将利用已有的解析数和执行计划，而忽略了优化器的相关工作，这就是软解析的过程；

如果上面两个减少中任由一个不成立，那么优化器都将进行创建解析树，生成执行计划的动作，这个过程都叫硬解析；

3.5对应存储过程的操作

创建并编译过程：

create or replace procedure

编译过程

alter procedure procname compile;

调用过程

用匿名子程序调用，直接写过程名

用有名子程序调用，直接写过程名；

删除过程：

drop procedure

3.5.1过程的使用案例

创建存储过程

create or replace procedure p_account

(p_id number,p_realname out varchar2,p_age out number)

ls

begin

select real_name,round((sysdate-birthdate)/360) into p_realname,p_age

from account

where id=p_id;

exception

when no_data_found then

p_realname :=’no account’;

p_age：=0；

end;

有名子程序调用：

declare

v_realname varchar2(20);

v_age number;

begin

p_account(1011,v_realname,v_age);

dbms_output.put_line(v_realname || ‘’ || v_age);

end;

匿名子程序调用,绑定变量：

variable b_realname varchar2(20)

variable b_age number

begin

p_account(1011,:b_realname,:b_age)；

end;

print b_realname;

print b_age;

3.6 PLSQL中的静态sql

oracle在解析sql时候会把PLSQL中定义的变量转为绑定变量insert into test values(:b1),减少硬解析的次数；

server process将执行完的sql cache起来，不关闭，当再执行sql，不需要软解析；

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

************************************************************************

****原文：blog.csdn.net/clark_xu徐长亮的专栏

************************************************************************

原文：http://blog.csdn.net/clark_xu/article/details/44220243