Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql loop
| pl/sql loop [message #10077] |
Wed, 31 December 2003 08:34  |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
M.b. it's not about oracle, how to deal with indexes.
I have next select:
select l.room,i.order
from inventory i,locations l
where i.id=l.id(+)
and result is.
room order
A01 100
A02 100
101
A03 102
A03 103
A04 103
and I need to combine rooms as concatenated strings with the same order.
like this
room order
A01,A02 100
101
A03 102
A03,A04 103
Assuming, I put all in pl/sql table. What loop should be to get this result or
Probably, there is select like this.
thanks
|
|
|
|
| Re: pl/sql loop [message #10082 is a reply to message #10077] |
Wed, 31 December 2003 21:14  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you are using Oracle 9i, you can create a generic aggregate function to concatenate strings, then you can use that function in a select statement. The following example includes the code to create such a function by Tom Kyte.
scott@ORA92> -- test data:
scott@ORA92> SELECT l.room, i.order_col
2 FROM inventory i,locations l
3 WHERE i.id = l.id (+)
4 /
ROOM ORDER_COL
---- ----------
A01 100
A02 100
101
A03 102
A03 103
A04 103
6 rows selected.
scott@ORA92> --
scott@ORA92> --
scott@ORA92> -- code from Tom Kyte:
scott@ORA92> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /
Type created.
scott@ORA92> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total
18 || ','
19 || value;
20 return ODCIConst.Success;
21 end;
22
23 member function ODCIAggregateTerminate(self IN string_agg_type,
24 returnValue OUT varchar2,
25 flags IN number)
26 return number
27 is
28 begin
29 returnValue := ltrim(self.total,',');
30 return ODCIConst.Success;
31 end;
32
33 member function ODCIAggregateMerge(self IN OUT string_agg_type,
34 ctx2 IN string_agg_type)
35 return number
36 is
37 begin
38 self.total := self.total || ctx2.total;
39 return ODCIConst.Success;
40 end;
41
42
43 end;
44 /
Type body created.
scott@ORA92> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
scott@ORA92> --
scott@ORA92> --
scott@ORA92> -- usage of stragg function above:
scott@ORA92> COLUMN rooms FORMAT A20
scott@ORA92> SELECT STRAGG (l.room) AS rooms, i.order_col
2 FROM inventory i,locations l
3 WHERE i.id = l.id (+)
4 GROUP BY i.order_col
5 /
ROOMS ORDER_COL
-------------------- ----------
A01,A02 100
101
A03 102
A03,A04 103
|
|
|
|
| Re: pl/sql loop [message #10175 is a reply to message #10077] |
Wed, 07 January 2004 04:36 |
Noam
Messages: 2
Registered: July 2000
|
Junior Member |
|
|
Hi,
I tried doing a similar thing a few months ago (my type is almost identical to the one James posted except for variation in variable names).
The problem is that with parrallel nature of the oracle merge the resulting string is NOT sorted so I can get either of the following two results:
A03,A04 103
A04,A03 103
This becomes an issue when you try to find all the
"A03,A04" combinations and and up missing the ones that are sorted another way. With only two strings concatenated this is not a big deal but when you get to as few 5 strings we have 5!=120 combinations and it gets really messy.
Any suggestions on how to get the result SORTED?
Thanks,
Noam
|
|
|
|
| Re: pl/sql loop [message #10188 is a reply to message #10175] |
Wed, 07 January 2004 21:31 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is another method, that has the disadvantages of being less efficient and less flexible, but has the advantages of ordering the concatentated values and working in 8i as well as 9i. It is a modification of another one of Tom Kyte's older methods. I added the order by within the function.
scott@ORA92> -- test data:
scott@ORA92> SELECT l.room, i.order_col
2 FROM inventory i,locations l
3 WHERE i.id = l.id (+)
4 /
ROOM ORDER_COL
---- ----------
A01 100
A02 100
101
A03 102
A03 103
A04 103
6 rows selected.
scott@ORA92> -- function:
scott@ORA92> CREATE OR REPLACE FUNCTION concatenate
2 (p_key_name IN VARCHAR2,
3 p_key_value IN VARCHAR2,
4 p_col_to_concat IN VARCHAR2,
5 p_table_name IN VARCHAR2,
6 p_separator IN VARCHAR2 DEFAULT ',')
7 RETURN VARCHAR2
8 AS
9 TYPE weak_ref_cur IS REF CURSOR;
10 v_string VARCHAR2 (4000);
11 v_separator VARCHAR2 ( 3) := NULL;
12 v_value VARCHAR2 (4000);
13 v_cur weak_ref_cur;
14 BEGIN
15 OPEN v_cur FOR
16 'SELECT ' || p_col_to_concat
17 || ' FROM ' || p_table_name
18 || ' WHERE ' || p_key_name || ' = :a'
19 || ' <b>ORDER BY :b</b>'
20 USING p_key_value, p_col_to_concat;
21 LOOP
22 FETCH v_cur INTO v_value;
23 EXIT WHEN v_cur%NOTFOUND;
24 v_string := v_string || v_separator || v_value;
25 v_separator := p_separator;
26 END LOOP;
27 CLOSE v_cur;
28 RETURN v_string;
29 END concatenate;
30 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> -- usage of function:
scott@ORA92> COLUMN rooms FORMAT A20
scott@ORA92> SELECT l.rooms, i.order_col
2 FROM inventory i,
3 (SELECT id, concatenate ('id', id, 'room', 'locations') AS rooms
4 FROM locations
5 GROUP BY id) l
6 WHERE i.id = l.id (+)
7 ORDER BY i.order_col, l.rooms
8 /
ROOMS ORDER_COL
-------------------- ----------
A01,A02 100
101
A03 102
A03,A04 103
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 23:10:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
