重写Oracle的wm_concat函数，自定义分隔符、排序

        oracle中，wm_concat函数是一个聚合函数，和mysql中的group_concat函数类似，不过group_concat函数比较强大，可以定义分隔符和排序，当然所谓强大是相对的，这里假使我们不知道oracle中的over函数，也不知道listagg函数。

       我们先来看看wm_concat函数能实现什么功能，通俗点==>列传行，如果不明白，请看下面截图(可以看到分隔符默认为','顺序也是杂乱的)

               

       所以，接下来，我们开始重写wm_concat函数(需要注意和需要说明的地方放在代码注释中...)

(1) 因为需要进行排序，首先自定义一个可变数组

?
1
2
-- 定义可变数组，字符串类型，长度32767，存放列值
CREATE OR REPLACE TYPE WYARRAY as TABLE OF VARCHAR(32767) ;

 

(2)自定义排序函数、分隔符函数

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 定义分隔符函数
create or replace function delimiter(colValue  in varchar2,
                                     delimiter in varchar2) return varchar2 is
  rtnValue varchar2(32767);
 
begin
 
  rtnValue := colValue || ' delimiter=>' || delimiter || '; ';
 
  return rtnValue;
 
end delimiter;

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 定义排序函数
create or replace function orderby(colValue in varchar2,
                                   orderby  in varchar2) return varchar2 is
  rtnValue varchar2(32767);
 
begin
 
  rtnValue := colValue || ' orderby=>' || LOWER(orderby) || '; ';
 
  return rtnValue;
 
end orderby;

　

(3) 重定义oracle接口函数、以及接口函数的实现体(实现分隔符和排序的主要代码)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 使用当前用户权限(使用authid current_user，定义type为用户当前用户的权限，举个例子：比如A用户他可以建立表，但是A用户在存储过程中如果建立表可能会提示权限不够，所以需要用authid current_user进行约束)
create or replace type wy_wm_concat authid current_user as object
(
 
--拼接字符串,存放中间值，当然也可以定义为clob，clob会使用临时段，导致临时表空间迅速增大;
--查看wmsys下的function可以发现Oracle10g到oracle11g自带的wm_concat函数的返回类型从clob变成varchar2
  currStr VARCHAR2(32767),
 
--分割字符串
  delimiter VARCHAR2(64),
 
--排序字符串(asc、desc)
  orderby VARCHAR2(64),
 
-- 定义字符串数组
  strArray WYARRAY,
 
-- 初始化接口函数
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat)
    RETURN NUMBER,
 
-- 迭代接口函数
  MEMBER FUNCTION ODCIAGGREGATEITERATE(self     IN OUT wy_wm_concat,
                                       colValue IN VARCHAR2) RETURN NUMBER,
 
-- 并行时字符串合并的接口函数
  MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat,
                                     next wy_wm_concat) RETURN NUMBER,
 
-- oracle终止接口函数
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(self        IN wy_wm_concat,
                                         returnValue OUT VARCHAR2,
                                         flags       IN NUMBER)
    RETURN NUMBER
)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
create or replace type body wy_wm_concat is  --定义函数的body
 
  --初始化函数
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat)
    RETURN NUMBER is
  begin
    init := wy_wm_concat('', ',', 'asc', WYARRAY());
    return ODCICONST.Success;
  END;
 
  -- 字符串拼接，self 为当前聚集函数的指针,用来与前面的计算结果进行关联
  MEMBER FUNCTION ODCIAGGREGATEITERATE(self     IN OUT wy_wm_concat,
                                       colValue IN VARCHAR2) RETURN NUMBER is
   
    tempStr varchar(500);
   
    extendStr varchar(500);
   
    deStr varchar(100);
   
    deLen int default 0;
   
    segStr varchar(500);
   
    --定义一个二维数组
    TYPE varArry IS VARRAY(2) OF VARCHAR2(200);
   
    tempArry varArry := varArry('', '');
   
  begin
   
    if instr(colValue, ' ', 1) > 0 then
      tempStr := substr(colValue, 1, instr(colValue, ' ', 1) - 1);
    else
      tempStr := colValue;
    end if;
   
    --排序和分隔符
    extendStr := REPLACE(colValue, tempStr || ' ');
   
    if instr(extendStr, ' ', 1) > 0 then
     
      tempArry(1) := substr(extendStr, 1, instr(extendStr, ' ', 1) - 1);
     
      tempArry(2) := substr(extendStr, instr(extendStr, ' ', 1));
     
      for i in 1 .. tempArry.count loop
        -- 获取分隔符
        if (tempArry(i) is not null) and
           (instr(tempArry(i), 'delimiter=>') > 0) THEN
         
          deStr := 'delimiter=>';
         
          deLen := length(deStr);
         
          segStr := substr(trim(tempArry(i)),
                           instr(trim(tempArry(i)), deStr) + deLen);
         
          self.delimiter := SUBSTR(segStr, 1, instr(segStr, ';', -1) - 1);
        END IF;
       
        -- 获取排序字符串
        if tempArry(i) is not null and
           (instr(tempArry(i), 'orderby=>') > 0) THEN
         
          deStr := 'orderby=>';
         
          deLen := length(deStr);
         
          segStr := substr(trim(tempArry(i)),
                           instr(trim(tempArry(i)), deStr) + deLen);
         
          self.orderby := SUBSTR(segStr, 1, instr(segStr, ';', -1) - 1);
         
        END IF;
       
      end loop;
     
    end if;
   
    -- 存放入数组
    self.strArray.extend;
   
    self.strArray(self.strArray.count) := tempStr;
   
    return ODCICONST.Success;
  END;
 
  --并行操作是用来合并两个聚集函数的两个不同的指针对应的结果
  MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat,
                                     next wy_wm_concat) RETURN NUMBER is
  begin
   
    -- 将next数组中元素全部放入self指针对应的数组中
    for i in 1 .. next.strArray.count loop
     
      self.strArray.extend;
     
      self.strArray(self.strArray.count) := next.strArray(i);
     
    end loop;
   
    return ODCICONST.Success;
  END;
 
  -- 终止函数，返回结果
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(self        IN wy_wm_concat,
                                         returnValue OUT VARCHAR2,
                                         flags       IN NUMBER) RETURN NUMBER IS
    temp_rtnValue varchar2(32767);
   
  BEGIN
    -- 排序
    if INSTR(self.orderby, 'desc') > 0 THEN
     
      for x in (select column_value
                  from Table(self.strArray)
                 order by 1 DESC) loop
       
        temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value;
       
      end loop;
    ELSE
      for x in (select column_value from Table(self.strArray) order by 1 ASC) loop
       
        temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value;
       
      end loop;
     
    END IF;
   
    returnValue := ltrim(temp_rtnValue, self.delimiter);
   
    return ODCICONST.Success;
  END;
 
END;

　

(4)自定义聚集函数

?
1
2
3
-- 定义聚集函数(未开启并行计算功能)
create or replace function wy_concat(colValue  VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING wy_wm_concat;

　　

  至此，主要的代码已经全部奉上，看看运行效果，如下截图：

 

  ①看看调用的默认情况(分隔符默认是逗号，排序默认是升序，在初始化函数中如此定义的)

  

   ②自定义分隔符(利用分隔符函数将分隔符定义为*)

  

 

   ③降序排序

   

 

    ④去重，为了可以使用wm_concat自带的去重函数，所以在自定义分隔符和排序函数时，实质是实用了字符串处理(如果你觉得处理字符串麻烦，可以自定义 type... as object ，在使用的时候可以很方便，不会用的童鞋可以私下问)

   

   

posted @   勇者归来  阅读(26786)  评论(9)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 零经验选手，Compose 一天开发一款小游戏！
· 通过 API 将Deepseek响应流式内容输出到前端
· 因为Apifox不支持离线，我果断选择了Apipost！
点击右上角即可分享
微信分享提示