通过Oracle函数SQL实现C# String.Format字符串格式化功能

副标题#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！?