现在要写一个函数,主要功能如下
给定两个字符串,如 oldkey = 'key1;key2;key3;key4' ,newkey = 'key2;key3;key5;key6'
要求给出 newkey 比 oldkey 多出的key值,如 上面多出的就是key5,key6
那么程序返回字符串'key5;key6'.由于本人对于oralce字符串操作不熟,
请各位帮忙,给出解决方案、提供详细解决思路或相关技术方案均可,谢谢!function getnewkeys(oldkeys clob,newkeys clob) return clob
as
addedkeys clob;
begin
...
return addedkeys;
end;
给定两个字符串,如 oldkey = 'key1;key2;key3;key4' ,newkey = 'key2;key3;key5;key6'
要求给出 newkey 比 oldkey 多出的key值,如 上面多出的就是key5,key6
那么程序返回字符串'key5;key6'.由于本人对于oralce字符串操作不熟,
请各位帮忙,给出解决方案、提供详细解决思路或相关技术方案均可,谢谢!function getnewkeys(oldkeys clob,newkeys clob) return clob
as
addedkeys clob;
begin
...
return addedkeys;
end;
参考字符串包含函数
该方法用来比较一个字符串是否包含另一个字符串
CREATE OR REPLACE FUNCTION strContent(str1 IN VARCHAR2, str2 IN VARCHAR2)
RETURN NUMBER IS
Result NUMBER;
sStr VARCHAR2(255);
scharacter VARCHAR2(255);
iIndex NUMBER;
sTemp VARCHAR2(255);
BEGIN
Result := 0;
scharacter := str1;
sStr := str2;
iIndex := INSTR(sStr, ',', 1);
WHILE (iIndex > 0) LOOP
iIndex := INSTR(sStr, ',', 1);
IF (iIndex > 0) THEN
sTemp := SUBSTR(sStr, 0, iIndex - 1);
ELSE
sTemp := sStr;//sTemp是str2拆分后的某一个字符串
END IF;
Result := INSTR(scharacter, sTemp, 1);//检查str1中出现sTemp的位置
IF Result > 0 THEN
RETURN Result;
END IF;
sStr := SUBSTR(sStr, iIndex + 1, LENGTH(sStr));
END LOOP;
RETURN Result;
END strContent;
RETURN clob IS
Result NUMBER;
sStr clob;
scharacter clob;
iIndex NUMBER;
sTemp clob;
resultstr clob;
BEGIN
if str1 is null THEN
return str2;
elsif str2 is null then
return null;
end if;
Result := 0;
scharacter := str1;
sStr := str2;
iIndex := INSTR(sStr, ';', 1);
resultstr := '';
WHILE (iIndex > 0) LOOP
iIndex := INSTR(sStr, ';', 1);
IF (iIndex > 0) THEN
sTemp := SUBSTR(sStr, 0, iIndex - 1);
ELSE
sTemp := sStr;--sTemp是str2拆分后的某一个字符串
END IF;
Result := INSTR(scharacter, sTemp, 1);--检查str1中出现sTemp的位置
IF Result <= 0 THEN
resultstr := resultstr||';'||sTemp;
END IF;
sStr := SUBSTR(sStr, iIndex + 1, LENGTH(sStr));
END LOOP;
RETURN resultstr;
END strContent;
RESULT VARCHAR2(2000);
BEGIN
WITH t1 AS
(SELECT ';' || oldkeys str FROM dual),
t2 AS
(SELECT ';' || newkeys str FROM dual)
SELECT REPLACE(wm_concat(s), ',', ';')
INTO RESULT
FROM (SELECT substr(t2.str,
instr(t2.str, ';', 1, LEVEL) + 1,
decode(instr(t2.str, ';', 1, LEVEL + 1),
0,
length(str) + 1,
instr(t2.str, ';', 1, LEVEL + 1)) - instr(t2.str, ';', 1, LEVEL) - 1) s
FROM dual, t2
CONNECT BY LEVEL <= (SELECT length(str) - length(REPLACE(str, ';')) FROM t2)
MINUS
SELECT substr(t1.str,
instr(t1.str, ';', 1, LEVEL) + 1,
decode(instr(t1.str, ';', 1, LEVEL + 1),
0,
length(str) + 1,
instr(t1.str, ';', 1, LEVEL + 1)) - instr(t1.str, ';', 1, LEVEL) - 1) s
FROM dual, t1
CONNECT BY LEVEL <= (SELECT length(str) - length(REPLACE(str, ';')) FROM t1));
RETURN RESULT;
END;
--测试
SELECT getnewkeys('key1;key2;key3;key4', 'key2;key3;key5;key6') FROM dual;