2022年存储过程、触发器的例子( .pdf

存储过程：1.CREATEPROCEDURE loving AS BEGIN SELECT*from student where ssex=女END：执行Exec loving 2.CREATEPROCEDURE loving1 as BEGIN SELECT*from student end 3.CREATEPROCEDURE pr1_sc_ins Param1 char(10),Param2 char(2),Param3 realAS BEGIN insertinto sc(sno,cno,grade)values(Param1,Param2,Param3)END 执行:EXEC pr1_sc_ins 200215121,4,85 或EXEC pr1_sc_ins Param1=200215121,Param2=6,Param3=85 4.CREATEPROCEDURE s_grade sname char(8),sgrade realoutput AS SELECT sgrade=grade from sc join student on student.sno=sc.sno where sname=sname执行：DECLAREsgrade real EXECs_grade sname=李勇 ,sgrade=sgrade OUTPUT 5.CREATEPROCEDURE s_grade1 ssno char(9),sgrade realoutput AS SELECT sgrade=avg(grade)from sc where sno=ssno 执行：DECLAREsgrade real EXEC s_grade1 ssno=200215121,sgrade=sgrade OUTPUT SELECT sgrade6.CREATE PROCEDURE s_grade2 youbiaocursor varyingoutput AS set=cursor forward_only -forward_only表示从第一条开始往下-static（这里可以添加）forSELECT sgrade=avg(grade)from sc groupby sno open youbiao -打开游标go执行：名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 12 页 -declare youbiao2 cursor -定义一个游标作参数，用于上面那个存储过程exec guocheng5 youbiao=youbiao2 outputSELECTyoubiao7.if exists(select*from sysobjects where name=guocheng and type=p)-判定存储过程是否存在drop proc guocheng go create proc guocheng id int,name nvarchar(50)as set nocount on set rowcount id -设置要查询多少条数据-select*from adminurl where urlname like name -(1)这里的通配符是在传参数的时候带上的select*from student where urlname like%+name+%-(2)这里的通配符是程序自带的，推荐这个go 8.加密存储过程和实现另外一种模糊查询（用到系统函数）-if exists(select name from sysobjects where name=guocheng2 and type=p)drop proc guocheng2 -判断是否存在go create proc guocheng2 name nvarchar(100)with encryption -实现对存储过程加密，以后谁也看不到内容，所以事先要有备份as set nocount on select*from student where charindex(name,urlname)0 -chaindex的作用相当于 Like name go drop proc guocheng2 exec guocheng2 功能 -存储过程的几种返回值（output,return,select）-(1)output存储过程 注意在.NET 中是怎样接受的 if exists(select name from sysobjects where name=guocheng3 and type=p)drop proc guocheng3 -判断是否存在go create proc guocheng3 n int output,-申明是输出参数name nvarchar(50)with encryption -加密as 名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 12 页 -set nocount on -不显示记录数，提高网络select*from student where urlname like%+name+%set n=rowcount -赋值go-开始测试declare n int -定义输出参数exec guocheng3 n output,管理 print n -验证是否输出参数已经赋值-（2）return存储过程,切记 return返回的必须是整型值 注意在.NET 中是怎样接受的 if exists(select name from sysobjects where name=guocheng4 and type=p)drop proc guocheng4 go create proc guocheng4 name nvarchar(50),n int with encryption as set nocount on set rowcount n select*from student where urlname like%+name+%if(rowcount0)return 1 else return 0 go-开始测试declare m int exec m=guocheng4 管理,4 print m-(3):带返回游标的存储过程,并且游标只能是output类型-【1.定义】if exists(select*from sysobjects where name=guocheng5 and type=p)drop proc guocheng5 -判断存在否go create proc guocheng5 youbiao cursor varying output -定义一个游标输出参数，varying表示可以变化的as set youbiao=cursor forward_only -forward_only表示从第一条开始往下-static（这里可以添加）for select comment from adminurl -static表示建立一个临时副本，不允许修改基表，如果没有就可以修改基表open youbiao -打开游标go-【2.使用】if exists(select name from sysobjects where name=guocheng6 and type=p)名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 12 页 -drop proc guocheng6 go create proc guocheng6 -用来调用 guocheng5 as declare n nvarchar(100)-定义一个变量用于接收游标的移动的每条记录declare youbiao2 cursor -定义一个游标作参数，用于上面那个存储过程exec guocheng5 youbiao=youbiao2 output -赋值给定义个游标fetch next from youbiao2 into n -每条记录赋值while(fetch_status=0)begin if(n=2m)update adminurl set comment=comment+M where current of youbiao2 -能进行修改的前提是上面定义的游标没有static else update adminurl set comment=comment+O where current of youbiao2 -能进行修改的前提是上面定义的游标没有static fetch next from youbiao2 into n -循环赋值end close youbiao2 deallocate youbiao2 go exec guocheng6 -开始执行存储过程6-创建一个带默认值的带判断的存储过程if exists(select name from sysobjects where name=guocheng7 and xtype=p)drop proc guocheng7 go create proc guocheng7 name nvarchar(100)=null,-定义一个默认值是空的输入参数n int output -定义一个输出参数as if name is null -判断参数是否为空begin print error!return end select n=count(*)from student where urlname like%+name+%-给输出参数赋值print n go declare m int -定义临时变量exec guocheng7 管理,m -执行exec guocheng7 n=m -执行带默认值的,但是不能写成exec guocheng7 m-执行远程存储过程-创建连接服务器名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 12 页 -exec sp_addlinkedserver srv_lnk,sqloledb,210.38.202.16 exec sp_addlinkedsrvlogin srv_lnk,false,null,sa,sgu3197-这个允许调用链接服务器上的存储过程exec sp_serveroption srv_lnk,rpc out,true go-执行远程存储过程，其中srv_lnk是远程数据库的别名exec srv_shop.dbo.guocheng1-设置或撤销自动执行存储过程-use master -必须设置这个数据库exec sp_procoption 存储过程名字,startup,on -设置自动执行的存储过程exec sp_procoption 存储过程名字,startup,off -取消自动执行的存储过程go-名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 12 页 -触发器:DML触发器，属于某个表1.CREATE TRIGGER tr1 ON sc FOR INSERT,UPDATE,DELETE AS PRINT inserted表：Select*from inserted PRINT deleted表：Select*from deleted执行 insertinto sc values(200215127,1,89)update sc set grade=78 where sno=200215127 2.CREATE TRIGGER tr_s1 ON student FOR INSERT,UPDA TE AS Begin DECLARE bh varchar(6)SELECT bh=inserted.sno FROM inserted Select bh/*获取插入或更新操作时的新值（学号）*/End 执行：insertinto student(sno)values(200215141)3.CREATETRIGGER sc_insupd ON sc FOR INSERT,UPDATE AS DECLARE cj int SELECT cj=inserted.grade from inserted IF(cj 100)BEGIN RAISERROR(成绩的取值必须在到之间,16,1)ROLLBACKTRANSACTION END 执行：insertinto sc values(200215127,2,109)在消息框中显示：你删除一条数据，操作成功inserted表：(1 行受影响)deleted表：(0 行受影响)消息50000，级别16，状态 1，过程 sc_insupd，第9 行成绩的取值必须在到之间消息3609，级别 16，状态1，第1 行事务在触发器中结束。批处理已中止。4.CREATE TRIGGER tr_del_s ON student FOR DELETE as 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 12 页 -BEGIN DECLARE bh char(5)Select bh=deleted.sno from deleted Delete sc where sno=bh END 执行：deletefrom student where sno=200215127在消息框中显示5use sss ifobject_id(loving20,TR)isnotnull DROP TRIGGER LOVING20 GO CREATETRIGGER LOVING20 ON COURSE AFTER DELETE ASBEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering with SELECT statements.SET NOCOUNT ON;-Insert statements for trigger here PRINT 你删除一条数据，操作成功 END GO 执行deletefrom course where cno=8在消息框中显示：你删除一条数据，操作成功(1 行受影响)6use sss ifobject_id(loving,TR)isnotnull DROP TRIGGER LOVING GO CREATETRIGGER LOVING ON COURSE AFTER INSERT,UPDATE ASRAISERROR(NOTIFY COURSR RELATIONS,16,10)GO 执行：insertinto course values(9,数据库系统原理,5,3)名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 12 页 -在消息框中显示：消息50000，级别16，状态 10，过程 LOVING2，第4 行NOTIFY COURSR RELATIONS(1 行受影响)执行：update course set Ccredit=3 where cno=1 在消息框中显示：消息50000，级别16，状态 10，过程 LOVING2，第4 行NOTIFY COURSR RELATIONS(1 行受影响)7.use sss ifobject_id(loving10,TR)isnotnull DROP TRIGGER LOVING10 GO CREATETRIGGER LOVING10 ON COURSE AFTER INSERT ASPRINT 你插入一条数据，操作成功 GO 执行：insertinto course values(9,数据库系统原理,5,3)在消息框中显示：消息50000，级别16，状态 10，过程 LOVING2，第4 行NOTIFY COURSR RELATIONS 你插入一条数据，操作成功8。CREATE TRIGGER s_reminder ON Student WITH ENCRYPTION AFTER INSERT,UPDATEAS RAISERROR(不能对该表执行添加、更新操作,16,10)ROLLBACK GO 执行：insertinto student(sno)values(200215142)在消息框中显示：(1 行受影响)消息50000，级别16，状态 10，过程 s_reminder，第 6 行不能对该表执行添加、更新操作消息3609，级别 16，状态1，第1 行事务在触发器中结束。批处理已中止。ALTERTRIGGER s_reminder ON Student AFTER INSERT AS RAISERROR(不能对该表执行添加操作,16,10)ROLLBACK 在消息框中显示：(1 行受影响)消息50000，级别16，状态 10，过程 s_reminder，第 5 行不能对该表执行添加操作消息3609，级别 16，状态1，第1 行事务在触发器中结束。批处理已中止。(1 行受影响)名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 12 页 -9.CREATETABLE sss(sno char(9)references student(sno),sage smallint NULL,updatatime datetime NULL)Createtrigger compensation on Student after update as ifrowcount=0 return ifupdate(sage)begininsert sss select sno,sage,getdate()from inserted end 执行：update student set sage=19 where sno=200215121 假设表结构是表(id主键,Count,c1,c2,c3,c4)数据类型都是int10.create trigger Mytrg on 表名instead of insert as declare Myii begin select Myii=Count from inserted if(Myii0)insert into 表名 select*from inserted else begin-这里执行你说的 满足条件是触发 的事情-范例中是 Count0 的值就执行插入新数据,否则不执行插入的范例用UPDATE是一样的代码如下:11.create trigger Mytrg on 表名instead of update as declare Myii int,id int,c1 int,c2 int,c3 int,c4 int begin select Myii=Count,id=id,c1=c1,c2=c2,c3=c3,c4=c4 from inserted if(Myii0)update 表名 set Count=Myii,c1=c1,c2=c2,c3=c3,c4=c4 where id=id-如果你再要更新其他字段,必须在这一个触发器实现,所以这个范例是你更新任意字段(除ID)else begin 名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 12 页 -这里执行你说的 满足条件是触发 的事情-范例中是 Count1 and(select count(*)from deleted)1)print 多条更新 else if(select count(*)from inserted)=0 and(select count(*)from deleted)0)print 多条删除 else if(select count(*)from inserted)1 and(select count(*)from deleted)=0)begin print 添加多条 declare m int set m=0 declare n int declare youbiao cursor for select id from inserted open youbiao fetch next from youbiao into n while(fetch_status=0)begin set m=m+2 fetch next from youbiao into n end close youbiao deallocate youbiao print m end end end-*-*drop trigger chufa -删除触发器select*from adminurl-实例实验insert into adminurl select*,from#n -注意语句中的，当查询表里面没有添加表的字段的时候可以用常量代替，它不会影响查询操作insert into adminurl select url,urlname,comment from adminurl where id in(15,8,6,4,1)-多条插入，检验触发器对多条插入的相应insert into adminurl select url,urlname,comment from adminurl where id=8 -单条插入名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 12 页 -