1 /**
2 * 创建演示表
3 */
4 create table tab_test
5 (
6 t_id number,
7 t_name varchar2(50),
8 t_age number,
9 t_sex varchar2(2)
10 )
11 ;
12
13 -- 插入测试数据
14 insert into tab_test values (1, 'a', '18', '男');
15 insert into tab_test values (2, 'b', '20', '女');
16 insert into tab_test values (3, 'c', '21', '男');
17 commit;
18
19 select * from tab_test;
1 /**
2 * 定义拼接多列字段值的函数
3 */
4 create or replace function f_split_joint_multicolumn(input varchar2)
5 return varchar2
6 parallel_enable
7 aggregate using typesumvarchar;
8
9 /**
10 * 使用Oracle自定义聚集函数创建自定义类型
11 */
12 create or replace type typesumvarchar as object
13 (
14 sum varchar2(1000),
15 static function odciaggregateinitialize(sctx in out typesumvarchar)
16 return number,
17 member function odciaggregateiterate(self in out typesumvarchar,
18 value in varchar2) return number,
19 member function odciaggregateterminate(self in typesumvarchar,
20 returnvalue out varchar2,
21 flags in number)
22 return number,
23 member function odciaggregatemerge(self in out typesumvarchar,
24 ctx2 in typesumvarchar) return number
25 )
26 /
27
28
29 -- 查询演示
30 select f_split_joint_multicolumn(t.t_sex) from tab_test t;
