dawei
副标题#e#
? 语言国际化要求，开发上要求Oracle数据库SQL中对应的返回信息-Message，实现一个通用函数调用，比如：提示信息内容：条码123456当前工站在FCT！”，即通用的信息内容格式化标准为：“条码{0}当前工站在{1}！”。
在C#代码中可以通过String.Format函数占位符替换的模式实现字符串格式化输出，Oracle中暂无此函数，所以可参考String.Format反编译的相应的逻辑去实现即可；
? ? ? ?信息内容中多个参数，是以占位符{}来表示，多个参数则用数组；通过自定义数组类型（即Table表结构类型）.如下代码所示：?
1?CREATE OR REPLACE TYPE stringarrary IS TABLE OF VARCHAR2 (4000);
? ? ? ?FUNCTION—StringSplit（参数–信息内容，参数–分割符）
--分割函数CREATE OR REPLACE FUNCTION StringSplit (
SOURCE VARCHAR2,--信息内容
spliter VARCHAR2--分割符
)
RETURN stringarrary
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
returnvalue stringarrary := stringarrary ();
BEGIN
IF (spliter IS NULL) OR (SOURCE IS NULL)
THEN
returnvalue.EXTEND;
returnvalue (1) := SOURCE;
ELSE
len := LENGTH (SOURCE);
len1 := LENGTH (spliter);
WHILE j < len
LOOP
j := INSTR (SOURCE,spliter,i);
IF j = 0
THEN
j := len;
str := SUBSTR (SOURCE,i);
returnvalue.EXTEND;
returnvalue (returnvalue.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (SOURCE,i,j - i);
i := j + len1;
returnvalue.EXTEND;
returnvalue (returnvalue.COUNT) := str;
END IF;
END LOOP;
END IF;
RETURN returnvalue;
END stringsplit;
?1?CREATE?OR?REPLACE?FUNCTION?stringsplit?(SOURCE?VARCHAR2,?spliter?VARCHAR2)
?2????RETURN?stringarrary
?3?IS
?4????j?????????????INT?????????????:=?0;
?5????i?????????????INT?????????????:=?1;
?6????len???????????INT?????????????:=?0;
?7????len1??????????INT?????????????:=?0;
?8????str???????????VARCHAR2?(4000);
?9????returnvalue???stringarrary????:=?stringarrary?();
10?BEGIN
11????IF?(spliter?IS?NULL)?OR?(SOURCE?IS?NULL)
12????THEN
13???????returnvalue.EXTEND;
14???????returnvalue?(1)?:=?SOURCE;
15????ELSE
16???????len?:=?LENGTH?(SOURCE);
17???????len1?:=?LENGTH?(spliter);
18?
19???????WHILE?j?<?len
20???????LOOP
21??????????j?:=?INSTR?(SOURCE,?spliter,?i);
22?
23??????????IF?j?=?0
24??????????THEN
25?????????????j?:=?len;
26?????????????str?:=?SUBSTR?(SOURCE,?i);
27?????????????returnvalue.EXTEND;
28?????????????returnvalue?(returnvalue.COUNT)?:=?str;
29?
30?????????????IF?i?>=?len
31?????????????THEN
32????????????????EXIT;
33?????????????END?IF;
34??????????ELSE
35?????????????str?:=?SUBSTR?(SOURCE,?i,?j?-?i);
36?????????????i?:=?j?+?len1;
37?????????????returnvalue.EXTEND;
38?????????????returnvalue?(returnvalue.COUNT)?:=?str;
39??????????END?IF;
40???????END?LOOP;
41????END?IF;
42?
43????RETURN?returnvalue;
44?END?stringsplit;
???
? ? ? 1.分割函数测试如下：??????
SELECT * FROM TABLE (CAST (StringSplit (‘1|12|123|1234||12345|‘,‘|‘) AS stringarrary));
??????? 结果：????
???????????????COLUMN_VALUE
?????????????? 1
?????????????? 12
?????????????? 123
???????????????1234
????
???????????????12345
??????? OK。
然后封装函数StringFormat：
CREATE OR REPLACE FUNCTION StringFormat (SOURCE VARCHAR2,param VARCHAR2,spliter VARCHAR2)
RETURN VARCHAR2
IS
i INT := 0;
len INT := 0;
params stringarrary := stringarrary ();
returnvalue VARCHAR2 (4000);
BEGIN
params := stringsplit (param,spliter);
IF params.COUNT > 0
THEN
len := params.COUNT;
returnvalue := SOURCE;
WHILE i < len
LOOP
returnvalue :=
REPLACE (returnvalue,CONCAT (CONCAT (‘{‘,TO_CHAR (i)),‘}‘),params (i + 1));
i := i + 1;
END LOOP;
END IF;
RETURN returnvalue;
END stringreplace;
1?CREATE?OR?REPLACE?FUNCTION?stringreplace (
2?SOURCE?VARCHAR2,
3?param?VARCHAR2,
4?spliter?VARCHAR2
5?)
6?RETURN?VARCHAR2
7?IS
8?i?INT?:=?0;
9?len?INT?:=?0;
10?params stringarrary :=?stringarrary ();
11?returnvalue?VARCHAR2?(4000);
12?BEGIN
13?params :=?stringsplit (param,spliter);
14?
15?IF?params.COUNT?>?0
16?THEN
17?len?:=?params.COUNT;
18?returnvalue :=?SOURCE;
19?
20?WHILE?i?<?len
21?LOOP
22?returnvalue :=
23?REPLACE?(returnvalue,
24?CONCAT (CONCAT (‘{‘,?‘}‘),
25?params (i?+?1)
26?);
27?i :=?i?+?1;
28?END?LOOP;
29?END?IF;
30?
31?RETURN?returnvalue;
32?END?stringreplace;
?? ? ? 2.测试Oracle—StringFormat函数了。
SELECT StringFormat(‘条码{0}当前工站在{1}！‘,‘123456|FCT‘,‘|‘) FROM DUAL;
1?select?stringreplace(‘{0}+{1}={0}{1}‘,‘吱吱|YY‘,‘|‘)?from?dual;
? ? ? ? 测试结果：
#p#副标题#e##p#分页标题#e#
? ? ? ? ??条码123456当前工站在FCT！?
