Shulou(Shulou.com)06/02 Report--

Introduction

Leaving aside the concepts, steps, and syntax of cursors, let's look at an example:

Table 1 OriginSalary Table 2 AddSalary

There are now two tables, one is the OriginSalary table-payroll table, with three fields: 0_ID employee number (NVARCHAR), O_Name employee name (NVARCHAR), and O_Salary salary (FLOAT).

Another table, AddSalary table-salary increment table. There are two fields, O_ID employee number and A_Salary increase salary. The O_ID of the two tables correspond one to one. Now ask for the salary increase + the original salary = the current salary, that is, O_Salary=O_Salary+A_Salary, and modify the salary field of the table OriginSalary.

For some programmers who are not familiar with cursors, this is not a difficult problem, and it may be easy to implement it programmatically. Let me first talk about the idea of using ASP.NET programs to solve this problem:

1. First get the number of records in the table OriginSalary and write a loop.

two。 Write the SQL statement "select * from dbo.OriginSalary as A left join dbo.AddSalary as B on A.O_ID=B.O_ID" to get the view.

3. Use Dataset to get O_Salary=O_Salary+A_Salary.

4. Write the UPDATE statement "update OriginSalary set obtainable Salary =" the added value "where Olympus ID =" get the value "

5. Cycle 3 times to complete this function.

Another way is to write stored procedures, which I won't list here.

I want you to think about this problem and some examples of batch processing before learning cursors. Some people may say, "can't the database process the data one by one? take out the data of the table AddSalary row by row, and then modify the table OriginSalary data row by row?" The answer is, of course, yes. This is the concept of cursors. In the next chapter, we will talk about what a cursor is. I will use cursors to solve the problem left to you just now.

1.1 the concept of cursors

Cursor, which enables users to access the result set returned by SQL Server row by row. One of the main reasons for using cursor is to convert collection operations into single record processing. After retrieving data from the database in SQL, the result is placed in an area of memory, and the result is often a collection of multiple records. The cursor mechanism allows the user to access these records line by line within the SQL server, and to display and process these records according to the user's wishes.

1.2 advantages of cursors

The following advantages of cursors can be obtained from the cursor definition, which make cursors play an important role in practical applications:

1) allows the program to perform the same or different operation on each row set returned by the query statement select, instead of performing the same operation on the entire row set.

2) provides the ability to delete and update rows in a table based on cursor location.

3) cursors actually act as a bridge between set-oriented database management system (RDBMS) and line-oriented programming, so that the two processing methods are communicated through cursors.

1.3 use of cursors

Having talked about the advantages of this multi-cursor, let's unveil the mystery of the cursor ourselves.

The order in which cursors are used: naming cursors, opening cursors, reading data, closing cursors, deleting cursors.

1.3.1 declare cursors

The simplest cursor declaration: DECLARE CURSOR FOR

The select statement can be a simple query, a complex successive query and a nested query.

Example: [table 2 AddSalary as an example]

Declare mycursor cursor for select * from AddSalary

So I declare a cursor mycursor on the table AddSalary

[advanced remarks]

DECLARE [INSENSITIVE] [SCROLL] CURSORFOR

Here I'll talk about [INSENSITIVE] and [SCROLL] in cursor intermediate applications.

INSENSITIVE

Indicates that MS SQL SERVER will store the data records selected by the cursor definition in a temporary table (established in the tempdb database). All reads to the cursor are answered by the temporary table. Therefore, the modification of the basic table does not affect the data extracted by the cursor, that is, the cursor does not change with the content of the basic table, and the basic table cannot be updated through the cursor. If the reserved word is not used, updates and deletions to the underlying table are reflected in the cursor.

It should also be noted that the cursor automatically sets the INSENSITIVE option when the following occurs.

a. Use DISTINCT, GROUP BY, HAVING UNION statements in SELECT statements

b. Use OUTER JOIN

c. The selected arbitrary table has no index

d. Treat the real value as the selected column.

SCROLL

Indicates that all extraction operations (such as FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If the reserved word is not used, only the NEXT extraction operation can be performed. Thus it can be seen that SCROLL greatly increases the flexibility of extracting data, so that any row of data records in the result set can be read at will without having to close

Reopen the cursor.

1.3.2 Open the cursor

It's very simple. Let's just open the cursor mycursor we just declared.

OPEN mycursor

1.3.3 read data

FETCH [NEXT | PRIOR | FIRST | LAST] FROM {cursor name | @ cursor variable name} [INTO @ variable name [,...] ]

Parameter description:

NEXT takes the data from the next row and takes the next row as the current row (incremented). Because the row pointer points to the first row of the cursor after the cursor is opened, the first FETCH NEXT operation will get the first row of data in the cursor set. NEXT is the default cursor extraction option.

INTO @ variable name [,...] Put the column data of the extraction operation into a local variable. Each variable in the list is associated with the corresponding column in the cursor result set from left to right. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the result column data type. The number of variables must match the number of columns in the cursor selection list.

Now let's fetch the data from the mycursor cursor!

When the cursor is opened, the row pointer will point before the first row of the cursor set. If you want to read the first row of data in the cursor set, you must move the row pointer to point to the first row. For this example, you can read the first row of data using the following operations:

Eg: Fetch next from mycursor or Fetch first from mycursor

So I took out the data in the cursor, but this alone is not enough, we also need to assign the extracted data to the variable.

/ / declare 2 variables

Declare @ O_ID NVARCHAR (20)

Declare @ A_Salary float

/ / pass the extracted value into the 2 variables just declared

Fetch next from mycursor into @ Olympiad ID @ A_Salary

1.3.4 close the cursor

CLOSE mycursor

1.3.5 Delete cursors

DEALLOCATE mycursor

1.3.6 case training

After introducing the five steps of using cursors above, let's get started and practice using cursors to fetch data from Table 2 AddSalary.

In order to run the cursors we created, we wrote the cursors in the stored procedure so that we could see the whole process of using the cursors.

Create a new stored procedure in sqlserver2000:

CREATE PROCEDURE PK_Test

AS

/ / declare 2 variables

Declare @ O_ID nvarchar (20)

Declare @ A_Salary float

/ / declare that the number of parameters in a cursor mycursor,select statement must be the same as the variable name taken from the cursor

Declare mycursor cursor for select O_ID,A_Salary from AddSalary

/ / Open the cursor

Open mycursor

/ / take the data from the cursor and assign it to the two variables we just declared

Fetch next from mycursor into @ Olympiad ID

/ / determine the status of the cursor

/ / 0 fetch statement succeeded

/ /-1 fetch statement failed or the row is not in the result set

/ /-2 the extracted row does not exist

While (@ @ fetch_status=0)

Begin

/ / shows the value we take out with a cursor each time

Print 'cursor successfully fetched a piece of data'

Print @ O_ID

Print @ A_Salary

/ / use a cursor to fetch the next record

Fetch next from mycursor into @ Olympiad ID

End

/ / close the cursor

Close mycursor

/ / undo the cursor

Deallocate mycursor

GO

Through the comments above, I think you all understand the whole process of creating cursors. But we are still an abstract understanding, we learn any knowledge, must be used in practice, in order to make abstract things concrete.

So let's run the stored procedure to see how the cursor is valued:

We open the query analyzer of SQLSERVER2000, and after setting up the database, we execute the stored procedure.

Exec PK_Test

Let me see the effect (pictured)

Through the example, we can see that the cursor fetches all the values line by line. Then I would like to ask you not to look at the following answers. I just left a question in the introduction to see if it can be solved.

Now let's write a stored procedure to solve the problem I left behind just now.

CREATE PROCEDURE PK_SalaryAdd

AS

Declare @ O_ID nvarchar (20), @ A_Salary float

Declare mycursor cursor for select O_ID,A_Salary from AddSalary

Open mycursor

Fetch next from mycursor into @ Olympiad ID

While (@ @ fetch_status = 0)

Begin

Update OriginSalary set O_Salary=O_Salary+@A_Salary where O_ID=@O_ID

Fetch next from mycursor into @ Olympiad ID

End

Close mycursor

Deallocate mycursor

GO

In the old way, we use a query analyzer to execute our stored procedure to see what the result looks like.

Exec PK_SalaryAdd

Let me see the effect (pictured)

Execute the stored procedure and see that we have affected three rows of data

Using the SQL statement, look at the result of the table OriginSalary now:

1.4 concluding remarks

I am glad that you can finish this tutorial, in fact, this is only the most basic application of cursors, showing that there may be more complex cursors in the logical relationship of life. But only when we learn to walk can we run.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.