27,580
社区成员
发帖
与我相关
我的任务
分享
/****** Object: StoredProcedure [dbo].[stu_room] Script Date: 08/10/2017 14:22:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stu_room]
AS
DECLARE @Stu_No NVARCHAR(20)
DECLARE @DorNo NVARCHAR(20)
DECLARE @RNo NVARCHAR(20)
DECLARE @id INT
DECLARE @i INT
SET @i = 1
SET @DorNo = 1
SET @RNo = '001'
DECLARE rs CURSOR LOCAL SCROLL
FOR
SELECT Stu_No
FROM dbo.Base_Student
OPEN rs
FETCH NEXT FROM rs INTO @Stu_No
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( SELECT Stu_sex
FROM Base_Student
WHERE Stu_No = @Stu_No
) = '男'
BEGIN
IF ( SELECT COUNT(*)
FROM dbo.Relation_Classroom
WHERE RNo = @RNo
AND DorNo = @DorNo
) < 6
BEGIN
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
SET @i = @i + 1
END
ELSE
BEGIN
SET @RNo = CAST(@RNO AS INT) + 1;
SET @RNo = '00' + CAST(@RNo AS NVARCHAR(20));
----缺少条件
----如果大于6了,就往下一个房间里插入数据
SET @i = 1
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
----缺少条件
END
END
ELSE
BEGIN
SET @DorNo = 2;
IF ( SELECT COUNT(*)
FROM dbo.Relation_Classroom
WHERE RNo = @RNo
AND DorNo = @DorNo
) < 6
BEGIN
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
SET @i = @i + 1
END
ELSE
BEGIN
SET @RNo = CAST(@RNO AS INT) + 1;
SET @RNo = '00' + CAST(@RNo AS NVARCHAR(20));
----如果大于6了,就往下一个房间里插入数据
SET @i = 1
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
END
END
FETCH NEXT FROM rs INTO @Stu_No
END
CLOSE rs
DEALLOCATE rs
/****** Object: StoredProcedure [dbo].[stu_room] Script Date: 08/10/2017 14:22:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stu_room]
AS
DECLARE @Stu_No NVARCHAR(20)
DECLARE @DorNo NVARCHAR(20)
DECLARE @RNo NVARCHAR(20)
DECLARE @id INT
DECLARE @i INT
SET @i = 1
SET @DorNo = 1
SET @RNo = '001'
DECLARE rs CURSOR LOCAL SCROLL
FOR
SELECT Stu_No
FROM dbo.Base_Student
OPEN rs
FETCH NEXT FROM rs INTO @Stu_No
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF ( SELECT Stu_sex
FROM Base_Student
WHERE Stu_No = @Stu_No
) = '男'
BEGIN
IF ( SELECT COUNT(*)
FROM dbo.Relation_Classroom
WHERE RNo = @RNo
AND DorNo = @DorNo
) < 6
BEGIN
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
SET @i = @i + 1
END
ELSE
BEGIN
SET @RNo = CAST(@RNO AS INT) + 1;
SET @RNo = '00' + CAST(@RNo AS NVARCHAR(20));
----缺少条件
----如果大于6了,就往下一个房间里插入数据
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
SET @i = @i + 1
----缺少条件
END
END
ELSE
BEGIN
SET @DorNo = 2;
IF ( SELECT COUNT(*)
FROM dbo.Relation_Classroom
WHERE RNo = @RNo
AND DorNo = @DorNo
) < 6
BEGIN
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
SET @i = @i + 1
END
ELSE
BEGIN
SET @RNo = CAST(@RNO AS INT) + 1;
SET @RNo = '00' + CAST(@RNo AS NVARCHAR(20));
----如果大于6了,就往下一个房间里插入数据
INSERT dbo.Relation_Classroom
( id, Stu_No, DorNo, RNo )
VALUES ( @i, @Stu_No, @DorNo, @RNo )
SET @i = @i + 1
END
END
FETCH NEXT FROM rs INTO @Stu_No
END
CLOSE rs
DEALLOCATE rs