《第十章存储过程触发器及自定义函数2.ppt》由会员分享，可在线阅读，更多相关《第十章存储过程触发器及自定义函数2.ppt（49页珍藏版）》请在三一办公上搜索。
1、数据库实用技术SQL Server 2008,第十章 存储过程、触发器 和自定义函数,棱帕等寸聊酮取兴印感蛆冲鸯见轨翘边锈无拷恕拄峭肘合壮寂捞庆睦串能第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,第十章 存储过程、触发器、自定义函数,SQL Server 2008,匣产番朔弱凶壮得眼替信递好代顶吩每唇馈漠戌侨骑屡虱梦灵衅等填版持第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统,为什么需要触发器,演示：为什么需要触发器.sql,帐户信息表bank,交易信息表transInfo,张三取
2、钱200 问题：没有自动修改张三的余额,最优的解决方案就是采用触发器：它是一种特殊的存储过程 也具备事务的功能 它能在多表之间执行特殊的业务规则,张三开户1000元,李四开户1元,毯尺答诸凑咋绣踩愉唬沏村你洱坚掸盛探挽豁哆眶宴乔净越力剥堵镀数娜第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,赵二,插入,什么是触发器,删除,触发器触发,赵二退休,赵二,员工表,退休员工表,硫侩拌湿镣音降隅磋龋晤哨奏蹬审当蝶满卷控阮饮腑竖虑祸鞠授归霞苫抡第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,10.2 触发器,10.2.1 触发器概念触发器（Trigger）是在对
3、表进行插入、更新或删除操作时自动执行的存储过程。触发器通常用于强制业务规则触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case）可引用其它表中的列,第十章 存储过程、触发器、自定义函数,SQL Server 2008,洁兔矽考兢砾诌礼北缘欠休拣特蔼挚揪捎拷芬薄趴炽勺均禾靖知药谗闺缓第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器的优点（1）触发器自动执行；（2）可以调用存储过程；（3）可以强化数据条件约束；常用来实现复杂的数据完整性。（4）触发器可以禁止或回滚违反引用完整性的更改；（5）级联、并行运行；
4、（6）触发器可以嵌套。,畅寝斌骆啄坟讼曝翻恭蚁肢接思构环毯哈叠盼滦蔓材烂盘桐拼镭堤夹繁娩第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,10.2 触发器,SQL Server 2008提供三类触发器：DML触发器：在数据库中发生数据操作（如：INSERT、UPDATE、DELETE）事件时自动执行。DDL触发器：在服务器或数据库中发生数据定义（如：CREATE、ALTER、DROP）事件时自动执行。该类触发器可用于执行一些数据库管理任务。登录触发器：在与SQL Server实例建立用户会话时自动执行，主要用来审核和控制服务器会话。为响应 LOGON 事件而激发存储过程。,
5、第十章 存储过程、触发器、自定义函数,SQL Server 2008,代胰棺麓擎抢剥阐稻侮懦市疫疫摔唤扮要凝代耙娘权飘缆爵冷孵幻谱天耪第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作deleted 表临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足，则
6、向用户报告错误消息，并回滚插入操作,inserted 和deleted 表2-1,良胶碟廉讽息田雨充嚎势咏汽挟劣勾柞靠图八远速瞻匪瞄谩懦鹰浚贪到踢第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,inserted 和deleted 表2-2,inserted表和deleted表存放的信息,浦扮轮膘遂潦探酒蚊兽倍熟怖凿邱假模懊凳昨袜兼问姜式筷骚壤缘爽删葛第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,10.2.2 创建触发器1.DML触发器DML触发器在用户对表中的数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行
7、。使用触发器模板创建触发器（略）：在展开SQL Server实例中，依次展开“数据库”“BillingSys”“表”，继续展开要创建触发器的具体表节点，选择“触发器”，右击选择“新建触发器”命令，打开“查询编辑器”，在“查询编辑器”中出现触发器的编程模板。当模板修改完成后，单击窗口工具栏中的“执行”按钮，创建该触发器。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,妙锗积嚎捐悦憎臂邦布乱质泪瞒误荷绿郭棉嗅抱荔斗例阀错壶葵倚贺爬莆第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,比如选择Customer表，打开触发器模板，其模板代码如下：CRE
8、ATE TRIGGER.ON AFTER AS BEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering with SELECT statements.SET NOCOUNT ON;-Insert statements for trigger hereENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,孟运栓戳懊古川槐和提嗡兑郴楚慷垛镊窟禁极臻恤塔央俯鸡润亭寄厩碍珠第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,使用CREATE TRIGGER创建
9、 DML触发器：语法格式如下：CREATE TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OF-触发器的类型INSERT,UPDATE,DELETE-指定数据修改操作AST-SQL语句或语句块;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,冈映僳呀囤威苫雪良辖元驰仿晋酋错肖虱崔簇耿印靛骏呕奏巨椰鹿拙霍毯第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,参数说明如下：触发器名：触发器名称，必须遵守标识符命名规则，不能以#或#开头。WITH ENCRYPTION：指定对触发器进行加密处理。FOR|AFT
10、ER：指定触发器中在相应的DML操作（INSERT、UPDATE、DELETE）成功执行后才触发。视图上不能定义FOR和AFTER触发器，只能定义INSTEAD OF触发器。INSTEAD OF：指定执行DML触发器用于“代替”引发触发器执行的INSERT、UPDATE或DELETE语句。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEAD OF触发器。INSERT,UPDATE,DELETE：指定能够激活触发器的操作，必须至少指定一个操作。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,螟焚茹祖凡掺辅锰旨两云掳吩蜀赎幻毁牡旗另汲
11、虐屈耘我尽氯京垄捆爆辞第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,【例10-4】在联系人表Relationer中修改联系人ID（RID）后，同步修改客户表Customer中的联系人ID（RID），保证修改记录满足参照完整性。这是一个UPDATE触发器，其代码如下：CREATE TRIGGER Update_RIDON RelationerAFTER UPDATEAS BEGIN SET NOCOUNT ON UPDATE Customer SET RID=(SELECT RID FROM INSERTED)where RID=(SELECT RID FROM
12、DELETED)ENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,逾勤坪辕亿揍医拼始抄澜泽谷陀磕喜倡乎净行却恶儡赴恋犁饼渍襄井拿贤第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,在对Relationer表中RID值进行修改时，需要执行UPDATE命令。当执行UPDATE命令时，会激发Relationer表中的触发器Update_RID，同步对Customer表中的RID列值进行修改。由于Customer表中的RID列存在外键约束fk_Customer，因此，在执行UPDATE命令前，需要对外键约束进行禁用设置，执行完UPDATE命令
13、后，再启用外键约束。具体代码如下：,第十章 存储过程、触发器、自定义函数,SQL Server 2008,许设编衙伦拇单论纷嘲驻旦瘁摧脏追玲壁资佃郡惶秒伶虹狰黑秒试衫撇岭第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,-禁用外键约束ALTER TABLE Customer NOCHECK CONSTRAINTfk_Customer-更新纪录 UPDATE Relationer SET RID=20106609 where RID=20101845-启用约束ALTER TABLE Customer CHECK CONSTRAINT fk_Customer,第十章 存
14、储过程、触发器、自定义函数,SQL Server 2008,枪咸别慑晾哮吾瓤姑勘庭组贩帐鸯烦俏业包倘吹螟颜秉站炒泻灿畸浑硬暂第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,【例】创建触发器stu_delete，实现如下功能：当按照学号删除student表中的某学生记录后，对应的该学生在sc表中的记录也被自动删除。T-SQL语句为：USE stuinfoGOCREATE TRIGGER stu_Delete ON studentFOR DELETEASDELETE FROM sc WHERE sno=(SELECT sno FROM deleted)执行后，我们查询stud
15、ent表和sc表，可以看到两个表中均存在学号为20070102的学生记录。在student表中执行数据删除语句：DELETE FROM student WHERE sno=20070102student表中有一行受影响而sc表中有三行数据受影响。说明设定的触发器被触发，sc表中的相应数据被自动删除。,底交衅己竖残皮棕更男听靠览咋索霹拣细圭队邦岂刚锥综趋伺寨贰椅赂清第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,2.DDL触发器使用CREATE TRIGGER创建 DDL触发器：语法格式如下：CREATE TRIGGER ON ALL SERVER|DATABASE
16、-指定触发器的作用域WITH ENCRYPTIONFOR|AFTER-触发器的类型,nAS;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,莲蜂瑰浪悬皿劣答结菠劫壶榆驳余查侯联抗赘含粹缨娃樱店酚款夜汹什黔第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,参数说明如下：ALL SERVER：指定 DDL触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现命令中指定的事件类型或事件组，就会激发该触发器。DATABASE：指定DDL触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中出现命令中指定的事件类型或
17、事件组，就会激发该触发器。WITH ENCRYPTION：对 CREATE TAIGGER语句的文本进行加密处理。FOR|AFTER：指定DDL触发器仅在命令中指定事件类型或事件组的所有操作都已成功执行时才被触发。事件类型：将激活DDL 触发器的T-SQL语言事件的名称。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE等操作。事件组：预定义的T-SQL语句事件分组的名称。执行任何属于事件组的T-SQL语句事件之后，都将激发DDL触发器。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,凿荣础漏遂系谜鳖郧捡呕卿邻公聊千劈媳连鹏匹趁桶技哈坦忆芹钓缎温
18、删第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,【例10-5】设计 DDL触发器，禁止修改和删除当前数据库中的任何表。代码如下：CREATE TRIGGER safe_databaseON DATABASEFOR ALTER_TABLE,DROP_TABLEASBEGIN PRINT 不能修改或删除表！如果必须要完成此操作，请先禁用触发器safe_database。ROLLBACKENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,镐恃务筷疑枷范趾机邮柿割堪灿证闯篱叮渺拍洒可廓刘蒸疙鲁沈酿蒂叙杨第十章存储过程触发器及自定义函数2第十
19、章存储过程触发器及自定义函数2,触发器,10.2.3管理触发器1.查看触发器信息因为触发器是特殊的存储过程，所以查看触发器也是使用系统存储过程实现。如：Sp_help 触发器名称。sp_helptext 触发器名称。sp_depends 触发器名称|表名。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,吉成铲贤佬焰株阑彤千芯檀唇妮树氰持淳啊瑶存奎决枣榴肄硅啊眶肩镰行第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,DDL触发器的应用,【例】创建服务器范围的DDL触发器，当创建数据库时，系统返回提示信息：“DATABASE CREATED”。T-SQL
20、语句为：CREATE TRIGGER trig_createON ALL SERVERFOR CREATE_DATABASEASPRINT DATABASE CREATED运行创建触发器后，使用语句：CREATE DATABASE demo运行结果，消息栏内出现我们设定的“DATABASE CREATED”,鸳赡施哄假吱早训块割缠睦偏宅拴讫蒸布阂猜黔捷绪耻暴巨正窿凭性懂钡第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,2.修改触发器修改DML触发器，语法格式如下：ALTER TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD O
21、FINSERT,UPDATE,DELETEAS;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,秉丹淘烛甜棍球霓苯弛王产力往桃干抄瑞膝障迪吱脱泪习酿采铆圆禽孝当第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,修改DDL触发器，其语法格式如下：ALTER TRIGGER ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER,nAS;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,伸咽横引咀枚拯给搔对奥具赏肇禄双娟柳它阔挥峦止辐综扮屹痘怜想位末第十章存储过程触发器及自定义函数2第十
22、章存储过程触发器及自定义函数2,触发器,3.删除触发器如果确认触发器已经不再需要，可以使用DROP TRIGGER命令将其删除。其语法格式如下：DROP TRIGGER 可以使用对象资源管理器来完成触发器的管理。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,勇影呕灌丧禾吁膛箕褥基虹劝挠盾尽迸秦浚库览错姐坑燎蔫算喧船颇镶摸第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,触发器,4.禁用与启用触发器当暂时不需要某个触发器时，可将其禁用。禁用触发器的语法格式如下：DISABLE TRIGGER ON 对象名|DATABASE|ALL SERVER已禁用
23、的触发器可以被重新启用。启用触发器的语法格式如下：ENABLE TRIGGER ON 对象名|DATABASE|ALL SERVER,第十章 存储过程、触发器、自定义函数,SQL Server 2008,烁料棘肮撒窥猩栗嘻抽地骄擒霉垄凿熏胆货咕栏惰今掂踢软儒要垣脏社献第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,10.3 用户自定义函数,10.3.1 自定义函数的基本概念用户自定义函数是为了实现某些功能，用户对多个T-SQL语句进行封装，并返回结果。用户自定义函数与存储过程的比较：,第十章 存储过程、触发器、自定义函数,SQL Server 2008,综粹伎闰完倔林揍撵
24、旗廓椽海栋滓兽伦芜蜕灸辨愁峻事床讹俘遏泞粱端硬第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,1.自定义函数的基本概念使用用户定义函数的优点如下：模块化程序设计：将特定的功能封闭在一个用户定义函数中，并存储在数据库中。这个函数只需创建一次，以后便可以在程序中多次调用。并且用户定义函数可以独立于程序源代码进行修改。执行速度快：与存储过程相似，用户定义函数实施缓存计划。即用户定义函数只需编译一次，以后可以多次重用，从而降低了T-SQL代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。减少网络流量：和存储过程一样可以减少
25、网络通信的流量。此外，用户定义函数还可以用在WHERE子句中,在服务器端过滤数据，以减少发送至客户端的数字或行数。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,淬礼锌拽涟梧凹秃眷标匡峦荔翠专肛氧剔旗旗珐浚瓜腋话悦阔匣葫馒富陈第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,2.自定义函数的分类：标量值函数：标量值函数的返回值是返回子句（RETURNS子句）中定义的类型的单个数据值，不能返回多个值。内嵌表值函数：内嵌表值函数返回的是在RETURNS子句中指定的“table”类型的数据行集（表）。在内嵌表值函数中，RETUAN子句在括号
26、中含有一条单独的SELECT查询语句，该语句的结果构成了内嵌表值函数所返回的表。多语句表值函数：与内嵌表值函数一样，多语句表值函数返回的是由选择结果构成的数据行集（表）。与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的TSQL语句，并且RETURNS子句指定的表带有列及其数据类型。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,直熏迁苏狗墙辽滚膀令娥舵瓷儡拒趋哎拨淑蚀侣床南杨嗽粤迅帆癌隘失岗第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,10.3.2 创建自定义函数1.创建标量值函数：标量值函数的函数体由一条或多条TS
27、QL语句组成，写在BEGIN与END之间。其语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表达式END参数说明如下：返回值的数据类型不能是text、ntext、image和timestamp类型。在BEGINEND之间，必须有一条RETURN语句，用于指定返回表达式，即函数的值。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,蛇慌静构返卷听岗亲侦谈茶寅沧脱徘敌篷柑卵歼帝用竣忧签登云且徘缔裴第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2
28、,用户自定义函数,【例10-6】定义函数EProduct_Number，当给出一个客户ID，返回该客户所拥有的产品数量。【分析】该自定义函数接收的参数是客户ID，数据类型应与EProduct中的CID一致；返回的值是产品数，该数据量一般不大，可以设为smallint类型。代码如下：CREATE FUNCTION EProduct_Number(EP_CID int)RETURNS smallintASBEGIN DECLARE epnum smallint SELECT epnum=count(*)FROM EProduct WHERE CID=EP_CID RETURN epnumENDGO
29、,第十章 存储过程、触发器、自定义函数,SQL Server 2008,塌实撩垃慈盲此蚁皮叹帐烘钱横榨哭堤争瘫蒲疤咒则化剩雪盏头锁冉翠阀第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,标量值函数的调用 在select语句中调用：调用形式：Select 所有者名称.函数名称(实参1，实参n)实参为已赋值的局部变量或表达式，实参的顺序要与函数创建时的顺序完全一致。,别暂镍吓虏纪迷彦耀滨富眺题洪乒兰货帅洲慑顾辱院维臭漫嘶诣迭皂剩训第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,【例】对创建的函数EProduct_Table_1进行调用。SELECT dbo
30、.EProduct_Table_1(20100146),稀纷议未陆疏竖笆债蔬星叁摈政皖驳惹遵潮估蹈排泉床滞已景烂莱批验萝第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,利用Exec语句执行 调用形式：EXEC 变量名=所有者名称.函数名称(实参1，实参n)或EXEC 变量名=Schema_name.function_name 形参名1=实参1，形参名n=实参n,顾蹈知捐永耪拥盔墟案终宜百敏杉寐硝栋逐蛤传娶绅贸雄氮偷诉轻姑韧第第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,【例】利用Exec调用函数getAverAge。在查询编辑器中，输入以下代码：U
31、SE jxglDECLARE age intEXEC age=dbo.getaverage tabname=students,sex=男PRINT 男学生的平均年龄：+CONVERT(varchar(10),age),驯淆驳蛀寞碉震询唇场杜日赶腔瑞很住端惭地时秦仇替要航垃绿贫握舌具第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,2.创建内联表值函数：语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS TableWITH ENCRYPTIONAS RETURN(SELECT查询语句)参数说明如下：内联表值函数没有函数体。RET
32、URNS Table子句说明返回值是一个表。RETURN子句中的SELECT语句是返回表中的数据。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,有淫四迟段烬希妹勿列曲跪沏羽妥铸绪酥祖餐斑调瓮视推想跪锄糊奎梅抑第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,【例10-7】定义函数EProduct_Table，当给出一个客户ID，返回该客户所拥有的产品号码。代码如下：CREATE FUNCTION EProduct_Table(EP_CID int)RETURNS TableAS RETURN(SELECT ENO,EName FRO
33、M EProduct WHERE cid=EP_CID,第十章 存储过程、触发器、自定义函数,SQL Server 2008,怕敷仅峻销螟芯敞擦忱邪往焰虎卡族兄壳魂革标谆井碟泳辕狄蚊确必让蠕第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,内联表值函数,内联表值函数的调用 内联表值函数只能通过Select语句进行调用，调用时，可以仅使用函数名。【例】调用函数getCourseScore，查询学生张小峰所选修的课程及其成绩。USE jxglSELECT*FROM getCourseScore(张小峰)GO,揍炊蔗虾倒饥端柒抢揣额然孝惜乌高痘嫁镑按呸棒歌副士坠乖漂答流鱼难第十章
34、存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,3.多语句表值函数,多语句表值函数和内联表值函数都返回表，二者的不同之处在于：内联表值函数没有函数主体，返回的表是单个Select语句的结果集；而多语句表值函数在BeginEnd块中定义的函数主体由T-SQL语句序列构成，这些语句可生成记录行并将行插入到表中，最后返回表。,陛阳钓肃槛判绣播雄咳腮淋桶挪锚硷敌炎跃辫士挚哎灸闻春缉冤傅尔鞠反第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,创建多语句表值函数：语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回变量
35、 Table(表结构定义)WITH ENCRYPTIONASBEGIN RETURNEND参数说明如下：RETURNS 返回变量子句指明该函数的返回局部变量，该变量的数据类型是Table，而且在该子句中还需要对返回的表进行表结构的定义。在BEGINEND之间的语句是函数体，函数体中必须包括一条不带参数的RETURN语句用于返回表。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,氢窘酉迪擒渭转揉斜廓抓磨氰锯定堪电炭圣没黑肃稼椽任冻敞冈房态瞪较第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,【例10-8】定义多语句表值函数EProduc
36、t_Table_1，完成上例功能。代码如下：CREATE FUNCTION EProduct_Table_1(EP_CID int)RETURNS tb Table-定义tb表变量的结构，其中的列名可以和原数据表名不一样，但数据类型要一致。(tb_Eno char(11),tb_EName varchar(50),tb_EJoinData DateTime)ASBEGIN INSERT INTO tb SELECT ENO,EName,EJoinDate FROM EProduct WHERE CID=EP_CID RETURNEND,第十章 存储过程、触发器、自定义函数,SQL Server
37、 2008,冠陡考拌谁涯撅听使翅猿肠疟盗矫额怀零埋叹闷您癸仿蠕抿睫痞弄檀掇揽第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,多语句表值函数的调用,应用举例【例】调用多语句表值函数EProduct_Table_1，查询20100146客户拥有的产品。在查询分析器中输入如下代码：SELECT*FROM EProduct_Table_1 20100146),祝嘲愧烹抨敢谁锈株蛙踊密饶妨笛俄妆执亭肇丰秸隶酒虎姑躁菱盘牛巡桑第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,查看多语句表值函数：多语句表值函数创建后，可以在SQL Server实例中
38、，依次展开“数据库”“BillingSys”“可编程性”“函数”“表值函数”节点，即可看到dbo.EProduct_Table_1函数。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,扰畔囤著询链狰分瓣铂弘源魏屎明牺苯咋捏灌步砰蕉钨稽哎匆晤美汪这流第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,4.使用菜单命令创建函数：在“对象资源管理器”中也可以完成创建函数的操作：新建标量值函数 新建表值函数,第十章 存储过程、触发器、自定义函数,SQL Server 2008,克遁赚箔排阐涵矣魔紫鱼倚偏瓢德吐役眶搏犬郁腻玛坡鲁掘迢班沁丑契装第十
39、章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,10.3.3 修改和删除自定义函数1.查看用户自定义函数：sp_help 函数名称：用于查看函数的一般信息，如函数的名称及相关参数。sp_helptext 函数名称：用于查看函数的正文信息。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,抒窜锡们吗伞樊查诺所最竖氢寂卞渠粤茬锁俏铁狂虎檬染搅弗冯悲宝施挎第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,用户自定义函数,2.修改用户自定义函数：语法格式如下：ALTER FUNCTION(形参名 数据类型,n)RETURNS
40、返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表达式END3.删除用户自定义函数:语法格式如下：DROP FUNCTION 4.使用菜单命令管理自定义函数：在“对象资源管理器”中选择需要修改或删除的自定义函数，右击，选择相应的菜单命令执行操作即可。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,很瘩步弛鹊袱蚂守蛤毋慷像痘擞佑雄妻寝攒窘瑰妄循几盟鸯监兢迅岁仍台第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,实验8：T-SQL编程的高级应用,1.存储过程应用编写一个存储过程，实现由产品表（EProduct）中的单价（EU
41、nivalence）列按月统计产品的通信费用数据，并为帐单信息表（Bills）添加一数据行。2.触发器应用一般情况下产品表EProduct中的客户ID（CID）是相对不变的，只有一种情况会发生变化，即原来客户已不再使用该产品了，相隔一段时间后有新的客户重新使用它，因此发生CID的变更。创建一个触发器，在产品表EProduct中修改客户ID（CID）后，如果在开通服务表户表StartAdditionalService有相应的产品，同步删除包含该产品号码的数据行，保证EProduct表中修改记录满足参照完整性。3.自定义函数应用定义函数EProduct_AddNumber，当给出一个产品号码，返回
42、该产品所绑定的附加服务数量。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,镭马涪俗锰罗捻埃炙彩恕减加羞召酸拜欠市碧瞎捡枫牛罐携煤科蛔泥涪甚第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,小结,存储过程基本概念。创建及管理存储过程（创建、修改、删除）调用存储过程。触发器基本概念。创建触发器（创建DML，DDL）管理触发器（查看、修改、删除、禁用、启用）用户自定义函数基本概念。创建自定义函数（创建标量值、内联表值、多语句表值函数）管理自定义函数（查看、修改、删除等）,第十章 存储过程、触发器、自定义函数,SQL Server 2008,坞神龟咎霖噬趟素半粱嫌风翰蜘卓鸿善城赤纤涣王塞孙喻是橇捶润去谢篓第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,Thank You!,仅薪净目作兹谢导毛抚署泛火谐茬散肄柒杜仑懒磊具辆途沏财牢陀耙镁悍第十章存储过程触发器及自定义函数2第十章存储过程触发器及自定义函数2,
