Chapter 5 Transactions and Cursors

Transaction: A series of operations performed by a number of T-SQL statements as a single logical unit of work. These operations are used as a whole, either all done or all revoked. A transaction is an inseparable working logical unit.

There are three ways to execute a transaction:

1. Display transaction: there is a clear begin, commit, and rollback;

2. Automatically submit transactions: add, delete, and modify data, if there is an error, the operation will be automatically canceled;

3. Implicit transaction: a transaction that starts automatically when a transaction commits or rolls back

Cursor: A mechanism that extracts one record at a time from a result set that includes multiple data records.

Using a cursor can be achieved:

1. Allow targeting to specific rows in the result set

2. Retrieve one or more rows of data from the current location of the result set

3. Support for modifying the current position of the result set

The operation is as follows:

/*--Transaction--*/ --Create a table Create table bank ( Customerld int primary key identity(1,1), Customername char(10), Currentmoney money, Check(currentmoney>=1) ) Go --adding data Insert into bank values('张三', 1000) Insert into bank values('李四',1) Go --Open the transaction Begin tran bank - Define variables that accept errors Declare @a int --update data Update bank set currentmoney=currentmoney-1000 where customername='张三' Set @a = @[email protected]@ERROR Update bank set currentmoney=currentmoney+1000 where customername='李四' Set @a = @[email protected]@ERROR

If @a=0 Begin -- No errors, executing transactions Commit tran Print 'transfer successfully' End Else Begin -- execution error, rollback transaction Rollback tran Print 'transfer failed' End Select * from bank Go

Use of cursors:

1. Define the cursor declare the cursor name cursor scroll for the query statement

2. Open the cursor open cursor name

3. Retrieve the cursor fetch cursor name

4. Close the cursor after use. Close cursor name

5. Release the cursor deallocate cursor name

The operation is as follows:

/*--cursor--*/ --Define the cursor Declare ha cursor scroll for select customername,currentmoney from bank -- use cursors Open ha - Define a variable to hold the value read from the cursor Declare @name varchar(5) , @money money -- read the first line of the cursor Fetch first from ha into @name,@money -- Cycle through the records in the cursor While @@FETCH_STATUS=0 Begin The print_name+' balance is: '+convert(varchar(max),@money)+'yuan' -- read the next line of the cursor Fetch next from ha into @name,@money End --Close the cursor Close ha -- release the cursor Deallocate ha