存储过程与触发器实验原理，存储过程及触发器的编写实验报告总结

1. 功能功能分为（1）系统功能和（2）自定义功能。自定义函数可以分为（1）标量函数（返回单个值）和（2）表值函数（返回查询结果），但本文主要介绍如何使用自定义函数。 (1) 编写函数计算银行总金额

创建函数GetSumCardMoney() 返回Money asbegindeclare @AllMOneymoneyselect @AllMOney=(Select SUM(CardMoney) from BankCard)return @AllMOneyend 函数调用

select dbo.GetSumCardMoney() 上面的函数没有任何参数，所以下面介绍带参数函数的定义和用法： (2) 传递账号，返回账户真实姓名。

create function GetNameById(@AccountId int)returns varchar(20)asbegindeclare @RealName varchar(20)select @RealName=(Select RealName from AccountInfo, where AccountId=@AccountId)return @RealNameend 函数调用

print dbo.GetNameById(2)(3) 传入开始时间和结束时间，返回交易记录（充值/提现），包括真实姓名、卡号、充值金额、提现金额、交易时间。方案一（逻辑复杂，函数内容多于返回结果的SQL语句，如定义变量）：

create function GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30))returns @ExchangeTable table(RealName varchar(30), --真实姓名CardNo varchar(30), --卡号MoneyInBank Money, --存款金额MoneyOutBank 资金， --提款金额ExchangeTime 小日期时间--交易时间)asbegininsert into @ExchangeTableselect AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank,CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchangeleft join BankCard on CardExchange.CardNo=BankCard.CardNoleft join AccountInfo BankCard.AccountId=AccountInfo.AccountId其中CardExchange.ExchangeTime @StartTime+'00:00:00'和@EndTime+'23:59:59'返回函数调用

select * from GetExchangeByTime('2018-6-1','2018-7-1') 选项2（逻辑简单，函数内容是直接的SQL查询语句）：

创建函数GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30)) 返回tableasreturnselect AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank,CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchangeleft join BankCard on CardExchange.CardNo=BankCard. CardNoleft 在BankCard.AccountId=AccountInfo.AccountId 处加入AccountInfo。这里@StartTime+' 00:00:00' 和@EndTime+' 23:59:59' 之间的CardExchange.ExchangeTime 进行函数调用。

select * from GetExchangeByTime('2018-6-19','2018-6-19') （4）查询银行卡信息，将银行卡状态1、2、3、4改为汉字``正常，挂失' ，转换为“冻结”。对于“各取消”，根据提款卡余额，提款卡等级在30万日元以下的称为“一般用户”，提款卡等级在30万日元以上的称为“VIP用户” 。分别显示卡号、身份证、姓名、余额、用户等级、现金卡状态。选项1：直接在SQL 语句中使用的用例

select * from AccountInfoselect * from BankCardsselect CardNo 卡号、AccountCode 身份证件、真实姓名、CardMoney 余额、CardMoney 300000（如果为“普通用户”，否则为“VIP 用户”）、最终用户级别、如果CardState=1、CardState=“普通”如果2 如果CardState=3，则“挂失”；如果CardState=4，则“冻结”；如果CardState=4，则“取消”，否则“异常” 从BankCard 内部合并中退出卡状态BankCard.AccountId=AccountInfo 的AccountInfo.AccountId 解决方案2：使用该函数级别和状态

create function GetGradeByMoney(@myMoney int)returns varchar(10)asbegindeclare @result varchar(10)if @myMoney 3000 set @result='普通用户'elseset @result='VIP 用户'return @resultendgocreate function GetStatusByNumber(@myNum int)返回值varchar(10)asbegindeclare @result varchar(10)if @myNum=1set @result='正常'else if @myNum=2set @result='报失'else if @myNum=3set @result='冻结'else if @myNum=4set @result='Logout'elseset @result='Exception'return @resultendgo 通过函数调用实现查询功能

从银行卡内部绑定中选择CardNo卡号、AccountCode身份证、真实姓名、CardMoney余额、dbo.GetGradeByMoney(CardMoney)账户等级、dbo.GetStatusByNumber(CardState)卡状态。 Join AccountInfo on BankCard.AccountId=AccountInfo.AccountId (5) 编写函数，根据出生日期查找年龄，并找到该年龄的实际年龄。示例：生日为2000 年5 月5 日，当前日期为2018 年5 月4 日，年龄为17 岁。是2000-5-5，当前日期是2018-5-6，18岁的测试数据如下：

创建表Emp(EmpId int 主键标识(1,2), --自动编号empName varchar(20), --name empSex varchar(4), --性别empBirth Smalldatetime --birthday)插入Emp(empName,empSex ) ,empBirth) Value('刘备','M','2008-5-8') 插入Emp(empName,empSex,empBirth) Value('关羽','M','1998-10-10 ' ) Insert into Emp(empName,empSex,empBirth) value('张飞','男','1999-7-5') Insert into Emp(empName,empSex,empBirth) Value('赵云','男','2003-12-12')insert into Emp(empName,empSex,empBirth)values('马超','男','2003-1-5')insert into Emp(empName,empSex,empBirth)values ('黄忠','男','1988-8-4')insert into Emp(empName,empSex,empBirth) Value('魏延','男','1998-5-2')insert into Emp ( empName,empSex, empBirth) )values('简勇','M','1992-2-20')插入Emp(empName,empSex,empBirth)values('诸葛亮','M','1993 - 3-1' ) Emp(empName,empSex,empBirth) 将值('Xu Shu','M','1994-8-5') 插入函数定义中。

创建函数GetAgeByBirth(@birth Smalldatetime) 返回intasbegindeclare @age intset @age=年(getdate()) - 年(@birth)if 月(getdate()) 月(@birth)set @age=@age - 1if 月( getdate())=Month(@birth) and day(getdate()) day(@birth)set @age=@age -1return @ageend 通过函数调用实现查询

select *,dbo.GetAgeByBirth(empBirth) Age from Emp 2.触发器触发器分类： （1）“Instead”触发器（2）“After”触发器“Instead”触发器：在执行操作之前执行“After”触发器：表和执行操作后执行的触发器内后续情况使用的测试数据如下：

--部门创建表Department(DepartmentId varchar(10) 主键， --主键，自动增加DepartmentName nvarchar(50), --部门名称)--人力资源信息创建表People(PeopleId int 主键identity(1 ,1) ), --主键，自动递增DepartmentId varchar(10), --部门编号，与部门表关联的外键PeopleName nvarchar(20), --人名PeopleSex nvarchar(2), --负责人性别PeoplePhone nvarchar( 20) , -- 电话号码、联系方式)insert intoDepartment(DepartmentId,DepartmentName)values('001','总经理室')insert intoDepartment(DepartmentId,DepartmentName)values('002','市场部')部门（部门ID、部门名称）插入值（'003'、'人力资源部'） 部门（部门ID、部门名称）值（'004'、'财务部门'） 部门（部门ID、部门名称）值('005)','软件部')People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)values('001','刘备','男','13558785478')插入People(DepartmentId,PeopleName,PeopleSex, PeoplePhone) value('001','关宇','男','13558788785') 插入People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)values('002' ,'张飞','男','13698547125 ') (1) 假设有一个部门表和一个员工表。添加员工时，如果在部门表中找不到该员工的部门编号，则会自动添加部门信息，部门名称为“新建部门”。编写触发器。

在insertasif 不存在后在People 上创建Tri_InsertPeople(select * fromDepartment whereDepartmentId=(select DepartmentId from insert)) insert intoDepartment(DepartmentId,DepartmentName) values((select DepartmentId from insert),'new Department') 测试触发器：

insert People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)values('009','赵云','男','13854587456') 插入员工赵云时，数据会自动添加到部门，我明白了。桌子。 （2）通过执行触发器，删除一个部门会删除该部门下的所有员工。编写触发器。

在Department 上创建触发器tri_DeleteDept。删除后，从人物中删除。 People.DepartmentId=（从“已删除”中选择“DepartmentId”）测试触发器：

deleteDepartment whereDepartmentId='001' 可以看到，如果删除这个部门，那么该部门下的所有员工都会同时被删除。 （3）创建触发器，删除部门时判断部门下是否有员工。如果没有，请删除它。如果没有，请删除它。编写触发器。

删除触发器tri_DeleteDept —— 删除前一个触发器，因为当前触发器也具有此名称。如果不存在则不删除，而是在Department (select * from People whereDepartmentId=(select DepartmentId from returned)) begindelete fromDepartment whereDepartmentId=(select DepartmentId from returned) 上创建触发器tri_DeleteDept 测试结束触发器：

删除部门ID='001'的部门删除部门ID='002'的部门删除部门ID='003'的部门可以看到，如果部门下没有员工，部门信息就可以删除成功了，有员工的部门不会被删除。部门下的项目没有被删除。请删除它。 （4）更改部门号后，同时更改该部门下所有员工的部门号，并编写触发器。

在updateasupdate People 之后，在Department 上创建触发器tri_UpdateDept。 setDepartmentId=(选择插入的部门id), DepartmentId=(选择删除的部门id) 测试触发器：

update Department set Department ID='zjb001' where Department ID='001' 可以看到不仅部门信息表中的部门号发生了变化，员工信息表中部门号001的信息也发生了变化.

3. 存储过程存储过程（procedure）是准备好的SQL和过程控制语句的集合。 (1) 没有输入或输出参数的存储过程。定义一个存储过程，查询账户余额最低的银行卡账户信息，并显示银行卡号、姓名和账户余额。

-- 方案1 create proc proc_MinMoneyCardas 选择银行卡前1名卡号、真实姓名、CardMoney余额的银行卡号。 BankCard内联AccountInfo on BankCard.AccountId=AccountInfo.AccountId by CardMoney ascgo order -- 方案2：（多人结果为多个时可见最低余额）create proc proc_MinMoneyCardas select CardNo 银行卡号、真实姓名、CardMoney余额来自BankCard 内部联接AccountInfo on BankCard.AccountId=AccountInfo.AccountId where CardMoney=(select MIN(CardMoney) from BankCard)go 执行存储过程。

存储过程exec proc_MinMoneyCard(2)没有输入输出参数来模拟银行卡存款操作。通过传递银行卡号和存款金额来完成存款操作。

创建proc proc_CunQian@CardNo varchar(30),@MoneyInBank Moneyas 更新BankCard 设置CardMoney=CardMoney + @MoneyInBank 其中CardNo=@CardNo CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) 为值(@CardNo,@MoneyInBank,0,GETDATE) insert()) --go 执行存储过程。

exec proc_CunQian '6225125478544587',3000 (3) 有输入参数、无输出参数、但有返回值（返回值必须是整数）的存储过程。模拟银行卡取款操作，输入您的银行卡号和取款金额即可执行取款操作。提现成功返回1，提现不成功返回-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) 名称(@ ) CardNo,0,@MoneyOutBank,GETDATE()) return 1go 执行存储过程。

声明@returnValue intexec @returnValue=proc_QuQian '662018092100000002',1000000print @returnValue (4)带输入输出参数的存储过程查询银行存取款信息和存取款总额以及一段时间内的存取款总额。返回总充值金额和总提现金额，同时显示经过的开始时间、结束时间以及充提币交易信息。

create proc proc_SelectExchange @startTime varchar(20), --开始时间@endTime varchar(20), --结束时间@SumIn Money 输出， --充值总金额@SumOut Money 输出--提现总金额asselect @SumIn=(select来自CardExchange 的SUM(MoneyInBank)，其中ExchangeTime 位于@startTime+' 00:00:00' 和@endTime+' 23:59:59')select @SumOut=(从CardExchange 中选择SUM(MoneyOutBank)，其中ExchangeTime 位于@startTime+' 00:00:00' 和@endTime+' 23 3 3 3 6059:59' ) 从CardExchange 中选择@startTime+'00:00:00'和@endTime+'23:59:59'之间的*ExchangeTime来运行存储过程。

声明@SumIn Money -- 充值总额声明@SumOut Money -- 提现总额exec proc_SelectExchange '2018-1-1','2018-12-31',@SumIn Output,@SumOut 输出选择@SumInselect @SumOut (5 ) 要升级具有并发输入和输出参数的存储过程的密码，请输入用户名和密码。如果您的用户名和密码正确且密码长度为8位，您的密码将自动升级为8位。

--有输入输出参数（输入输出参数采用密码） --密码升级。如果用户名和密码正确且密码长度为8，则输入用户名和密码。自动升级为8位密码。 select FLOOR(RAND() *10) -- 从0 到9 的随机数create proc procPwdUpgrade@cardno nvarchar(20),@pwd nvarchar(20) outasif not names(select) * From BankCard (CardNo=@cardno and CardPwd=@ pwd)set @pwd=''elsebeginif len(@pwd) 8begindeclare @len int=8- len(@pwd)declare @i int=1while @i=@lenbeginset @pwd=@pwd + Cast(FLOOR(RAND( ) *10) as varchar (1))set @i=@i+1endupdate BankCard set CardPwd=@pwd where CardNo=@cardnoendendgodeclare @pwd nvarchar(20)='123456' exec procPwdUpgrade '6225547854125656',@pwd Outputselect @pwd作者：农民马以升，原文链接：https://www.cnblogs.com/wml-it/p/16105503.html