|
部门?表的数据结构
/*==============================================================*/
/* Table: qxgl_dept???????????????????????????????????????????? */
/*==============================================================*/
create table qxgl_dept (
?? dept_id????????????? varchar(32)????????? not null,?? --部门id
?? dept_name??????????? varchar(32)????????? not null,--部门名称
?? dept_code??????????? varchar(32)????????? null,? --部门代码
?? dept_parent_id?????? varchar(32)????????? not null,--父部门
?? dept_level?????????? int????????????????? not null,--部门级别
?? dept_id_path???????? varchar(1024)??????? not null,--部门id全路径
?? dept_fullname??????? varchar(1024)??????? not null,--部门全称
?? dept_order_id??????? int????????????????? not null default 0,--部门排序号
?? dept_area??????????? varchar(32)????????? not null,
?? dept_type??????????? varchar(32)????????? not null default '0',
?? dept_linkman???????? varchar(32)????????? null,
?? dept_linkmanphone??? varchar(32)????????? null,
?? dept_email?????????? varchar(32)????????? null,
?? dept_phone?????????? varchar(32)????????? null,
?? dept_fax???????????? varchar(32)????????? null,
?? dept_address???????? varchar(255)???????? null,
?? dept_postalcode????? varchar(32)????????? null,
?? dept_introduce?????? varchar(2000)??????? null,
?? dept_create_time???? timestamp??????????? null default getdate(),
?? constraint PK_QXGL_DEPT primary key (dept_id),
?? constraint AK_KEY_2_QXGL_DEP unique (dept_name)
)
go
?
--首先在sqlserver2000中创建用户函数
--查询父单位的函数
CREATE Function GetParentDept(@dept_id varchar(32),@Level int)
Returns @Parents Table([dept_id] varchar(32),[dept_name] varchar(128),[dept_parent_id] varchar(32))
As
Begin
??? If @Level < 1?? --如果参数@Level小于“1”，则得到所有父单位
??????? Begin???????
??????????? Insert @Parents
??????????????? Select dept_id,dept_name,dept_parent_id
??????????????? From [qxgl_dept]
??????????????? Where [dept_id] = (Select [dept_parent_id]
??????????????? From [qxgl_dept] Where [dept_id]=@dept_id)???????
???????????
??????????? While @@ROWCOUNT > 0
??????????????? Begin????????????????????
??????????????????? Insert @Parents
??????????????????????? Select B.dept_id,B.dept_name,B.dept_parent_id
??????????????????????? From @Parents A Inner Join [qxgl_dept] B
??????????????????????? On A.[dept_parent_id]=B.[dept_id]
??????????????????????? Where B.[dept_parent_id] Not In
??????????????????????? (Select Distinct [dept_parent_id] From @Parents)
??????????????? End
??????? End
??? Else
??????? Begin
??????????? Set @level = @Level -1
???????????
??????????? Insert @Parents
??????????????? Select dept_id,dept_parent_id
??????????????? From [qxgl_dept]
??????????????? Where [dept_id] = (Select [dept_parent_id]
??????????????? From [qxgl_dept] Where [dept_id]=@dept_id)???????????
???????????
??????????? While @@ROWCOUNT > 0 And @level > 0
??????????????? Begin
??????????????????? Set @level = @Level -1
??????????????????? Insert @Parents
??????????????????????? Select B.dept_id,B.dept_parent_id
??????????????????????? From @Parents A Inner Join [qxgl_dept] B
??????????????????????? On A.[dept_parent_id]=B.[dept_id]
??????????????????????? Where B.[dept_parent_id] Not In
??????????????????????? (Select Distinct [dept_parent_id] From @Parents)
??????????????? End
??????? End
Return
End
--查询子单位的函数
CREATE Function GetChildDept(@dept_id varchar(32),@Level int)
Returns @Child Table([dept_id] varchar(32),[dept_name] varchar(64),[dept_parent_id] varchar(32))
As
Begin
??? If @Level < 1? --如果参数@Level小于“1”，则得到所有子单位
??????? Begin???????????
??????????? Insert @Child
??????????????? Select dept_id,dept_parent_id
??????????????? From [qxgl_dept] Where [dept_parent_id] = @dept_id
??????????? While @@ROWCOUNT > 0
??????????????? Begin????????????????????
??????????????????? Insert @Child
??????????????????????? Select B.dept_id,B.dept_parent_id
??????????????????????? From @Child A Inner Join [qxgl_dept] B
??????????????????????? On A.[dept_id]=B.[dept_parent_id]
??????????????????????? Where B.[dept_parent_id] Not In
??????????????????????? (Select Distinct [dept_parent_id] From @Child)
??????????????? End
??????? End
??? Else
??????? Begin
??????????? Set @level = @Level -1
??????????? Insert @Child
??????????????? Select dept_id,dept_parent_id
??????????????? From [qxgl_dept] Where [dept_parent_id]=@dept_id
??????????? While @@ROWCOUNT > 0 And @level > 0
??????????????? Begin
??????????????????? Set @level = @Level -1
??????????????????? Insert @Child
??????????????????????? Select B.dept_id,B.dept_parent_id
??????????????????????? From @Child A Inner Join [qxgl_dept] B
??????????????????????? On A.[dept_id]=B.[dept_parent_id]
??????????????????????? Where B.[dept_parent_id] Not In
??????????????????????? (Select Distinct [dept_parent_id] From @Child)
??????????????? End
??????? End???
??? Return
End
--查询
select * from GetParentDept('402881601b553a1d011b553d31c2000b',0)
select * from GetChildDept('dept111122223333444455556666dept',1)
--删除函数drop Function GetParentDeptdrop Function GetChildDept
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
