通过Oracle函数SQL实现C# String.Format字符串格式化功能
发布时间：2021-02-25 03:16:29 所属栏目：站长百科 来源：网络整理
导读：? 语言国际化要求，开发上要求Oracle数据库SQL中对应的返回信息-Message，实现一个通用函数调用，比如：提示信息内容：条码123456当前工站在FCT
|
副标题[/!--empirenews.page--]
? 语言国际化要求，开发上要求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;
?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 ??????? 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;
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;
SELECT StringFormat(‘条码{0}当前工站在{1}！‘,‘123456|FCT‘,‘|‘) FROM DUAL;
1?select?stringreplace(‘{0}+{1}={0}{1}‘,‘吱吱|YY‘,‘|‘)?from?dual;
? ? ? ? 测试结果： （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
