/* Table: MY_ORG_LIST */
create table MY_ORG_LIST (
CORG_ID varchar ( 36 ) not null ,
CORG_NAME varchar ( 50 ) null ,
CREMARK varchar ( 1024 ) null ,
CSTATUS int null ,
CORG_TYPE int null ,
constraint PK_MY_ORG_LIST primary key (CORG_ID)
)
go
/* Table: MY_ORG_USER */
create table MY_ORG_USER (
CROW_ID varchar ( 36 ) not null ,
CPARENT_ID varchar ( 36 ) not null ,
CCHILD_ID varchar ( 36 ) not null ,
CTYPE int null ,
CGRADE_ID varchar ( 36 ) null ,
CREF_KEY int null ,
constraint PK_MY_ORG_USER primary key (CROW_ID)
)
go
/* Index: IDX_ORG_USER_RELATION_UQ */
create index IDX_ORG_USER_RELATION_UQ on MY_ORG_USER (
CPARENT_ID,
CCHILD_ID
)
go
/* Table: MY_USER_LIST */
create table MY_USER_LIST (
CUSER_ID varchar ( 36 ) not null ,
CUSER_NAME varchar ( 20 ) null ,
CNAME varchar ( 20 ) null ,
CPASSWORD varchar ( 20 ) null ,
CPASS_STATUS int null ,
CUSER_GRADE int null ,
CSEX varchar ( 4 ) null ,
CREMARK varchar ( 1024 ) null ,
CONGUARD int null ,
CSTATUS int null ,
CWORKFOLDER varchar ( 1024 ) null ,
constraint PK_MY_USER_LIST primary key (CUSER_ID)
)
go
下面是存储过程:
create
procedure
pro_comp_user(
@i_comp_id varchar ( 60 )
)
as
-- 查找组织以及下级组织的所有人员 2004-07-15
declare @ORG_TABLE table
(
CORG_ID varchar ( 36 ),
CLEVEL int
)
declare @CLEVEL int
declare @CPARENT_ID varchar ( 36 )
declare @CCHILD_ID varchar ( 36 )
set @CPARENT_ID = @i_comp_id
set @CLEVEL = 1
insert into @ORG_TABLE
(CORG_ID,CLEVEL) values (@CPARENT_ID,@CLEVEL)
loops:
begin
insert into @ORG_TABLE
select CCHILD_ID CORG_ID,
@CLEVEL + 1
from MY_ORG_USER
where CPARENT_ID in
( select CORG_ID from @ORG_TABLE where CLEVEL = @CLEVEL) and
CTYPE = 1
if @@rowcount > 0
begin
set @CLEVEL = @CLEVEL + 1
goto loops
end
end
begin
select c.CNAME ,
c.CUSER_NAME
-- d.CORG_ID ,
-- d.CORG_NAME
from @ORG_TABLE a
inner join MY_ORG_USER b
on a.CORG_ID = b.CPARENT_ID
inner join MY_USER_LIST c
on b.CCHILD_ID = c.CUSER_ID
inner join MY_ORG_LIST d
on a.CORG_ID = d.CORG_ID
where b.CTYPE = 0
-- order by CUSER_NAME
end
@i_comp_id varchar ( 60 )
)
as
-- 查找组织以及下级组织的所有人员 2004-07-15
declare @ORG_TABLE table
(
CORG_ID varchar ( 36 ),
CLEVEL int
)
declare @CLEVEL int
declare @CPARENT_ID varchar ( 36 )
declare @CCHILD_ID varchar ( 36 )
set @CPARENT_ID = @i_comp_id
set @CLEVEL = 1
insert into @ORG_TABLE
(CORG_ID,CLEVEL) values (@CPARENT_ID,@CLEVEL)
loops:
begin
insert into @ORG_TABLE
select CCHILD_ID CORG_ID,
@CLEVEL + 1
from MY_ORG_USER
where CPARENT_ID in
( select CORG_ID from @ORG_TABLE where CLEVEL = @CLEVEL) and
CTYPE = 1
if @@rowcount > 0
begin
set @CLEVEL = @CLEVEL + 1
goto loops
end
end
begin
select c.CNAME ,
c.CUSER_NAME
-- d.CORG_ID ,
-- d.CORG_NAME
from @ORG_TABLE a
inner join MY_ORG_USER b
on a.CORG_ID = b.CPARENT_ID
inner join MY_USER_LIST c
on b.CCHILD_ID = c.CUSER_ID
inner join MY_ORG_LIST d
on a.CORG_ID = d.CORG_ID
where b.CTYPE = 0
-- order by CUSER_NAME
end