存储过程触发器及自定义函数省公共课一等奖全国赛课获奖课件.pptx
《存储过程触发器及自定义函数省公共课一等奖全国赛课获奖课件.pptx》由会员分享,可在线阅读,更多相关《存储过程触发器及自定义函数省公共课一等奖全国赛课获奖课件.pptx(55页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、数据库实用技术SQLServer第十章存放过程、触发器和自定义函数第1页数据库实用技术第十章第十章存放过程、触发器、自定义函数存放过程、触发器、自定义函数存放过程存放过程1触发器触发器2用户自定义函数用户自定义函数3SQL Server SQL Server 实训:实训:T-SQL编程高级应用编程高级应用4第2页数据库实用技术存放过程存放过程v存放过程概述lT-SQL语句进行编程有两种方法:u一个是把T-SQL语句全部写在应用程序中,并存放在当地;u另一个是把部分T-SQL语句编写程序作为存放过程存放在SQLServer中,只在当地应用程序调用存放过程。大多数程序员偏向使用后者。l存放过程概念
2、:u存放过程(StoredProcedure)是一组编译好、存放在服务器上、能完成特定功效T-SQL语句集合,是数据库一个对象。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第3页数据库实用技术存放过程存放过程v存放过程概述l使用存放过程优点:u存放过程只需编译一次,以后即可屡次执行,所以能够提升应用程序性能。u存放过程一经被创建,能够在程序中被屡次调用;而且修改存放过程不会影响应用程序源代码,所以极大地提升了程序重用性、可维护性、共享性和可移植性。u存放过程存放在服务中,能够降低网络流量。u存放过程可被作为一个安全机制来充分
3、利用。l存放过程分类:u系统存放过程。u扩展存放过程。u用户定义存放过程。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第4页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程:u依次选择“数据库”“BillingSys”“可编程性”“存放过程”节点,右击选择“新建存放过程”命令,打开“查询编辑器”,在“查询编辑器”中出现存放过程编程模板。如左图所表示。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第5页数据库实用技术
4、存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程:u修改存放过程编程模板,插入需要T-SQL代码。存放过程编程模板。CREATEPROCEDURE-Addtheparametersforthestoredprocedurehere=,=ASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;-InsertstatementsforprocedurehereSELECT,ENDGO第十章第十章 存放过程、触发器、自定义函数存放
5、过程、触发器、自定义函数SQL Server SQL Server 第6页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程:u修改存放过程编程模板,插入需要T-SQL代码。存放过程编程模板。说明:参数按以下格式包含在尖括号()中:。其中尖括号内是参数三个元素:参数名称、该参数数据类型以及该参数默认值。parameter_name:列出存放过程或函数中参数。此字段是只读。data_type:模板中参数数据类型。此字段是只读。若要更改数据类型,请更改模板中参数。default_value:为所选参数指定值。默认值。第十章第十章 存放过程、触发器、自定义函
6、数存放过程、触发器、自定义函数SQL Server SQL Server 第7页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程:u修改存放过程编程模板,插入需要T-SQL代码。在存放过程中编写代码。在上述模板代码中:在”CREATEPROCEDURE”行命令中,用户必须自己定义一个存放过程名称,来替换参数部分,即“”部分。“Param1,”、“Param2,”行命令用来指定参数项,假如用户需要为该存放过程指定参数,则按照提醒指定参数,比如:Cust_namevarchar(20);假如不需要参数,则删除这两条命令。“SELECTParam1,”行命
7、令是为参数赋值。假如没有参数,则删除此条命令。用户从模板第33行之后(即“-Insert statements for procedure here”之后),插入所要编写存放过程代码。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第8页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程:【例10-1】为计费系统创建一个实现查询单位客户及其联络人存放过程。CREATEPROCEDUREmypro1ASBEGIN-SETNOCOUNTONaddedtopreventextraresults
8、etsfrom-interferingwithSELECTstatements.SETNOCOUNTON;selectcid,cname,rname,rtelephonefromCustomerinnerjoinRelationeronCustomer.RID=Relationer.RIDwhereCType=商业orCType=公众orCType=大户ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第9页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程:u单击工具栏上“执行
9、”按钮,即可创建该存放过程。u此时,刷新对象资源管理器,重新展开“存放过程”节点,能够看到刚建立存放过程。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第10页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l利用对象资源管理器修改存放过程:u在对象资源管理器窗口中,找到需要修改存放过程节点,在其上右击选择“修改”命令,打开“查询编辑器”,其中出现要修改存放过程代码。u对现有存放过程进行修改。u修改完成后,单击工具栏上“执行”按钮,即可完成存放过程修改。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、
10、自定义函数SQL Server SQL Server 第11页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l利用对象资源管理器删除存放过程:u在对象资源管理器窗口中,找到需要删除存放过程节点,右击选择“删除”命令。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第12页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l创建存放过程:uCREATEPROCEDUR语句语法格式以下:CREATEPROCEDURE|PROC;n形参名数据类型,变参名数据类型OUTPUT,WITHENCRYPTIO
11、N|RECOMPILEFORREPLICATIONAS第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第13页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l创建存放过程:uCREATEPROCEDUR语句参数说明:n:是可选整数,用于对同名过程分组。形参名:指定过程中参数。每个参数仅作用于该过程本身,是局部。变参名:指定作为输出参数支持结果集。该参数由存放过程动态结构,其内容可能发生改变。仅适合用于CURSOR参数。WITHENCRYPTION:指示SQLServer将CREATEPROCEDURE语句原
12、始文本转换为含糊格式。WITHRECOMPILE:指示数据库引擎不缓存该过程计划,该过程在运行时编译。假如指定了FORREPLICATION,则不能使用此选项。FORREPLICATION:指定不能在订阅服务器上执行为复制创建存放过程。使用FORREPLICATION选项创建存放过程可用作存放过程筛选器,且只能在复制过程中执行。假如指定了FORREPLICATION,则无法申明参数。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第14页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l查看存放过程信息:us
13、p_help存放过程名称:用于查看存放过程普通信息,如存放过程名称、属性、类型和创建时间。usp_helptext存放过程名称:用于查看存放过程正文信息。usp_depends存放过程名称|表名:用于查看指定存放过程所引用表或者指定表包括到全部存放过程。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第15页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l修改存放过程:uALTERPROCEDURE语句语法格式以下:ALTERPROCEDURE|PROC形参名数据类型,变参名数据类型OUTPUT,WITHE
14、NCRYPTION|RECOMPILEFORREPLICATIONAS第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第16页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l删除存放过程:uDROPPROCEDURE语句语法格式以下:DROPPROCEDURE|PROC,n比如,删除数据库BillingSys中存放过程“mypro2”,其代码为:USEBillingSysDROPPROCEDUREmypro2第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL
15、Server 第17页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l调用存放过程:uEXECUTE语句语法格式以下:Exec|Execute整型变量=存放过程名,n|存放过程变量名过程参数=参数值|可变参数名OUTPUT|DEFAULT,nWITHRECOMPILE;第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第18页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l调用存放过程:uEXECUTE语句参数说明:整型变量:是可选项,代表存放过程返回状态,该变量在用于EXECUTE语句
16、前,必须已经申明过。n:可选整数,用于对同名过程分组。过程参数:为存放过程参数赋值。其中参数名必须和存放过程定义中相同。OUTPUT:指定该参数为输出参数,该参数在存放过程中定义时也必须使用OUTPUT选项申明。DEFAULT:指明该参数使用默认值。假如该参数定义时没有指定默认值,则不能使用DEFAULT选项。WITHRECOMPILE:使用WITHRECOMPILE,强制在执行存放过程时重新对其进行编译。而普通情况下,存放过程只有在第一次执行时,系统对其进行编译,并将存放起来,以后执行时直接取出执行计划执行,不再编译。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQ
17、L Server SQL Server 第19页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例:【例10-2】为计费系统创建一个实现输入一个有效客户ID,查询其联络人存放过程。创建该存放过程Query_Relationer代码以下:CREATEPROCEDUREQuery_RelationerQueryCIDint,-输入形参QueryRNamevarchar(20)OUTPUT-输出形参ASBEGINSELECTQueryRName=RNameFROMRelationerWHERERID=(SELECTRIDFROMCustomerWHERECI
18、D=QueryCIDANDCStatus=1)ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第20页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例:【例10-2】为计费系统创建一个实现输入一个有效客户ID,查询其联络人存放过程。假如要查询客户ID为3530联络人,调用该存放过程Query_Relationer,代码以下:DECLARERelationer_namevarchar(20),Cust_IDintSETCust_ID=3530EXECUTEQuery_Relat
19、ionerCust_ID,Relationer_nameOUTPUTPRINT客户ID为+convert(char(8),Cust_ID)+联络人是:+Relationer_nameu假如要查询客户ID为3531联络人。只须在下面代码第二行,将变量Cust_ID值改为3531。u调用该存放过程,结果没有任何信息返回。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第21页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例:【例10-2】为计费系统创建一个实现输入一个有效客户ID,查询其联
20、络人存放过程。改进:前面设计存放过程Query_Relationer还有待改进。对不存在客户,提醒“不存在”信息。所以,需要对给定客户ID是否在表中存在给出判断。代码以下:ALTERPROCEDUREQuery_RelationerQueryCIDint,-输入形参QueryRNamevarchar(20)OUTPUT-输出形参ASBEGIN-判断用户输入CID值是否存在,假如不存在,返回“不存在”IFEXISTS(SELECTCIDFROMCustomerWHERECID=QueryCID)SELECTQueryRName=RNameFROMRelationerWHERERID=(SELEC
21、TRIDFROMCustomerWHERECID=QueryCIDANDCStatus=1)ELSESETQueryRName=不存在ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第22页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例:【例10-3】利用存放过程计算阶乘。代码以下:CREATEPROCEDUREfactorial-创建存放过程factorialin_numint,out_numfloatOUTPUTASBEGINDECLAREiint,ffloatSETi
22、=1SETf=1WHILEi=in_numBEGINSETf=f*iSETi=i+1ENDSETout_num=fENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第23页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例:【例10-3】利用存放过程计算阶乘。求9阶乘,调用存放过程factorial,代码以下:DECLAREfactorfloatEXECUTEfactorialout_num=factorOUTPUT,in_num=9PRINTfactor第十章第十章 存放过程、
23、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第24页数据库实用技术触发器触发器v触发器概念l触发器(Trigger)是一个特殊存放过程,它不允许带参数,不能由用户直接经过名称调用,而是由用户某一动作自动触发。lSQLServer提供三类触发器:uDML触发器:在数据库中发生数据操作(如:INSERT、UPDATE、DELETE)事件时自动执行。uDDL触发器:在服务器或数据库中发生数据定义(如:CREATE、ALTER、DROP)事件时自动执行。u登录触发器:在与SQLServer实例建立用户会话时自动执行,主要用来审核和控制服务器会话。第十章第十
24、章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第25页数据库实用技术触发器触发器v创建触发器lDML触发器uDML触发器在用户对表中数据进行插入(INSERT)、修改(UPDATE)和删除(DELETE)时自动运行。u使用触发器模板创建触发器:在展开SQLServer实例中,依次展开“数据库”“BillingSys”“表”,继续展开要创建触发器详细表节点,选择“触发器”,右击选择“新建触发器”命令,打开“查询编辑器”,在“查询编辑器”中出现触发器编程模板。当模板修改完成后,单击窗口工具栏中“执行”按钮,创建该触发器。第十章第十章 存放过
25、程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server SQL Server 第26页数据库实用技术触发器触发器v创建触发器lDML触发器uDML触发器在用户对表中数据进行插入(INSERT)、修改(UPDATE)和删除(DELETE)时自动运行。u使用触发器模板创建触发器:比如选择Customer表,打开触发器模板,其模板代码以下:CREATETRIGGER.ONAFTERASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;-Ins
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 触发器 自定义 函数 公共课 一等奖 全国 获奖 课件
限制150内