1、查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。4.2 调用常用的系统存储过程EXECsp_databases -列出当前系统中的数据库EXECsp_renamedbNorthwind,Northwind1-修改数据库的名称（单用户访问）USE stuDB -当前数据库中查询的对象的列表GOEXECsp_tables-返回某个表列的信息EXECsp_columns stuInfo -返回某个表列的信息EXECsp_
2、help stuInfo -查看表stuInfo的信息EXECsp_helpconstraint stuInfo -查看表stuInfo的约束EXECsp_helpindex stuMarks -查看表stuMarks的索引EXECsp_helptextview_stuInfo_stuMarks-查看视图的语句文本EXECsp_stored_procedures-查看当前数据库中的存储过程4.3 调用常用的扩展存储过程：xp_cmdshell 可以执行DOS命令下的一些的操作,以文本行方式返回任何输出 调用语法：EXECxp_cmdshell DOS命令NO_OUTPUT【案例分析】创建数据库
3、bankDB，要求保存在D:bankUSE masterEXECxp_cmdshellmkdir d:bank,no_output -创建文件夹D:IFEXISTS（SELECT*FROM sysdatabasesWHEREname=bankDB）DROPDATABASE bankDBCREATEDATABASE bankDB（ GO-查看文件夹D:dir D:bank-查看文件5、创建存储过程5.1 定义存储过程的语法createprocedure 存储过程名 参数 数据类型= 默认值output, , 参数n 数据类型= 默认值outputAS SQL语句 GO 参数可选 参数分为输入参数
4、、输出参数 输入参数允许有默认值5.2 创建不带参数的存储过程 【问题】请创建存储过程，查看本次考试平均分以及未通过考试的学员名单说明：笔试和机试都通过了60分才算通过。-创建不带参数的存储过程createprocedure proc_studeclare avgwri floatdeclare avglab floatselect avgwri=avg（笔试成绩）,avglab=avg（上机成绩）from 成绩表 -笔试平均分和机试平均分变量print笔试成绩分数=+convert（varchar（10）,avgwri）上机成绩分数=+convert（varchar（10）,avglab）i
5、f avgwri70 and avglab70 -显示考试成绩的等级本班成绩：优秀else较差-显示未通过的学员- 参加考试不及格的学生 select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩from 学生信息表as a innerjoin 成绩表as b on a.学号=b.学号where b.笔试成绩60 or b.上机成绩605.3 调用存储过程execute（执行）语句用来调用存储过程调用的语法：EXEC 过程名 参数EXEC proc_stu5.4带参数的存储过程 存储过程的参数分两种：输入参数、输出参数 输入参数：用于向存储过程传入值，类似C语言的按值传递; 输出参数：用
6、于在调用存储过程后，返回结果，类似C语言的按引用传递; int sum （int a, int b） int s; s=a+b; return s;c=sum（5, 8）传入参数值返回结果【问题】修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是分），这导致考试的评判结果也相应变化。根据试卷的难度，我们希望笔试和机试的及格线应该是随时变化的，而不是固定的60分。【分析】在述存储过程添加个输入参数：writtenPass 笔试及格线labPass 机试及格线-带输入参数的存储过程createprocedure proc_stu2 writtenPass int,-
7、输入参数：笔试及格线 labPass int-输入参数：机试及格线=参加考试不及格的学生select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩from 学生信息表as a -查询没有通过考试的学员innerjoin 成绩表as b on a.学号=b.学号where b.笔试成绩writtenpass or b.上机成绩labpass 调用带参数的存储过程-假定本次考试机试偏难，机试的及格线定为分，笔试及格线定为分-机试及格线降分后，李斯文（分）成为“漏网之鱼”了exec proc_stu2 60,55-或这样调用：EXEC proc_stu2 labPass=55,writtenP
8、ass=605.5 带输入参数的默认值问题：如果试卷的难易程度合适，则调用者还是必须如此调用：EXEC proc_stu2 60,60，比较麻烦这样调用就比较合理：EXEC proc_stu2 55 -笔试及格线分，机试及格线默认为分EXEC proc_stu2 -笔试和机试及格线都默认为标准的分createprocedure proc_stu3 writtenPass int=60,-笔试及格线：默认为分 labPass int=60 -机试及格线：参加本次考试没有通过的学员：select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩from 学生信息表as a-查询没有通过考试的学员
9、innerjoin 成绩表as b ON a.学号=b.学号 WHERE 笔试成绩writtenPass OR 上机成绩labPass 调用带参数默认值的存储过程EXEC proc_stu -都采用默认值EXEC proc_stu 64 -机试采用默认值EXEC proc_stu 60,55 -都不采用默认值-错误的调用方式：希望笔试采用默认值，机试及格线分EXEC proc_stu ,55 -正确的调用方式：EXEC proc_stu labPass=55 5.6 带输出参数的存储过程如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了修改上例，返回未通过考试的
10、学员人数。CREATEPROCEDURE proc_stu4 notpassSum intOUTPUT,-输出（返回）参数：表示没有通过的人数 writtenPass int=60, labPass int=60 -推荐将默认参数放后select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩from 学生信息表as a -统计并返回没有通过考试的学员人数innerjoin 成绩表as b on a.学号=b.学号where b.笔试成绩labpassselect notpassnum=count（学号）from 成绩表where 笔试成绩writtenpass or 上机成绩=3-后续语
11、句引用返回结果未通过人数：+convert（varchar（5）,sum）+人,超过%,及格分数线还应下调ELSE人,已控制在%以下，及格分数线适中1.调用时也必须跟随关键字OUTPUT，否则SQL Server将视为输入参数。5.7处理存储过程中的错误 可以使用print语句显示错误信息，但这 些信息是临时的，只能显示给用户 raiserror 显示用户定义的错误信息时 可指定严重级别， 设置系统变量ERROR 记录所发生的错误等5.7.1 使用raiserror语句raiserror语句的用法如下：raiserror（msg_id | msg_str,severity,state WITH
12、option,.n） msg_id：在sysmessages系统表中指定用户定义错误信息 msg_str：用户定义的特定信息，最长255个字符 severity：定义严重性级别。用户可使用的级别为018级 state：表示错误的状态，1至127之间的值 option：指示是否将错误记录到服务器错误日志中 完善上例，当用户调用存储过程时，传入的及格线参数不在之间时，将弹出错误警告，终止存储过程的执行。CREATEPROCEDURE proc_stu5 notpassSum intOUTPUT,-输出参数 writtenPass int=60,-默认参数放后 labPass int=60 -默认参
13、数放后AS-错误处理IF（NOT writtenPass BETWEEN 0 AND 100）OR（NOT labPass BETWEEN 0 AND 100）BEGINraiserror（及格线错误，请指定之间的分数，统计中断退出,16,1）RETURN-立即返回，退出存储过程END .其他语句同上例，略GO -引发系统错误，指定错误的严重级别，调用状态为（默认）,并影响ERROR系统变量的值/*-调用存储过程，测试RAISERROR语句-*/DECLARE sum int, t intEXEC proc_stu sum OUTPUT,604 -笔试及格线误输入分SET t=ERROR错误号
14、：+convert（varchar（5）,t ）IF t0 -不等于0RETURN-退出批处理，后续语句不再执行=3-如果执行了RAISERROR语句，系统全局ERROR将不等于，表示出现了错误6用户自定义函数在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。 用户自定义函数可以在企业管理器中创建，也可以使用create function语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。 标量函数：返回单个数据值。 表值函数：返回值是一个记录集合表。在此函数中，
15、return语句包含一条单独的select语句。 多语句表值函数：返回值是由选择的结果构成的记录集。6.1 使用create function语句创建用户自定义函数使用createfunction创建用户自定义函数，其语法格式如下：createfunction owner_name. function_name（ parameter_name AS scalar_parameter_data_type = default ,.n ）returns scalar_return_data_type as begin function_body retunrn scalar_expressionen
16、d function_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。 parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定default关键字才能获得默认值。 scalar_parameter_data_type：参数的数据类型。 scalar_return_da
17、ta_type：是用户定义函数的返回值。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。 function_body：位于begin和end之间的一系列 Transact-SQL 语句，其只用于标量函数和多语句表值函数。 scalar_expression：用户自定义函数中返回值的表达式。6.2 标量函数例：在stuDB库中创建一个用户自定义标量值函数xuefen，该函数通过输入成绩来判断是否取得学分，当成绩大于等于60时，返回取得学分，否则，返回未取得学分。代码如下：USE stuDBCREATEFUNCTION xue
18、fen（chengji int）RETURNSnvarchar（10）declare returnsxuefen nvarchar（10）if chengjiset returnsxuefen=取得学分不能取得学分RETURN returnsxuefen使用刚才定义的xuefen函数来查看课程号为“”的课程，学生获得学分的情况。在查询编辑器中输入如下代码：SELECT 学号,成绩=（笔试成绩+上机成绩）/2,dbo.xuefen（笔试成绩+上机成绩）/2） AS 学分情况FROM 成绩表 WHERE 课程号=6.3 表值函数表值函数遵循的原则： RETURNS子句仅包含关键字table。不必定
19、义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集的格式设置。 FUNCTION BODY 不由BEGIN和END分隔。 RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。在stuDB库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：CREATEFUNCTION XUESHENG（inputdep nvarchar（4）RETURNStableRETURN（SELECT 学号, 姓名 FROM 学生WHERE 所属院系=inputdep）建立好该内嵌表值函数后，就可以象
20、使用表或视图一样来使用它：SELECT*FROM DBO.XUESHENG（6.4 多语句表值函数多语句函数的主体中允许使用以下语句： 赋值语句 DECLARE 语句，该语句定义函数局部的数据变量和游标。 SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。 INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。 EXECUTE语句调用扩展存储过程。6.5 多语句表
21、值函数案例：在stuDB库中创建一个多语句表值函数chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。CREATEFUNCTION chengji（ inputkm aschar（20）/*为chengji 函数定义的表结构，名称变量为cji*/RETURNS cj TABLE（科目编号varchar（10）, 姓名varchar（10）, 成绩int）INSERT cj /*该变量是上面定义的表名称变量*/select b.科目编号,a.学生姓名,（b.笔试成绩+b.上机成绩）/2 from 学生信息表as a innerjoin 成绩表as b on a.学号=b.
22、学号where b.科目编号=inputkm 在查询编辑器中输入以下查询命令： SELECT * FROM dbo.chengji（9001）6.6 查看、修改和删除自定义函数1.查看用户自定义函数的属性在SQL Server中，根据不同需要，可以使用sp_helptext、sp_help等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下：使用sp_helptext查看用户定义函数的文本信息，其语法格式为：sp_helptext 用户自定义函数名使用sp_help查看用户自定义函数的一般信息，其语法格式为：sp_help 用户自定义函数名使用有关系统过程查看ST
23、UDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下：USE STUDBSP_HELPTEXT XUEFEN2.修改用户自定义函数的属性使用SQL命令修改用户自定义函数，使用ALTERFUNCTION 命令可以修改用户自定义函数。修改由CREATEFUNCTION 语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下：ALTERFUNCTION owner_name. function_nameRETURNS scalar_return_data_type AS RETURN scalar_expression其中的参数与建立用户自定义函数中的参数意义相同。3.使用T-SQL命令删除用户自定义函数使用DROP命令可以一次删除多个用户自定义函数，其语法格式为：DROPFUNCTION 所有者名称.函数名称,n删除在student库上建立的xuefen函数。USE studentDROP FUNCTION dbo.xuefen总结 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等 存储过程允许带参数，参数分为： 输入参数 输出参数 其中，输入参数可以有默认值。 输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值 输出参数从存储过
