1. PL/SQL是什么
- pl/sql(procedural language/sql)是oracle在标准sql的基础上增加了过程化处理，把DML和select语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言
- pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
1.1 扩展
- 过程、函数、触发器用pl/sql语言编写
- 过程、函数、触发器存储、运行在oracle数据库上
- pl/sql是非常强大的数据库过程语言
- 过程、函数可以在java程序中调用
2. 程序结构
2.1 PL/SQL程序结构
PL/SQL程序包括：
- 声明部分：DECLARE和BEGIN之间的语句
- 执行部分：BEGIN和EXCEPTION或者BEGIN和END之间的语句
- 异常处理部分：EXCEPTION和END之间的语句
其中声明和异常处理部分为非必须
例如：
DECLARE
v_AccountID NUMBER(5) := 1001;
v_RealName VARCHAE(20);
BEGIN
SELECT real_name INTO v_RealName
FROM account WHERE id = v_AccountID;
EXCEPION
WHEN NO_DATA_FOUND THEN
INSERT INTO Fee_LOG(descr)
VALUES('Account 1001 does not exist!');
COMMIT;
END
2.2 Hello World
BEGIN
dbms_output.put_line('Hello World')
END;
说明：
dbms_output – package包
包里包含过程、函数、过程
put_line(‘Hello World’)
2.3 注释
- 单行注释:
-- - 多行注释: 由
/*开头，由*/结尾
3. 数据类型
PL/SQL中的数据类型分为两种：
- 标量类型，包括数字型、字符型、日期型、布尔型
- 复合类型，包括recode、associative array、nested table、varray
3.1 标量类型
3.1.1 数字类型
- number
- number的子类型dec(38),float(38),real(18)…
- binary_integer( 只能用于PL/SQL )
3.1.2 字符型
- varchar2、varchar(长度：1~32767)
- string( 只能用于PL/SQL，长度：1～32767)
- char(长度：1～32767)
- long
3.1.3 日期型
- date
3.1.4 布尔型( 只能用于PL/SQL )
- 用于存储逻辑值(true,false,null)
- 不能向数据库中插入boolean类型的数据
- 不能将列值保存到boolean变量中
- 只能对boolean变量执行逻辑操作
3.2 复合类型
3.2.1 记录(RECORD)
- 记录类型，用于处理单行多列数据
- 语法
DECLARE TYPE t_rec IS RECORD -- t_rec是记录类型 (realname account.real_name%TYPE, -- real_name是成员 idcardno account.idcard_no%TYPE, birthdate date ); v_account t_rec; -- v_account是记录类型的变量
- RECORD变量的引用
- 记录类型变量的属性引用方法是：变量名.属性名
- 在insert语句和update语句中可以使用记录类型变量
- 例子1
declare
type t_account is record(
real_name account.real_name%type,
idcard_no account.idcard_no%type,
birthdate date
);
v_account t_account;
v_id account.id%type := 1011;
begin
select real_name,idcard_no,birthdate
into v_account
from account
where id = v_id;
dbms_output.put_line(v_account.real_name);
dbms_output.put_line(v_account.idcard_no);
dbms_output.put_line(v_account.birthdate);
end;
- 例子2
DECLARE v_account account%ROWTYPE; BEGIN insert into account values v_account; update account set row = v_account; commit; END;
3.2.2 关联数组(Associative array)
#####声明
DECLARE TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARCHAR2(size_limit)]
说明：
- 同种类型的一维、无边界的稀疏集合，只能用于PL/SQL
- type_name是关联数组的类型名，element_type是关联数组中存储的元素类型
- INDEX BY后面的数据类型是下标的数据类型
####Associative array的方法
- exists
- exists(i):判断第i个元素是否存在
- count
- 返回联合数组的元素个数，不包括被删除的元素
- 对于空的联合数组，返回0
- first和last
- 返回最小和最大下标号，如果为空，返回null
- prior(n)和next(n)
- 返回第n个元素的前一个和后一个下标号，如果不存在，则返回null
- trim(n)
- 从最后一个元素删除n个元素
- delete
- delete：删除所有元素
- delete(n)：删除第n个元素
- delete(m,n)：从第m个元素删除到第n个元素
####Associative array的异常
DECLARE TYPE numlist IS TABLE OF number INDEX BY binary_integer; nums numlist; --自动初始化为null BEGIN nums(null) := 3; --触发VALUE_ERROR IF nums(1) = 1 THEN --触发NO_DATA_FOUND ... END IF; END;
例子1 简单用法
declare type t_indtab is table of number index by binary_integer; v_indtab t_indtab; begin v_indtab(1) := 1; v_indtab(2) :=2; v_indtab(10) := 10; v_indtab(5) := 5; dbms_output.put_line(v_indtab(1)); dbms_output.put_line(v_indtab(10)); dbms_output.put_line(v_indtab(5)); dbms_output.put_line(v_indtab(2)); -- 返回联合数组的元素个数 dbms_output.put_line(v_indtab.count); -- 返回最小下标 dbms_output.put_line(v_indtab.first); -- 返回最大标 dbms_output.put_line(v_indtab.last); end;
例子2 遍历关联数组(for)
declare
type t_indtab is table of number
index by binary_integer;
v_indtab t_indtab;
begin
v_indtab(1) := 1;
v_indtab(2) :=2;
v_indtab(10) := 10;
v_indtab(5) := 5;
for i in v_indtab.first .. v_indtab.last loop
if v_indtab.exists(i) then
dbms_output.put_line(v_indtab(i));
end if;
end loop;
end;
例子3 遍历关联数组(while)，效率大于for
declare
type t_indtab is table of number
index by binary_integer;
v_indtab t_indtab;
v_index binary_integer;
begin
v_indtab(1) := 1;
v_indtab(2) :=2;
v_indtab(10) := 10;
v_indtab(5) := 5;
v_index := v_indtab.first;
while v_index <= v_indtab.last loop
dbms_output.put_line(v_indtab(v_index));
v_index := v_indtab.next(v_index);
end loop;
end;
案例1
declare
cursor c_account is
select id,real_name,idcard_no from account;
type t_rec is record(
real_name varchar2(20),
idcard_no char(18)
);
type t_account is table of t_rec
index by binary_integer;
v_account t_account;
v_rec t_rec;
v_index account.id%type;
begin
for i in c_account loop
v_rec.real_name := i.real_name;
v_rec.idcard_no := i.idcard_no;
v_account(i.id) := v_rec;
end loop;
v_index := v_account.first;
while v_index <= v_account.last loop
dbms_output.put_line(v_index||'--'||v_account(v_index).real_name||'--'||v_account(v_
index).idcard_no);
v_index := v_account.next(v_index);
end loop;
end;
3.3 %TYPE方式
- 变量具有与数据库的表中某列或其它变量相同的类型
DECLARE v_RealName account.real_name%TYPE; DECLARE v_TempVar number(7,3) not null := 12.3; v_anotherVar v_tempvar%TYPE := 12.3;
3.4 %ROWTYPE
- 用表结构或视图结构定义变量
- 当使用%rowtype定义记录变量时，RECORD成员的名称和类型与表或视图的列名称和类型完全相同
DECLARE v_account accout%ROWTYPE
4. 变量声明
4.1 语法
var_name type [constant] [not null] [:=vale];
- 说明
- PL/SQL规定没有初始化的变量为NULL
- 例子
DECLARE v_RealName varchar2(20); v_BaseCost number(7,2) := 5.9; v_Count binary_integer := 0;
5. PL/SQL流程控制语句
5.1 分支
5.1.1 if
IF boolean_expre1 THEN ... ... ELSIF boolean_expre2 THEN ... ... ELSE ... ... END IF;
5.1.2 case
CASE WHEN condition THEN return_expr
[WHEN condition THEN return_expr]
... ...
ELSE else_expr
END
5.2 循环
5.2.1 LOOP
LOOP statement1; statement2; EXIT WHENEND LOOP; </pre> #### 5.2.2 WHILE WHILELOOP statement1; statement2; [EXIT WHEN ] END LOOP; </pre> #### 5.2.3 FOR - 语法 FOR 循环计时器 IN 下限..上限 LOOP statement1; statement2; END LOOP;- 说明： - 每个循环一次，循环变量自动加1；使用关键字`REVERSE`，循环变量自动减1 - 跟在`IN REVERSE`后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式 - 可以使用`EXIT`或者`EXIT WHEN`子句退出循环 ## 6. 变量 - 在匿名块或存储过程中定义的变量为局部变量，即作用域在整个匿名块或存储过程中。上述程序运行结束，该变量也不存在了。 - 绑定变量[宿主变量] - 在PL/SQL的SQL中直接使用绑定变量 :bv_name ，不需要定义 - 绑定变量的定义SQL>variable i number Begin for i in 1 .. 1000 loop execute immediate 'insert into test values(:i)' useing i; :i = i; -- 绑定变量的赋值 end loop; dbms_output.put_line(:i); commit; end; SQL>print i; SQL>exec :i := 10000 SQL>print i--绑定变量 SQL>variable v_realname varchar2(20) SQL>variable v_age number SQL>exec nova_account_select(1011, :v_realname, :v_age); SQL>print v_realname; SQL>print v_age;## 7. 异常(Exception) ### 7.1 Oracle错误 - PL/SQL错误 - 编译时错误 - 运行时错误 - 运行时的错误 - oracle错误(ORA-xxxx) - PL/SQL运行错误 - 用户定义的条件 ### 7.2 Oracle错误处理机制 - 在程序运行期间的错误对应一个异常(EXCEPTION) - 一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，由异常处理器来处理运行时错误。  ### 7.3 异常的类型 - 隐式触发 - ORACLE预定义异常 - 非ORACLE预定义异常 - 显式触发 - 用户自定义异常 ### 7.4 PL/SQL中的异常处理 #### 7.4.1 预定义异常 例子1declare type t_rec_acc is record( real_name varchar2(20), idcard_no number(20) ); type t_account is table of t_rec_acc index by binary_integer; v_account t_account; begin select real_name,idcard_no bulk collect into v_account from account where 1 = 2; for i in v_account.first .. v_account.last loop dbms_output.put_line(v_account(i).real_name ||'--'||v_account(i).idcard_no); end loop; exception when value_error then dbms_output.put_line('v_account is empty'); end;#### 7.4.2 非预定义异常 例子declare type t_rec_acc is record( real_name varchar2(20), idcard_no number(20) ); type t_account is table of t_rec_acc index by binary_integer; v_account t_account; e_integrity exception; pragma exception_init(e_integrity,-2291); begin insert into nova_service_detail(id,service_id) values (11111,22222); select real_name,idcard_no bulk collect into v_account from account where 1 = 2; for i in v_account.first .. v_account.last loop dbms_output.put_line(v_account(i).real_name ||'--'||v_account(i).idcard_no); end loop; exception when value_error then dbms_output.put_line('v_account is empty'); when e_integrity then dbms_output.put_line('no service'); end;#### 7.4.3 自定义异常 例子declare v_first binary_integer; e_notnull exception; v_sqlerrm varchar2(1000); begin insert into nova_service_detail(id,service_id) values (11111,22222); if v_first is null then raise e_notnull; end if; exception when e_notnull then dbms_output.put_line('v_forst is null'); when others then v_sqlerrm := sqlerrm; dbms_output.put_line('erro message:'||v_sqlerrm); end;## 8. PL/SQL中的SQL DDL语句不能在PL/SQL中直接写BEGIN execute immediate 'create table test(c1 number)'; END;PL/SQL中的SQL分类 - 静态SQL 在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象，即SQL语句是在PL/SQL编译阶段编译的。 - 动态SQL 在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 ### 8.1 静态SQL DML和TCL可以直接中PL/SQL中直接写BEGIN insert into host(id) values('10.0.0.11'); commit; END;### 8.2 本地动态SQL 本地动态SQL使用`EXECUTE IMMEDIATE`语句来实现。 DDL只能使用动态SQL执行。BEGIN execute immediate 'insert into host(id) values('10.0.0.11')'; execute immediate 'commit'; END;## 9. PL/SQL中的SELECT 根据select语句返回的记录数，将select语句的实现分为两类： - 当且仅当返回一条记录 用 `select .. into ..` 语句实现 - 返回0条或多条记录 用 `cursor` 实现 用 `select .. bulk collect into ..`实现 ### 9.1 `select .. into ..`DECLARE v_id number(10) := 1011; v_realname account.real_name%type; v_idcardno account.idcard_no%type; BEGIN select real_name,idcard_no into v_realname,v_idcardno from account where id = v_id; dbms_output.put_line(v_realname); dbms_output.put_line(v_idcardno); END;### 9.2 `CURSOR` ####概念 - 根据select语句返回的记录数，若返回记录数是0条或多条用`CURSOR`实现 - Oracle使用专有SQL工作区(private sql workarea)来执行sql语句，存储处理信息。这个工作区称为`CURSOR`。 - Oracle所执行的每一个sql语句都有唯一的`CURSOR`与之相对应。 - 程序员用PL/SQL的`CURSOR`定义所需执行的select语句 ####`CURSOR`的分类 - 隐式`CURSOR` - `select .. into ..` - DML语句 - 显示`CURSOR` - 返回多条记录的select语句用显示`CURSOR`实现 ####显式CURSOR的处理  ####显式CURSOR的属性  ####隐式CURSOR的属性  ####CURSOR的声明 - 在游标声明中使用标准的select语句 - 如果需要按指定的次序处理行，可在查询中使用order by子句 - 可以在查询中引用变量，但必须在CURSOR语句之前声明这些变量DECLARE cursor c_account_id(p_account_id number) is select real_name,idcard_no from account where id = p_account_id;####OPEN CURSOR - 通过open cursor来执行select语句并标识结果集 - select语句如果没有返回记录，不会出现异常open c_account(1011);####FETCH CURSOR - 检索当前行，把值赋给变量 - 变量可以是RECORD类型或简单变量 - 如果是简单变量 - 包含相同数量的变量 - 把每一个变量和相应的列进行位置匹配 - 通过循环检测CURSOR中是否包含数据行FETCH cursor_name INOT [variable1,variable2,...|record_name];####结果集提取的控制 - 使用循环处理显式CURSOR结果集中的多行数据 - 每次FETCH一行，反复进行 - 使用%NOTFOUNFD属性检测一次不成功的提取操作 - 使用显式CURSOR的属性检测每一次提取是否成功，避免出现无限循环 ####CLOSE CURSOR - 处理完结果集中的数据后，应该关闭CURSOR - 如果需要，可以再次打开该CURSOR - CURSOR一旦关闭，所有和该CURSOR相关的资源都会被释放，不可再从关闭的CURSOR中提取数据 - 任何对关闭的CURSOR的操作都会引发 `INVALID_CURSOR`错误 - 每个session能打开的CURSOR数量由`OPEN_CURSOR`参数决定close c_account;例子1 loop 循环的写法declare cursor c_account is select real_name,idcard_no from account; v_account c_account%rowtype; begin open c_account; loop fetch c_account into v_account; exit when c_account%notfound; dbms_output.put_line(v_account.real_name||' '||v_account.idcard_no); end loop; close c_account; end;例子2 for 循环的写法declare cursor c_account is select real_name,idcard_no from account; begin for i in c_account loop dbms_output.put_line(i.real_name||' '||i.idcard_no); end loop; end;例子3 隐式CURSOR的处理begin insert into test_cjp values (111); dbms_output.put_line(sql%rowcount||' rows inserted'); delete from test_cjp where c1 <= 10; dbms_output.put_line(sql%rowcount||' rows deleted'); end;说明： `sql%rowcount`中的`sql`表示离他最近的sql语句 ### 9.3 `select .. bulk collect into ...` 例子1 bulk collectDECLARE type t_rec is record( real_name account.real_name%type, idcard_no account.idcard_no%type ); type t_account is table of t_rec index by binary_integer; v_account t_account; BEGIN select real_name,idcard_no bulk collect into v_account from account where 1 = 1; for i in v_account.first .. v_account.last loop dbms_output.put_line(v_account(i).real_name||'--'||v_account(i).idcard_no); end loop; END;例子2DECLARE type t_rec is record( real_name account.real_name%type, age number(3) ); type t_account is table of t_rec index by binary_integer; v_account t_account; v_age_sum number(6) := 0; BEGIN select real_name,round((sysdate-birthdate)/365) bulk collect into v_account from account where 1 = 1; if v_account.count <> 0 then for i in v_account.first .. v_account.last loop v_age_sum := v_age_sum + v_account(i).age; dbms_output.put_line(v_account(i).real_name||'--'||v_account(i).age ||'--'||v_age_sum); end loop; else dbms_output.put_line('v_account is empty'); end if; END;## 10. 子程序 ### 10.1 有名子程序 - 命名的PL/SQL块，编译并存储在数据库中，可以在任何需要的地方调用 - 子程序的组成部分： - 子程序头 - 声明部分 - 可执行部分 - 异常处理部分(可选) ### 10.2 匿名子程序和有名子程序 - 匿名子程序 - 匿名块不存在数据库中 - 每次使用时都会进行编译 - 不能供其他块调用 - 有名子程序 - 模块化：将程序分解为逻辑模块 - 可重用性：可以被任意数目的程序调用 - 可维护性：简化维护操作 - 安全性：通过设置权限，使数据更安全 ### 10.3 有名子程序的分类 - procedure：过程 - function：函数 - package：包 - trigger：触发器 ### 10.4 过程(procedure) #### 10.4.1 什么是procedure？ #### 10.4.2 语法CREATE [OR REPLACE] PROCEDURE proc_name [(arg_name [ {IN|OUT|IN OUT}] type, ...)] {IS|AS}BEGIN [EXCEPTION ] END; </pre> 例子 --创建procedure create or replace procedure nova_proc1 (p_in varchar2, p_out out varchar2, p_inout in out varchar2) is v_in varchar2(10); begin --p_in := p_in || 'd'; v_in := p_in || 'd'; p_out := p_out || 'd'; p_inout := p_inout || 'd'; end; --调用procedure declare v_out varchar2(10) := 'abc'; v_inout varchar2(10) := 'abc'; begin nova_proc1('abc',v_out,v_inout); dbms_output.put_line(v_out); dbms_output.put_line(v_inout); end;####调用存储过程 1. 用匿名块调用begin proc_name; end;2. 在SQL工作表中直接调用exec account_number;####存储过程中的参数 #####形参和实参 - 在创建过程语句中的参数为形参 - 在调用过程时括号内的参数为实参 #####形参的种类 - in，参数的缺省模式 - 在调用过程的时候，实际参数的值被传递给该过程；在过程内部，形参只__可读__的。 - out - 在调用过程时，任何的实参将被忽略；在过程内部，形参是只__可写__的。 - in out - 是in与out的组合。在调用过程的时候，实参的值可以被传递给该过程；在过程内部，形参也可以被读出也可以被写入；过程结束时，形式参数的内容将赋给调用时的实际参数。 #####对实际参数的要求 - 模式为IN的形参对应的实际参数可以是常量或变量 - 模式为IN OUT或OUT的形参对应的实际参数必须是变量，用于存储返回的值，所以不能是常量或表达式。 #####形参的限制 - 在调用过程当中，实际参数在将值传递给过程时，也传递了对变量的限制 - 形式参数不能声明长度，但可使用%TYPE来进行限制 #####带参数的过程调用 - 位置表示法 - 调用时添入所有参数，实参与形参按顺序一一对应 - 名字表示法 - 调用时给出形参名字，并给出实参 - procname(12,p_outparm=>v_var1,p_inout=>10); - 两种方法可以混用 - 混用时，第一个参数必须通过位置来指定 - 名字表示对于参数很多时，可提高程序的可读性 #####使用缺省参数 - 形参可以指明缺省值 parm_name [mode] type {:=|default} init_type - 位置标示法时，所有的缺省值都放在最后面 - 使用名字标示法则无所谓 - 声明时，如果有缺省值，尽量将缺省值放在参数表的末尾 ####调用过程所需的权限 - 在过程中执行DDL操作，所需的权限必须通过直接授予的方式，不能通过角色授予。 - 调用过程时，所有角色都是disable的，即角色中包含的所有权限都不能生效。 - 调用其他用户的过程，必须由过程的属主授予执行权限 - grant execute on procname to username; > 匿名块的编译和执行在一起 > 有名块的编译和执行分开，role在两个阶段可以状态不一致(enable disable). > oracle解决办法，所有通过角色授予的系统权限不能用。在存储过程中需要的系统权限，必须通过直接授权的方式。 ### 10.5 函数(FUNCTION) ####什么是function？ ####语法create [or replace] function fun_name [(arg_name [{in|out|in out}] type,...)] return type isbegin return value; [exception ] end; </pre> ####例子 --声明函数 create or replace function fun1 (p_in number,p_out out number) return number is begin p_out :=2; return p_in; end; --------------------------------------------------- --调用函数 declare v_in number := 123; v_out number(5); v_result number(5); begin v_result := fun1(v_in,v_out); dbms_output.put_line('v_out '||v_out); dbms_output.put_line('v_result '||v_result); end;### 10.6 包(package) ####什么是package - package是一个可以将相关对象存储在一起的PL/SQL结构。package包含了两个分离的组成部分：specification(package的声明，包声明)和body(声明中的程序实体，包体)。每个部分都单独被存储在数据字典中。包声明是一个操作接口，对应用来说是可见的； - 包体是黑盒，对应用来说隐藏列实现细节。 ####包的组成 - 将相关的若干程序单元组织到一块，用一个包来标识这个集合，包可以包含以下的程序单元：  ####声明语法create or replace package pkg_name {is|as} 公共变量(variable)的定义| 公共类型(type)的定义| 公共异常(exception)的定义| 公共游标(cursor)的定义| 函数说明 | 过程说明 end;####包体语法--创建包声明 create or replace package body pkg_name is ... end;例子--创建包声明 create or replace package n_pkg1 is type t_rec is record (m1 number, m2 varchar2(10) ); v_rec t_rec; procedure proc1; function fun1(p_in number) return number; end; -------------------------------------------- --创建包体 create or replace package body n_pkg1 is procedure proc1 is begin dbms_output.put_line(v_rec.m1); end; function fun1(p_in number) return number is begin return p_in; end; end; -------------------------------------------- --调用 begin n_pkg1.v_rec.m1 := n_pkg1.fun1(10); n_pkg1.proc1;####包中变量的作用域 sessioin ####包的优点 - 方便对存储过程和函数的组织 - 将相关的过程和函数组织在一起 - 在一个用户环境中解决命名的冲突问题 - 方便对存储过程和函数的管理 - 在不改变包的声明定义是可以改变包体的实现 - 限制过程、函数的依赖性 - 方便对存储过程和函数的安全性管理 - 整个包的访问权限只需一次性授权 - 区分公用过程和私有过程 - 改善性能 - 在包被首次调用时作为一个整体全部调入内存 - 减少多次调用时的磁盘I/O次数 ### 10.7 触发器(trigger) ####DML行级触发器create [or replace] trigger trig_name {before|after} insert [or update ...] on tab_name for each row [when restricting_condition] PL/SQL block;####:old和:new 在行级触发器中，在列名前加上:old标识符表示该列变化前的值，加上:new标识符表示变化后的值 ## 11. SQL的处理过程 ### 11.1 过程 - 语法检查(syntax check) - 检查此sql的拼写是否符合语法 - 语义检查(semantic check) - 诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限 - 对sql语句进行解析(parse) - 利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan) - 执行sql，返回结果(execute and return) ### 11.2 软分析和硬分析 Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值 - 假设存在，则将此sql与cache中的进行比较；假设“相同”，就将利用已有的解析树与执行计划，而省略列优化器的相关工在。这就是软分析的过程。 - 如果上面两个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程叫硬分析。 - 分析语句 软分析、硬分析 - 绑定变量 - 赋值 - 执行语句 - 获取数据 ### 11.2 静态SQL/动态SQL的性能分析 - 静态SQL - 动态SQL 例子：--静态SQL create or replace procedure proc1 is begin for i in 1 .. 1000 loop insert into test values(i); end loop; commit; end; --编译存储过程时编译SQL语句 begin proc1 end;--1次硬分析、1次软分析、1000次执行 ------------------------------------------- --未使用绑定变量 create or replace procedure proc1 is begin for i in 1 .. 1000 loop execute immediate 'insert into test values('||i||')'; end loop; commit; end;--编译存储过程时不编译SQL语句 begin proc1 end;--1000次硬分析、1000次软分析、1000次执行 ------------------------------------------- --使用绑定变量 create or replace procedure proc1 is begin for i in 1 .. 1000 loop execute immediate 'insert into test values(:i)' using i; end loop; commit; end;--编译存储过程时不编译SQL语句 begin proc1 end;--1次硬分析、1000次软分析、1000次执行####小节 - 尽量使用静态SQL - 动态SQL中要使用绑定变量 ## 12. 附录 ####设置输出打开set serveroutput on;###显示创建对象时的错误信息sql>show error###查procedureselect object_name,object_type,status from user_objects where object_name='NOVA_ACCOUNT_NUMBER'
