1、select * from 成绩表select * from 学生信息表1 存储过程的定义存储过程（procedure）类似于C 语言中的函数 用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果int sum(int a,int b)int s;s =a+b;return s ;存储过程相当于C语言中的函数存储过程可以包含数据操纵语句、变量、逻辑 控制语句等 2 存储过程的优点执行速度更快允许模块化程序设计提高系统安全性减少网络流通量存储过程-单个 SELECT 语句SELECT 语句块SELECT语句与逻辑控制语句可以包含3、存储过程的分类3.1 系统存储过程由系统定义，
2、存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头3.2 用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数4、常用的系统存储过程4.1 系统存储过程列表系统存储过程 说明sp_databases 列出服务器上的所有数据库。sp_helpdb 报告有关指定数据库或所有数据库的信息sp_renamedb 更改数据库的名称sp_tables 返回当前环境下可查询的对象的列表sp_columns 回某个表列的信息sp_help 查看某个表的所有信息sp_helpconstraint 查看某个表的约束sp_helpind
3、ex 查看某个表的索引sp_stored_procedures 列出当前环境中的所有存储过程。sp_password 添加或修改登录帐户的密码。sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。4.2 调用常用的系统存储过程EXEC sp_databases-列出当前系统中的数据库EXEC sp_renamedb Northwind,Northwind1 -修改数据库的名称(单用户访问)USE stuDB -当前数据库中查询的对象的列表GOEXEC sp_tables -返回某个表列的信息EXEC sp_columns stuInfo -返回某个
4、表列的信息EXEC sp_help stuInfo -查看表stuInfo的信息EXEC sp_helpconstraint stuInfo -查看表 stuInfo 的约束EXEC sp_helpindex stuMarks -查看表 stuMarks 的索引EXEC sp_helptext view_stuInfo_stuMarks -查看视图的语句文本EXEC sp_stored_procedures -查看当前数据库中的存储过程4.3 调用常用的扩展存储过程：xp_cmdshell可以执行DOS命令下的一些的操作 ,以文本行方式返回任何输出 调用语法：EXEC xp_cmdshell
5、DOS命令 NO_OUTPUT【案例分析】创建数据库bankDB，要求保存在D:bankUSE masterGOEXEC xp_cmdshell mkdir d:bank, no_output -创建文件夹D:bankIF EXISTS(SELECT * FROM sysdatabases WHERE name=bankDB)DROP DATABASE bankDBGOCREATE DATABASE bankDB()GO -查看文件夹D:bankEXEC xp_cmdshell dir D:bank -查看文件5、创建存储过程5.1 定义存储过程的语法create procedure 存储过程
6、名参数 数据类型= 默认值 output,参数n 数据类型= 默认值outputASSQL语句GO和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值5.2 创建不带参数的存储过程【问题】请创建存储过程，查看本次考试平均分以及未通过考试的学员名单说明：笔试和机试都通过了60分才算通过。-创建不带参数的存储过程create procedure proc_stuASdeclare avgwri float,avglab floatselect avgwri=avg(笔试成绩),avglab=avg (上机成绩 ) from 成绩表 -笔试平均分和机试平均分变量print 笔试
7、成绩分数=+convert(varchar (10),avgwri)print 上机成绩分数=+convert(varchar (10),avglab)if avgwri70 and avglab70 -显示考试成绩的等级print 本班成绩：优秀elseprint 本班成绩：较差 -显示未通过的学员print -print 参加考试不及格的学生 select a.学生姓名,a.学号,b. 笔试成绩,b .上机成绩from 学生信息表as a inner join 成绩表 as b on a.学号= b.学号where b.笔试成绩=3 -后续语句引用返回结果print 未通过人数：+conv
8、ert( varchar(5),sum)+ 人, 超过%,及格分数线还应下调ELSEprint 未通过人数：+convert( varchar(5),sum)+ 人, 已控制在%以下，及格分数线适中GO强调：1.调用时也必须跟随关键字 OUTPUT，否则 SQL Server 将视为输入参数。5.7 处理存储过程中的错误可以使用 print 语句显示错误信息，但这 些信息是临时的，只能显示给用户 raiserror 显示用户定义的错误信息时可指定严重级别，设置系统变量ERROR记录所发生的错误等 5.7.1 使用 raiserror 语句raiserror 语句的用法如下： raiserror
9、 (msg_id | msg_str,severity,state WITH option,.n)msg_id：在 sysmessages 系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长 255 个字符severity：定义严重性级别。用户可使用的级别为 018 级state：表示错误的状态，1 至 127 之间的值option：指示是否将错误记录到服务器错误日志中 问题：完善上例，当用户调用存储过程时，传入的及格线参数不在之间时，将弹出错误警告，终止存储过程的执行。说明：笔试和机试都通过了 60 分才算通过。CREATE PROCEDURE proc_stu5notpa
10、ssSum int OUTPUT, -输出参数writtenPass int=60, -默认参数放后labPass int=60 -默认参数放后AS -错误处理IF (NOT writtenPass BETWEEN 0 AND 100) OR (NOT labPass BETWEEN 0 AND 100)BEGINraiserror (及格线错误，请指定之间的分数，统计中断退出,16 ,1)RETURN -立即返回，退出存储过程END其他语句同上例，略GO -引发系统错误，指定错误的严重级别，调用状态为（默认）, 并影响ERROR 系统变量的值/*-调用存储过程，测试RAISERROR 语句-
11、*/DECLARE sum int, t intEXEC proc_stu sum OUTPUT ,604 -笔试及格线误输入分SET t=ERROR print 错误号： +convert(varchar(5),t )IF t=3 -如果执行了RAISERROR语句，系统全局ERROR将不等于，表示出现了错误print 未通过人数：+convert( varchar(5),sum)+ 人, 超过%,及格分数线还应下调ELSEprint 未通过人数：+convert( varchar(5),sum)+ 人, 已控制在%以下，及格分数线适中GO6 用户自定义函数在 SQL Server 中，用户
12、不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。用户自定义函数可以在企业管理器中创建，也可以使用 create function 语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有 1024 参数。标量函数：返回单个数据值。表值函数：返回值是一个记录集合表。在此函数中，return 语句包含一条单独的 select 语句。 多语句表值函数：返回值是由选择的结果构成的记录集。6.1 使用 create function 语句创建用户自定义函数使用create function创建用户自定义函数，其语法格式
13、如下：create function owner_name. function_name( parameter_name AS scalar_parameter_data_type = default ,.n ) returns scalar_return_data_type as beginfunction_bodyretunrn scalar_expressionend function_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。 parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用
14、于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定“default“关键字才能获得默认值。 scalar_parameter_data_type：参数的数据类型。 scalar_return_data_type：是用户定义函数的返回值。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外） 。 function_body：位于 begin 和 end
15、之间的一系列 Transact-SQL 语句，其只用于标量函数和多语句表值函数。 scalar_expression：用户自定义函数中返回值的表达式。6.2 标量函数例：在 stuDB 库中创建一个用户自定义标量值函数 xuefen，该函数通过输入成绩来判断是否取得学分，当成绩大于等于 60 时，返回取得学分，否则，返回未取得学分。代码如下：USE stuDBGOCREATE FUNCTION xuefen(chengji int) RETURNS nvarchar(10) BEGIN declare returnsxuefen nvarchar(10)if chengji60set retu
16、rnsxuefen=取得学分elseset returnsxuefen=不能取得学分RETURN returnsxuefenENDGO使用刚才定义的xuefen函数来查看课程号为“”的课程，学生获得学分的情况。在查询编辑器中输入如下代码：USE stuDBGOSELECT 学号, 成绩=(笔试成绩+ 上机成绩)/2,dbo .xuefen(笔试成绩+上机成绩)/ 2) AS 学分情况FROM 成绩表 WHERE 课程号 =GO6.3 表值函数表值函数遵循的原则：RETURNS 子句仅包含关键字 table。不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格
17、式设置。FUNCTION BODY 不由 BEGIN 和 END 分隔。RETURN 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表。例：在 stuDB 库中创建一个内嵌表值函数 XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：CREATE FUNCTION XUESHENG(inputdep nvarchar(4) RETURNS table AS RETURN( SELECT 学号, 姓名 FROM 学生WHERE 所属院系=inputdep)GO建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：SELECT
18、 * FROM DBO.XUESHENG()GO6.4 多语句表值函数多语句函数的主体中允许使用以下语句：赋值语句DECLARE 语句，该语句定义函数局部的数据变量和游标。SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的FETCH 语句。INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。EXECUTE 语句调用扩展存储过程。6.5 多语句表值函数案例：在 s
19、tuDB 库中创建一个多语句表值函数 chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。代码如下：USE stuDBGOCREATE FUNCTION chengji( inputkm as char(20) )/*为 chengji 函数定义的表结构，名称变量为cji*/RETURNS cj TABLE(科目编号varchar (10),姓名varchar(10),成绩int )ASBEGININSERT cj /*该变量是上面定义的表名称变量*/select b.科目编号,a.学生姓名,( b.笔试成绩+ b.上机成绩 )/2 from 学生信息表as a inn
20、er join 成绩表as b on a.学号 =b.学号where b.科目编号= inputkm RETURNENDGO在查询编辑器中输入以下查询命令：SELECT * FROM dbo.chengji(9001)6.6 查看、修改和删除自定义函数1.查看用户自定义函数的属性在SQL Server中，根据不同需要，可以使用 sp_helptext、sp_help 等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下：使用sp_helptext查看用户定义函数的文本信息，其语法格式为：sp_helptext 用户自定义函数名使用sp_help查看用户自定义函数的
21、一般信息，其语法格式为：sp_help 用户自定义函数名例：使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下：USE STUDBGOSP_HELPTEXT XUEFENGO2.修改用户自定义函数的属性使用SQL命令修改用户自定义函数，使用 ALTER FUNCTION 命令可以修改用户自定义函数。修改由CREATE FUNCTION 语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下：ALTER FUNCTION owner_name. function_name( parameter_name A
22、S scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type AS BEGINfunction_bodyRETURN scalar_expressionEND其中的参数与建立用户自定义函数中的参数意义相同。3.使用T- SQL命令删除用户自定义函数使用DROP 命令可以一次删除多个用户自定义函数，其语法格式为：DROP FUNCTION 所有者名称.函数名称,n案例：删除在student库上建立的xuefen函数。代码如下：USE studentGODROP FUNCTION dbo.xuefenGO总结存储过程是一组预编译的 SQL 语句，它可以包含数据操纵语句、变量、逻辑控制语句等 存储过程允许带参数，参数分为：输入参数输出参数其中，输入参数可以有默认值。 输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值 输出参数从存储过程中返回（输出）值，后面跟随 OUTPUT 关键字 RAISERROR 语句用来向用户报告错误 用户自定义函数：标量函数、表值函数、多语句表值函数
