关于PL/SQL的泛型(Generics)
By Steven Feuerstein
编写泛型的可重用的代码可以开拓思维并且能够节省大量时间，因为可以避免一再地编写同样的代码。另一方面，必须承认泛型程序通常比起语法的具体变化更加复杂和难于测试。此外，我们常常不知不觉地陷入过度设计的危险境地。
“咦，我为什么不增加5个参数以便使程序更加灵活呢？”——这很危险。
针对泛型应用尝试的三个建议：
·不要过度设计(Don’t overdesign)。现在所需要的以及即将需要的，必须是实际存在的，在设计程序时避免考虑可能的需求。
·不要过度编码(Don't overcode)。确认是否遵守已决定好的设计。再次提醒，当编写泛型代码时，你会发现自己越来越多的考虑各种可能性。
·减少时间浪费(Cut your losses)。当开始构造泛型程序时，你可能会发现这比预期的要困难。相信你的直觉，如果你脑海中响起这样的话语，“是的，我知道我可以做到……但是它真的非常非常重要吗？”这时，你应该郑重考虑放弃你的泛型企图，而编写更加明确的符合你当前需求的程序。
泛型的问题及解答：
1、应该怎么声明“内部”字符串——泛型函数中的本地变量？当声明VARCHAR2类型的本地变量时，必须指定长度。因此，问题变成：怎么声明长度最好？难道要声明为VARCHAR2(32767)，仅仅因为那是最大可能的长度？
解答：
声明本地变量的最佳实践是基于共享程序包中最大可能的字符串长度来定义一种子数据类型（SUBTYPE）。这样，在声明本地变量时，所有字符串函数都可以引用该数据类型。如果所有字符串函数都包含在一个单独的包(package)中，那么也可以只在包中定义这种子数据类型。并且，这不会占用超过需要的更多内存，因为ORACLE数据库会按照声明为32767字符长度的字符串变量所需要的来分配内存。
要点：避免写死的（hard-coded）VARCHAR2限制。
BETWNSTR函数是我常用来示范不同最佳实践技术的程序，它只是对SUBSTR函数做了些简单改动。
内置函数SUBSTR语法为：SUBSTR(string,a,b)，返回从字母位置a开始的有b个字符长的string的一部分。
SUBSTR非常好用，但我常常知道一个字符串的开始和结束位置，而想得到这两个位置之间的部分。如果使用SUBSTR，我不得不计算从开始到结束的字符个数，而且我经常忘记相关公式（结束位置 - 开始位置 + 1），因此我编写了一个小程序来记忆这个公式，如下：
FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN PLS_INTEGER
, end_in IN PLS_INTEGER
)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
BEGIN
l_return := SUBSTR ( string_in, start_in, end_in - start_in + 1 );
RETURN l_return;
END betwnstr;
注意：BETWNSTR程序仅仅是为示范为建立的一个简单程序，它并不能真正处理关于“字符之间”的所有情况，另外程序中声明了一个本地变量，实际上并不真正需要它，这也是为了示范。（本文后面有实际使用中的字符处理包的全部代码）
代码列表 1: 包含BETWNSTR函数的string_pkg包
| CREATE OR REPLACE PACKAGE string_pkg IS SUBTYPE maxvarchar2_t IS VARCHAR2 ( 32767 ); FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER ) RETURN VARCHAR2; END string_pkg; /
CREATE OR REPLACE PACKAGE BODY string_pkg IS FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER ) RETURN VARCHAR2 IS l_return maxvarchar2_t; BEGIN l_return := SUBSTR ( string_in , start_in , end_in - start_in + 1 ); RETURN l_return; END betwnstr; END string_pkg; / |
现在，我将BETWNSTR移到一个包中，而且去掉了写死的（hard-coded）变量声明（既l_return VARCHAR2(32767);这种方式）。注意，我声明子数据类型(SUBTYPE），如下：
SUBTYPE maxvarchar2_t IS VARCHAR2 ( 32767 );
并且在本地变量声明中使用了该数据类型。
当然，在这个包程序中，还是有固定的数字32767，但是它只出现了一次，而且它将充当新的长VARCHAR2数据类型。如果ORACLE扩展了VARCHAR2的最大长度，那只需要修改这一个地方，重新编译string_pkg，然后所有的程序都能利用上已扩展的数据类型。
2、怎样是声明和使用字符串列表(list of strings)的最佳方法？
解答：
针对字符串列表，需要声明一个已有集合类型的列表。可以使用ORACLE提供的类型，比如DBMS_SQL.VARCHAR2S（每个字符串最大255字节）或DBMS_SQL.VARCHAR 2A （每个字符串32767字节，Oracle9i Database Release 2）。也可以在字符串处理包中定义自己的类型并引用它。
假设，我希望在字符串包中增加一个函数：输入分隔符分开的字符串（如逗号分开的字符串，或者DBMS_UTILITY.FORMAT_CALL_STACK返回的以换行符分开的字符串），返回以分隔符之间的字符串为元素的一个集合。
A、使用ORACLE定义的通用集合类型，比如那些DBMS_SQL包中的类型。下面是这种方法的函数定义部分。
| CREATE OR REPLACE PACKAGE string_pkg IS SUBTYPE maxvarchar2_t IS VARCHAR2 ( 32767 ); FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER ) RETURN VARCHAR2;
FUNCTION list_to_collection ( string_in IN VARCHAR2 , delim_in IN VARCHAR2 DEFAULT ' , ' ) RETURN DBMS_SQL.varchar 2a ; END string_pkg; / |
B、另一种方法，在包中定义自己的集合类型，然后在函数接口中使用。
| CREATE OR REPLACE PACKAGE string_pkg IS SUBTYPE maxvarchar2_t IS VARCHAR2 ( 32767 ); TYPE maxvarchar2_aat IS TABLE OF maxvarchar2_t INDEX BY PLS_INTEGER; FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER ) RETURN VARCHAR2;
FUNCTION list_to_collection ( string_in IN VARCHAR2 , delim_in IN VARCHAR2 DEFAULT ' , ' ) RETURN maxvarchar2_aat; END string_pkg; / |
第一种选择（使用DBMS_SQL.VARCHAR 2A ）节省了一些代码，适用于已经使用了DBMS_SQL类型的那些程序。它的缺陷是引入了外部依赖(external dependency)，但是因为ORACLE数据库所有版本里都提供DBMS_SQL包，也不必为这种外部依赖感到过分担心。
尽管如此，一般来说，最好避免这种依赖，或者使其影响最小化。第二种选择使你定义了一个完全独立的应用。
3、怎样编写程序使之同时适用于VARCHAR2变量和CLOB变量？
解答：
要点：仅当需要使用超过32767字符长度的字符串时，才使用CLOB。
假设数据库版本是Oracle9i Database Release 2，通常应该使用同样的代码，这是因为ORACLE数据库的新版本允许内置字符函数处理CLOB变量，同VARCHAR2变量一样。在Oracle9i Database Release 2及以上版本，在许多环境下，可以认为处理VARCHAR2和CLOB的函数是可替换的。参考《Oracle Database Application Developer's Guide—Large Objects》的“PL/SQL Semantics for LOBs”部分来了解ORACLE如何隐式转换VARCHAR2和CLOB，和如何将许多标准的VARCHAR2内置程序直接应用于CLOB。
同时由于CLOB比VARCHAR2字符串大的多，逻辑上可以认为VARCHAR2类型是CLOB的子类型，因此，适用于CLOB的函数也应当能够适用于VARCHAR2。
也许有人会说，“那让我们使用CLOB做为字符串函数的标准数据类型好了。”其实，我们也能够建立一个VARCHAR2“外壳”，它仅仅调用CLOB语法来执行（注：意思是抛弃VARCHAR2的处理语法）。然而鉴于CLOB的性能特性曲线，建议仅当确认VARCHAR2(32767)不够用时才使用CLOB。
因此，从性能立场出发，我应该实现CLOB版本的list_to_collection，输入CLOB，返回CLOB类型的集合。利用拷贝粘贴操作，并将所有出现的VARCHAR2声明都更改为CLOB，来生成这一函数。而且，在CLOB的性能特性得到改善之前，都应该为这种数据类型的变量创建单独处理的程序。
4、如何消除写死的(hard-coding)字面上的32767？
解答：
现在，还有一个问题：处理CLOB变量过程中，在调用SUBSTR时写死了32767的最大长度（如SUBSTR(vclob,1,32767)）。怎么消除它呢？
这可以通过在包的声明部分添加一个常量声明的办法来绕开这个问题。
CREATE OR REPLACE PACKAGE string_pkg
IS
c_max_varchar2_len CONSTANT PLS_INTEGER := 32767;
SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);……
现在可以在调用SUBSTR时使用这个常量。
16 FOR clob_index IN 1 .. l_clobs.COUNT
17 LOOP
18 l _return ( clob_index ) :=
19 SUBSTR ( l_clobs ( clob_index )
20 , 1
21 , c_max_varchar2_len );
22 END LOOP ;
这样程序更完美了，但在包的声明部分依然存在多个数字32767。
如果完全不能容忍代码中出现这种值的重复，那么还可以采用条件编译(conditional compilation)的方法。
ALTER SESSION SET plsql_ccflags = 'max_varchar2_length:32767'
/
CREATE OR REPLACE PACKAGE string_pkg
IS
SUBTYPE maxvarchar2_t IS VARCHAR2 ( $$max_varchar2_length );
……
--函数实现可以如下所示：
16 FOR clob_index IN 1 .. l_clobs.COUNT
17 LOOP
18 l _return ( clob_index ) :=
19 SUBSTR ( l_clobs ( clob_index )
20 , 1
21 , $$max_varchar2_length );
22 END LOOP ;
结论：
我们都应当关注创建可重用程序的机会，特别是便利的字符串函数。在创建这些泛型应用的过程中，我们应当竭尽全力地使代码易于维护（避免硬编码(hard-coding)和多余的语法），同时还要确保这些代码足够有效。
代码列表 2: 完整的字符串处理包string_fun
| ALTER SESSION SET plsql_ccflags = 'max_varchar2_length:32767' /
CREATE OR REPLACE PACKAGE string_fun IS SUBTYPE maxvarchar2_t IS VARCHAR2 ( $$max_varchar2_length );
TYPE maxvarchar2_aat IS TABLE OF maxvarchar2_t INDEX BY PLS_INTEGER;
TYPE clob_aat IS TABLE OF CLOB INDEX BY PLS_INTEGER;
FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER , inclusive_in IN BOOLEAN := TRUE ) RETURN VARCHAR2;
FUNCTION list_to_collection ( string_in IN VARCHAR2 , delim_in IN VARCHAR2 DEFAULT ',' ) RETURN maxvarchar2_aat;
FUNCTION cloblist_to_collection ( string_in IN CLOB , delim_in IN VARCHAR2 DEFAULT ',' ) RETURN clob_aat; END string_fun; /
CREATE OR REPLACE PACKAGE BODY string_fun IS FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER , inclusive_in IN BOOLEAN := TRUE ) RETURN VARCHAR2 IS c_last CONSTANT PLS_INTEGER := LENGTH ( string_in ); l_start PLS_INTEGER; l_numchars PLS_INTEGER := LEAST ( ABS ( end_in ), c_last ) - ABS ( start_in ) + 1; BEGIN IF string_in IS NULL OR ( start_in < 0 AND end_in > 0 ) OR ( start_in > 0 AND end_in < 0 ) OR ( start_in < 0 AND end_in > start_in ) OR ( start_in > 0 AND end_in < start_in ) THEN RETURN NULL; ELSE IF start_in < 0 THEN l_start := GREATEST ( end_in, -1 * LENGTH ( string_in )); ELSIF start_in = 0 THEN l_start := 1; l_numchars := ABS ( end_in ) - ABS ( l_start ) + 1; ELSE l_start := start_in; END IF;
IF NOT NVL ( inclusive_in, FALSE ) THEN l_start := l_start + 1; l_numchars := l_numchars - 2; END IF;
RETURN ( SUBSTR ( string_in, l_start, l_numchars )); END IF; END betwnstr;
FUNCTION cloblist_to_collection ( string_in IN CLOB , delim_in IN VARCHAR2 DEFAULT ',' ) RETURN clob_aat IS l_loc PLS_INTEGER; l_row PLS_INTEGER := 1; l_startloc PLS_INTEGER := 1; l_return clob_aat; BEGIN IF string_in IS NOT NULL THEN LOOP -- Get the next item. l_loc := INSTR ( string_in, delim_in, l_startloc );
IF l_loc = l_startloc THEN l_return ( l_row ) := NULL; ELSIF l_loc = 0 THEN l_return ( l_row ) := SUBSTR ( string_in, l_startloc ); ELSE l_return ( l_row ) := SUBSTR ( string_in, l_startloc, l_loc - l_startloc ); END IF;
-- Was that the last one? IF l_loc = 0 THEN EXIT; ELSE l_startloc := l_loc + 1; l_row := l_row + 1; END IF; END LOOP ; END IF;
RETURN l_return; END cloblist_to_collection;
FUNCTION list_to_collection ( string_in IN VARCHAR2 , delim_in IN VARCHAR2 DEFAULT ',' ) RETURN maxvarchar2_aat IS l_loc PLS_INTEGER; l_row PLS_INTEGER := 1; l_startloc PLS_INTEGER := 1; l_return maxvarchar2_aat; BEGIN IF string_in IS NOT NULL THEN LOOP -- Get the next item. l_loc := INSTR ( string_in, delim_in, l_startloc );
IF l_loc = l_startloc THEN l_return ( l_row ) := NULL; ELSIF l_loc = 0 THEN l_return ( l_row ) := SUBSTR ( string_in, l_startloc ); ELSE l_return ( l_row ) := SUBSTR ( string_in, l_startloc, l_loc - l_startloc ); END IF;
-- Was that the last one? IF l_loc = 0 THEN EXIT; ELSE l_startloc := l_loc + 1; l_row := l_row + 1; END IF; END LOOP ; END IF;
RETURN l_return; END list_to_collection; END string_fun; / |
附注：
尽管必须承认CLOB比VARCHAR2要慢，我想我依然应该展示怎么实现CLOB版本的“list to collection”，紧接着是VARCHAR2版本的实现方式。这是一种避免程序逻辑重复的有用技术，你完全可以把它应用到你自己的情形中。当然，避免代码冗余通常不比避免性能瓶径更重要。（原文为：avoiding code redundancy usually cannot trump performance bottlenecks.）
下面是字符串包的定义部分。
CREATE OR REPLACE PACKAGE string_pkg
IS
---前面的部分，省略
... previous VARCHAR2 elements ...
TYPE clob_aat IS TABLE OF CLOB
INDEX BY PLS_INTEGER;
FUNCTION cloblist_to_collection (
string_in IN CLOB
, delim_in IN VARCHAR2 DEFAULT ','
)
RETURN clob_aat;
END string_pkg;
/
下面来看看cloblist_to_collection函数的定义部分。
FUNCTION cloblist_to_collection (
string_in IN CLOB
, delim_in IN VARCHAR2 DEFAULT ','
)
RETURN clob_aat
IS
l_loc PLS_INTEGER;
l_row PLS_INTEGER := 1;
l_startloc PLS_INTEGER := 1;
l_return clob_aat;
现在必须彻底改造list_to_collection函数，下面是我的实现方式。
1 FUNCTION list_to_collection (
2 string_in IN VARCHAR2
3 , delim_in IN VARCHAR2 DEFAULT ','
4 )
5 RETURN maxvarchar2_aat
6 IS
7 l _clobs clob_aat;
8 l _return maxvarchar2_aat;
9 BEGIN
10 -- Parse the string as a CLOB.
11 l _clobs := cloblist_to_collection (
12 TO_CLOB ( string_in ), delim_in );
13
14 -- Copy the individual items to the string collection.
15 -- Use SUBSTR to avoid VALUE_ERROR exceptions.
16 FOR clob_index IN 1 .. l_clobs.COUNT
17 LOOP
18 l _return ( clob_index ) :=
19 SUBSTR ( l_clobs ( clob_index )
20 , 1
21 , 32767);
22 END LOOP ;
23
24 RETURN l_return;
25* END list_to_collection;
7-8行声明了用于cloblist_to_collection调用的CLOB集合，而另一个集合用于函数的返回。
11-12行将VARCHAR2字符串转化为CLOB类型，然后传递给cloblist_to_collection进行解析，将返回CLOB类型的集合。
16-22行将CLOB集合的内容拷贝到VARCHAR2集合中，使用SUBSTR只截取CLOB的前32767个字符，以避免触发VALUE_ERROR错误。
注意：为避免使用SUBSTR而导致的截去和可能的数据损失，可能需要调整语法以分割那些超过32767字符的个别项目，并将其做为单独项目传递回VARCHAR2集合中。
整个逻辑就是如果传入CLOB，那么由cloblist_to_collection进行正常处理；如果传入VARCHAR2，那么将其转化为CLOB，调用cloblist_to_collection处理，然后将处理完成的结果再次转化为VARCHAR2，这样就节省了处理VARCHAR2字符串的重复代码。
130
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
