insert into node values( '00000001','广东分公司','00000000');
insert into node values( '00000002','四川分分公司','00000000');
insert into node values( '00000005','云南分分公司','00000000');
insert into node values( '00000003','广州办事处','00000001');
insert into node values( '00000004','成都办事处','00000002');
insert into node values( '00000006','昆明办事处','00000005');
create table USERORDER
(
NODECODE CHAR(8),
USERCODE CHAR(8),
DEALRATE INTEGER
);
/*
USERORDER 订单审核人表：NODECODE网点编号(与NODE表的NODECODE关联)，USERCODE用户编号，DEALRATE 等级
*/
insert into USERORDER values( '00000000','A1',1);
insert into USERORDER values( '00000001','B1',1);
insert into USERORDER values( '00000001','B2',2);
insert into USERORDER values( '00000002','C1',1);
insert into USERORDER values( '00000002','C2',2);
insert into USERORDER values( '00000003','D1',1);
insert into USERORDER values ('00000004','D2',1);
insert into USERORDER values( '00000006','F1',1);
select node,nodecode,usercode,dealrate from
(select b.nodecode nodecode ,a.nodecode node , a.usercode usercode ,a.dealrate dealrate from USERORDER a ,node b
where a.nodecode = b.nodecode
and b.nodecode<>'00000000'
and nodewith='00000000'
union
select b.nodecode nodecode ,a.nodecode node ,a.usercode usercode ,a.dealrate dealrate from USERORDER a ,node b
where a.nodecode=b.nodewith
and b.nodecode<>'00000000'
and nodewith='00000000'
union
select b.nodecode nodecode ,a.nodecode node ,a.usercode usercode ,a.dealrate dealrate from USERORDER a ,node b
where a.nodecode=(select nodecode from node c where c.nodewith=b.nodecode )
and b.nodecode<>'00000000'
and nodewith='00000000')
order by nodecode ,node desc ,dealrate
create or replace package RefCursor is
type t_RefCursor is ref cursor;
end RefCursor;
create or replace procedure test
as
v_cur RefCursor.t_RefCursor;
v_count varchar2(8);
v_level number;
v_sql varchar2(4000);
v_sql1 varchar2(4000);
i number;
begin
v_sql:='create or replace view aaa as select '
open v_cur for 'select b.level,count(1)
from USERORDER a left join (select level,NODECODE from NODE connect by prior NODECODE=NODEWITH) b
on a.NODECODE=b.NODECODE group by b.level order by level desc';
open v_cur;
fetch v_cur into v_level,v_count;
while v_cur %found loop
for i in 1..v_count loop
v_sql1:=v_sql1||' ,max(case when level='||v_level||' and DEALRATE='||v_count||' then USERCODE else null end) col_'||v_level||'_'||i;
exit when i=v_count;
end loop;
fetch v_cur into v_level,v_count;
end loop;
close v_cur;
v_sql1:=v_sql1||' from (select b.level,a.DEALRATE,a.USERCODE
from USERORDER a left join (select level,NODECODE from NODE connect by prior NODECODE=NODEWITH) b
on a.NODECODE=b.NODECODE) tab'
execute immediate v_sql||substr(v_sql1,2);
end;
/
