存储过程触发器和用户定义函数

2020/1/21第8章存储过程、触发器和用户定义函数软件工程系刘金岭2020/1/22/738.1流程控制语句BEGIN......ENDIF......ELSECASE语句循环语句2020/1/23/73流程控制语句(1)BEGIN…END语句BEGIN…END语句能够将多个T-SQL语句组合成一个语句块，并将它们视为一个单元处理。其语句格式如下：BEGINT-SQL语句[,…n][BEGIN…END[,…n]]END在BEGIN…END语句中可以嵌套另外的BEGIN…END语句来定义另一语句块。T-SQL语言提供了一些可以用于改变语句执行顺序的命令，称为流程控制语句。流程控制语句允许用户更好地组织存储过程中的语句，可以方便地实现程序的功能。2020/1/24/73IF…ELSE语句IF条件表达式命令行或语句块[ELSE[条件表达式]命令行或语句块]例8.1在教学管理数据库中，如果“C4”号课程的平均成绩高于80分，则显示“C4号课程的平均成绩还不错”，否则显示“C4号课程的平均成绩一般”。IF(SELECTAVG(GRADE)FROMSCWHERECNO='C4')80PRINT'C4号课程的平均成绩还不错'ELSEPRINT'C4号课程的平均成绩一般'流程控制语句(2)2020/1/25/73CASE语句(1)简单CASE语句:CASE输入条件表达式WHEN条件表达式值1THEN返回表达式1WHEN条件表达式值2THEN返回表达式2…[ELSE返回表达式n]END流程控制语句(3)例8.2在教学管理数据库中，查询S表中学生所在系的中文名称。如“李芸”的系部是“信息系”。SELECTSNAMEAS'姓名',CASESDEPTWHEN'CS'THEN'计算机科学系'WHEN'IS'THEN'信息系'WHEN'MC'THEN'机械系’FROMSWHEN'MA'THEN'数学系'WHERESNAME='李芸'ENDAS'系部’2020/1/26/73(2)搜索CASE语句CASEWHEN条件表达式值1THEN返回表达式1WHEN条件表达式值2THEN返回表达式2…[ELSE返回表达式n]END例8.3在教学管理数据库中，显示学生“C1”课程的“成绩等级”。SELECTSNAMEAS'姓名',CASEWHENGRADE=90THEN'优秀'WHENGRADE=80THEN'良好‘WHENGRADE=70THEN'中等'WHENGRADE=60THEN'及格'WHENGRADE60THEN'不及格'ENDAS'成绩等级'FROMSJOINSCONS.SNO=SC.SNOANDCNO='C1'流程控制语句(4)2020/1/27/73循环语句WHILE条件表达式BEGIN命令行或语句块[BREAK]----断路[CONTINUE]----短路命令行或语句块END流程控制语句(5)2020/1/28/73例8.4在教学管理数据库中，利用循环的PRINT语句输出S表中女同学的信息。DECLARE@infoVARCHAR(200)DECLARE@cursCURSORSET@curs=CURSORSCROLLDYNAMICFORSELECT'学号是：'+SNO+';姓名是：'+SNAME+';性别是：'+SEX+';年龄是：'+convert(varchar(3),AGE)+'系部是：'+SDEPTFROMSWHERESEX='F'OPEN@cursFETCHNEXTFROM@cursINTO@infoWHILE(@@fetch_status=0)--游标读取下一条数据成功。BEGINPRINT@infoFETCHNEXTFROM@cursINTO@infoEND流程控制语句(6)2020/1/29/73RETURN语句使用RETURN语句，可以从查询或过程中无条件地退出，而不去执行位于RETURN之后的语句。语句格式为：RETURN[整形表达式]其中，整形表达式为一个整型数值，是RETURN语句要返回的值。该语句的含义是：向执行调用的过程或应用程序返回一个整数值。注意：当用于存储过程时，不能返回空值。如果试图返回空值，将生成警告信息，并返回0值。流程控制语句(7)2020/1/210/738.2存储过程主要内容存储过程概述创建存储过程调用存储过程管理存储过程流程控制语句(2)2020/1/211/73存储过程的分类(1)SQLServer支持的存储过程可分为5类：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。（1）系统存储过程。系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，它们为检索系统表的信息提供了方便快捷的方法。（2）用户存储过程。本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以sp_为前缀。SQLServer2008中，本地存储过程可以使用T-SQL语言编写。2020/1/212/73存储过程的分类(2)①存储过程：存储过程保存T-SQL语句集合，可以接受和返回用户提供的参数。存储过程中可以包含根据客户端应用程序提供的信息，在一个或多个表中插入新行所需的语句。②CLR存储过程：CLR存储过程是对Microsoft.NETFramework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。它们在“.NETFramework程序集”中是作为类的公共静态方法实现的。CLR常用简写词语，CLR是公共语言运行时，CommonLanguageRuntime)和Java虚拟机一样也是一个运行时环境，它负责资源管理（内存分配和垃圾收集），并保证应用和底层操作系统之间必要的分离。2020/1/213/73存储过程的分类(3)（3）临时存储过程。临时存储过程属于用户存储过程。如果用户存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。如果用户存储过程的名称前有两个“##”，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。（4）远程存储过程。远程存储过程指从远程服务器上调用的存储过程。（5）扩展存储过程。在SQLServer环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。2020/1/214/73存储过程概述(1)存储过程是T-SQL语句和流程控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。使用存储过程的优势和不足提高了处理复杂任务的能力。主要用于数据库中执行操作的编程语句，通过接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。增强了代码的复用率和共享性。存储过程只需编译一次，以后即可多次执行，因此使用存储过程可以提高应用程序的性能。减少网络中的数据流量。譬如一个需要数百行SQL代码的操作用一条执行语句完成，不需要在网络中发送数百行代码，从而大大减轻了网络负荷。可作为安全机制使用。数据库用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权限。这样，对于数据表，用户只能通过存储过程来访问，并进行有限的操作，从而保证了表中数据的安全。2020/1/215/73存储过程概述(2)使用存储过程也有不足之处，主要表现在：如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则需要更新程序集中的代码以添加参数、更新调用等，一般比较繁琐。可移植性差。由于存储过程将应用程序绑定到SQLServer，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架。代码可读性差，因此一般比较难维护。2020/1/216/73存储过程概述(3)常见的存储过程(1)系统存储过程系统存储过程是由SQLServer系统提供的存储过程，可以作为命令执行各种操作。存储过程功能sp_addlogin创建一个新的login帐户。sp_addrole在当前数据库中增加一个角色。sp_cursorclose关闭和释放游标。sp_dbremove删除数据库和该数据库相关的文件。sp_droplogin删除一个登录帐户。sp_helpindex返回有关表的索引信息。sp_helprolemember返回当前数据库中角色成员的信息。sp_helptrigger显示触发器类型。sp_lock返回有关锁的信息。sp_primarykeys返回主键列的信息。sp_statistics返回表中的所有索引列表。2020/1/217/73存储过程概述(4)例1查看数据库文件useJXGLgosp_helpfilego例2查看数据库对象的相关信息execsp_databases;--查看数据库execsp_tables;--查看表execsp_columnsstudent;--查看列2020/1/218/73存储过程概述(5)例3在对象表sys.objects中查询数据库JXGL的所有存储过程。USEJXGLselect*fromsys.objectswheretype='P';或：例4查询数据库JXGL的所有存储过程。USEJXGLselect*fromsys.objectswheretype_desclike'%pro%'andnamelike'sp%';2020/1/219/73存储过程概述(6)(2)扩展存储过程扩展存储过程以在SQLServer环境外执行的动态链接库(Dynamic-LinkLibraries，DLL)来实现。扩展存储过程功能xp_availablemedia查看系统上可用的磁盘驱动器的空间信息。xp_dirtree查看某个目录下所有子目录的结构。xp_enumdsn查看系统上设定好的ODBC数据源。xp_enumgroups查看系统上的组信息。xp_fixeddrives列出服务器上固定驱动器以及可用空间。例8.6查看’d:\mssql’目录结构。EXECxp_dirtree'd:\mssql'例8.5在教学管理数据库中，显示表S的相关性信息。EXECsp_depends@objname='S'2020/1/220/73创建存储过程(1)利用SSMS图形方式(1)在“对象资源管理器”中，展开要创建存储过程的数据库。(2)展开“数据库”、存储过程所属的数据库以及“可编程性”。(3)右键单击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”菜单项，出现“新建存储过程”对话框。参数需要修改三个元素：参数的名称、参数的数据类型以及参数的默认值。参数按以下格式包括在尖括号()中：parameter_name,data_type,default_value。parameter_name：模板中参数的名称，此字段是只读的。data_type：模板中参数的数据类型，此字段是只读的。若要更改数据类型，请更改模板中的参数。default_value：为所选参数的指定值，默认值。2020/1/221/73创建存储过程(1)(4)在主菜单“查询”上，单击子菜单“指定模板参数的值”。如图所示。(5)在“指定模板参数的值”对话框中，“值”列包含参数的建议值。接受这些值或将其替换为新值，再单击“确定”。(6)在查询编辑器中，使用过程语句替换SELECT语句。(7)若要测试语法，请在“查询”菜单上，单击“分析”。(8)若要创建存储过程，请在“查询”菜单上，单击“执行”。(9)若要保存脚本，请在“文件”菜单上，单击“保存”。2020/1/222/73创建存储过程(1)例8.8在教学管理数据库中，利用“新建存储过程”面板，创建学号和课程号参数的成绩查询存储过程SC_