Look at the database again--(7) cursor

background :

In fact, the look at the database again . The series blog didn't plan to write so much , But there's been more recent contact with the database , and touched these things again. , rarely used in the past , so just sort of , Share with you .

Introduction :

Cursors , is a data buffer , used to store SQL execution result of the statement . Unlike the general execution process , , cursors are fetching one record at a time from the result set .

the difference from a relational database :

relational database-oriented collection, general execution result is a set, if you want to select one or several records, you need to use where clause.

Cursors --facing a single record. Cursors can perform the same or different operations on each row in the result set returned by the query statement.

Therefore,

Cursors link the database management system as a set-oriented and the line-oriented programming, so that two data processing methods can communicate.

Type:

Three types of cursors, including: Transact_sql cursors, API server cursors and client cursors.

Server cursors (background cursors)

1) Transact_sql Cursors

mainly in SQL used in scripts, stored procedures, triggers, and does not support extracting chunks of data or multiple rows of data.

typically used on a server that is sent from a client to a server SQL statements or batches, stored procedures, triggers , Transact_sql to manage.

2 ) API server Cursors

Primarily used on the server, the client application invokes API cursor functions, and the MS SQL SEVER OLE DB provider, ODBC Drive, or db_library dynamic-link library (DLL) will route these customer requests to the server to process the API cursors.

Client cursor (foreground cursor)

3 ) Customer Cursors

A client cursor is used primarily when a result set is cached on the client. In a customer cursor, there is a default result set that is used to cache the entire result set on the client. Client cursors only support static cursors rather than dynamic cursors.

Because server cursors do not support all Transact-SQL statements or batches, client cursors are often used only as a helper for server cursors. Because in general, server cursors can support the vast majority of cursor operations.

Example:

The records in the database table are as follows:

--Define CURSOR declare cursor_test cursor SCROLL for  SELECT firstname,middlename,lastname from adventureworksdw2012.dbo.dimcustomer--opens the cursor open cursor_test--declares the variable DECLARE @test1 varchar (TEN) DECLARE @test2 varchar (10 ) DECLARE @test3 VARCHAR (10)--Take the first row of records from the cursor_test into @test1, @test2, @test3PRINT @test1 print @test2 print @ test3--take down a row of records fetch NEXT from the cursor_test into @test1, @test2, @test3PRINT @test1 print @test2 print @test3--close the cursor  Cursor_testdeallocate Cursor_test

Execution Result:

Summary:

don't be afraid of not knowing, afraid don't know. With the cursor, you can save a lot of tedious work, no longer need to manually one-to-one to the operation of each row of records, saving a lot of work time.

Look at the database again--(7) cursor