SQL batch processing and cursor

SQL Batch Processing
Batch Processing: [Define] A group of complete data and SQL commands (one or multiple SQL commands can be included) passed from the client to the server to form a batch processing.

(1) If a syntax error exists in a batch, the entire batch cannot be compiled.
(2) batch processing can contain multiple stored procedures, but the exec keyword must be used before the remaining stored procedures except the first stored procedure.
(3) Some Special SQL commands cannot be combined with other SQL commands in a batch. Such as the create table command and create view command, these commands can only exist in a separate stored procedure.
(4) All batch processing commands use go as the end flag. When the compiler reads go, all the statements in front of go are considered as a batch and packaged into a data packet and sent to the server.
(5) go is not an integral part of the statements of transact-SQL, but a front-end command used to indicate the completion of batch processing.

Note:
In some cases, if there are some errors in the SQL command, which cannot be identified during compilation and will only be wrong during execution, in this way, some useless junk data will be generated after a part of the batch processing operation is executed. In this way, we need to introduce a program that can ensure that all SQL commands are successfully executed or all SQL commands fail to be executed. This is a "transaction "!

T_ SQL cursor
Deallocate aa -- release cursor

Declare AA cursor -- declare a cursor
For
Select userid, username from test1 -- add a dataset for the cursor

For read only -- set read-only attributes

Open aa -- open a cursor

-- Declare @ AA int -- declare a variable
-- Select @ AA = @ cursor_rows -- assign the number of data rows in the cursor to this variable
-- Select @ AA

-- Close aa -- close the cursor

Fetch next from AA -- get the next row of data

While @ fetch_status = 0 -- determine the retrieval status
Begin
Fetch next from AA -- get the next row of data in sequence
End
Close aa -- close the cursor

* ******* About the cursorArticleIntroduction *********

A major reason for using cursor is to convert the set operation into a single record processing method. After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows you to access these records row by row in SQL Server and display and process these records as you wish.

1.Why use cursor:

Use cursor(Cursor)The main reason is to convert the set operation into a single record processing method. UseSQLAfter the language retrieves data from the database, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows usersSQL ServerTo display and process these records row by row.

2.How to use a cursor:

Generally, using a cursor follows the following general steps:

(1)Declare the cursor. Match the cursorT-SQLThe result set of the statement.
(2)Open the cursor.
(3)Use a cursor to operate data.
(4)Close the cursor.

2.1.Declared cursor

Declare cursorStatementSQL-92Standard syntax format:

DeclareCursor name[Insensitive] [scroll] cursor

ForSQL-Statement

Eg:

Declare mycrsrvar cursor

For select * From tbmydata

2.2Open cursor

Open mycrsrvar

When the cursor is opened, the row Pointer Points to1If you want to read1Row data. You must move the row pointer to1Line. In this example, you can use the following operations to read1Row data:

Fetch first from e1cursor

OrFetch next from e1cursor

2.3Use cursor to operate data

The following example uses@ Fetch_statusControl inWhileCursor activity in a loop

/*The following operations are performed to read data using a cursor.*/

Declare e1cursor cursor/*Declares a cursor. The default value isForward_onlyCursor*/

For select * From c_example

Open e1cursor/*Open cursor*/

Fetch next from e1cursor/*Read1Row data*/

While @ fetch_status = 0/*UseWhileCyclic control cursor Activity*/

Begin

Fetch next from e1cursor/*Other rows of data will be read in the loop body*/

End

Close e1cursor/*Close cursor*/

Deallocate e1cursor/*Delete cursor*/

2.4Close cursor

UseCloseStatement to close the cursor

Close {[Global]Cursor name} |Cursor variable name}

UseDeallocateStatement to delete a cursor. The syntax format is as follows:

Deallocate {[Global]Cursor name} | @Cursor variable name

3. FetchThe concise operation syntax is as follows:

 Fetch

[Next | prior | first | last]

From

{Cursor name| @Cursor variable name} [@Variable name[,...]

Parameter description:

NextRemove the data of a row and use the next row as the current row.(Increment). After the cursor is opened, the row Pointer Points to the cursor1So the first executionFetch nextThe Operation will obtain1Row data.NextIs the default cursor extraction option.

Into @Variable name[,...]Put the extracted column data in a local variable. The variables in the list are associated with the corresponding columns in the cursor result set from left to right. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the data type of the result column. The number of variables must be the same as the number of columns in the cursor selection list.

Slave --------------------------------------------------------------------------------------------------------------------------------

Each executionFetchAfter the operation, you usually need to check the global variables.@ Fetch_statusTo determineFetchWhether the operation is successful. The variable has three status values:

·0Indicates successful execution.FetchStatement.

·-1IndicatesFetchStatement failure. For example, moving a row pointer causes it to exceed the result set.

·-2Indicates that the extracted row does not exist.

Because@ Fetch_statuIs a global variable. Any cursor on a connection may affect the value of this variable. ThereforeFetchStatement, you must execute anotherFetchTo make a correct judgment.