关系数据库高级技术

《关系数据库高级技术》由会员分享，可在线阅读，更多相关《关系数据库高级技术（33页珍藏版）》请在金锄头文库上搜索。

1、关系数据库高级技术关系数据库高级技术 存储过程和触发器的定义和使用 2007 8 161清华大学知识工程研究室 内容 1 PL SQL简单介绍 2 定义存储过程 3 使用存储过程 4 定义触发器 5 使用触发器 6 总结 2007 8 162清华大学知识工程研究室 2007 8 16清华大学知识工程研究室3 1 PL SQL简单介绍简单介绍 1 1 PL SQL简单介绍 1 PL SQL是一种高性能的基于事务处理的语言 能运行在 任何Oracle环境中 支持所有数据处理命令 通过使用 PL SQL程序单元处理SQL的数据定义和数据控制元素 2 PL SQL支持所有SQL数据类型和所有SQL函数

2、 同时支 持所有Oracle对象类型 3 PL SQL块可以被命名和存储在Oracle服务器中 同时也 能被其他的PL SQL程序或SQL命令调用 任何客户 服务器工 具都能访问PL SQL程序 具有很好的可重用性 4 可以使用Oracle数据工具管理存储在服务器中的PL SQL 程序的安全性 可以授权或撤销数据库其他用户访问PL SQL 程序的能力 5 PL SQL代码可以使用任何ASCII文本编辑器编写 所以 对任何Oracle能够运行的操作系统都是非常便利的 2007 8 164清华大学知识工程研究室 1 2 PL SQL与简单SQL的比较 对于SQL Oracle必须在同一时间处理每

3、一条SQL语句 在网络环境下这就意味 作每一个独立的调用都必须被oracle服务 器处理 这就占用大量的服务器时间 同时导致网络拥挤 而PL SQL是以整个 语句块发给服务器 这就降低了网络拥 挤 2007 8 165清华大学知识工程研究室 1 3 PL SQL语法 块结构语言 一个PL SQL 程序包含了一个或多个逻辑块 逻辑块的构成 声明部分 Declaration section 关键字DECLARE 执行部分 Executable section 关键字BEGIN 异常处理部分 Exception section 关键字EXCEPTION 2007 8 166清华大学知识工程研究室 1

4、 3 PL SQL语法 续 PL SQL块语法结构如下 DECLARE declaration statements BEGIN executable statements EXCEPTION exception statements END 2007 8 167清华大学知识工程研究室 1 4 PL SQL的限制 I PL SQL块不能在屏幕上显示SELECT语 句的输出 II SELECT语句必须包括一个INTO子串或 者是游标的一部分 III 执行部分使用的变量和常量必须首先在 声明部分声明 IV 执行部分必须至少包括一条可执行语句 V 数据定义语言 Data Definition lan

5、guage 不能在执行部分中使用 2007 8 16清华大学知识工程研究室8 2007 8 16清华大学知识工程研究室9 2 定义存储过程定义存储过程 2 1 创建存储过程 创建存储过程的语句如下 CREATE OR REPLACE PROCEDURE 过程名 参数1 方式l 数据类型1 参数2 方式2 数据类型2 IS AS is或as完全等价 BEGIN PL SQL过程体 END 过程名 2007 8 16清华大学知识工程研究室10 2 2 过程参数的类型 1 in参数类型 表示输入给过程的参数 2 out参数类型 表示参数在过程中将 被赋值 可以传给过程体的外部 3 in out参数类

6、型 表示该类参数既可以 向过程体传值 也可以在过程体中赋值 以便向过程体外传值 2007 8 16清华大学知识工程研究室11 2 3 存储过程实例 存储过程 显示所指定雇员名所在的部门名和位置 CREATE OR REPLACE PROCEDURE DeptMesg penameemp ename TYPE pdnameOUT dept dname TYPE plocOUT dept loc TYPE AS BEGIN SELECT dname loc INTO pdname ploc FROM emp dept WHERE emp deptno dept deptno AND emp ena

7、me pename END 2007 8 16清华大学知识工程研究室12 2007 8 16清华大学知识工程研究室13 3 使用存储过程使用存储过程 3 1 在PL SQL中调用 VARIABLE vdname VARCHAR2 14 VARIABLE vloc VARCHAR2 13 EXECUTE DeptMesg SMITH vdname vloc PRINT vdname vloc 2007 8 16清华大学知识工程研究室14 3 2 在Java程序中调用 使用JDBC的CallableStatement类型 1 创建 CallableStatement对象 2 设置IN和OUT参数

8、3 设置INOUT参数 4 先检索结果 再检索 OUT 参数 5 检索作为OUT参数的NULL值 2007 8 16清华大学知识工程研究室15 3 2 1 创建CallableStatement对象 CallableStatement 对象是用 Connection 方 法 prepareCall 创建的 CallableStatementcstmt con prepareCall callgetTestData 2007 8 16清华大学知识工程研究室16 3 2 2 设置IN和OUT参数 设置IN参数 通过 setXXX 方法完成 设置OUT参数 通过 registerOutParamet

9、er 方法完成 CallableStatementcstmt con prepareCall callgetTestData cstmt registerOutParameter 1 java sql Types TINYINT cstmt registerOutParameter 2 java sql Types DECIMAL 3 cstmt executeQuery bytex cstmt getByte 1 java math BigDecimaln cstmt getBigDecimal 2 3 2007 8 16清华大学知识工程研究室17 3 2 3设置INOUT参数 Callab

10、leStatementcstmt con prepareCall callreviseTotal cstmt setByte 1 25 cstmt registerOutParameter 1 java sql Types TINYINT cstmt executeUpdate bytex cstmt getByte 1 2007 8 16清华大学知识工程研究室18 3 2 4先检索结果 再检索OUT参数 建议先检索由执行CallableStatement 对象 所产生的结果 然后再 用 CallableStatement getXXX 方法来检 索 OUT 参数 2007 8 16清华大学知

11、识工程研究室19 3 2 5检索作为OUT参数的NULL值 返回到 OUT 参数中的值可能会是 JDBC NULL 0或false是否源于JDBC NULL 用方法wasNull进行检测 getXXX 方法读取的最后一个值是 JDBC NULL 则该方法返回 true 否则返回 flase 2007 8 16清华大学知识工程研究室20 3 2 6 复杂返回值 存储过程允许返回ResultSet等复杂类型 create procedure list early deaths return refcursor as declare toesup refcursor begin open toesu

12、p for select poets name deaths age from poets deaths all entries in deaths are for poets but the table might become generic where poets id deaths mort id and deaths age 60 return toesup end 2007 8 16清华大学知识工程研究室21 3 2 6 复杂返回值 续 Java代码 static void sendearlydeaths printwriter out connection con null ca

13、llablestatement toesup null try con connectionpool getconnection postgresql needs a transaction to do this con setautocommit false setup the call callablestatement toesup connection preparecall call list early deaths toesup registeroutparameter 1 types other toesup execute resultset rs resultset toe

14、sup getobject 1 while rs next string name rs getstring 1 int age rs getint 2 out println name was age years old rs close catch sqlexception e we should protect these calls toesup close con close 2007 8 16清华大学知识工程研究室22 2007 8 16清华大学知识工程研究室23 4 定义触发器定义触发器 4 1 触发器的设计规则 作用范围清晰 不要让触发器去完成Oracle后台已经能够 完成的功

15、能 限制触发器代码的行数 不要创建递归的触发器 触发器仅在被触发语句触发时进行集中的 全局的操作 同用户和数据库应用无关 2007 8 16清华大学知识工程研究室24 4 2 触发器类型 可以创建被如下语句所触发的触发器 1 DML语句 DELETE INSERT UPDATE 2 DDL语句 CREATE ALTER DROP 3 数据库操作 SERVERERROR LOGON LOGOFF STARTUP SHUTDOWN 2007 8 16清华大学知识工程研究室25 4 3 1 定义触发器的语法 CREATE OR REPLACE TRIGGER name BEFORE AFTER ev

16、ent OR ON table FOR EACH ROW STATEMENT WHEN condition plsql block call procedures 2007 8 16清华大学知识工程研究室26 4 3 2 注意事项 1 触发器可以声明为在对记录进行操作之前 在之前 检查约 束之前和 INSERT UPDATE 或 DELETE 执行前 或之后 在检查约 束之后和完成INSERT UPDATE或DELETE操作 触发 2 一个 FOR EACH ROW 执行指定操作的触发器为操作修改的 每一行都调用一次 3 SELECT 并不更改任何行 因此不能创建 SELECT 触发器 这 种场合下规则和视图更适合 4 触发器和某一指定的表格有关 当该表格备删除时 任何 与该表有关的触发器同样会被删除 5 在一个表上的每一个动作只能有一个触发器与之关联 6 在一个单独的表上 最多只能创建三个触发器与之关联 一个INSERT触发器 一个DELETE触发器和一个UPDATE触发器 2007 8 16清华大学知识工程研究室27 4 4 修改和删除触发器 DROP TRIGGER name O