第五章T-SQL程序设计-课件

第5章Transact-SQL程序设计5.2变量5.3运算符5.1注释5.5流程控制语句5.4函数1能力目标掌握Transact-SQL的语法结构；学会Transact-SQL变量和函数的用法；了解Transact-SQL各种控制结构的区别和用法。2态度目标团队精神；态度积极；任务按时完成；出勤。3重点难点重点：掌握Transact-SQL的语法结构。难点：学会使用Transact-SQL语言编写程序。4[案例一]declare@xint,@yint,@iint--局部变量的定义select@i=1,@x=0,@y=0/*局部变量赋值，等同于set@i=1*/while(@i<=10)begin if(@i%2=0) set@x=@x+@i*@i else set@y=@y+@i*@i set@i=@i+1endselect@x,@y--或print@x5注:全局变量以@@开头,由系统定义和维护,不能由用户创建,大部分全局变量记录了SQLSERVER服务器的当前状态信息.全局变量不可以赋值.例:@@ERROR:用于返回最后执行的T-SQL语句的错误代码,为int型.如果@@ERROR的值为非0,说明执行过程出错,错误代码查手册6[项目一]查询学生成绩表中成绩大于平均分的所有记录declare@degreedecimal(5,1)set@degree=(selectavg(score)fromt_score)select*fromt_scorewherescore>@degree[项目二]演示局部变量的作用Declare@msgvarchar(50)Set@msg=‘helloworld!’GoPrint@msggo7[项目三]按学号查询，查询是否存在040302的同学记录并显示相应信息declare@snochar(8)set@sno=‘040302’if@sno=(selects_numberfromt_studentwheres_number=‘040302’) print‘学号为’+@sno+’的同学存在’else print‘学号为’+@sno+’的同学不存在’注：可以使用exists8[项目三]查询学生表中7月份出生的学生人数，以标题‘人数’输出declare@numberintset@number=7selectcount(*)as人数fromt_studentwheremonth(birthday)=@number95.1注释注释是表示用户提供的说明内容。有两种格式：

单行注释：使用两个联在一起的减号“--”作为注释内容标记，其后为注释内容。

多行注释：使用“/*注释内容*/”格式。可以将注释插入单独行中或嵌套（只限“--”）在Transact-SQL命令行的末端或者Transact-SQL语句中。服务器不对注释进行解释。注意：不要将GO命令包含在注释中。

105.2变量

1.局部变量定义DECLAER{@变量名称数据类型}|{@游标变量名称CURSOR}{,…n}

2.局部变量的赋值使用SET命令或SELECT命令对局部变量赋值。SET{@变量名称=表达式}或SELECT{@变量名称=表达式}[,…n]

3.系统全局变量全局变量是由系统提供且预先声明的变量，通过在名称前保留两个@@符号区别于局部变量(@@IDENTITY,@@ERROR)115.3运算符

1.算术运算符

+，-，*，/，%（模余）

2.赋值运算符

=

3.按位运算符

&（按位与运算）、|（按位或运算）和^（按位异或运算

4.比较运算符

=、>、<、>=、<=、<>、!=、!<、!>125.3运算符

5.逻辑运算符135.3运算符

5.字符串串联运算符字符串串联运算符允许通过加号（+）进行字符串串联。例如：print‘abc’+‘123’的输出结果是abc123。

7.一元运算符

+（数值为正）、-（数值为负）和~（返回数值的补数即按位NOT）145.4函数

1.聚合函数

AVG([ALL|DISTINCT]expression)——返回平均值COUNT({[ALL|DISTINCT]expression}|*))——返回统计记录行数MAX|MIN([ALL|DISTINCT]expression)——返回最大|最小值SUM([ALL|DISTINCT]expression)——返回汇总和155.4函数

2.标量函数GETDATE()——获取服务器系统日期时间

3.数学函数

ROUND(numeric_expression,length[,function])

4.字符串函数

SUBSTRING(expression,start,length)165.4函数

5.

用户自定义函数语法格式1：标量函数（ScalarFunctions）CREATEFUNCTION[schema_name.]function_name([{@parameter_name[AS][type_schema_name.]

parameter_data_type

[=default]}

[,...n]

])RETURNSreturn_data_type

[WITH<function_option>[,...n]]

[AS]

BEGIN

function_body

RETURNscalar_expression

END[;]175.4函数

5.

用户自定义函数语法格式2：内嵌表值函数（InlineTable-ValuedFunctions）CREATEFUNCTION[schema_name.]function_name([{@parameter_name

[AS][type_schema_name.]

parameter_data_type

[=default]}

[,...n]

])RETURNSTABLE

[WITH<function_option>[,...n]]

[AS]

RETURN[(]select_stmt[)][;]185.4函数

5.

用户自定义函数语法格式3：多语句表值函数（MultistatementTable-ValuedFunctions）

CREATEFUNCTION[schema_name.]function_name([{@parameter_name

[AS][type_schema_name.]

parameter_data_type

[=default]}

[,...n]

])RETURNS@return_variableTABLE<table_type_definition>

[WITH<function_option>[,...n]]

[AS]

BEGIN

function_body

RETURN

END[;]195.5流程控制语句

1.BEGIN...END语句【案例5.2】从goods表中，判断商品售价是否大于2800，并显示售价大于2800的商品名称和实际售价。BEGIN{sql_statement|statement_block}ENDIF(SELECTAVG(saleprice)FROMgoods)>=2800BEGINPRINT‘下面列出的是售价大于2800的商品名称和实际售价。'PRINT''SELECTgnameAS商品名称,salepriceAS售价

FROMgoodsWHEREsaleprice>=2800ENDELSEPRINT‘售价不到2800元。205.5流程控制语句

2.IF…ELSE语句

【案例5.3】使用多个IF...ELSE块，对产品型号为“MAC重型机械”的产品平均价格和2800000比较，分别显示在2800000上下的该类产品的名称。

IFBoolean_expression

{sql_statement|statement_block}

[ELSE

{sql_statement|statement_block}]215.5流程控制语句

[项目四]判断SALES数据库是否有商品的库存少于10，如果有，则将每一商品都入货50，直至所有商品的库存量都多于10或者有商品的库存量超过200Whileexists(select*fromgoodswherenumber<10) begin updategoodssetnumber=number+50 if(selectmax(number)fromgoods)>200 break end225.5流程控制语句

3.CASE语句格式1：简单CASE函数CASEinput_expression

WHENwhen_expressionTHENresult_expression[...n]

[ELSEelse_result_expression

]END格式2：CASE搜索函数CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]END235.5流程控制语句

4.WHILE语句WHILEBoolean_expression

{sql_statement|statement_block}

[BREAK]

{sql_statement|statement_block}

[CONTINUE]24WAITFOR语句两种用法：waitfordelay:使查询在一段时间间隔后继续执行例：waitfordelay’00:00:05’ selectgname,manufacturer,costpricefromgoodswhereindate>=‘2005-1-1’waitfortime：使查询在某一时刻继续执行例：waitfortime‘9:25:50’ selectgname,manufacturer,costpricefromgoodswhereindate>=‘2005-1-1’2513六月2026Oracle10g管理及应用5.6游标基本操作

如果希望对SELECT结果集中的数据进行逐行处理操作，需要用到游标。游标中有一个指针，通过FETCH语句行动该指针。在通过游标对表的行数据进行处理的操作过程，主要包括以下四步：声明游标、打开游标、提取数据和关闭游标。1．声明游标声明游标就是声明变量，使变量成为指定的PL/SQL控制结构。声明游标的语法格式如下所示：DECLARE游标名CURSORFORSELECT语句2．打开游标在游标声明以后，读取数据之前，必须先打开游标才能使用游标，打开游标使用OPEN语句，其语法格式如下所示：OPEN游标名;2613六月2026Oracle10g管理及应用5.6游标基本操作3．提取数据提取数据操作是通过游标处理表中的各数据行，提取数据时，需要把游标中的数据行提取到对应结构的变量中。提取数据的命令为FETCH，其语法格式如下所示：FETCH游标名INTO变量1,变量2,……4．关闭游标在游标使用完之后，必须要关闭游标。关闭游标使用CLOSE语句，其语法结构如下所示：CLOSE游标变量名;使用游标主要用于逐行处理数据表中的数据27使用游标从学生信息数据库中查询学生的信息DECLAREstu_cursorCURSORFOR SELECT*FROMt_studentOPENstu_cursorFETCHNEXTFROMstu_cursorWHILE(@@FETCH_STATUS=0)BEGIN FETCHNEXTFROMstu_cursorEND28案例分析使用案例数据库eTradeInfo，在“SQLServerManagementStudio”管理窗口中，完成以下题目对象创建和Transact-SQL代码调试。

1.编写一个函数，返回所有价格在80至530元的U盘的生产数量