抛砖引玉一下吧。写个函数来处理表已建好,表名为tab1,两个字段分别是field1和field2首先,创建几个需要的类 SQL> create type t1_num is object(num number); 2 /
Type created
SQL> create type t1_char is object(str varchar2(20)); 2 /
Type created
SQL> create type t2_num is table of t1_num; 2 /
Type created
SQL> create type t2_char is table of t1_char; 2 /
Type created创建函数 SQL> create or replace function func_1(input_string varchar2) return varchar2 as 2 num_flag integer; 3 num_array t2_num := t2_num(); 4 num_array_tmp t2_num := t2_num(); 5 char_array t2_char := t2_char(); 6 result_str varchar2(1000); 7 begin 8 char_array.extend(); 9 char_array(char_array.count) := t1_char(input_string); 10 loop 11 num_flag := 0; 12 for x in (select distinct field2 13 from tab1 14 where field1 in (select str from table(char_array)) 15 and field2 not in (select num from table(num_array))) loop 16 num_array.extend(); 17 num_array(num_array.count) := t1_num(x.field2); 18 num_array_tmp.extend(); 19 num_array_tmp(num_array_tmp.count) := t1_num(x.field2); 20 num_flag := num_flag + 1; 21 end loop; 22 exit when num_flag = 0; 23 char_array.delete; 24 for y in (select distinct field1 25 from tab1 26 where field2 in (select num from table(num_array_tmp))) loop 27 char_array.extend(); 28 char_array(char_array.count) := t1_char(y.field1); 29 end loop; 30 end loop; 31 for z in (select distinct field1 32 from tab1 33 where field2 in (select num from table(num_array)) 34 order by field1) loop 35 result_str := result_str || ',' || z.field1; 36 end loop; 37 return substr(result_str, 2); 38 end; 39 /
Function created执行 SQL> select func_1('A') from dual;
FUNC_1('D') -------------------------------------------------------------------------------- D
BZ,能用一个SQL解决吗?我没想出来,坐等高手
select * from tzw_ls where field1 in( select field1 from tzw_ls where field2 in( select a.field2 from tzw_ls a where a.field1 in('A'))) 这个估计得写存储过程了,还得用到游标等。
好吧,再来个SQL,感觉不如存储过程靠谱和高效 而且,9i好像不支持nocycle的写法 select max(substr(sys_connect_by_path(field1, ','), 2)) from (select field1, rownum rn from (select distinct field1 from tab1 t start with field1 = 'A' connect by nocycle(prior field2 = field2 and field1 != prior field1) or (prior field1 = field1 and field2 != prior field2) order by 1)) start with rn = 1 connect by prior rn = rn - 1
SQL> create type t1_num is object(num number);
2 /
Type created
SQL> create type t1_char is object(str varchar2(20));
2 /
Type created
SQL> create type t2_num is table of t1_num;
2 /
Type created
SQL> create type t2_char is table of t1_char;
2 /
Type created创建函数
SQL> create or replace function func_1(input_string varchar2) return varchar2 as
2 num_flag integer;
3 num_array t2_num := t2_num();
4 num_array_tmp t2_num := t2_num();
5 char_array t2_char := t2_char();
6 result_str varchar2(1000);
7 begin
8 char_array.extend();
9 char_array(char_array.count) := t1_char(input_string);
10 loop
11 num_flag := 0;
12 for x in (select distinct field2
13 from tab1
14 where field1 in (select str from table(char_array))
15 and field2 not in (select num from table(num_array))) loop
16 num_array.extend();
17 num_array(num_array.count) := t1_num(x.field2);
18 num_array_tmp.extend();
19 num_array_tmp(num_array_tmp.count) := t1_num(x.field2);
20 num_flag := num_flag + 1;
21 end loop;
22 exit when num_flag = 0;
23 char_array.delete;
24 for y in (select distinct field1
25 from tab1
26 where field2 in (select num from table(num_array_tmp))) loop
27 char_array.extend();
28 char_array(char_array.count) := t1_char(y.field1);
29 end loop;
30 end loop;
31 for z in (select distinct field1
32 from tab1
33 where field2 in (select num from table(num_array))
34 order by field1) loop
35 result_str := result_str || ',' || z.field1;
36 end loop;
37 return substr(result_str, 2);
38 end;
39 /
Function created执行
SQL> select func_1('A') from dual;
FUNC_1('A')
--------------------------------------------------------------------------------
A,B,C
SQL> select func_1('D') from dual;
FUNC_1('D')
--------------------------------------------------------------------------------
D
BZ,能用一个SQL解决吗?我没想出来,坐等高手
select field1 from tzw_ls where field2 in( select a.field2 from tzw_ls a where a.field1 in('A')))
这个估计得写存储过程了,还得用到游标等。
而且,9i好像不支持nocycle的写法
select max(substr(sys_connect_by_path(field1, ','), 2))
from (select field1, rownum rn
from (select distinct field1
from tab1 t
start with field1 = 'A'
connect by nocycle(prior field2 = field2
and field1 != prior field1)
or (prior field1 = field1 and field2 != prior field2)
order by 1))
start with rn = 1
connect by prior rn = rn - 1
BZ让我长见识了,原来没有用过nocycle, Thanks!!!
BZ让我长见识了,原来没有用过nocycle, Thanks!!!
互相学习嘛