PLSQL资料-存储过程1
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
19
触发器类型
触发事件决定了触发器的类型
语句:INSERT,UPDATE,DELETE 语句 定时:BEFORE,AFTER 定时 级别: 级别 行级触发器(FOR EACH ROW), 行级触发器 对于有触发语句影响每一行记录
20
触发器主体可以访问的表
变化表:被 语句正在修改的表,义既定义触 变化表 被DML语句正在修改的表 义既定义触 语句正在修改的表 发器的表 限制表:有些表在创建的时候就带有参考完整 限制表 有些表在创建的时候就带有参考完整 性限制的声明主建与外建 触发器主体中的限制 不可以读取或修改任何变化表
PL/SQL块
18
触发器
before关键字后出现的的触发事件,指的是运行 的时机,在触发事件之前运行还是之后运行 on后接表名,表明该触发器会在哪一个表上做 监听,一旦有对该表对应的事件发生则会触发触 发器的执行 for each row表示该条dml操作影响了几条数据, 触发器就被调用几次,是行级触发器.不加则是 语句级触发器
23
Sequence和Trigger的运用 Sequence和Trigger的运用
三、创建一个触发器
create or replace trigger my_trigger before insert on test for each row begin select emp_sequence.nextval into :new.id from dual; end; / 四、insert操作测试 操作测试
创建存储过程( 创建存储过程(Create Procedure)
Create or Replace Procedure
IS AS IN 过程名(变量 OUT 数据类型) IN OUT
变量定义； Begin 过 … 程 体 End 过程名；
缺省
可以省略
3
创建函数( 创建函数(Create Function)
Create or Replace Function 函数名(变量 Return 数据类型 变量定义； Begin … Return 值； End 函数名；
4
IS AS
IN OUT IN OUT
数据类型) 缺省
可以省略
Package 包的设计
5
包的定义
包是由包规范和包主体组成的. 包是由包规范和包主体组成的 包是可以将相关对象存储在一起的pl/sql结构 结构 包是可以将相关对象存储在一起的 包只能存储在数据库中,不能是本地的 包只能存储在数据库中 不能是本地的. 不能是本地的 包是一个带有名字的声明,可以通过包来避免函数或者存储过程重 包是一个带有名字的声明 可以通过包来避免函数或者存储过程重 名. 相当于一个pl/sql块的声明部分 块的声明部分. 相当于一个 块的声明部分 在块的声明部分出现的任何东西都能出现在包中 包可以包含过程,函数 游标与标量 包规范我们可以看作是java中 包可以包含过程 函数,游标与标量 包规范我们可以看作是 函数 游标与标量.包规范我们可以看作是 中 的接口,而包主题就像是这个接口的实现类 而包主题就像是这个接口的实现类. 的接口 而包主题就像是这个接口的实现类 可以从其他pl/sql块中引用包 包提供了可用于 块中引用包,包提供了可用于 的全局变量. 可以从其他 块中引用包 包提供了可用于pl/sql的全局变量 6 的全局变量
1、执行速度比普通的SQL语句快 执行速度比普通的SQL语句快 再运行存储过程前，数据库已对其进行了语法和句法分析， 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执 行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL 行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL 语句的性能 语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 2、便于集中控制 当企业规则变化时，只需要在数据库的服务器中修改相应的存储过程， 当企业规则变化时，只需要在数据库的服务器中修改相应的存储过程，而不 需要逐个的在应用程序中修改，应用程序保持不变即可， 需要逐个的在应用程序中修改，应用程序保持不变即可，这样就省去了修改应 用程序工作量。 用程序工作量。 3、可以降低网络的通信量 4、保证数据库的安全性和完整性 通过存储过程不仅可以使没有权限的用户在控制之下间接地存取数据库，保 通过存储过程不仅可以使没有权限的用户在控制之下间接地存取数据库， 证数据的安全；而且可以使相关的动作在一起发生， 证数据的安全；而且可以使相关的动作在一起发生，从而可以维护数据库的完 整性。 整性。 5、灵活性 存储过程可以用流控制语句编写，具有很强的灵活性， 存储过程可以用流控制语句编写，具有很强的灵活性，可以完成复杂的判断 和运算，可以根据条件执行不通SQL语句 语句。 和运算，可以根据条件执行不通SQL语句。 2
21
不可以读取或修改限制表的主建,唯一值列 外 不可以读取或修改限制表的主建 唯一值列,外 唯一值列
创建行级触发器：
当触发器已经存在时，使用Replace选项。 Update中的of是可选项，用于指定Update语句要 修改的列。 根据进行一个操作时触发器的触发次数决定是用 语句级、还是行级触发器。 当某一操作结果只影响一行时，语句级触发器与 行触发器效果相同。当操作影响到数据库多行数 据时，语句级触发器只触发一次，而行级触发器 触发多次。
22
Sequence和Trigger的运用 Sequence和Trigger的运用
一、创建一个Sequence 创建一个
CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; 二、创建一张测试表 create table test(id number primary key,name varchar2(20));
16
创建语句级触发器
SQL> Create or Replace Trigger 触发器名 Before Insert After Delete On 表名 Update {of} Declare ……. Begin …… End；
PL/SQL块
17
创建行级触发器
SQL> Create or Replace Trigger 触发器名 Before Insert After Delete On 表名 Update {of} FOR EACH ROW Declare ……. Begin …… End；
《Oracle PL/SQL程序设计》 PL/SQL程序设计》
存储过程、包、数据库触发器设计
(Oracle Procedural Option) Option)
1
存储过程
存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL 存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL 语句；可供应用程序直接调用。 语句；可供应用程序直接调用。
数据库触发器与存储过程比较：
数据库触发器是在进行数据操纵时自动触发的， 存储过程要通过程序调用。 在数据库触发器中可以调用存储过程、函数。 在触发器中禁止使用COMMIT 、ROLLBACK语句， 存储过程中可以使用PL/SQL中可以使用的全部SQL 语句。 在触发器中不得间接调用含有COMMIT、 ROLLBACK语句的存储过程。
24
包的初始化
包存放在数据库中 在第一次被调用的时候,包从数据库中调入内存中并被初始化 在第一次被调用的时候 包从数据库中调入内存中并被初始化 包中定义的所有的变量都被分配内存 每个会话都将拥有自己的包内变量的副本 可以指定包的初始化代码. 可以指定包的初始化代码
7
创建包( 创建包(Create Package)
Create or Replace Package 包名 定 义 公 共 元 素
IS AS
Procedure 过程名()； Function 函数名() Return 数据类型； 变量定义； 例外定义； 光标定义； ……； End 包名；
8
创建包体( 创建包体(Create Package Body)
Create or Replace Package Body 包名 Procedure Procedure Function Function ……； End 包名； 过程定义； 过程定义； 函数定义； 函数定义；
IS AS
9
创建包
10
创建包体
1
----定义过程
11
创建包体
2
3
----定义过程
12
包的调用
13
Oracle Database Trigger 数据库触发器设计
14
wenku.baidu.com
数据库触发器作用
防止非法的数据库操纵、维护数据库安全 对数据库的操作进行审计，存储历史数据 完成数据库初始化处理 控制数据库的数据完整性 进行相关数据的修改 完成数据复制 自动完成数据库统计计算 限制数据库操作的时间、权限等，控制实体的 安全性。 15
触发器类型
触发事件决定了触发器的类型
语句:INSERT,UPDATE,DELETE 语句 定时:BEFORE,AFTER 定时 级别: 级别 行级触发器(FOR EACH ROW), 行级触发器 对于有触发语句影响每一行记录
20
触发器主体可以访问的表
变化表:被 语句正在修改的表,义既定义触 变化表 被DML语句正在修改的表 义既定义触 语句正在修改的表 发器的表 限制表:有些表在创建的时候就带有参考完整 限制表 有些表在创建的时候就带有参考完整 性限制的声明主建与外建 触发器主体中的限制 不可以读取或修改任何变化表
PL/SQL块
18
触发器
before关键字后出现的的触发事件,指的是运行 的时机,在触发事件之前运行还是之后运行 on后接表名,表明该触发器会在哪一个表上做 监听,一旦有对该表对应的事件发生则会触发触 发器的执行 for each row表示该条dml操作影响了几条数据, 触发器就被调用几次,是行级触发器.不加则是 语句级触发器
23
Sequence和Trigger的运用 Sequence和Trigger的运用
三、创建一个触发器
create or replace trigger my_trigger before insert on test for each row begin select emp_sequence.nextval into :new.id from dual; end; / 四、insert操作测试 操作测试
创建存储过程( 创建存储过程(Create Procedure)
Create or Replace Procedure
IS AS IN 过程名(变量 OUT 数据类型) IN OUT
变量定义； Begin 过 … 程 体 End 过程名；
缺省
可以省略
3
创建函数( 创建函数(Create Function)
Create or Replace Function 函数名(变量 Return 数据类型 变量定义； Begin … Return 值； End 函数名；
4
IS AS
IN OUT IN OUT
数据类型) 缺省
可以省略
Package 包的设计
5
包的定义
包是由包规范和包主体组成的. 包是由包规范和包主体组成的 包是可以将相关对象存储在一起的pl/sql结构 结构 包是可以将相关对象存储在一起的 包只能存储在数据库中,不能是本地的 包只能存储在数据库中 不能是本地的. 不能是本地的 包是一个带有名字的声明,可以通过包来避免函数或者存储过程重 包是一个带有名字的声明 可以通过包来避免函数或者存储过程重 名. 相当于一个pl/sql块的声明部分 块的声明部分. 相当于一个 块的声明部分 在块的声明部分出现的任何东西都能出现在包中 包可以包含过程,函数 游标与标量 包规范我们可以看作是java中 包可以包含过程 函数,游标与标量 包规范我们可以看作是 函数 游标与标量.包规范我们可以看作是 中 的接口,而包主题就像是这个接口的实现类 而包主题就像是这个接口的实现类. 的接口 而包主题就像是这个接口的实现类 可以从其他pl/sql块中引用包 包提供了可用于 块中引用包,包提供了可用于 的全局变量. 可以从其他 块中引用包 包提供了可用于pl/sql的全局变量 6 的全局变量
1、执行速度比普通的SQL语句快 执行速度比普通的SQL语句快 再运行存储过程前，数据库已对其进行了语法和句法分析， 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执 行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL 行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL 语句的性能 语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 2、便于集中控制 当企业规则变化时，只需要在数据库的服务器中修改相应的存储过程， 当企业规则变化时，只需要在数据库的服务器中修改相应的存储过程，而不 需要逐个的在应用程序中修改，应用程序保持不变即可， 需要逐个的在应用程序中修改，应用程序保持不变即可，这样就省去了修改应 用程序工作量。 用程序工作量。 3、可以降低网络的通信量 4、保证数据库的安全性和完整性 通过存储过程不仅可以使没有权限的用户在控制之下间接地存取数据库，保 通过存储过程不仅可以使没有权限的用户在控制之下间接地存取数据库， 证数据的安全；而且可以使相关的动作在一起发生， 证数据的安全；而且可以使相关的动作在一起发生，从而可以维护数据库的完 整性。 整性。 5、灵活性 存储过程可以用流控制语句编写，具有很强的灵活性， 存储过程可以用流控制语句编写，具有很强的灵活性，可以完成复杂的判断 和运算，可以根据条件执行不通SQL语句 语句。 和运算，可以根据条件执行不通SQL语句。 2
21
不可以读取或修改限制表的主建,唯一值列 外 不可以读取或修改限制表的主建 唯一值列,外 唯一值列
创建行级触发器：
当触发器已经存在时，使用Replace选项。 Update中的of是可选项，用于指定Update语句要 修改的列。 根据进行一个操作时触发器的触发次数决定是用 语句级、还是行级触发器。 当某一操作结果只影响一行时，语句级触发器与 行触发器效果相同。当操作影响到数据库多行数 据时，语句级触发器只触发一次，而行级触发器 触发多次。
22
Sequence和Trigger的运用 Sequence和Trigger的运用
一、创建一个Sequence 创建一个
CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; 二、创建一张测试表 create table test(id number primary key,name varchar2(20));
16
创建语句级触发器
SQL> Create or Replace Trigger 触发器名 Before Insert After Delete On 表名 Update {of} Declare ……. Begin …… End；
PL/SQL块
17
创建行级触发器
SQL> Create or Replace Trigger 触发器名 Before Insert After Delete On 表名 Update {of} FOR EACH ROW Declare ……. Begin …… End；
《Oracle PL/SQL程序设计》 PL/SQL程序设计》
存储过程、包、数据库触发器设计
(Oracle Procedural Option) Option)
1
存储过程
存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL 存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL 语句；可供应用程序直接调用。 语句；可供应用程序直接调用。
数据库触发器与存储过程比较：
数据库触发器是在进行数据操纵时自动触发的， 存储过程要通过程序调用。 在数据库触发器中可以调用存储过程、函数。 在触发器中禁止使用COMMIT 、ROLLBACK语句， 存储过程中可以使用PL/SQL中可以使用的全部SQL 语句。 在触发器中不得间接调用含有COMMIT、 ROLLBACK语句的存储过程。
24
包的初始化
包存放在数据库中 在第一次被调用的时候,包从数据库中调入内存中并被初始化 在第一次被调用的时候 包从数据库中调入内存中并被初始化 包中定义的所有的变量都被分配内存 每个会话都将拥有自己的包内变量的副本 可以指定包的初始化代码. 可以指定包的初始化代码
7
创建包( 创建包(Create Package)
Create or Replace Package 包名 定 义 公 共 元 素
IS AS
Procedure 过程名()； Function 函数名() Return 数据类型； 变量定义； 例外定义； 光标定义； ……； End 包名；
8
创建包体( 创建包体(Create Package Body)
Create or Replace Package Body 包名 Procedure Procedure Function Function ……； End 包名； 过程定义； 过程定义； 函数定义； 函数定义；
IS AS
9
创建包
10
创建包体
1
----定义过程
11
创建包体
2
3
----定义过程
12
包的调用
13
Oracle Database Trigger 数据库触发器设计
14
wenku.baidu.com
数据库触发器作用
防止非法的数据库操纵、维护数据库安全 对数据库的操作进行审计，存储历史数据 完成数据库初始化处理 控制数据库的数据完整性 进行相关数据的修改 完成数据复制 自动完成数据库统计计算 限制数据库操作的时间、权限等，控制实体的 安全性。 15