《《SQL Server数据库应用技术实例教程》-李东芸-电子教案 任务七》由会员分享，可在线阅读，更多相关《《SQL Server数据库应用技术实例教程》-李东芸-电子教案 任务七（35页珍藏版）》请在金锄头文库上搜索。
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的变量和运算 任务：定义、使用T-SQL的局部变
2、量，了解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_name datatype ，
3、n,7.1.1 T-SQL的变量,其中： variable_name 局部变量的名称。以开始，必须遵循SQL标识符和对象的命名规范，且名字不能使用保留字。 datatype 指定局部变量的数据类型。该数据类型可以是系统数据类型或用户自定义数据类型。 使用SET语句给局部变量赋值：SET variable_name = expression , variable_name = expression ,创建和应用存储过程,【例7-1】定义整型局部变量和一个字符型局部变量并赋值。代码如下： DECLARE variable_int int,variable_char char(15) SET var
4、iable_int=60 SELECT variable_char=HELLO CHINA SELECT variable_int SELECT variable_char 如果被赋值的字符串长度超过DECLARE语句中定义的长度，赋值时超出的部分将被截去。,7.1.1 T-SQL的变量,创建和应用存储过程,2全局变量 全局变量是SQL Server 系统所提供并赋值的变量。全局变量的名字以开头。大多数全局变量的值是报告用户当前会话信息。表7-1给出了SQL Server 的常见全局变量。 可以使用SELECT显示局部变量和全局变量的值（必须首先定义变量）。基本语法如下： 显示局部变量：SEL
5、ECT variable_name 显示全局变量：SELECT variable_name,7.1.1 T-SQL的变量,创建和应用存储过程,在SQL Server 2005 中有一元运算符、赋值运算符、算术运算符、比较运算符、连接运算符、逻辑运算符等。 1 . 一元运算符 包括3种：（正）、（负）和（按位取反）。其中（按位取反）只可用于整型数据类型的表达式。 2. 赋值运算符 赋值运算符，即等号（=），主要用于为变量赋值。 3. 算数运算符 包括：加（）、减（）、乘（）、除（）和取模（）。可以操作的数据类型是int、smallint、tinyint、float、real、money或者sma
6、llmoney；加和减运算符可用于对datetime及smalldatetime值执行算数运算；取模（）运算操作数的数据类型是 int、smallint、tinyint。,7.1.2 T-SQL的运算符,创建和应用存储过程,4. 比较运算符 包含=、（不等于）、()（优先级控制符）。可用于除了text、ntext或image数据类型以外的所有的表达式。比较运算符的结果为布尔数据类型，包含true、false两种值。 5. 连接运算符 连接运算符（）用于两个字符串的连接。字符串连接运算符用来连接char、varchar和text数据类型。 6. 逻辑运算符 包括： AND （与）、OR（或）、
7、NOT（非）逻辑运算符也称为位运算符。使用逻辑运算符可以把多个条件合并起来，逻辑运算返回的数据类型为布尔型。,7.1.2 T-SQL的运算符,创建和应用存储过程,7.1.2 T-SQL的运算符,SQL Server 中各种运算符的优先级顺序,创建和应用存储过程,7.2 认识存储过程 任务：了解存储过程的定义，分析“高职院校教 学管理系统”的存储过程需求。,7.2.1 存储过程的概念及特点 7.2.2 分析“高职院校教学管理系统”的存储过程需求,创建和应用存储过程,7.2.1 存储过程的概念及特点,1. 什么是存储过程 存储过程是一组预先编译好的、能实现特定数据 操作功能的SQL代码集。它与特定
8、数据库相关联，一起被存储在SQL Server服务器上。客户端通过应用程序调用执行，可以象使用函数一样重复使用这此代码集，实现所定义的操作。 存储过程分为以下3类： 系统存储过程 安装SQL Server 时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。 用户自定义存储过程 用户在用户数据库中创建的存储过程。 扩展存储过程 是SQL Server 可以动态装载并执行的动态链接库（DLL），其前缀为xp_，它允许用户使用DLL访问SQL Server。用户使用其他编程语言（如C或C+等）编写。,创建和应用存储过程,7.2.1 存储过程的概念及特点,2存储过程的特点 存储过程需
9、要先编译后运行，而再次调用该存储过程时，不需再次编译。存储过程能够接受参数、嵌套调用另一存储过程、返回状态值和调用结果，还可以被远程数据库服务器执行。有以下特点： 能极大增强SQL语句的功能、效率和灵活性 更好地保证数据的完整性和安全性 能降低网络的通信流量 能提高应用的开发效率和系统的可维护性,创建和应用存储过程,7.2.2 分析“高职院校教学管理系统”的存储过程需求, 存储、检索、维护学生的信息，如查询有关学生的基本信息、在学生信息表中增加记录、查询有关学生的学习成绩，平均成绩及总成绩。 存储、检索、维护教师的信息，如查询有关教师的信息、在教师表中增加或删除教师信息。 存储、检索、维护班级
10、信息，如查询某个班的平均成绩，查询某门课程的不及格人数。 存储、检索、维护课程的信息，如查询某门课程由哪个教师任教，查询该课程的平均成绩。,创建和应用存储过程,7.3 创建存储过程 任务：创建带输入、输出参数的存储过程，应用输入参数带入查询条件，输出参数带出统计结果。,7.3.1 创建和执行存储过程的步骤 7.3.2 创建和调用不带参数的存储过程 7.3.3 创建和调用带参数的存储过程,创建和应用存储过程,7.3.1 创建和执行存储过程的步骤,用户自定义存储过程应遵循以下原则： 存储过程的名称不能使用sp_作前缀。不要为只运行一次的T-SQL命令组构建存储过程。 1创建存储过程的模板代码 可使
11、用SSMS创建存储过程，操作步骤略。 2创建存储过程的CREATE PROCEDURE语句 基本语法如下： CREATE PROC EDURE procedure_name ( parameter data_type VARYING =default OUTPUT ) , . . .n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS sql_statment,其中： procedure_name 指定存储过程名称。 parameter 存储过程的输入或输出参数。可以声明一个或多个参数，分为输入参数和输出参数。输入参数负责向存储过程
12、带入值，输出参数将存储过程的返回值传递给变量，向客户端输出。 OUTPUT 保留字，指定参数为输出参数，其定义位于所有输入参数之后。返回值是存储过程执行完成时参数的当前值。 Default 为参数指定默认值。 WITH RECOMPILE 重编译选项，表明 SQL Server 不会保存该存储过程的执行计划 ，要求每次执行存储过程都要重新编译和优化，并创建新的查询计划。 ENCRYPTION 加密选项。 AS 指定该存储过程要执行的操作。 通过使用()符号作为第一个字符来指定参数名，必须符合有关标识符的规则。一个存储过程最多可定义 2,100 个参数,创建和应用存储过程,使用T-SQL最常用的
13、执行存储过程的方法，是使用 EXECUTE 语句（系统存储过程可不使用 EXECUTE 关键字）。基本语法如下： EXECUTE procedure_name value_list | parameter_vari1ble OUTPUT ,.n with recompile 其中： procedure_name 执行的存储过程名。 value_list 输入参数要带入存储过程的参数值列表。 parameter_vari1ble output 参数变量。若存储过程返回一个输出参数，则把其值交给某一参数变量，带回客户端。 with recompile 强制每次运行都先重新编译存储过程。,7.3.1
14、 创建和执行存储过程的步骤,创建和应用存储过程,7.3.2 创建和调用不带参数的存储过程,根据“高职院校教学管理系统”中的存储过程需求分析，创建并调用存储过程来查询学生基本信息。 【例7-2】创建存储过程proc_stu_info，要求该存储过程返回学生基本信息。代码如下： USE JXGL GO CREATE PROCEDURE proc_stu_info AS SELECT * FROM student_info GO,提示：每个存储过程应该完成一项单独的工作，这也符合软件工程的思想。为防止其他用户看到自己所编写的存储过程脚本，创建存储过程时使用参数WITH ENCRYPTION加密存储过
15、程的定义。,创建和应用存储过程,7.3.3 创建和调用带参数的存储过程,用户创建带参数的存储过程，通过参数与调用程序进行通信，输入参数向该存储过程传递值，由输出参数将值返回至调用程序。 1创建和调用带输入参数的存储过程 【例7-3】创建存储过程proc_stud_info2，查询某个学生信息。USE JXGL GO CREATE PROCEDURE proc_stud_info2 s_name nvarchar(20) AS SELECT * FROM student_info WHERE stud_name=s_name GO,其中： “N孙晨湄”是为输入参数s_name指定的值，它将被带入
16、存储过程，并出现在应用该参数的位置“stud_name=s_name”，达到为字段stud_name赋值的目的。,创建和应用存储过程,2创建和调用带输出参数的存储过程 在创建存储过程中定义输出参数，使存储过程返回一个或多个值。 【例7-4】创建存储过程proc_aver_score，查询某名学生的平均成绩。 CREATE PROCEDURE proc_aver_score s_id char(10), aver_score numeric(5,2) OUTPUT AS SELECT aver_score =avg(grade) FROM student_grade WHERE stud_id=s_id GO,7.3.3 创建和调用带
