(一)
PL/SQL 是oracle在标准SQL语言上的过程性扩展,它不仅允许嵌入SQL语句,而且允许定义变量和常量,允许过程语言结构(条件语句和循环语句),允许使用 异常处理oracle错误等。通过使用PL/SQL,可以在一个PL/SQL块中包含多条SQL语句和PL/SQL语句。PL/SQL具有以下优点:
1。提高应用系统的运行性能。
通过使用PL/SQL块包含多条SQL语句,从而减少了SQL语句的发送次数,降低了网络开销,提高了应用程序性能
2。提供模块化的程序设计功能。
将企业规则和商业逻辑集成到PL/SQL子程序当中,提供给客户端调用,简化了客户端应用程序的开发和维护工作,降低了耦合度
3。允许定义标识符,允许定义变量、常量、游标和异常等标识符
4。具有过程语言控制结构,允许使用条件分支语句和循环语句
5。具有良好的兼容性,在允许运行oracle的任何平台上执行,不仅在数据库服务端执行,也可以在oracle提供的工具中开发PL/SQL
6。处理运行错误,使用PL/SQL提供的EXCEPTION,开发人员可以集中处理各种oracle错误和PL/SQL错误,简化了错误处理。
(二)
PL/SQL块的概念:
Block是PL/SQL的基本单元,编写PL/SQL本质上就是编写PL/SQL块,块的嵌套层次没有限制。
1。块的结构:块由3部分组成
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/ContractedBlock.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
其中只有执行部分是必须的!
2。分类:
1)匿名块,匿名块是指没有名称的PL/SQL块,可以内嵌到应用程序中,如:
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
2)命名块:与匿名块相似,使用命名块主要是为了区分多级嵌套关系,如:
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
3)子程序,又可以分成3种:过程、函数和包
A。过程用于执行特定操作,当建立过程时既可以指定输入参数,也可以指定输出参数,从而在应用环境和程序间传递数据,使用CREATE PROCEDURE语句,如:
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
B)函数,与过程主要不同是函数需要返回数据,在函数头和函数体都必须有RETURN语句,如:
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
![](http://dennis-zane.iteye.com/Images/OutliningIndicators/None.gif)
C)包,类似JAVA包的概念,包含两部分,包规范和包体,包规范相当于C++中的头文件声明子程序,而包体包含这些子程序的具体实现。调用包中的子程序时,需要 ‘包名.子程序名’ 这样的形式调用
4。触发器是指隐含执行的存储过程,定义触发器需要指定触发事件和触发操作,常见触发事件如insert,update,delete等,使用CREATE TRIGGER命令建立