Transact-SQL程序设计.ppt

第7章 第7章Transact SQL程序设计 数据库应用教程 本章主要内容 常用内置函数 变量与表达式 程序控制语句 游标及其使用 用户自定义函数 主要内容 存储过程及其使用 7 1变量与表达式 7 1 1变量 1 全局变量 SQLServer使用全局变量来记录SQLServer服务器的活动状态 它由系统定义并保留在系统中 用户和程序不需声明就可随时读取 但不能改变它的值 全局变量的名称以两个 字符开头 SQLServer提供了30多个全局变量 7 1变量与表达式 7 1 1变量 例7 1 用 ERROR变量检查错误 在查询分析器中输入下列代码并执行 USECollegeMISGODECLARE ERRORNOINT 定义一个局部变量 ERRORNO INSERTINTOStudent StuNo StuName StuID VALUES 050301001 王高林 324382475454342321 SET ERRORNO ERROR 把错误号临时保存起来 IF ERRORNO0 如果插入语句出错 输出出错信息及错误号 PRINT 插入错误 错误号 CONVERT varchar ERRORNO CONVERT varchar ERRORNO 的作用是把 ERRORNO转换为varchar类型 7 1变量与表达式 7 1 1变量 1 ERROR变量 在SQLServer中执行一条Transact SQL语句之后 如果语句成功执行 那么 ERROR变量的值为0 如果出现一个错误 则该变量的值是一个不为0的错误号 有关错误号的具体含义 读者可查阅sysmessages系统表 2 PRINT语句量 格式 PRINT anyASCIItext local variable FUNCTION string expr 功能 输出字符串 局部变量 全局变量或函数的返回值 字符串表达式的值 7 1变量与表达式 7 1 1变量 2 局部变量 1 局部变量的声明 例如 DECLARE StuNochar 9 定义一个名为 StuNo的局部变量 数据类型是char 9 DECLARE Birthdaydatetime 定义一个名为 Birthday的局部变量 数据类型是datetime DECLARE Heightdecimal 5 3 定义一个名为 Height的局部变量 数据类型是decimal 5 3 格式 DECLARE 局部变量名数据类型 n 功能 声明由 局部变量名 指定的局部变量 7 1变量与表达式 7 1 1变量 2 局部变量 2 局部变量的赋值 格式1 SET 功能1 把 表达式 的值赋给 变量名 指定的局部变量 格式2 SET 功能2 把 表达式 的值赋给 变量名 指定的局部变量 格式3 SELECT FROMWHERE 功能3 把从 表名 指定的表中 按照WHERE子句中 条件 选取记录 把计算出来的 表达式 的值赋给 变量名 指定的局部变量 如果SELECT语句返回多个数值 则局部变量取最后一个返回值 例7 2 求出学号为 060301002 的学生的选课门数并输出 USECollegeMISGODECLARE XHASchar 9 存放学号 DECLARE NUMBERASint 存放课程门数 SET XH 060301002 给变量赋值 通过SELECT语句得到学号为 060301002 的学生的选课门数并赋值给 NUMBER SELECT NUMBER COUNT FROMSelectCourseWHEREStuNo XHPRINT 060301002号学生选了 CONVERT char 2 NUMBER 门课 7 1变量与表达式 7 1 2运算符与表达式 1 算术运算符与算术表达式 7 1变量与表达式 7 1 2运算符与表达式 2 位运算符与位表达式 位运算符用于对整型数进行按拉运算 有位与 位或 位异或 位取反 等运算符 用位运算符把整型量连接起来的式子称位表达式 位表达式的结果是一个整型数 1 位与运算符 参加运算的两位都为1 则结果为1 否则结果为0 一个位与1位与 结果为该位 与0位与 结果为0 所以程序中通常使用它将一个数的某些位清零 2 位或运算符 参加运算的两个位只要有一个为1 那么运算结果为1 一个位与1位或 结果为1 与0位或 结果不变 所以在程序中常用它将一个数的某些位置1 7 1变量与表达式 7 1 2运算符与表达式 2 位运算符与位表达式 3 位异或运算符 参加运算的两个位相同 结果为0 不同结果为1 一个位与1异或 结果把它取反 一个位与0异或 结果不变 所以在程序中常用它把一个数的某些位取反 4 位取反运算符 是一个单目运算符 用来对一个整形数按位取反 例如 a和 b的类型都是int 假设 a的值为 5 b的值为223 求 a b 首先把 a的值转换成补码为 1111111111111011 223的二进制是 0000000011011111 这两个数位或后的机器码是1111111111111111 该值是 1 7 1变量与表达式 7 1 2运算符与表达式 3 关系 比较 运算符与关系表达式 7 1变量与表达式 7 1 2运算符与表达式 4 逻辑运算符与逻辑表达式 7 1变量与表达式 7 1 2运算符与表达式 4 逻辑运算符与逻辑表达式 例如 有下列表达式 假设x 3 y 5 a 2 b 3 x aANDybANDxb ANDNOT x y 结果为FALSE 7 1变量与表达式 7 1 2运算符与表达式 5 字符运算符与字符表达式 字符运算符只有一个 称为字符串连接运算符 它把两个字符串连接在一起构成一个新的字符串 用字符串连接运算符连接字符串所组成的式子称字符表达式 例如表达式 Iam ateacher 的运算结果是 Iamateacher 6 运算符的优先级 在一个表达式中 运算符的优先级决定了运算的顺序 Transact SQL语言中 各运算符的优先级如下 关系运算符 NOT AND OR在一个表达式中 应先算括号里再算括号外 先算优先级高的运算符 再算优先级低的运算符 相同优先级的运算符按自左向右的顺序进行运算 7 2常用内置函数 7 2 1字符串函数 7 2常用内置函数 7 2 1字符串函数 7 2常用内置函数 7 2 2数学函数 7 2常用内置函数 7 2 2数学函数 7 2常用内置函数 7 2 3日期函数 7 2常用内置函数 7 2 3日期函数 7 2常用内置函数 7 2 4类型转换函数 7 2常用内置函数 7 2 5系统函数 7 3程序控制语句 7 3 1BEGIN END语句 格式 BEGIN END 功能 把 语句1 语句n 的多条语句组合成一个语句块 使它们能够整体执行 7 3程序控制语句 7 3 2IF ELSE语句 格式 IF ELSE 功能 如果条件为TRUE执行语句1 如果条件为FALSE执行语句2 如果省略 ELSE语句2 则条件为FALSE时什么也不执行 例7 3 求出课程号为 0002 的课程的优秀人数并输出 USECollegeMISGODECLARE CourseNoASchar 4 存放课程号 DECLARE GoodNumASint 存放对应课程号的优秀人数 SET CourseNo 0002 给变量赋值 IFEXISTS SELECT FROM 如果该门课有优秀的记录 SelectCourseWHERECourseNo CourseNoANDScore 90 BEGIN 通过SELECT语句得到0002号课程的优秀人数并赋值给 GoodNum SELECT GoodNum Count StuNo FROMSelectCourseWHERECourseNo CourseNoANDScore 90PRINT 0002号课程优秀人数为 CONVERT char 2 GoodNum 人 ENDELSEPRINT 0002号课没有人获得优秀成绩 7 3程序控制语句 7 3 3CASE表达式 1 简单CASE表达式 格式 CASEWHENTHEN WHENTHEN WHENTHEN ELSE END 功能 用 比较表达式 的值依次与WHEN子句中的 比较值1 比较值2 比较值n 比较 直到找到第一个与 比较表达式 的值完全相同的 比较值 时 便将对应WHEN子句指定的 结果表达式 作为CASE表达式的值返回 例7 4 从SelectCourse表中查询出所有学生的选课信息 包括学号 课程名 用课程名代替课号 和成绩 并按成绩降序排列 USECollegeMISGOSELECTStuNo CourseNo CASECourseNoWHEN 0001 THEN 计算机应用基础 WHEN 0002 THEN 程序设计基础 WHEN 0003 THEN 操作系统 ELSE 计算机网络技术 END SCOREFROMSelectCourseORDERBYScoreDESC 7 3程序控制语句 7 3 3CASE表达式 2 搜索CASE表达式 格式 CASEWHENTHEN WHENTHEN WHENWHEN ELSE END 功能 依次计算WHEN子句后的 逻辑表达式1 逻辑表达式2 逻辑表达式n 直到找到第一个值为TRUE的 逻辑表达式 把该WHEN子句后的 结果表达式 作为CASE表达式的值返回 如果所有的 逻辑表达式 的值均为FALSE 则检查是否有ELSE子句存在 如果有ELSE子句 便将ELSE子句之后的 结果表达式 作为CASE表达式值返回 如果不存在ELSE子句 便返回一个NULL值作为CASE表达式的值 例7 5 从SelectCourse表中查询出所有学生的选课信息 包括学号 课程名 用课程名代替课号 和成绩 用成绩等级代替成绩 成绩等级为优秀 良好 中等 及格和不及格 并按学号降序排列 SELECTStuNo CourseNo CASECourseNoWHEN 0001 THEN 计算机应用基础 WHEN 0002 THEN 程序设计基础 WHEN 0003 THEN 操作系统 ELSE 计算机网络技术 END Score CASEWHENScore 90THEN 优秀 WHENScore 80THEN 良好 WHENScore 70THEN 中等 WHENScore 60THEN 良好 ELSE 不及格 ENDFROMSelectCourseORDERBYStuNoDESC 7 3程序控制语句 7 3 4WHILE语句 格式 WHILE语句 功能 首先测试 逻辑表达式 的值 如果为TRUE 则执行 语句 即循环体 然后再测试 逻辑表达式 如果为TRUE 再执行语句 直到某次测试 逻辑表达式 的值时 发现该表达式的值为FALSE时 退出循环 执行WHILE语句后面的语句 7 3程序控制语句 7 3 4WHILE语句 格式 WHILEBEGIN CONTINUE BREAK END 功能 首先测试 逻辑表达式 的值 如果为TRUE 则执行BEGIN END语句块 即循环体 然后再测试 逻辑表达式 如果为TRUE 再执行BEGIN END语句块 直到某次测试 逻辑表达式 的值时 发现该表达式的值为FALSE时 退出循环 执行WHILE语句后面的语句 如果在执行BEGIN END语句块的过程中 遇到CONTINUE语句可使程序忽略CONTINUE之后的语句 直接跳回到WHILE命令行 重新测试条件 决定是否执行下一次循环 若遇到BREAK语句将提前直接退出循环 跳转到循环之后的语句 例7 6 编写程序输出12 22 32 102的值 DECLARE SASint TASintDECLARE IASsmallintSET S 0SET I 1WHILE I 10BEGINSET T I ISET S S TSET I I 1ENDPRINT 和为 CONVERT char 6 S 例7 7 编写程序输出1到100之间所有能被3整除的数的和 DECLARE SASintDECLARE IASsmallintSET S 0SET I 0WHILE I0 如果 I的值不能被3整除 CONTINUE 开始下一次循环 SET S S I 如果 I的值能被3整除 把 I的值加到 S中 ENDPRINT 和为 CONVERT char 6 S 例7 8 编写程序输出3到100之间所有素数的和 所谓素数是指只能被1和其本身整除的数 DECLARE SASintDECLARE IASsmallint JASsmallintSET S 0SET I 3WHILE I 99 外层循环从3到99 BEGINSET J 2WHILE J I 1 内层循环用来判断 I是不是素数 BEGINIF I J 0 如果 I能够被 J整除 则不是素数 BREAK 退出循环 SET J J 1ENDIF J I 符合该条件则 I是素数 SET S S I 把素数加到和 S中 SET I I 2 I的值加2 原因是只有奇数才可能是素数 ENDPRINT 和为 CONVERT char 6 S 输出和 7 3程序控制语句 7 3 5WAITFOR语句 格式1 WAITFORDELAY HH MM SS 功能 计算机暂停由 HH MM SS 指定的时间间隔后再继续执行下面的语句 格式2 WAITFORDELAYTIME HH MM SS 功能 计算机暂停到 HH MM SS 指定的时间时才继续执行下面的语句 7 4用户自定义函数 7 4 1用户自定义函数的创建 查看 修改与删除 1 用户自定义函数的创建 1 使用Transact SQL语句创建函数 格式 CREATEFUNCTION owner name function name parameter name AS scalar parameter data type default n RETURNSscalar return data type WITH n AS BEGINfunction bodyRETURNscalar expressionEND 功能 定义由 function name 指定名称的标量函数 例7 7 为CollegeMIS数据库创建一个名为ScoreGrade的用户自定义函数 该函数用来对百分制成绩进行判断 并返回相应的等级 成绩大于等于90分时 返回的等级为 优秀 成绩大于等于80分而小于90分时 返回的等级为 良好 成绩大于等于70分而小于80分时 返回的等级为 中等 成绩大于等于60分而小于70分时 返回的等级为 及格 其它情况返回不及格 并调用该函数显示出所有选修了 0002 号课的人的成绩等级 CREATEFUNCTIONScoreGrade Scoretinyint RETURNSvarchar 8 BEGINDECLARE GradeStrvarchar 8 SET GradeStr CaseWHEN Score 90THEN 优秀 WHEN Score 80THEN 良好 WHEN Score 70THEN 中等 WHEN Score 60THEN 及格 ELSE 不及格 ENDRETURN GradeStrEND USECollegeMISGOSELECTS StuNoas学号 S StuNameAS姓名 dbo ScoreGrade SC Score AS等级FROMSelectCourseSCINNERJOINStudentSONSC StuNo S StuNoWHERECourseNo 0002 GO 7 4用户自定义函数 7 4 1用户自定义函数的创建 查看 修改与删除 1 用户自定义函数的创建 2 使用企业管理器创建函数 例7 10 为CollegeMIS数据库创建一个名为FailureNum的用户自定义函数 该函数的作用是根据用户输入的课程号 统计该门课不及格 小于60分 的人数 7 4用户自定义函数 7 4 1用户自定义函数的创建 查看 修改与删除 2 用户自定义函数的查看 1 通过企业管理器查看 格式1 EXEC sp help 2 使用系统存储过程查看用户自定义函数信息 功能 查看 用户自定义函数名 指定的函数的概要信息 如语句 execsp helpFailureNum 格式2 EXEC sp helptext 功能 查看 用户自定义函数名 指定的函数的定义文本信息 7 4用户自定义函数 7 4 1用户自定义函数的创建 查看 修改与删除 3 用户自定义函数的修改 格式 ALTERFUNCTION owner name function name parameter namescalar parameter data type default n RETURNSscalar return data type WITH n AS BEGINfunction bodyRETURNscalar expressionEND 功能 修改由function name作为函数名指定的用户自定义函数 例 现在规定考试成绩低于55分为不及格 则可使用如下语句对FailureNum函数进行修改 ALTERFUNCTION dbo FailureNum CourseNochar 4 RETURNSintASBEGINDECLARE NumintSELECT Num Count StuNo FROMSelectCourseWHERECourseNo CourseNoANDScore 55RETURN NumEND 7 4用户自定义函数 7 4 1用户自定义函数的创建 查看 修改与删除 4 用户自定义函数的删除 1 使用企业管理器删除用户自定义函数 格式 DROPFUNCTION owner name function name n 2 使用Transact SQL语句删除用户自定义函数 功能 删除由function name作为函数名指定的用户自定义函数 例如 要删除FailureNum函数 只需执行如下语句 USECollegeMISGODROPFUNCTIONdbo FailureNumGO 7 4用户自定义函数 7 4 2用户自定义函数的类型 2 标量函数 标量函数返回值的类型为SQLServer2000的系统数据类型 但不可以是text ntext image cursor timestamp table等类型 标量函数的函数体语句定义在BEGIN END语句内 7 4 1节中创建的FailureNum函数和ScoreGrade函数均为标量函数 7 4用户自定义函数 7 4 2用户自定义函数的类型 1 内嵌表值函数 内嵌表值函数的返回值是一个表 该类函数的函数体不使用BEGIN END语句 而是通过RETURN语句返回SELECT查询得到的一个结果集 其功能相当于一个参数化的视图 因此在使用上 内嵌表值函数就可以当成一个虚表来使用 格式 CREATEFUNCTION owner name function name parameter name AS scalar parameter data type default n RETURNSTABLE WITH n AS RETURN select stmt 功能 创建由function name作为函数名指定的用户自定义的内嵌表值函数 例7 11 为CollegeMIS数据库创建一个名为StuNo Score的内嵌表值函数 其作用是输入一个学号 查询出该学生的所学选课成绩信息 包括学号 姓名 课号 课程名和成绩 USECollegeMISgoCREATEFUNCTIONStuNo Score StuNochar 9 RETURNStableASRETURN SELECTS StuNoAS学号 S StuNameAS姓名 C CourseNoAS课程号 C CourseNameAS课程名 SC ScoreAS成绩FROMStudentS CourseC SelectCourseSCWHERES StuNo StuNoANDS StuNo SC StuNoANDSC CourseNo C CourseNo USECollegeMISGOSELECT FROMStuNo Score 060301002 GO 7 4用户自定义函数 7 4 2用户自定义函数的类型 3 多语句表值函数 多语句表值函数可以看作标量函数和内嵌表值函数的联合 它集中了这两个函数的优点 其函数值也是一个表 但函数体也用BEGIN END语句定义 作为函数返回值的表中的数据由函数体中的语句插入 因此 多语句表值函数可以进行多次查询操作 弥补了内嵌表值函数的不足 例7 12 为CollegeMIS数据库创建一个名为Score Total的多语句表值函数 函数的功能是输入一个年份 统计出该级学生各门课的平均分 最高分和最低分 USECollegeMISGOCREATEFUNCTIONScore Total yearchar 2 RETURNS cjhzTABLE 课程号char 4 课程名varchar 30 平均分decimal 4 1 最高分tinyint 最低分tinyint AS BEGINDECLARE T1TABLE CNochar 4 PJFdecimal 4 1 ZGFtinyint ZDFtinyint INSERT T1SELECTCourseNo AVG Score AS平均分 Max SCORE AS最高分 MIN SCORE AS最低分FROMSelectCourseWHEREStuNoLike year GROUPBYCourseNoINSERT cjhzSELECTC CourseNo C CourseName T PJF T ZGF T ZDFFROMCourseCINNERJOIN T1TONC CourseNo T CNoRETURNENDGO USECollegeMISGOSELECT FROMdbo Score Total 96 GO 7 5游标及其使用 通常情况下 执行SELECT语句得到的总是一个记录的集合 大部分SQL语句都把这个记录的集合作为一个整体进行处理 但是 有时用户也需要对这些数据集合中的每一行进行操作 要在服务器端实现该功能 就要用到游标 游标提供了一种在服务器内部处理结果集的方法 它可以识别一个数据集内部指定的行 从而可以有选择地按行采取操作 7 5游标及其使用 7 5 1游标的声明 1 使用SQL 92标准的语法声明游标 格式 DECLAREcursor name INSENSITIVE SCROLL CURSORFORselect statement FOR READONLY UPDATE OFcolumn name n 功能 声明由cursor name作为游标名指定的游标 7 5游标及其使用 7 5 1游标的声明 2 使用Transact SQL扩展语法声明游标 格式 DECLAREcursor nameCURSOR LOCAL GLOBAL FORWARD ONLY SCROLL STATIC KEYSET DYNAMIC FAST FORWARD READ ONLY SCROLL LOCKS OPTIMISTIC TYPE WARNING FORselect statement FORUPDATE OFcolumn name n 功能 声明由cursor name作为游标名指定的游标 例7 13 定义一个游标用来从SelectCourse表中提取数据 此游标为局部 滚动游标 游标数据来源于SelectCourse表 为成绩是优秀的所有记录 USECollegeMISGODECLAREGoodScorecurCURSORLOCALSCROLLFORSELECT FROMSelectCourseWHEREScore 90 7 5游标及其使用 7 5 2游标的打开 关闭与释放 1 打开游标 格式 OPEN GLOBAL cursor name cursor variable name 功能 打开由cursor name作为游标名指定的游标或由游标变量名cursor variable name引用的游标 说明 1 如果游标是静态游标 即是以STATIC选项声明的 OPEN语句将创建一个临时表来放置结果集 如果游标是键值驱动游标 即是以KEYSET选项声明的 OPEN语句将创建一个临时表来放置关键字值 这些临时表都储存在tempdb数据库中 2 打开游标的时候 服务器将执行SELECT语句 获取游标中的数据 3 游标打开后 可以使用全局变量 CURSOR ROWS来返回当前申明的游标可以操作的数据行的数量 取值有四种情况 m 1 0 n 7 5游标及其使用 7 5 2游标的打开 关闭与释放 2 关闭游标和释放游标 格式 CLOSE GLOBAL cursor name cursor variable name 功能 关闭由cursor name作为游标名指定的游标或由游标变量名cursor variable name引用的游标 格式 DEALLOCATE GLOBAL cursor name cursor variable name 功能 释放由cursor name作为游标名指定的游标或由游标变量名cursor variable name引用的游标 例如 CLOSEGoodScorecurDEALLOCATEGoodScorecur 7 5游标及其使用 7 5 3游标的使用 1 使用游标读取数据 格式 FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM GLOBAL cursor name cursor variable name INTO variable name n 功能 从由cursor name作为游标名指定的游标或由游标变量名cursor variable name引用的游标中读取一行数据 说明 1 NEXT PRIOR FIRST LAST NEXT表示返回当前行的下一行 这是默认选项 PRIOR表示返回当前行的上一行 FIRST表示返回数据集的第一行 LAST表示返回数据集的最后一行 2 ABSOLUTE n nvar 返回游标数据集的第n行 如果n为正 从数据集的开头算起 如果n为负 则从数据集的末尾算起 3 RELATIVE n nvar 返回数据集中相对于当前记录的第n行 如果n为负 将从当前行开始往回数 对于SCROLL关键字声明的游标 可以很方便地移动游标的记录指针的位置 有下列语句 OPENGoodScorecur 打开游标 FETCHNEXTFROMGoodScorecur 提取下一条记录 FETCHPRIORFROMGoodScorecur 提取前一条记录 FETCHFIRSTFROMGoodScorecur 提取第一条记录 FETCHLASTFROMGoodScorecur 提取最后一条记录 FETCHABSOLUTE6FROMGoodScorecur 提取第六条记录 FETCHRELATIVE 5FROMGoodScorecur 提取当前记录前面的第五条记录 注意 每次滚动操作都应检查 FETCH STATUS的值 以确保新位置的有效性 若 FETCH STATUS的值不为0 则操作无效 说明 4 FROM 表明下一个关键字是游标 数据就取自该游标 5 INTO variable name n 把从游标中读取的数据保存在变量中以备以后使用 其中变量的数据类型必须与从游标中返回的数据行的相应字段的数据类型完全匹配 否则将产生错误 每次执行FETCH语句后 FETCH语句的执行情况都将在全局变量 FETCH STATUS中反映出来 FETCH STATUS变量有三种取值 0 表示存取成功 1 表示没有取出数据 因为记录指针的位置超出了结果集 和 2 表示取出的行不再是结果集的成员 因此 在对已读取数据行操作之前 可通过检查全局变量 FETCH STATUS的值 检查通过游标取出的数据行的合法性 例7 14 把例7 13中定义的SelectCoursecur游标中的数据按行读出并显示出来 USECollegeMISGODECLARE XHchar 9 JSHChar 6 DECLARE KCHchar 4 CJtinyintDECLAREGoodScorecurCURSORLOCALSCROLLFORSELECT FROMSelectCourseWHEREScore 90OPENGoodScorecurPRINT 学号 教师号 课程号 成绩 FETCHNEXTFROMGoodScorecurINTO XH JSH KCH CJWHILE FETCH STATUS 0BEGINPRINT XH JSH KCH CONVERT char 3 CJ FETCHNEXTFROMGoodScorecurINTO XH JSH KCH CJENDCLOSEGoodScorecurDEALLOCATEGoodScorecurGO 7 5游标及其使用 7 5 3游标的使用 2 使用游标修改数据 若要用游标对基础表的数据行进行更新操作 需要在申明游标时使用UPDATE关键字 用游标修改数据时 须使用 WHERECURRENTOF游标名 来定位要修改的数据行 修改完毕后 游标基础表中相应的数据行同样会得到修改 例7 15 给数据表CollegeMIS的声明一个可更新的游标 游标的数据为SelectCourse表中成绩大于等于90分的记录 并限定可以更新的字段为Score 然后将该字段的值减1 USECollegeMISGODECLARE XHchar 9 JSHChar 6 DECLARE KCHchar 4 CJtinyintDECLAREGoodScorecurCURSORLOCALSCROLLFORSELECT FROMSelectCourseWHEREScore 90FORUPDATEOFScoreOPENGoodScorecur FETCHNEXTFROMGoodScorecurINTO XH JSH KCH CJWHILE FETCH STATUS 0BEGINUPDATESelectCourseSETScore Score 1 根据游标修改表中的数据 WHERECURRENTOFGoodScorecur 定位在修改的数据行 FETCHNEXTFROMGoodScorecurINTO XH JSH KCH CJENDCLOSEGoodScorecurDEALLOCATEGoodScorecurSELECT 显示修改后的表中的相关数据 FROMSelectCourseWHEREScore 90GO 7 5游标及其使用 7 5 3游标的使用 3 使用游标删除数据 若要用游标删除基础表的数据行 需要在申明游标时使用UPDATE关键字 同时由于删除数据行时涉及到数据行的所有字段 因此在UPDATE关键字后应包括所有字段 或省略字段名 用游标删除基础表的数据行时 须使用 WHERECURRENTOF游标名 来定位要删除的数据行 删除完毕后 游标基础表中相应的数据行同样会得到删除 例7 16 给数据表CollegeMIS的声明一个可更新的游标 游标的数据为SelectCourse表中成绩大于等于90分的记录 然后利用该游标将基础表中的所有大于等于90分的记录删除 USECollegeMISGODECLARE XHchar 9 JSHChar 6 DECLARE KCHchar 4 CJtinyintDECLAREGoodScorecurCURSORLOCALSCROLLFORSELECT FROMSelectCourseWHEREScore 90FORUPDATEOPENGoodScorecurFETCHNEXTFROMGoodScorecurINTO XH JSH KCH CJ WHILE FETCH STATUS 0BEGINDELETEFROMSelectCourse 根据游标删除表中的数据 WHERECURRENTOFGoodScorecur 定位在删除的数据行 FETCHNEXTFROMGoodScorecurINTO XH JSH KCH CJENDCLOSEGoodScorecurDEALLOCATEGoodScorecurSELECT 显示删除后的表中的相关数据 FROMSelectCourseWHEREScore 90 显示结果为空 GO 7 6存储过程及其使用 7 6 1存储过程概述 存储过程是在SQLServer服务器上创建 运行的过程 用来完成特定的任务 它可以作为一个独立的数据库对象存储在数据库中 也可以作为一个单元供用户在应用程序中调用 由于存储过程只在第一次执行时编译 以后执行时无需编译 因此语句的执行速度较快 存储过程在创建以后 可以被多次调用 可以将经常执行的操作编写成存储过程 在以后的操作中多次调用 而不必重新书写语句 因此利用它可以提高工作效率 存储过程中可以用输入参数来接受调用程序的输入 可以用输出参数把处理结果返回给调用程序 存储过程还可以嵌套调用 存储过程执行完毕后 是否执行成功还可以通过存储过程的状态参数来查询 7 6存储过程及其使用 7 6 2存储过程的创建与管理 1 创建存储过程 1 使用企业管理器创建存储过程 例7 17 为CollegeMIS数据库创建一个名称为P1 XSCX的存储过程 该存储过程返回所有计算机应用专业的男学生的基本情况 包括学号 姓名 身份证号 性别 电话号码等信息 SELECTStuNoAS学号 StuNameAS姓名 StuIDAS身份证号 SexAS性别 TelephoneAS电话号码FROMStudentWHEREMajor 计算机应用 ANDSex 男 7 6存储过程及其使用 7 6 2存储过程的创建与管理 1 创建存储过程 2 使用查询分析器执行Transact SQL语句创建存储过程 格式 CREATEPROCEDUREprocedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 功能 创建由 procedure name 作为过程名指定的存储过程 例7 18 为CollegeMIS数据库创建一个名称为P2 CJCX的存储过程 该存储过程返回所有计算机应用专业的学生的学习情况 包括学号 姓名 课程号 课程名和成绩信息 USECollegeMISGOCREATEPROCEDUREP2 CJCX 创建存储过程 ASSELECTS StuNoAS学号 S StuNameAS姓名 C CourseNoAS课程号 C CourseNameAS课程名 SC ScoreAS成绩FROMStudentS CourseC SelectCourseSCWHERES StuNo SC StuNoANDS Major 计算机应用 ANDC CourseNo SC CourseNo 7 6存储过程及其使用 7 6 2存储过程的创建与管理 2 执行存储过程 2 使用查询分析器执行Transact SQL语句创建存储过程 格式 EXECUTE return status procedure name number procedure name var parameter value variable OUTPUT DEFAULT n WITHRECOMPILE 功能 执行由 procedure name 作为过程名指定的存储过程或由局部变量 pprocedure name var 引用的存储过程 例如 EXECUTEP2 CJCX 7 6存储过程及其使用 7 6 2存储过程的创建与管理 3 查看存储过程 1 通过企业管理器查看存储过程信息 格式 EXEC sp help 例如 EXECsp helpP1 XSCX 2 使用系统存储过程查看存储过程信息 功能 查看 存储过程名 指定的存储过程的概要信息 格式 EXEC sp helptext 例如 EXECsp helptextP1 XSCX 功能 查看 存储过程名 指定的存储过程的定义文本信息 7 6存储过程及其使用 7 6 2存储过程的创建与管理 4 存储过程的修改 2 使用查询分析器执行Transact SQL语句创建存储过程 格式 ALTERPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 功能 修改由procedure name作为存储过程名指定的存储过程 例 要想P2 CJCX存储过程不显示课程号 则可使用如下语句对该存储过程进行修改 ALTERPROCEDUREP2 CJCX 修改存储过程 ASSELECTS StuNoAS学号 S S