Home php教程 PHP开发 How to traverse loop data using SQL cursor

How to traverse loop data using SQL cursor

Dec 14, 2016 am 11:56 AM

If you perform a traversal loop operation on the data, you can achieve it through SQL cursors. The method is introduced in detail below for your reference. I hope it will be helpful to you in learning SQL database.

The advantage of SQL cursors is that they can easily loop through data from a result set and perform operations.
1. The cursor allows the application to perform the same or different operations on each row in the row result set returned by the query statement select, instead of performing the same operation on the entire result set at once;
2. It also provides the ability to perform the same operation on the table based on the cursor position. The ability to delete or update data;
3. Cursors connect the collection-oriented database management system and row-oriented programming, allowing the two data processing methods to communicate.
         However, cursors also have shortcomings - complexity and inefficiency are the biggest shortcomings of cursors, and they are also the main reasons why cursors are often not thought of when using stored procedures.

The following is an application of a cursor example in actual work. The cursor is used to copy a column value of the data in table A to a column in table B. The two tables have the same UID field, and the condition is that the same UID Data is copied.

declare @level varchar(100)
declare @uid varchar(100)
declare cur cursor--Define a cursor
read_only
for select egg_code.user_id,egg_prize_level
from egg_code inner join egg_prize on egg_prize.user_id =egg_code.user_id--Specify the cursor for the obtained data set

open cur--Open the cursor
fetch next from cur into @uid,@level--Put the column data of the extraction operation into local variables
while(@ @fetch_status=0)--Returns the status of the last cursor executed by the FETCH statement, not the status of any cursor currently opened by a connection.

begin
--print 'Level:'+@level+'-------------User ID:'+@uid

update egg_code set prize_level=@level where user_id=@uid --Execute the operation

--Advance the next information
fetch next from cur into @uid,@level
end
close cur--Close the cursor
deallocate cur--Delete the cursor
go

The order of using the cursor: reputation Cursor, open cursor, read data, close cursor, delete cursor.
Since @@FETCH_STATUS is global for all cursors on a connection, be careful when using @@FETCH_STATUS. After executing a FETCH statement, @@FETCH_STATUS must be tested before executing another FETCH statement on another cursor. The value of @@FETCH_STATUS is not defined before any fetch operation occurs on this connection.
For example, the user executes a FETCH statement from one cursor and then calls a stored procedure that opens and processes the results of another cursor. When control returns from a called stored procedure, @@FETCH_STATUS reflects the result of the last FETCH statement executed in the stored procedure, not the result of the FETCH statement before the stored procedure was called.
The above cursor was used by Quanzhou SEO during a website golden egg smashing event. During the use process, part of the data in the two tables was later changed to the program, which caused some data to be unable to be synchronized, resulting in front-end query. The relevant data cannot be found in the table. I have tried to use simple SQL statements to implement such a synchronization function before using a cursor, but it has never been possible, so I can only use a cursor to achieve it.


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)