《SQL Server数据库应用技术实例教程》课件任务七

任务：了解T-SQL的变量和运算符。学习存储过程的定义及其特点，创建存储过程的方法，如何执行存储过程，查看、修改、删除存储过程。

7.1认识T-SQL的变量和运算7.2认识存储过程7.3创建存储过程7.3创建存储过程7.4管理存储过程7.5知识进阶项目实训任务七提高数据操作速度

__在SQLServer2005中创建和应用存储过程了解T-SQL的变量和运算符了解什么是存储过程及其特点掌握创建存储过程的方法掌握如何执行存储过程学会查看、修改、删除存储过程学习目标创建和应用存储过程7.1认识T-SQL的变量和运算任务：定义、使用T-SQL的局部变量，了解T-SQL的运算符。7.1.1T-SQL的变量7.1.2T-SQL的运算符创建和应用存储过程T-SQL语言的变量分为局部变量和全局变量。1．局部变量局部变量使用DECLARE语句声明，在声明时它的默认值为NULL，使用SET语句为其赋值。局部变量仅生存于声明它批中，如存储过程。处理结束后，局部变量中的信息将丢失。SQLServer使用SELECT语句或PRINT语句来将变量的值提交给用户，而存储过程则将变量作为一个Output参数返回。基本语法如下：

DECLARE@variable_namedatatype[，@variable_namedatatype][，…n]7.1.1T-SQL的变量其中：

@variable_name局部变量的名称。以@开始，必须遵循SQL标识符和对象的命名规范，且名字不能使用保留字。

@datatype指定局部变量的数据类型。该数据类型可以是系统数据类型或用户自定义数据类型。使用SET语句给局部变量赋值：SET@variable_name=expression[,@variable_name=expression]…创建和应用存储过程【例7-1】定义整型局部变量和一个字符型局部变量并赋值。代码如下：DECLARE@variable_intint,@variable_charchar(15)SET@variable_int=60SELECT@variable_char='HELLOCHINA'SELECT@variable_intSELECT@variable_char如果被赋值的字符串长度超过DECLARE语句中定义的长度，赋值时超出的部分将被截去。7.1.1T-SQL的变量创建和应用存储过程2．全局变量全局变量是SQLServer系统所提供并赋值的变量。全局变量的名字以@@开头。大多数全局变量的值是报告用户当前会话信息。表7-1给出了SQLServer的常见全局变量。可以使用SELECT显示局部变量和全局变量的值（必须首先定义变量）。基本语法如下：显示局部变量：SELECT@variable_name

显示全局变量：SELECT@@variable_name7.1.1T-SQL的变量创建和应用存储过程在SQLServer2005中有一元运算符、赋值运算符、算术运算符、比较运算符、连接运算符、逻辑运算符等。1.一元运算符包括3种：＋（正）、－（负）和～（按位取反）。其中～（按位取反）只可用于整型数据类型的表达式。2.赋值运算符赋值运算符，即等号（=），主要用于为变量赋值。3.算数运算符包括：加（＋）、减（－）、乘（×）、除（÷）和取模（℅）。可以操作的数据类型是int、smallint、tinyint、float、real、money或者smallmoney；加和减运算符可用于对datetime及smalldatetime值执行算数运算；取模（℅）运算操作数的数据类型是int、smallint、tinyint。7.1.2T-SQL的运算符创建和应用存储过程4.比较运算符包含=、＞、＞＝、＜、＜＝、＜＞（不等于）、()（优先级控制符）。可用于除了text、ntext或image数据类型以外的所有的表达式。比较运算符的结果为布尔数据类型，包含true、false两种值。5.连接运算符连接运算符（＋）用于两个字符串的连接。字符串连接运算符用来连接char、varchar和text数据类型。6.逻辑运算符包括：AND（与）、OR（或）、NOT（非）逻辑运算符也称为位运算符。使用逻辑运算符可以把多个条件合并起来，逻辑运算返回的数据类型为布尔型。7.1.2T-SQL的运算符创建和应用存储过程7.1.2T-SQL的运算符

（）（括号）～（按位取反）正、负乘、除、取模：*、/、%加、连接符、减比较运算符：=、>、<、>=、<=、<>NOT(非)AND(与)OR(或)=（赋值）高低SQLServer中各种运算符的优先级顺序创建和应用存储过程

7.2认识存储过程任务：了解存储过程的定义，分析“高职院校教学管理系统”的存储过程需求。7.2.1存储过程的概念及特点7.2.2分析“高职院校教学管理系统”的存储过程需求创建和应用存储过程7.2.1存储过程的概念及特点1.什么是存储过程存储过程是一组预先编译好的、能实现特定数据操作功能的SQL代码集。它与特定数据库相关联，一起被存储在SQLServer服务器上。客户端通过应用程序调用执行，可以象使用函数一样重复使用这此代码集，实现所定义的操作。存储过程分为以下3类：⑴系统存储过程安装SQLServer时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。⑵用户自定义存储过程用户在用户数据库中创建的存储过程。⑶扩展存储过程是SQLServer可以动态装载并执行的动态链接库（DLL），其前缀为xp_，它允许用户使用DLL访问SQLServer。用户使用其他编程语言（如C或C++等）编写。创建和应用存储过程7.2.1存储过程的概念及特点2．存储过程的特点存储过程需要先编译后运行，而再次调用该存储过程时，不需再次编译。存储过程能够接受参数、嵌套调用另一存储过程、返回状态值和调用结果，还可以被远程数据库服务器执行。有以下特点：⑴能极大增强SQL语句的功能、效率和灵活性⑵更好地保证数据的完整性和安全性⑶能降低网络的通信流量⑷能提高应用的开发效率和系统的可维护性创建和应用存储过程7.2.2分析“高职院校教学管理系统”的存储过程需求⑴存储、检索、维护学生的信息，如查询有关学生的基本信息、在学生信息表中增加记录、查询有关学生的学习成绩，平均成绩及总成绩。⑵存储、检索、维护教师的信息，如查询有关教师的信息、在教师表中增加或删除教师信息。⑶存储、检索、维护班级信息，如查询某个班的平均成绩，查询某门课程的不及格人数。⑷存储、检索、维护课程的信息，如查询某门课程由哪个教师任教，查询该课程的平均成绩。创建和应用存储过程7.3创建存储过程任务：创建带输入、输出参数的存储过程，应用输入参数带入查询条件，输出参数带出统计结果。

7.3.1创建和执行存储过程的步骤7.3.2创建和调用不带参数的存储过程7.3.3创建和调用带参数的存储过程创建和应用存储过程7.3.1创建和执行存储过程的步骤用户自定义存储过程应遵循以下原则：存储过程的名称不能使用sp_作前缀。不要为只运行一次的T-SQL命令组构建存储过程。1．创建存储过程的模板代码可使用SSMS创建存储过程，操作步骤略。2．创建存储过程的CREATEPROCEDURE语句基本语法如下：CREATEPROC[EDURE]procedure_name[(@parameterdata_type[VARYING][=default][OUTPUT])][,...n][WITH]RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASsql_statment其中：

procedure_name指定存储过程名称。

@parameter存储过程的输入或输出参数。可以声明一个或多个参数，分为输入参数和输出参数。输入参数负责向存储过程带入值，输出参数将存储过程的返回值传递给变量，向客户端输出。

OUTPUT保留字，指定参数为输出参数，其定义位于所有输入参数之后。返回值是存储过程执行完成时参数的当前值。Default为参数指定默认值。WITHRECOMPILE重编译选项，表明SQLServer不会保存该存储过程的执行计划，要求每次执行存储过程都要重新编译和优化，并创建新的查询计划。ENCRYPTION加密选项。AS指定该存储过程要执行的操作。通过使用(@)符号作为第一个字符来指定参数名，必须符合有关标识符的规则。一个存储过程最多可定义2,100个参数创建和应用存储过程使用T-SQL最常用的执行存储过程的方法，是使用EXECUTE语句（系统存储过程可不使用EXECUTE关键字）。基本语法如下：[[EXEC[UTE]]procedure_name[value_list]|[@parameter_vari1bleOUTPUT][,...n][withrecompile]其中：

①procedure_name执行的存储过程名。②value_list输入参数要带入存储过程的参数值列表。③parameter_vari1bleoutput参数变量。若存储过程返回一个输出参数，则把其值交给某一参数变量，带回客户端。④withrecompile强制每次运行都先重新编译存储过程。7.3.1创建和执行存储过程的步骤创建和应用存储过程7.3.2创建和调用不带参数的存储过程根据“高职院校教学管理系统”中的存储过程需求分析，创建并调用存储过程来查询学生基本信息。【例7-2】创建存储过程proc_stu_info，要求该存储过程返回学生基本信息。代码如下：

USEJXGLGOCREATEPROCEDUREproc_stu_infoASSELECT*FROMstudent_infoGO

提示：每个存储过程应该完成一项单独的工作，这也符合软件工程的思想。为防止其他用户看到自己所编写的存储过程脚本，创建存储过程时使用参数WITHENCRYPTION加密存储过程的定义。创建和应用存储过程7.3.3创建和调用带参数的存储过程用户创建带参数的存储过程，通过参数与调用程序进行通信，输入参数向该存储过程传递值，由输出参数将值返回至调用程序。1．创建和调用带输入参数的存储过程【例7-3】创建存储过程proc_stud_info2，查询某个学生信息。USEJXGLGOCREATEPROCEDUREproc_stud_info2@s_namenvarchar(20)ASSELECT*FROMstudent_infoWHEREstud_name=@s_nameGO

其中：“N'孙晨湄'”是为输入参数@s_name指定的值，它将被带入存储过程，并出现在应用该参数的位置“stud_name=@s_name”，达到为字段stud_name赋值的目的。创建和应用存储过程2．创建和调用带输出参数的存储过程在创建存储过程中定义输出参数，使存储过程返回一个或多个值。【例7-4】创建存储过程proc_aver_score，查询某名学生的平均成绩。CREATEPROCEDUREproc_aver_score@s_idchar(10),@aver_scorenumeric(5,2)OUTPUTASSELECT@aver_score=avg(grade)FROMstudent_gradeWHEREstud_id=@s_idGO7.3.3创建和调用带参数的存储过程创建和应用存储过程调用以上存储过程，代码如下：DECLARE@sidchar(10)--定义参数变量DECLARE@averscorenumeric(5,2)--定义参数变量SET@sid='D06010209'--为参数变量赋值--将参数变量@sid的值传递给输入参数，指定参数变量@averscore将输出参数的值带回客户端EXECproc_aver_score@sid,@averscoreOUTPUTPRINT N'学号为'+CONVERT(CHAR(10),@sid)+ N'的同学的平均成绩是：'+CONVERT(char(10),@averscore)

其中：PRINT语句只能输出字符串，因此在该例中使用转换函数CONVERT()将数字转换为字符输出。7.3.3创建和调用带参数的存储过程创建和应用存储过程【例7-5】创建存储过程aaddb，实现求任意两个整数的和。代码如下：CREATEPROCEDUREaaddb@aINT,@bINT,@cINTOUTPUTASSELECT@c=@a+@bGO执行以下代码调用存储过程aaddb。DECLARE@aINTDECLARE@bINTDECLARE@cINTSET@a=130SET@b=6SET@c=50EXECaaddb@a,@b,@cOUTPUTPRINTCONVERT(CHAR(5),@a)+N'与'+CONVERT(CHAR(5),@b)+N'的和等于:'+CONVERT(CHAR(5),@c)GO

其中：在PRIINT语句中的@a、@b、@c均指参数变量，而非存储过程参数。7.3.3创建和调用带参数的存储过程创建和应用存储过程7.4管理存储过程任务：使用SSMS及T-SQL两种方法，管理存储过程或重新编译存储过程。创建“高职院校教学管理系统”需要的存储过程。7.4.1查看、修改和删除存储过程7.4.2重新编译存储过程创建和应用存储过程7.4.1查看、修改和删除存储过程1．查看存储过程存储过程被创建以后，它的名字存放在当前数据库的系统表sysobjects中，源代码存放在系统表syscomments中。（1）通过系统存储过程查看用户存储过程。使用系统存储过程sp_helptext查看未加密的存储过程的源代码。基本语法如下：

sp_helptext[@objname=]'name‘使用系统存储过程sp_help可以查看相关存储过程的信息。基本语法如下：sp_helpproc_name

（2）使用SSMS查看存储过程，操作步骤略。创建和应用存储过程7.4.1查看、修改和删除存储过程2．修改存储过程两种方法：一种是把旧的存储过程删除，然后重新创建该存储过程；另一种是用单个的步骤修改存储过程，如更改存储过程或参数定义，但该存储过程定义的权限将保留。（1）使用T-SQL语句修改存储过程。使用ALTERPROCEDURE语句修改已经创建的存储过程，不会更改权限，也不影响相关的存储过程或触发器。

ALTERPROC[EDURE]procedure_name[(@parameterdata_type[VARYING][=default][OUTPUT])][,...n][WITH]RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASsql_statment创建和应用存储过程3．删除存储过程删除存储过程可以使用SQL语句，也可以使用SSMS来完成。（1）使用SQL语句删除存储过程。使用DROPPROCEDUR语句从当前数据库中删除一个或多个存储过程，基本语法如下：DROPPROCEDUREprocedure_name[,…n]执行代码：DROPPROCaaddb,proc_stud_info。将存储过程aaddb和proc_stud_info一起删除。再次刷新“存储过程”节点时，这两个存储过程在存储过程列表中已不再存在。（2）使用SSMS删除存储过程，操作步骤略。7.4.1查看、修改和删除存储过程创建和应用存储过程7.4.2重新编译存储过程为使存储过程能够根据数据库的改变重新变化，要求SQLServer在每次执行存储过程时都对它重新编译。1．创建存储过程时设置重编译选项

CREATEPROCEDURE...[WITHRECOMPILE]

在每次执行时，SQLServer都对该存储过程重新编译和优化，并创建新的查询计划。【例7-6】为JXGL数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。代码如下：

CREATEPROCproc_stud_course@s_idchar(10)WITHRECOMPILEASSELECT*FROMstudent_gradeWHEREstud_id=@s_id创建和应用存储过程7.4.2重新编译存储过程为使存储过程能够根据数据库的改变重新变化，要求SQLServer在每次执行存储过程时都对它重新编译。1．创建存储过程时设置重编译选项

CREATEPROCEDURE...[WITHRECOMPILE]

在每次执行时，SQLServer都对该存储过程重新编译和优化，并创建新的查询计划。【例7-6】为JXGL数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。代码如下：

CREATEPROCproc_stud_course@s_idchar(10)WITHRECOMPILEASSELECT*FROMstudent_gradeWHEREstud_id=@s_id创建和应用存储过程7.4.2重新编译存储过程2．执行存储过程时重编译在EXECUTE中使用WITHRECOMPILE选项，执行存储过程时，先重新编译。基本语法如下：EXECUTEprocedure_name[parameter][WITHRECOMPILE]【例7-7】带重新编译选项执行存储过程proc_aver_score，查询某同学的平均成绩。DECLARE@sidchar(10)DECLARE@averscorenumeric(5,2)SET@sid='D06010209'EXECproc_aver_score@sid,@averscoreOUTPUTWITHRECOMPILEPRINTN'学号为'+CONVERT(CHAR(10),@sid)+N'的同学的平均成绩是:'+CONVERT(char(10),@averscore)创建和应用存储过程7.4.2重新编译存储过程3．使用sp_recompile系统存储过程指定表的存储过程进行重编译。基本语法如下：

sp_recompiletable_name如EXECsp_recompilestudent_info，将强制表student_info的所有存储过程在下一次运行时重新编译创建和应用存储过程7.5知识进阶任务：认识扩展存储过程的概念，以及如何注册扩展存储过程到SQL