资源描述
第一章:PL/SQL概述
一、什么是PL/SQL
ORACLE公司发明PL/SQL为了克服SQL语言自身的一些缺陷并为那些要在ORACLE中运行核心业务提供一种完整的解决方案。PL/SQL具有以下特点
l 是一种高度结构化、易读易理解的语言
l 是一种标准的、可移植的ORACLE语言,实现了编写一次到处运行
l 是一种嵌入式语言,必须在特定的宿主环境内执行,不能编写一个PL/SQL可执行程序
l 是一种高性能的、高集成度的数据库语言,如果你使用VB、DELPHI等进行数据库开发,最终你会发现使用PL/SQL将比其他任何语言都跟轻松高效
二、改进的执行授权和事物一致性
数据库和SQL语言让我们可以紧密地控制对任何个别数据库表的访问和修改,例如可以通过GRANT命令确保只有特定的角色和用户可以对指定的表执行更新操作,另一方面GRANT命令不能确保用户或者应用执行的更新操作能够正确执行,换句话说,数据库不能确保跨越多个表的事物一致性,在业务事务中涉及多个表是很常见的。
PL/SQL实现紧密地控制和管理能力是通过执行授权。我们只是授权执行某个过程,后者控制对底层数据结构的访问。从ORACLE8I开始,PL/SQL提供了AUTHID子句,我们可以使用之前描述的定义者权限模型,也可以使用调用者权限模型。
三、PL/SQL结构:
SQL整合
Declare
I_book_count int;
Begin
Select count(*) into i_book_count from books where author like ‘12’;
Dbms_output.put_line(‘123’);
--改名
Update books set author = ‘31’;
End;
说明:
Declare:声明单元
Begin:执行单元
--:注释
出现错误
PL/SQL提供了强大的错误处理机制
Procedure check_account(account_id_in in accounts.id%type)
Is
I_balance_remaining number;
I_balance_below_minimum exception;
I_account_name account.name%type;
Begin
Select name into i_account_name from accounts where id = account_id_in;
I_balance_remaining := account_balance(account_id_in);
If i_balance_remaining < 1000
Then
Raise i_balance_below_minimum;
End if;
Exception
When no_data_found
Then log_error(….);
When i_balance_below_minumum
Then log_error(….);
Raise;
End;
第二章:语言基础
2.1、PL/SQL块结构
块:最小的有意义的代码单元,一个块就是一组代码,这个块给出了执行边界,也为变量声明和异常处理提供了作用范围,PL/SQL允许我们创建匿名块和命名块,命名块可以使包、过程、函数、触发器或者对象。
一个PL/SQL块最多可以由4个不同的单元组成,只有一个单元是必不可少的
Header
Is
Declaration section
Begin
Execution section
Exception
Exception section
End;
块头:只有命名块才会有这个单元,块头单元确定了这个命名块或者程序的调用方法,这个单元是可选的。
声明单元:这部分定义变量、游标以及可以被后面的执行单元或者异常处理单元引用的子块,这一单元也是可选的。
执行单元:这部分包含PL/SQL引擎运行时要执行的语句,这一单元时必须的。
异常处理单元:这部分处理过程中抛出的异常,这一单元也是可选的。
Procedure get_happy(ename_in in varchar2)
Is
I_hiredate date;
Begin
I_hiredate := sysdate -2;
Insert into employee(emp_name,hiredate) values (ename_in,i_hiredate);
Exception
When dup_val_in_index
Then
Dbms_output.put_line(‘cannot insert.’);
End;
2.2、匿名块
如果这个块没有头部单元,直接以DECLARE或者BEGIN开始,这个块就叫做匿名块。匿名块没有一个可用于引用的句柄,因此不能被其他块调用,相反,匿名块作为执行PL/SQL语句的一个容器,经常会调用过程和函数,因为一个匿名块可以由它自己的声明单元和异常处理单元,开发人员经常会在一个大的程序中借助嵌套匿名块的方式以提供一系列标示和异常处理。
一个匿名块的语法:
[declare ..声明语句…]
Begin ..一个或多个执行语句…
[exception ..异常处理语句…]
End;
一个最小的匿名块
Begin
Dbms_output.put_line(sysdate);
End;
一个添加了声明单元的匿名块
Declare
I_right_now varchar2(9);
Begin
I_right_now := sysdate;
Dbms_output.put_line(i_right_now);
End;
一个添加了异常处理单元的匿名块
Declare
I_right_now varchar2(9);
Begin
I_right_now := sysdate;
Dbms_output.put_line(i_right_now);
Exception
When value_error
Then
Dbms_output.put_line(‘123’);
End;
2.3、命名块
命名块与匿名块的区别在于头部分:
过程的头部分
Procedure [schema.]name[parameter,parameter…..] authid definer|current_user
函数的头部分
Function [schema.]name[parameter,parameter…..]
Return return_datatype
authid definer|current_user
parallel enable
pipelined using |aggregate using
因为ORACLE允许在SQL语句中调用函数,因此函数的头部会比过程的头部有更多的可选成员。
2.4、嵌套块
PL/SQL借鉴了ADA和PASCAL语言,因此块可以嵌套到其他块中。
Procedure calc_totals
Is
Year_total number;
Begin
Year_total := 0;
/*嵌套块开始*/
Declare
Month_total := number;
Begin
Month_total := year_total/12;
End set_month_total;
/*嵌套块结束*/
End;
被嵌套块也可称为闭包块、子块。外层的PL/SQL块也称为包围块、父块。总的来说、嵌套块的好处就是为我们提供了一个可以在代码中控制作用范围和可见性的方法。
Declare
Cursor emp_cur is ……;
Begin
Declare
Total_sales number;
Begin
Declare
I_hiredate date;
Begin
……
End;
End;
End;
2.5、作用范围
在任何一种编程语言中,作用范围的意思都是指确定一个给定标示符所指代的“具体东西”的方法。如果标示符多次出现,语言本身的作用范围规则将用于定义到底是用的哪一个。
在PL/SQL中,变量、异常、模块和一些其他的结构都属于声明他们的块局部的,当这个块执行结束后,我们再也无法引用这些结构了,比如calc_total过程中,我们可以再过程内的任何地方引用外部块的元素,比如year_total变量,然而在内部块声明的元素对于外部块是不可见的。
Package scope_demo
Is
g_global number;
procedure set_global (number_in in number);
end scope_demo;
package body scope_demo
is
procedure set_global (number in number)
is
i_salary number := 10000;
i_count pls_integer;
begin
<<local_block>>
Declare
i_inner number;
begin
select count(*) into i_count
from employees
where department_id = i_inner and salary > i_salary;
end local_block;
g_global := number_in;
end set_global;
end scope_demo;
任何对scope_demo包具有execute授权的模式的任何代码块都可以使用变量scope_demo.g_global。而i_salary这个变量只能在set_global过程内部使用。I_inner这个变量只能被局部或者嵌套块内部使用。
2.6、规范SQL语句中所有对变量和列的引用
Package scope_demo
Is
g_global number;
procedure set_global (number_in in number);
end scope_demo;
package body scope_demo
is
procedure set_global (number in number)
is
i_salary number := 10000;
i_count pls_integer;
begin
<<local_block>>
Declare
i_inner number;
begin
select count(*) into set_global.i_count
from employees
where department_id = local_block.i_inner and salary > set_global.i_salary;
end local_block;
scope.demo.g_global := set_global.number_in;
end set_global;
end scope_demo;
做了这些改动后,对每一个变量或者列的引用都通过表的别名、报名、过程名或者嵌套块的标签名规范化了。这么做的好处在于:
l 改善代码的可读性
l 避免由于变量名和列名一样时可能出现的BUG
l 充分利用ORACLE11G的细粒度依赖这一特性
2.7、可见性
Declare
A date;
B date;
Begin
A := sysdate;
B := add_months(a,6);
End;
因为a,b两个变量的声明是在同一个块中,我们可以非常方便仅通过非规范化的标示符来引用他们,这些标示符也被称为可见标示符,一个可见标示符实际上可能属于以下情况:
l 一个在当前块声明的标示符
l 一个在包含当前块的块中声明的标示符
l 属于你所有的一个单独的数据库对象
l 一个你能看到是数据库别名
l 一个循环体中的索引变量
Package pany_pkg
Is
Last_pany_id number;
……
End pany_pkg;
然后可以再包的外面应用这个变量
If new_pany_id = pany_pkg.last_pany_id then
在默认情况下,给一个包级别的变量所赋的值只会在当前数据库会话的生命周期内有效,不会超过会话生命周期。
Procedure calc_totals
Is
Salary number;
Begin
….
Declare
Salary number;
Begin
Salary := calc_totals.salary;
End;
……..
End;
声明的第一个salary创建了一个作用域时整个过程范围的标示符,在嵌入块中声明了另一个同名的标示符,因此在内部块中引用变量salary时它会优先解释称内部块中声明的变量,因为这个变量是无需任何规范化就是可见的。
Procedure remove_order (order_id in number)
Is
Begin
Delete orders where order_id = order_id;
End;
这段代码的结果是删除order表中一切内容,尽管已经传入可order_id。其原因是:SQL的名称解析规则首先使用的是列名而不是PL/SQL标示符。
因此,在编写代码时应该要有预见性,如果不想通过把每个变量都规范化从而保证其唯一性,那就需要某种小心地命名规范以避免这些冲突。
2.8、PL/SQL字符集
PL/SQL中的特殊符号
符号
说明
;
一个声明和语句的结束
%
属性指示符如%isopen,%rowtype;也用作like通配符
_
Like中的单字符通配符
@
远程位置指示符
:
宿主变量标示符
**
幂运算
<> != ^=
不等于
||
连接符
<<>>
标签符
<= >=
大于等于、小于等于
:=
赋值
=>
位置表示法使用的关联操作符
..
范围操作符
--
单行注释
/**/
多行注释
2.9、标识符
标识符就是一个PL/SQL对象名字,包括:
l 常量或者变量
l 异常
l 游标
l 程序名
l 保留字
l 标签
标识符的默认属性
l 长度最多30个字符
l 必须字母开始
l 可以带有$ _ #符号
l 不能有任何空白字符
l 通常不区分大小写,除非带上双引号
2.10、直接量
所谓直接量就是那些不是通过标识符表现出来的值,它只是一个值。
与标识符不同,字符串直接量是大小写敏感的,如:
If ‘Steven’ = ‘steven’ –结果为false
2.11、null
在ORACLE数据库中,一个值的缺失是通过关键字NULL来标示的,差不多所有的数据类型的PL/SQL变量都可以由NULL状态,唯一例外是关联数组类型。
在ORACLE SQL和PL/SQL中,一个空字符串和零个字符的直接量时没有区别的如
‘’ is null –结果为true
在pl/sql中给一个varchar2(n)变量赋值一个长度为0 的字符串,导致的结果就是null。
但如果在pl/sql中给char(n)变量赋值一个长度为0的字符串,数据库会用空格字符填满这个变量。因而这个变量不为null。
尽管如此,这种行为只在PL/SQL中存在,如果在数据库中,当向CHAR类型列插入长度为零的字符串,数据库不会用空格把内容填满,而是null。这说明ORACEL只是部分采纳了ANSI SQL标准92和99的要求。
2.12、在一个直接量字符串中嵌入单引号
如果想让字符串中带有一个单引号,我们需要使用两个紧挨的单引号
'there''s no body.'
there's no body
'''123'''
''123''
''''
'
''''''
''
从10G开始,数据库引入了用户自定义分隔符
q' (there's no body.) '
there's no body
q' {''123''}'
''123''
q'[']'
'
q'|''|'
''
如例子演示的,我们可以使用简单的分割符如| !,或者使用成对的如{}、[]进行分割。
2.13、pragma关键字
Pragma通常告诉编译器该采取什么行为
如
Autonomous_transaction
告诉PL/SQL引擎,当前块对数据库所做的提交或修改不影响主事务或者外层事务。
Exception_init
告诉编译器一个特殊的错误标示
Declare
No_such_sequence exception;
Pragma exception_init (no_such_sequence,-2289);
Begin
…….
Exception
When no_such_sequence
Then
………………
End;
第三章:PL/SQL程序结构
3.1、条件和顺序控制
3.1.1、IF语句
IF类型
特点
IF THEN END IF;
IF和THEN之间的条件决定THEN和END IF之间的语句是否执行,如果条件部分的求值结果是FALSE或者NULL,这些代码就不会执行
IF THEN ELSE END IF;
实现了2选1逻辑,根据位于IF和THEN两个关键字之间的条件求值结果,或者是THEN ELSE之间的代码被执行,或者是ELSE END IF之间的代码被执行。总之两部分代码肯定有一个被执行
IF THEN ELSIF ELSE END IF;
IF语句会从一系列互斥条件中选择一个TRUE的,然后执行条件关联语句。如果在9I以上,应考虑使用CASE语句
3.1.2、IF THEN组合
对于IF THEN组合,需要理解的是三值逻辑
If salary > 4000
Then
…..
End if;
当salary为NULL时,是不会执行THEN END IF之间的代码的。
用IS NULL或者IS NOT NULL是应对这种问题的好方法,或者使用NVL函数也可以应对。
3.1.3、IF THEN ELSE组合
IF condition
Then
….true…
Else
…false/null….
End if;
通常使用布尔变量要方便一些
Order_exceed_balance := :customer.order_total > max_allowable_order;
If Order_exceed_balance
Then
….
3.1.4、IF THEN ELSIF组合
IF condition-1
Then
Statement-1
Elsif condition-n
Then
Statement-n
[else
Else statements]
End if;
If salary between 1000 and 2000
Then
Give_bonus(employee_id,1500);
Elsif salary between 2000 and 4000
Then
Give_bonus(employee_id,1000);
Elsif salary >4000
Then
Give_bonus(employee_id,500);
Else
Give_bonus(employee_id,0);
End if;
IF-ELSIF的条件部分总是按照从前到后的顺序进行求值,如果2个条件的求值结果都是TRUE,只有第一个条件部分会执行,比如之前的例子,2000的薪水会导致1500的津贴,尽管2000的薪水也满足1000的津贴,尽管在ELSIF总条件重叠是允许的,但是在程序设计上还是应该尽量避免这种情况发生
3.1.5、嵌套IF
If condition1
Then
If condition2
Then
Statement2
Else
If condition3
Then
Statement3
Elsif condition4
Then
Statement4
End if;
End if;
End if;
对于复杂的逻辑来说,嵌套IF是必不可少的,不过使用这种嵌套要小心,它非常难于理解和调试,如果发现嵌套层数超过3层就应该仔细审查逻辑,寻求更简单的方法来完成,如果不能,那么可以考虑创建一个或多个本地模块来隐藏最内层的IF语句。
嵌套IF结构的一个主要好处就是可以延迟内层条件的求值,只有当外层求值结果为TRUE时,它所包含的内部IF语句条件部分才会被求值。
If award_bonus(employee_id) then
If print_chech(employee_id) then
Dbms_output.put_line(‘123’);
End if;
End if;
3.1.6、短路求值
PL/SQL使用短路求值,也就是说PL/SQL不需要对一个IF语句中的所有表达式去求值,如
If condition1 and condition2
Then
……
Else
……
End if;
当第一个条件是false或为NULL,pl/sql就会立即停止对表达式的继续求值,立即跳转到else分支上。
但是考虑以下语句:
My_boolean := condition1 and condition2
如果condition1是null,这个表达式不会出现短路,因为最终结果可能是null或者false,这完全依赖于condition2。而对于if来说,null和false没有区别,因此短路时有意义的。
与AND类似,如果OR操作符的第一个数十TRUE则PL/SQL会立即执行THEN分支,因此也可以发生短路。
因此从优化的角度,如果一个消耗大量CPU和内存资源的条件,此时短路求值非常有用,一定要将这类条件放到整个条件的最后。
If low_cpu_condition and high_cpu_condition
Then
……
End if;
类似的,嵌套IF也可采用短路求值,最外层的条件应为low_cpu_condition。
3.2、case语句和表达式
简单CASE语句:通过值来关联一个或者多个PL/SQL语句,根据表达式的返回值来选择哪一个被执行。
搜索性CASE语句:根据一系列布尔条件来确定要执行的PL/SQL语句系列,那些和第一个求值结果是TRUE的条件相管理的语句会被执行。
3.2.1、简单CASE语句
Case employee_type
When ‘S’ then
Dbms_putput.put_line(‘S’);
When ‘H’ then
Dbms_putput.put_line(‘H’);
Else
Raise invalid_employee_type;
End case;
这个CASE语句明确使用了ELSE子句,不过该子句是可选的,如果没有明确使用,PL/SQL隐含使用:
Else
Raise case_not_found;
这个行为处理和IF是完全不同的,对于CASE来说,如果没有满足WHEN的情形,CASE将导致错误
对于发放津贴的例子,使用CASE子句也可以完成,一个开放性的做法如下:
Case true
When salary >=1000 and salary <= 2000
Then
……..
When salary >2000 and salary <= 4000
Then
……..
Else
……..
End case;
虽然case true方案看起来是聪明的技巧,其实它是搜索型case语句的实现方法
3.2.2、搜索型CASE
Case
When expression1 then
Statement1
When expression2 then
Statement2
Else
…..
End case;
与简单case一样,下面规则也同样适用
l 一旦某些语句被执行,整个执行也就结束了,即使多个表达式为TRUE,也只有和第一个表达式相关联的语句被执行
l ELSE是可选的,如果没有ELSE且没有一个为TRUE,则跑出CASE_NOT_FOUND异常
l WHEN表达式从上到下依次求值
既然WHEN语句是按照顺序求值,通过把最可能的WHEN放在代码最前面,将提升性能,而且如果WHEN本身消耗极大CPU,也可采用短路求值方法获得性能上的益处。
同样,CASE语句中可以使用任何语句,所以可以吧内层CASE语句用IF替换,在CASE语句内也可以嵌套任何语句
Case
When …. Then
Case
When …then
Else
……
End case;
When …. Then
…..
End case;
3.2.3、case表达式
Select case when a = 0 then 0
When a=1 then 1
Else 2
End ;
From tab_a;
与case语句不同的是,即使case表达式中没有when子句被选择也不会跑出异常,相反,如果没有when被满足,case表达式返回NULL。
3.2.4、GOTO语句
GOTO语句可以无条件跳转到PL/SQL块中其他可执行语句处,与其他语法结构一样,只要适当、仔细使用GOTO,程序就会变得很强大
GOTO的常见格式为:
GOTO label_name
其中LABEL_NAME是代表目标语句的标签名称
begin
goto secont_output;
dbms_output.put_line(‘1’);
<<second_output>>
Dbms_output.put_line(‘2’);
End;
GOTO语句有些限制:
一个标签后面至少跟着一个可执行语句
GOTO语句的目标标签必须和GOTO语句在一个作用域内
GOTO语句的目标标签必须和GOTO语句在PL/SQL代码块的相同部分
3.2.5、NULL
有时我们可能不希望PL/SQL做任何事情,此时会用到NULL
3.2.5.1、增加程序可读性
If :report_mgr.selection = ‘detail’
Then
Exec_detail_report;
End if;
如果用户没有选择detail,那么程序什么也不做,如果你在代码中增加了:
If :report_mgr.selection = ‘detail’
Then
Exec_detail_report;
Else
Null;
End if;
此时你就明确强调了不用担心,你已经考虑到这种情况。
3.2.5.2、在标签之后使用NULL
有时NULL和标签配对使用,如果数据状态表明无需额外的处理则代码快速跳转到程序结尾。
Procedure process_date(data_in orders%rowtype,data_action in varchar2)
Is
Status integer;
Begin
If data_in.ship_data is not null
Then
Status := validate_shipdate(data_in.ship_date);
If status != 0 then goto end_of_proc; end if;
End if;
……………..
<<end_of_proc>>
Null;
End;
3.3、循环迭代处理
循环结构有以下三种:
l 简单循环或者无限循环
l For循环
l While循环
简单循环:仅以LOOP关键字开始,以END LOOP结束,靠循环体内的EXIT和EXIT WHEN或者RETURN来推出循环。
Loop
Exit when n>2;
Dbms_output.put_line(n);
End loop;
For循环:分数值循环和游标循环
For n in 1..3 loop
…….
End loop;
For n in (select employee_id from emp) loop
……
End loop;
While循环:和简单循环类似,区别在于可能循环一次都不执行
While n >2
Loop
……
End loop;
3.3.1、人为地无限循环
有些程序如监控程序,需要永远执行下去,循环将消耗大量CPU,此时需要避免CPU耗尽,解决办法是在循环中加入适当暂停。
Loop
Data_gather_procedure;
Dbms_lock.sleep(10);
End loop;
3.3.2、结束一个人为地无限循环
Declare
Pipename constant varchar2(12) := ‘singnaler’;
Result integer;
Pipebuf varchar2(64);
Begin
----创建一个指定名字的私有管道
Result := dbms_pipe.create_pipe(pipename);
Loop
Data_gather_procedure;
Dbms_lock.sleep(10);
----检查管道是有有消息
If dbms_pipe.receive_message(pipename,0) = 0
Then
----解释消息表采取的动作
Dbms_pipe.unpack_message(pipebuf);
Exit when pipebuf = ‘stop’;
End if;
End loop;
End ;
构造一个伙伴程序,这个程序只需要在管道中发送一个STOP就可以终止循环
Declare
Pipename varchar2(12) := ‘signaler’;
Result integer := dbms_pipe.create_pipe(pipename);
Begin
Dbms_pipe.pack_message(‘stop’);
End;
3.3.3、出理非平滑增长
PL/SQL中没有提供可以指定特殊的步幅,递进的步幅总是以1为单位。
For loop_index in 1 .. 100
Loop
If mod (loop_index,2) = 0
Then
Cale_values(loop_index);
End if;
End loop;
然而使用简单的方法可以将循环次数减半
For even_number in 1 .. 50
Loop
Calc_values(even_number*2);
End loop;
3.3.4、游标型FOR循环
Declare
Cursor cur_a is select id,name from emp;
Rec_a cur_a%rowtype;
Begin
Open cur_a;
Loop
Fetch cur_a into rec_a;
Exit when cur_a%notfound;
……..
End loop;
Close cur_a;
End;
以上代码用游标型FOR循环来改写:
Declare
Cursor cur_a is select id,name from emp;
Begin
For rec_a in cur_a loop
……
End loop;
End;
3.3.5、continue语句
11G提供了continue语句,用于从本次迭代循环中退出
Begin
For I in 1 .. 10 loop
Continue when mod(1,2)=0;
Dbms_output.put_line(i);
End loop;
End;
当然使用IF也可以达到CONTINUE子句的效果,但是CONTINUE子句更加优雅,代码量更小。正确使用CONTINUE子句可以使嵌套层数大大减少。
3.3.6、迭代处理技巧
循环索引使用易于理解的名字
一个程序只应有一个入口和一个出口
在FOR和WHILE循环中不要使用EXIT,EXIT WHEN。
在循环中不要使用RETURN或者GOTO,这样会导致非结构化的循环结束,且随着代码量增大将难于理解和维护。
3.3.7、循环计数
Declare
I pls_integer := 0;
Begin
For i_rec in cur
Loop
……
I := i_rec%rowcount;
End loop;
If I > 10 then
……
3.3.8、有时100%的SQL有缺点,因为SQL语句通常是全是或无的逻辑。采用游标型FOR循环可以提供更多的灵活性
Begin
For i_rec in cur
Loop
Begin
Insert …
Delete…
Exception
When others then
Log_error(xxx);
End;
End loop;
End;
第四章:异常处理
术语:
系统异常:由ORACLE定义的,在PL/SQL运行时引擎发现的错误后抛出的异常。
程序员定义的异常:用EXCEPTION_INIT指定给ORACLE指定的错误名称
异常处理单元:PL/SQL块中的可选部分,包含一个或多个异常处理句柄
抛出:通过通知运行时引擎出现错误来终止当前块的运行,数据库本身可以抛出异常,也可以通过RAISE和RAISE_APPLICATION来抛出。
处理:异常处理单元捕获错误,在处理句柄中编写代码来处理错误,向用户显示消息或者进行错误传播。
作用范围:可
展开阅读全文