第9章 用T-SQL编程新.ppt

,复习,规则的概念及与check约束的区别默认值的定义、绑定、删除,第9章用T-SQL编程,教学目的：了解事务的特性，掌握T-SQL语句以及流控制语句的使用，掌握变量的类型。教学要求：掌握T-SQL语句的使用，变量的基本类型，掌握用户自定义函数的使用。,第9章用T-SQL编程,9.1执行多条T-SQL语句9.2变量9.3流程控制语句9.4用户自定义函数9.5事务,9.1执行多条T-SQL语句,批：是一个T-SQL语句集，这些语句被应用程序作为一个整体提交给服务器，并在服务器端作为一个整体来执行。（用Go作结束标志）例如：以下的例子包括三个批：UseMarketGoCreateviewcust_TelAsSelectFirstName,LastName,TelFromCustomersGOSelect*fromCust_TelGo,注释：是程序中不被执行的正文。,说明代码的含义，增强代码的可读性,可以把程序中暂时不用的语句注释掉，使其暂时不被执行。,有两种形式：,-（两个减号）：用于注释单行。,/*/用于注释多行。注意多行注释不能跨批。,9.1执行多条T-SQL语句,有两个作用：,存储过程：是在服务器上实现定义并编译好的一组T-SQL语句，存储过程可以接受参数也可以返回值。,触发器：是一类特殊的存储过程，它不能在应用程序中直接被调用，而是在用户对表中的数据进行修改操作（包括插入数据、修改数据和删除数据）时自动被执行。,例：UseMarketGo-查看所有客户的信息Select*FromCustomersGo/*查看货品所有订单的累计金额*/SelectSUM（OrderSum）FromOrders,9.1执行多条T-SQL语句,UseNorthwindGoDeclareMyVarINT/*Startofthesplitcomment.GoEndofthesplitcomment.*/SelectMyVar=29Go考虑上述语法是否有错误？,9.1.1批,脚本：是一系列顺序提交的批。,9.1.2脚本,特点：,脚本可以直接在查询分析器等工具中输入并执行也可以保存在文件中。,一个脚本可以包含一个或多个批，脚本中的Go命令标志一个批的结束。,可以将服务器上创建一个数据库的步骤永久地记录在脚本文件中。,可以将语句保存为脚本文件，便于拷贝。,9.2变量,局部变量是用户定义的变量。它用Declare语句声明，利用局部变量可以保存程序执行过程中的中间结果，保存由存储过程返回的数据值等。（1）局部变量的定义必须先用DECLARE命令定义后才可以使用。DECLAERvariable_namedata_type，varialbe_namedatatype.,9.2.1局部变量,例：声明一个整型变量Order_Count，使用如下语句：DeclareOrder_Countint,9.2变量,在一条Declare语句中可以声明多个局部变量，变量之间用逗号分隔。如：DeclareLastNamevarchar(30),FirstNamevarchar(20),Telvarchar(30),9.2.1局部变量,（2）局部变量的赋值,使用SET语句，语法如下：SETvariable_name=expression.,Variable_name：局部变量名。,Expression：是与局部变量的数据类型相匹配的表达式。,9.2变量,例：USEMarketGO-Declaretwovariables.DeclareFirstNamevarchar(20),LastNamevarchar(20)-Settheirvalues.SetFirstName=TaoSetLastName=Li-UsethemintheWhereclauseofaSelectstatement.SelectLastName,FirstName,TelFromCustomersWhereFirstName=FirstNameandLastName=LastNameGo,9.2.1局部变量,9.2变量,9.2.1局部变量,用SELECT语句也可以为局部变量赋值：,例如：以下语句将FirstName变量赋值为Tao：SelectFirstName=Tao,注意：如果Select得到的不是单一的结果而是一组结果集，那么最后一个结果将被赋值给变量。如：在以下语句中FirstName变量得到的是Select语句查找到的最后一个FirstName。SelectFirstName=FirstNameFromCustomers,9.2变量,全局变量是SQLServer系统提供并赋值的变量，存储的通常是一些SQLServer的配置设定值和统计数据。在使用全局变量时应该注意以下几点：用户不能建立全局变量。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“”开头。全局变量对用户来说是只读的。局部变量的名称不能与全局变量的名称相同。,9.2.2全局变量,例：version表示SQLServer的版本信息。,9.2变量,9.2.2全局变量,9.3流程控制语句,使用BEGINEND可以将多条T-SQL语句封装起来，构成一个独立的语句块。BEGIN关键字表示语句块的开始，END关键字表示语句块的结束，它们必须成对出现。BEGINEND的语法格式如下：BEGINsql_statement|statement_blockEND,9.3.1BEGINEND,9.3流程控制语句,IFELSE可以用来控制语句的条件执行。当IF关键字后的条件满足（布尔表达式返回TRUE）时，则在执行IF关键字及其条件之后的T-SQL语句。否则，就执行ELSE关键字后的T-SQL语句（若ELSE部分存在）。IFELSE语句的语法格式如下：IFBoolean_expressionsql_statementELSEsql_statement,9.3.2IFELSE,9.3流程控制语句,9.3.2IFELSE,真,假,9.3流程控制语句,【例】判断Orders表中是否有记录，如果有，则显示Entryavailable，否则，显示Noentry。IF（SelectCount（*）fromOrders）=0PrintNoentryElsePrintEntryavailable,注：,IFElse只对后面的一条语句有效，若后面要执行多于一条的语句须用BeginEnd括起来组成一个语句块。,9.3流程控制语句,【例】判断Orders表是否有Battery货品的记录，如果有，则显示Entryavailable，并查询Battery货品订单的总额。IFExists（Select*fromOrderswhereGoodsName=Battery)BeginPrintEntryavailableSelectGoodsName,Sum(OrderSum)FromOrdersWhereGoodsName=BatteryEnd,9.3流程控制语句,使用WHLIE语句可以实现语句块的循环执行。WHLIE语句的语法格式如下：WHILEBoolean_expressionsql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUE,9.3.3WHLIE,9.3流程控制语句,DeclarexintSetx=0Whilex3BeginSetx=x+1Printx=+convert(char(1),x)EndGo,9.3.3WHLIE,9.3流程控制语句,Break用于退出最内层的WHILE循环。语法格式如下：WhileBoolean_expressionsql_statementBreaksql_statement,9.3.4BREAK,DeclarexintSetx=0Whilex3Beginsetx=x+1Printx=+convert(char(1),x)BreakEndgo,执行结果：x=1,9.3流程控制语句,Continue语句用于重新开始一次While循环，在Continue语句之后的语句都不会被执行，而是跳转到循环开始的地方继续执行。于退出最内层的WHILE循环。,9.3.4CONTINUE,DeclarexintSetx=0Whilex3Beginsetx=x+1Printx=+convert(char(1),x)If(x=2)continuePrintxisnot2ENDGo,执行结果为：X=1Xisnot2X=2X=3Xisnot2,WAITFOR语句可以将它之后的语句在一个指定的间隔之后执行，或在未来的某一指定时间执行。WAITFOR语句的语法形式为：WAITFORDELAYtime|TIMEtime其中，DELAY用于指定时间间隔，TIME用于指定某一时刻，其数据类型为datetime，格式为hh:mm:ss。,9.3流程控制语句,9.3.6WAITFOR,以下代码指示等待两秒后查询CustomersWaitFordelay00:00:02Select*fromMarket.dbo.customers,9.3流程控制语句,GOTO语句使一个批的执行转到另一个有标号的语句。GOTO语句和标号之间的语句永远不会被执行。GOTO语句的语法格式如下：GOTOlabel其中label就是需指向的标签。标签必须符合标识符规则，且必须存在。,9.3.7GOTO,9.3流程控制语句,IF(Selectcount(*)fromOrdersWhereGoodsName=Battery)=0GOTONOACTIONPrintEntryavailableSelectGoodsName,Sum(Ordersum)FromOrdersWhereGoodsName=BatteryNOACTION:PrintFinish,9.3.7GOTO,9.3流程控制语句,RETURN语句的作用是无条件地退出正在操作的批处理、查询、存储过程或触发器。从而不执行位于RETURN之后的语句。RETURN语句退出时，还可以根据需要返回一个状态信息。RETURN语句的语法格式如下：RETURNinteger_expression,9.3.8RETURN,注：,Return可以返回一个整数。,CASE函数可以根据多个选择确定执行的内容。,9.3流程控制语句,CASEinput_expressionWHENwhen_expressionTHENresult_expression.nELSEelse_result_expressionEND,简单表达式，语法为:,9.3.9CASE,CASE表达式有两种形式:,条件表达式,When_expression与Input_expression比较，两值相等时执行后面的Result_expression语句。否则，执行else_result_expression,9.3流程控制语句,例：判断Goods表中Status列的值，如果为1，则返回“正在整理中，尚不能出售”，为0，则返回“可以出售”，否则，返回“未知的状态”。UseMarketGOSelectNameasGoodsName,GoodsStatus=CasestatusWhen1then正在整理中，尚不能出售When0then可以出售Else未知的状态EndFromGoods,9.3.9CASE,9.3流程控制语句,CASEWHENBoolean_expressionTHENresult_expression.nELSEelse_result_expressionEND,选择表达式，语法为:,9.3.9CASE,9.3流程控制语句,例：判断Goods表中货品的库存量（Storage）的大小，按Storage列大小的不同返回不同的描述。UseMarketGOSelectNameasGoodsName,StorageStatus=CaseWhenStorage=0then没有库存WhenStorage=300andStorage1000THEN库存正常Else库存过多EndFromGoods,9.3.9CASE,9.3流程控制语句,语法为：PRINTstring|local_variable|global_variable其中，string可以是一个字符串，local_variable和global_variable分别代表一个局部变量和一个全局变量，该变量必须是char或varchar类型，或可以隐式地转化成char或varchar类型。例：DeclareMyMsgchar(20)SetMyMsg=Thisismymessage.PrintMyMsgRAISEERROR作用是将错误信息显示在屏幕上，同时也可以记录在NT日志中。RAISEERROR可以返回以下两种类型的信息：,9.3.10RAISERROR和PRINT,保存在sysmessages系统表中的用户自定义错误信息，在RAISERROR语句中用错误号表示。,9.3流程控制语句,9.3.10RAISERROR和PRINT,RAISERROR语句中以字符串形式给出错误的信息。,RAISEERROR与PRINT的区别：,RAISERROR能给返回的信息指定错误号、错误严重等级和状态。,RAISERROR可以要求该错误被记录在SQLServer错误日志和WindowsNT应用程序日志中。,RAISERROR中的信息字符串可以像C语言中的Printf语句一样有包含替换符的变量和参数。,9.3流程控制语句,9.3.10RAISERROR和PRINT,RAISEERROR基本语法格式：,RAISEERROR(msg_id|msg_str,severity,statewithoption),Msg_id：是sysmessages系统表中用户自定义错误信息的错误号，任何用户自定义错误号都应大于50000。Msg_str：如果不使用sysmessages系统表中的用户自定义错误信息，也可以在Raiserror语句中以字符串的形式直接给出错误信息。severity：代表错误的严重等级，用大于0的整数表示。State：代表发生错误时的状态信息，可以是1到127之间的任意整数。,9.4用户自定义函数,根据用户定义函数返回值的类型，可将用户定义函数分为如下三个类别：(1)返回数值的用户自定义函数(2)内联（单语句）的返回表的用户自定义函数(3)多语句的返回表的用户自定义函数,语法格式：CREATEFUNCTIONfunction_name/*函数名部分*/(parameter_nameASscalar_parameter_data_type=default,.n)/*形参定义部分*/RETURNSscalar_return_data_type/*返回参数的类型*/WITHEncryption/*函数选项定义*/ASBEGINfunction_body/*函数体部分*/RETURNscalar_expression/*返回语句*/END,9.4.1返回数值的用户自定义函数,9.4用户自定义函数,CREATEFUNCTIONGoodsOrderSum(GoodsNamevarchar(20)ReturnmoneyBeginDeclareGSUMmoneySelectGSUM=Sum(OrderSum)FromOrdersWhereGoodsName=GoodsNameReturnGSUMEnd,创建一个用户自定义函数，返回一个货品的订单总金额。,9.4用户自定义函数,语法格式：CREATEFUNCTIONfunction_name/*函数名部分*/(parameter_nameASscalar_parameter_data_type=default,.n)/*形参定义部分*/RETURNSTable/*返回参数的类型*/WITHENCRYPTION/*函数选项定义*/ASRETURN(Select-statement),9.4.2内联（单语句）的返回表的用户自定义函数,9.4用户自定义函数,例：UseMarketGoCreateViewBeijingCustomersAsSelect*fromCustomersWhereCity=Beijing,9.4用户自定义函数,例：UseMarketGoCreateFuctionCityCustomers(CityVarchar(20)ReturnTableReturn(Select*fromCustomersWhereCity=city),9.4用户自定义函数,当输入Select*fromCityCustomers(Beijing),CREATEFUNCTIONowner_name.function_name/*定义函数名部分*/(parameter_nameASscalar_parameter_data_type=default,.n)/*定义函数参数部分*/RE