员工管理.sql

来自「毕业设计作品--超市管理系统」· SQL 代码 · 共 165 行

use DB_SMS
go

-----------------------------------------------------
--1. proc_getEmployeeInfo
--功能：查询员工信息(员工编号, 员工姓名, 员工权限)
--输入：员工编号, 员工姓名, 权限	
--输出：员工信息(TB_EMPLOYEE, TB_EMPLOYEE_TYPE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_getEmployeeInfo')
	drop procedure proc_getEmployeeInfo
go
create procedure proc_getEmployeeInfo
	@empId varchar(17) = '',	--员工编号
	@empName varchar(10) = '',	--员工姓名
	@emtName varchar(10) = ''	--员工权限
as
	if @empId != ''
		--根据员工编号查询
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and empId like '%'+@empId+'%' and empState = 0
	else if @empName != ''
		--根据员工姓名查询
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and empName like '%'+@empName+'%' and empState = 0
	else if @emtName != ''
		--根据员工权限查询
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and emtName like '%'+@emtName+'%' and empState = 0
	else 
		--获取所有员工信息
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and empState = 0
go

-----------------------------------------------------
--2. proc_addEmployee
--功能：添加员工
--输入：员工编号, 员工姓名, 初始密码, 权限
--输出：成功或失败信息(TB_EMPLOYEE, TB_EMPLOYEE_TYPE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_addEmployee')
	drop procedure proc_addEmployee
go
create procedure proc_addEmployee
	@empId varchar(17),		--员工编号
	@empName varchar(10),	--员工姓名
	@empPass varchar(8),	--员工密码
	@emtName varchar(10),	--员工权限
	@state int output		--输出状态
as
	begin transaction 
	declare @errorSum int	--错误累计
	set @errorSum = 0
	--获取员工类型编号
	declare @emtId varchar(17)	--员工类型编号
	select @emtId = emtId from TB_EMPLOYEE_TYPE where emtName = @emtName
	--添加员工信息
	insert into TB_EMPLOYEE values(@empId, @emtId, @empName, @empPass, 0)
	set @errorSum = @errorSum + @@error

	if @errorSum <> 0
	begin
		--回滚事务
		rollback transaction
		set @state = 0
	end
	else 
	begin
		--提交事务
		commit transaction
		set @state = 1
	end
go

-----------------------------------------------------
--3. proc_GetAllEmployeeInfo
--功能：获取所有的员工信息
--输入：
--输出：所有的员工信息
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_GetAllEmployeeInfo')
	drop procedure proc_GetAllEmployeeInfo
go
create procedure proc_GetAllEmployeeInfo
as
	select * from TB_EMPLOYEE
go

-----------------------------------------------------
--4. proc_updateEmployee
--功能：修改员工
--输入：员工编号, 员工姓名, 初始密码, 权限
--输出：成功或失败信息(TB_EMPLOYEE, TB_EMPLOYEE_TYPE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_updateEmployee')
	drop procedure proc_updateEmployee
go
create procedure proc_updateEmployee
	@empId varchar(17),		--员工编号
	@empName varchar(10),	--员工姓名
	@empPass varchar(8),	--员工密码
	@emtName varchar(10),	--员工权限
	@state int output		--输出状态
as
	begin transaction 
	declare @errorSum int	--错误累计
	set @errorSum = 0
	--获取员工类型编号
	declare @emtId varchar(17)	--员工类型编号
	select @emtId = emtId from TB_EMPLOYEE_TYPE where emtName = @emtName
	--添加员工信息
	update TB_EMPLOYEE set emtId = @emtId, empName = @empName, empPass = @empPass
		where empId = @empId
	set @errorSum = @errorSum + @@error

	if @errorSum <> 0
	begin
		--回滚事务
		rollback transaction
		set @state = 0
	end
	else 
	begin
		--提交事务
		commit transaction
		set @state = 1
	end
go

-----------------------------------------------------
--5. proc_deleteEmployee
--功能：删除员工信息
--输入：员工编号
--输出：成功或失败信息
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_deleteEmployee')
	drop procedure proc_deleteEmployee
go
create procedure proc_deleteEmployee
	@empId varchar(17),		--员工编号
	@state int output		--状态信息
as
	begin transaction
	--更改员工状态为1(不可用)
	update TB_EMPLOYEE set empState = 1 where empId = @empId
	if @@error <> 0
	begin
		--回滚事务
		rollback transaction
		set @state = 0
	end
	else 
	begin
		--提交事务
		commit transaction
		set @state = 1
	end
go

select * from tb_employee