语言国际化要求,开发上要求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(参数--信息内容,参数--分割符)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--分割函数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;
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.分割函数测试如下:
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SELECT * FROM TABLE (CAST (StringSplit ('1|12|123|1234||12345|', '|') AS stringarrary));
测试 结果:
COLUMN_VALUE
1
12
123
1234
12345
OK。
然后封装函数StringFormat:
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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;
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 ('{', TO_CHAR (i)), '}'),
25 params (i + 1)
26 );
27 i := i + 1;
28 END LOOP;
29 END IF;
30
31 RETURN returnvalue;
32 END stringreplace;
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SELECT StringFormat('条码{0}当前工站在{1}!', '123456|FCT', '|') FROM DUAL;
测试结果:
条码123456当前工站在FCT!