1. Define the cursor Definition
The core of a cursor statement is to define a cursor Identification name and associate it with a query statement. The DECLARE statement is used to DECLARE the cursor. It defines the data set stored in the cursor through the SELECT query. Statement format:
DECLARE cursor name [INSENSITIVE] [SCROLL]
Cursor for select statement
[FOR {read only | UPDATE [OF column name table]}]
Parameter description:
INSENSITIVE option: indicates that the defined cursor uses the copy of the query result of the SELECT statement, and the operation on the cursor is based on the copy. Therefore, data changes to the basic table of the cursor during this period cannot be reflected in the cursor. This type of cursor cannot be used to modify the data in the basic table.
SCROLL option: specifies that the cursor can be used to extract data from all cursor data locating methods. The cursor locating methods include PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n options.
Select statement: a standard SELECT query statement. The query result is a cursor data set. One or more tables in the cursor data set are called the cursor base table.
When a cursor declaration statement has one of the following conditions, the system automatically defines the cursor as an INSENSITIVE cursor:
Keywords such as DISTINCT, UNION, group by, and HAVING are used in the SELECT statement;
No unique index exists in any base table of the cursor.
Others
Read only: READ-ONLY cursor is defined.
UPDATE [OF column name table] Option: defines columns that can be modified by the cursor. If you use the OF column name table option, only the specified columns can be modified. Otherwise, all columns can be modified.
For example, to query the instructor name and the course name, the statement for defining the cursor TCURSOR is as follows:
DECLARE TCURSOR CURSOR
SELECT tname, cname
FROM teacher, couse
WHERE teacher. tno = couse. tno
2. Open the cursor
Open the cursor statement and execute the query statement in the cursor definition. The query results are stored in the cursor buffer. And point the cursor pointer to the first tuples In the cursor area as the default access position of the cursor. The content of the query result depends on the setting of the query statement and the query conditions.
Statement format for opening a cursor:
Exec SQL OPEN <cursor Name> 〉
If the open cursor is INSENSITIVE, a temporary table is generated when the cursor is opened, and the defined cursor data set is copied from its base table.
In SQL Server, after the cursor is opened, the number of rows in the cursor result set can be read from the global variable @ CURSOR_ROWS.
Example 1: Open the cursor of the created instructor name and the class name.
OPEN tcursor
Example 2: display the number of rows in the cursor result set
SELECT the number of data rows = @ CURSOR_ROWS 3. The current tuples in the read cursor Area
The read cursor data statement reads the values of the current tuples In the cursor area and assigns each component to the specified Shared primary variable in sequence. The FETCH statement is used to read data from the cursor. The statement format is:
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]
FROM] cursor name
[INTO @ variable 1, @ variable 2,...]
Where:
NEXT: Read the NEXT row in the cursor. When the cursor is read for the first time, NEXT returns the first row in the result set.
The PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n options are only applicable to SCROLL cursors. They indicate the last row, the first row, the last row, the nth row, and the nth row relative to the current position in the cursor. When n is negative, ABSOLUTE n and RELATIVE n read data from the last row of the cursor result set or the last n rows of the current row.
The INTO clause indicates that the read data is stored in the specified local variable. The data type of each variable must match the data type returned by the cursor. Otherwise, an error occurs.
If the tuples In the cursor area have been read, The SQLSTATE value of the system state variable is set to 02000, meaning "no tuple found ".
For example, read the second row of data at the current position in tcursor.
Fetch relative 2 FROM tcursor 4. Use a cursor to modify data
The UPDATE and DELETE statements in SQL Server also support cursor operations. They can be used to modify or DELETE the current data rows in the base table of the cursor.
The UPDATE statement format is:
UPDATE table_name
SET column name = expression }[,... N]
Where current of cursor_name
The format of the DELETE statement is:
Delete from table_name
Where current of cursor_name
Note:
Current of cursor_name:
Indicates the data of the current row referred to by the current cursor. Current of can only be used in UPDATE and DELETE statements.
Note:
O if you use a cursor to modify the base table data, the declared cursor can be updated.
O has the permission to modify and delete the corresponding database objects (base tables of the cursor.
5. Close the cursor
After the cursor is closed, the data in the cursor area cannot be read again. The close statement closes an opened cursor and cannot read the cursor. However, you can use the open statement to open the cursor again.
The format of the close statement is:
Close cursor name
For example, disable the tcursor cursor as follows:
Close tcursor 6. delete a cursor statement
The deallocate statement deletes the data structure of the defined cursor and cannot be reused after deletion. Statement format:
Deallocate cursor name
For example, delete a tcursor cursor.
Deallocate tcursor Example 1: Student ID and name of the js2001 class:
# Define Max 30
Exec SQL begin declare section;
Char tn [12], Fu [20]; // defines the main variable //
Exec SQL end declare section;
Char tarn1 [30] [12], tarn2 [30] [20]; // define the C variable //
......
Exec SQL // Execute SQL statements and define the cursor //
Dclare scursor cursor for // declare the cursor scursor //
Select SnO, sname // query SnO, sname //
From student // query the student table chax //
Where sclass = 'js2001 '; // sclass = 'js2001' class //
Exec SQL open scursor; // open the cursor //
For (I = 0; I <Max; I ++)
{Exec SQL fetch from scursor
Into @ TN, @ Fu; // get the host variable //
Tarn1 = tn; // assign a value to the C array variable //
Tarn2 = Fu;
}
....................
Exec SQL close scursor; // close the cursor //
Exec SQL deallocate scursor; // delete a cursor // Example 2: Define a cursor so that the result set includes all rows and columns in the authors table of the pubs database. Because the scroll option is not specified, fetch next is the only available extraction option.
Declare authors_cursor cursor for // declare the cursor authors_cursor //
Select * from authors
Open authors_cursor // open the cursor authors_cursor //
Fetch next from authors_cursor // read a row in the cursor authors_cursor //
.................... Example 3: define and use a scroll cursor
Declare tcursor scroll cursor
Select tname, cname from teacher, couse
Where teacher. TNO = couse. TNO;
Open tcursor;
The second row of data at the current position in the tcursor.
Fetch relative 2 from tcursor
Retrieve the last row of data in tcursor
Fetch last from tcursor
The first 4th rows of data from the current position in the tcursor
Fetch relative-4 from tcursor Example 4 Use @ fetch_status to control the cursor activity in a while loop.
Declare s_cursor cursor
Select sname, SnO from student
Open s_cursor
Fetch next from s_cursor
While @ fetch_status = '20140901'
Begin fetch next from s_cursor end
Close s_cursor
Deallocate s_cursor Example 5: The general wage adjustment for employees starts from the minimum wage. The total wage cannot exceed 10% yuan. After an employee's salary is adjusted, if the total salary reaches or exceeds 0.5 million yuan, it will not be adjusted. In addition, if all the employees are adjusted, the total salary has not reached 0.5 million yuan, so far. The current employee table EMP defines a cursor Cl. The cursor query statements take out the employee number and wage value, and sort them in ascending order. The following is the program code:
Void addsalary ()
{Exec SQL begin declare section // declare SQL variables //
Char empno [8], e_sno, sqlstate [6];
Float s_sal, e_sal;
Exec SQL end declare section; // declare the SQL variable //
Exec SQL declare Cl cursor for // defines the cursor Cl. You can perform any operation on the ENO and Sal columns of the EMP table.
Select Eno, Sal
From EMP
Order by Sal ASC;
Exec SQL open Cl; // open the cursor/
Exec SQL select sum (SAL) into @ s_sal from EMP; // get the sum of the salary to s_sal
While (s_sal <500000.00)
{
Exec SQL fetch from Cl
Into @ e_sno, @ e_sal; // put the data read from the cursor into the e_sno and e_sal variables.
If (sqlstate = '20140901') break; // read the record and exit //
Exec SQL update EMP
Set sal = Sal * 1.1 // The cursor content is updated, and the Sal * 1.1 corresponding to empno = e_sno
Where empno = @ e_eno;
S_sal = s_sal + e_sal * 0.1; // calculates the total salary to s_sal.
};
Exec SQL close Cl;/Close the cursor /} SQL Server provides two cursor application interface methods: the first is an ANSI-compliant SQL cursor statement, which can be used to declare, open, read, or close a cursor, these statements can be used in transact_ SQL statements or stored procedures. The second is the database function form, which can be called by the client's db_library or ODBC application.
The cursor statement enhances transact_ SQL's ability to process set data. In SQL Server, you can also modify or delete data in the base table through the cursor. 7. Notes when using a cursor:
(1) Despite the flexible use of cursors, you can directly operate a single row of data in a dataset, but the cursor will affect the system performance in the following aspects:
-Using a cursor will increase the page lock and table lock.
-Increases network traffic.
-Added additional overhead for the server to process the corresponding commands.
(2) optimization problems when using cursors:
-Specify the usage of the cursor: For read only or for update
-After for update, specify the modified Column |
