TURN: Transact-SQL CURSOR usage details ~ ~ Very detailed

/*
Principle: The cursor is to extract data according to the specified requirements of the corresponding data set, and then data processing.
1.1 The concept of cursors
Cursor It enables the user to access the result set returned by SQL Server on a row-by-line basis.
One of the main reasons for using cursors is to convert the set operation to a single record processing mode.
When data is retrieved from a database in SQL language, the result is placed in an area of memory, and the result is often a collection of multiple records.
The cursor mechanism allows users to access these records row-by-line within SQL Server, displaying and processing these records at the user's own will.
Advantages of 1.2 Cursors
The following advantages are obtained from the cursor definition, which makes the cursor play an important role in the actual application:
1) allows the program to perform the same or different operations on each row in the rowset returned by the query statement select, rather than performing the same operation on the entire rowset.
2) provides the ability to delete and update rows in a table based on the cursor location.
3) cursors actually serve as a bridge between the set-oriented database management system (RDBMS) and the line-oriented program design, which enables the two processing methods to communicate through cursors.

1.3 Use of Cursors
Speaking of the advantages of this multi-cursor, we are now going to reveal the mysterious veil of the cursor in person.
The order in which cursors are used: reputation cursors, open cursors, read data, close cursors, delete cursors.
1.3.1 Declaring cursors
Simplest cursor declaration: DECLARE < cursor name >cursor for<select statement >;
Where the SELECT statement can be a simple query, or it can be a complex set of queries and nested queries
1.3.2 Opening Cursors
Very simply, we'll open the cursor we just declared MyCursor
OPEN MyCursor
1.3.3 reading data
FETCH [NEXT | PRIOR | First | Last] from {cursor name | @ cursor variable name} [into @ variable name [, ...]]
Parameter description:
Next takes a row of data and takes the next line as the current row (increment).
Because the row pointer is before the 1th row of the cursor after the cursor is opened, the first fetch next operation obtains the 1th row of data in the cursor set.
Next is the default cursor extraction option.
into @ variable name [,...] puts the column data of the extraction operation into the local variable.
Each variable in the list is associated from left to right with the corresponding column in the cursor result set.
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 be the same as the number of columns in the cursor selection list.

1.3.4 Closing Cursors
CLOSE MyCursor
1.3.5 Deleting cursors
Deallocate mycursor

Give a concrete example:
*/

----------------------------------------------
declare @ID nvarchar (20); --Define variables to save ID numbers
DECLARE @DATE DATETIME;
DECLARE @DFD NVARCHAR (200); --Define variables to hold values
SET @DFD = ';
DECLARE mycursor cursor FOR
Select Mid,createddate from Fd_menu--specifying cursors for the obtained data set
Open MyCursor--opening cursors
FETCH NEXT from MyCursor to @ID, @DATE--start grabbing the first piece of data
while (@ @fetch_status =0)--if data is always in the data set
Begin
--select Tb_b.name, (tb_b.gz + @A) from tb_b where tb_b.id = @id--start doing what you want to do (update Yes, delete it)
SET @[email protected]+cast (@ID as NVARCHAR) + '. V. ' +convert (NVARCHAR, @DATE, 111);
Select @DFD;
FETCH NEXT from MyCursor to @ID, @DATE--skip to next data
End
Close MyCursor--closing cursors
Deallocate mycursor--delete cursor

-------------------------------------------------------------------------
-----------------------Loop Example------------------------------------------
-------------------------------------------------------------------------
TRUNCATE TABLE V_test

DECLARE @i INT;
SET @i=1;
While @i<1000
BEGIN
INSERT into V_test (V_NAME,V_DESC) VALUES (' IAMV ' +str (@i), ' Victor ' +str ([email protected])
SET @[email protected]+1
END

SELECT * from V_test
-------------------------------------------------------------------------
DECLARE @ID BIGINT;
DECLARE @NAME NVARCHAR (50);
DECLARE V_cursor CURSOR for (
SELECT v_id,v_name from V_test WHERE Right (v_name,2) = ' 10 '
)
OPEN V_cursor;
FETCH NEXT from V_cursor to @ID, @NAME
while (@ @FETCH_STATUS =0)
BEGIN
PRINT CAST (@ID as NVARCHAR) [email protected];
FETCH NEXT from V_cursor to @ID, @NAME;
END
CLOSE V_cursor;
Deallocate v_cursor;
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
/*
Declaring SCROLL cursors and using other FETCH options
Scroll indicates that the cursor pointer can be moved (otherwise only forward)
The following example creates a SCROLL cursor that supports all scrolling capabilities through the last, PRIOR, RELATIVE, and ABSOLUTE options.
*/
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--Declaring local variables
DECLARE @ID BIGINT;
DECLARE @NAME NVARCHAR (50);
DECLARE v_cursor SCROLL CURSOR for
SELECT v_id,v_name from V_test;
OPEN V_cursor;
--Locate the record at the specified location
FETCH ABSOLUTE from V_cursor to @ID, @NAME
Select @id as ID, @name as name
--Position records relative to the current record
FETCH RELATIVE-30 from V_cursor to @id, @name
Select @id as ID, @name as name
--Navigate to the previous entry in the current record
Fetch prior from V_cursor to @id, @name
Select @id as ID, @name as name
--navigates to the current record after a
FETCH NEXT from V_cursor to @id, @name
Select @id as ID, @name as name
--position to first record
Fetch first from v_cursor to @id, @name
Select @id as ID, @name as name
--position to tail record
Fetch last from V_cursor to @id, @name
Select @id as ID, @name as name

CLOSE V_cursor;
Deallocate v_cursor;
-------------------------------------------------------------------------------------
/*

*/
-------------------------------------------------------------------------------------
DECLARE @ID BIGINT;
DECLARE @NAME NVARCHAR (50);
DECLARE v_cursor CURSOR SCROLL for (
SELECT v_id,v_name from V_test WHERE Right (v_name,2) = ' 10 '
)
OPEN V_cursor;
FETCH NEXT from V_cursor to @ID, @NAME

while (@ @FETCH_STATUS =0)
BEGIN
PRINT CAST (@ID as NVARCHAR) [email protected];
FETCH RELATIVE 2 from V_cursor to @ID, @NAME;
END

CLOSE V_cursor;
Deallocate v_cursor;
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
/*
Dynamic means that cursors can be read and written (otherwise cursors are read-only)
UPDATE v_test SET v_desc= ' Victor 199 ' WHERE Current of V_cursor
*/
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
DECLARE v_cursor CURSOR SCROLL DYNAMIC
/*scroll indicates that the cursor pointer can be moved (otherwise only forward)
Dynamic means that a cursor can be read or written (otherwise the cursor is read-only) */
For
SELECT V_desc from V_test WHERE CAST (right (v_desc,3) as INT) <200

OPEN V_cursor
DECLARE @DESC NVARCHAR (50)

FETCH NEXT from V_cursor to @DESC
while (@ @fetch_status =0)
BEGIN
PRINT ' Product Name: ' + @DESC
FETCH NEXT from V_cursor to @DESC
END

FETCH first from V_cursor to @DESC
Print @DESC
Update v_test set v_desc= ' 123122 ' WHERE current of V_cursor
/**//*delete from product where CURRENT of my_cursor */
CLOSE V_cursor
Deallocate v_cursor
----------------------------------------------------------------
SELECT * from V_test
Update v_test set v_desc= ' Victor 199 ' WHERE v_id=801
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
/*
DECLARE my_cursor1 cursor FOR
Select Ncontentid,dtedittime from content where datepart (month,dtedittime) = ' 9 ' and datepart (day,dtedittime) = ' 26 '
Open My_cursor1
declare @date sysname
declare @nID sysname
DECLARE @tempDate datetime
FETCH NEXT from My_cursor1 to @nID, @date
while (@ @fetch_status = 0)
Begin
Set @tempDate = DateAdd (day,87, @date)
--print @tempDate
Update Content set [email protected] where Ncontentid = @nID
FETCH NEXT from My_cursor1 to @nID, @date
End
Close My_cursor1
Deallocate My_cursor1

*/

-----------------------
DECLARE @JJ NVARCHAR (10)
SET @JJ = ' 12345 ';
SET @JJ = Right (@JJ, left (@JJ, 1))
PRINT @JJ
----------------------

TURN: Transact-SQL CURSOR usage details ~ ~ Very detailed