作者:Q457423356 | 来源:互联网 | 2023-05-18 20:20
我有员工表Employee主键EmployeeId(自增长);还有薪资概览表Salary主键SalaryId(自增长),以及薪资细节表SalaryDetail主键EmployeeId和SalaryId
我有员工表Employee主键EmployeeId(自增长);还有薪资概览表Salary主键SalaryId(自增长),以及薪资细节表SalaryDetail主键EmployeeId和SalaryId。
现在,我要发4月份的薪水,就会在Salary表中新建一条记录用于描述当月的工资发放信息,然后在SalaryDetail表为Employee表中的每个员工创建一条工资记录。
我是数据库菜鸟,各位不要笑话,请告诉我这样一个存储过程怎么写。
9 个解决方案
需要具体的表及表结构,测试数据和你想要的存储过程返回的结果!直观点!
CREATE TABLE Employee(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL)
INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立')
INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵')
INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强')
INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三')
INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四')
CREATE TABLE Salary(
[SalaryId] [int] IDENTITY(1,1) NOT NULL,
[SYear] [int] NULL,
[SMonth] [int] NULL
)
CREATE TABLE SalaryDetail(
[SalaryId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[Salary] [money] NULL)
上面是三个表的结构和部分数据,我现在要做的类似于下面的功能,至于存储过程的返回结果并不重要:
-- 新增2011年4月工资记录
INSERT Salary(SYear,SMonth) VALUES(2011,4)
-- 为每个员工增加相应工资记录
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,2,3000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,3,2000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,4,1800)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,5,2100)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,6,5000)
CREATE TABLE Employee(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL)
SET IDENTITY_INSERT Employee ON
INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立')
INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵')
INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强')
INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三')
INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四')
SET IDENTITY_INSERT Employee OFF
CREATE TABLE Salary(
[SalaryId] [int] IDENTITY(1,1) NOT NULL,
[SYear] [int] NULL,
[SMonth] [int] NULL
)
CREATE TABLE SalaryDetail(
[SalaryId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[Salary] [money] NULL)
-- 新增2011年4月工资记录
INSERT Salary(SYear,SMonth) VALUES(2011,4)
-- 为每个员工增加相应工资记录
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,2,3000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,3,2000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,4,1800)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,5,2100)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,6,5000)
--SQL:
CREATE PROC proc_test
@year int,
@month int
AS
BEGIN TRY
BEGIN TRAN
DECLARE @SalaryId INT
INSERT Salary([SYear], [SMonth]) VALUES(@year, @month)
SET @SalaryId = SCOPE_IDENTITY()
INSERT SalaryDetail
(
[SalaryId],
[EmployeeId],
[Salary]
)
SELECT
@SalaryId,
A.[EmployeeID],
0.00
FROM Employee A
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO
--test
EXEC proc_test 2012, 4
GO
SELECT * FROM SalaryDetail
/*
1 2 3000.00
1 3 2000.00
1 4 1800.00
1 5 2100.00
1 6 5000.00
2 2 0.00
2 3 0.00
2 4 0.00
2 5 0.00
2 6 0.00
*/
用游标取得employee表的所有工号
挨个获取员工各项工资值(salarydetail表的相关字段)后插入salarydetail表;
记住用事务,这个涉及到money
CREATE TABLE Employee(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL)
SET IDENTITY_INSERT Employee ON
INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立')
INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵')
INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强')
INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三')
INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四')
SET IDENTITY_INSERT Employee OFF
CREATE TABLE Salary(
[SalaryId] [int] IDENTITY(1,1) NOT NULL,
[SYear] [int] NULL,
[SMonth] [int] NULL
)
CREATE TABLE SalaryDetail(
[SalaryId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[Salary] [money] NULL)
-- 新增2011年4月工资记录
INSERT Salary(SYear,SMonth) VALUES(2011,4)
-- 为每个员工增加相应工资记录
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,2,3000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,3,2000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,4,1800)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,5,2100)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,6,5000)
--SQL:
CREATE PROC proc_test
@year int,
@month int
AS
BEGIN TRY
BEGIN TRAN
DECLARE @SalaryId INT
INSERT Salary([SYear], [SMonth]) VALUES(@year, @month)
SET @SalaryId = SCOPE_IDENTITY()
INSERT SalaryDetail
(
[SalaryId],
[EmployeeId],
[Salary]
)
SELECT
@SalaryId,
A.[EmployeeID],
0.00
FROM Employee A
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO
--test
EXEC proc_test 2012, 4
GO
SELECT * FROM SalaryDetail
/*
1 2 3000.00
1 3 2000.00
1 4 1800.00
1 5 2100.00
1 6 5000.00
2 2 0.00
2 3 0.00
2 4 0.00
2 5 0.00
2 6 0.00
*/