文献综述(Oracle存储过程原理及应用

1、 宋龙：重庆交通大学毕业设计（文件综述） 毕业设计（论文）文献综述题 目 Oracle存储过程 原理及应用 专 业 计算机科学与技术 班 级 07级计科 1 班 学 生 宋 龙 指导教师 王 家 伟 重庆交通大学2011年 ORACLE存储过程原理及应用摘要 计算机技术的飞速发展，推动了整个社会的信息化进程，促进了信息技术的进步，催生了量的信息系统，尤其是各种管理软件。而管理软件离不开对大量数据的处理，这就要依赖于数据库管理系统。Oracle 是当前世界上使用最为广泛的数据库管理系统之一，由于Oracle 拥有强大的数据管理能力和较好的数据保密性，以及出众的技术优势。Oracle 得到了大规模

2、的应用，它经常作为大型管理软件的后台数据库。程序可以通过相应的软件接口对数据库进行操作，因为大型系统的业务规则一般都比较复杂，需要进行多个库表的操作，逻辑比较复杂，如果单纯使用SQL 语句进行数据库操作，应用程序代码会非常冗长，网络传输的负担会加重，系统响应速度也较慢，一旦业务规则发生变化，需要进行大量的代码修改工作，尤其是对于非常复杂的数据库操作，作量会成倍增长，与此相应的系统调试的工作量也很大。这非常不利于系统的维护和使用，同时造成了系统客户端的工作量的加大，而服务器强大的处理能力又被闲置。针对这种弊端，几乎所有的大型系统都使用存储过程来进行数据库操作。采用这种数据库操作模式，可以把应用程

3、序和数据库操作相对独立开来，分别管理。关键字：信息技术, Oracle, 数据管理, 存储过程, 数据保密, 数据库, 数据库技术, 数据库系统引言在Oracle 中，复杂的业务规则和应用逻辑可作为过程（procedure）存储。存储过程是一组SQL 和PL/SQL 语句，它允许我们把加强业务规则的代码从应用移动到数据库中。结果是，代码只存储了一次，却可以被多个应用使用。因为Oracle 支持存储过程，应用中的代码可以变得更紧凑一致和更易于维护。使用存储过程可以极大地提高数据访问效率，提升整个应用系统的性能。针对一些比较复杂的业务规则的处理，可以放在数据库中由服务器执行。在客户服务器或三级应用

4、中，把复杂应用的处理，从应用转移到数据库服务器，可以明显提高性能。另外，因为数据代码被存储在数据库中且为静态，可以从重复使用数据库中的相同查询获益。系统全局区中的共享SQL 区（shared SQL Area）储存可执行命令执行后的分析版本。从而当一个过程被再次执行时，它将能够利用上一次执行的分析操作来提高过程的执行性能。基于这些原因，应用开发的效果也从中受益。为了创建一个过程对象， 必须拥有CREATE PROCEDURE 系统权限（为角色RESOURECE 的一部分）。如果此过程对象在另一用户的领域中，则必须拥有CREATE ANY PROCEDURE 系统权限。因为存储过程往往与一些数据

5、库表有关联，所以要保证存储过程的正常使用，这些过程对象的所有者必须对这些表拥有一定的操作权限，而执行这些过程对象的用户并不需要在其所基于的表上具有特权。这样的用户，仅需在此过程对象上具有EXECUTE 权限即可。1 存储过程简介存储过程是SQL 语句和可选控制流语句的预编译集合,以一个名称存储作为一个单元处理. 与传统意义上SQL 规范中的SQL 语言不同,它是由SQL 语句和流控制语句共同组成的一个过程序,通过所设置的参数,调用其它过程,并返回状态值给调用者以指示操作的成功或失败,并能够在远程数据库中运行. 此外,存储过程经过数据库编译和优化后存储在数据库服务器中,用以被客户程序执行调用.2

6、 存储过程的优点1) 提高系统效率2 . 对于客户端来说, 只要调用已在服务器中存放的存储过程, 并通过网络发送该过程名和少量入口参数, 数据库服务器就可以执行该过程, 在执行完成后只返回结果集数据给客户端应用程序, 而无需再在网上传送大量的命令和中间结果数据, 这可以降低网络负担, 并且又可以充分利用服务器的高性能来提高运算速度.2) 提高系统可维护性. 因为修改存储过程通常比修改所部署的组件中的硬编码SQL 语句要容易. 并且存储过程和组件一样能够作到一次编写多次调用, 修改与程序的其他部分独立.3) 增强系统的安全性. 当创建了存储过程之后,可以将执行该过程的权限授予其它用户,从而使得他

7、可以执行特定的数据库操作,而不能访问其它模式对象(例如表) . 例如,你可以将执行过程(更新表) 的权限授予其它用户,但不授予它们直接访问该表的权限.4) 减少网络负荷. 当客户端发出执行存储过程的请求时,只有执行存储过程的命令在内部网络上传送,当它们到达数据库服务器时,运行存储过程,客户端在网上只接收返回结果或状态信息,使得客户机与服务器的通讯量降至最小,大大减少了网络负荷.3 存储过程的使用3.1 存储过程的使用前提在Oracle 中，复杂的业务规则和应用逻辑可作为过程（procedure）存储。存储过程是一组SQL 和PL/SQL 语句，它允许我们把加强业务规则的代码从应用移动到数据库中

8、。结果是，代码只存储了一次，却可以被多个应用使用。因为Oracle 支持存储过程，应用中的代码可以变得更紧凑一致和更易于维护。使用存储过程可以极大地提高数据访问效率，提升整个应用系统的性能。针对一些比较复杂的业务规则的处理，可以放在数据库中由服务器执行。在客户服务器或三级应用中，把复杂应用的处理，从应用转移到数据库服务器，可以明显提高性能。另外，因为数据代码被存储在数据库中且为静态，可以从重复使用数据库中的相同查询获益。系统全局区中的共享SQL 区（shared SQL Area）储存可执行命令执行后的分析版本。从而当一个过程被再次执行时，它将能够利用上一次执行的分析操作来提高过程的执行性能。

9、基于这些原因，应用开发的效果也从中受益。为了创建一个过程对象， 必须拥有CREATEPROCEDURE 系统权限（为角色RESOURECE 的一部分）。如果过程对象在另一用户的领域中，则必须拥有CREATE ANY PROCEDURE 系统权限。因为存储过程往往与一些数据库表有关联，所以要保证存储过程的正常使用，这些过程对象的所有者必须对这些表拥有一定的操作权限，而执行这些过程对象的用户并不需要在其所基于的表上具有特权。这样的用户，仅需在此过程对象上具有EXECUTE 权限即可.3.2 存储过程的使用技巧用来执行过程的语法取决于调用这个过程的环境，如果是在命令窗口下，可以通过使用execute

10、 命令加上存储过程的名称来执行，当然，过程所需要的输入参数必须赋上相应的值，如果有输出参数的话，还得先定义相应的变量，用于得到过程的输出结果，所有的参数都必须跟在过程名后并用括号圈起来，如同下面所示的例子（protest 为存储过程名）：execute protest（test）；如果一个过程内调用了其他的过程，则无须execute 命令，即：protest（test）；如果要执行另外一个用户拥有的过程，就得给这个过程创建一个同义词，或者在这个过程的前面加上所有者的名称。当执行远程过程时，必须制定数据库链接名称。数据库链接的名称必须在过程名称之后和变量之前指定（REMOTEDB 为远程数据库链

11、接的名称），如下所示：execute protestREMOTEDB（test）；为了对远程过程的调用更加方便，我们通常给远程过程创建一个同义词，这样能够避免因为远程数据库链接的名称发生变化而必须修改所有远程数据库链接名称。创建了同义词以后，用户就可以使用此同义词名称来引用远程过程。如果远程过程调用涉及远端数据库中的更新操作，为了能正常调用远程过程，必须安装分布式选项。否则，用户就有可能不能调用远程过程，这势必给系统开发带来极大的不便。过程的创建语法是：create or replaceprocedureuser.（argumentINOUTIN OUTdatatype，argumentINO

12、UTIN OUTdatatype ）ISASblock external program；过程写好后，一定要在SQLPLUS 中进行编译，而且只有编译成功后，才能被执行，即使编译成功了，还要进行相关的功能测试，确保过程执行后，能完成预定的各项任务，所有这些就涉及过程的调试。过程在SQLPLUS 环境中进行调试时，通过show errors 显示与最近创建的过程对象相关的所有错误。此命令将检查数据字典视图USERERRORS，以寻找对过程对象的最近编译相关的错误。命令show errors 将显示每个错误的行和列号及具体的错误信息。为了查看与过去创建的过程对象相关的错误，可以直接查询USERER

13、RORS。对USERERRORS 的查询不是公用的。因为这样的一个查询，可能意味着在两个或多个过程中存在错误。除了命令show errors 提供的调试信息外，还可以使用DBMSOUTPUT 包。当数据库中安装有过程选项时，就可以建立DBMSOUTPUT。为了使用DBMSOUTPUT，必须首先发布命令set serveroutput on 在执行欲调试的过程对象之前。DBMSOUTPUT 允许在包中使用3 个调试函数：PUT 把多个输出放在同一行中；PUTLINE 把每个输出分别放在单独行中；NEWLINE 与PUT 一起使用，表明当前输出行的结束。PUT 和PUTLINE 用于生成用户所希望

14、显示的调试信息。例如，假设正在调试一个包含循环的过程，可能希望在每次通过此循环时跟踪某个变量的变化。为了跟踪此变量的值，可以使用与下列所示非常相似的一个命令。在此例中，列Name 的值被显示出来，在其前面的是前缀字符串Name。例：PUTLINE（Name: Name）；也可以在循环外使用PUTLINE，但是这种用法最好与函数中的命令RETURN 的用法在一起使用。除了在ORACEL 的SQLPLUS下调试过程外，还可以使用一些第三方开发的调试工具，例如目前使用比较广泛的ORACLE 开发工具PLSQLDeveloper，这是一种非常简便易用，且功能强大的软件，它基本满足了ORACLE 数据库

15、开发所需的功能，因为它是一种图形化的工具，避免了使用者要输入大量的命令，降低了对使用者的要求，许多数据库命令都实现了菜单化，让人一目了然。使用这种工具软件，可以大大提高开发效率。在过程对象中可以建立不同的错误条件。对于定义的每一个错误条件，可以选择一个错误信息，当此错误发生时被显示出来。显示给用户的错误代码和错误信息通过过程RAISEAPPLICATIONERROR 被设置，此过程可以在任何过程对象中调用。过程中经常会使用一些SQL 语句来查询一些数据，而且许多语句的功能相同，只是执行的对象不同而已，所以如果能灵活地组织SQL 语句，将给开发工作带来许多方便，提高代码的使用效率，动态SQL 语

16、句的执行使用EXECUTE IMMEDIATE命令，该命令不进行提交，必须显式提交。在过程中经常会遇到多重查询的情况，处理此类情况，可以采用动态游标，通过使用动态游标，用户可以根据需要，传入相关的参数进行查询，然后在游标内部进行进一步的查询，这样可以降低SQL 语句的复杂程度，提高查询效率，同时，可以提高过程的重复使用率。3.3 Oracle 存储过程的使用过程3.3.1 Oracle存储过程的创建Oracle 中创建存储过程的语法结构如下3 :Create procedure ( ) As begin return end ;3.3.2Oracle 存储过程的调用Oracle 的存储过程保存

17、在数据库中,应用程序是通过JDBC 的接口访问数据库的,调用存储过程应该建立数据库连接,通过CallableStatement 接口调用存储过程4 . 步骤如下:1) 加载JDBC 驱动程序:要通过JDBC 连接数据库,应用程序必须加载合适的JDBC 驱动程序,可以使用Class. forName 方法注册驱动程序.2) 建立与数据库的连接:建立与数据库的连接可以使用DriverManager 的getConnection 方法建立一个Connection 对象.3) 通过CallableStatement 接口调用存储过程: CallableStatement 接口是PreparedStat

18、ement 类的子类,可以使用PreparedStatement 接口中的setxxx 方法来设置存储过程的输入参数( IN 参数) . 当存储过程有输出参数时,必须用registerOutPuter 方法注册输出参数,在执行了存储过程后,再用getxxx 方法获得输出参数的值.3.3.3 存储过程中参数调用方法JDBC 中的参数有3 类:输入参数( IN) 、输出参数(OUT) 和输入输出参数( IN OUT) . 输入参数( IN参数) :用来接收输入数据,通常用在SQL 语句、存储过程或函数中. 用于SQL 语句中的输入参数必须在SQL 语句执行前进行赋值,用于存储过程中的输入参数则用来

19、接收输入数据. 输出参数(OUT参数) :在存储过程中用来输出运算结果. 输入输出参数( IN OUT 参数) :在存储过程中可用于接收数据,也可用于输出运算结果.下面的程序段给出了存储过程中参数在java语句中的调用方法.String pro = ” call wincase. spDelPerson( ?, ?, ?) ”;OracleCallableStatement cs = (Oracle.CallableStatement) con. prepareCall (pro) ;cs. registerOutParameter (1 , java. sql . Types. INTEGE

20、R) ; /设置输出参数类型cs. registerOutParameter (2 , java. sql . Types. INTEGER) ; /设置输出参数类型cs. setInt (2 , 0) ; /第二个参数是输入输出参数cs. setInt (3 , personId) ; /设置输入参数cs. execute () ;4 在JAVA 中使用Oracle 存储过程的几点建议综上所述,以下提出针对在JAVA 中使用Oracle 存储过程的几点建议5 :1) 要使存储过程与数据访问逻辑组件相关联,每个存储过程只应被一个数据访问逻辑组件调用,并应与调用它的数据访问逻辑组件相关联.2) 定义存储过程时,存储过程