1、第第9 9章章 存储过程与触发器存储过程与触发器u本章导读本章导读本章介绍了数据库中两个重要的可编程对象存储过程和触发器，并结合实本章介绍了数据库中两个重要的可编程对象存储过程和触发器，并结合实例讲解了在例讲解了在SQL ServerSQL Server中如何创建、修改和管理存储过程和触发器。中如何创建、修改和管理存储过程和触发器。u学习目的与要求学习目的与要求（1 1）掌握在）掌握在SQL ServerSQL Server设计和管理存储过程的方法设计和管理存储过程的方法 .（2 2）掌握在掌握在SQL ServerSQL Server设计和管理触发器的方法设计和管理触发器的方法9.1 设计和
2、管理存储过程设计和管理存储过程存储过程是独立于数据表之外的数据库对象，是存储过程是独立于数据表之外的数据库对象，是SQL Server服务器上一组预编译的服务器上一组预编译的Transact-SQL语句，用于完成语句，用于完成某项任务，它们可以接受参数、输出参数、返回单个或多某项任务，它们可以接受参数、输出参数、返回单个或多个结果集、返回状态值和参数值。存储过程独立于程序源个结果集、返回状态值和参数值。存储过程独立于程序源代码，可单独修改，可以被调用任意次，可以引用其他存代码，可单独修改，可以被调用任意次，可以引用其他存储过程。储过程。也就是说，当用户对数据进行操作时，需要采取一定也就是说，当
3、用户对数据进行操作时，需要采取一定的动作来完成设计的功能或者保证数据的完整性与一致性的动作来完成设计的功能或者保证数据的完整性与一致性。在这里，可以借助存储过程完成数据行为。将数据库设。在这里，可以借助存储过程完成数据行为。将数据库设计过程转化为数据设计与数据行为设计的结合。存储过程计过程转化为数据设计与数据行为设计的结合。存储过程使数据管理变得更容易使数据管理变得更容易。9.1.1存储过程概述存储过程概述9.1 设计和管理存储过程设计和管理存储过程1、存储过程的类型、存储过程的类型（1）系统存储过程（2）用户定义存储过程（3）临时存储过程（4）扩展存储过程2、存储过程的主要优点、存储过程的主
4、要优点（1）模块化编程。（2）快速执行（3）减少网络通信量（4）提供安全机制 9.1.1存储过程概述存储过程概述9.1.2创建存储过程创建存储过程1 1格式格式CREATE PROCEDURE CREATE PROCEDURE 架构名称架构名称.存储过程名存储过程名 parameter parameter 数据类型数据类型=default -=default -设置默认值。设置默认值。OUTPUT -OUTPUT -说明说明parameterparameter定义的定义的存储过程参数为一返回值。存储过程参数为一返回值。,.n,.nWITH encryption|recompile -WITH
5、encryption|recompile -对存储过程对存储过程文本进行加密。文本进行加密。FOR REPLICATIONFOR REPLICATIONAS AS SQL 2 2存储过程的各选项设置规则存储过程的各选项设置规则1 1）parameter parameter是过程中的参数。在是过程中的参数。在 CREATE CREATE PROCEDURE PROCEDURE 语句中可以声明一个或多个参数。语句中可以声明一个或多个参数。2 2）如果定义了）如果定义了 default default 值，则无需指定此参数的值值，则无需指定此参数的值即可执行过程。默认值必须是常量或即可执行过程。默认
6、值必须是常量或 NULLNULL3 3）OUTPUTOUTPUT选项指示参数是输出参数。选项指示参数是输出参数。4 4）如果创建存储过程时，使用）如果创建存储过程时，使用 WITH encryptionWITH encryption子子句，过程定义将以不可读的形式存储。句，过程定义将以不可读的形式存储。5 5）FOR REPLICATION FOR REPLICATION 指定不能在订阅服务器上执行指定不能在订阅服务器上执行为复制创建的存储过程。为复制创建的存储过程。6 6）SQL 指定过程要执行的操作指定过程要执行的操作7 7）可以在存储过程内引用临时表。）可以在存储过程内引用临时表。9.1
7、.2创建存储过程创建存储过程【例例9.19.1】创建一个存储过程创建一个存储过程proc_student1proc_student1用于显示学号用于显示学号为为“J0402J0402”的学生基本信息（包括学生学号、姓名、性别、的学生基本信息（包括学生学号、姓名、性别、系）。系）。CREATE PROCEDURE studentcourse.proc_student1 CREATE PROCEDURE studentcourse.proc_student1 ASAS SELECT SELECT 学号学号,姓名姓名,性别性别,系系 FROM sFROM s WHERE WHERE 学号学号=J04
8、02J0402GOGO9.1.2创建存储过程创建存储过程1.1.格式格式 EXEC|EXECUTE EXEC|EXECUTE 返回状态返回状态=schema_name.=schema_name.存储过程名称存储过程名称 形参形参=value|=value|变量变量 OUTPUT|DEFAULT OUTPUT|DEFAULT ,.n ,.n WITH RECOMPILE WITH RECOMPILE 2.2.功能。功能。“返回状态返回状态”是保存存储过程的返回状态。是保存存储过程的返回状态。“形参形参”是在定是在定义存储过程时，定义的参数。在采用义存储过程时，定义的参数。在采用“形参形参=val
9、ue=value”格式时，参数格式时，参数名称和常量不必按在存储过程中定义的顺序提供。但是，如果任何参名称和常量不必按在存储过程中定义的顺序提供。但是，如果任何参数使用了数使用了“形参形参=value=value”格式，则对后续的所有参数均必须使用该格式，则对后续的所有参数均必须使用该格式。格式。“valuevalue”是传递给存储过程的参数值。如果参数名称没有指定是传递给存储过程的参数值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供。，参数值必须以在存储过程中定义的顺序提供。9.1.3执行存储过程执行存储过程【例例9.29.2】现在我们就来执行刚才创建好的存储过程现在我们就来
10、执行刚才创建好的存储过程proc_student1proc_student1，显示学号为，显示学号为“J0402J0402”的学生基本信息（包的学生基本信息（包括学生学号、姓名、性别、系）。括学生学号、姓名、性别、系）。方法一：在方法一：在SQLServer Management StudioSQLServer Management Studio中执行存储过中执行存储过程程方法二：使用命令方法二：使用命令在新建的查询窗口输入命令：在新建的查询窗口输入命令：Execute proc_student1Execute proc_student19.1.3执行存储过程执行存储过程存储过程和调用者之间通
11、过参数交换数据，可以按输入的存储过程和调用者之间通过参数交换数据，可以按输入的参数执行，调用者也可以通过存储过程返回的状态值对存储参数执行，调用者也可以通过存储过程返回的状态值对存储过程进行管理。过程进行管理。1.1.参数参数存储过程的参数在创建时声明，存储过程的参数在创建时声明，SQL ServerSQL Server支持两种参数支持两种参数：输入参数和输出参数。：输入参数和输出参数。1 1）输入参数）输入参数 输入参数允许调用程序为存储过程传送数据值。要定义输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须在存储过程的输入参数，必须在CREATE PROCEDUREC
12、REATE PROCEDURE语句中声明语句中声明一个或多个变量及类型。在执行存储过程时，可以为输入参一个或多个变量及类型。在执行存储过程时，可以为输入参数传递参数值，或使用默认值。数传递参数值，或使用默认值。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.39.3】创建一个有输入参数的存储过程创建一个有输入参数的存储过程proc_student2proc_student2用于显示指定学号的学生的基本信息用于显示指定学号的学生的基本信息(包括学生学号、姓名、包括学生学号、姓名、性别、系性别、系)。执行该存储过程显示学号为。执行该存储过程显示学号为J0404J0404的学生的信
13、息。的学生的信息。命令代码如下。命令代码如下。CREATE PROCEDURE proc_student2CREATE PROCEDURE proc_student2 num char(6)-num char(6)-输入参数输入参数numnumASASBEGINBEGIN SELECT SELECT 学号学号,姓名姓名,性别性别,系系 FROM SFROM S WHERE WHERE 学号学号=num=numENDENDGOGO9.1.4 存储过程的参数和状态值存储过程的参数和状态值执行存储过程命令如下。执行存储过程命令如下。EXEC proc_student2 num=J0404EXEC p
14、roc_student2 num=J0404-在执行过在执行过程中变量可以显式命名程中变量可以显式命名说明：说明：(1)(1)我们定义了输入参数我们定义了输入参数numnum，来接收学生的学号，输入参，来接收学生的学号，输入参数数numnum的数据类型必须与学生信息表的数据类型必须与学生信息表S S中学生的学号类型一致中学生的学号类型一致。(2)(2)在执行存储过程时，必须提供输入参数的值，输入参数在执行存储过程时，必须提供输入参数的值，输入参数值可以是常量、变量或表达式，但数据类型必须与定义存储过值可以是常量、变量或表达式，但数据类型必须与定义存储过程时给出的输入参数一致。程时给出的输入参数
15、一致。9.1.4 存储过程的参数和状态值存储过程的参数和状态值9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.4】创建存储过程创建存储过程s_info。根据学生姓名和学号。根据学生姓名和学号查询学生的电话、所在系。查询学生的电话、所在系。USE studentcourseIF EXISTS(SELECT name FROM sysobjects WHERe name=s_info and type=p)-如果已经存在如果已经存在s_info存储存储过程，则删除它过程，则删除它 DROP PROCEDURE s_info -删除存储过程删除存储过程s_infoGOCREATE
16、PROC s_info -创建存储过程创建存储过程s_infostname varchar(8),stsno varchar(10)-多个输入参数之间用多个输入参数之间用“,”分分隔隔AS SELECT 姓名姓名,学号学号,电话电话,系系FROM S WHERE 姓名姓名=stname AND 学号学号=stsnoGO9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.4】创建存储过程创建存储过程s_info。根据学生姓名和学号。根据学生姓名和学号查询学生的电话、所在系。查询学生的电话、所在系。EXECUTE s_info 李丽李丽,J0401 -执行存储过程执行存储过程s_in
17、foGO运行结果如下。运行结果如下。姓名姓名 课程号课程号 电话电话 系系李丽李丽 J0401 931-1234 管理信息系管理信息系9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.5】创建存储过程创建存储过程s_default，根据学生姓名和学，根据学生姓名和学号查询学生的电话、所在系。如果未提供学生姓名和号查询学生的电话、所在系。如果未提供学生姓名和学号，该存储过程将显示学号为学号，该存储过程将显示学号为J0401，姓名为李丽，姓名为李丽的学生信息。的学生信息。CREATE PROC s_default stname varchar(8)=李丽李丽,stsno varch
18、ar(10)=J0401 -定义输入参数的同时，定义输入参数的同时，设置参数的默认值设置参数的默认值AS SELECT 姓名姓名,学号学号,电话电话,系系 FROM SWHERE 姓名姓名=stname AND 学号学号=stsnoGO9.1.4 存储过程的参数和状态值存储过程的参数和状态值执行存储过程执行存储过程s_defaultEXECUTE s_default -执行存储过程时，没有执行存储过程时，没有提供传递参数。提供传递参数。运行结果如下。运行结果如下。姓名姓名 学号学号 电话电话 所在系所在系李丽李丽 J0401 931-1234 管理信息系管理信息系 参数默认值可以是参数默认值可
19、以是 NULL 值。在这种情况下，如值。在这种情况下，如果未提供参数，果未提供参数，SQL Server 将根据存储过程的其他语将根据存储过程的其他语句执行存储过程，不会显示错误信息。句执行存储过程，不会显示错误信息。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.6】创建存储过程创建存储过程s_nul，根据学生姓名和学号，根据学生姓名和学号查询学生选修的课程。如果未提供学生姓名和学号，查询学生选修的课程。如果未提供学生姓名和学号，则显示提示信息则显示提示信息“请输入学号和姓名！请输入学号和姓名！”。CREATE PROC s_null stname varchar(8)=N
20、ULL,stsno varchar(10)=NULL -设置默认值为空值设置默认值为空值AS IF stname IS NULL OR stsno IS NULL -如果未提如果未提供参数供参数,则显示提示信息则显示提示信息 SELECT 请输入学号和姓名！请输入学号和姓名！ELSE SELECT 姓名姓名,SC.学号学号,SC.课程号课程号,C.课程名课程名 FROM SC,S,C WHERE S.姓名姓名=stname AND S.学号学号=stsno and S.学号学号=SC.学号学号 and SC.课程号课程号=C.课程号课程号GO9.1.4 存储过程的参数和状态值存储过程的参数和状
21、态值【例例9.6】创建存储过程创建存储过程s_nul，根据学生姓名和学号，根据学生姓名和学号查询学生选修的课程。如果未提供学生姓名和学号，查询学生选修的课程。如果未提供学生姓名和学号，则显示提示信息则显示提示信息“请输入学号和姓名！请输入学号和姓名！”。执行存储过程执行存储过程s_ null。执行执行1：执行时没有带参数：执行时没有带参数EXEC s_null 运行结果如下。运行结果如下。请输入学号和姓名！请输入学号和姓名！9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.6】创建存储过程创建存储过程s_nul，根据学生姓名和学号，根据学生姓名和学号查询学生选修的课程。如果未提
22、供学生姓名和学号，查询学生选修的课程。如果未提供学生姓名和学号，则显示提示信息则显示提示信息“请输入学号和姓名！请输入学号和姓名！”。执行执行2：执行时提供参数，由于没有采用显式命名方：执行时提供参数，由于没有采用显式命名方式，参数的顺序必须与定义时的顺序一致。式，参数的顺序必须与定义时的顺序一致。EXEC s_null 李丽李丽,J0401 运行结果如下。运行结果如下。姓名姓名 学号学号 课程号课程号课程名课程名李丽李丽 J0401 C01 数据库数据库李丽李丽 J0401 C02 C语言语言李丽李丽 J0401 C03 数据结构数据结构李丽李丽 J0401 C04 计算机应用基础计算机应用
23、基础李丽李丽 J0401 C05 网络技术网络技术9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.6】创建存储过程创建存储过程s_nul，根据学生姓名和学号，根据学生姓名和学号查询学生选修的课程。如果未提供学生姓名和学号，查询学生选修的课程。如果未提供学生姓名和学号，则显示提示信息则显示提示信息“请输入学号和姓名！请输入学号和姓名！”。若执行以下语句：若执行以下语句：EXEC s_null J0401,李丽李丽 上述执行命令将上述执行命令将“J0401”赋值给参数赋值给参数stname，“李丽李丽”赋值给参数赋值给参数stsno，这直接导致找不到满足条，这直接导致找不到满足条
24、件的记录行。件的记录行。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.6】创建存储过程创建存储过程s_nul，根据学生姓名和学号，根据学生姓名和学号查询学生选修的课程。如果未提供学生姓名和学号，查询学生选修的课程。如果未提供学生姓名和学号，则显示提示信息则显示提示信息“请输入学号和姓名！请输入学号和姓名！”。执行执行3：以显式命名方式执行存储过程：以显式命名方式执行存储过程s_null，此时参，此时参数的赋值顺序任意。数的赋值顺序任意。EXEC s_null stname=李丽李丽,stsno=J0401 EXEC s_null stsno=J0401,stname=李丽李
25、丽运行结果同执行方式运行结果同执行方式2。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.7】创建存储过程创建存储过程s_like，根据姓名，查询学生，根据姓名，查询学生的平均成绩。如果执行时，没有带参数，则显示陈的平均成绩。如果执行时，没有带参数，则显示陈姓学生的平均成绩。姓学生的平均成绩。CREATE PROC s_likestname varchar(8)=陈陈%-设置默认值带通配符设置默认值带通配符ASSELECT 姓名姓名=姓名姓名,平均成绩平均成绩=avg(SC.成绩成绩)FROM SC,SWHERE S.学号学号=SC.学号学号 AND S.姓名姓名 LIKE
26、stnameGROUP BY S.学号学号,姓名姓名ORDER BY S.学号学号,姓名姓名GOEXECUTE s_like GO9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.8】创建存储过程创建存储过程s_count，根据课程名，检，根据课程名，检索选修某门课程的学生总人数。索选修某门课程的学生总人数。CREATE PROC s_countctname varchar(30)=NULL -设置默认值为空值设置默认值为空值AS IF ctname IS NULL PRINT 请输入课程名！请输入课程名！ELSES E L E C T 课 程 名课 程 名=课 程 名课 程
27、名,学 生 选 修 人 数学 生 选 修 人 数=COUNT(DISTINCT 学号学号)FROM SC,CWHERE C.课程号课程号=SC.课程号课程号 AND C.课程名课程名=ctnameGROUP BY 课程名课程名ORDER BY 课程名课程名GO9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.8】创建存储过程创建存储过程s_count，根据课程名，检，根据课程名，检索选修某门课程的学生总人数。索选修某门课程的学生总人数。执行存储过程。执行存储过程。执行一：执行一：EXECUTE s_count 数据结构数据结构或或EXECUTE s_count ctname=数
28、据结构数据结构GO执行二：执行二：EXECUTE s_count ctname=default或或EXECUTE s_count9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.9】创建存储过程创建存储过程PRscore，根据课程号查询，根据课程号查询选修这门课程的学生成绩。选修这门课程的学生成绩。CREATE PROCEDURE PRscore ids char(3)-设置输入参数设置输入参数idsASSELECT C.课程号课程号,C.课程名课程名,SC.学号学号,S.姓名姓名,SC.成绩成绩FROM C INNER JOIN SC ON C.课程号课程号=SC.课程号课程
29、号INNER JOIN S ON SC.学号学号=S.学号学号where C.课程号课程号=ids执行存储过程：执行存储过程：exec PRscore ids=C01或或exec PRscore C019.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.10】创建存储过程创建存储过程PRidscore，根据学号查询学，根据学号查询学生各科的成绩。生各科的成绩。CREATE PROCEDURE PRidscoreidstudent char(6)-设置输入参数设置输入参数idstudentASSELECT SC.学号学号,S.姓名姓名,C.课程名课程名,SC.成绩成绩,C.学分学分
30、FROM C INNER JOIN SC ON C.课程号课程号=SC.课程号课程号INNER JOIN S ON SC.学号学号=S.学号学号where S.学号学号=idstudent 执行存储过程：执行存储过程：exec PRidscore J0401或或exec PRidscore idstudent=J04012 2）输出参数）输出参数 输出参数允许存储过程将数据值返回给调用程序。输出参数允许存储过程将数据值返回给调用程序。OUTPUTOUTPUT关键字用来指出输出参数。关键字用来指出输出参数。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.119.11】创建一个带
31、输出参数的存储过程创建一个带输出参数的存储过程proc_student3proc_student3，用于显示指定学号的学生各门课程的平均成绩，执行存储过程，用于显示指定学号的学生各门课程的平均成绩，执行存储过程，返回学号为返回学号为J0401J0401的学生的平均成绩。的学生的平均成绩。CREATE PROCEDURE proc_student3CREATE PROCEDURE proc_student3num char(6),-num char(6),-设置输入参数设置输入参数numnumsavg smallint output -savg smallint output -使用使用OUTP
32、UTOUTPUT关关键字指定键字指定 savg savg为输出参数为输出参数ASASBEGINBEGINSELECT savg=avg(SELECT savg=avg(成绩成绩)FROM S JOIN SC ON S.FROM S JOIN SC ON S.学号学号=SC.=SC.学号学号 WHERE S.WHERE S.学号学号=num=numENDENDGOGO9.1.4 存储过程的参数和状态值存储过程的参数和状态值9.1.4 存储过程的参数和状态值存储过程的参数和状态值执行存储过程：执行存储过程：DECLAREsavg_value smallint -定义局部变量定义局部变量savg_v
33、alue存放输出参数的值存放输出参数的值E X E C p r o c _ s t u d e n t 3 n u m=J 0 4 0 1 ,savg=savg_value OUTPUT或或EXEC proc_student3 J0401,savg_value OUTPUTSelect savg_value as 平均成绩平均成绩说明：说明：(1)该例中创建存储过程时，除了需要输入参数，还该例中创建存储过程时，除了需要输入参数，还需要把学生的平均成绩返回给调用程序，所以还要需要把学生的平均成绩返回给调用程序，所以还要定义一个输出参数定义一个输出参数savg，用，用OUTPUT选项指定，数选项指
34、定，数据类型必须和学生选课表的成绩一致或兼容。据类型必须和学生选课表的成绩一致或兼容。(2)在执行存储过程时，必须定义一个局部变量存放在执行存储过程时，必须定义一个局部变量存放输出参数的值，并且用输出参数的值，并且用OUTPUT选项指出。选项指出。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。CREATE PROC sgsn varchar(8)=J0401,cn varchar(3)=C02,gr smallint outp
35、ut -output变量必须在定义存变量必须在定义存储过程时进行定义储过程时进行定义AS SELECT 学号学号,课程号课程号,成绩成绩FROM SCWHERE SC.学号学号=sn and SC.课程号课程号=cnSELECT gr=成绩成绩 -向变量赋值的向变量赋值的 SELECT 语句语句FROM SCWHERE SC.学号学号=sn and SC.课程号课程号=cnGO9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。注意
36、下述写法是错误的。注意下述写法是错误的。SELECT 学号学号,课程号课程号,gr=成绩成绩FROM SCWHERE SC.学号学号=sn and SC.课程号课程号=cn因为，向变量赋值的因为，向变量赋值的 SELECT 语句不能与数据检索操语句不能与数据检索操作结合使用。作结合使用。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。存储过程可以以多种组合方式执行。存储过程可以以多种组合方式执行。执行一：执行一：DECLARE
37、g smallint -定义局部变量定义局部变量g存放存放输出参数的值输出参数的值EXECUTE sg sn=J0402,gr=g output select g as 成绩成绩运行结果如下。运行结果如下。学号学号 课程号课程号 成绩成绩J0402 C02 859.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。执行二：执行二：DECLARE g1 smallintEXECUTE sg sn=DEFAULT,gr=g1运行结果如下。
38、运行结果如下。学号学号 课程号课程号 成绩成绩J0401 C02 939.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。执行三：执行三：DECLARE g1 smallintEXECUTE sg gr=g1 运行结果如下。运行结果如下。学号学号 课程号课程号 成绩成绩J0401 C02 939.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程
39、号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。执行四：执行四：DECLARE g1 smallintEXECUTE sg cn=default,gr=g1 output运行结果如下。运行结果如下。学号学号 课程号课程号 成绩成绩J0401 C02 939.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。执行五：执行五：DECLARE g1 smallintEXECUTE sg cn=C03,gr=g1 o
40、utput运行结果如下。运行结果如下。学号学号 课程号课程号 成绩成绩J0401 C03 999.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。执行六：执行六：DECLARE g1 smallintEXECUTE sg sn=default,cn=C03,gr=g1 output运行结果如下。运行结果如下。学号学号 课程号课程号 成绩成绩J0401 C03 999.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.12
41、】创建存储过程创建存储过程sg，根据输入的学号和课，根据输入的学号和课程号，获得指定学号和课程号的课程成绩。程号，获得指定学号和课程号的课程成绩。执行七：执行七：DECLARE g1 smallintEXECUTE sg cn=C03,sn=J0402,gr=g1 output运行结果如下。运行结果如下。学号学号 课程号课程号 成绩成绩J0402 C03 779.1.4 存储过程的参数和状态值存储过程的参数和状态值2返回状态值返回状态值 存储过程可以返回整型状态值，表示过程是否存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。如果存储过程没成功执行，或者过程失败的原因。如果
42、存储过程没有显式设置返回代码的值，有显式设置返回代码的值，SQL Server默认返回代码默认返回代码为为0，表示成功执行；若返回，表示成功执行；若返回-1到到-99之间的整数，表之间的整数，表示没有成功执行。也可以使用示没有成功执行。也可以使用RETURN语句，用大于语句，用大于0或小于或小于-99的整数来定义自己的返回状态值，以表示的整数来定义自己的返回状态值，以表示不同的执行结果。在执行存储过程时，要定义一个不同的执行结果。在执行存储过程时，要定义一个变量来接收返回的状态值。变量来接收返回的状态值。1)RETURN语句格式语句格式RETURN 返回整型值的表达式返回整型值的表达式2)功能
43、功能RETURN 语句将无条件地从过程、批处理或语句块中语句将无条件地从过程、批处理或语句块中退出。返回整型值。退出。返回整型值。9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.13】检索相应学生信息，如果在执行检索相应学生信息，如果在执行 findstudent 时没有给出学生姓名参数，时没有给出学生姓名参数，RETURN语句语句将一条消息发送给用户，然后从过程中退出。将一条消息发送给用户，然后从过程中退出。CREATE PROCEDURE findstudentnm char(8)=NULLAS IF nm IS NULL BEGIN PRINT You must giv
44、e a student name RETURN ENDELSE BEGIN SELECT S.姓名姓名,S.学号学号,S.性别性别 FROM S WHERE S.姓名姓名=nm END9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.13】检索相应学生信息，如果在执行检索相应学生信息，如果在执行 findstudent 时没有给出学生姓名参数，时没有给出学生姓名参数，RETURN语句语句将一条消息发送给用户，然后从过程中退出。将一条消息发送给用户，然后从过程中退出。执行存储过程。执行存储过程。EXEC findstudent运行结果如下。运行结果如下。You must give
45、 a student name9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.14】创建存储过程创建存储过程checkstate，查询指定课，查询指定课程的最高成绩，如果最高成绩大于程的最高成绩，如果最高成绩大于90分，则返回分，则返回状态代码状态代码 1。否则，返回状态代码。否则，返回状态代码0。CREATE PROCEDURE checkstateparacno varchar(3)ASIF(SELECT max(成绩成绩)FROM sc WHERE 课程号课程号=paracno)90return 1else return 09.1.4 存储过程的参数和状态值存储过程的参
46、数和状态值【例例9.14】创建存储过程创建存储过程checkstate，查询指定课，查询指定课程的最高成绩，如果最高成绩大于程的最高成绩，如果最高成绩大于90分，则返回分，则返回状态代码状态代码 1。否则，返回状态代码。否则，返回状态代码0。执行存储过程。执行存储过程。DECLARE return_status intEXEC return_status=checkstate paracno=C03SELECT Return Status=return_status运行结果如下。运行结果如下。return Status 19.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.15】
47、创建带有返回值的存储过程创建带有返回值的存储过程proc_student4，用于显示，用于显示指定学号的学生各门课程的平均成绩。在存储过程中，用返回指定学号的学生各门课程的平均成绩。在存储过程中，用返回值值1表示用户没有提供输入参数，否则返回值为表示用户没有提供输入参数，否则返回值为2。CREATE PROCEDURE proc_student4num char(6)=NULL,savg smallint outputASBEGINIF num is NULL return 1else BEGIN SELECT savg=avg(成绩成绩)FROM SC WHERE SC.学号学号=num r
48、eturn 2 ENDEND9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.15】创建带有返回值的存储过程创建带有返回值的存储过程proc_student4，用于显示，用于显示指定学号的学生各门课程的平均成绩。在存储过程中，用返回指定学号的学生各门课程的平均成绩。在存储过程中，用返回值值1表示用户没有提供输入参数，否则返回值为表示用户没有提供输入参数，否则返回值为2。执行存储过程。执行存储过程。执行一：不为参数执行一：不为参数num提供学号，默认值为提供学号，默认值为null。DECLAREsavg smallint,return_value intEXEC return_v
49、alue=proc_student4savg=savg OUTPUTSELECT return_value as 返回值返回值,savg as 平均成绩平均成绩运行结果如下。运行结果如下。返回值返回值 平均成绩平均成绩1 NULL9.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.15】创建带有返回值的存储过程创建带有返回值的存储过程proc_student4，用于显示，用于显示指定学号的学生各门课程的平均成绩。在存储过程中，用返回指定学号的学生各门课程的平均成绩。在存储过程中，用返回值值1表示用户没有提供输入参数，否则返回值为表示用户没有提供输入参数，否则返回值为2。执行二：为
50、参数执行二：为参数num提供学号。提供学号。DECLAREsavg smallint,return_value intEXEC return_value=proc_student4 savg=savg OUTPUT,num=J0401SELECT return_value as 返回值返回值,savg as 平均成绩平均成绩运行结果如下。运行结果如下。返回值返回值 平均成绩平均成绩2 919.1.4 存储过程的参数和状态值存储过程的参数和状态值【例例9.16】创建存储过程创建存储过程update_S_1，修改指定学，修改指定学号的数据信息。号的数据信息。CREATE PROCEDURE upd
