SQL Server transactions, exceptions, and cursors

Transferred from: http://www.cnblogs.com/hoojo/archive/2011/07/19/2110325.html

Ø Business

In a database, it is sometimes necessary to run instructions from multiple steps as a whole that either succeeds or fails altogether, which requires a transaction.

1, the characteristics of the business

A transaction consists of several T-SQL directives, and all instructions are submitted to the database system last night, and when executed, the set of instructions is either completed or all canceled. Therefore, a transaction is an inseparable logical unit.

A transaction has 4 properties: atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability), also known as the Acid property of a transaction.

atomicity : All work within a transaction is either complete or incomplete, and there is only one part of the completion.

consistency : Within a transaction, the operation cannot violate the database's constraints or rules, and the internal data structure must be correct when the transaction completes.

Isolation : Transactions are directly isolated from each other, if there are two transactions that operate on the same database, such as reading table data. Everything that a transaction sees is either the state before the other transaction completes, or the state after the other transaction completes. One transaction is not likely to encounter the middle state of another transaction.

Persistence : After a transaction is complete, its impact on the database system is persistent, even if it is a system error, and the result of the transaction is still present after the system is restarted.

2, the mode of the transaction

A. Show transactions

A display transaction is a user using T-SQL to explicitly define a transaction's start (BEGIN TRANSACTION) and commit (COMMIT TRANSACTION) or ROLLBACK TRANSACTION (ROLLBACK TRANSACTION)

B. Auto COMMIT Transaction

Autocommit transactions are a way of automating and automatically rolling back transactions, which is the default transaction mode for T-SQL. For example, when deleting a table record, if the record has a primary foreign key relationship, the deletion will be affected by the primary foreign KEY constraint, then the deletion will be canceled.

You can set the transaction into an implicit mode: Set implicit_transaction on;

C, implicit transactions

An implicit transaction is when a transaction commits or rolls back, and SQL Server automatically starts the transaction. Therefore, implicit transactions do not need to begin with the BEGIN TRANSACTION display, just the T-SQL statement that commits the transaction directly or rolls back the transaction.

When used, a set implicit_transaction on statement is required, the implicit transaction mode is opened, the next statement initiates a new thing, and the next statement starts a new transaction.

3. Transaction processing

Common T-SQL transaction statements:

A, BEGIN TRANSACTION statement

Starting a transaction, while the @ @trancount global variable is used to record the number of transactions with a value of 1, you can record the error message during execution with the @ @error global variable, and if there are no errors to commit the transaction directly, an error can be rolled back.

B, COMMIT TRANSACTION statement

Rolls back a transaction that represents the end of an implicit or displayed transaction, and the modifications made to the database are formally in effect. and the value of @ @trancount minus 1;

C, ROLLBACK TRANSACTION statements

Rolls back the transaction, after executing the ROLLBACK TRAN statement, the data is rolled back to the state of Begin Tran

4. Examples of transactions

--Start a transaction
BEGIN TRANSACTION Tran_bank;
declare @tran_error int;
    Set @tran_error = 0;
    Begin try
        Update bank Set Totalmoney = totalMoney-10000 where userName = ' Jack ';        
        Set @tran_error = @tran_error + @ @error;
        Update bank Set Totalmoney = Totalmoney + 10000 where userName = ' Jason ';
        Set @tran_error = @tran_error + @ @error;
    End Try
    Begin Catch        
        
        Set @tran_error = @tran_error + 1;
    End Catch
if (@tran_error > 0)
    Begin
        --Performing an error, rolling back the transaction
        Rollback Tran;
        print ' transfer failed, cancel transaction ';
    End
Else
    Begin
        --no exception, COMMIT transaction
        Commit Tran;
        print ' transfer success ';
    End
Go

Ø exception

In the program, sometimes it is possible to complete some Transact-SQL with error and exception information. If we want to handle the exception information ourselves, we need to capture this information manually. Then we can use try catch to complete.

TRY ... A catch construct consists of two parts: a TRY block and a catch block. If an error condition is detected in a Transact-SQL statement contained in a TRY block, the control is passed to the CATCH block, which can be handled in this block.

After the CATCH block handles the exception error, the control is passed to the first Transact-SQL statement that follows the END CATCH statement. If the end CATCH statement is the last statement in a stored procedure or trigger, control returns to the code that called the stored procedure or trigger. The Transact-SQL statement after the statement that generated the error in the TRY block will not be executed.

If there are no errors in the TRY block, control is passed to the statement immediately following the associated END CATCH statement. If the end CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that called the stored procedure or trigger.

The try block begins with a BEGIN try statement and ends with an end Try statement. You can specify one or more Transact-SQL statements between the BEGIN try and END try statements. The CATCH block must follow the TRY block. The catch block begins with a begin catch statement and ends with an end Catch statement. In Transact-SQL, each TRY block is associated with only one CATCH block.

# error function

TRY ... Catch uses the error function to catch the error message.
    Error_number () returns the error number.
    Error_message () returns the full text of the error message. This text includes values that are provided for any replaceable parameters, such as length, object name, or time.
    Error_severity () returns the error severity.
    Error_state () returns the error status number.
    Error_line () returns the line number in the routine that caused the error.
    Error_procedure () returns the name of the stored procedure or trigger where the error occurred.

Example

--Error message stored procedure
if (object_id (' Proc_error_info ') is not null)
    drop procedure Proc_error_info
Go
Create proc Proc_error_info
As
    
        Error_number () ' Error number ',
        Error_message () ' Error message ',
        Error_severity () ' Severity ',
        Error_state () ' Good state ',
        Error_line () ' Error line number ',
        Error_procedure () ' Error object (stored procedure or trigger) name ';
Go

# Example: Handling error messages with exceptions

--Simple Try Catch example
Begin try
    Select 1/0;
End Try
Begin Catch
    exec proc_error_info; --Call the error message stored procedure
End Catch
Go

# example: An exception can handle error messages

--
--Simple Try catch example, unable to handle error
Begin try
    SELECT * * from student;
End Try
Begin Catch
    exec proc_error_info;
End Catch
Go
--
--Simple Try catch example, no error handling (Table object not present)
Begin try
    select * FROM St;
End Try
Begin Catch
    exec proc_error_info;
End Catch
Go
--
--Exception handling, which can handle error messages in a stored procedure (trigger) (no Table object exists)
if (object_id (' Proc_select ') is not null)
    drop procedure Proc_select
Go
Create proc Proc_select
As
    select * FROM St;
Go
Begin try
    exec proc_select;
End Try
Begin Catch    
    exec proc_error_info;
End Catch
Go

Exceptions cannot handle compile-time errors, such as syntax errors. And the error that occurs when a partial name object is not parsed correctly by recompiling.

# example: A transaction that cannot be committed

--Create a temporary table
if (object_id (' Temp_tab ', ' u ') is not null)
    drop table Temp_tab
Go
CREATE TABLE Temp_tab (
    ID int primary key identity (100000, 1),
    Name varchar (200)
)
Go

Begin try
    BEGIN Tran;
    --No createtime field
    ALTER TABLE temp_tab drop column createtime;
    Commit Tran;
End Try
Begin Catch
    EXEC proc_error_info;--Display exception information
    if (xact_state () =-1)
    Begin
        The print ' session has an active transaction, but an error occurred that caused the transaction to be categorized as a transaction that could not be committed. ‘
            The + ' session cannot commit a transaction or rollback to a savepoint; it can only request a full rollback of the transaction. ‘
            The + ' session cannot perform any write operations until the transaction is rolled back. A session can only perform read operations before rolling back a transaction. ‘
            + ' After a transaction is rolled back, the session can perform read-write operations and start a new transaction. ‘;
    End
    else if (xact_state () = 0)
    Begin
        The print ' session has no active transaction. ‘;
    End
    else if (xact_state () = 1)
    Begin
        The print ' session has an active transaction. A session can perform any operation, including writing data and committing a transaction. ‘;
    End
End Catch
Go

# example: Handling Exception Log information

--
---exception, error information table
if (object_id (' errorlog ', ' U ') is not null)
drop table Errorlog
Go
CREATE TABLE Errorlog (
Errorlogid int PRIMARY key identity (1), the primary key of the--errorlog row.
Errortime datetime default GetDate ()-The date and time the error occurred.
UserName sysname Default Current_User,-the user who executes the batch that has the error.
errornumber int,--the error number of the error that occurred.
errorseverity int,--The severity of the error that occurred.
errorstate int,--the status number of the error that occurred.
Errorprocedure nvarchar (126),--the name of the stored procedure or trigger where the error occurred.
ErrorLine int,--the line number where the error occurred.
ErrorMessage nvarchar (4000)
)
Go
--
--Stored procedure: Adding Exception Log information
if (object_id (' Proc_add_exception_log ', ' P ') is not null)
drop proc Proc_add_exception_log
Go
Create proc proc_add_exception_log (@logId int = 0 Output)
As
Begin
SET NOCOUNT on;
Set @logId = 0;
Begin try
if (Error_number () is null)
Return
        
if (xact_state () =-1)
Begin
The print ' session has an active transaction, but an error occurred that caused the transaction to be categorized as a transaction that could not be committed. ‘
The + ' session cannot commit a transaction or rollback to a savepoint; it can only request a full rollback of the transaction. ‘
The + ' session cannot perform any write operations until the transaction is rolled back. A session can only perform read operations before rolling back a transaction. ‘
+ ' After a transaction is rolled back, the session can perform read-write operations and start a new transaction. ‘;
End
else if (xact_state () = 0)
Begin
The print ' session has no active transaction. ‘;
End
else if (xact_state () = 1)
Begin
The print ' session has an active transaction. A session can perform any operation, including writing data and committing a transaction. ‘;
End
        
--Add log information
INSERT into errorlog values (GetDate (),
Current_User, Error_number (),
Error_severity (), Error_state (),
Error_procedure (),
Error_line (), error_message ());
--Set self-increment
Select @logId = @ @identity;
End Try
Begin Catch
print ' Add Exception Log information error ';
EXEC proc_error_info;--Display error message
return-1;
End Catch
End
Go
--
Example---handling exception information
declare @id int;
Begin try
BEGIN Tran;
--Delete the record information with the foreign key
Delete classes where id = 1;
Commit Tran;
End Try
Begin Catch
EXEC proc_error_info;--Display error message
if (xact_state () <> 0)
Begin
Rollback Tran;
End
EXEC proc_add_exception_log @id output
End Catch
SELECT * from errorlog where errorlogid = @id;
Go

Ø Cursors

Cursors can either process the result set of a select or do not require all processing, returning a result after processing the recordset.

1. A cursor is actually a mechanism that extracts one record at a time from the result set of multiple data records. Cursors can be completed:

# allow targeting to specific rows in the result set

# retrieves one or more rows of data from the current position of the result set

# supports modification of the current position in the result set

Because cursors are the operations of a recordset, this adds to the burden on the server, typically using cursors in cases where complex result sets are manipulated. SQL Server 2005 has three types of cursors: T-SQL cursors, API cursors, and client cursors.

2, the basic operation of the cursor

The basic operations of cursors are to define cursors, open cursors, iterate through cursors, close cursors, and delete cursors.

A. Defining cursors

DECLARE cursor_name    --cursor name
cursor [local | global]    --Global, local
[Forward only | scroll]    --Cursor scrolling mode
[Read_Only | scroll_locks | optimistic]    --Read mode
For select_statements                    --query statement
[For Update | of column_name ...]        --Modify Fields

Parameters:

Forward Only | Scroll: The previous parameter, the cursor can only be moved backwards, after a parameter, the cursor may move freely

READ_ONLY: Read-only Cursors

Scroll_locks: Cursor lock, when the cursor is read, the database locks the record so that the cursor completes the operation on the record

Optimistic: This parameter does not lock the cursor, and if the record is read into the cursor, updating or deleting the cursor will not exceed

B. Open the cursor

Open cursor_name;

After the cursor is opened, you can use the global variable @ @cursor_rows to display the number of read record bars

C. Retrieving cursors

FETCH cursor_name;

The search method is as follows:

Fetch first; Read the first line

FETCH Next; Read Next line

Fetch prior; Read previous line

Fetch last; Read last line

Fetch absolute N; Read a row

If n is a positive integer, the nth record is read

If n is negative, the countdown fetches the nth record

If n is, no record is read

Fetch pelative N

If n is a positive integer, the nth record after the last read of the record is read

If n is negative, the nth record before the last read of the record is read

If n is, the last read record is read

D. Close the cursor

Close cursor_name;

E, delete cursors

DEALLOCATE cursor_name;

3. Cursor Operation example

--Create a cursor
Declare CURSOR_STU cursor Scroll for
    Select ID, name, age from student;
--Open cursor
Open cursor_stu;
--Store the Read value
declare @id int,
        @name nvarchar (20),
        @age varchar (20);
--Read the first record
Fetch first from Cursor_stu to @id, @name, @age;
--Cyclic reading of cursor records
print ' reads the data as follows: ';
--Global variables
while (@ @fetch_status = 0)
Begin
    print ' number: ' + CONVERT (char (5), @id) + ', Name: ' + @name + ', type: ' + @age;
    --Continue reading the next record
    FETCH NEXT from Cursor_stu to @id, @name, @age;
End
--Close cursor
Close area_cursor;

--Delete cursor
--deallocate Area_cursor;

SQL Server transactions, exceptions, and cursors