- 浏览: 116652 次
- 来自: 北京
文章分类
最新评论
-
hww_1030:
spring+springmvc+ibatis整合注解方式实例 -
jackclchan:
你好,能将demo源码发我下吗?jackclchan@qq.c ...
spring+springmvc+ibatis整合注解方式实例 -
amwiqplhui:
你好,能将demo源码发我下吗?772851689@qq.co ...
spring+springmvc+ibatis整合注解方式实例 -
jiangshenyi:
你好,上面的demo 能发我一份了 deshanjiang8 ...
spring+springmvc+ibatis整合注解方式实例 -
springmvc-freemarker:
spring mvc demo教程源代码下载,地址:http: ...
spring+springmvc+ibatis整合注解方式实例
1、/*存储过程spwebAddLeaveReq*/
CREATE PROC DBO.spwebAddLeaveReq
@EmployeeID INT,
@StartTime DATETIME,
@SubmitTime datetime,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblLeave(
EmployeeID,
StartTime,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID ,
@StartTime ,
@SubmitTime,
@EndTime ,
@ApproverID ,
@Hours ,
@Reason )
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddLeaveReq TO BlueHillASPUser
2、/*创建存储过程spwebAddOTReq向表 tblOvertime 插入一条已提交的加班申请*/
CREATE PROC spwebAddOTReq
@EmployeeID INT,--
@Type TINYINT,
@SubmitTime DATETIME,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblOvertime(
EmployeeID,
Type,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID,
@Type,
@SubmitTime,
@EndTime,
@ApproverID,
@Hours,
@Reason)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddOTReq TO BlueHillASPUser
4、/*创建存储过程spwebCancelLeaveReq用来取消表 tblLeave 中的一条请假申请*/
CREATE PROC spwebCancelLeaveReq
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelLeaveReq TO BlueHillASPUser
5、/*创建存储过程spwebCancelOTReq用来取消表tblOvertime中一条加班申请*/
CREATE PROC spwebCancelOTReq
@OvertimeID INT
AS
DELETE tblOvertime
WHERE OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelOTReq TO BlueHillASPUser
6、/*创建存储过程spwebChangeLeaveStatus更新表tblLeave中的一条请假申请记录的状态,
并输入更新的理由。*/
CREATE PROC spwebChangeLeaveStatus
@LeaveID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET Status=@Status,DenyReason=@DenyReason
where LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeLeaveStatus TO BlueHillASPUser
7、/*创建存储过程spwebChangeOTStatus更新表tblOvertime中的一条请假申请记录的状态,
并输入更新的理由。*/
CREATE PROC spwebChangeOTStatus
@OvertimeID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblOvertime
SET Status=@Status,DenyReason=@DenyReason
where OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeOTStatus TO BlueHillASPUser
8、/*创建存储过程spwebGetCEOInfo可以得到一个 CEOID从TBLEMPLOYEE表中。*/
CREATE PROC spwebGetCEOInfo
@CEOID INT
AS
RETURN @CEOID(SELECT EmployeeID FROM tblEmployee WHERE Title='CEO')
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetCEOInfo TO BlueHillASPUser
9、/*创建存储过程spwebGetCEOInfo可以按指定部门ID
和指定的时间段汇总本部门的员工考勤信息。*/
CREATE PROC spwebGetDeptAttendSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM viwwebAllAttend
WHERE DeptID=@DeptID OR (BreachTime>=@StartTime AND BreachTime<=@EndTime)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptAttendSummary TO BlueHillASPUser
10、/*创建存储过程spwebGetDeptLvSummary可以按指定部门ID
和指定的时间段汇总本部门员工已批准的请假信息。*/
CREATE PROC spwebGetDeptLvSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,L.StartTime,L.EndTime FROM tblLeave AS L
INNER JOIN tblEmployee AS E ON L.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (L.StartTime<=@StartTime AND L.EndTime>=@EndTime) OR L.Status='已批准'
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptLvSummary TO BlueHillASPUser
11、/*创建存储过程spwebGetDeptOTSummary可以按指定部门ID、指定时间段
和指定加班类型汇总本部门员工已批准的加班信息*/
CREATE PROC spwebGetDeptOTSummary
@DeptID INT,
@Type TINYINT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,O.StartTime,O.EndTime FROM tblOvertime AS O
INNER JOIN tblEmployee AS E ON O.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (O.StartTime<=@StartTime AND O.EndTime>=@EndTime)
OR Type=@Type
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptOTSummary TO BlueHillASPUser
12、/*创建存储过程spwebGetEmployeeID根据员工登录名得到员工 ID。*/
CREATE PROC spwebGetEmployeeID
@LoginName CHAR(20),
@EmployeeID INT OUTPUT
AS
SET @EmployeeID=(SELECT EmployeeID FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmployeeID TO BlueHillASPUser
/*执行语句*/
DECLARE @ANSWER INT
EXEC spwebGetEmployeeID 'l1',@ANSWER OUTPUT
SELECT 'SDFGG',@ANSWER
13、/*创建存储过程spwebGetEmpPassword根据员工登录名得到员工密码。*/
CREATE PROC spwebGetEmpPassword
@LoginName CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillWinUser
14、/*创建存储过程spwebGetEmpPassword根据员工ID得到员工登录密码*/
CREATE PROC spwebGetEmpPasswordByID
@EmployeeID CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE EmployeeID=@EmployeeID)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPasswordByID TO BlueHillASPUser
15、/*创建存储过程spwebUpdatePassword根据员工ID
更新表 tblEmployee 中的员工登录密码*/
CREATE PROC spwebUpdatePassword
@EmployeeID INT,
@Password BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@Password
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillWinUser
16、/*创建存储过程spwebUpdateSelfIntro根据员工ID
更新表 tblEmployee 中的员工自我介绍信息*/
CREATE PROC spwebUpdateSelfIntro
@EmployeeID INT,
@SelfIntro CHAR(200)
AS
UPDATE tblEmployee
SET SelfIntro=@SelfIntro
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdateSelfIntro TO BlueHillASPUser
17、/*此存储过程根据输入的参数信息先确定要添加的绩效考核子项
目所属的绩效考核是否存在,如果不存在,就先在表 tblPerformance
中添加一条绩效考核信息,然后再在表 tblPerformItem 中添加要加入的绩效考核子项目。*/
CREATE PROC spwebAddPerformObject
@EmployeeID INT,
@PerformYear INT,
@PerformSeason TINYINT,
@ObjectContent NCHAR(100)
AS
--DECLARE @GETDATE GETDATE
DECLARE @SELECTID INT
SET @SELECTID=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
IF @EmployeeID IN(SELECT EmployeeID FROM tblPerformance)
BEGIN
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID,@ObjectContent)
END
ELSE
BEGIN
INSERT tblPerformance(EmployeeID,SubmitTime,PerformYear,PerformSeason)
VALUES(@EmployeeID,CURRENT_TIMESTAMP,'2006','1')
DECLARE @SELECTID2 INT
SET @SELECTID2=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID2,@ObjectContent)
END
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddPerformObject TO BlueHillASPUser
18、/*创建存储过程spwebDeletePerformItem从表
tblPerformItem 中删除一条指定记录。*/
CREATE PROC spwebDeletePerformItem
@PerformItemID INT
AS
DELETE tblPerformItem
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebDeletePerformItem TO BlueHillASPUser
19、/*创建存储过程spwebGetDeptSalarySummary可以按部门得到指定时间段内
的员工工资汇总信息。*/
CREATE PROC spwebGetDeptSalarySummary
@DeptID INT,
@StartTime DATETIME
AS
SELECT S.SalaryID,S.EmployeeID,S.SalaryTime,
S.BalaryTime,S.OvertimeSalary,S.AbsenseSalary,
S.OtherSalary,D.DeptID FROM tblSalary AS S
INNER JOIN tblEmployeeID AS E ON E.EmployeeID=S.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DepID
WHERE D.DeptID=@DeptID AND S.SararyTime=@StartTime
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptSalarySummary TO BlueHillASPUser
20、/*创建存储过程spwebUpdatePerform根据传入的参数信息来更新表tblPerformance。*/
CREATE PROC spwebUpdatePerform
@PerformID INT,
@SelfComment NCHAR(200),
@RelfComment NCHAR(200),
@SelfScore INT,
@ReviewScore INT
AS
UPDATE tblPerformance
SET PerformID=@PerformID,SelfComment=@SelfComment,
SelfScore=@SelfScore,ReviewScore=@ReviewScore
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerform TO BlueHillASPUser
21、/*创建存储过程spwebUpdatePerformItem根据绩效考核子项目ID,
更新表 tblPerformItem 中的子项目内容。*/
CREATE PROC spwebUpdatePerformItem
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET PerformItemID=@PerformItemID,ObjectContent=@ObjectContent
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItem TO BlueHillASPUser
22、/*创建存储过程spwinAttendanceQuery根据指定的时间段查询员工考勤信息。*/
CREATE PROC spwinAttendanceQuery
@StartDate DATETIME,
@EndDATE DATETIME
AS
SELECT * FROM tblAttendance
WHERE BreachTime>=@StartDate and BreachTime<=@EndDATE
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAttendanceQuery TO BlueHillWinUser
23、/*创建存储过程spwinChangeEmployeeDept根据员工ID和员工所属部门名称,
来更新表 tblEmployee 中的员工部门 ID。*/
CREATE PROC spwinChangeEmployeeDept
@EmployeeID INT,
@DeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangeEmployeeDept TO BlueHillWinUser
24、/*创建存储过程spwinCreateDepartment向表tblDepartment
添加一条新部门信息的记录。*/
CREATE PROC spwinCreateDepartment
@DeptName NCHAR(10),
@Desciption NVARCHAR(50),
@ManagerID INT
AS
INSERT tblDepartment(DeptName,Desciption,ManagerID)
VALUES(@DeptName,@Desciption,@ManagerID)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinCreateDepartment TO BlueHillWinUser
25、/*创建存储过程spwinDeleteDepartment从表 tblDepartment
中删除一个指定的部门,在删除前先判断该部门是否还有员工,
如有员工则不删除该部门并返回,如无任何员工,则删除该部门。*/
CREATE PROC spwinDeleteDepartment
@DeptName CHAR(10)
AS
IF (SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
IN (SELECT DeptID FROM tblEmployee)
BEGIN
RAISERROR('有员工存在这部门',16,1)
END
ELSE
BEGIN
DELETE tblDepartment
WHERE DeptName=@DeptName
END
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteDepartment TO BlueHillWinUser
26、/*创建存储过程spwinDeleteEmployee根据指定的员工 ID
从表 tblEmployee 中删除一条员工记录。*/
CREATE PROC spwinDeleteEmployee
@EmployeeID INT
AS
DELETE tblEmployeeID
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteEmployee TO BlueHillWinUser
27、/*创建存储过程根据指定的请假申请ID从表 tblLeave
中删除一条请假申请记录。*/
CREATE PROC spwinDelLeaveRequest
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDelLeaveRequest TO BlueHillWinUser
28、/*创建存储过程spwinDeptAllEmployee根据部门名称从视图
viwwinEmployeeList中得到本部门员工的详细信息。*/
CREATE PROC spwinDeptAllEmployee
@DeptName NCHAR(10)
AS
SELECT * FROM viwwinEmployeeList
WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptAllEmployee TO BlueHillWinUser
29、/*创建存储过程spwinDeptLeave根根据部门 ID 得到本部门员工的请假信息*/
CREATE PROC spwinDeptLeave
@DeptID INT
AS
SELECT * FROM tblLeave AS L INNER JOIN tblEmployee AS E
ON L.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptLeave TO BlueHillWinUser
30、/*创建存储过程spwinEmpLeave汇总指定员工的请假信息。*/
CREATE PROC spwinEmpLeave
@EmployeeID INT
AS
SELECT * FROM tblLeave
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinEmpLeave TO BlueHillWinUser
31、/*创建存储过程spwinGetAllDepartment从表
tblDepartment中得到所有部门的部门 ID 和部门名称。*/
CREATE PROC spwinGetAllDepartment
AS
SELECT DeptID,DeptName FROM tblDepartment
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetAllDepartment TO BlueHillWinUser
32、/*创建存储spwinGetEmpbyDeptName得到指定部门名称的部门的所有员工的详细信息*/
CREATE PROC spwinGetEmpbyDeptName
@DeptName NCHAR(10)
AS
SELECT * FROM tblEmployee AS E INNER JOIN tblDepartment AS D
ON E.DeptID=D.DeptID
WHERE D.DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetEmpbyDeptName TO BlueHillWinUser
33、/*创建存储spwinMoveEmpBetweenDept完成把一个部门所有员工转移到另一个指定部门。*/
CREATE PROC spwinMoveEmpBetweenDept
@FromDeptName NCHAR(10),
@ToDeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@ToDeptName)
WHERE DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@FromDeptName)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinMoveEmpBetweenDept TO BlueHillWinUser
34、/*创建存储spwinRejectLeaveRequest拒绝一条请假申请。。*/
CREATE PROC spwinRejectLeaveRequest
@LeaveID INT,
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET DenyReason=@DenyReason
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinRejectLeaveRequest TO BlueHillWinUser
35、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID,
更新表 tblPerformItem 中的自我评分。*/
CREATE PROC spwebUpdatePerformItemSelf
@PerformItemID INT,
@SelfScore INT
AS
UPDATE tblPerformItem
SET SelfScore=@SelfScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemSelf TO BlueHillASPUser
36、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID,
更新表 tblPerformItem 中的经理评分。*/
CREATE PROC spwebUpdatePerformItemReview
@PerformItemID INT,
@ReviewScore INT
AS
UPDATE tblPerformItem
SET ReviewScore=@ReviewScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemReview TO BlueHillASPUser
37、/*创建储过程spwebGetSubmittedPerform按指定的年份和季度
汇总指定部门的绩效考核详细信息。*/
CREATE PROC spwebGetSubmittedPerform
@DeptID INT,
@PerformYear INT,
@PerformSeason INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear AND P.PerformSeason=@PerformSeason
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetSubmittedPerform TO BlueHillASPUser
38、/*创建储过程spwebGetDeptPerformSummary按指定部门汇总指定年份
的本部门员工的绩效考核信息。*/
CREATE PROC spwebGetDeptPerformSummary
@DeptID INT,
@PerformYear INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptPerformSummary TO BlueHillASPUser
39、/*创建存储过程spwinOTbyGroup按部门名称汇总本部门的员工加班信息*/
CREATE PROC spwinOTbyGroup
@DeptName NVARCHAR(20)
AS
SELECT * FROM tblOvertime AS O INNER JOIN tblEmployee AS E
ON O.EmployeeID=E.EmployeeID INNER JOIN tblDepartment
AS D ON D.DeptID=E.DeptID WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinOTbyGroup TO BlueHillWinUser
40、/*创建存储过程spwebPerformReviewed把表tblPerformance
中的Status字段更新为 1,表示此条记录已经审核。*/
CREATE PROC spwebPerformReviewed
@PerformID INT
AS
UPDATE tblPerformance
SET Status='1' WHERE PerformID=@PerformID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebPerformReviewed TO BlueHillASPUser
41、/*创建存储过程spwinBasicSalaryByEmpID根据员工编号从表
tblEmployee 中查询得到此员工的基本工资信息。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT BasicSalary FROM tblEmployee
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinBasicSalaryByEmpID TO BlueHillWinUser
42、/*创建存储过程spwinBasicSalaryByEmpID通过联接表 tblEmployee 和 tblSalary,按指定员工编号汇总员工工资的历史记录。
。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT * FROM tblSalary AS S INNER JOIN tblEmployee AS E
ON E.EmployeeID=S.EmployeeID WHERE E.EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSalaryHistoryByEmpID TO BlueHillWinUser
43、/*创建存储过程spwinSetBasicSalary用来设置员工的基本工资。*/
CREATE PROC spwinSetBasicSalary
@EmployeeID INT,
@BasicSalary INT
AS
UPDATE tblSalary
SET BasicSalary=@BasicSalary WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSetBasicSalary TO BlueHillWinUser
44、/*创建存储过程spwinQueryEventbyTime从表 tblSystemEvent
中获取指定时间段内的系统事件。*/
CREATE PROC spwinQueryEventbyTime
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM tblSystemEvent WHERE EventTime>=@StartTime AND
EventTime<=@EndTime
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinQueryEventbyTime TO BlueHillWinUser
45、/*创建存储过程spwinChangePassword根据登录名和旧密码来更新密码*/
CREATE PROC spwinChangePassword
@LoginName NVARCHAR(20),
@OldPassword BINARY(20),
@NewPassword BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@NewPassword WHERE LoginName=@LoginName AND
[Password]=@OldPassword
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangePassword TO BlueHillWinUser
46、/*创建存储过程spwinAddEvent向表tblSystemEvent 添加一条新的系统事件记录。*/
CREATE PROC spwinAddEvent
@Message NVARCHAR(50)
AS
ALTER TABLE tblSystemEvent
ADD Message nvarchar (50)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAddEvent TO BlueHillWinUser
47、/*创建存储过程spwebUpdatePerformItemObj根据绩效考核子项目编号,更新该子项目的项目内容。*/
CREATE PROC spwebUpdatePerformItemObj
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET ObjectContent=@ObjectContent
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemObj TO BlueHillASPUser
CREATE PROC DBO.spwebAddLeaveReq
@EmployeeID INT,
@StartTime DATETIME,
@SubmitTime datetime,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblLeave(
EmployeeID,
StartTime,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID ,
@StartTime ,
@SubmitTime,
@EndTime ,
@ApproverID ,
@Hours ,
@Reason )
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddLeaveReq TO BlueHillASPUser
2、/*创建存储过程spwebAddOTReq向表 tblOvertime 插入一条已提交的加班申请*/
CREATE PROC spwebAddOTReq
@EmployeeID INT,--
@Type TINYINT,
@SubmitTime DATETIME,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblOvertime(
EmployeeID,
Type,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID,
@Type,
@SubmitTime,
@EndTime,
@ApproverID,
@Hours,
@Reason)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddOTReq TO BlueHillASPUser
4、/*创建存储过程spwebCancelLeaveReq用来取消表 tblLeave 中的一条请假申请*/
CREATE PROC spwebCancelLeaveReq
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelLeaveReq TO BlueHillASPUser
5、/*创建存储过程spwebCancelOTReq用来取消表tblOvertime中一条加班申请*/
CREATE PROC spwebCancelOTReq
@OvertimeID INT
AS
DELETE tblOvertime
WHERE OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelOTReq TO BlueHillASPUser
6、/*创建存储过程spwebChangeLeaveStatus更新表tblLeave中的一条请假申请记录的状态,
并输入更新的理由。*/
CREATE PROC spwebChangeLeaveStatus
@LeaveID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET Status=@Status,DenyReason=@DenyReason
where LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeLeaveStatus TO BlueHillASPUser
7、/*创建存储过程spwebChangeOTStatus更新表tblOvertime中的一条请假申请记录的状态,
并输入更新的理由。*/
CREATE PROC spwebChangeOTStatus
@OvertimeID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblOvertime
SET Status=@Status,DenyReason=@DenyReason
where OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeOTStatus TO BlueHillASPUser
8、/*创建存储过程spwebGetCEOInfo可以得到一个 CEOID从TBLEMPLOYEE表中。*/
CREATE PROC spwebGetCEOInfo
@CEOID INT
AS
RETURN @CEOID(SELECT EmployeeID FROM tblEmployee WHERE Title='CEO')
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetCEOInfo TO BlueHillASPUser
9、/*创建存储过程spwebGetCEOInfo可以按指定部门ID
和指定的时间段汇总本部门的员工考勤信息。*/
CREATE PROC spwebGetDeptAttendSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM viwwebAllAttend
WHERE DeptID=@DeptID OR (BreachTime>=@StartTime AND BreachTime<=@EndTime)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptAttendSummary TO BlueHillASPUser
10、/*创建存储过程spwebGetDeptLvSummary可以按指定部门ID
和指定的时间段汇总本部门员工已批准的请假信息。*/
CREATE PROC spwebGetDeptLvSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,L.StartTime,L.EndTime FROM tblLeave AS L
INNER JOIN tblEmployee AS E ON L.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (L.StartTime<=@StartTime AND L.EndTime>=@EndTime) OR L.Status='已批准'
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptLvSummary TO BlueHillASPUser
11、/*创建存储过程spwebGetDeptOTSummary可以按指定部门ID、指定时间段
和指定加班类型汇总本部门员工已批准的加班信息*/
CREATE PROC spwebGetDeptOTSummary
@DeptID INT,
@Type TINYINT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,O.StartTime,O.EndTime FROM tblOvertime AS O
INNER JOIN tblEmployee AS E ON O.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (O.StartTime<=@StartTime AND O.EndTime>=@EndTime)
OR Type=@Type
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptOTSummary TO BlueHillASPUser
12、/*创建存储过程spwebGetEmployeeID根据员工登录名得到员工 ID。*/
CREATE PROC spwebGetEmployeeID
@LoginName CHAR(20),
@EmployeeID INT OUTPUT
AS
SET @EmployeeID=(SELECT EmployeeID FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmployeeID TO BlueHillASPUser
/*执行语句*/
DECLARE @ANSWER INT
EXEC spwebGetEmployeeID 'l1',@ANSWER OUTPUT
SELECT 'SDFGG',@ANSWER
13、/*创建存储过程spwebGetEmpPassword根据员工登录名得到员工密码。*/
CREATE PROC spwebGetEmpPassword
@LoginName CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillWinUser
14、/*创建存储过程spwebGetEmpPassword根据员工ID得到员工登录密码*/
CREATE PROC spwebGetEmpPasswordByID
@EmployeeID CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE EmployeeID=@EmployeeID)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPasswordByID TO BlueHillASPUser
15、/*创建存储过程spwebUpdatePassword根据员工ID
更新表 tblEmployee 中的员工登录密码*/
CREATE PROC spwebUpdatePassword
@EmployeeID INT,
@Password BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@Password
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillWinUser
16、/*创建存储过程spwebUpdateSelfIntro根据员工ID
更新表 tblEmployee 中的员工自我介绍信息*/
CREATE PROC spwebUpdateSelfIntro
@EmployeeID INT,
@SelfIntro CHAR(200)
AS
UPDATE tblEmployee
SET SelfIntro=@SelfIntro
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdateSelfIntro TO BlueHillASPUser
17、/*此存储过程根据输入的参数信息先确定要添加的绩效考核子项
目所属的绩效考核是否存在,如果不存在,就先在表 tblPerformance
中添加一条绩效考核信息,然后再在表 tblPerformItem 中添加要加入的绩效考核子项目。*/
CREATE PROC spwebAddPerformObject
@EmployeeID INT,
@PerformYear INT,
@PerformSeason TINYINT,
@ObjectContent NCHAR(100)
AS
--DECLARE @GETDATE GETDATE
DECLARE @SELECTID INT
SET @SELECTID=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
IF @EmployeeID IN(SELECT EmployeeID FROM tblPerformance)
BEGIN
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID,@ObjectContent)
END
ELSE
BEGIN
INSERT tblPerformance(EmployeeID,SubmitTime,PerformYear,PerformSeason)
VALUES(@EmployeeID,CURRENT_TIMESTAMP,'2006','1')
DECLARE @SELECTID2 INT
SET @SELECTID2=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID2,@ObjectContent)
END
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddPerformObject TO BlueHillASPUser
18、/*创建存储过程spwebDeletePerformItem从表
tblPerformItem 中删除一条指定记录。*/
CREATE PROC spwebDeletePerformItem
@PerformItemID INT
AS
DELETE tblPerformItem
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebDeletePerformItem TO BlueHillASPUser
19、/*创建存储过程spwebGetDeptSalarySummary可以按部门得到指定时间段内
的员工工资汇总信息。*/
CREATE PROC spwebGetDeptSalarySummary
@DeptID INT,
@StartTime DATETIME
AS
SELECT S.SalaryID,S.EmployeeID,S.SalaryTime,
S.BalaryTime,S.OvertimeSalary,S.AbsenseSalary,
S.OtherSalary,D.DeptID FROM tblSalary AS S
INNER JOIN tblEmployeeID AS E ON E.EmployeeID=S.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DepID
WHERE D.DeptID=@DeptID AND S.SararyTime=@StartTime
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptSalarySummary TO BlueHillASPUser
20、/*创建存储过程spwebUpdatePerform根据传入的参数信息来更新表tblPerformance。*/
CREATE PROC spwebUpdatePerform
@PerformID INT,
@SelfComment NCHAR(200),
@RelfComment NCHAR(200),
@SelfScore INT,
@ReviewScore INT
AS
UPDATE tblPerformance
SET PerformID=@PerformID,SelfComment=@SelfComment,
SelfScore=@SelfScore,ReviewScore=@ReviewScore
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerform TO BlueHillASPUser
21、/*创建存储过程spwebUpdatePerformItem根据绩效考核子项目ID,
更新表 tblPerformItem 中的子项目内容。*/
CREATE PROC spwebUpdatePerformItem
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET PerformItemID=@PerformItemID,ObjectContent=@ObjectContent
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItem TO BlueHillASPUser
22、/*创建存储过程spwinAttendanceQuery根据指定的时间段查询员工考勤信息。*/
CREATE PROC spwinAttendanceQuery
@StartDate DATETIME,
@EndDATE DATETIME
AS
SELECT * FROM tblAttendance
WHERE BreachTime>=@StartDate and BreachTime<=@EndDATE
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAttendanceQuery TO BlueHillWinUser
23、/*创建存储过程spwinChangeEmployeeDept根据员工ID和员工所属部门名称,
来更新表 tblEmployee 中的员工部门 ID。*/
CREATE PROC spwinChangeEmployeeDept
@EmployeeID INT,
@DeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangeEmployeeDept TO BlueHillWinUser
24、/*创建存储过程spwinCreateDepartment向表tblDepartment
添加一条新部门信息的记录。*/
CREATE PROC spwinCreateDepartment
@DeptName NCHAR(10),
@Desciption NVARCHAR(50),
@ManagerID INT
AS
INSERT tblDepartment(DeptName,Desciption,ManagerID)
VALUES(@DeptName,@Desciption,@ManagerID)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinCreateDepartment TO BlueHillWinUser
25、/*创建存储过程spwinDeleteDepartment从表 tblDepartment
中删除一个指定的部门,在删除前先判断该部门是否还有员工,
如有员工则不删除该部门并返回,如无任何员工,则删除该部门。*/
CREATE PROC spwinDeleteDepartment
@DeptName CHAR(10)
AS
IF (SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
IN (SELECT DeptID FROM tblEmployee)
BEGIN
RAISERROR('有员工存在这部门',16,1)
END
ELSE
BEGIN
DELETE tblDepartment
WHERE DeptName=@DeptName
END
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteDepartment TO BlueHillWinUser
26、/*创建存储过程spwinDeleteEmployee根据指定的员工 ID
从表 tblEmployee 中删除一条员工记录。*/
CREATE PROC spwinDeleteEmployee
@EmployeeID INT
AS
DELETE tblEmployeeID
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteEmployee TO BlueHillWinUser
27、/*创建存储过程根据指定的请假申请ID从表 tblLeave
中删除一条请假申请记录。*/
CREATE PROC spwinDelLeaveRequest
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDelLeaveRequest TO BlueHillWinUser
28、/*创建存储过程spwinDeptAllEmployee根据部门名称从视图
viwwinEmployeeList中得到本部门员工的详细信息。*/
CREATE PROC spwinDeptAllEmployee
@DeptName NCHAR(10)
AS
SELECT * FROM viwwinEmployeeList
WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptAllEmployee TO BlueHillWinUser
29、/*创建存储过程spwinDeptLeave根根据部门 ID 得到本部门员工的请假信息*/
CREATE PROC spwinDeptLeave
@DeptID INT
AS
SELECT * FROM tblLeave AS L INNER JOIN tblEmployee AS E
ON L.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptLeave TO BlueHillWinUser
30、/*创建存储过程spwinEmpLeave汇总指定员工的请假信息。*/
CREATE PROC spwinEmpLeave
@EmployeeID INT
AS
SELECT * FROM tblLeave
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinEmpLeave TO BlueHillWinUser
31、/*创建存储过程spwinGetAllDepartment从表
tblDepartment中得到所有部门的部门 ID 和部门名称。*/
CREATE PROC spwinGetAllDepartment
AS
SELECT DeptID,DeptName FROM tblDepartment
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetAllDepartment TO BlueHillWinUser
32、/*创建存储spwinGetEmpbyDeptName得到指定部门名称的部门的所有员工的详细信息*/
CREATE PROC spwinGetEmpbyDeptName
@DeptName NCHAR(10)
AS
SELECT * FROM tblEmployee AS E INNER JOIN tblDepartment AS D
ON E.DeptID=D.DeptID
WHERE D.DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetEmpbyDeptName TO BlueHillWinUser
33、/*创建存储spwinMoveEmpBetweenDept完成把一个部门所有员工转移到另一个指定部门。*/
CREATE PROC spwinMoveEmpBetweenDept
@FromDeptName NCHAR(10),
@ToDeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@ToDeptName)
WHERE DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@FromDeptName)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinMoveEmpBetweenDept TO BlueHillWinUser
34、/*创建存储spwinRejectLeaveRequest拒绝一条请假申请。。*/
CREATE PROC spwinRejectLeaveRequest
@LeaveID INT,
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET DenyReason=@DenyReason
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinRejectLeaveRequest TO BlueHillWinUser
35、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID,
更新表 tblPerformItem 中的自我评分。*/
CREATE PROC spwebUpdatePerformItemSelf
@PerformItemID INT,
@SelfScore INT
AS
UPDATE tblPerformItem
SET SelfScore=@SelfScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemSelf TO BlueHillASPUser
36、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID,
更新表 tblPerformItem 中的经理评分。*/
CREATE PROC spwebUpdatePerformItemReview
@PerformItemID INT,
@ReviewScore INT
AS
UPDATE tblPerformItem
SET ReviewScore=@ReviewScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemReview TO BlueHillASPUser
37、/*创建储过程spwebGetSubmittedPerform按指定的年份和季度
汇总指定部门的绩效考核详细信息。*/
CREATE PROC spwebGetSubmittedPerform
@DeptID INT,
@PerformYear INT,
@PerformSeason INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear AND P.PerformSeason=@PerformSeason
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetSubmittedPerform TO BlueHillASPUser
38、/*创建储过程spwebGetDeptPerformSummary按指定部门汇总指定年份
的本部门员工的绩效考核信息。*/
CREATE PROC spwebGetDeptPerformSummary
@DeptID INT,
@PerformYear INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptPerformSummary TO BlueHillASPUser
39、/*创建存储过程spwinOTbyGroup按部门名称汇总本部门的员工加班信息*/
CREATE PROC spwinOTbyGroup
@DeptName NVARCHAR(20)
AS
SELECT * FROM tblOvertime AS O INNER JOIN tblEmployee AS E
ON O.EmployeeID=E.EmployeeID INNER JOIN tblDepartment
AS D ON D.DeptID=E.DeptID WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinOTbyGroup TO BlueHillWinUser
40、/*创建存储过程spwebPerformReviewed把表tblPerformance
中的Status字段更新为 1,表示此条记录已经审核。*/
CREATE PROC spwebPerformReviewed
@PerformID INT
AS
UPDATE tblPerformance
SET Status='1' WHERE PerformID=@PerformID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebPerformReviewed TO BlueHillASPUser
41、/*创建存储过程spwinBasicSalaryByEmpID根据员工编号从表
tblEmployee 中查询得到此员工的基本工资信息。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT BasicSalary FROM tblEmployee
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinBasicSalaryByEmpID TO BlueHillWinUser
42、/*创建存储过程spwinBasicSalaryByEmpID通过联接表 tblEmployee 和 tblSalary,按指定员工编号汇总员工工资的历史记录。
。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT * FROM tblSalary AS S INNER JOIN tblEmployee AS E
ON E.EmployeeID=S.EmployeeID WHERE E.EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSalaryHistoryByEmpID TO BlueHillWinUser
43、/*创建存储过程spwinSetBasicSalary用来设置员工的基本工资。*/
CREATE PROC spwinSetBasicSalary
@EmployeeID INT,
@BasicSalary INT
AS
UPDATE tblSalary
SET BasicSalary=@BasicSalary WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSetBasicSalary TO BlueHillWinUser
44、/*创建存储过程spwinQueryEventbyTime从表 tblSystemEvent
中获取指定时间段内的系统事件。*/
CREATE PROC spwinQueryEventbyTime
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM tblSystemEvent WHERE EventTime>=@StartTime AND
EventTime<=@EndTime
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinQueryEventbyTime TO BlueHillWinUser
45、/*创建存储过程spwinChangePassword根据登录名和旧密码来更新密码*/
CREATE PROC spwinChangePassword
@LoginName NVARCHAR(20),
@OldPassword BINARY(20),
@NewPassword BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@NewPassword WHERE LoginName=@LoginName AND
[Password]=@OldPassword
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangePassword TO BlueHillWinUser
46、/*创建存储过程spwinAddEvent向表tblSystemEvent 添加一条新的系统事件记录。*/
CREATE PROC spwinAddEvent
@Message NVARCHAR(50)
AS
ALTER TABLE tblSystemEvent
ADD Message nvarchar (50)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAddEvent TO BlueHillWinUser
47、/*创建存储过程spwebUpdatePerformItemObj根据绩效考核子项目编号,更新该子项目的项目内容。*/
CREATE PROC spwebUpdatePerformItemObj
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET ObjectContent=@ObjectContent
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemObj TO BlueHillASPUser
相关推荐
数据库存储过程生成代码类工具是一种高效且实用的开发辅助软件,它主要针对数据库中的存储过程进行操作,将这些过程转化为可读性强、易于维护的C#代码。这种工具极大地提升了开发人员的工作效率,减少了手动编写代码...
获取SQLServer数据库中所有存储过程的源代码,可以对代码进行搜索,也可以把源代码保存,非常实用。 可根据需要修改。
C# Ado.net实现读取SQLServer数据库存储过程列表及参数信息示例 本文主要介绍了使用C# Ado...本文提供了使用C# Ado.net实现读取SQL Server数据库存储过程列表及参数信息的详细示例代码和解释,希望对大家有所帮助。
### Oracle通用数据库存储过程代码——高效分页存储过程解析 #### 标题解析 标题“Oracle通用数据库存储过程代码——高效分页存储过程”表明这是一个适用于Oracle数据库的存储过程,主要用于实现高效的分页查询功能...
在IT行业中,数据库存储过程是数据库管理系统中一种非常重要的编程元素。它们是一组为了完成特定功能的SQL语句集,可以被保存并重复使用,提高了数据处理的效率和代码的可维护性。在某些特定场景下,比如商业软件...
标题中的“使用C#导出数据库存储过程源码”指的是使用C#编程语言编写程序来获取SQL Server数据库中的存储过程的源代码。这通常涉及到数据库连接、查询执行以及结果处理等步骤。C#提供了丰富的ADO.NET库,如...
SQL数据库存储过程是一种预编译的SQL代码集合,它封装了一系列复杂的数据库操作,可以在需要时通过调用存储过程的名字来执行。存储过程的使用对于数据库性能优化、代码复用和安全性提升都有显著作用。 首先,了解...
【存储过程概述】 ...学习数据库存储过程不仅能够提升数据库管理效率,还能优化应用程序性能,确保数据的安全性和一致性。通过深入理解和实践,开发者能够更好地控制数据库操作,构建高效且健壮的数据库应用。
本文将围绕“执行存储过程的代码和数据库脚本”这一主题展开,通过分析提供的Demo.sql文件和执行存储过程的相关知识,帮助初级学习者更好地理解和应用这些概念。 首先,让我们了解一下存储过程的基本概念。存储过程...
本项目是一款基于Oracle数据库的自动化代码生成工具源码,包含2150个文件,涵盖1578个...该工具能够自动从数据库存储过程生成Java后端代码,并支持根据数据库表结构生成CRUD操作存储过程,旨在提高数据库代码开发效率。
数据库查询的存储过程 数据库查询的存储过程是数据库管理系统中一种非常重要的概念。它可以将多个SQL语句组合成一个单元,提高数据库的查询效率和性能。 存储过程的优点: 1. 可以在单个存储过程中执行一系列SQL...
4. 代码重用:存储过程可以被多次调用,降低代码重复,提高开发效率。 5. 易于维护:将复杂的业务逻辑封装到存储过程中,便于管理和修改。 在实验中,我们创建了三个存储过程,分别用于借书、预约和还书功能。例如...
数据库存储过程是数据库管理系统(DBMS)中的一种重要特性,主要用于封装一组SQL语句和数据库操作,便于重复使用和管理。存储过程具有多种优势,包括模块化编程、提高执行效率、减少网络通信量、增强安全性等。在SQL...
**存储过程**是一种预编译的SQL代码块,它可以在数据库服务器上保存并重复调用。当第一次创建存储过程时,数据库服务器会对其进行编译,之后每次调用时都会使用已编译的版本,从而显著提高了执行效率。存储过程可以...
在这个特定的项目中,我们关注的是一个基于ASP.NET的数据库存储过程代码生成源程序。这个程序用VB(Visual Basic)作为后端编程语言,并结合ASP.NET的技术,为开发者提供了一种便捷的方式,动态生成与数据库存储过程...
连接数据库,自动创建数据库中所有表的存储过程
此外,存储过程还可以增强代码的复用性,降低数据库的复杂性,并且有利于实施权限控制,提高数据安全性。 二、存储过程的基本语法 创建存储过程通常使用`CREATE PROCEDURE`语句,例如: ```sql CREATE PROCEDURE ...
在Oracle数据库中,存储过程是一组预编译的SQL语句和PL/SQL块,可以在数据库中存储并重复使用,提高代码的复用性和执行效率。 连接Oracle数据库通常涉及以下步骤: 1. 导入JDBC驱动:在Java项目中,我们需要添加...
接着,"存储过程"是数据库中预编译的SQL语句集合,它可以完成一系列复杂的数据库操作,如购票、退票、查询等。存储过程的使用可以提高数据访问效率,同时增强安全性,减少网络传输的数据量。在火车订票系统中,可能...