|
http://www./forum.php?mod=viewthread&tid=5444
建立动态PL/SQL块
在PL/SQL中，动态地构造和执行代码块可能是最另人兴奋的事。想一下：当一个用户正在运行你的应用程序，这个软件可以做下面的任何事情：
建立一个程序，它有一个含有全局性可访问数据结构的包。
通过名字获得（和改变）全局变量值。
在编译时调用未命名的函数和过程。
当然，这种较为笼统的工作确实有一些额外的复杂因素。下面是你操作PL /SQL块和NDS时要记住的技巧：
动态字符串必须是一个有效的PL/SQL块。它必须以关键字DECLARE或BE GIN开始，以一个END语句和分号结束。
在你的动态块里，仅能获得具有全局范围的PL/SQL代码元素（孤立的函数、过程以及在包的说明中定义的元素）。动态PL/SQL块在本地封装块的范围外执行。在一个动态PL/SQL字符串内产生的错误可被执行字符串的局部块捕捉并处理。
Errors raised within a dynamic PL/SQL string can be trapped and handled by the local block in which the string was executed.
让我们用两个（一个简单，一个比较复杂）基于真实生活经历的例子来探索这些规则。
一个基本的灵活工具。 首先我将建立一个短小的工具来执行动态 PL/SQL：
/* File: dynplsql.sp */
CREATE OR REPLACE PROCEDURE dynPLSQL (blk IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN ' || RTRIM (blk, ';') || '; END;';
END;
/
这一程序集中了许多前面提到的PL/SQL执行规则。用BEGIN-END对将字符串封装起来，我保证无论我加入什么，都会被作为一个有效的PL/SQL块运行。例如，我可以如此简单地动态执行calc-totals过程：
SQL> exec dynPLSQL ('calc_totals');
现在让我们超越这一普遍性的例子，来看一些更具体的例子。
一个真实的犯罪故事（写出这种代码的方法是一种真实的犯罪。）我曾经在一个保险公司呆过一段时间，在那里，建立保险单的原则与IRS建立税法的原则是一样的：越复杂越好。例如，一张保险单可能具有成百行的细则项目，每一项有一数值与之相联系，每一项适用于或不适用于某一特定索赔要求。
在公司里，开发人员采取了如下方法：对保险单中的每一行号，他们都写了一行处理过程。于是，为了处理第一行，他们将调用：
process_line1
并且，如果第514行适用于索赔要求，他们将调用：
process_line514
因此，他们最终的程序是这样：
CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)
IS
BEGIN
IF line = 1 THEN process_line1;
ELSIF line = 2 THEN process_line2;
...
ELSIF line = 514 THEN process_line514;
...
END IF;
END;
多年以来，这一程序像牡蛎里的珍珠在成长：process-lineN 程序在一层又一层地扩充。令人悲哀地是，珍珠的层次产生了美丽，而程序中的层次却使得编译和执行慢了许多。
当然，动态SQL极好地适应于这一情节，我在代码中定义了一个清楚的模式：对第N行运行process-lineN ，并将其翻译成动态构建PL/SQL块，如下所示：
CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)
IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN process_line' || line || '; END;';
END;
于是这一由成千上万行代码组成的程序缩减成了一个可执行的语句。
本地动态SQL的乐趣
虽然我能够不断地提供动态PL/SQL的令人兴奋的应用实例（例如能够执行你交给它的任意表达式或函数的计算器程序，或者一个列表解析引擎，它将列表元素返回到你指定的集合中），但是，我希望此时你已经掌握了其中的要点。记住我们在这曾经讨论过的问题，尤其是那些关于尽可能地捆绑变量、使用AUTHID CURRENT-USER 子句来正确解析授权，以及不停地捕获错误等方面的问题，你会充分准备好创建自己精巧的、动态结构的SQL和PL/SQL字符串。
Steven Feuerstein, 是Oracle PL/SQL语言的重要专家，是关于PL/SQL方面五本书的作者：《Oracle PL/SQL编程》，《利用包的高级Oracle PL/SQL编程》，《Oracle内置包》，《Oracle8i特性的Oracle PL/SQL编程指南》，《Oracle PL/SQL开发人员工作手册》以及《PL/SQL CD 书架》（全部由O’Reilly &Associates出版）。Steven还为RevealNet公司 (www.) 构建了PL/SQL开发员实用工具程序，并通过PL/SolutionS(www.)提供培训和咨询。
在动态SQL中使用BULK COLLECT
Oracle8i提供了许多不同的改进PL/SQL程序性能的方法。一个非常有用的新特性就是BULK COLLECT 子句，使用它可以用一句简单的SELECT INTO语句查询大量的行。这些行直接被放置在各种集合（嵌套表，索引表，或不同的数组）中。
下面是一个使用BULK COLLECT检索关于我编写的或合著的PL/SQL书籍的多行信息的例子：
CREATE TYPE titles_t IS TABLE OF book.title%TYPE;
/
CREATE TYPE authors_t IS TABLE OF book.author%TYPE;
/
CREATE TYPE page_count_t IS TABLE OF book.page_count%TYPE;
/
DECLARE
titles titles_t := titles_t ();
authors authors_t := authors_t ();
page_counts page_count_t := page_count_t ();
BEGIN
SELECT author, title, page_count
BULK COLLECT INTO authors, titles, page_counts
FROM book
WHERE author LIKE 'FEUERSTEIN%';
END;
你会注意到，只用一个查询，我们就方便地检索了所有的行。
现在 BULK COLLECT是一个PL/SQL语句，而不是SQL语言的一部分。因此，如果想用动态SQL执行一个BULK COLLECT操作，我必须在一个PL/SQL块内进行操作。例如，假设我想创建一个通用函数，能够检索任意表中任意列的所有VARCHAR2型值，并且尽可能有效地这样做。
BULK COLLECT和NDS共同提供了达到这一目的的工具。我粗略地拼合了一个PL/SQL块，它包含一个非常通用的BULK COLLECT查询。我为集合包括了一个占位符，然后用包含实际集合的U SING子句执行这个块，这一集合容纳了所有的行。
如下列所示，all_strings.fun文件不仅包含函数的定义，而且包含了显示由函数填充的集合内容的程序，以及基于雇员表的示范脚本：
/* file: all_strings.fun */
CREATE OR REPLACE FUNCTION all_strings (
tab_in IN VARCHAR2,
col_in IN VARCHAR2,
where_in IN VARCHAR2 := NULL
)
RETURN strings_t
IS
dynstr VARCHAR2 (2000);
l_strings strings_t := strings_t ();
BEGIN
dynstr :=
'BEGIN
SELECT ' || col_in ||
' BULK COLLECT INTO :strings ' ||
' FROM ' || tab_in ||
' WHERE ' || NVL (where_in, '1 = 1') ||
';
END;';
EXECUTE IMMEDIATE dynstr USING OUT l_strings;
RETURN l_strings;
END;
/
|
