SQL SERVER Advanced Syntax t-SQL

Information printing

1.1 Printing Methods

Print messages directly:
- Code format:
```
--print
Copy the code
```
- Actual combat demonstration:
  
  You can see it’s printed in the window;
To print messages in a table, you can set multiple columns and the name of each column:
- Code format:
```
--select
Copy the code
```
- Actual combat demonstration:
  
  The results are printed in a table; Select can also be used to query data from tables;

Variables, Go statements, and operators

2.1 Variables in T-SQL are divided into local variables and global variables

Local variables:
- Features:
  - It’s prefixed with @
  - Declare first, then assign
- Example:
```
declare @str varchar(20)
set @str = 'I love database programming'- or select@str = 'I love database programming'
print @str
Copy the code
```
  Declarations in SQL begin with DECLARE
- What is the difference between a set assignment and a select assignment?
  - Set: The value assigned to a variable
  - Select: is used to query data from a table. If multiple records are queried, the value of the last record is assigned to a variable, for example:
```
Select @variable name = field name from table nameCopy the code
```
  In the assignment process, if the data is queried in a table, if there is only one record, use either set or SELECT, but select is used customarily.

Global variables:

Features:
- It’s prefixed with @, @
- It is defined and maintained by the system and read-only

Description:

- the @@ERROR: Returns the error number of the last statement executed --@@IDENTITY: returns the last inserted identifier value --@@MAX_CONNECTIONS: Returns the maximum number of simultaneous user connections --@@ROWCOUNT: returns the number of rows affected by the previous statement --@@SERVERNAMEReturns the name of the local Server where SQL Server is running --@@SERVICENAME: Returns the name of the registry key under which SQL Server is running --@@TRANCOUNT: Returns the number of active transactions currently connected --@@LOCK_TIMEOUT: Returns the current lock timeout setting for the current session (ms)Copy the code

Sample code:
- Open an account for Zhao Yun. Id number: 420107199904054233
  
  We first perform an insert operation, when the user information is saved, and then define a variable called AccountId that is loaded as @@inEntity, which is the primary key ID of the previous INSERT operation. Get the primary key ID, then insert BankCard to open the account.
- The card number and balance of Zhang Fei’s bank card are required. The ID card of Zhang Fei is 420107199602034138
```
-- Option 1: Connection query select CardNo Card number,CardMoney balance from BankCard left join AccountInfo on bankcard. AccountId = AccountInfo.AccountId where AccountCode ='420107199602034138'Solution 2: Use variable DECLARE@AccountId int
select @AccountId = (select AccountId from AccountInfo where AccountCode = '420107199602034138') select CardNo,CardMoney balance from BankCard where bankcard. AccountId =@AccountId
Copy the code
```
  In scheme 2, the value of @accountid is passed in through the select statement, which passes the result of the query to AccountId.

2.2 go statement

Summary:
- Wait for the code before the GO statement to complete before executing the code after the go statement
- The end of a batch statement
Code examples:
- Wait for the previous code to complete:
  In this figure, if ‘use DBTEST1’ is used, an exception will be thrown. So in such a business scenario, we can use Go to execute the subsequent code after the previous code has been executed.
- A sign of the end of a batch:
  If it is above go, the declared scope is above, if it is between two Go, it is between two Go, and it can define a scope. After GO, it cannot continue to use this variable because the batch processing is finished.

2.3 the operator

There are seven types of operators used in T-SQL, as shown in the figure:

2.4 Actual Case

Practical examples:

Given the length and width of a rectangle, find its circumference and area
```
declare @c int =10
declare @k int =5
declare @zc int
declare @mj int
set @zc= (@c + @k) *2
set @mj = @c*@k
print 'Circumference :'+ Convert(varchar(10),@zc) 
print 'Circle area' + Convert(varchar(10),@mj)
Copy the code
```
The printed result is: circle length: 30 circle area: 50; Here Convert is to Convert @zc and @mj to a string of type varchar(10). You can also use cast to achieve the same effect, as shown in the figure:I’ll talk about the differences later
Query the information about the frozen bank cards whose balance exceeds 1,000,000
```
select * from BankCard where CardState = 3 and CardMoney> 1000000
Copy the code
```
So 3 is frozen

Query the information about a frozen bank card with a balance equal to 0

select * from BankCard where CardState = 3 and CardMoney =0
Copy the code

Query the account information and bank card information with “Liu” in the name

select * from AccountInfo inner join BankCard on BankCard.AccountId = AccountInfo.AccountId  where RealName like '% % liu'
Copy the code

Query the bank card information with the balance between 2000 and 5000

select * from BankCard where CardMoney between 2000 and 5000
Copy the code

The information about the frozen or deregistered bank card is displayed
```
select * from BankCard where CardState in(3.4)
Copy the code
```

Guan Yu ID card: 420107199507104133, Guan Yu came to the bank to open an account, check whether the ID card exists in the account table, open an account if it does not exist, open a card if it does not open an account directly.

declare @AccountId int
if EXISTS(select * from AccountInfo where AccountCode = '420107199507104133')Begin select @accountid= (select AccountId from AccountInfo where AccountCode = '420107199507104133') insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState) values('6225547858741264'.@AccountId.'123456'.0.1)
	end
else-- Begin insert into does not existAccountInfo(AccountCode,AccountPhone,RealName,OpenTime) 
		values('420107199507104133'.'13656565656'.'guan yu',getdate())
		set @AccountId = @@IDENTITY
		insert into BankCard (CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225547858741264'.@AccountId.'123456'.0.1)
	end
Copy the code

Extension Step 7: Add a limit to the requirement above, that is, a person can open a maximum of three bank cards:

declare @AccountId int-- Account NUMBER DECLARE@CardCount int- the card numberif EXISTS(select * from AccountInfo where AccountCode = '420107199507104133')Begin select @accountid= (select AccountId from AccountInfo where AccountCode = '420107199507104133') 
		select @CardCount = (select count(*) from BankCard where AccountId = @AccountId)
		if @CardCount< =2
			begin
				insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState) values('6225547858741264'.@AccountId.'123456'.0.1)
			end
		elseBegin print 'There are too many cards in your name, only 3 cards at most' end endelse-- Begin insert into does not existAccountInfo(AccountCode,AccountPhone,RealName,OpenTime) 
		values('420107199507104133'.'13656565656'.'guan yu',getdate())
		set @AccountId = @@IDENTITY
		insert into BankCard (CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225547858741264'.@AccountId.'123456'.0.1)
	end
Copy the code

Check the balance of bank card account, whether all the balance exceeds 3000

if 3000 < All(select CardMoney from BankCard)
	begin
		print 'All bank cards have balances over 3000'
	end
else
	begin
		print 'Not all bank cards have balances over 3000'
	end
Copy the code

Query the balance of the bank card account and check whether the balance exceeds 30000000

if 30000000 < Any(select CardMoney from BankCard)
	begin
		print 'Bank card balance exceeds 3000'
	end
else
	begin
		print 'No bank card balance exceeds 3000'
	end
Copy the code

3. Process control

3.1 Selecting a branch structure

The style is:

if xxx
	begin
		xxx
	end
else
	begin
		xxx
	end
Copy the code

Case demonstration:

The bank card number of a user is 6225547854125656. The user performs the withdrawal operation and withdraws 5000 YUAN. If the balance is sufficient, the user will perform the withdrawal operation and prompt “successful withdrawal”; otherwise, the prompt “insufficient balance” will be prompted.

declare @banlance money
select @balance = (select CardMoney from BankCard where CardNo = '6225547854125656')
if @balance> =5000
	begin
		update BankCard set CardMoney = CardMoney -5000
		insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
		values('6225547854125656'.0.5000,GETDATE())
	end
elsePrint 'insufficient balance'Copy the code

Query information bank card, the card state 1, 2, 3, 4 respectively into Chinese characters, “normal, report the loss, freeze, cancel”, and according to the display card, bank card balance below 300000 for the “average user”, and more than 300000 as the “VIP member”, according to column card number respectively, and the id, name, balance, user level, bank card state.

Select CardNo,AccountCode,RealName,CardMoney balance,case
	when CardMoney >= 300000 then 'the VIP users'
	else 'Ordinary user'End User levelcase CardState
	when 1 then 'normal'
	when 2 then 'report the loss of'
	when 3 then 'freeze'
	when 4 then 'cancel'
	else 'abnormal'
end
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
Copy the code

Loop structure (while)

Cycle print 1-10

declare @int =1
while @i< =10
begin 
	print @i
	set @i = @i +1
end
Copy the code

Print the multiplication table in a loop

declare @i int =1
while @i < 9
	begin
		declare @str varchar(1000) = ' '
		declare @j int =1
		while @j< =@i
			begin
				set @str = @str + cast(@i as varchar(1)) + '*' + cast(@i as varchar(1)) + '=' + cast(@i*@j as varchar(2)) + char(9) set @j = @j+1 end set @i = @i +1 endCopy the code

Special characters: char(9): TAB; Char (10): newline character, print is automatic newline

4. Sub-query

4.1 Guan Yu’s bank card number is “6225547858741263”. The information of the bank card with more balance is found, and the card number, ID card, name and balance are displayed.

Solution a:

declare @gyBalance money
select @gyBalance = (select CardMoney from BankCard where CardNo = '6225547858741263'Select accountNo, AccountCode, RealName, Account balance from BankCard left join AccountInfo on bankcard. AccountId = AccountInfo.AccountId where CardMoney >@gyBalance
Copy the code

Scheme 2:

Select CardNo id,AccountCode ID, RealName Name,CardMoney balance from BankCard left join AccountInfo on bankcard. AccountId = AccountInfo.AccountId WHERE CardMoney > (select CardMoney from BankCard where CardNo ='6225547858741263')
Copy the code

4.2 Query the transaction details with the highest balance from all account information (information of deposit and withdrawal) :

Solution a:

select * from CardExchange where CardNo in (select CardNo from BankCard where CardMoney = (select MAX(CardMoney) from BankCard))
Copy the code

Scheme 2:

select * from CardExchange where CardNo = (select top 1 CardNo from BankCard order by CardMoney desc)
Copy the code

4.3 Query the bank card and account information with withdrawal record, display the card number, ID card, name and balance

Select CardNo Card number,AccountCode ID,RealName name,CardMoney balance from BankCard inner join AccountInfo on bankcard.accountid = AccountInfo.AccountId where CardNo in (select CardNo from CardExchange where MoneyOutBank > 0)
Copy the code

4.4 Check the information of bank cards and accounts without deposit records, and display the card number, ID card, name and balance.

Select CardNo,AccountCode id,RealName name, CardMoney balance from BankCard inner join AccountInfo on bankcard. AccountId = AccountInfo.AccountId  where CardNo not in (select CardNo from CardExchange where MoneyInBank > 0 )
Copy the code

4.5 Guan Yu’s bank card number is “6225547858741263”, so I would like to check whether he has received the transfer on that day.

if exists(select * from CardTransfer where CardNoIn = '6225547858741263' and CONVERT(VARCHAR(22).GETDATE(a)23),= CONVERT(VARCHAR(22), TransferTime, 23))
	begin
		print 'Transfer received'
	end
else
	begin
		print 'No transfer received'
	end
Copy the code

4.6 Query the bank card account information with the most transactions (deposits and withdrawals), showing the card number, ID card, name, balance and transaction times

select top 1BankCard.CardNo Card number,AccountCode ID card, RealName name, CardMoney balance, Temp.myCount Number of transactions from BankCard inner join AccountInfo on bankcard. AccountId = AccountInfo.AccountId
inner join (select CardNo, count(*) myCount from CardExchange group by CardNo) Temp on BankCard.CardNo = Temp.CardNo
order by Temp.myCount desc
Copy the code

4.7 Check the bank card account information without transfer transaction record, and display the card number, ID card, name and balance.

Select CardNo, AccountCode id, RealName name, CardMoney balance from BankCard inner join AccountInfo on bankcard. AccountId = AccountInfo.AccountId 
where CardNo not in (select CardNoOut from CardTransfer)
and CardNo not in (select CardNoIn from CardTransfer)
Copy the code

5 pages.

5.1 Solution 1: Top Mode

declare @PageSize int = 5
declare @PageIndex int = 1
select top(@PageSize) * from Student where StuId not in (select top(@PageSize* (@PageIndex-1)) StuId from Student)
Copy the code

The query efficiency is not high when there is a large amount of data

5.2 Scheme 2: Paging through rowNumber

declare @PageSize int = 4
declare @PageIndex int = 2
select * from (select ROW_NUMBER(a) over(order by StuId) RowId , * from Student) Temp
where RowId between(@PageIndex-1)* @PageSize+1 and @PageIndex * @PageSize
Copy the code

6. The transaction

6.1 Assuming that Liu Bei withdraws 6000, (add Check constraint and set account balance must >=0), the requirements are as follows: use transaction implementation, modify balance and add withdrawal record, use transaction

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.5000,GETDATE())
set @myError = @myError + @@ERROR
if @myError =0
	begin
		commit transaction
		print 'Withdrawal successful'
	end
else 
	begin
		rollback transaction
		print 'Withdrawal failed'
	end
Copy the code

@@error will return an ERROR int value when an exception occurs. We put this ERROR value into @myError. If its final value is greater than 0, it indicates that there is an exception and the rollback will be carried out.

6.2 Assume that Liu Bei transfers 1000 YUAN to Zhang Fei (add check constraint, set account balance must be >= 0); The analysis steps are as follows: (1) Zhang Fei adds 1000 yuan (2) Liu Bei deducts 1000 yuan (3) Generate transfer records; Requirement: Use transactions to resolve this problem

begin transaction
declare @myerr int =0
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '6225547854125656' set @myerr = @myerr + @@ERROR
update BankCard set CardMoney = CardMoney -1000 where CardNo = '6225125478544587' set @myerr = @myerr + @@ERROR
insert into CardTransfer(CardNoOut, CardNoIn , TransferMoney,TransferTime) values('6225125478544587'.'6225547854125656'.1000,GETDATE()) set @myerr = @myerr + @@ERROR
if @myerr = 0
	begin
		commit transaction
		print 'Transfer successful'
	end
else 
	begin
		rollback transaction
		print 'Withdrawal failed'
	end
Copy the code

Index of seven.

7.1 an overview of the

Index: Improves retrieval query efficiency.
SQL SERVER index type:
- According to storage structure: clustered index (also known as clustered index, clustered index), “non-clustered index (non-clustered index, non-clustered index)”;
  - Clustered index: Stores rows in a table or view in order of their key value, with only one clustered index per table. A clustered index is a way of reorganizing the actual data on disk to sort by a specified column or columns of values (similar to a pinyin index in a dictionary) (physical storage order)
  - Nonclustered index: has a row-independent structure that contains nonclustered index keys, each of which is intended to point to a pointer to a row containing a modified key value. (similar to a partial index in a dictionary) (logical storage order)
- According to data uniqueness:
  - The only index
  - Nonunique index
- Number of key columns:
  - Single index
  - The column index more
- How to create index:
  1. Through the explicit CREATE INDEX command
  2. As an implied object when creating a constraint
    1. Primary key constraint (clustered index)
    2. Unique constraint (unique index)
- Create index syntax:
```
CREATE [UNIQUE] [CLUSTERED | NONCLUSTE] 
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n])
Copy the code
```
- Index basic sample syntax:
```
-- exp: Creates a non-clustered indexcreate nonclustered index indexAccount on AccountInfo(AccountCode)Drop index indexAccount on AccountInfo drop index indexAccount on AccountInfoCopy the code
```

7.2 Index code examples

Add an index to the AccountCode field in the AccountInfo table

create unique nonclustered index index_code on AccountInfo(AccountCode)
Copy the code

Sys.indexes

select * from sys.indexes where name = 'index_code'
Copy the code

Remove the index

drop index index_code on AccountInfo
Copy the code

Displays the specified index for query

select * from AccountInfo with(index = index_code) where AccountCode = '420107199507104133'
Copy the code

Eight. View

8.1 Actual Combat Demonstration

View, which can be understood as a virtual table

Code demo:

Write view to query all bank card account information, display card number, ID card, name, balance

Create view CardAndAccount as Select CardNo, AccountCode ID card, RealName Name,CardMoney balance from BankCard left join AccountInfo on bankcard. AccountId= AccountInfo.AccountId goCopy the code

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

Delete the view

drop view View_Account_Card
Copy the code

9. The cursor

9.1 an overview of the

Cursor: Locates a row in the result set
Cursor classification:
1. Static cursor: When a cursor is operated, data changes. The data in the cursor does not change.
2. Dynamic: Data changes while operating the cursor. The data in the cursor changes. Default value.
3. KeySet: When a cursor is manipulated, the identified column changes, data in the cursor changes, other columns change, and data in the cursor stays the same.

9.2 Actual code Examples

Declare mycursorScroll -- Create a cursorforSelect Member account from Member # mycur select Member account from Member # mycur Scroll is a type that means it's a scroll cursor MemberAccount means the value of the cursor is the MemberAccount field in the Member table. Fetch first from mycur fetch last from mycur Fetch absolute2From mycur -- fetch relative2Select * from myCUR; fetch next from mycur; fetch prior to mycur@acc varchar(20)
fetch absolute 2 from mycur into @accThe second row of the absolute cursor is saved@accSelect * from Member where MemberAccount = select * from Member where MemberAccount =@acc-- Traverse cursor Declare@acc varchar(20)
fetch absolute 1 from mycur into @acc- the @@fetch_status: 0Extraction success, -1Indicates failure, and -2There is nowhile @@fetch_status = 0
	begin
		print 'Extraction successful' + @acc
		fetch next from mycur into @accEnd -- the top traversal iswhileContinue to judge the end, and then begin method has been moved down the cursor, and then realized the traversal operation; Select * from Member fetch absolute select * from Member fetch absolute2 from mycur update Member set MemberPwd = '654321' where current of mycur

fetch absolute 2From mycur delete from Member where current of mycur close mycur delete from Member where current of mycur Circularly display multiple columns of data declare MYCURSORScrollforSelect MemberAccount, MemberPwd, nickname from Member select MemberAccount, MemberPwd, nickname from Member We use according to the actual to determine; With the cursor defined above, let's start the traversal: DECLARE@acc varchar(20)
declare @pwd varchar(20)
declare @nickname varchar(20)

fetch absolute 1 from mycur into @acc.@pwd.@nickname- the @@fetch_status: 0Extraction success, -1Failure, -2There is nowhile @@fetch_status = 0 
	begin 
		print 'Username :'+@acc + ', password: '+@pwd +', nickname: '+@nickname fetch next from mycur into @acc.@pwd.@nicknameEnd -- close mycur -- Delete the cursor deallocate mycurCopy the code

After the cursor is closed, it can be opened again, but if it needs to be used again after deletion, it must be rebuilt. Cursors are like Pointers to data, so they are very flexible.

Ten function.

10.1 an overview of the

Classification of functions:
1. The system function
2. Custom function
  1. Scalar valued functions (return a single value)
  2. Table-valued functions (return query results)

10.2 function actual combat code demonstration

Write a function to sum the amount of the bank (no arguments, return a scalar value) :

drop function GetSumMoney
create function GetSumMoney(a) returns money
as 
begin
	declare @sum money
	select @sum= (select SUM(CardMoney)From BankCard) return @sum end -- call select DBo.GetSumMoney(a)
Copy the code

The above functions have no parameters. The following describes the definition and use of functions with parameters

Pass in the account number and return the real name of the account

create function GetRealNameById(@accid int) returns varcahr(30) 
as 
begin
	declare @name varchar(30)
	select @name = (select RealName from AccountInfo where AccountId = @accid) return @name
end

select dbo.GetRealNameById(2)
Copy the code

Pass the start time and end time, return the transaction record (deposit and withdraw money), the transaction record contains the real name, card number, deposit amount, withdrawal amount, transaction time

Scheme 1 :(complex queries use this scheme, there are other logical methods besides query)

create function GetRecordByTime(@start varcahr(30),@end varchar(30)) returns @result table (
	RealName varchar(20)-- Real name CardNovarchar(30)Bank card number (form primary/foreign key relationship with bank card table) As begin insert into @result select RealName name,CardExchange.CardNo MoneyInBank Deposit amount, MoneyOutBank withdraw amount,ExchangeTime Transaction time from CardExchange inner join BankCard on CardExchange.CardNo= BankCard.CardNo 
	inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where ExchangeTime between @start+ '00:00:00' and @end + "23:59:59"
	returnEnd -- The function call uses select *from GetRecordByTime('2020-01-01'.'2020-12-12')
Copy the code

Method 2 :(only return + SQL query result in function body)

drop function GetRecordByTime
create function GetRecordByTime(@start varcahr(30),@end varchar(30)) return table as return select RealName,CardExchange.CardNo, MoneyInBank, ExchangeTime from CardExchange inner join BankCard on CardExchange.CardNo= BankCard.CardNo 
	inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where ExchangeTime between @start+ '00:00:00' and @end + "23:59:59"Go -- Function calls use select *from GetRecordByTime('2020-01-01'.'2020-12-12')
Copy the code

Query the bank card information, and convert the status of bank card 1,2,3 and 4 into Chinese characters “normal, loss reporting, frozen, cancellation” respectively. According to the balance of bank card, the card level below 30W is ordinary user, and the card level above 300,000 is VIP user. The card number, ID card, name, balance, user level and bank card status are displayed respectively.

General enquiries:

Select CardNo,AccountCode id,RealName, CardMoney balance,case 
	when CardMoney < 300000 then 'Ordinary user'
	else 'the VIP users'End User level,case
	CardState
	when 1 then 'normal'
	when 2 then 'report the loss of'
	when 3 then 'freeze'
	when 4 then 'cancel'
	else 'abnormal'End Card status from BankCard inner join AccountInfo on bankcard. AccountId = AccountInfo.AccountIdCopy the code

Query using a function:

Create user level functionscreate function GetGrade(@cardmoney money) returns varchar(30) 
as 
begin
	declare @result varchar(30)
	if @cardmoney >= 300000
		set @result = 'the VIP users'
	else 
		set @result = 'Ordinary user'
	return @resultEnd -- Find the status of the card as a function:create function GetState(@state int) returns varchar(30)
as
begin
	declare @result varchar(30)
	if @state =1
		set @result = 'normal'
	else if @state = 2
		set @result = 'report the loss of'
	else if @state = 3
		set @result = 'freeze'
	else if @state = 4
		set @result = 'cancel'
	else
		set @result = 'abnormal'
	return @resultEnd -- Use the function directly when querying, Select CardNo Card number, AccountCode ID, RealName name, CardMoney balance, Dbo. GetGrade(CardMoney) user level, Dbo. GetState(CardState) Card status from BankCard inner join AccountInfo on bankcard. AccountId = AccountInfo.AccountId from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountIdCopy the code

Write a function to calculate the age according to the date of birth, and the age is the real age, for example :(1) the birthday is 2000-5-5, the current is 2018-5-4, and the age is 17. (2) Birthday is 2000-5-5, current is 2018-5-6, age is 18

Select *,year(GETDATE()) -year (empBirth) age from Empcreate function GetAge(@birth smalldatetime) returns int
as 
begin
	declare @age int
	set @age = year(GETDATE()) -year(@birth)
	if month(getdate()) < month(@birth)
		set @age = @age -1
	if month(getdate()) = month(@birth) and day(getdate()) <day(@birth)
		set @age = @age -1
	return @ageEnd select *,dbo.GetAge(empBirth) age from EmpCopy the code

Functions are more precise, more flexible, and can be reused where they are needed;

Xi. Trigger

11.1 an overview of the

Trigger concept: A trigger is a special type of stored procedure that is different from the stored procedures we cover in the next section. Triggers are automatically invoked primarily by events that trigger. Stored procedures can be called by the name of the stored procedure.
What is a trigger? Trigger a special stored procedure that is automatically executed when a table is inserted, updated, or deleted. Triggers are typically used for more complex constraints of check constraints. The difference between a trigger and a normal stored procedure is that a trigger operates on a table. When performing operations such as update, INSERT, and delete, the system automatically invokes the triggers corresponding to the table. Triggers in SQL Server2005 can be divided into two types: DML triggers and DDL triggers. DDL triggers fire with the effect of various data definition language statements, such as create, alter, and drop statements.
Trigger classification:
- After trigger
  1. The insert trigger
  2. The update trigger
  3. The delete trigger
- Instead of a trigger
The difference is that after triggers require an INSERT, update, or delete operation to be triggered and can only be defined on a table. Instead of a trigger, it does not perform its defined actions (INSERT, update, delete) but only the trigger itself. Instead of triggers can be defined on a table or on a view.
Table of triggers: Triggers have two special tables: insert table (Instered) and delete table (deleted table). These are both logical tables and virtual tables. A system creates two tables in memory that are not stored in the database. And both tables are read-only, so you can only read data, not modify it. The result of these two tables is always the same structure as the table to which the change trigger is applied. When the trigger completes its work, both tables are deleted. Data in the INSERTED table is data that has been inserted or modified, while data in the DELETED table is data that was updated or deleted.

11.2 Code practice:

Assume that there are department tables and employee tables. If the department id of the employee cannot be found in the department table when you add an employee, the department information is automatically added and the department name is New Department.

create trigger tri_InsertPeople on People after insert
as
	if exists(select * from Department where DepartmentId = (select DepartmentId from inserted))
		begin
			insert into Department(DepartmentId, DepartmentName)
			values((select DepartmentId from inseted),' new department ') end go -- Test trigger insert intoPeople(DepartmentId, PeopleName, PeopleSex,PeoplePhone) values('003'.'zhaoyun'.'male'.'13698547125')
	insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone) values('002'.'zhang fei'.'male'.'13698547125')
Copy the code

Trigger implementation, delete a department when all the employees under the department are deleted

create trigger tri_DeleteDept on Department after delete as delete from People where DepartmentId = (select DepartmentId Delete from Department where DepartmentId = go select * from Department select * from People'006'
Copy the code

Equivalent to the implementation of a cascading deletion operation;

Create a trigger to delete a department and determine whether there are employees in the department. If there are employees in the department, the department will not be deleted. If there are no employees, the department will be deleted.

drop trigger tri_DeleteDept
create trigger tri_DeleteDept on Department after delete
as 
	if not exists(select * from People where DepartmentId = (select DepartmentId from deleted)) 
		delete from Department where DepartmentId = (select DepartmentId from deleted) go -- delete from Department where DepartmentId ='001'
Copy the code

After a department number is changed, the department numbers of all employees in the department are changed simultaneously

create trigger tri_UpdateDept on Department after update
as
	update People set DepartmentId = (select DepartmentId from inserted)
	where DepartmentId = (select DepartmentId from deleted)
go

update Department set DepartmentId = '005' where DepartmentId = '001'
Copy the code

Note: when using triggers, one function point triggers the deletion of B according to the deletion of A, another function point triggers the deletion of C according to the deletion of B, and so on. The original operation of A only wants to delete B, but other triggers may also take effect at the same time, causing all the triggers to be deleted.

Xii. Stored procedures

12.1 an overview of the

Summary:
- Transact-sql stored procedures, much like methods in the JAVA language, can be called repeatedly. When a stored procedure is executed once, the statement can be cached so that the statement in the cache can be used for the next execution. This improves the performance of the stored procedure.
Concept:
1. A stored Procedure is a set of SQL statements that are compiled and stored in a database to perform specific functions. Users can execute the statements by specifying the name and parameters of the stored Procedure.
2. Stored procedures can contain logical control statements and data manipulation statements that can accept parameters, output parameters, return single or multiple result sets, and return values.
3. Because stored procedures are compiled on the database server and stored in the database when they are created, stored procedures run faster than individual BLOCKS of SQL statements. At the same time, only the stored procedure name and necessary parameter information need to be provided during the call, so it can reduce the network traffic and simplify the network burden to a certain extent.
Advantages:
1. Stored procedures allow standard component programming:
  - After a stored procedure is created, it can be executed multiple times in the program without having to rewrite the SQL statement of the stored procedure. Moreover, database professionals can modify stored procedures at any time without affecting the application source code, greatly improving program portability.
2. Stored procedures enable faster execution:
  - If an operation contains a large number of T-SQL statements that are executed multiple times, stored procedures can execute much faster than batch processes. Because stored procedures are precompiled, when a stored procedure is run for the first time, the query optimizer analyzes it, optimizes it, and gives the storage plan that ends up in the system tables, while batch T-SQL statements are precompiled and optimized every time they are run, so they are slower.
3. Stored procedures reduce network traffic
  - For an operation on a database object, if the T-SQL statement involved in the operation is organized into a stored procedure, when the stored procedure is called on the client, only the call statement is passed in the network. Otherwise, there will be multiple SQL statements, thus reducing network traffic and reducing network load.
4. Stored procedures can be exploited as a security mechanism:
  - The system administrator can restrict the permission of a stored procedure to prevent unauthorized users from accessing data and ensure data security.
Functions are referred to in SQL, while stored procedures can be called externally. For example, Java or C# can call stored procedure statements directly.

Common system stored procedures are:

exec sp_databases; -- Query database exec sp_tables; Exec sp_columns student; Exec sp_helpIndex student; Exec sp_helpConstraint student; -- constraint exec sp_stored_procedures; exec sp_helptext'sp_stored_procedures'; Exec sp_rename student, stuInfo; Exec sp_renamedb myTempDB, myDB; -- Change database name exec SP_defaultdb'master'.'myDB'; -- Change the default database for login names to exec SP_helpdb; Exec sp_helpdb master;Copy the code

12.2 Case Demonstration

Example system stored procedure:

Alter table rename exec sp_rename'stu'.'stud'; select * from stud; -- rename the exec sp_rename column'stud.name'.'sName'.'column';
exec sp_help 'stud'; -- rename index exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student'; Select * from sys.objects where type ='P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
Copy the code

Combat: User-defined stored procedures:

Create syntax:

The create proc | procedure pro_name [} {@ parameter data type = default value (the output), {@} parameter data type = default value (the output),...  as SQL_statementCopy the code

A stored procedure with no input parameters and no output parameters;

Drop proc proc_MinMoneyCard create proc proc_MinMoneyCard as select top. Drop proc proc_MinMoneyCard as select top1CardNo,RealName,CardMoney from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId order by CardMoney go exec proc_MinMoneyCard select * from BankCard (The lowest balance, more than one person, Create proc proc_MinMoneyCard as select CardNo, RealName,CardMoney from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardMoney = (select min(CardMoney) from BankCard)
go
exec proc_MinMoneyCard
Copy the code

A stored procedure with input parameters and no output parameters

Create proc proc_Cunqian create proc proc_Cunqian@CardNo varchar(30)
@money money
as 
	update BankCard set CardMoney = CardMoney + @money where CardNo = @CardNo
	insert into CardExchange(CardNo, MoneyInBank,MoneyOutBank,ExchangeTime)
	values(@CardNo.@money.0,getdate())
go
select * from BankCard
select * from CardExchange
exec proc_Cunqian '6225547858741263',1000
Copy the code

Stored procedures with input parameters and no output parameters, but with return values (return values must be integers)

-- Simulate the bank card withdrawal operation, input the bank card number, withdraw money amount, realize the withdrawal operation, -- withdraw money successfully, return1, failed to withdraw money return -1
create proc proc_Quqian
	@CardNo varchar(30),
	@money money
as
	update BankCard set CardMoney = CardMoney - @money
	where CardNo = @CardNo
	if @@ERROR <> 0
		return -1
	insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values(@CardNo.0.@money,getdate())
	return 1
	go

	declare @returnValue int
	exec @returnValue = proc_Quqian '6225125478544587'.100
	select @returnValue~ ~ ~Copy the code

A stored procedure with input parameters and output parameters

-- Query the bank deposit or withdrawal information of a certain period as well as the total amount of deposit or withdrawal. -- Pass in the start time and end time, display the transaction information of deposit or withdrawal, and return the total amount of deposit or withdrawal. create proc proc_selectExchange@start varchar(20(Start time@end varchar(20), -- End time@sumInMoney output, -- Total amount of deposits@sumOutMoney output -- total amount of money as select@sumIn= (select sum (MoneyInBank) from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59')
	select @sumOut = (select sum(MoneyOutBank) from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59')
	select * from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59'
go
declare @sumIn money
declare @sumOut money
exec proc_selectExChange '2020-1-1','2020-12-11',@sumIn output,@sumOut output
select @sumIn
select @sumOut
Copy the code

A stored procedure with simultaneous input and output parameters

-- Password upgrade, pass in username and password, if username and password is correct, and password length <8Automatically upgrade to8Bit password select *from BankCard
select floor(rand()*10)

create proc procPwdUpgrade
	@CardNo nvarchar(20), -- Card number @pwdnvarchar(20)Output -- card number asif not exists(select * from BankCard where CardNo = @CardNo and CardPwd = @pwd)
		set @pwd = ' '
	else
		begin
			if len(@pwd) < 8
			begin
				declare @len int = 8 - len(@pwd)
				declare @i int = 1
				while @i< =@len
					begin
						set @pwd = @pwd + cast(floor(rand() * 10) as varchar(1))
						set @i = @i+1
					end
				update BankCard set CardPwd = @pwd where CardNo = @CardNo
		end 
go
declare @pwd nvarchar(20) = '123456'
exec procPwdUpgrade '6225125478544587'.@pwd output
select @pwd
Copy the code

@pwd nvarchar(20) output indicates that it can be used as both input and output;

Read the ~ blogger code word is not easy, feel useful can collect praise, thank you for watching, we come together ヾ(◍°∇°◍) Blue