/* 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下面是存储过程&＃xff1a;
create procedure pro_comp_user(&＃64;i_comp_id varchar(60))as--查找组织以及下级组织的所有人员 2004-07-15declare &＃64;ORG_TABLE table( CORG_ID varchar(36), CLEVEL int)declare &＃64;CLEVEL intdeclare &＃64;CPARENT_ID varchar(36)declare &＃64;CCHILD_ID varchar(36)set &＃64;CPARENT_ID &＃61; &＃64;i_comp_idset &＃64;CLEVEL &＃61; 1insert into &＃64;ORG_TABLE(CORG_ID,CLEVEL) values (&＃64;CPARENT_ID,&＃64;CLEVEL)loops:begin insert into &＃64;ORG_TABLE select CCHILD_ID CORG_ID, &＃64;CLEVEL &＃43; 1 from MY_ORG_USER where CPARENT_ID in (select CORG_ID from &＃64;ORG_TABLE where CLEVEL &＃61; &＃64;CLEVEL) and CTYPE &＃61; 1 if &＃64;&＃64;rowcount > 0 begin set &＃64;CLEVEL &＃61; &＃64;CLEVEL &＃43; 1 goto loops end endbeginselect c.CNAME , c.CUSER_NAME --d.CORG_ID , --d.CORG_NAME from &＃64;ORG_TABLE a inner join MY_ORG_USER b on a.CORG_ID &＃61; b.CPARENT_ID inner join MY_USER_LIST c on b.CCHILD_ID &＃61; c.CUSER_ID inner join MY_ORG_LIST d on a.CORG_ID &＃61; d.CORG_IDwhere b.CTYPE &＃61; 0--order by CUSER_NAMEend
京公网安备 11010802041100号