oracle取俩个字符串的差集思路：
--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 ;
View Code
2.创建 FUNCTION REMOVESAMESTR(OLDSTR VARCHAR2, SIGN VARCHAR2);
1CREATE OR REPLACE FUNCTION REMOVESAMESTR(OLDSTR VARCHAR2, SIGN VARCHAR2)
2RETURN VARCHAR2IS
3STR 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);
12BEGIN
13-- 空字符串
14IF OLDSTR IS NULL THEN
15RETURN('');
16END IF;
17--字符串太长
18IF LENGTH(OLDSTR) >1000THEN
19RETURN(OLDSTR);
20END IF;
21STR := OLDSTR;
22
23  CURRENTINDEX :=0;
24  STARTINDEX  :=0;
25
26  LOOP
27    CURRENTINDEX := CURRENTINDEX +1;
28    ENDINDEX    := INSTR(STR, SIGN, 1, CURRENTINDEX);
29IF (ENDINDEX <=0) THEN
30EXIT;
31END IF;
32
33    ARR(CURRENTINDEX) := TRIM(SUBSTR(STR,
34                                      STARTINDEX +1,
35                                      ENDINDEX - STARTINDEX -1));
36    STARTINDEX := ENDINDEX;
37END LOOP;
38
39--取最后⼀个字符串:
40  ARR(CURRENTINDEX) := SUBSTR(STR, STARTINDEX +1, LENGTH(STR));
41
42--去掉重复出现的字符串:
43FOR I IN1 .. CURRENTINDEX -1 LOOP
44FOR J IN I +1 .. CURRENTINDEX LOOP
45IF ARR(I) = ARR(J) THEN
46        ARR(J) :='';
47END IF;
48END LOOP;
49END LOOP;
50
51STR :='';
52FOR I IN1 .. CURRENTINDEX LOOP
53IF ARR(I) IS NOT NULL THEN
54STR :=STR||SIGN|| ARR(I);
55--数组置空:
56      ARR(I) :='';
57END IF;
58END LOOP;
59--去掉前⾯的标识符:
60  RESULT := SUBSTR(STR, 2, LENGTH(STR));
61RETURN(RESULT);
62END REMOVESAMESTR;
View Code
3.创建 Function COMPARETWOSTR(IN_STR IN VARCHAR2) ；
1CREATE OR REPLACE FUNCTION COMPARETWOSTR(IN_STR IN VARCHAR2)
2RETURN VARCHAR2AS
3  RESULTSTR VARCHAR2(4000);
4BEGIN
5SELECT LISTAGG(STR, ',') WITHIN GROUP(ORDER BY STR)
6INTO RESULTSTR
7FROM (SELECT LEVEL STR
8FROM DUAL
9          CONNECT BY LEVEL<=17
10          MINUS
11SELECT TO_NUMBER(COLUMN_VALUE) STR
oracle字符串转数组12FROM TABLE(SPLITSTR(IN_STR, ',')));
13RETURN RESULTSTR;
14 EXCEPTION
15WHEN OTHERS THEN
16    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM ||
17                          DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
18END COMPARETWOSTR;
View Code
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)；
1CREATE 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    **********************************************************************/
9RETURN str_split
10    PIPELINED
11AS
12    v_length  NUMBER := LENGTH(p_string);
13    v_start    NUMBER :=1;
14    v_index    NUMBER;
15BEGIN
16WHILE(v_start <= v_length)
17    LOOP
18        v_index := INSTR(p_string, p_delimiter, v_start);
19
20IF v_index =0
21THEN
22PIPE ROW(SUBSTR(p_string, v_start));
23            v_start := v_length +1;
24ELSE
25PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
26            v_start := v_index +1;
27END IF;
28END LOOP;
29
30RETURN;
31END splitstr;
32
33
34
View Code
5.最终结果

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。