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);

 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;
View Code

3.创建 Function COMPARETWOSTR(IN_STR IN VARCHAR2) ；

 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;
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)；

 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
View Code

5.最终结果

posted @ 2017-06-15 17:23  linbo.yang  阅读(609)  评论(0)  编辑  收藏  举报