In SQL's SP development, the cursor is an indispensable role. However, some improper use cursors will cause serious system performance, all, many people want to find a way, that is, to achieve the function of the cursor, and have little effect on performance. This is a loop instead of a cycle in this article.
Under normal circumstances, the purpose of using the cursor is to reach a loop, but if we do it in a loop, how to control the current variable value is the key.
The table used in the following code
Create Table TbluserDetail
(
Username nvarchar (30) Not null, / * User name * /
Userage int, / * user age * /
UserDepartment NVARCHAR (30) / * User Division * /
In the above table, there are several data.
Now, we must take out the top 5 sentences of each department, and you will be from the old to the small row.
It can be used by the game.
code show as below
Declare @DEPARTMENT NVARCHAR (30)
Declare CurdePartment Cursor for Select Distinct UserDepartment from TbluserDetail
Open CurDepartment
Fetch next from curdePartment Into @DEPARTMENT
While @@ fetch_status = 0
Begin
SELECT TOP 5 Username
From tbluserdetail
Where userDepartment = @ departmentment
Order by Userage DESC
Fetch next from curdePartment Into @DEPARTMENT
end
Close CurDepartment
Deallocate CurDepartment
Use cycles to use a temporary table
Declare @TBLDEPARTMENT TABLE (
Uid Int Identity (1, 1),
Department NVARCHAR (30))
Declare @DEPARTMENT NVARCHAR (30)
Declare @ncount as int
Declare @allcount as int
SELECT @ ncount = 1
INSERT @TBLDEPARTMENT (Department)
Select Distinct UserDepartment from TbluserDetail
SELECT @allcount = @@ rowcount
While @ncount <= @ allcount
Begin
SELECT TOP 5 Username
From tbluserdetail
WHERE userDepartment = (Select Department from @TBLDEPARTMENT WHERE UID = @ ncount)
Order by Userage DESC
SELECT @ ncount = @ ncount 1
end
In this way, the function is the same. When using a cycle, it is not easy to cause a deadlock caused by improper use of the cursor, which is obvious when operating a large amount of data.
Of course, there are some cases that cannot be used instead of cursors. For example: there are many exactly the same records in a table, and the first one is updated, the second is not changed.
There is no way to cycle at this time.
