9i的行列转换
[email protected]> create or replace type StringAggType as object
2 (
3 theString varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT StringAggType )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT StringAggType ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN StringAggType,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT StringAggType,
22 ctx2 IN StringAggType)
23 return number
24 );
25 /Type [email protected]>
[email protected]> create or replace type body StringAggType
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
5 return number
6 is
7 begin
8 sctx := StringAggType( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT StringAggType,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.theString := self.theString || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT StringAggType,
32 ctx2 IN StringAggType)
33 return number
34 is
35 begin
36 self.theString := self.theString || ',' || ctx2.theString;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /Type body [email protected]>
[email protected]> CREATE or replace
2 FUNCTION stringAgg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING StringAggType;
5 /Function created.
调用函数stringAgg时,提示pl/sql 数字或值错误,
17 self.theString := self.theString || ',' || value;应该怎么修改?
[email protected]> create or replace type StringAggType as object
2 (
3 theString varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT StringAggType )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT StringAggType ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN StringAggType,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT StringAggType,
22 ctx2 IN StringAggType)
23 return number
24 );
25 /Type [email protected]>
[email protected]> create or replace type body StringAggType
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
5 return number
6 is
7 begin
8 sctx := StringAggType( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT StringAggType,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.theString := self.theString || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT StringAggType,
32 ctx2 IN StringAggType)
33 return number
34 is
35 begin
36 self.theString := self.theString || ',' || ctx2.theString;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /Type body [email protected]>
[email protected]> CREATE or replace
2 FUNCTION stringAgg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING StringAggType;
5 /Function created.
调用函数stringAgg时,提示pl/sql 数字或值错误,
17 self.theString := self.theString || ',' || value;应该怎么修改?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货