Cursor in SQL Server

Sometimes we need to read data one by one, as shown below:

This. sqlconnection1.open ();
Sqldatareader myreader = This. sqlcommand1.executereader ();
Do
{
While (myreader. Read ())
{
Console. writeline ("{0} {1}", myreader. getint32 (0), myreader. getstring (1 ));
} While (myreader. nextresult ());
Myreader. Close ();
Sqlconnection1.close ();

We often write this in our own program, but there is certainly a way to access data one by one in SQL Server, that is, the cursor.
For example:
I want to find the lastname and firstname of all persons whose titleofcourtesy is 'Mr. 'from the employees of the nurthwind database. I also need to access them one by one. The following is an example:

Use northwind
Go
-- Define a cursor
Declare mycursor cursor
Select lastname, firstname from employees
Where titleofcourtesy = 'Mr .'
-- Open the cursor
Open mycursor
-- Read data row by row
Fetch next from mycursor
-- @ Fetch_status: The fetch statement is successful.
While @ fetch_status = 0
Begin
Fetch next from mycursor
End
-- Close the cursor
Close mycursor
-- Release cursor
Deallocate mycursor
Go

The results are read one by one.

However, the cursor occupies memory and uses their incredible ways to lock the table. Every execution of fetch is equal to execution of the SELECT command? This means that if your cursor has 10000 records, it will execute 10000 select! If you use a set of select, update, or delete operations to complete the corresponding work, it will be much more efficient.
About cursor and database performance:
Cursor threshold Option
Specifies the number of rows in the cursor set.
If it is set to-1, all the cursor sets are generated synchronously, which is useful for small cursor sets.
If it is set to 0, all cursor sets are generated asynchronously, which is helpful for a large number of cursor sets.
If it is set to another value, Asynchronization and synchronization are determined by comparing the expected number of rows in the cursor set with the threshold value. The former is greater than the latter, asynchronous; otherwise, synchronous.
You can use the sp_configure system stored procedure to modify this setting. It can be changed only when show advanced options is set to 1. The modification takes effect immediately without restarting the database.

 


2.2 implicit cursor

All implicit cursors are assumed to return only one record.

You do not need to declare, open, or close an implicit cursor. PL/SQL implicitly opens, processes, and closes the cursor.

For example:

.......

Select studentno, studentname

Into curstudentno, curstudentname

From studentrecord

Where name = 'gg ';

The cursor is automatically opened, related values are assigned to corresponding variables, and then closed. After execution, the PL/SQL variable curstudentno and curstudentname have values.

Four implicit attributes of a cursor

% Found, % notfound, % isopen, % rowcount

The results of % found, % notfound, and % isopen are Boolean and % rowcount are integers.

Example:
Test Table Data
A name
-------
1 aa
2 bb
3 cc
4 dd
5 ee

Program:
Set serveroutput on
Declare
Cursor ABC (idpara number) is -- with Parameters
Select name from test where a = idpara;
T_name test. name % type;
E exception; -- custom exception
Nopen exception; -- custom exception
Begin
Open ABC ('3'); -- the value of idpara is 3.
Fetch ABC into t_name;
If ABC % isopen then -- if the cursor has been opened
Dbms_output.put_line ('cursor is open ');
Else
Raise nopen; -- throw a custom exception (the cursor is not opened)
End if;
If ABC % notfound then -- if the data is not found
Raise E; -- throw a custom exception (data not found)
Elsif ABC % found then -- if the data is found
Dbms_output.put_line (t_name );
Dbms_output.put_line ('I found' | ABC % rowcount | 'data! '); -- Number of data records found in the output
End if;
Exception
When nopen then -- execution exception when the cursor is not opened
Dbms_output.put_line ('the cursor is not found! ');
When E then -- no abnormal execution is found for Data
Dbms_output.put_line ('the EID is not found! ');
End;
/

Complete.