oracle存储过程

下载 第29章 使用存储子过程、包与提供的包 本章要点： 定义存储子过程与包 建立与使用存储程序 使用S H O WE R R O R S调试 检查存储程序或包的状态 建立与使用包 关于Oracle 8i数据库提供的包 描述提供的包 开始学习Oracle 8i提供的包 使用Oracle 8i提供的包 29.1 定义存储子过程与包 当你着手创建存储子程序和包的时候，真正的 O r a c l e 8 i应用开发开始了，存储子程序和包 是经过编译并存储在数据库中的永久程序代码模块。它们是你 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 的可共享、可重入并且可 重用的软件对象。可以使用支持远程过程调用的语言从其他的 P L / S Q L模块、S Q L语句以及客 户端应用中调用它们。 当你编译了一个存储子程序或包时，程序的源代码、编译代码、编译状态以及全部编译 错误存储到数据字典中。各种不同的数据字典视图可以帮助你查看这些项。使用 U S E R _视图， 能够得到关于所编译模块的信息；使用 A L L _视图，能够得到其他人编译并授权你访问的相关 模块限制信息；假如你拥有数据库系统管理员权限，使用 D B A _视图，可以得到任何人编译的 全部信息。这些视图在表2 9 - 1中列出（为了简便，作为D B A _视图列出）。 表29-1 存储子程序和包的数据字典视图 视 图 名 称 说 明 D B A _ S O U R C E 所有编译模块的文本源代码 D B A _ E R R O R S 全部模块编译错误的文本列表清单 D B A _ O B J E C T _ S I Z E 编译模块的状态，例如有效性、源代码以及对象大小 D B A _ O B J E C T S 编译模块种类（存储过程、函数、包、包程序体） D B A _ D E P E N D E N C I E S 对象相关性列表，例如在包中引用的表 不存在暴露对象代码的视图，因为根本不需要看到它。你要知道的一切就是它是不是在 这里以及它是否是合法的。当一个相关对象被修改或删除时，例如向一个表添加一列或删除 一个表时，一个已编译模块变为非法。如果一个存储模块变为非法，必须由服务器自动地重 新编译它或由它的拥有者手工编译它。 29.2 建立与使用存储程序 创建存储子程序的语法与在匿名 P L / S Q L块中定义子程序的语法非常类似。除含一些额外 增加的特性外，存储子程序拥有子程序的全部相同特性，这些特性你已经在前面学习编写过。 让我们使用你先前看到的b o o l _ t o _ c h a r函数编写一个存储函数（参见清单 2 9 - 1）。 清单29-1 BOOL2CHR.SQL—编写重用代码存储子程序 服务器回答： 这就是你所得到的一切。服务器并不执行程序，它仅编译程序，以便日后当你从其他的 P L / S Q L块中调用它时，你可以执行它。 提示 C R E ATE OR REPLACE语法创建一个新函数或替换一个已有函数。这意味着不 需要给存储代码增加重编译或改变的源代码，而是使用新版本完全代替它们。 警告 当使用C R E ATE OR REPLACE时，需要良好的源代码管理手段。当替换子程序 时，老代码将永远从数据字典中消失。它还意味着在模式中只能含有一个拥有这个名 字的对象。 现在，使用一个未命名P L / S Q L块运行这个新创建的存储函数，如程序清单 2 9 - 2中所示。 清单29-2 TESTBOOL.SQL—测试执行存储子程序 这个例子差不多测试了存储函数可能返回值的全部可能性。它被称为一个单元测试。对 于每项输入，验证它的输出结果。输入值应该测试所有的临界状态（输入定义限制内或接近 输入限制的值，包括限制之间的一些随机值）。应该给你的存储子程序准备单元测试文件，就 像这个文件，那么你就可以检验并验证你的代码是否工作正确。将单元测试程序与存储子程 序一起保留，以便当你修改子程序的时候，可以再次测试它。 470计计第六部分计Oracel 接口和应用工具 下载 在运行程序清单2 9 - 2后，服务器送回下列输出结果信息： 如果尝试传入一个不是布尔值的其他值，将会发生什么？试试看： 服务器响应下列信息： 错误消息表明这是一个编译错误。 P L / S Q L引擎的强类型检查捕获这个错误并返回详尽的 错误消息。 看一下另外一个略微长一些的存储过程例子，如程序清单 2 9 - 3所示： 清单29-3 SHOWINDX.SQL—显示表的索引信息的存储过程 第29章计使用存储子过程、包与提供的包计计471 下载 这一次，服务器返回下面信息： 要执行这个过程，可以从一个匿名 P L / S Q L块中调用它，或者简单地使用 E X E C U T E命令 单行调用它： 提示 E X E C U T E命令只能用于运行单行语句。如果语句包含两个或多个语句行，必须 使用匿名P L / S Q L块（使用B E G I N⋯E N D）。如果将两条或多条语句塞入一行，仍旧可 以使用E X E C U T E命令。实际上，E X E C U T E命令将一行扩展为一个P L / S Q L块，它仅仅 是简写形式。 下面是作者的测试系统生成的输出结果信息： 第一次运行这个存储过程时，当等待服务器将过程加载到内存中的时候，你或许注意到 了一个略微的停顿。在继续进行的调用中，因为过程已经被加载到数据库的高速缓存，运行 它的速度明显加快了。 这个特定的例子展示了一些良好的特性，它们是在你自己的存储过程中所需要的。你应 该与局部变量声明的结构保持一致，并且以相同的顺序放置它们。还有，注意例外处理程序 中的块，一定要关闭游标，以免一个错误把它设为打开状态。如果没有这样做而且遇到一个 例外，下一次当运行这个存储过程的时候，将由于 C U R S O R _ A L R E A D Y _ O P E N错误立即产生 472计计第六部分计Oracel 接口和应用工具 下载 故障。因为游标在会话内保持打开状态直到它被关闭或会话结束。为了清除打开的游标，你 的用户将不得不重新连接数据库。 如果用户需要显示所有的表和它们的索引，只需要简单地删除单独输入参数（和括号）， 就像下面所示： EXECUTE show_index； 必须将自己放到用户的角度来设想用户希望采用哪种方式使用这个工具。更好的方法是 询问用户。 29.2.1 从SQL调用存储子过程 假设不满意O r a c l e 8 i提供的内建函数TO _ N U M B E R（）。你的抱怨可能是一个字符到数字 的转换失败了，原因是字符串不能表示一个合法的数字， S Q L失败并突然终止。你所希望的 是，至少错误能够被准确地处理，那么就可以继续处理剩余的数据集了。尝试使用存储函数 解决这个问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 ，如程序清单2 9 - 4中所示： 清单29-4 CHAR2NUM.SQL—字符到数字转换 可以采用两种方式运行这个存储函数： ■ 从一个P L / S Q L块。 ■ 从一条S Q L语句。 首先，尝试从P L / S Q L块中运行它，如程序清单2 9 - 5所示。 清单29-5 TESTC2N1.SQL—从P L / S Q L块测试c h a r _ t o _ n u m b e r（）函数 第29章计使用存储子过程、包与提供的包计计473 下载 服务器返回下列信息： 现在让我们在一条S Q L语句中测试它，如程序清单2 9 - 6所示。 清单29-6 TESTC2N2.SQL—从S Q L运行c h a r _ t o _ n u m b e r（）函数 得到与先前相同的结果。当你刚刚完成从一个字符串到数字的转换，就将它转换回字符 串，看起来有些愚蠢，但是这样验证了存储函数的操作，尤其在上一个查询中。 29.2.2 从PL/SQL调用存储子过程 已经见到过如何从一个 P L / S Q L块中激活一个存储子程序。它们有什么更高的使用价值 呢？已经尽力构造一个有 1 4个表连结而成的查询了吗？我们可以使用其他手段取而代之，为 每个主表创建一组单行查找程序，或许在连结中用到过主表的键值。然后，在一个 P L / S Q L块 中，只要写入用于驱动一个游标循环的最少量的表。在循环内部，执行单行查找以找出附带 的数据值。使用这项技术，可以看到速度提高了 1 0 0 %或更多。先前需要执行 2 0分钟的查询， 使用这种技术可以在 2分钟内或更短的时间内完成。如果使用了某种类型的报表编写器来格式 化输出结果，可以将数据写入临时表，然后临时表可以使用报表工具快速扫描。显然，这样 做在某种程度上减慢了程序执行速度，但是它还是比使用大量、复杂的查询让 O r a c l e 8 i服务器 陷入停顿要快得多。另外，它还易于验证输出结果的正确性。如果严格地测试单行查找程序， 必须验证的唯一事实是足够小的查询。 现在考虑一下有多少次不得不编写一个复杂查询的外部连结代码。在 P L / S Q L块中，测试 单行查找没有发现数据并且跳转到循环的结束或继续循环是非常容易的事情。例如，一个申 请处理程序也许含有一个循环，它看起来有点像这样： 474计计第六部分计Oracel 接口和应用工具 下载 本例中，单行查找的S Q L C O D E结果值返回变量s t a t u s（状态）。为了让这个程序真正有用， 需要将有关为什么申请没有被处理（没有被批准）的信息储存到某种类型的应用错误表中。 更好的方法是，在查找程序的内部，可以将查找键值和错误代码存储到你设计的一个错误表 中，那样，你可以在日后判定引起一个申请未被处理的子程序和键值。调用程序可以存储额 外的数据（例如环境信息），可以更加容易地排除故障。 存储过程的另外一个用途就是实现商务规则。可以从触发子、客户程序或其他的 P L / S Q L 程序中调用这些规则，如下例所示： 这里，应用定义例外引起一个 I N S E RT操作被回滚。一个存储过程定义商务规则，可以在 许多地方使用该商务规则。如果需要修改规则，可以在存储过程的程序体中更改它。与它相 关的P L / S Q L代码仅仅需要被重新编译即可。 29.3 使用SHOW ERRORS调试 到目前为止，存储过程都是没有任何问题地编译通过。不幸的是，可以预料到会犯错误 从而导致编译错误。幸好，调试错误的信息唾手可得。但是， O r a c l e 8 i提供的用于观察它们的 功能有一些使用限制，原因是它相对比较简单，但是我们有更好的解决 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 。 当未命名P L / S Q L块不能成功编译时，服务器将错误信息直接转储回 S Q L * P l u s。对于存储 子过程，这个过程略有区别。在编译失败后，立即输入下列命令： 每个错误以及错误出现的行号都详尽地显示出来。 提示 SHOW ERRORS命令仅显示最后一个存储子程序或包提交的编译错误。如果 在一行中提交了两个子程序， SHOW ERRORS命令只显示第二个子程序的错误信息 （如果有的话）。但是，在U S E R _ E R R O R S中仍然可以同时获得两个子程序的错误信 息。 请考虑程序清单2 9 - 7中的源代码，在过程之中隐藏了一个缺陷： 第29章计使用存储子过程、包与提供的包计计475 下载 清单29-7 SHOWERR1.SQL—具有缺陷的错误格式化过程 服务器返回下列错误消息： 要得到更多信息，需要输入下列命令： SHOW ERRORS命令的输出结果如下所示： 476计计第六部分计Oracel 接口和应用工具 下载 注意 P L / S Q L引擎清除存储源代码的空白行。如果为了程序的可读性，在程序中留有 两倍行距，会发现存储在U S E R _ S O U R C E中的源代码变得很快与源代码不一样了，这 使在最初源代码文件上进行的调试更为困难。 某些错误消息相当清楚，另外一些却非常含糊。还提供了错误出现的行号和列号。不幸 的是，出现问题的源代码行没有显示出来。可以使用下面的 S Q L语句显示源代码： 源代码显示如下： 配合上面的错误消息，将看到下列信息： 在第9行上，必须基于g e t _ e r r o r s % R O W T Y P E定义记录变量，而不是% T Y P E。 第1 9行错误的原因是g e t _ e r r o r s _ r e c没有正确地定义。 第2 1行错误的原因是g e t _ e r r o r s _ r e c没有正确地定义。 第2 3行错误的原因是g e t _ e r r o r s _ r e c没有正确地定义。 在第2 6行上，你忘记了声明变量e r r s。 可以看到一个错误是怎样扩展到整个代码的。在声明中修改错误后，相关的错误全部消 失了。 还有，要注意在这次编译中没有暴露的一些语法错误。例如，列命令 t y p e应该大写并且应 该用双引号括起来，因为 T Y P E实际上是一个保留字。在上面的五个错误清除后，这个错误出 现了（试一试）。 修改并提高的存储过程在程序清单 2 9 - 8中可以找到。 清单29-8 SHOWERR.SQL—最终版本，P L / S Q L错误格式化过程 第29章计使用存储子过程、包与提供的包计计477 下载 通过执行新的过程，得到的结果更为清晰、更为详细并且更易于阅读： 478计计第六部分计Oracel 接口和应用工具 下载 你会更喜欢这样。或许你想控制出现问题行前后的源代码打印行数，以便能够得到相关 的上下文。我把这个问题作为一个练习留给你来解决。 29.4 检查存储程序或包的状态 执行下面的S Q L语句查看你的全部存储子程序的状态： 这条命令的输出结果，在我的系统上如下所示： 注意过程S H O W E R R 1存在，即使它不能成功地编译。它确实在那里，源代码和它的所有 内容。但是如果试图运行它的话，会得到一个错误。 提示 表结构的更改有时候会使存储过程和对象无效。可以使用A LT E R⋯C O M P I L E命 令重新编译指定的数据库对象，如下面的例子所示： ALTER PROCEDURE SHOWERRS COMPILE； 第29章计使用存储子过程、包与提供的包计计479 下载 另外一个要查看的有趣事情是代码统计。要查看这些信息，运行下面的语句： 在我的系统上，这条语句的输出结果看起来如下所示： 注意，因为没有能够成功编译， S H O W E R R 1没有语法分析或代码大小。有趣的是，代码 和语法分析的大小大于源代码。在源代码大小与语法分析及代码大小之间的确没有太多的关 联。代码分析的大小取决于游标、隐式 S Q L、子程序等对象的数目——你可能会获得更多存 储的事物。代码大小使你具有了每个对象在 S G A中占用多少内存的概念。 提示 使用DESCRIBE命令，能够得到特定存储子程序参数列表的方便快捷的视图。 29.5 建立与使用包 包是程序对象的集合，可以提供数据、游标和其他语言结构的连续性。当引用包里的任 意对象时，整个包便被加载到内存中。虽然这看起来像是一个大的磁盘需求，但要记住当包 被加载后，代码对所有的用户可用。只要有任意一个用户引用这个包，它就保留在 S G A区中， 当前在S G A区里面的全部程序代码都是可用的。另外一种做法是像加载存储子程序那样分别 加载每段程序代码。因为被加载后，代码是可以重入的，所以对于全体用户都可用。代码重 入弥补了第一次磁盘命中所造成的系统开销。从服务器视图的观点来说，这样非常有效，既 节省了内存的使用又减少了磁盘存取。 包还允许子程序重载，这个特性对于纯粹的存储子程序不可用。使用重载，同一个子程 序名称可以在不同组合及数目的参数类型中被重复使用。如果观察 S TA N D A R D . S Q L，可以看 到TO _ C H A R、TO _ D AT E和TO _ N U M B E R的几个声明，每种声明都带有不同数据类型的参 数。 29.5.1 包声明与包体 P L / S Q L包由两个部分组成： ■ 包声明（也被称为头）。 480计计第六部分计Oracel 接口和应用工具 下载 ■ 包体。 包体包含包的声明部分中提到的全部子程序的实际代码。 包声明含有希望展示给其他人的全部语言结构，它们可以是变量、用户定义数据类型、 游标以及子程序，放置在声明中的任何对象都是可全局访问的，这里没有可执行代码，只有 子程序的声明。程序清单2 9 - 9展示了一个包头的例子，其中使用了几个你已经见过的函数。 清单29-9 LIBHDR.SQL—一个提议的库包的声明 注意在其他的存储子程序对象中见到的 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 的 C R E ATE OR REPLACE语法。现在，不必 像创建单个子程序那样显式地创建子程序，而使用以下方法代替，当子程序封装在块的内部 时，就像原先那样声明它们，除了在包头中。只要定义一个声明，包括子程序的名称、参数 （如果有的话）以及返回类型（如果有的话）。还有，注意子程序是如何遵循全部其他声明的。 那些熟悉C编程语言的人将认出这种函数原形。既然声明存在，不必开发这些程序的实际 代码，只需要修改它们—不违背定义。只要声明可用，开发者可以立即着手应用的开发， 即使程序体还没有编写。由于子程序声明和实现是分开的，如果仅仅代码升级了，使用这些 例程的程序就不需要重新编译。只有当声明部分改变的时候，相关模块才需要重新编译。 注意 包编程的标准惯例是将包头与包体分别存放在不同的文件中。使用这种方式，如 果仅仅是代码发生变化（最可能的情况是当包还比较新但仍然处于不断变化的的状态 时），仅需要重新编译包体。 使用所希望的任意标准化命名约定。有些人喜欢给包头和包体使用不同的扩展名 （* . h d r和* . p k g），还有些人喜欢总是给包头和包体使用* . s q l扩展名，但还是要指出包的 内容（*hdr.sql、*pkg.sql）。 包体含有声明中显示的子程序的实际代码，以及私有定义子程序、变量、用户定义数据 第29章计使用存储子过程、包与提供的包计计481 下载 类型以及游标。这些对象隐藏在最下面。包体还可以包含初始化代码，它们只在包被首次引 用时执行一次，虽然这样做看起来限制了包的使用，但你可以使用它来激活输出、设置各种 不同的应用标志（类似于调试开 /关）、以及收集全局用户相关数据。虽然包体与包头是有所 区别的对象，但在包头被成功地编译之后，包体才能够进行成功地编译。 这个对象在声明中被称为包体，程序被声明得好像存在于一个匿名块中。还要注意包体 的名字必须与包头名字相同。头中声明的每个程序在包体中有相应的实现过程。 另外需要注意的事情是在它们的声明中初始化变量。当包第一次被调用的时候，这样的 初始化只进行一次。这是因为这些变量只被分配并初始化一次，然后在整个会话间保持不变。 如果我在包的声明中重新设置调试标志而节省了几毫秒，在包的初始化中，就需要更明显的 并且更多的维护来做这件事情。 29.5.2 比较公有声明与私有声明 在包声明中声明的任何对象在使用范围上都是全局的。也就是说，在当前的会话内，任 何P L / S Q L程序可以调用并使用声明中定义的变量、游标和子程序。相反，只在包体中声明的 变量、游标以及子程序，虽然对于包体自身来说是全局的，但对于外部世界是隐藏的。在两 种情况下，变量和游标都是不变的，它们保存当前值，直到从内存中消失或会话终止。 我对使用全局变量的建议是尽量少用全局变量，或根本不用。还有，如果将他们隐藏在 包体中，在控制程序访问它们时，将得到全部变量保持不变的好处。通过创建全局可用的子 程序来管理它们，可以处理这个访问。 调试接口程序表明这样一个标准惯例，不要将一个变量本身暴露给外部世界，而是提供 操作这个变量的子程序。例如，你不希望开发者直接修改和检查变量 d e b u g _ f l a g的值，如果这 个调试标志是公共和全局的，任何程序就能够使用带有潜在危害或者至少不希望的结果更改 它。如果将来需要添加一些东西或更改调试功能实现的手段，对开发者隐藏具体的实现细节。 这是面向对象编程的常用方法。 除了常量和各种表的记录变量，我很少使用全局变量。我还喜欢将声明以一个标准的顺 序放置，如清单2 9 - 1 0中所示。这样一来，我总是能够精确地知道到哪里去查找需要的东西。 清单29-10 LIBPKG.SQL—前面声明中的包体 482计计第六部分计Oracel 接口和应用工具 下载 29.5.3 引用包元素 使用点符号，包外部的P L / S Q L代码可以引用包的内容，如清单 2 9 - 11中所示。记住，点符 第29章计使用存储子过程、包与提供的包计计483 下载 号被用来消除一个对象使用范围上所有含义模糊的地方。为了引用一个包装的对象，还必须 提供包的名称，采用以下格式‘包名字 .包对象’。如果引用对象的代码在同一个范围内，例 如在同一个包内，则不需要使用限定符。 清单2 9 - 11 TESTLIB.SQL—测试提议的库包的执行情况 要使其成为一个真正的测试，首先彻底退出你的 S Q L * P l u s会话，然后重新进入，这样可 以确保会话的确是新的。然后，输入命令 SET SERVER OUTPUT ON，并运行程序清单2 9 . 11 中所示的未命名块。注意该块没有打开输出，这点小事在包的初始化代码中执行。调试标志 正常情况下默认值为N U L L，也被初始复位了。服务器返回下列信息： 现在再次运行它。 注意，现在调试标志的初始值为 T R U E，这个值从一个程序传递到下一个程序。如果重新 连接，一定会拥有一个新的会话，并且再次执行包的初始化。记住，代码静态地存在内存中， 但是每位用户得到一个全新的局部私有变量集。运行第二个会话并验证它的行为。 29.6 Oracle 8i数据库提供的包 除了给予Oracle 8i服务器基本功能的 S TA N D A R D包，例如基本子类声明和数据类型转换 程序外，还有一组专供数据库系统管理员和开发者使用的包。这些包根据它们的名字有所区 别，它们使用 D B M S _或U T L _开头，表示它们与数据库进行交互或提供常规目的的应用。首 先，快速浏览这些包并在稍后更为严密地检查它们。 29.6.1 在服务器内交互 大多数Oracle 8i提供的包在服务器环境内部处理数据：数据字典项、用户数据库对象或在 484计计第六部分计Oracel 接口和应用工具 下载 系统全局区（System Global Area）中发现的单独的对象，像共享内存缓冲池等。使用它们， 可以管理快照、重新编译程序单元、当数据库项目改变时产生异步警报、运行作业等等。 大部分这样的数据库包与内建在数据库应用中的功能交互，或者通过调用可加载外部模 块例如D L L来扩展数据库环境。既不希望也不需要理解或使用这些模块的入口点。我宁愿明 确地告诉你不要尝试直接使用它们，一定要通过 P L / S Q L编程接口提供的手段来使用它们。 29.6.2 在服务器以外交互 一些包对调用环境给出反馈信息，或者通过其他手段给外部处理进程提供一个接口。例 如O r a c l e 8 i的管道特性，是专门用于会话之间通信的一种方法，它被严格地限定为只在内存中 —没有数据库对象用作中间存储介质。另外，当在 S Q L * P l u s中运行 P L / S Q L程序时， D B M S _ O U T P U T包使打印语句能够显示。 29.6.3 从服务器获取更多的信息 有几个包装例程可以令你获得你的数据库的附加调整信息，例如共享缓冲池的使用、段 空间信息、运行追踪、获得常规数据库信息等等。在习惯使用它们后，它们都会变成工具箱 中的标准部件。 29.7 描述提供的包 表2 9 - 2是一些重要的Oracle 8i提供的包的一个方便、快捷参考，它还是关于提供的包的内 容的一个简明描述说明。 表29-2 提供的包小结 包 名 称 包头文件 说 明 D B M S _ A L E R I d b m s a l r t . s q l 异步处理数据库事件 D B M S _ A P P LT C AT I O N _ I N F O d b m s u t i l . s q l 注册当前运行的应用的名称（用于性能监控） D B M S _ A Q A D M d b m s a q a d . s q l 与高级队列选项一起使用 D B M S _ D D L d b m s u t i l . s q l 重新编译存储子程序和包，分析数据库对象 D B M S _ D E B U G d b m s p b . s q l P L / S Q L调试器接口 D B M S _ D E F E R d b m s d e f r. s q l 远程过程调用应用的用户接口 D B M S _ D E S C R I B E d b m s d e s c . s q l 说明存储子程序的参数 D B M S _ J O B d b m s j o b . s q l 按指定的时间或间隔执行用户定义的作业 D B M S _ L O C K d b m s l o c k . s q l 管理数据库块 D B M S _ O U T P U T d b m s o t p t . s q l 将文本行写入内存，供以后提取和显示 D B M S _ P I P E d b m s p i p e . s q l 通过内存“管道”在会话之间发送并接收数据 D B M S _ P R O F I L E R d b m s p b p . s q l 用于配置P L / S Q L脚本以鉴别瓶颈问题 D B M S _ R E F R E S H d b m s s n a p . s q l 管理能够被同步刷新的快照组 D B M S _ S E S S I O N d b m s u t i l . s q l 程序地执行Alter Session（改变会话）语句 D B M S _ S H A R E D _ P O O L d b m s p o o l . s q l 查看并管理共享池内容 D B M S _ S N A P S H O T d b m s s n a p . s q l 刷新、管理快照，并清除快照日志 D B M S _ S PA C E d b m s u t i l . s q l 获取段空间信息 D B M S _ S Q L d b m s s q l . s q l 执行动态S Q L和P L / S Q L D B M S _ S Y S T E M d b m s u t i l . s q l 开/关给定会话的S Q L追踪 D B M S _ T R A N S A C T I O N d b m s u t i l . s q l 管理S Q L事务 第29章计使用存储子过程、包与提供的包计计485 下载 (续) 包 名 称 包头文件 说 明 D B M S _ U T I L I T Y d b m s u t i l . s q l 多种实用工具：对于一个给定的模式，重新编译存储 子程序和包、分析数据库对象、格式化错误信息并调用 堆栈用于显示、显示实例是否以并行服务器模式运行、 以1 0毫秒间隔获取当前时间、决定数据库对象的全名、 将一个 P L / S Q L表转换为一个使用逗号分割的字符串或 卷、获取数据库版本 /操作系统字符串 U T L _ R AW u t l r a w. s q l R AW数据类型的字符串函数 U T L _ F I L E u t l f i l e . s q l 读/写基于A S C I I字符的操作系统文件 U T L _ H T T P u t l h t t p . s q l 从给定的U R L得到H T M L格式的主页 D B M S _ L O B d b m s l o b . s q l 管理巨型对象 29.8 开始学习Oracle 8i提供的包 除了与Oracle 8i服务器一起安装的缺省包之外，还有许多可以与选择软件一起安装到服务 器的其他包。高级队列选项，即 Oracle 8i的一个新建包，就是一个这样的例子。 在能够使用包含在Oracle 8i提供的包中的程序前，应该首先检查它们已被安装，并且是合 法的。数据库系统管理员应该运行下面的查询： 上面的查询会给出一个类似下面信息的列表： 486计计第六部分计Oracel 接口和应用工具 下载 还有一些其他包没有显示在上面，但是这里不必关心它们。这些包中的多数不是为用户 应用调用而准备的，仅仅在内部使用而已。 29.8.1 定位DBMS包 Oracle 8i提供的包位于% O r a c l e _ H O M E % \ R D B M S \ A D M I N下，其中O r a c l e _ H O M E是指向 O r a c l e主目录的路径（可以检查 U N I X系统中名字相同的系统变量或检查 Windows NT环境下 的注册项）。可以检查表2 9 - 2中列出的每个包的包头文件，查看有哪些程序和全局变量可用。 或许还要注意具有p r v t * . s q l和p r v t * . p l b形式的文件（例如， p r v t p i p e . s q l和p r v t p i p e . p l b）的 存在，前面一种是提供的包的包体，采用 A S C I I格式；后面一种是包体的二进制编译版本。 P L B文件对于P L / S Q L引擎是可识别的，当提交给 O r a c l e 8 i服务器时，产生一个合法并且可执 行的包体。它们代表包体的“发布”形式。 警告 不要试图修改任何Oracle 8i提供的包的包体并重新编译，这样会破坏一些东西。 29.8.2 确定包已正确安装 需要验证两件事： ■ 包存在并且合法（就像前面的查询显示的一样）。 ■ 用户对它们拥有E X E C U T E权限或者对用户打算使用的包拥有 E X E C U T E权限。 使用下面的查询，一个用户可以查看他们是否对 O r a c l e 8 i提供的包拥有执行权限： 一个典型的回答应该如下所示： 所有的Oracle 8i提供的包应该被授予 P U B L I C上的E X E C U T E特权。我使用数据字典视图 a l l _ f l a v o r验证这点。如果打算使用的任何包丢失，数据库系统管理员可以运行适当的脚本重 第29章计使用存储子过程、包与提供的包计计487 下载 新生成它们。如果包存在但却是非法的，数据库系统管理员可以使用下面的命令重新编译它： 其中n a m e是非法包的名字。子句 COMPILE PA C K A G E告诉O r a c l e 8 i重新编译包声明和包 体。如果仅仅包体需要重新编译，运行下面的命令： 提示 一些提供的包可能与其他包有相关性。如果你必须重新编译一个包头，会让所有 相关的包非法。再次检查在重新编译后其他的包是否合法。如果只有包体被重新编译， 相关的包不会变为非法。这种方式也有可能使任何用户写入的存储子程序和包受到影 响。 29.9 使用Oracle 8i提供的包 下面将使用一些难易适度并且非常有用的例子表明每个提供的包中的一些有趣的内容。 29.9.1 使用DBMS_APPLICATION_INFO监控 这个包让开发者能够在视图 V $ s q l a r e a和V $ s e s s i o n中添加追踪信息，可以追踪的数据种类 如下所示： ■ 存储在V $ s q l a r e a . m o d u l e和V $ s e s s i o n . m o d u l e中的模块名称（例如当前运行着的应用的 名称），最长可以是4 8字节的VA R C H A R 2字符串。 ■ 存储在V $ s q l a r e a . a c t i o n和V $ s e s s i o n . a c t i o n中的当前动作（例如，“更新客户信息”、“验 证信用卡限额”），最长可以达到3 2个字节的VA R C H A R 2字符串。 ■ 存储在 V $ s e s s i o n . c l i e n t _ i n f o中的任意客户定义信息，最长可以达到 6 4个字节的 VA R C H A R 2字符串。 Oracle 8i不对这些信息做任何处理，这些信息提供给数据库系统管理员使用，这样她就可 以针对每个应用和部件操作执行统计。超过所能支持长度的字符串将被截断。程序清单 2 9 - 1 2 显示了一个简单的例子。 清单2 9 - 1 2 A P P I N F O . S Q L—设置并读取应用信息 488计计第六部分计Oracel 接口和应用工具 下载 得到的响应信息如下所示： 在会话启动期间，数据库系统管理员可以使用下面的程序检查谁在做什么： 这次得到下列信息： 数据库系统管理员可以使用下面的语句查看每个人运行这个模块和动作的效果： 结果非常有趣： 我明确地做出的唯一查询是第一个查询，但是很明显 S Q L * P l u s在这之后做了一些工作。 因为一个模块执行不同的动作，开发者应该对 s e t _ a c t i o n（）发出正确的调用以反映这点。 采用这种方式，可以收集到一些有趣的统计信息，这些信息反映了一个应用是如何通过查询、 动作、模块、用户或用户组恶劣地冲击服务器的。 注意 甚至当用户断开连接之后，项仍然存在于V $ s q l a r e a中直到相关的S Q L过时，从 SGA中消失为止。 29.9.2 使用DBMS_DDL重新编译包 可以对这个包做两件事： ■ 使用a l t e r _ c o m p i l e（）重新编译存储子程序和包。 ■ 使用a n a l y z e _ o b j e c t（）分析一个表、索引或簇。 一个a l t e r _ c o m p i l e（）的简单使用会发现哪些存储程序对象是合法的，然后重新编译它们。 程序清单2 9 - 1 3表明这种情形。 第29章计使用存储子过程、包与提供的包计计489 下载 清单29-13 RECOMPIL.SQL—只重新编译非法的程序对象 程序会返回如下所示的信息： 警告 如果一个程序对象不能够成功地重新编译，a l t e r _ c o m p i l e将不会通知你！在运行 alter_compile后，应该检查包的状态，确认它被成功地编译。 如果提供了一个并不存在的程序对象名，或者输错了程序对象名称，会得到下列信息： 可以程序地分析（生成统计数据）表、索引和簇。例如可以分析模式中全部或选定的对 象，如程序清单2 9 - 1 4所示。 清单29-14 runstats.sql—程序地运行统计 490计计第六部分计Oracel 接口和应用工具 下载 第29章计使用存储子过程、包与提供的包计计491 下载 注意必须做全部输入验证。部分原因是因为 a n a l y z e _ o b j e c t函数自己对此做得很少。例如， 如果提供了一条使用C O M P U T E方法估算的纪录行，会得到下列信息： 当选择全部默认值计算统计信息时，运行结果如下所示： 如果输入了一个非法的方法，服务器返回下列信息： 这正是所希望的，反复使用它你会看到你是如何喜欢它。 A N A LY Z E命令的这种实现方式没有做 VALID STRUCTURE和LIST CHAINED ROWS。 因此，它是一种不完全的实现过程，但是仍然相当有用。 29.9.3 使用DBMS_OUTPUT格式化输出 如果跟着教材学习，你已经非常熟悉包中的 p u t _ l i n e过程，这里是关于这个包实现的一些 细节。 ■ 每行使用一个换行符结束。 ■ 每行最多允许2 5 5个字节，包括换行符。 ■ 每行存储在私有的P L / S Q L表中。 ■ 除非首先调用D B M S _ O U T P U T. E N A B L E，否则不存储任何东西。 ■ 缓存大小范围 规定 关于下班后关闭电源的规定党章中关于入党时间的规定公务员考核规定下载规定办法文件下载宁波关于闷顶的规定 为2 000至1 000 000之间。 使用过程e n a b l e打开输出特性。如果没有首先打开输出， p u t _ l i n e调用将被忽略。当激活 输出时，还要指定一个缓存大小，如下所示： 反之，可以使用d i s a b l e关闭输出，如下所示： 可以使用 p u t _ l i n e存储一行文本。该函数被重载，可以接受一个 D AT E、N U M B E R或 VA R C H A R 2数值。通过使用p u t过程（它也以同样方式被重载），还可以存储一行没有结束的 492计计第六部分计Oracel 接口和应用工具 下载 文本。如果创建的文本行需要一些逻辑，则这个过程非常有用，如下面的例子所示： 通过使用n e w _ l i n e过程，可以结束使用 p u t过程提交的文本。该过程使用一个标志表示当 前文本行结束，并将文本行的长度与文本行一起存储（这对于用户来说是完全透明的）。注意， 如果试图不首先使用 p u t过程就发送新文本行，以两倍或三倍行距打印输出行，该过程将不工 作： 这次给出下列信息： 请注意没有打印两倍行距。 如果试图在使用n e w _ l i n e过程结束一行前，输出一个超过 2 5 5个字节可接受长度的字符串， 会得到一个例外： 为什么 O r a c l e公司的人将字符串的长度限制为 2 5 5？回顾前面的例子，在那里一个 VA R C H A R 2（3 2 7 6 7）类型的P L / S Q L表很快地耗光了系统的可用内存（第 2 8章“P L / S Q L基本 原理”，程序清单2 8 - 1 0）。换句话说，他们选择了一个他们认为合理的限制。 使用g e t _ l i n e返回字符串，这正是 S Q L * P l u s返回使用p u t _ l i n e写入的字符串所做的事情。 它以你看不见的方式调用 g e t _ l i n e过程，直到再没有任何行可用为止。如果正在编写一个 3 G L 程序接受使用 p u t _ l i n e写入的文本行，确实只需要将注意力放到 g e t _ l i n e（还有它的多行版本 g e t _ l i n e s）过程的使用上。如果愿意，可以在一个 P L / S Q L程序中使用它，或许将它们插入一 个表中，例如缓存文本行然后以 F I F O方式访问它们。 缓存大小有什么用？毕竟，一个 P L / S Q L表可以含有超过四十亿的元素！设想你编写了一 个C程序通过g e t _ l i n e过程接收缓存的字符串，分配一块 4 . 2 9千兆×2 5 5字节的内存的确是一个 浪费（而且也不是一个好主意）。你将需要上万亿字节的内存！虽然内存今天便宜了许多，但 还没有便宜到那种地步。从而，你要求用户规定缓存的大小，比方说，在 2 K与1 M B之间，并 且那正是你所分配的。包追踪高速缓存空间的使用，因为你的 C程序预期的正是用户指定的 数据。然后将返回到该内存区的文本行压缩，浪费很少的空间或基本不浪费空间。最后，所 有的文本行都可以显示给用户。这或许看起来不像是一个特别完善的模式，但它简单而且实 用。 第29章计使用存储子过程、包与提供的包计计493 下载 如果扩展缓存空间为所指定的大小，将得到下面的例外： 29.10 小结 本章学习了关于如何编写存储过程和包的丰富信息，还学习了如何使用与数据库服务器 一起提供的包。学习了如何使用 SHOW ERRORS命令调试存储程序，甚至能够编写一个脚本 提高那些命令的输出。最后，学习了如何检查存储过程的合法性，以及如果存储过程变为非 法时如何重新编译它们。现在应该能够理解本章中的例子并能够使用你自己的用户化要求来 提高它们，或创建你自己设计的新过程。 494计计第六部分计Oracel 接口和应用工具 下载 第29 章使用存储子过程、包与提供的包 29.1 定义存储子过程与包 29.2 建立与使用存储程序 29.2.1 从SQL 调用存储子过程 29.2.2 从PL/SQL 调用存储子过程 29.3 使用SHOW ERRORS 调试 29.4 检查存储程序或包的状态 29.5 建立与使用包 29.5.1 包声明与包体 29.5.2 比较公有声明与私有声明 29.5.3 引用包元素 29.6 Oracle 8i 数据库提供的包 29.6.1 在服务器内交互 29.6.2 在服务器以外交互 29.6.3 从服务器获取更多的信息 29.7 描述提供的包 29.8 开始学习Oracle 8i 提供的包 29.8.1 定位DBMS 包 29.8.2 确定包已正确安装 29.9 使用Oracle 8i 提供的包 29.9.1 使用DBMS_APPLICATION_INFO 监控 29.9.2 使用DBMS_DDL 重新编译包 29.9.3 使用DBMS_OUTPUT 格式化输出 29.10 小结