Cursor and transaction and error message mechanism

I. cursor concept: processing a result set as a set and processing certain fields of one or more rows of the dataset each time.

The cursor structure is as follows:

1. Define a cursor to associate the cursor with the result set of the Transact-SQL statement.

Declare @ bookid int, @ bname varchar (50), @ bindex int

Declare book_cursor cursor

Select bookid, bookname, bookIndex from Bas_bookList

2. Execute the Transact-SQL statement dataset filling cursor to open the cursor

Open book_cursor

 

3. Retrieve the first row from the cursor and extract some fields in the first or first row.

Fetch next from book_cursor into @ bookid, @ bname, @ bindex

 

4. Operate the current row as needed

@ Fetch_status three statuses are 0,-1, and-2. To determine whether the cursor execution is correct.

0 indicates that the cursor is executed correctly.-1 indicates that an error occurs in the cursor and-2 indicates that an empty row is found.

While @ fetch_status = 0

Begin

Delete update insert, etc.

Fetch next from book_cursor into @ bookid, @ bname, @ bindex select the next row of data

End

 

4. Close the cursor

Close book_cursor

Deallocate book_cursor

 

Note: client cursors are supported by odbc. There are some restrictions when using them. They can only use forward-only and static cursors. They cache results to the client, all cursor operations are cached by the client. It is not executed on the server. Generally, it is not used in this way. It is used only for Transact-SQL and batch processing that are not supported by the server.

This section is only for my understanding of client cursors.

 

2. transactions are easy to use. I will not detail them here.

 

1. Use transactions in the Stored Procedure

 

Statement structure:

Transaction start point: Begin transaction

Commit the transaction, complete the data operation changes starting from the transaction start point, and release the resources occupied by the transaction: commit transaction

If a transaction error occurs, roll back: rollback

 

At the transaction start point, begin transaction tran1 causes @ trancount to increase by 1

Execute the transaction. Commit ttansaction tran1 causes @ trancount to decrease by 1 until it is reduced to 0.

Rollback is the starting point of the transaction or a storage point of the transaction, that is, the definition point.

 

2. transactions can also be used in the C # program.

Using (system. Data. sqlclient. sqlconnection conn = new system. Data. sqlclient. sqlconnection ("database connection string "))

{

Conn. open ();

Using (system. Data. sqlclient. sqltransaction trans = conn. begintransaction ())

{

Try

{

SQL statement;

Trans. Commit ();

}

Catch

{

Trans. Rollback ();

}

}

}

Iii. Transact-SQL is similar to Exception Handling in C. The Transact-SQL statement group can be included in the TRY block. If an error occurs in the TRY block, the event processing is forwarded to the Catch Block.

 

Statement Structure

BEGIN TRY

Transact-SQL statements

END TRY

BEGIN CATCH

Error Handling Mechanism

END CATCH

In fact, the basic knowledge described above. At the beginning of development, you will understand and use it.

This article is just a systematic summary. For your reference. Not brilliant.

 

I found a problem in my actual application. For a complex stored procedure, combining the above three usage methods will improve the execution efficiency. First, to locate the error point, roll back the transaction, and combine try and transaction. If a large amount of data is to be processed, the cursor may be used, transactions process data permanently only when they execute commit. Is this the case when a cursor is used? Write the cursor into the transaction and complete the execution of all the cursors, then, the transaction is processed. If an exception occurs, the transaction is rolled back. I tried it and it was much faster. In addition, the variables defined in the same stored procedure in Transact-SQL are valid throughout the running cycle, which makes the entire idea easy to implement. For example:

 

Set nocount on;

BEGIN TRY

Begin transaction tran1

DECLARE @ proarageid BIGINT, @ procomid BIGINT, @ proagentid BIGINT, @ progropid BIGINT

--- Statement

DECLARE acursor CURSOR

For select id, provicename, cityname FROM Ass_ArrearageTemporary

OPEN acursor

Fetch next from acursor INTO @ proarageid, @ procomid, @ proagentid WHILE @ FETCH_STATUS = 0

BEGIN

--- Statement

Fetch next from acursor INTO @ proarageid, @ procomid, @ proagentid

END

Commit transaction tran1

CLOSE acursor

DEALLOCATE acursor

End try

Begin catch

If @ trancount> 0

Begin

Close acursor

Deallocate acursor

Rollback transaction tran1

Return

End

End catch

 

 

Although not very high, it is also the author's painstaking efforts.