关系数据库高级技术

关系数据库高级技术存储过程和触发器的定义和使用2007/8/161清华大学知识工程研究室内容1.PL/SQL简单介绍2.定义存储过程3.使用存储过程4.定义触发器5.使用触发器6.总结2007/8/162清华大学知识工程研究室2007/8/16清华大学知识工程研究室31.PL/SQL简单介绍1.1PL/SQL简单介绍（1）PL/SQL是一种高性能的基于事务处理的语言，能运行在任何Oracle环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。（2）PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有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.2PL/SQL与简单SQL的比较对于SQL，Oracle必须在同一时间处理每一条SQL语句，在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。2007/8/165清华大学知识工程研究室1.3PL/SQL语法块结构语言一个PL/SQL程序包含了一个或多个逻辑块逻辑块的构成声明部分（Declarationsection）关键字DECLARE执行部分（Executablesection）关键字BEGIN异常处理部分（Exceptionsection）关键字EXCEPTION2007/8/166清华大学知识工程研究室1.3PL/SQL语法（续）PL/SQL块语法结构如下：[DECLARE]declarationstatementsBEGINexecutablestatements[EXCEPTION]exceptionstatementsEND2007/8/167清华大学知识工程研究室1.4PL/SQL的限制I.PL/SQL块不能在屏幕上显示SELECT语句的输出II.SELECT语句必须包括一个INTO子串或者是游标的一部分III.执行部分使用的变量和常量必须首先在声明部分声明IV.执行部分必须至少包括一条可执行语句V.数据定义语言(DataDefinitionlanguage)不能在执行部分中使用2007/8/16清华大学知识工程研究室82007/8/16清华大学知识工程研究室92.定义存储过程2.1创建存储过程创建存储过程的语句如下：CREATE[ORREPLACE]PROCEDURE＜过程名＞(＜参数1，[方式l]＜数据类型1，＜参数2，[方式2]＜数据类型2，……)IS|AS--is或as完全等价BEGINPL/SQL过程体END＜过程名＞2007/8/16清华大学知识工程研究室102.2过程参数的类型（1）in参数类型：表示输入给过程的参数。（2）out参数类型：表示参数在过程中将被赋值，可以传给过程体的外部。（3）inout参数类型：表示该类参数既可以向过程体传值，也可以在过程体中赋值，以便向过程体外传值。2007/8/16清华大学知识工程研究室112.3存储过程实例存储过程，显示所指定雇员名所在的部门名和位置CREATEORREPLACEPROCEDUREDeptMesg(penameemp.ename%TYPE,pdnameOUTdept.dname%TYPE,plocOUTdept.loc%TYPE)ASBEGINSELECTdname,locINTOpdname,plocFROMemp,deptWHEREemp.deptno=dept.deptnoANDemp.ename=pename;END;2007/8/16清华大学知识工程研究室122007/8/16清华大学知识工程研究室133.使用存储过程3.1在PL/SQL中调用VARIABLEvdnameVARCHAR2(14);VARIABLEvlocVARCHAR2(13);EXECUTEDeptMesg('SMITH',:vdname，:vloc);PRINTvdnamevloc;2007/8/16清华大学知识工程研究室143.2在Java程序中调用使用JDBC的CallableStatement类型1.创建CallableStatement对象2.设置IN和OUT参数3.设置INOUT参数4.先检索结果，再检索OUT参数5.检索作为OUT参数的NULL值2007/8/16清华大学知识工程研究室153.2.1创建CallableStatement对象CallableStatement对象是用Connection方法prepareCall创建的CallableStatementcstmt=con.prepareCall({callgetTestData(?,?)});2007/8/16清华大学知识工程研究室163.2.2设置IN和OUT参数设置IN参数通过setXXX方法完成设置OUT参数通过registerOutParameter方法完成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清华大学知识工程研究室173.2.3设置INOUT参数CallableStatementcstmt=con.prepareCall({callreviseTotal(?)});cstmt.setByte(1,25);cstmt.registerOutParameter(1,java.sql.Types.TINYINT)；cstmt.executeUpdate();bytex=cstmt.getByte(1);2007/8/16清华大学知识工程研究室183.2.4先检索结果，再检索OUT参数建议先检索由执行CallableStatement对象所产生的结果，然后再用CallableStatement.getXXX方法来检索OUT参数2007/8/16清华大学知识工程研究室193.2.5检索作为OUT参数的NULL值返回到OUT参数中的值可能会是JDBCNULL0或false是否源于JDBCNULL用方法wasNull进行检测getXXX方法读取的最后一个值是JDBCNULL，则该方法返回true，否则返回flase2007/8/16清华大学知识工程研究室203.2.6复杂返回值存储过程允许返回ResultSet等复杂类型createprocedurelist_early_deaths()returnrefcursorasdeclaretoesuprefcursor;beginopentoesupforselectpoets.name,deaths.agefrompoets,deaths--allentriesindeathsareforpoets.--butthetablemightbecomegeneric.wherepoets.id=deaths.mort_idanddeaths.age60;returntoesup;end;2007/8/16清华大学知识工程研究室213.2.6复杂返回值（续）Java代码staticvoidsendearlydeaths(printwriterout){connectioncon=null;callablestatementtoesup=null;try{con=connectionpool.getconnection();//postgresqlneedsatransactiontodothis...con.setautocommit(false);//setupthecall.callablestatementtoesup=connection.preparecall({?=calllist_early_deaths()});toesup.registeroutparameter(1,types.other);toesup.execute();resultsetrs=(resultset)toesup.getobject(1);while(rs.next()){stringname=rs.getstring(1);intage=rs.getint(2);out.println(name+was+age+yearsold.);}rs.close();}catch(sqlexceptione){//weshouldprotectthesecalls.toesup.close();con.close();}}2007/8/16清华大学知识工程研究室222007/8/16清华大学知识工程研究室234.定义触发器4.1触发器的设计规则①作用范围清晰②不要让触发器去完成Oracle后台已经能够完成的功能③限制触发器代码的行数④不要创建递归的触发器⑤触发器仅在被触发语句触发时进行集中的，全局的操作，同用户和数据库应用无关。2007/8/16清华大学知识工程研究室244.2触发器类型可以创建被如下语句所触发的触发器：（1）DML语句（DELETE，INSERT，UPDATE）；（2）DDL语句（CREATE，ALTER，DROP）；（3）数据库操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。2007/8/16清华大学知识工程研究室254.3.1定义触发器的语法CREATE[ORREPLACE]TRIGGERname{BEFORE|AFTER}{event[OR...]}ONtable[FOR[EACH]{ROW|STATEMENT}][WHEN(condition)]plsqlblock|callprocedures2007/8/16清华大学知识工程研究室264.3.2注意事项（1）触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和INSERT，UPDATE或DELETE执行前)或之后(在检查约束之后和完成INSERT，UPDATE或DELETE操作)触发.。（2）一个FOREACHROW执行指定操作的触发器为操作修改的每一行都调用一次。（3）SELECT并不更改任何行，因此不能创建SELECT触发器.这种场合下规则和视图更适合。（4）触发器和某一指定的表格有关，当该表格备删除时，任何与该表有关的触发器同样会被删除。（5）在一个表上的每一个动作只能有一个触发器与之关联。（6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。2007/8/16清华大学知识工程研究室274.4修改和删除触发器DROPTRIGGERnameONtable;2007/8/16清华大学知识工程研究室284.5实例当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员CREATEORREPLACETRIGG