Usage of the SQL stored procedure cursor loop and how SQL uses cursor to write a simple loop _mssql

Source: Internet
Author: User
Tags closing tag getdate rtrim

With cursors, and while you can traverse each record in your query and pass the required fields to the variable for handling accordingly

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

DECLARE 
@A1 VARCHAR (a),
@A2 VARCHAR (a),
@A3 INT
DECLARE CURSOR youcurname for SELECT a1,a2,a3 Youtablename
OPEN youcurname
fetch NEXT from Youcurname to @a1, @a2, @a3 while
@ @fetch_status <>-1
begin
Update ... set ...-a3 where ...

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

FETCH NEXT from Youcurname to @a1, @a2, @a3 end close
youcurname
deallocate youcurname

—————————————

At the time of application development, we may often encounter the following applications, by querying the recordset of the datasheet, looping through each record, and manipulating the data of another table through each recordset, such as inserts and updates, we now assume that there is a business: The teacher chooses classes for the students in the class, Selected courses such as philosophy, Marxist political economy, introduction to Mao Zedong Thought, Deng Xiaoping theory of these courses, now operates mainly as follows:

1 The first to inquire these students have not graduated from the list, after graduation can not be selected classes;
2 in the batch selection of students, but also need to add a corresponding course;
3 Point added after the end of elective course.

The small amount of data may not see the use of Java programs directly to do database operations this way to achieve the weaknesses, because it every time in the operation of the database, there are frequent and database I/O direct interaction, this performance of the sacrifice is not supposed, then we look at the following method, Implemented through the cursor method of a stored procedure: establishing a Stored procedure:

Create PROCEDURE p_insertsubject
@SubjectId int
as
DECLARE rs CURSOR local SCROLL for
select StudentID From student where Studentgradu = 1
OPEN rs
FETCH NEXT from RS to @tempStudentID while
@ @FETCH_STATUS = 0
   begin
Insert selsubject values (@SubjectId, @tempStudentID)
FETCH NEXT from Rs into @tempStudentID
end< C12/>close rs Go

When you are working with a cursor on a recordset loop, you typically perform the following steps:

1. Pass the recordset to the cursor;
2. Open cursor
3. Start cycle
4. Take a value from the cursor
5. Check that line is returned
6, processing
7, closed cycle
8, close the cursor

The above method has certainly been improved a lot in performance, but we also think that when the stored procedure is written, sometimes we try to avoid using cursors to operate, so we can also transform the above stored procedure, use the following method to implement:

Create PROCEDURE p_insertsubject
@SubjectId int
as declare @i int,
@studentid
DECLARE @ Tcanstudent TABLE
(
studentid int
, Flagid TINYINT
)
BEGIN
Insert @tCanStudent Select studentid,0 from student where Studentgradu = 1
SET @i=1 while
(@i>=1)
BEGIN
SELECT @studentid = ' '
SELECT top 1 @studentid = StudentID from @tCanStudent WHERE flagid=0
SET @i=@ @ROWCOUNT
IF @i<=0 GOTO R Eturn_lab
Insert selsubject values (@SubjectId, @studentid)
IF @ @error =0
UPDATE @tCanStudent SET flagid =1 WHERE StudentID = @studentid
return_lab: End of Go

We now analyze the above stored procedure, which is implemented by first storing the recordset data that satisfies the condition in a table variable, and adding a flagid in the table variable to store the data initial value of 0, then loop the recordset, each time, You change the value of the corresponding Flagid to 1, and then according to the loop to find satisfying conditions equal to 0, you can see that each cycle once, the processing of the recordset will be less once, and then the loop to select a good schedule to insert, until the recordset's bar number is 0 o'clock stop loop, this completes the operation.

Comparing the application of the above several cyclic methods, you will know that sometimes we can implement different methods for the same function, but the impact of the end application performance will be very large, the second and third greatly reduce the number of database interactive I/O operations, will save a lot of time, Method Three avoids the use of cursors and can save unnecessary overhead.

The agent using SQL can perform scheduled tasks. Put the written SQL statement in the planning task, you can achieve wonderful results, such as scheduled backup data, timed to perform specific operations, etc., when it involves a loop operation of a lot of data, here will use the cursor, of course, SQL also has a circular statement, If you use a while. While the function can only achieve a general operation, the function of the cursor more powerful, can be in a specified set of loop operation data, to achieve dynamic operation, then more cattle, oh, the following information for archival purposes.

While

Sets the criteria for repeating the execution of a SQL statement or statement block. Executes the statement repeatedly as long as the specified condition is true. You can use the break and CONTINUE keywords to control the execution of statements within the while loop inside the loop.

Grammar

While Boolean_expression
  {sql_statement | statement_block}
  [Break]
  {sql_statement | statement_block}
   [CONTINUE]

Parameters

Boolean_expression

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

Copy Code code as follows:

{sql_statement | statement_block}

Transact-SQL statements or groups of statements defined with statement blocks. To define a statement block, use the control flow keyword BEGIN and end.

Break

Causes the exit from the inner while loop. Any statements that appear after the end keyword will be executed, and the ending keyword is a circular closing tag.

CONTINUE

Causes the while loop to restart execution, ignoring any statements after the CONTINUE keyword.

Comments

If two or more while loops are nested, the inner break causes the exit to the next outer loop. First Run all statements after the inner loop ends, and then the next outer loop starts again.

Example

DECLARE @i int
set @i=1 while
@i<30
begin
inserts into Test (userid) VALUES (@i)
set @i=@i+1
End

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

While condition
begin
Execute Operation
set @i=@i+1
end

A. In the nested IF ... Use break and CONTINUE in ELSE and while

In the following example, if the average price is less than the $30,while cycle, the price is doubled and then the maximum is selected. If the highest price is less than or equal to the $50,while cycle reboot and double the price again. The loop constantly doubles the price until the highest price exceeds $, then exits the while loop and prints a message.

Use the pubs go while
(SELECT AVG. (price) from titles) < $
BEGIN
  UPDATE titles
  SET price = Price * 2
   select Max (price) from titles
  IF (SELECT MAX (price) from titles) > $
  break
  ELSE
  CONTINUE
End
PRINT ' Too of the ' Market to bear '

B. Using while in the process with cursors

The following while structure is a 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 it starts executing this stored procedure, the row is skipped. A successful fetch (0) will execute the BEGIN ... The SELECT statement inside the end loop.

Single variable loop

Use the pubs
DECLARE tnames_cursor cursor for the
  SELECT table_name from 
  INFORMATION_SCHEMA. TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--set @tablename = ' authors '
FETCH NEXT from Tnames_cursor into @tablename while
(@ @FETCH_STATUS <>-1)
BEGIN
  IF (@ @FETCH_STATUS <>-2) C12/>begin 
  SELECT @tablename = RTRIM (@tablename) 
  EXEC (' SELECT ' + @tablename + ' = count (*) from ' 
    + @ TableName)
  PRINT ' End
  FETCH NEXT from tnames_cursor to @tablename end
Close
Tnames_ Cursor
deallocate tnames_cursor

Multi-Variable loop

CREATE  PROCEDURE My_feecount as
declare @ expiration  char (m)
declare @ piece area   char (%)
DECLARE @ contributor number of users CHAR (
declare @sql char)
declare cur_data cursor for 
 select CONVERT (varchar (10), due time,) as Expiration time, Slices, count (distinct main_id) as number of users from 
 V_aipu_fee where commit time >=convert (varchar), GETDATE () -90,120) and submit time <convert (varchar), GETDATE () +1-90,120)
 and charge type = ' renewal charge '
 Group by convert (varchar (10), due time, 120), slice area ORDER BY
 CONVERT (varchar (10), due time,) 
open Cur_data
fetch NEXT from Cur_data to @ due time, @ Zone, @ Contributor number of users While 
(@ @fetch_status = 0) 
begin
  Set @sql = ' Update ' +rtrim (@ region) + ' +rtrim ' (@ Zone) + ' paid-in + ' +rtrim (@ fee users) + ' where charge date = ' +rtrim (@ Expires) + ' '
 print @sql
 fetch NEXT from cur_data to @ expiration, @ piece area, @ Toll user end
Close Cur_data

Next to introduce you to SQL use cursor write a simple loop

1. Wrong schedule

and SQL Server A day's effort, only two simple stored procedures are written. Of course, although the IQ is not high enough to this part. Half the day is spent on the wrong line.

The System.Data.SqlClient.SqlException:SQL Server does not exist or 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 () on _20060409.webform1.test () in D:\work files\20060409 \webform1.aspx.cs:line 52

The sudden report of this mistake, this is not connected from the program. All services except MSSQLServerADHelper are turned on or out of service; name is correct, no alias, no instance name, port number server-side and client are 1433. In order to find out the problem had to go online to check the results found that even after the network cable this problem is gone. Halo, it seems to be less hit what patch.

2. Write cycle

First of all, I have two tables in my hand, rights and roles.

The table structure is as follows:

Rights
-------------------
rightid int right
varchar
==========
roles
-- -----------------
Roleid int
roletype int role
varchar ()
Rightid int    

There is a role admin in my list. Admin has all the permissions in the right table, but there are four fields, and if I finish this speed with my handwriting, I think I'll forget the number. It takes a mind to save the strength of the hand.

In the Query Analyzer wrote a half-day, finally is chi out.

The first use of TRUNCATE table to the role of the original failure information cleared, heard will be faster than delete, but the number of small see no obvious effect.

Then create a temporary stored procedure for the fill.

CREATE PROCEDURE Tempfill
declare mycursor cursor for
Select Rightid from rights--These are the sentences that define the cursor and then take it from the rights A rightid to
the cursor open mycursor
declare @rightid int--defines a variable
fetch mycursor into @rightid--put Mycurso R Current value to @rightid while
@ @fetch_status =
begin
inserts into Rolestable (Roletype,role,rightid) VALUES (, ' admin ', @rightid)
fetch mycursor into @rightid end close
mycursor
deallocate Myc Ursor          

It's finished.

Execute Tempfill to execute him again.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.