6、 Transaction index view cursor

Time:2022-5-14

1、 Business

Personnel information is as follows: (the second column is the ID number, and the third column is the bank card number)

–Liu Bei 420107198905064135 6225125478544587
–Guan Yu 420107199507104133 6225547858741263
–Zhang Fei 420107199602034138 6225547854125656

(1) Assuming Liu Bei withdraws 6000 (add check constraint, set account balance must be > = 0), it is required to use transaction implementation, modify balance and add withdrawal record

begin transaction
declare @MyError int = 0
update BankCard set CardMoney = CardMoney-6000 where CardNo = '6225125478544587'
set @MyError = @MyError + @@ERROR
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',0,6000,GETDATE())
set @MyError = @MyError + @@ERROR
if @MyError = 0
begin
	commit transaction
	Print 'withdrawal succeeded'
end	
else
begin
	rollback transaction
	Print 'insufficient balance'
end

(2) Assume that Liu Bei transfers 1000 yuan to Zhang Fei (add check constraint and set the account balance to be > = 0); There are three analysis steps: (1) Zhang Fei adds 1000 yuan, (2) Liu Bei deducts 1000 yuan, (3) generates transfer records; Use transactions to resolve this issue.

begin transaction
declare @Error int = 0
update BankCard set CardMoney = CardMoney -1000 where CardNo = '6225125478544587'
set @Error = @@ERROR + @Error
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '6225547854125656'
set @Error = @@ERROR + @Error
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225547854125656',1000,GETDATE())
set @Error = @@ERROR + @Error
if @Error = 0
	begin
		commit
		Print 'transfer succeeded'
	end
else
	begin
		rollback
		Print 'transfer failed'		
	end

2、 Index

Index: improve the efficiency of retrieval and query.

SQL Server index type:By storage structure: “clustered index (also known as cluster index, cluster index)”, “non clustered index (non cluster index, non cluster index)”;

Clustered index: These data rows are stored according to the sorting of the key values of the data rows in the table or view. Each table has only one clustered index. A clustered index is a reorganization of the actual data on disk to sort by a specified column or columns of values (similar to the Pinyin index in a dictionary) (physical storage order).

Nonclustered index: it has a structure independent of data rows, contains nonclustered index key values, and each key value item has a pointer to the data row containing the key value. (similar to the partial radical index in a dictionary) (logical storage order).

SQL Server index other categories:

Distinguish by data uniqueness: “unique index”, “non unique index”; The number of key columns is distinguished: “single column index” and “multi column index”.

How to create an index:

  1. Through the explicit create index command
  2. As an implicit object when creating constraints
    1. Primary key constraint (clustered index)
    2. Unique constraint (unique index)

Create index syntax:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX  ON ( [ASC|DESC][,...n])

Basic example syntax of index:
--Exp: create a nonclustered index
--create nonclustered index indexAccount on AccountInfo(AccountCode)
--Delete an index
--drop index indexAccount on AccountInfo

Note: index information is stored in the system view sys Indexes.
Query according to the specified index
select * from AccountInfo with(index=indexAccount) where AccountCode='6225125478544587'

3、 View
View: can be understood as a virtual table.
(1) Write a view to query all bank card account information and display card number, ID card, name and balance.
create view CardAndAccount as
Select cardno, account code, ID card, realName name, cardmoney balance from bankcard 
left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
go

If you want to query the corresponding information, you do not need to write complex SQL statements. You can directly use the view as follows:
select * from CardAndAccount

4、 Cursor
Cursor: navigate to a row in the result set.
Cursor classification:
(1) Static cursor: when the cursor is operated, the data changes and the data in the cursor remains unchanged
(2) Dynamic cursor: when operating the cursor, the data changes and the data in the cursor changes. The default value is.
(3) Keyset driven cursor: when operating the cursor, the identified column changes, the data in the cursor changes, other columns change, and the data in the cursor remains unchanged.
The assumptions and data structure are as follows:
create table Member
(
	MemberId int primary key identity(1,1),
	MemberAccount nvarchar(20) unique check(len(MemberAccount) between 6 and 12),
	MemberPwd nvarchar(20),
	MemberNickname nvarchar(20),
	MemberPhone nvarchar(20)
)

insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
Values ('liubei ',' 123456 ',' Liu Bei ',' 4659874564 ')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
Values ('guanyu ',' 123456 ',' Guan Yu ',' 42354234124 ')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
Values ('zhangfei ',' 123456 ',' Zhang Fei ',' 41253445 ')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
Values ('zhangyun ',' 123456 ',' Zhao Yun ',' 75675676547 ')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
Values ('machao ',' 123456 ',' Ma Chao ',' 532523523 ')

Create cursor:
--1. Create cursor (scroll stands for scrolling cursor, without scroll, it is forward only, and can only support fetch next)
declare CURSORMember cursor scroll 
for select MemberAccount from Member

Open cursor:
open  CURSORMember

Extract data:
Fetch first from cursormember -- the first row of the result set
Fetch last from cursormember -- last line
Fetch absolute 1 from cursormember -- the number starting from the first row of the cursor, the nth row.
Fetch relative 3 from cursormember -- number from current position, line n.
Fetch next from cursormember -- the next line of the current position
Fetch priority from cursormember -- the previous row of the current position

Extract data to variables for other use (take out the user name in line 3 and query the user's details):
declare @MemberAccount varchar(30)
fetch absolute 3 from CURSORMember into @MemberAccount
select * from Member where MemberAccount = @MemberAccount

Use the cursor to extract all account information:
--Scheme I:
fetch absolute 1 from CURSORMember
while @@FETCH_ STATUS = 0  [email protected]@FETCH_ Status = 0, extraction succeeded, - 1 Extraction failed, - 2 rows do not exist
	begin
		fetch next from CURSORMember
	end
	
--Scheme II:
declare @MemberAccount varchar(30)
--fetch next from CURSORMember into @MemberAccount
fetch absolute 1 from CURSORMember into @MemberAccount
while @@FETCH_ STATUS = 0  [email protected]@FETCH_ Status = 0, extraction succeeded, - 1 Extraction failed, - 2 rows do not exist
	begin
		Print 'extraction succeeded:' + @ memberaccount
		fetch next from CURSORMember into @MemberAccount
	end

Modify and delete data with cursor:
fetch absolute 3 from CURSORMember
update Member set MemberPwd = '1234567' where Current of CURSORMember

fetch absolute 3 from CURSORMember
delete Member where Current of CURSORMember

Close cursor:
close CURSORMember

Delete cursor:
deallocate CURSORMember

Create a cursor to point to a row and multiple columns of data, and display the data circularly:
--If you point to all data here, you can modify the statement after for to select * from member
declare CURSORMember cursor scroll
for select MemberAccount,MemberPwd,MemberNickname,MemberPhone from Member

open CURSORMember

declare @MemberAccount varchar(30)
declare	@MemberPwd nvarchar(20)
declare	@MemberNickname nvarchar(20)
declare	@MemberPhone nvarchar(20)
fetch next from CURSORMember into @MemberAccount,@MemberPwd,@MemberNickname,@MemberPhone
while @@FETCH_ STATUS = 0  [email protected]@FETCH_ Status = 0, extraction succeeded, - 1 Extraction failed, - 2 rows do not exist
	begin
		Print 'extraction succeeded:' + @ memberaccount+','[email protected]+','[email protected]+','[email protected]
		fetch next from CURSORMember into @MemberAccount,@MemberPwd,@MemberNickname,@MemberPhone
	end
close CURSORMember

Recommended Today

Vue component communication – provide / inject

Component communication is a very common operation in Vue development, and props is certainly the most commonly used component communication, but props is convenient for parent-child component communication, but when there are many component levels, props operation is very cumbersome and not easy to write;Note: for convenience, I put inject directly in the sub component […]