oracle 取俩个字符串的差集
思路:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--funcation RemoveSameStr(in_str,splitStr) ;用于去除重复值 ; SELECT b.memberid, RemoveSameStr(wm_concat(b.productidlist),',') AS productidlist FROM BASE_ACCOUNT_BILL_GROUP b WHERE b.grouptype =3 GROUP BY b.memberid ;
2.创建 FUNCTION REMOVESAMESTR(OLDSTR VARCHAR2, SIGN VARCHAR2);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE OR REPLACE FUNCTION REMOVESAMESTR(OLDSTR VARCHAR2, SIGN VARCHAR2) 2 RETURN VARCHAR2 IS 3 STR VARCHAR2(1000); 4 CURRENTINDEX NUMBER; 5 STARTINDEX NUMBER; 6 ENDINDEX NUMBER; 7 8 TYPE STR_TYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; 9 ARR STR_TYPE; 10 11 RESULT VARCHAR2(1000); 12 BEGIN 13 -- 空字符串 14 IF OLDSTR IS NULL THEN 15 RETURN(''); 16 END IF; 17 --字符串太长 18 IF LENGTH(OLDSTR) > 1000 THEN 19 RETURN(OLDSTR); 20 END IF; 21 STR := OLDSTR; 22 23 CURRENTINDEX := 0; 24 STARTINDEX := 0; 25 26 LOOP 27 CURRENTINDEX := CURRENTINDEX + 1; 28 ENDINDEX := INSTR(STR, SIGN, 1, CURRENTINDEX); 29 IF (ENDINDEX <= 0) THEN 30 EXIT; 31 END IF; 32 33 ARR(CURRENTINDEX) := TRIM(SUBSTR(STR, 34 STARTINDEX + 1, 35 ENDINDEX - STARTINDEX - 1)); 36 STARTINDEX := ENDINDEX; 37 END LOOP; 38 39 --取最后一个字符串: 40 ARR(CURRENTINDEX) := SUBSTR(STR, STARTINDEX + 1, LENGTH(STR)); 41 42 --去掉重复出现的字符串: 43 FOR I IN 1 .. CURRENTINDEX - 1 LOOP 44 FOR J IN I + 1 .. CURRENTINDEX LOOP 45 IF ARR(I) = ARR(J) THEN 46 ARR(J) := ''; 47 END IF; 48 END LOOP; 49 END LOOP; 50 51 STR := ''; 52 FOR I IN 1 .. CURRENTINDEX LOOP 53 IF ARR(I) IS NOT NULL THEN 54 STR := STR || SIGN || ARR(I); 55 --数组置空: 56 ARR(I) := ''; 57 END IF; 58 END LOOP; 59 --去掉前面的标识符: 60 RESULT := SUBSTR(STR, 2, LENGTH(STR)); 61 RETURN(RESULT); 62 END REMOVESAMESTR;
3.创建 Function COMPARETWOSTR(IN_STR IN VARCHAR2) ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE OR REPLACE FUNCTION COMPARETWOSTR(IN_STR IN VARCHAR2) 2 RETURN VARCHAR2 AS 3 RESULTSTR VARCHAR2(4000); 4 BEGIN 5 SELECT LISTAGG(STR, ',') WITHIN GROUP(ORDER BY STR) 6 INTO RESULTSTR 7 FROM (SELECT LEVEL STR 8 FROM DUAL 9 CONNECT BY LEVEL <= 17 10 MINUS 11 SELECT TO_NUMBER(COLUMN_VALUE) STR 12 FROM TABLE(SPLITSTR(IN_STR, ','))); 13 RETURN RESULTSTR; 14 EXCEPTION 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM || 17 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 18 END COMPARETWOSTR;
4.创建 function splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN str_split ;
CREATE OR REPLACE TYPE "STR_SPLIT" IS TABLE OF VARCHAR2 (4000);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) 2 /********************************************************************** 3 * xdshi add blockment 2015-09-21 4 * 通用方法:按列返回分割字符串后的内容 5 * 入参:p_string 分割字符串 6 * p_delimiter 分割符 7 * 返回:每个分割出来的字符串 8 **********************************************************************/ 9 RETURN str_split 10 PIPELINED 11 AS 12 v_length NUMBER := LENGTH(p_string); 13 v_start NUMBER := 1; 14 v_index NUMBER; 15 BEGIN 16 WHILE(v_start <= v_length) 17 LOOP 18 v_index := INSTR(p_string, p_delimiter, v_start); 19 20 IF v_index = 0 21 THEN 22 PIPE ROW(SUBSTR(p_string, v_start)); 23 v_start := v_length + 1; 24 ELSE 25 PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start)); 26 v_start := v_index + 1; 27 END IF; 28 END LOOP; 29 30 RETURN; 31 END splitstr; 32 33 34
5.最终结果