ITPub博客

首页 > 应用开发 > IT综合 > One way for str2table(zt asktom)

One way for str2table(zt asktom)

原创 IT综合 作者:jametong 时间:2005-04-22 12:50:11 0 删除 编辑
Mohammad -- Thanks for the question regarding "list all user(s) group by group_list", version 8.1.7
originally submitted on 27-Jun-2003 10:46 Eastern US time, last updated 21-Apr-2005 12:02 You Asked (Jump to Tom's latest followup)
Hello Tom,

i have the follwing table:

LOGIN_NAME GROUP_LIST
--------------- --------------------------------------------------
Demo 1; 9000; 1000;
Koo 1000;
Feh 1000; 1006; 1021; 1030;
Hene 1006; 1000; 1021;
Jaehrling 1000;
Koell 1000;
Woeh 1000; 1001;


Now I am trying to write a query to get the below results

Demo 1
Demo 9000
Demo 1000
.
..

Could you plaese help me to solve this problem

Best regards
Mohammad
and we said...
oh yuck.  hate it when people do that.  "relational" database... ok

ops$tkyte@ORA817DEV> create or replace type myArray as table of number
2 /

Type created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2table( p_string in varchar2
) return myArray
2 as
3 l_data myArray := myArray();
4 l_string long := p_string;
5 l_n number;
6 begin
7 while (l_string is not null)
8 loop
9 l_n := instr( l_string, ';' );
10 if ( l_n = 0 )
11 then
12 l_n := length(l_string)+1;
13 end if;
14 l_data.extend;
15 l_data(l_data.count) := substr( l_string, 1, l_n-1 );
16 l_string := substr( l_string, l_n+1 );
17 end loop;
18 return l_data;
19 end;
20 /

Function created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop table t;

Table dropped.

ops$tkyte@ORA817DEV> create table t ( login_name varchar2(10), group_list
varchar2(200) );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t values ( 'demo', '1; 9000; 1000;' );

1 row created.

ops$tkyte@ORA817DEV> insert into t values ( 'feh', '1000; 1006; 1021; 1030;' );

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select t.login_name, t2.column_value
2 from t, TABLE(cast( str2table(t.group_list) as myArray )) t2
3 /

LOGIN_NAME COLUMN_VALUE
---------- ------------
demo 1
demo 9000
demo 1000
feh 1000
feh 1006
feh 1021
feh 1030

7 rows selected.


is one way.
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94317/viewspace-796605/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280615