Several ways to write SQL circular statements
From: https://www.cnblogs.com/guorongtao/p/11939751.html
Several ways to write SQL circular statements
1. Normal loop statement
1 2 3 4 5 6 7 8 9 10 11 12 13 14 declare @orderNum varchar(255) create table #ttableName(id int identity(1,1),Orders varchar(255)) declare @n int,@rows int insert #ttableName(orders) select orderNum from pe_Orders where orderId<50 --select @rows=count(1) from pe_Orders select @rows =@@rowcount set @n=1 while @n<=@rows begin select @orderNum=OrderNum from PE_Orders where OrderNum=(select Orders from #ttableName where id=@n) print (@OrderNum) select @n=@n+1 end drop table #ttableName
2. Cursor loop without transaction
1 2 3 4 5 6 7 8 9 10 11 12 13 declare @orderN varchar(50) --A temporary variable used to hold cursor values declare y_curr cursor for --Declare cursor as orderNum select orderNum from pe_Orders where orderId<50 open y_curr --Open cursor fetch next from Y_curr into @orderN ----Start loop cursor variable while(@@fetch_status=0) ---Return by FETCH Statement, not the state of any cursors currently open by the connection. begin print (@orderN) update pe_Orders set Functionary+@orderN where orderNum=@orderN --Operation database fetch next from y_curr into @orderN --Start loop cursor variable end close y_curr --Close cursor deallocate y_curr --Release cursor
3. Cursor loop with transaction
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 select orderNum,userName,MoneyTotal into #t from pe_Orders po DECLARE @n int,@error int --set @n=1 set @error=0 BEGIN TRAN --Declare start transaction declare @orderN varchar(50),@userN varchar(50) --A temporary variable used to hold cursor values declare y_curr cursor for --Declare cursor as orderNum,userName select orderNum,userName from PE_Orders where Orderid<50 open y_curr fetch next from y_curr into @orderN,@userN while @@fetch_status = 0 BEGIN select isnull(sum(MoneyTotal),0),orderNum from #t where username=@userN -- set @n=@n+1 set @error=@error+@@error --Record each run sql Is the rear 0 correct fetch next from y_curr into @orderN,@userN END IF @error=0 BEGIN commit tran ---Transaction commit END ELSE BEGIN ROLLBACK TRAN ---Transaction rollback END close y_curr deallocate y_curr DROP TABLE #t
4. if statement usage example
1 2 3 4 5 6 7 8 9 10 declare @a int set @a=12 if @a>100 begin print @a end else begin print 'no' end
5. Example of using while statement
1 2 3 4 5 6 7 8 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end -- Set repeat SQL Condition of a statement or statement block. Repeat the statement as long as the specified condition is true. have access to BREAK and CONTINUE Keyword in loop internal control WHILE Execution of statements in a loop. This item was previously obtained from the Internet!
6. Temporary tables and try
1 2 3 4 5 6 7 8 -- Add temporary table select * into #csj_temp from csj -- Delete temporary table try begin try -- Detection code start drop table #csj_temp end try begin catch -- Error start end catch
7. Cursor cycle read record
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 declare @temp_temp int --declare @Cur_Name --@Cur_Name="aaa" --------------------------------- Create cursor --Local(Local Cursor ) DECLARE aaa CURSOR for select House_Id from House_House where Deleted=0 or deleted is null ----------------------------------- Open cursor Open aaa ----------------------------------- Traversal and fetch cursors fetch next from aaa into @temp_temp --print @temp_temp while @@fetch_status=0 begin --Do what you have to do select * from House_monthEnd where House_Id=@temp_temp fetch next from aaa into @temp_temp -- Assign value to variable -- end ----------------------------------- Close cursor Close aaa ----------------------------------- Delete cursor Deallocate aaa
Reading catalog
In the process of database development, we often encounter the situation of traversing data tables. When we mention traversing tables, we may think of using cursors at first. Although using cursors is intuitive and easy to understand, it does not comply with the principle of set oriented operation, and its performance is lower than that of set oriented. Of course, from the perspective of set oriented operation, there are also two methods to traverse the table. To sum up, there are the following methods to traverse the table.
- Use cursor
- Using table variables
- Use temporary tables
My requirement is to add a column of fullname for the HR.Employees table, with the value of firstname+lastname.
-- The requirement is to add a new column fullname，Value firstname+lastname ALTER TABLE HR.Employees ADD fullname NVARCHAR(30) NULL; GO
The original effect is shown in the figure below.
This requirement could have been solved with an sql statement, as shown in the following code. However, in order to demonstrate the traversal of the table, I still use these three methods to implement it.
USE TSQLFundamentals2008; GO UPDATE HR.Employees SET fullname= firstname+' '+lastname;
Use cursor
The code for using cursors is cumbersome. To sum up, it mainly includes the following steps: declaring cursors, opening cursors, using cursors, closing cursors and releasing cursors. The sample code is as follows.
-- Method 1: cursor -- Declare variable DECLARE @empid AS INT, @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20); -- declare cursor DECLARE C_Employees CURSOR FAST_FORWARD FOR SELECT empid,firstname,lastname FROM HR.Employees ORDER BY empid; OPEN C_Employees; -- Take the first record FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname; WHILE @@FETCH_STATUS=0 BEGIN -- operation UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid; -- Remove a record FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname; END -- Close cursor CLOSE C_Employees; -- Release cursor DEALLOCATE C_Employees;
Run the script, and the effect is shown in the figure below.
It can be seen that the effect we want has been achieved.
Using table variables
Because the use of cursors has performance problems and violates the set oriented idea, it is necessary to use the set oriented idea to find a better solution. The following method is implemented by using table variables. The code is as follows.
1 -- Method 2: use table variables 2 -- Declare table variables 3 DECLARE @temp TABLE 4 ( 5 empid INT, 6 firstname NVARCHAR(10), 7 lastname NVARCHAR(20) 8 ); 9 10 -- Inserts data from the source table into a table variable 11 INSERT INTO @temp(empid, firstname, lastname ) 12 SELECT empid,firstname,lastname FROM HR.Employees 13 ORDER BY empid; 14 15 -- Declare variable 16 DECLARE 17 @empid AS INT, 18 @firstname AS NVARCHAR(10), 19 @lastname AS NVARCHAR(20); 20 21 WHILE EXISTS(SELECT empid FROM @temp) 22 BEGIN 23 -- You can also use top 1 24 SET ROWCOUNT 1 25 SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM @temp; 26 UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid; 27 SET ROWCOUNT 0 28 29 DELETE FROM @temp WHERE empid=@empid; 30 END
Use temporary tables
Temporary tables can also realize the function of table variables, so we can also use temporary tables to realize this requirement. The code is as follows.
1 -- Method 3: use temporary tables 2 -- Create temporary table 3 IF OBJECT_ID('tempdb.dbo.#tempemployees','U') IS NOT NULL DROP TABLE dbo.#tempemployees; 4 GO 5 6 SELECT empid,firstname,lastname 7 INTO dbo.#tempemployees 8 FROM HR.Employees 9 ORDER BY empid; 10 11 --SELECT * FROM dbo.#tempemployees; 12 13 -- Declare variable 14 DECLARE 15 @empid AS INT, 16 @firstname AS NVARCHAR(10), 17 @lastname AS NVARCHAR(20); 18 19 WHILE EXISTS(SELECT empid FROM dbo.#tempemployees) 20 BEGIN 21 -- You can also use top 1 22 SET ROWCOUNT 1 23 SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM dbo.#tempemployees; 24 UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid; 25 SET ROWCOUNT 0 26 27 DELETE FROM dbo.#tempemployees WHERE empid=@empid; 28 END