Shulou(Shulou.com)06/01 Report--

This article mainly explains how to use functions, stored procedures and triggers in SQL Server. The explanation in this article is simple and clear and easy to learn and understand. Please follow the editor's ideas to study and learn how to use functions, stored procedures and triggers in SQL Server.

I. function

Functions are divided into (1) system functions and (2) custom functions.

Custom functions can be divided into (1) scalar-valued functions (returning a single value) and (2) table-valued functions (returning query results).

This article mainly introduces the use of custom functions.

(1) write a function to calculate the sum of the amount of the bank

Create function GetSumCardMoney () returns money asbegin declare @ AllMOney money select @ AllMOney = (select SUM (CardMoney) from BankCard) return @ AllMOneyend

Function call

Select dbo.GetSumCardMoney ()

The above functions have no parameters. The definition and use of functions with parameters are described below.

(2) pass the account number and return the real name of the account.

Create function GetNameById (@ AccountId int) returns varchar (20) asbegin declare @ RealName varchar (20) select @ RealName = (select RealName from AccountInfo where AccountId = @ AccountId) return @ RealNameend

Function call

Print dbo.GetNameById (2)

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

Scenario 1 (the logic is complex, and the content of the function is other than the sql statement that returns the result, such as defining variables, etc.):

Create function GetExchangeByTime (@ StartTime varchar (30), @ EndTime varchar (30)) returns @ ExchangeTable table (RealName varchar (30),-- real name CardNo varchar (30),-- card number MoneyInBank money,-- deposit amount MoneyOutBank money,-- withdrawal amount ExchangeTime smalldatetime-transaction time) asbegin insert into @ ExchangeTable select AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchange left join BankCard on CardExchange.CardNo = BankCard.CardNo left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardExchange.ExchangeTime between @ StartTime+' 00001 and @ EndTime+' 23Fr59 StartTime+' 59' returnend

Function call

Select * from GetExchangeByTime ('2018-6-1')

Scenario 2 (the logic is simple and the function content is a sql query statement directly):

Create function GetExchangeByTime (@ StartTime varchar (30), @ EndTime varchar (30)) returns tableas return select AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank, CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchange left join BankCard on CardExchange.CardNo = BankCard.CardNo left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardExchange.ExchangeTime between @ StartTime+' 00EndTime varchar 00' and @ EndTime+' 23:59:59'go

Function call:

Select * from GetExchangeByTime ('2018-6-1919)

(4) inquire the bank card information, convert the bank card status 1jing2jing3jin4 into the Chinese characters "normal, report loss, freeze, cancel" respectively. According to the bank card balance, the bank card level below 300000 is "ordinary user", and 300000 and above is "VIP user", showing card number, ID card, name, balance, user level, bank card status respectively.

Scenario 1: use case when directly in sql statements

Select * from AccountInfoselect * from BankCardselect CardNo card number, AccountCode ID card, RealName name, CardMoney balance, case when CardMoney < 300000 then 'ordinary user' else 'VIP user' end user level Case when CardState = 1 then 'normal' when CardState = 2 then 'report loss' when CardState = 3 then 'Frozen' when CardState = 4 then 'Log out of' else 'abnormal' end Card status from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId

Plan 2: realize the level and state with a function

Create function GetGradeByMoney (@ myMoney int) returns varchar (10) asbegin declare @ result varchar (10) if @ myMoney < 3000 set @ result = 'normal user' else set @ result = 'VIP user' return @ resultendgocreate function GetStatusByNumber (@ myNum int) returns varchar (10) asbegin declare @ result varchar (10) if @ myNum = 1 set @ Result = 'normal' else if @ myNum = 2 set @ result = 'reported loss' else if @ myNum = 3 set @ result = 'frozen' else if @ myNum = 4 set @ result = 'logout' else set @ result = 'abnormal' return @ resultendgo

Function call to realize query function

Select CardNo card number, AccountCode ID card, RealName name, CardMoney balance, dbo.GetGradeByMoney (CardMoney) account level, dbo.GetStatusByNumber (CardState) card status from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId

(5) write a function to calculate the age and age according to the date of birth, for example:

Birthday is 2000-5-5, currently 2018-5-4, age 17

The birthday is 2000-5-5, currently 2018-5-6, and the age is 18

The test data are as follows:

Create table Emp (EmpId int primary key identity (1) 2),-- automatic numbering empName varchar (20),-- name empSex varchar (4),-- gender empBirth smalldatetime-- birthday) insert into Emp (empName,empSex,empBirth) values ('Liu Bei', 'male', '2008-5-8') insert into Emp (empName,empSex,empBirth) values ('Guan Yu', 'male', '1998-10-10') insert into Emp (empName,empSex) EmpBirth) values ('Zhang Fei', 'male', '1999-7-5') insert into Emp (empName,empSex,empBirth) values ('Zhao Yun', 'male', '2003-12-12') insert into Emp (empName,empSex,empBirth) values ('Ma Chao', 'male', '2003-1-5') insert into Emp (empName,empSex,empBirth) values ('Huang Zhong', 'male', '1988-8-4') insert into Emp (empName,empSex,empBirth) values ('Wei Yan', 'male' '1998-5-2') insert into Emp (empName,empSex,empBirth) values ('Jian Yong', 'male', '1992-2-20') insert into Emp (empName,empSex,empBirth) values ('Zhuge Liang', 'male', '1993-3-1') insert into Emp (empName,empSex,empBirth) values ('Xu Shu', 'male', '1994-8-5')

Function definition:

Create function GetAgeByBirth (@ birth smalldatetime) returns intasbegin 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

Function call to implement query

Select *, dbo.GetAgeByBirth (empBirth) age from Emp II, trigger

Trigger classification: (1) "Instead of" trigger (2) "After" trigger

"Instead of" trigger: executed before an operation is performed

"After" trigger: executed after an operation is performed

The table and test data to be used in the later cases in the trigger are as follows:

-- Department create table Department (DepartmentId varchar (10) primary key,-- Primary key Automatically increase DepartmentName nvarchar (50),-- department name)-- personnel information create table People (PeopleId int primary key identity (1)),-- primary key, automatically increase DepartmentId varchar (10),-- department number, foreign key, associate with department table PeopleName nvarchar (20),-- person name PeopleSex nvarchar (2),-- personnel gender PeoplePhone nvarchar (20),-- phone number Contact information) insert into Department (DepartmentId,DepartmentName) values ('001MJ' General Manager') insert into Department (DepartmentId,DepartmentName) values ('002Jing' Marketing Department') insert into Department (DepartmentId,DepartmentName) values ('003Med' personnel Department') insert into Department (DepartmentId,DepartmentName) values ('004Med' Finance Department') insert into Department (DepartmentId,DepartmentName) values ('005Jing' Software Department') insert into People (DepartmentId,PeopleName,PeopleSex,PeoplePhone) values ('001Jing' Liu Bei', 'male' '13558785478') insert into People (DepartmentId,PeopleName,PeopleSex,PeoplePhone) values (' 001ZHY 'Guan Yu,' male', '13558788785') insert into People (DepartmentId,PeopleName,PeopleSex,PeoplePhone) values (' 002Zhangfei', 'male', '13698547125')

(1) suppose there is a department table and an employee table, and if the department number of the employee is not found in the department table, the department information is automatically added, and the department name is "New Department".

Write triggers:

Create trigger tri_InsertPeople on Peopleafter insertasif not exists (select * from Department where DepartmentId = (select DepartmentId from inserted)) insert into Department (DepartmentId,DepartmentName) values ((select DepartmentId from inserted), 'new department') go

Test trigger:

Insert People (DepartmentId,PeopleName,PeopleSex,PeoplePhone) values ('009 Zhaoyun', 'male', '13854587456')

We will find that when we insert the employee Zhao Yun, we will automatically add data to the department table.

(2) trigger implementation, when deleting a department, all employees under the department will be deleted.

Write triggers:

Create trigger tri_DeleteDept on Departmentafter deleteasdelete from People where People.DepartmentId = (select DepartmentId from deleted) go

Test trigger:

Delete Department where DepartmentId = '001'

We will find that when we delete this department, we will delete all employees under that department at the same time

(3) create a trigger to determine whether there are employees under a department when deleting a department. If so, do not delete it. If not, delete it.

Write triggers:

Drop trigger tri_DeleteDept-removes the previous trigger because the current trigger is also called create trigger tri_DeleteDept on DepartmentInstead of deleteas if not exists (select * from People where DepartmentId = (select DepartmentId from deleted)) begin delete from Department where DepartmentId = (select DepartmentId from deleted) endgo

Test trigger:

Delete Department where DepartmentId = '001'delete Department where DepartmentId =' 002'delete Department where DepartmentId = '003'

We will find that when there are no employees in the department, the department information can be deleted successfully, while the department with employees under the department has not been deleted.

(4) after changing a department number, change the department number of all employees under that department synchronously.

Write triggers:

Create trigger tri_UpdateDept on Departmentafter updateas update People set DepartmentId = (select DepartmentId from inserted) where DepartmentId = (select DepartmentId from deleted) go

Test trigger:

Update Department set DepartmentId=' zjb001' where DepartmentId='001'

We will find that not only the department number in the department information table has been modified, but also the information with department number 001 in the employee information table has been changed.

III. Stored procedures

A Procedure is a precompiled collection of SQL statements and process control statements.

(1) stored procedures with no input parameters and no output parameters.

Define the stored procedure to query the bank card account information with the lowest account balance, and display the bank card number, name and account balance.

-- Scheme 1: create proc proc_MinMoneyCardas select top 1 CardNo bank card number, RealName name, CardMoney balance from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId order by CardMoney ascgo-- scheme 2: (the balance is the lowest, and multiple people display multiple results) create proc proc_MinMoneyCardas select CardNo bank card number, RealName name, CardMoney balance from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardMoney= (select MIN (CardMoney) from BankCard) go

Execute the stored procedure:

Exec proc_MinMoneyCard

(2) stored procedures with input parameters but no output parameters

Simulate the bank card saving operation, pass in the bank card number, deposit amount, and realize the saving operation.

Create proc proc_CunQian@CardNo varchar (30), @ MoneyInBank moneyas update BankCard set CardMoney = CardMoney + @ MoneyInBank where CardNo = @ CardNo insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values (@ CardNo,@MoneyInBank,0,GETDATE ())-go

Execute the stored procedure:

Exec proc_CunQian '6225125478544587

(3) stored procedures that have input parameters, no output parameters, but return values (return values must be integers).

Simulate the bank card withdrawal operation, pass in the bank card number, withdraw the amount of money, realize the withdrawal operation, withdraw money successfully, return 1, withdraw failure return-1

Create proc proc_QuQian@CardNo varchar (30), @ MoneyOutBank moneyas update BankCard set CardMoney = CardMoney-@ MoneyOutBank where CardNo = @ CardNo if @ @ ERROR 0 return-1 insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values (@ CardNo,0,@MoneyOutBank,GETDATE ()) return 1go

Execute the stored procedure:

Declare @ returnValue intexec @ returnValue = proc_QuQian '662018092100000002 million print @ returnValue

(4) stored procedures with input parameters and output parameters

Find out the bank deposit and withdrawal information of a certain period of time, as well as the total amount of deposit and withdrawal, pass in the start time and end time, display the transaction information of deposit and withdrawal, and return the total amount of deposit and withdrawal.

Create proc proc_SelectExchange @ startTime varchar (20)-- start time @ endTime varchar (20)-- end time @ SumIn money output -- Total amount of deposits @ SumOut money output-- Total amount of withdrawals asselect @ SumIn = (select SUM (MoneyInBank) from CardExchange where ExchangeTime between @ startTime+' 00VOR 00VOO and @ endTime+' 23RH 59VOR 59') select @ SumOut = (select SUM (MoneyOutBank) from CardExchange where ExchangeTime between @ startTime+' 00VRO 00RO and @ endTime+' 23 startTime+' 59 and 59') select * from CardExchange where ExchangeTime between @ startTime+' 00rig 00' and @ endTime+' 23:59:59'go

Execute the stored procedure:

Declare @ SumIn money-- Total amount of deposits declare @ SumOut money-- Total amount of withdrawals exec proc_SelectExchange '2018-1-1 SumInselect @ SumOut

(5) stored procedures with simultaneous input and output parameters

Password upgrade, pass in the user name and password, if the user name password is correct, and the password length

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.