T-SQL程序设计基础

第9章T-SQL程序设计根底9.1SQL语言和T-SQL语言9.2T-SQL的标识符9.3T-SQL的数据类型9.4T-SQL的运算符及表达式9.6T-SQL中的语句9.5T-SQL中的变量9.7T-SQL函数SQL与T-SQLSQL(结构化查询语言〕-数据库能够识别指令、执行对应的操作并为程序提供数据的指令集。T-SQL是SQL的加强版。T-SQL由数据定义语言〔DDL〕、数据操纵语言〔DML〕、数据控制语言〔DCL〕组成。T-SQL语句的语法格式规定：〔1〕语句中的字母大小写均可；〔2〕关键字有特殊用途，定义变量名时不得使用关键字；〔3〕语句中的日期型常量和字符型常量必须用单引号括起来；〔4〕语句中的标点符号必须用英文标点，即半角符号；〔5〕一条语句可以分行写，一行也可以写多条语句，语句末尾不写任何标点。标识符标识符是指用户在SQLServer中定义的效劳器、数据库、数据库对象、变量和列等对象名称。SQLServer标识符分为规那么标识符和定界标识符两类。规那么标识符规那么标识符是严格按标识符命名规那么定义的标识符，不需要任何定界符号括起来。定界标识符定界标识符是使用定界符号[]或“”将标识符括起来的标识符。使用了定界标识符，既可以遵守标识符的命名规那么，也可以不遵守标识符命名规那么。标识符标识符命名规那么1．标识符必须以字母、_(下划线)、@或#开头；2．标识符可以是字母、数字、_、@、#或$的组合；3．标识符不允许使用保存字；4．标识符不允许使用除_、@、#或$之外的其他符号，例如：不能使用空格符号；5．标识符大小写等价。特殊标识符某些以特殊符号打头的标识符在T-SQL中具有特殊意义。例如：以#开头的标识符表示临时表；以@开头的标识符表示局部变量；以@@开头的标识符表示全局变量；提示：全局变量由系统提供，用户不要定义以@@开头的标识符。T-SQL数据类型语法存储长度适用范围整型数据类型Int4个字节-231~（231-1）内所有正负整数Smallint2个字节-215~（215-1）内所有正负整数Tinyint1个字节0~255范围的所有正整数bigint8个字节-263~（263-1）T-SQL数据类型语法存储长度适用范围备注浮点数据类型Real4个字节精确到7位小数存储十进制小数。Float8个字节最多可精确到15位小数Decimal实际存储空间Decimal（p,s）,p表示总位数，s表示小数点后的位数。0<=s<=p<=38。例如：decimal(8,6)则范围(-99.999999~99.999999）NumericT-SQL数据类型语法存储长度适用范围备注货币型Money8个字节用于存储货币精确度为万分之一Samllmoney4个字节范围比Money小精确度为万分之一位型Bit1个字节常用作逻辑变量表示真假只能输入0与1,非此值当作1T-SQL数据类型语法存储长度适用范围备注日期与时间型Datetime8个字节精度三百分之一秒，即3.33毫秒。MMDDYYYYhh:mmAM/PM1753.1.1~9999.12.3123:59:59存储日期和时间的结合体,引用时用单引号Smalldatetime4个字节精度1分钟1900.1.1~2079.6.6T-SQL数据类型语法存储长度适用范围备注二进制型Binary(n)取决于定义固定长度的二进制数据N为1~8000，最后用检索输出的是二进制Varbinary(n)N+4字节二进制数据的长度未知或变化较大时可用存放8000字节内可变长数据T-SQL数据类型语法存储长度适用范围备注文本型Text实际大小最大可存储231-1存储长度大于8000个字节的二进制数据Ntext实际大小最大可存储230-1存储长度大于4000个字符的unicode字符串图形image实际大小最大可存储231照片、图、画存储长度大于8000个字符的字符串T-SQL数据类型语法存储长度适用范围备注字符型Char（n）N字节固定长度的非Unicode字符数据，输入字符少于n,以空格填满。若超长则截掉。N为1-8000范围Varchar(n)实际长度N为最大长度小于N时不加空格可节省空间Nchar(n)N单位Unicode标准，两个字节为存储单位，容纳量增加了。N为1-4000范围Nvarchar(n)存储大小是输入数据的实际长度T-SQL数据类型语法存储长度适用范围备注文本型Text实际大小最大可存储231-1存储长度大于8000个字节的二进制数据Ntext实际大小最大可存储230-1存储长度大于4000个字符的unicode字符串图形image实际大小最大可存储231照片、图、画存储长度大于8000个字符的字符串T-SQL运算符和表达式算术运算运算符：+，-，*，/、%字符串运算运算符：+〔表示两个字符串的连接〕关系运算运算符：>、<、>=、<=、=、<>、!>、!<等逻辑运算运算符：and、or、not优先级：not>and>or位运算运算符：~〔取反〕、&〔按位与〕、^〔按位异或〕、 |〔按位或〕优先级：~>&>^>|赋值运算：<变量>=<表达式>单目运算符单目运算运算符指只有一个操作数的运算符，包括+〔正〕、-〔负〕和~〔位反〕。例如：DECLARE@intNumINTSET@intNum=10SELECT-@intNum返回结果为-10T-SQL运算符和表达式T-SQL中的常量和变量常量是指在程序运行过程中，其值不会改变的量。字符型常量如：‘abcde’整型常量如：11，70，1200等实型常量如：3.14，3.5等日期型常量如：6/25/83，may192000等货币常量如：$1000等变量是指在程序运行过程中，其值会改变的量。常用来保存程序中的输入数据、中间结果和输出数据。变量变量三要素：变量名、变量类型、变量值。变量按数据类型可划分为：整型数据类型、货币数据类型、实数类型、日期时间类型、字符串类型等变量按作用域范围可划分为：局部变量和全局变量局部变量概念：用户自定义的变量，只能在定义它的程序中使用。特点：必须先定义后使用；默认值为NULL；程序中使用时，先赋值再使用。定义方法：declare@变量名

数据类型,……例如：declare@xint/*定义一个int型变量*/局部变量赋值方法：SET语句的根本语句格式如下：SET@变量名=表达式SELECT语句格式如下：SELECT@变量名=表达式[FROM<表名>WHERE<条件>]例如：DECLARE@quantityINT SET@quantity=210全局变量全局变量属于系统变量，不能由用户定义，全局变量不可以赋值，可在任何程序中使用。常用的全局变量有：１〕@@ERROR：返回最后执行的T-SQL语句的错误代码，返回类型为integer。２〕@@ROWCOUNT：返回受上一语句影响的行数，除了DECLARE语句外，其他任何语句都可以改变其值。３〕@@IDENTITY：返回最后插入的标识值，返回类型为numeric。T-SQL中的语句注释语句显示输出语句批处理语句Begin…end语句条件语句循环语句返回语句注释是程序代码中不执行的文本字符串。在SQLServer中，可以使用两种类型的注释字符：“--”用于单行注释；“/**/”用于多行注释。例如：/*查询商品信息*/SELECT*FROM学生--从学生表中查询信息GO注释

显示和输出语句1.PRINT语句PRINT语句用来在屏幕上输出相关信息。根本语句格式如下：PRINT'anyASCIItext'|@local_variable|@@FUNCTION|string_expr2.select语句一次可输出假设干个局部变量值或表达式的值。根本语句格式如下：select@局部变量名1[,……]|表达式1[,……]批处理多条语句作为一个批处理执行时，其语句之间用GO分隔。USE学生_课程--翻开数据库GO/*一个批处理结束*/declare@nameIDchar(8)SELECT@nameID=学号FROM学生where姓名='李明'select@nameIDGO

批处理例如：USE学生_课程GOCREATEVIEWaw学生asselect*from学生GOSELECT*FROMaw学生GOBEGIN…END语句BEGIN…END语句该语句用于将多条T-SQL语句封装起来，构成一个语句块，它用在IF…ELSE、WHILE等语句中，使语句块内的所有语句作为一个整体被执行。BEGIN…END语句可以嵌套使用。BEGIN…END的根本语句格式如下：BEGIN {SQL语句｜语句块}END条件语句IF…ELSE语句是条件判断语句，其中，ELSE子句是可选的，最简单的IF语句没有ELSE子句局部。IF…ELSE的根本语句格式如下：IF<布尔表达式> {SQL语句｜语句块}[ELSE {SQL语句｜语句块}]IF…ELSE语句的执行方式是：如果布尔表达式的值为True，那么执行IF后面的语句块；否那么，执行ELSE后面的语句块。多分支结构CASE…END表达式格式1〕Case测试表达式 when常量值1then结果表达式1 when常量值2then结果表达式2 …… when常量值nthen结果表达式n 【else 结果表达式n+1】 end功能：根据测试表达式的值得到一个对应值。

多分支结构2、格式二casewhen条件表达式1then语句1when条件表达式2then语句2……when条件表达式nthen语句nelse语句n+1end例题例题9-4

根据职工的现有工资值，给职工增加工资。将增加后的工资值存入变量@x中，并且输出@x的值。declare@salaryint,@xintset@salary=2900set@x=casewhen@salary>=1500and@salary<2000then@salary*1.2when@salary>=2000and@salary<2500then@salary*1.4when@salary>=2500and@salary<3000then@salary*1.6else5000endprint@x此题的运行结果：46403.循环语句WHILE的根本语句格式如下：while条件表达式begin<T-SQL命令或语句块>[break][continue]end功能：假设条件表达式成立，那么反复执行begin…end之间的语句。〔1〕条件表达式不需要加括号；〔2〕循环可以嵌套使用；〔3〕break语句的作用是无条件跳出本层循环；〔4〕continue语句的作用是结束本次循环，重新判定循环条件；〔5〕break和continue语句常常和if~else语句配合使用【例】计算1+2+3+……+100的和DECLARE@iInt,@sumIntSELECT@i=1,@sum=0--可以使用两个SET语句WHILE@i<=100SELECT@sum=@sum+@i,@i=@i+1PRINT@sum

注意：循环体内只有一个语句可不用BEGIN…END。例题9-5求1！+2！+3！+……+20！declare@sumbigint,@tbigint,@iint,@jintset@sum=0set@i=1while@i<=20beginset@j=1set@t=1while@j<=@ibeginset@t=@t*@jset@j=@j+1endset@sum=@sum+@tset@i=@i+1endprint@sum此题的运行结果：Declare@iintSet@i=0SELECT*FROM选课while@i<=@@ROWCOUNTBEGINifexists(SELECT*FROM选课WHERE成绩>=60)beginSELECT*FROM选课WHERE成绩>=60SELECT'成绩超过60，不需要补考'endelsebeginSELECT*FROM选课WHERE成绩<60PRINT'成绩低于60，需要补考'endset@i=@i+1END返回语句RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN语句的根本语句格式如下：RETURN[integer_expression]其中，参数integer_expression为返回的整型值。系统内置函数如同其他编程语言一样，T-SQL语言也提供了丰富的数据操作函数，常用的函数有：１〕数据转换函数２〕字符串函数３〕日期和时间函数４〕数学函数５〕系统函数。本章首页函数询汇总函数1.AVG()2.COUNT()3.MIN()4.MAX()5.SUM()类型转换函数类型转换函数是将表达式的结果从一种数据类型转换为另外一种数据类型，比方将数值类型转换为字符类型等CONVERT(<数据类型>[(<长度>)],<表达式>[,日期格式])字符串函数ASCII()返回字符串表达式最左面字符的ASCII码值CHAR()把一个表示ASCII代码的数值转换成对应的字符CHARINDEX()返回一个子串在字符串表达式中的起始位置PATINDEX()返回一个子串在字符串表达式中的起始位置，在子串中可以使用通配符‘%’，这个函数可以用在TEXT、CHAR和VARCHAR类型的数据上DIFFERENCE()返回两个字符串的匹配程度SOUNDEX()返回两个字符串发音的匹配程度LOWER()把大写字母转换成小写字母字符串函数〔续〕UPPER()将小写字母转换成大写字母LTRIM()删除字符串的前导空格RTRIM()删除字符串的尾部空格REPLICATE()重复一个字符表达式若干次REVERSE()取字符串的逆序SPACE()产生空格字符串STR()将数值转换成字符串STUFF()用一个子串按规定取代另一个子串RIGHT()从字符的右部取子串SUBSTRING()取子串函数字符串函数求字符串字符个数函数能返回给定字符串表达式的字符个数，其中不包含尾随空格。语法：LEN〔string_expression〕参数：string_expression是要计算的字符串表达式。返回值即字符的个数，为int数据类型。字符串函数取子串函数LEFT()、RIGHT()及SUBSTRING()函数都能从字符串中取出子串，只是实现方法不同。返回空格函数SPACE()函数：返回由重复的空格组成的字符串。语法：SPACE(integer_expression)参数：integer_expression是表示空格个数的正整数。如果integer_expression为负，那么返回空字符串。返回值即是空格字符串，为char数据类型。字符串函数(3)大小写转换函数UPPER()函数将小写字符转换为大写字符LOWER()函数那么将大写字符转换为小写字符。删除空格函数LTRIM()和RTRIM()函数分别用于删除字符串的左部空格和右部空格。数字转换字符函数STR()函数：将数字数据转换成字符数据。ABS()SIN()COS()TAN()COT()ASIN()ACOS()绝对值函数正弦函数余弦函数正切函数余切函数反正弦函数反余弦函数本章首页ATAN()CEILING()FLOOR()ROUND()DEGREES()RADIANS()EXP()反正切函数向上取整函数向下取整函数四舍五入函数将弧度转换成角度将角度转换成弧度指数函数LOG()LOG10()PI()POWER()SQRT()RAND()SIGN()自然对数函数以10为底的对数函数圆周率乘方函数平方根函数产生一个随机数返回+1、0或-1数学函数(1)数学函数(1)返回整数值函数CEILING与FLOOR函数都用于返回数值表达式的整数值，但返回的值不同。乘方运算函数POWER(数值表达式1，数值表达式2)自然指数函数语法：EXP(float表达式)求指定的float表达式的自然指数值，返回float型的值。平方根函数语法：SQRT(float表达式)求指定的float表达式的平方根，返回float型的值。数学函数(2)产生随机数函数用于返回一个位于0和1之间的随机数。语法：RAND(整数表达式)整型表达式在这里起着产生随机数的起始值的作用。四舍五入函数语法：ROUND(数值表达式，整数)该函数将数值表达式四舍五入成整数指定精度的形式。在这里，整数可以是正数或负数。正数表示要进行运算的位置在小数点后，反之要运算的位置在小数点前。日期和时间函数(1)日期〔date〕函数涉及与日期计算有关的一些功能命令格式DATEADD()在一个日期值上加上个间隔，返回值仍是日期值DATEDIFF()计算两个日期值之间的间隔DATENAME()返回表示日期中某部分的字符串DATEPART()返回表示日期中某部分的数值GETDATE()返回系统日期和时间日期和时间函数(1)返回当前的系统时间函数GETDATE()：返回当前的系统时间。返回日期时间的指定局部函数DATEPART和DATENAME函数都能返回给定日期的指定局部，如：年、月、日等。改变数值后的日期时间函数DATEADD函数在指定日期时间的根底上加一段时间，返回新的日期时间值。Dateadd(datepart,number,date)日期和时间函数(2)求两日期时间之间的差值函数DATEDIFF函数：返回开始日期和结束日期在给定日期局部上的差值。datediff(datepart,startdate,enddate)日期和时间函数(3)Datepart参数可用的值Year yy,yyyyquarter qq,qMonth mm,mdayofyear dy,yDay dd,dWeek wk,wwHour hhminute mi,nsecond ss,smillisecond msUSE图书_读者DECLARE@datevarchar(15),@日期varchar(15)SET@date=getdate()--也可使用SELECTSELECT@日期='当前日期为：'--也可使用SETPRINT@日期+@date--不能用PRINT@日期，@datePRINT''--输出空串可以空行SET@date=(SELECTMAX(借阅日期)FROM借阅)PRINT'最迟的借书日期为：'+@dateGO类型转换函数CAST和CONVERT函数能将某种数据类型的表达式显式转换为另一种数据类型CAST和CONVERT提供相似的功能，但CONVERT功能更强一些。CAST(expressionASdata_type)CONVERT(data_type[(length)],expression[,style])9.3存储过程SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库效劳器来完成，以实现某个任务，这种方法就是存储过程。存储过程具有如下优点：1〕加快系统运行速度。2〕当对数据库进行复杂操作时〔如对多个表进行Update、Insert、Query、Delete时〕，可将此复杂操作用存储过程封装起来与数据库提供的事务处理一起使用。3〕存储过程可以重复使用，可减少数据库开发人员的工作量。存储过程具有如下优点：4〕平安性高，可设定只有某用户才具有对指定存储过程的使用权。5〕减少网络流量。存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。1〕系统提供的存储过程：在安装SQLServer时，系统创立了很多系统存储过程。系统存储过程主要用于从系统表中获取信息，系统存储过程的名字都以“sp_”为前缀。2〕用户定义的存储过程：是由用户为完成某一特定功能而编写的存储过程。3〕扩展存储过程：是对动态链接库〔DLL〕函数的调用。使用命令方式创立和执行存储过程1.创立存储过程创立存储过程的根本语句格式如下：CREATEPROCEDURE存储过程名[{@参数1数据类型}[=默认值][OUTPUT]，……，{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句……2.使用输入参数在创立存储过程时可以使用输入参数，并给参数指定默认值，这样，调用存储过程时相应参数可以不赋值。3.使用输出参数输出参数用于在存储过程中返回值，使用OUTPUT声明输出参数。4.存储过程的返回值存储过程可以用return语句返回值。(1)创立存储过程(MS)CREATEPROCEDURE〈过程名〉[;〈版本号〉][@〈参数名〉〈参数类型〉[=〈缺省值〉][OUTPUT]…]

[WITHRECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION]

AS〈SQL语句组〉

1)版本号是整数，它用于将有相同名字的存储过程编为不同的组。2)OUTPUT用于给调用者返回值。3)RECOMPILE为重编译。4)ENCYPTION为加密选项。5)参数前加“@”为局部变量，加“@@”那么说明为全局变量。

调用存储过程(MS)(3)过程的调用语句为：EXE[UTE]〈过程名〉[[@〈参数名〉=]〈参数〉…[〈版本号〉](2)删除存储过程DROPPROCEDURE〈存储过程名组〉例包含SELECT语句的存储过程。

use学生_课程gocreateprocedurep1asselect*from选课where成绩>70执行：execp1带参数的过程CREATEPROCEDURE存储过程名([@参数名数据类型[=default][output])ASSQL语句例9.2创立带精确匹配参数的存储过程。CREATEprocedurep2(@cxint〕 as select*from选课 where成绩>=@cxGO运行存储过程：Executep270例:创立带通配符参数的存储过程。use学生_课程goCREATEprocedurep3(@xmvarchar(10)=‘%李明‘)asselect*from学生where姓名like@xmGO执行方式1、execp3--使用缺省值2、execp3张小红—指定参数3、execp3‘李%’4、declare@ccvarchar(10)set@cc='%明'executep3@cc使用企业管理器创立存储过程利用企业管理器创立存储过程步骤如下：1〕展开要在其中创立存储过程的数据库，右击【存储过程】图标弹出快捷菜单，如以下图所示。2〕在快捷菜单中选择【新建存储过程】选项，翻开【新建存储过程】对话框，在其文本框中书写存储过程定义，如以下图所示。3〕定义完存储过程后，单击【确定】按钮保存存储过程。查看存储过程时，可以在前面选择【新建存储过程】图的右边窗格中选择指定的存储过程，如以下图所示，单击右键选择【属性】选项，即可查看指定存储过程的详细内容。删除存储过程时，可以在该图所示的快捷菜单中选择【删除】选项。使用命令方式修改和删除存储过程1.修改存储过程ALTERPROCEDURE根本语句格式如下：CREATEPROC[EDURE]存储过程名[{@参数1数据类型}[=默认值][OUTPUT]，……，{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句……2.删除存储过程使用DROPPROCEDURE可以删除存储过程，其根本语句格式如下：DROPPROCEDURE存储过程名9.6游标的创立与使用、游标的概念SELECT语句是对数据表的整行〔记录〕整列〔字段〕数据进行操作的，其结果是假设干行假设干列的“结果集”—也是一张“表格”，而不是针对某个特定的数据项进行操作。在我们实际需要中，尤其在应用程序中，并不能把“表格”作为一个整体进行处理，通常使用数组表示同种类型的某一“列”数据，使用结构体或记录类表示多个不同类型的某一“行”数据，使用结构体或记录数组可以表示一张表。

游标的概念我们如何把数据表中的某一行、某一列的一个数据项从一个完整的表中提取出来呢？游标的主要用途就是在T-SQL脚本程序、存储过程、触发器中对SELECT语句返回的结果集进行逐行逐字段处理，把一个完整的数据表按行分开，一行一行的逐一提取记录，并从这一记录行中逐一提取各项数据。游标与变量类似，必须先定义后使用。游标的使用过程：定义声明游标翻开游标从游标中提取记录并别离数据关闭游标释放游标。、用DECLARE语句定义游标1、基于SQL-92标准的DECLARE语句

DECLARE游标名

[INSENSITIVE][SCROLL]

CURSORFORSELECT语句

[FOR{READONLY|UPDATA[OF字段名[,…n]]}]SELECT指定该游标使用的结果集，不允许使用COMPUTE或INTO子句。READONLY表示只读，该游标中的数据不允许修改，即不允许在UPDATE或DELETE语句中引用该游标。UPDATA[OF字段名[,…n]]表示在该游标内可以更新根本表的指定字段，省略字段名列表表示可以更新所有字段。INSENSITIVE定义游标时自动在系统tempdb数据库中创立一个临时表存储游标使用的数据，在游标使用过程中基表数据改变不影响游标的数据，但该游标的数据不允许修改。省略该项表示游标直接从基表中取得数据，即游标使用的数据将随基表数据的变化而动态变化。SCROLL表示该游标可以在FETCH语句中任意指定数据的提取方式，省略该项表示该游标仅支持NEXT顺序提取方式。、用DECLARE语句定义游标2、SQLServer2000T-SQL中的扩展DECLARE语句

DECLARE游标名CURSOR[FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|OPTIMISTIC][TYPE_WARNING]

FORSELECT语句[FORUPDATE[OF字段名[,…n]]]FORWARD_ONLY指定该游标为顺序结果集，只能用NEXT向前方式顺序提取记录。SCROLL指定该游标为滚动结果集，可以使用向前、向后、定位方式提取记录。STATIC与INSENSITIVE含义相同，在系统tempdb数据库中创立临时表存储游标使用的数据，即游标不会随根本表内容而变化，同时也无法通过游标来更新根本表。例：定义一个学生游标DECLARE学生游标cursorFORSELECT学号,姓名,性别,年龄,所在系FROM学生WHERE年龄<30游标的一些参数KEYSET指定游标中列的顺序是固定的，并且在tempdb内建立一个KEYSET表，根本表数据修改时能反映到游标中。如果根本表添加符合游标的新记录时该游标无法读取(但其他语句使用WHERECURRENTOF子句可对游标中新添加的记录数据进行修改)。如果游标中的一行被删除掉，那么用游标提取时@@FETCH_STATUS的返回值为-2。

DYNAMIC指定游标中的数据将随根本表而变化，但需要大量的游标资源。FAST_FORWARD指定FORWARD_ONLY而且READ_ONLY类型游标。使用FAST_FORWARD参数那么不能同时使用FORWARD_ONLY、SCROLL、OPTIMISTIC或FORUPDATE参数。OPTIMISTIC指明假设游标中的数据已发生变化，那么对游标数据进行更新或删除时可能会导致失败。

TYPE_WARNING指定假设游标中的数据类型被修改成其他类型时，给客户端发送警告。、用DECLARE语句定义游标假设省略FORWARD_ONLY|SCROLL那么不使用STATIC、KEYSET和DYNAMIC时默认为FORWARD_ONLY游标，使用STATIC、KEYSET或DYNAMIC之一那么默认为SCROLL游标。

假设省略READ_ONLY|OPTIMISTIC参数，那么默认选项为：

如果未使用UPDATE参数不支持更新，那么游标为READ_ONLY；

STATIC和FAST_FORWARD类型游标默认为READ_ONLY；DYNAMIC和KEYSET类型游标默认为OPTIMISTIC。注意：

不能将SQL-92游标语法与MSSQLServer游标的扩展语法混合使用。假设在CURSOR前使用了SCROLL或INSENSITIVE那么为SQL-92游标语法，那么不能再在CURSOR和FORSELECT语句之间使用任何保存字，反之同理。、用OPEN语句翻开游标语法格式：OPEN[GLOBAL]游标名语句功能：翻开指定的游标如果全局游标与局部游标同名时，GLOBAL表示翻开全局游标，省略为翻开局部游标。用DECLARE定义的游标，必须翻开以后才能对游标中的结果集进行处理。就是说DECLARE只声明了游标的结构格式，翻开游标才执行SELECT语句得到游标中的结果集。注意翻开游标后，可以使用全局变量〔系统的无参函数〕@@ERROR判断该游标是否翻开成功。@@ERROR为0那么翻开成功，否那么翻开失败。使用@@CURSOR_ROWS可得到翻开游标中当前存在的记录行数，其返回值为：

0：表示无符合条件的记录或该游标已经关闭或已释放

-1：表示该游标为动态的，记录行经常变动无法确定n：正整数n表示指定结果集已从表中全部读入，总共n条记录-m：表示指定的结果集还没全部读入，目前游标中有m条记录、用FETCH语句从游标中提取数据FETCH[next|prior|first|last|absolute{n|@nvar}|relative{n|@nvar}]FROM[GLOBAL]游标名[INTO@变量名[,…n]]说明：在游标内有一个游标指针CURSOR指向游标结果集的某个记录行—称为当前行，游标刚翻开时CURSOR指向游标结果集第一行之前。例:FETCHabsolute2FROM学生游标into@xm,@xb,@nl,@bm--提取第二条记录中的姓名,性别,年龄,所在系到局部变量中记录指针的移动后移2条记录:FETCHrelative2FROM雇员游标into@xm,@xb,@nl,@bm提取下一条记录FETCHnextFROM雇员游标into@xm,@xb,@nl,@bm提取记录的方式NEXT顺序向下提取当前记录行的下一行，并将其作为当前行。第一次对游标操作时取第一行为当前行，处理完最后一行，再用FETCHNEXT那么CURSOR指向结果集最后一行之后，@@FETCH_STATUS的值为-1。PRIOR顺序向前提取当前记录的前一行，并将其作为当前行。第一次用FETCHPRIOR对游标操作时，没有记录返回，游标指针CURSOR仍指向第一行之前。FIRST提取游标结果集的第一条记录，并将其作为当前行。LAST提取游标结果集的最后一条记录，并将其作为当前行。、用FETCH语句从游标中提取数据ABSOLUTE{n|@nvar}按绝对位置提取游标结果集的第n或第@nvar条记录，并将其作为当前行。假设n或@nvar为负值那么提取结尾之前的倒数第n或第@nvar条记录。n为整数，@nvar为整数类型变量。

RELATIVE{n|@nvar}按相对位置提取当前记录之后〔正值〕或之前〔负值〕的第n或第@nvar条记录，并将其作为当前行。其它子句FROM指定提取记录的游标，global用于指定全局游标，省略为局部游标。

INTO指定将提取记录中的字段数据存入对应的局部变量中。变量名列表的个数、类型必须与结果集中记录的字段的个数、类型相匹配。、用FETCH语句从游标中提取数据翻开游标用FETCH提取记录后，可用@@FETCH_STATUS检测游标的当前状态。@@FETCH_STATUS的返回值为：0：FETCH语句提取记录成功-1：FETCH语句执行失败或提取的记录不在结果集内-2：被提取的记录已被删除或根本不存在注意：@@FETCH_STATUS只能检测游标提取记录后的状态，假设用作循环条件输出多条记录时，必须在循环之前先用FETCH提取一条记录，再用@@FETCH_STATUS判断提取记录是否成功，以确定是否进行循环。、用CLOSE语句关闭游标语法格式：CLOSE[GLOBAL]游标名语句功能：释放游标中的结果集，解除游标记录行上的游标指针。当游标提取记录完毕后，应及时关闭该游标释放结果集的内存空间。游标关闭后，其定义结构仍然存储在系统中，但不能提取记录和定位更新，需要时可用OPEN语句再次翻开。注意：关闭只有定义而没有翻开的游标会产生语法错误。、用DEALLOCATE语句释放游标语法格式：DEALLOCATE[GLOBAL]游标名语句功能：删除指定的游标，释放该游标所占用的所有系统资源。

deallocate学生游标DECLARE@nameCHAR(8),@agesmallint,@sexchar(2),@departchar(30)DECLARE学生游标CURSORforSELECT姓名,年龄,性别,所在系FROM学生,选课where学生.学号=选课.学号open学生游标FETCHFROM学生游标into@name,@age,@sex,@depart--先提取第一条记录WHILE@@FETCH_STATUS=0BEGINprint@name+'今年'+cASt(@ageaschar(2))+'岁，性别是'+@sex+','+'所在系是'+@departFETCHnextFROM学生游标into@name,@age,@sex,@departENDCLOSE学生游标【实例练习】使用游标逐条查看《学生》中的信息，用变量输出各项数据，并统计被查询学生人数及平均年龄。USE学生_课程DECLARE学生游标cursorkeySET--定义游标FORSELECT姓名,性别,年龄,所在系FROM学生OPEN学生游标--翻开游标IF@@error=0--判断游标翻开成功BEGINIF@@cursor_rows>0--判断游标结果集记录个数大于0【实例练习】BEGINPRINT'游标记录数为：'+convert(varchar(2),@@cursor_rows)DECLARE@xmvarchar(8),@xbnchar,@nlint,@bmnvarchar(5),@rsint,@pjnlintFETCHabsolute2FROM学生游标into@xm,@xb,@nl,@bm--提取记录PRINT'第2条记录:'+@xm+@xb+cASt(@nlASchar(2))+@bmFETCHrelative2FROM学生游标into@xm,@xb,@nl,@bmPRINT'后移2条记录:'+@xm+@xb+cASt(@nlASchar(2))+@bmSET@rs=0SET@pjnl=0PRINT'全部记录为：'FETCHfirstFROM学生游标into@xm,@xb,@nl,@bm--先提取第一条记录WHILE@@FETCH_STATUS=0BEGINPRINTcASt(@rs+1ASchar(2))+':'+@xm+@xb+cASt(@nlASchar(2))+@bmFETCHnextFROM学生游标into@xm,@xb,@nl,@bmSET@rs=@rs+1SET@pjnl=@pjnl+@nlENDPRINT'实际统计记录数为：'+cASt(@rsASchar(2))+'平均年龄为：'+cASt(@pjnl/@rsASchar(6))END【实例练习】ENDELSEPRINT'游标翻开失败！'CLOSE学生游标--关闭游标DEALLOCATE学生游标--删除游标USE学生_课程DECLARE学生游标cursorkeySET--定义游标FORSELECT姓名,性别,年龄,所在系FROM学生OPEN学生游标--翻开游标IF@@error=0--判断游标翻开成功BEGINIF@@cursor_rows>0--判断游标结果集记录个数大于0BEGINPRINT'游标记录数为：'+convert(varchar(2),@@cursor_rows)DECLARE@xmvarchar(8),@xbnchar,@nlint,@bmnvarchar(5),@rsint,@pjnlintFETCHabsolute2FROM学生游标into@xm,@xb,@nl,@bm--提取记录PRINT'第2条记录:'+@xm+@xb+cASt(@nlASchar(2))+@bmFETCHrelative2FROM学生游标into@xm,@xb,@nl,@bmPRINT'后移2条记录:'+@xm+@xb+cASt(@nlASchar(2))+@bmSET@rs=0SET@pjnl=0PRINT'全部记录为：'FETCHfirstFROM学生游标into@xm,@xb,@nl,@bm--先提取第一条记录WHILE@@FETCH_STATUS=0BEGINPRINTcASt(@rs+1ASchar(2))+':'+@xm+@xb+cASt(@nlASchar(2))+@bmFETCHnextFROM学生游标into@xm,@xb,@nl,@bmSET@rs=@rs+1SET@pjnl=@pjnl+@nlENDPRINT'实际统计记录数为：'+cASt(@rsASchar(2))+'平均年龄为：'+cASt(@pjnl/@rsASchar(6))ENDENDELSEPRINT'游标翻开失败！'CLOSE学生游标--关闭游标DEALLOCATE学生游标--删除游标9.5嵌入式SQLSQL语言提供了两种不同的使用方式：交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差异，在程序设计的环境下，SQL语句要做某些必要的扩充9.5.1嵌入式SQL的一般形式为了区分SQL语句与主语言语句，需要:前缀：EXECSQL结束标志：随主语言的不同而不同以C为主语言的嵌入式SQL语句的一般形式：

EXECSQL

<SQL语句>;例：EXECSQLDROPTABLEStudent;以COBOL作为主语言的嵌入式SQL语句的一般形式：

EXECSQL<SQL语句>END-EXEC例：EXECSQLDROPTABLEStudentEND-EXEC9.5.2嵌入式SQL语句与主语言之间的通信将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句SQL语句描述性的面向集合的语句负责操纵数据库高级语言语句过程性的面向记录的语句负责控制程序流程1.SQL通信区SQLCA：SQLCommunicationAreaSQLCA是一个数据结构SQLCA的用途SQL语句执行后，DBMS反响给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句SQLCA的内容与所执行的SQL语句有关与该SQL语句的执行情况有关例：在执行删除语句DELETE后，不同的执行情况，SQLCA中有不同的信息：违反数据保护规那么，操作拒绝没有满足条件的行，一行也没有删除成功删除，并有删除的行数无条件删除警告信息由于各种原因，执行出错SQLCA的使用方法定义SQLCA用EXECSQLINCLUDESQLCA加以定义使用SQLCASQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE如果SQLCODE等于预定义的常量SUCCESS，那么表示SQL语句成功，否那么表示出错应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理主变量在SQL语句中使用的主语言程序变量简称为主变量。输入主变量：由应用程序对其赋值，SQL语句引用；输出主变量：由SQL语句结对其赋值，返回给应用程序。一个主变量既可以是输入主变量，也可以是输出主变量。利用输入主变量，可以和数据库中插入数据，修改数据库中的数据，执行指定的操作；利用输出主变量，可以得到SQL语句的结果数据和状态。主变量的用途输入主变量指定向数据库中插入的数据将数据库中的数据修改为指定值指定执行的操作指定WHERE子句或HAVING子句中的条件输出主变量获取SQL语句的结果数据获取SQL语句的执行状态指示变量一个主变量可以附带一个指示变量〔IndicatorVariable〕什么是指示变量整型变量用来“指示”所指主变量的值或条件指示变量的用途输入主变量可以利用指示变量赋空值输出主变量可以利用指示变量检测出是否空值，值是否被截断在SQL语句中使用主变量和指示变量的方法1)说明主变量和指示变量BEGINDECLARESECTION ......... .........(说明主变量和指示变量) .........ENDDECLARESECTION2)使用主变量说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现为了与数据库对象名〔表名、视图名、列名等〕区别，SQL语句中的主变量名前要加冒号〔:〕作为标志3)使用指示变量指示变量前也必须加冒号标志必须紧跟在所指主变量之后在SQL语句之外(主语言语句中)使用主变量和指示变量的方法可以直接引用，不必加冒号3.游标〔cursor为什么要使用游标SQL语言与主语言具有不同数据处理方式SQL语言是面向集合的，一条SQL语句原那么上可以产生或处理多条记录主语言是面向记录的，一组主变量一次只能存放一条记录仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式什么是游标游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果每个游标区都有一个名字用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理小结在嵌入式SQL中，SQL语句与主语言语句分工非常明确SQL语句：直接与数据库打交道主语言语句1.控制程序流程2.对SQL语句的执行结果做进一步加工处理SQL语句用主变量从主语言中接收执行参数，操纵数据库SQL语句的执行状态由DBMS送至SQLCA中主语言程序从SQLCA中取出状态信息，据此决定下一步操作如果SQL语句从数据库中成功地检索出数据，那么通过主变量传给主语言做进一步处理SQL语言和主语言的不同数据处理方式通过游标来协调例：带有嵌入式SQL的一小段C程序............EXECSQLINCLUDESQLCA;/*(1)定义SQL通信区*/EXECSQLBEGINDECLARESECTION;/*(2)说明主变量*/CHARtitle_id(7);CHARtitle(81);INTroyalty;EXECSQLENDDECLARESECTION;main(){EXECSQLDECLAREC1CURSORFORSELECTtit_id,tit,royFROMtitles;/*(3)定义游标*//*从titles表中查询tit_id,tit,roy*/EXECSQLOPENC1;/*翻开游标*/for(;;){EXECSQLFETCHC1INTO:title_id,:title,:royalty;/*(5)游标操作〔将当前数据放入主变量并推进游标指针〕*/if(sqlca.sqlcode<>SUCCESS)/*(6)利用SQLCA中的状态信息决定何时退出循环*/break;printf("TitleID:%s,Royalty:%d",:title_id,:royalty);printf("Title:%s",:title);/*打印查询结果*/}EXECSQLCLOSEC1;/*(7)游标操作〔关闭游标〕*/}9.5.2不用游标的SQL语句1.几种不需要使用游标的SQL语句

(1)用于说明主变量的说明性语句

SQL的说明性语句主要有两条：

EXECSQLBEGINDECLARESECTION；

EXECSQLENDDECLARESECTION；

(2)数据定义和数据控制语句

(3)查询结果为单记录的查询语句

(4)数据的插入语句和某些数据删除、修改语句

独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。2.不用游标的查询语句EXECSQLSELECT[ALL|DISTINCT]〈目标列表达式〉[，…n]

INTO〈主变量〉[〈指示变量〉][，…n]

FROM〈表名或视图名〉[，…n][WHERE〈条件表达式〉]；

1)在语句开始前要加EXECSQL前缀。

2)该查询语句中又扩充了INTO子句。

3)在WHERE子句的条件表达式中可以使用主变量。

4)由于查询的结果集中只有一条记录，该语句中不必有排序和分组子句。

5)INTO子句中的主变量后面跟有指示变量时：结果列值为NULL，指示变量为负值，结果列不向该主变量赋值；传递正常，指示变量的值为0；主变量宽度不够，那么指示变量的值为数据截断前的宽度。例子【例5-41】查询学号为主变量givesno、课号为主变量givecno的值的学生选课记录。

EXECSQLSELECT学号，课程号，成绩

INTO:Sno，:Cno，:grade:gradeid

FROM选课

WHERE学号=:givesnoAND课程号=:givecno；

3.不用游标的数据维护语句

(1)不用游标的数据删除语句

【例5-42】删除学号由主变量Sno决定的学生记录。

EXECSQLDELETE

FROM学生

WHERE学号=:Sno；(2)不用游标的数据修改语句【例5-43】将计算机系所有学生的年龄都加上主变量Raise。

EXECSQLUPDATE学生

SET年龄=年龄+:Raise

WHERE所在系=‘计算机系’；【例5-44】将计算机系学生的年龄置空。

Raiseid=-1EXECSQLUPDATE学生SET年龄=年龄+:Raise:Raiseid；(3)不用游标的数据插入语句【例5-45】将学号为主变量Sno、课程号为Cno的选课记录，插到库中。EXECSQLINSERTINTO选课VALUES(:Sno，:Cno)；9.5.3使用游标的SQL必须使用游标的SQL语句有:查询结果为多条记录的SELECT语句(1)定义游标命令

EXECSQLDECLARE〈游标名〉CURSOR

FOR〈子查询〉

[FORUPDATEOF〈字段名1〉[，…n]]；

删除和修改数据的语句中，WHERE为：

WHERECURRENTOF〈游标名〉

【例5-49】定义按主变量DEPT查询系里学生的游标。EXECSQLDECLARESXCURSORFOR

SELECT*FROM学生

WHERE所在系=:DEPT；(2)翻开、推进和关闭游标命令EXECSQLOPEN〈游标名〉；

执行对应的查询语句，并将游标指向结果集的第一条记录前。翻开的游标处于活动状态，可以被推进。EXECSQLFETCH〈游标名〉INTO〈主变量组〉；

将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。EXECSQLCLOSE〈游标名〉；关闭游标【例5-47】翻开SX游标。DEPT=‘计算机系’

EXECSQLOPENSX；【例5-48】将翻开的指向系的游标向前推进。EXECSQLFETCHSX

INTO:Sno，:Sname，:Sage，:Sdept；例1查询某个系全体学生的信息〔学号、姓名、性别和年龄〕。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中......EXECSQLINCLUDESQLCA;......EXECSQLBEGINDECLARESECTION;....../*说明主变量deptname,HSno,HSname,HSsex,HSage等*/............EXECSQLENDDECLARESECTION;............gets(deptname);/*为主变量deptname赋值*/......EXECSQLDECLARESXCURSORFORSELECTSno,Sname,Ssex,SageFROMStudentWHERESDept=:deptname;/*说明游标*/EXECSQLOPENSX/*翻开游标*/WHILE(1)/*用循环结构逐条处理结果集中的记录*/{EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/if(sqlca.sqlcode<>SUCCESS)break;/*假设所有查询结果均已处理完或出现SQL语句错误，那么退出循环*/

/*由主语言语句进行进一步处理*/............};EXECSQLCLOSESX;/*关闭游标*/............9.6数据控制机制和语句9.6.1数据控制机制1.授权定义具有授权资格的用户，如DBA或DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。2.存权处理DBMS把授权的结果编译后存入数据字典中。3.查权操作当提出操作请求时，系统在数据字典中查找该用户的数据操作权限，当拥有该操作权时执行其操作，否那么系统将拒绝其操作。9.6.2数据控制语句1.授权语句

GRANT〈系统特权组〉To〈用户组〉|PUBLIC

[WITHGRANTOPTION]；

1)PUBLIC指数据库的所有用户。

2)WITHGRANTOPTION:获得权限的用户可以把该权限再授予别的用户。

GRANTALLPRIVILIGES|〈对象特权组〉ON〈对象名〉

TO〈用户组〉|PUBLIC

[WITHGRANTOPTION]；

1)ALLPRIVILIGES指所有的对象特权。

2)对象名指操作的对象标识，如表名、视图名和过程名等。

例子【例9-51】把修改学生学号和查询学生表的权力授予用户王平。

GRANTUPDATE(学号),SELECTON学生TO王平;【例9-52】把建立数据库和备份数据库的权力赋给用户王平。

GRANTCREATEDATABASE，BACKUPDATABASETO王平；2.收权语句REVOKE语句的一般格式为：

REVOKE〈权限组〉|ALLPRIVILIGES[ON〈对象名〉]

FROM〈用户名组〉|PUBLIC；

其中：ON子句用于指定被收回特权的对象；ALLPRIVILIGES指收回所有特权；PUBLIC指所有用户【例9-53】将用户王平的可以在学生表中修改学生学号的权利收回。

REVOKEUPDATE(学号)ON学生FROM王平；3.拒绝访问语句拒绝访问语句的一般格式为：

DENYALL[PRIVILIGES]|〈权限组〉[ON〈对象名〉]TO〈用户组〉|PUBLIC；

其中：ON子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。9.7.创立和管理缺省(1)创立缺省

CREATEDEFAULT〈缺省名〉(2)绑定缺省

EXECsp_bindfault‘〈缺省名〉’，‘〈表名〉.〈列名〉’

系统存储过程执行时的格式为：

EXEC〈存储过程名〉〈参数组〉

如果参数是常量，那么要加定界符‘’。(3)解除缺省

EXECsp_unbinddefault‘〈缺省名〉’，‘〈表名〉.〈列名〉’(4)删除缺省

DROPDEFAULT〈缺省名组〉

MS格式createdefault<默认名>as‘默认值‘createdefaultxbas'男‘sp_binddefaul默认名,’表名.列名’sp_bindefaultxb,'学生.性别‘sp_unbindefault‘表名.列名’sp_unbindefault'学生.性别‘dropdefault默认名dropdefaultxb9.7.创立和管理规那么(1)创立规那么

CREATERULE〈规那么名〉AS〈规那么表达式〉

规那么表达式是WHERE子句中的有效表达式。createrulenum_ruleas@numbetween0and10