搜索
第7章SQLServer2008高级应用北京林业大学软件教研室27.1Transact-SQL程序设计7.2存储过程7.3触发器7.4备份和还原北京林业大学软件教研室37.1Transact-SQL程序设计Transact-SQL语言就是在标准SQL的基础上进行扩充而推出的SQLServer专用的结构化SQL。Transact-SQL语言最主要的用途是设计服务器端的能够在后台执行的程序块。7.1.1变量全局变量全局变量由系统定义和维护的，只能使用预先说明及定义的全局变量。全局变量对用户而言是只读的，用户无法对它们进行修改或管理。存储过程、触发器等北京林业大学软件教研室4局部变量DECLARE@变量名变量类型[，@变量名变量类型，…]SELECT@局部变量=变量值或SET@局部变量=变量值[例7-1]声明一个长度为8个字符的变量id，并赋值。DECLARE@idchar(8)SELECT@id='10010001'北京林业大学软件教研室5【例7-2】查询学号为“S7”的学生的学号与姓名，并存储到变量@sno和@sn中。DECLARE@snovarchar(10)DECLARE@snvarchar(10)SELECT@sno=SNo,@sn=SNFROMSWHERESNo='S7'注释符在T-SQL中可以使用两类注释符：（1）ANSI标准的注释符“--”用于单行注释；（2）与C语言相同的程序注释符，即“/*……*/”，“/*”用于注释文字的开头，“*/”用于注释文字的结尾，可在程序中标识多行文字为注释。北京林业大学软件教研室67.1.2运算符运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。算术运算符算术运算符对两个表达式执行数学运算，参与运算的表达式必须是数值数据类型或能够进行算术运算的其他数据类型。北京林业大学软件教研室7赋值运算符等号(=)是唯一的Transact-SQL赋值运算符。在以下示例中，将创建一个@MyCounter变量，然后赋值运算符将@MyCounter的值赋为1。DECLARE@MyCounterINT;SET@MyCounter=1;字符串串联运算符加号(+)是字符串串联运算符，可以用它将字符串串联起来。其他所有字符串操作都使用字符串函数进行处理。例如'good'+''+'morning'的结果是'goodmorning'。北京林业大学软件教研室8比较运算符比较运算符用来比较两个表达式值之间的大小关系，可以用于除了text、ntext和image数据类型之外的所有数据类型。运算的结果为True或False，通常用来构造条件表达式。北京林业大学软件教研室9逻辑运算符逻辑运算符用来对多个条件进行运算，运算的结果为True或False，通常用来表示复杂的条件表达式。北京林业大学软件教研室10按位运算符按位运算符对两个二进制数据或整数数据进行位操作，但是两个操作数不能同时为二进制数据，必须有一个为整数数据。一元运算符一元运算符只对一个表达式进行运算北京林业大学软件教研室11运算符优先级和结合性SQLServer2008中运算符的优先级顺序如下●~（按位取反）●*（乘）、/（除）、%（取余）●+（正）、-（负）、+（加）、+（字符串串联）、-（减）、&（按位与）、^（按位异或）、|（按位或）●=、、、=、=、、!=、!、!（比较运算符）●Not●And●All、Any、Between、In、Like、Or、Some●=（赋值）北京林业大学软件教研室127.1.3批处理批处理是包含一个或多个T-SQL语句的组，批处理的所有语句被整合成一个执行计划。批处理是使用GO语句将多条SQL语句进行分隔，其中每两个GO之间的SQL语句就是一个批处理单元。如果在编译过程中出现语法错误，那么批处理中所有的语句均无法正常执行。【例7-3】执行批处理程序，依次查询学生选课表SC、学生总数。GOSELECT*FROMSCSELECTCOUNT(*)FROMSGO北京林业大学软件教研室137.1.4流程控制命令BEGIN命令行或程序块ENDIF条件表达式命令行或程序块[ELSE命令行或程序块]IF[NOT]EXISTS(SELECT子查询)命令行或程序块[ELSE命令行或程序块]CASE表达式WHEN表达式THEN表达式…WHEN表达式THEN表达式[ELSE表达式]ENDCASE北京林业大学软件教研室14【例7-4】从SC数据表中求出学号为S7同学的平均成绩，如果此平均成绩大于或等于60分，则输出“Pass!”信息。IF(SELECTAVG(Score)FROMSCWHERESNo='S7')=60BEGINPRINT'Pass!'END北京林业大学软件教研室15【例7-5】从S数据表中读取学号为S1同学的数据记录，如果存在，则输出“存在学号为S1的学生”，否则输出“不存在学号为S1的学生”。USETeach/*将教学管理数据库Teach置为当前数据库*/GODECLARE@messageVARCHAR(255)/*定义变量message*/IFEXISTS(SELECT*FROMSWHERESNo='S1')SET@message='存在学号为S1的学生'ELSESET@message='不存在学号为S1的学生'PRINT@messageGO北京林业大学软件教研室16【例7-6】从学生表S中，选取Sno和Sex，如果Sex为“男”则输出“M”，如果为“女”则输出“F”。SELECTSNo,Sex=CASESexWHEN'男'THEN'M'WHEN'女'THEN'F'ENDFROMS格式2：CASEWHEN表达式THEN表达式…WHEN表达式THEN表达式[ELSE表达式]ENDCASE命令可以嵌套到SQL命令中。北京林业大学软件教研室17【例7-7】从SC表中查询所有同学选课的成绩情况，凡成绩为空的输出“未考”、小于60分的输出“不及格”、60分至70分的输出“及格”、70分至90分的输出“良好”、大于或等于90分的输出“优秀”。SELECTSNo,CNo,Score=CASEWHENScoreISNULLTHEN'未考'WHENScore60THEN'不及格'WHENScore=60ANDScore70THEN'及格'WHENScore=70ANDScore90THEN'良好'WHENScore=90THEN'优秀'ENDFROMSC北京林业大学软件教研室18WHILE条件表达式BEGIN命令行或程序块[BREAK][CONTINUE][命令行或程序块]ENDWAITFOR{DELAY'时间'|TIME'时间'|ERROREXIT|PROCESSEXIT|MIRROREXIT}GOTO标识符RETURN（[整数值]）北京林业大学软件教研室19【例7-8】以下程序计算1～100之间所有能被3整除的数的个数及总和。DECLARE@sSMALLINT,@iSMALLINT,@numsSMALLINTSET@s=0SET@i=1SET@nums=0WHILE(@i=100)BEGINIF(@i%3=0)BEGINSET@s=@s+@iSET@nums=@nums+1ENDSET@i=@i+1ENDPRINT@sPRINT@nums北京林业大学软件教研室20【例7-9】等待1小时2分零3秒后才执行SELECT语句。WAITFORDELAY'01:02:03'SELECT*FROMS【例7-10】指定在11:24:00时间点时开始执行SELECT语句。WAITFORTIME'11:24:00'SELECT*FROMS北京林业大学软件教研室21【例7-11】求1+2+3+…+10的总和。DECLARE@sSMALLINT,@iSMALLINTSET@i=1SET@s=0BEG:IF(@i=10)BEGINSET@s=@s+@iSET@i=@i+1GOTOBEGENDPRINT@s北京林业大学软件教研室227.1.5常用命令BACKUP用于将数据库内容或其事务处理日志备份到存储介质上（软盘、硬盘、磁带等）。CHECKPOINT用于将当前工作的数据库中被更改过的数据页或日志页从数据缓冲器中强制写入硬盘。DBCC用于验证数据库完整性、查找错误、分析系统使用情况等。北京林业大学软件教研室23DECLAREDECLARE的语法格式如下：DECLARE{{@local_variabledata_type}|{@cursor_variable_nameCURSOR}|{table_type_definition}}[,...n]DECLARE命令用于声明一个或多个局部变量、游标变量或表变量。例：DECLARE@xCHAR,@yCHAR(10)SELECT@x='123',@y='data_type'PRINT@xPRINT@y北京林业大学软件教研室24EXECUTEEXECUTE命令用来执行存储过程。KILLKILL命令用于终止某一过程的执行。PRINTPRINT的语法格式如下：PRINT'anyASCIItext'|@local_variable|@@FUNCTION|string_expressionPRINT命令向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量。北京林业大学软件教研室25RAISERROR用于在SQLServer系统返回错误信息时，同时返回用户指定的信息。READTEXTREADTEXT命令的语法格式如下：READTEXT{table.columntext_pointeroffsetsize}[HOLDLOCK]READTEXT命令用于从数据类型为TEXT、NTEXT或IMAGE的列中读取数据。RESTORERESTORE命令用来将数据库或其事务处理日志备份文件由存储介质回存到SQLServer系统中。北京林业大学软件教研室26SELECTSELECT命令可用于给变量赋值，其语法格式如下：SELECT{@local_variable=expression}[，...n]SELECT命令可以一次给多个变量赋值。SET命令有两种用法。（1）用于给局部变量赋值。（2）用于用户执行SQL命令时，SQLServer处理选项的设定。SET：选项ON；SET：选项OFF；SET：选项值。北京林业大学软件教研室27SHUTDOWNSHUTDOWN[WITHNOWAIT]SHUTDOWN命令用于停止SQLServer的执行。WRITETEXTWRITETEXT{table.columntext_pointer}[WITHLOG]{data}WRITETEXT命令用于向数据类型为TEXT、NTEXT或IMAGE的列中读取数据。USEUSE{database}USE命令用于改变当前使用的数据库为指定的数据库。北京林业大学软件教研室287.1.6常用函数统计函数STDEV函数STDEV函数返回表达式中所有数据的标准差。STDEVP函数STDEVP函数返回表达式中所有数据的总体标准差。VAR函数VAR函数返回表达式中所有数据的统计变异数。VARP函数VARP函数返回表达式中所有数据的总体变异数。北京林业大学软件教研室29算术函数函数功能三角函数SINCOSTANCOT返回以弧度表示的角的正弦返回以弧度表示的角的余弦返回以弧度表示的角的正切返回以弧度表示的角的余切反三角函数ASINACOSATAN返回正弦是FLOAT值的以弧度表示的角返回余弦是FLOAT值的以弧度表示的角返回正切是FLOAT值的以弧度表示的角角度弧度转换DEGREESRADIANS把弧度转换为角度把角度转换为弧度北京林业大学软件教研室30幂函数EXPLOGLOG10SQRT返回表达式的指数值返回表达式的自然对数值返回表达式的以10为底的对数值返回表达式的平方根取近似值函数CEILINGFLOORROUND返回大于等于表达式的最小整数返回小