Usage of SQL stored procedure cursor loop and How to Use cursor to write a simple loop, stored procedure cursor

Usage of SQL stored procedure cursor loop and How to Use cursor to write a simple loop, stored procedure cursor

The cursor and WHILE can be used to traverse each record in your query and pass the required fields to the variable for corresponding processing.

============================

DECLARE @A1 VARCHAR(10),@A2 VARCHAR(10),@A3 INTDECLARE CURSOR YOUCURNAME FOR SELECT A1,A2,A3 FROM YOUTABLENAMEOPEN YOUCURNAMEfetch next from youcurname into @a1,@a2,@a3while @@fetch_status<>-1beginupdate … set …-a3 where …

...... The operation you want to perform is written here

fetch next from youcurname into @a1,@a2,@a3endclose youcurnamedeallocate youcurname

-------------

During application development, we often encounter the following applications. We query the record set of a data table and recycle each record, using each record set to operate data on another table, such as insertion and update, we now assume that there is a business like this: The teacher selects courses for students in the class, the courses selected include philosophy, Marxist Political Economics, Introduction to Mao Zedong Thought, and Deng Xiaoping theory. The operations are as follows:

1) Check the list of students who have not yet graduated. After graduation, you cannot select courses;
2) When selecting students in batches, you also need to add a corresponding course;
3) Click Add to finish the course selection.

The small amount of data may not show the weakness of using Java programs to directly perform database operations multiple times, because every time it operates the database, there are frequent direct interaction with the database I/O, and this performance sacrifice is really not appropriate. Let's look at the following method, through the cursor method of the stored procedure to achieve: create a stored procedure:

Create PROCEDURE P_InsertSubject@SubjectId intASDECLARE rs CURSOR LOCAL SCROLL FORselect studentid from student where StudentGradu = 1OPEN rsFETCH NEXT FROM rs INTO @tempStudentIDWHILE @@FETCH_STATUS = 0BEGINInsert SelSubject values (@SubjectId,@tempStudentID)FETCH NEXT FROM rs INTO @tempStudentIDENDCLOSE rsGO

When processing record set loops using cursors, perform the following steps:

1. Pass the record set to the cursor;
2. Open the cursor
3. Start Loop
4. values from the cursor
5. Check that the row is returned.
6. Processing
7. Disable Loop
8. Close the cursor.

The above method has undoubtedly improved the performance, but we also think that when writing a stored procedure, we sometimes try to avoid using cursors for operations as little as possible, therefore, we can use the following methods to transform the stored procedure:

Create PROCEDURE P_InsertSubject@SubjectId intASdeclare @i int,@studentidDECLARE @tCanStudent TABLE(studentid int,FlagID TINYINT)BEGINinsert @tCanStudent select studentid,0 from student where StudentGradu = 1SET @i=1WHILE( @i>=1)BEGINSELECT @studentid=''SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE flagID=0SET @i=@@ROWCOUNTIF @i<=0 GOTO Return_LabInsert SelSubject values (@SubjectId,@studentid)IF @@error=0UPDATE @tCanStudent SET flagID=1 WHERE studentid = @studentidReturn_Lab:ENDEndGO

Now let's analyze the above stored procedure. The implementation method is to first store the qualified record set data in a table variable, add a FLAGID In the table variable to store the data with the initial value 0, and then loop through the record set. Each cycle will change the value of the corresponding FLAGID to 1, then, we can find the condition that the condition is equal to 0 based on the loop. We can see that each loop will reduce the number of records processed once, and then insert them to the selected course schedule cyclically, the operation is completed until the number of records in the record set is 0.

Compared with the applications of the above several cyclic methods, we will know that sometimes we may implement different methods for the same function, and the performance of the final application will be greatly affected, the second and third methods greatly reduce the frequency of database interaction I/O operations, which saves a lot of time. Method 3 can avoid using cursors and save unnecessary costs.

You can use the SQL Agent to execute scheduled tasks and put prepared SQL statements in scheduled tasks to achieve amazing results, such as scheduled data backup and scheduled execution of specific operations, when a large number of data records are involved in a loop operation, the cursor is used here. Of course, there are loop statements in SQL, such as While. However, the while function can only implement general operations, and the cursor function is more powerful. It can be used to cyclically operate data in a specified set to achieve dynamic operations, the following documents are for Archival Use.

WHILE

Set the conditions for repeated execution of SQL statements or statement blocks. If the specified condition is true, the statement is executed repeatedly. You can use the BREAK and CONTINUE keywords to control the execution of statements in the while loop within the loop.

Syntax

WHILE Boolean_expression  { sql_statement | statement_block }  [ BREAK ]  { sql_statement | statement_block }  [ CONTINUE ]

Parameters

Boolean_expression

Returns a TRUE or FALSE expression. If a Boolean expression contains a SELECT statement, you must enclose the SELECT statement in parentheses.

Copy codeThe Code is as follows:
{SQL _statement | statement_block}

Group statements defined by statement blocks. To define statement blocks, use the control flow keywords BEGIN and END.

BREAK

This causes the exit from the WHILE loop at the innermost layer. Execute any statement that appears after the END keyword. The END keyword is the END mark of the loop.

CONTINUE

Execute the WHILE loop again and ignore any statements after the CONTINUE keyword.

Note

If two or more WHILE loops are nested, the BREAK of the inner layer will exit the next outer loop. First, run all the statements after the inner loop ends, and then run the next outer loop again.

Example

declare @i intset @i=1while @i<30begininsert into test (userid) values(@i)set @i=@i+1end

------------------------------------------------------------

While condition begin execute operation set @ I = @ I + 1end

A. Use BREAK and CONTINUE in nested IF... ELSE and WHILE

In the following example, if the average price is less than $30, the WHILE loop will double the price and select the highest price. If the maximum price is less than or equal to $50, the while loop restarts and doubles the price again. This cycle continues to double the price until the maximum price exceeds $50, then exit the WHILE loop and print a message.

USE pubsGOWHILE (SELECT AVG(price) FROM titles) < $30BEGIN  UPDATE titles  SET price = price * 2  SELECT MAX(price) FROM titles  IF (SELECT MAX(price) FROM titles) > $50  BREAK  ELSE  CONTINUEENDPRINT 'Too much for the market to bear'

B. Use the WHILE

The following WHILE structure is part of the process named count_all_rows. In the following example, the WHILE structure tests the return value of the function @ FETCH_STATUS for the cursor. Because @ FETCH_STATUS may return-2,-1, or 0, all cases should be tested. If a row is deleted from the cursor results after execution of this stored procedure, the row is skipped. After successfully extracting (0), the SELECT statement inside the BEGIN... END loop is executed.

Single Variable Loop

USE pubsDECLARE tnames_cursor CURSORFOR  SELECT TABLE_NAME   FROM INFORMATION_SCHEMA.TABLESOPEN tnames_cursorDECLARE @tablename sysname--SET @tablename = 'authors'FETCH NEXT FROM tnames_cursor INTO @tablenameWHILE (@@FETCH_STATUS <> -1)BEGIN  IF (@@FETCH_STATUS <> -2)  BEGIN   SELECT @tablename = RTRIM(@tablename)   EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '     + @tablename )  PRINT ' ' END  FETCH NEXT FROM tnames_cursor INTO @tablenameENDCLOSE tnames_cursorDEALLOCATE tnames_cursor

Multi-variable Loop

Create procedure my_FeeCount ASdeclare @ expiration time char (10) declare @ area char (20) declare @ Number of paying users char (10) declare @ SQL char (2000) declare cur_data cursor for select convert (varchar (10), expiration date, 120) as expiration time, region, count (distinct main_id) as paying users from V_aipu_fee where submission time> = convert (varchar (10), getdate ()-90,120) and submission time <convert (varchar (10), getdate () + 1-90,120) and charge type = 'renewal billing' Group by convert (varchar (10), expiration time, 120), Region order by convert (varchar (10 ), expiration time, 120) open cur_datafetch next from cur_data into @ expiration time, @ region, @ Number of paying users while (@ fetch_status = 0) begin set @ SQL = 'update' + RTRIM (@ region) + 'Accept = '+ RTRIM (@ region) + 'Accept +' + RTRIM (@) + 'where billing date = ''' + RTRIM (@ expiration time) + ''' print @ SQL fetch next from cur_data into @ expiration time, @ region, @ paying users endclose cur_data

Next we will introduce SQL to write a simple loop Using cursor.

1. troubleshooting

Compared with SQL server, SQL server only writes two simple stored procedures. Of course, although the IQ is not high, it is not as low as this. Most of the day is spent on troubleshooting.

System. Data. SqlClient. SqlException: the SQL Server does not exist or the access is denied. At

System. data. sqlClient. connectionPool. getConnection (Boolean & isInTransaction) at System. data. sqlClient. sqlConnectionPoolManager. getPooledConnection (SqlConnectionString options, Boolean & isInTransaction) at System. data. sqlClient. sqlConnection. open () at _ 20060409. webForm1.test () in d: \ work files \ 20060409 \ webform1.aspx. cs: line 52

This error is reported when the program cannot be connected. In the service, all services except MSSQLserverADHelper are enabled or fail; The name is correct, there is no alias, And the Instance name is not started; the port number is 1433 on both the server and client. In order to find out the problem, I had to go online and check the result and find that this problem was gone after the network cable was connected. Dizzy, it seems that there are few patches.

2. Write Loop

The first requirement is that I have two tables, rights and roles.

The table structure is as follows:

rights　　-------------------　　rightid int　　right varchar(20)　　　　＝＝＝＝＝＝＝＝＝＝　　　　roles　　-------------------　　roleid int　　roletype int　　role varchar(20)　　rightid int

In my role table, there is a role admin. Admin has all the permissions in the right table, but there are four fields. if I finish this speed by hand, I can add this number. Saving your hands is a waste of effort.

I wrote it in the query analyzer for a long time, and finally it was just a crash.

First, use the truncate table to clear the original failure information of the role. It is faster than the delete statement, but the quantity is less than the delete statement.

Create a temporary storage process for filling.

Create procedure TempFill declare mycursor cursor for select rightid from rights -- the two are a sentence that defines the cursor, then get a rightid from rights to open mycursor declare @ rightid int -- Define a variable fetch mycursor into @ rightid -- give the current value of mycursor to @ rightid while @ fetch_status = begin insert into rolestable (roletype, role, rightid) values (, 'admin', @ rightid) fetch mycursor into @ rightid end close mycursor deallocate mycursor

This completes writing.

Execute TempFill and execute it again.