SQLServer数据库应用技术实例教程 任务7 提高数据操作速度.ppt

任务 了解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 name 7 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的运算符 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 idGO 7 3 3创建和调用带参数的存储过程 创建和应用存储过程 调用以上存储过程 代码如下 DECLARE sidchar 10 定义参数变量DECLARE averscorenumeric 5 2 定义参数变量SET sid D06010209 为参数变量赋值 将参数变量 sid的值传递给输入参数 指定参数变量 averscore将输出参数的值带回客户端EXECproc aver score sid averscoreOUTPUTPRINTN 学号为 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知识进阶任务 认识扩展存储过程的概念 以及如何注册扩展存储过程到SQLServer 7 5 1认识扩展存储过程7 5 2使用扩展存储过程 创建和应用存储过程 7 5 1认识扩展存储过程 SQLServer中