搜索
——数据库应用教程本章主要内容常用内置函数变量与表达式程序控制语句游标及其使用用户自定义函数主要内容存储过程及其使用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字段名1，……，字段名n，@变量名=表达式FROM表名WHERE条件【功能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）：xaANDyb'结果为FALSEabANDxy'结果为TRUENOT(ab)ANDNOT(xy)'结果为FALSE7.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语句1……语句nEND【功能】把“语句1”~“语句n”的多条语句组合成一个语句块，使它们能够整体执行。7.3程序控制语句7.3.2IF…ELSE语句【格式】IF条件语句1[ELSE语句2]【功能】如果条件为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表达式【格式】CASE比较表达式WHEN比较值1THEN结果表达式1[WHEN比较值2THEN结果表达式2…WHEN比较值nTHEN结果表达式n][ELSE结果表达式n+1]END【功能】用“比较表达式”的值依次与WHEN子句中的“比较值1”、“比较值2”、…、“比较值n”比较，直到找到第一个与“比较表达式”的值完全相同的“比较值”时，便将对应WHEN子句指定的“结果表达式”作为CASE表达式的值返回。【例7-4】从SelectCourse表中查询出所有学生的选课信息，包括学号、课程名（用课程名代替课号）和成绩，并按成绩降序排列。USECollegeMISGOSELECTStuNo,CourseNo=CASECourseNoWHEN'0001'THEN'计算机应用基础'WHEN'0002'THEN'程序设计基础'WHEN'0003'THEN'操作系统'ELSE'计算机网络技术'END,SCOREFROMSelectCourseORDERBYScoreDESC7.3程序控制语句7.3.3CASE表达式2．搜索CASE表达式【格式】CASEWHEN逻辑表达式1THEN结果表达式1[WHEN逻辑表达式2THEN结果表达式2……WHEN逻辑表达式nWHEN结果表达式n][ELSE结果表达式n+1]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'不及格'ENDFROMSelectCourseORDERBYStuNoDESC7.3程序控制语句7.3.4WHILE语句【格式】WHILE逻辑表达式语句【功能】首先测试“逻辑表达式”的值，如果为TRUE，则执行“语句”（即循环体）；然后再测试“逻辑表达式”，如果为TRUE，再执行语句；……；直到某次测试“逻辑表达式”的值时，发现该表达式的值为FALSE时，退出循环，执行WHILE语句后面的语句。7.3程序控制语句7.3.4WHILE语句【格式】WHILE逻辑表达式BEGIN语句块1……[CONTINUE]……语句块i……[BREAK]……语句块nEND【功能】首先测试“逻辑表达式”的值，如果为TRUE，则执行BEGIN……END语句块（即循环体）；然后再测试