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

1、任务：任务：了解T-SQL的变量和运算符。学习存储过程的定义及其特点，创建存储过程的方法，如何执行存储过程，查看、修改、删除存储过程。7.1 认识T-SQL的变量和运算7.2 认识存储过程7.3 创建存储过程7.3 创建存储过程7.4 管理存储过程7.5 知识进阶 项目实训任务七任务七 提高数据操作速度提高数据操作速度 _在SQL Server 2005中创建和应用存储过程了解T-SQL的变量和运算符了解什么是存储过程及其特点掌握创建存储过程的方法掌握如何执行存储过程学会查看、修改、删除存储过程学习目标学习目标创建和应用存储过程7.1 认识T-SQL的变量和运算 任务：定义、使用任务：定义、使

2、用T-SQL的局部变量，了解的局部变量，了解T-SQL的运算符。的运算符。7.1.1 T-SQL的变量7.1.2 T-SQL的运算符创建和应用存储过程T-SQL语言的变量分为局部变量和全局变量。1局部变量局部变量使用DECLARE语句声明，在声明时它的默认值为NULL，使用SET语句为其赋值。局部变量仅生存于声明它批中，如存储过程。处理结束后，局部变量中的信息将丢失。SQL Server 使用SELECT语句或PRINT语句来将变量的值提交给用户，而存储过程则将变量作为一个Output参数返回。基本语法如下：DECLARE variable_name datatype ，variable_na

3、me datatype ，n其中：其中：variable_name 局部变量的名称。以开始，必须遵循SQL标识符和对象的命名规范，且名字不能使用保留字。datatype 指定局部变量的数据类型。该数据类型可以是系统数据类型或用户自定义数据类型。使用SET语句给局部变量赋值：SET variable_name=expression ,variable_name=expression 创建和应用存储过程【例7-1】定义整型局部变量和一个字符型局部变量并赋值。代码如下：DECLARE variable_int int,variable_char char(15)SET variable_int=60

4、SELECT variable_char=HELLO CHINASELECT variable_intSELECT variable_char如果被赋值的字符串长度超过DECLARE语句中定义的长度，赋值时超出的部分将被截去。创建和应用存储过程2全局变量 全局变量是SQL Server 系统所提供并赋值的变量。全局变量的名字以开头。大多数全局变量的值是报告用户当前会话信息。表7-1给出了SQL Server 的常见全局变量。可以使用SELECT显示局部变量和全局变量的值（必须首先定义变量）。基本语法如下：显示局部变量：SELECT variable_name 显示全局变量：SELECT var

5、iable_name创建和应用存储过程在SQL Server 2005 中有一元运算符、赋值运算符、算术运算符、比较运算符、连接运算符、逻辑运算符等。1.一元运算符一元运算符 包括3种：（正）、（负）和（按位取反）。其中（按位取反）只可用于整型数据类型的表达式。2.赋值运算符赋值运算符 赋值运算符，即等号（=），主要用于为变量赋值。3.算数运算符算数运算符 包括：加（）、减（）、乘（）、除（）和取模（）。可以操作的数据类型是int、smallint、tinyint、float、real、money或者smallmoney；加和减运算符可用于对datetime及smalldatetime值执行算

6、数运算；取模（）运算操作数的数据类型是 int、smallint、tinyint。创建和应用存储过程4.比较运算符比较运算符 包含=、（不等于）、()（优先级控制符）。可用于除了text、ntext或image数据类型以外的所有的表达式。比较运算符的结果为布尔数据类型，包含true、false两种值。5.连接运算符连接运算符 连接运算符（）用于两个字符串的连接。字符串连接运算符用来连接char、varchar和text数据类型。6.逻辑运算符逻辑运算符 包括：AND（与）、OR（或）、NOT（非）逻辑运算符也称为位运算符。使用逻辑运算符可以把多个条件合并起来，逻辑运算返回的数据类型为布尔型。创

7、建和应用存储过程（）（括号）（按位取反）正、负乘、除、取模：*、/、%加、连接符、减比较运算符：=、=、=、NOT(非)AND(与)OR(或)=（赋值）高高低低创建和应用存储过程 7.2 7.2 认识存储过程认识存储过程 任务：任务：了解存储过程的定义，分析“高职院校教 学管理系统”的存储过程需求。7.2.1 存储过程的概念及特点7.2.2 分析“高职院校教学管理系统”的存储过程需求创建和应用存储过程1.什么是存储过程存储过程是一组预先编译好的、能实现特定数据 操作功能的SQL代码集。它与特定数据库相关联，一起被存储在SQL Server服务器上。客户端通过应用程序调用执行，可以象使用函数一样

8、重复使用这此代码集，实现所定义的操作。存储过程分为以下3类：系统存储过程 安装SQL Server 时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。用户自定义存储过程 用户在用户数据库中创建的存储过程。扩展存储过程 是SQL Server 可以动态装载并执行的动态链接库（DLL），其前缀为xp_，它允许用户使用DLL访问SQL Server。用户使用其他编程语言（如C或C+等）编写。创建和应用存储过程2存储过程的特点 存储过程需要先编译后运行，而再次调用该存储过程时，不需再次编译。存储过程能够接受参数、嵌套调用另一存储过程、返回状态值和调用结果，还可以被远程数据库服务器执行

9、。有以下特点：能极大增强SQL语句的功能、效率和灵活性 更好地保证数据的完整性和安全性 能降低网络的通信流量 能提高应用的开发效率和系统的可维护性创建和应用存储过程 存储、检索、维护学生的信息，如查询有关学生的基本信息、在学生信息表中增加记录、查询有关学生的学习成绩，平均成绩及总成绩。存储、检索、维护教师的信息，如查询有关教师的信息、在教师表中增加或删除教师信息。存储、检索、维护班级信息，如查询某个班的平均成绩，查询某门课程的不及格人数。存储、检索、维护课程的信息，如查询某门课程由哪个教师任教，查询该课程的平均成绩。创建和应用存储过程7.3 7.3 创建存储过程创建存储过程 任务：任务：创建带

10、输入、输出参数的存储过程，应用输入参数带入查询条件，输出参数带出统计结果。7.3.1 创建和执行存储过程的步骤7.3.2 创建和调用不带参数的存储过程7.3.3 创建和调用带参数的存储过程创建和应用存储过程7.3.1 创建和执行存储过程的步骤用户自定义存储过程应遵循以下原则：存储过程的名称不能使用sp_作前缀。不要为只运行一次的T-SQL命令组构建存储过程。1创建存储过程的模板代码 可使用SSMS创建存储过程，操作步骤略。2创建存储过程的CREATE PROCEDURE语句 基本语法如下：CREATE PROC EDURE procedure_name(parameter data_type

11、VARYING=default OUTPUT),.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION ASsql_statment 其中：其中：procedure_name 指定存储过程名称。parameter 存储过程的输入或输出参数。可以声明一个或多个参数，分为输入参数和输出参数。输入参数负责向存储过程带入值，输出参数将存储过程的返回值传递给变量，向客户端输出。OUTPUT 保留字，指定参数为输出参数，其定义位于所有输入参数之后。返回值是存储过程执行完成时参数的当前值。Default 为参数指定默认值。WITH RECOMPILE 重编译选项，

12、表明 SQL Server 不会保存该存储过程的执行计划，要求每次执行存储过程都要重新编译和优化，并创建新的查询计划。ENCRYPTION 加密选项。AS 指定该存储过程要执行的操作。通过使用()符号作为第一个字符来指定参数名，必须符合有关标识符的规则。一个存储过程最多可定义 2,100 个参数创建和应用存储过程使用T-SQL最常用的执行存储过程的方法，是使用 EXECUTE 语句（系统存储过程可不使用 EXECUTE 关键字）。基本语法如下：EXECUTE procedure_name value_list|parameter_vari1ble OUTPUT ,.n with recompi

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

14、L GO CREATE PROCEDURE proc_stu_info AS SELECT*FROM student_info GO 提示：提示：每个存储过程应该完成一项单独的工作，这也符合软件工程的思想。为防止其他用户看到自己所编写的存储过程脚本，创建存储过程时使用参数WITH ENCRYPTION加密存储过程的定义。创建和应用存储过程7.3.3 创建和调用带参数的存储过程 用户创建带参数的存储过程，通过参数与调用程序进行通信，输入参数向该存储过程传递值，由输出参数将值返回至调用程序。1创建和调用带输入参数的存储过程【例7-3】创建存储过程proc_stud_info2，查询某个学生信息。U

15、SE JXGLGOCREATE PROCEDURE proc_stud_info2 s_name nvarchar(20)AS SELECT *FROM student_info WHERE stud_name=s_name GO 其中：其中：“N孙晨湄”是为输入参数s_name指定的值，它将 被 带 入 存 储 过 程，并 出 现 在 应 用 该 参 数 的 位 置“stud_name=s_name”，达到为字段stud_name赋值的目的。创建和应用存储过程2创建和调用带输出参数的存储过程创建和调用带输出参数的存储过程 在创建存储过程中定义输出参数，使存储过程返回一个或多个值。【例7-4】

16、创建存储过程proc_aver_score，查询某名学生的平均成绩。CREATE PROCEDURE proc_aver_score s_id char(10),aver_score numeric(5,2)OUTPUTAS SELECT aver_score=avg(grade)FROM student_grade WHERE stud_id=s_id GO7.3.3 创建和调用带参数的存储过程创建和应用存储过程调用以上存储过程，代码如下：DECLARE sid char(10)-定义参数变量DECLARE averscore numeric(5,2)-定义参数变量SET sid=D0601

17、0209 -为参数变量赋值-将参数变量sid的值传递给输入参数，指定参数变量averscore将输出参数的值带回客户端EXEC proc_aver_score sid,averscore OUTPUT PRINT N学号为 +CONVERT(CHAR(10),sid)+N的同学的平均成绩是：+CONVERT(char(10),averscore)其中：其中：PRINT语句只能输出字符串，因此在该例中使用转换函数CONVERT()将数字转换为字符输出。7.3.3 创建和调用带参数的存储过程创建和应用存储过程【例7-5】创建存储过程aaddb，实现求任意两个整数的和。代码如下：CREATE PRO

18、CEDURE aaddb a INT,b INT,c INT OUTPUTAS SELECT c=a+bGO执行以下代码调用存储过程aaddb。DECLARE a INT DECLARE b INT DECLARE c INTSET a=130 SET b=6 SET c=50EXEC aaddb a,b,c OUTPUT PRINT CONVERT(CHAR(5),a)+N 与 +CONVERT(CHAR(5),b)+N 的和等于:+CONVERT(CHAR(5),c)GO 其中：其中：在PRIINT语句中的a、b、c均指参数变量，而非存储过程参数。7.3.3 创建和调用带参数的存储过程创建

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

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

21、parameter data_type VARYING=default OUTPUT),.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS sql_statment创建和应用存储过程3删除存储过程删除存储过程可以使用SQL语句，也可以使用SSMS来完成。（1）使用SQL语句删除存储过程。使用DROP PROCEDUR语句从当前数据库中删除一个或多个存储过程，基本语法如下：DROP PROCEDURE procedure_name ,n执行代码：DROP PROC aaddb,proc_stud_info。将存储过程aaddb和proc_st

22、ud_info一起删除。再次刷新“存储过程”节点时，这两个存储过程在存储过程列表中已不再存在。（2）使用SSMS删除存储过程，操作步骤略。7.4.1 查看、修改和删除存储过程创建和应用存储过程7.4.2 重新编译存储过程为使存储过程能够根据数据库的改变重新变化，要求SQL Server 在每次执行存储过程时都对它重新编译。1创建存储过程时设置重编译选项 CREATE PROCEDURE.WITH RECOMPILE 在每次执行时，SQL Server都对该存储过程重新编译和优化，并创建新的查询计划。【例7-6】为JXGL数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。代码如下：

23、CREATE PROC proc_stud_course s_id char(10)WITH RECOMPILE AS SELECT*FROM student_grade WHERE stud_id=s_id创建和应用存储过程7.4.2 重新编译存储过程为使存储过程能够根据数据库的改变重新变化，要求SQL Server 在每次执行存储过程时都对它重新编译。1创建存储过程时设置重编译选项创建存储过程时设置重编译选项 CREATE PROCEDURE.WITH RECOMPILE 在每次执行时，SQL Server都对该存储过程重新编译和优化，并创建新的查询计划。【例7-6】为JXGL数据库创建一

24、个带重编译选项的存储过程，用于查询某学生的成绩信息。代码如下：CREATE PROC proc_stud_course s_id char(10)WITH RECOMPILE AS SELECT*FROM student_grade WHERE stud_id=s_id创建和应用存储过程7.4.2 重新编译存储过程2执行存储过程时重编译执行存储过程时重编译在EXECUTE中使用WITH RECOMPILE选项，执行存储过程时，先重新编译。基本语法如下：EXECUTE procedure_name parameter WITH RECOMPILE【例7-7】带重新编译选项执行存储过程proc_a

25、ver_score，查询某同学的平均成绩。DECLARE sid char(10)DECLARE averscore numeric(5,2)SET sid=D06010209 EXEC proc_aver_score sid,averscore OUTPUT WITH RECOMPILEPRINT N学号为 +CONVERT(CHAR(10),sid)+N的同学的平均成绩是:+CONVERT(char(10),averscore)创建和应用存储过程7.4.2 重新编译存储过程3使用sp_recompile 系统存储过程指定表的存储过程进行重编译。基本语法如下：sp_recompile tab

26、le_name如EXEC sp_recompile student_info，将强制表student_info的所有存储过程在下一次运行时重新编译 创建和应用存储过程 7.5 知识进阶知识进阶 任务：任务：认识扩展存储过程的概念，以及如何注册扩展存储过程到SQL Server。7.5.1 认识扩展存储过程7.5.2 使用扩展存储过程创建和应用存储过程 7.5.1 认识扩展存储过程 SQL Server中的另一类存储过程是扩展存储过程，是以SQL以外的程序设计语言编写的外部程序，一般以xp_为前缀，以动态链接库（DLL）形式存储在服务器上，SQL Server可以动态装载并执行它们。扩展存储过程

27、只能添加到master数据库中。编写好扩展存储过程后，固定服务器角色（sysadamin）成员即可在SQL Server服务器上注册该扩展存储过程，并将它们的执行权限授权其他用户。SQL Server本身提供了一些扩展存储过程，也可以由开发人员来编写。创建和应用存储过程7.5.2 使用扩展存储过程1使用T-SQL语句注册扩展存储过程SQL Server 2005的系统管理员通过系统存储过程 sp_addextendedproc来添加一个扩展存储过程到master数据库中。基本语法如下：sp_addextendedproc functname=procedure,dllname=dll 其中：其

28、中：functname=procedure：在DLL内调用的函数名称。dllname=dll：包含该函数的DLL名称。sp_addextendedproc将新的函数名称添加到系统表sysobjects中，在SQL Server中注册新扩展存储过程的名称，同时在syscomments表中添加一个条目。2使用SSMS注册扩展存储过程创建和应用存储过程项目实训项目实训 1实训题目实训题目 为“销售合同管理系统”创建存储过程。2实训目的实训目的（1）了解T-SQL语言的变量定义、运算符定义。（2）根据项目的特点分析对存储过程的需求。（3）掌握存储过程的创建与管理，能够根据项目的需要创建存储过程。3实训内容实训内容（1）分析“销售合同管理系统”的存储过程需求。（2）创建存储过程proc_employee_info，返回员工基本信息。（3）创建带输入参数的存储过程。（4）创建带输出参数的存储过程 proc_contract_search，完成合同数量查询。创建和应用存储过程