第8章 T-SQL编程基础1.ppt

第8章T-SQL编程基础,第8章T-SQL编程基础,教学提示：SQLServer程序设计是读者学习SQLServer数据库的一个重要环节，它对以后程序开发有着直接的决定因素。教学目标：通过本章的学习，读者应该掌握编程的基础知识、基本语句；,SQL（StructuredQueryLanguage）语言是关系数据库的标准语言，它是在1974年由Boyce和Chamberlin提出的。SQLServer2008数据库系统的编程语言是Transact-SQL语言，这是一种非过程化的语言。本章介绍了Transact-SQL的标识符、运算符、表达式、函数、变量与常量、流程控制语句及游标的使用。,8.1Transact-SQL基础知识,Transact-SQL是SQL数据库查询语言的一个强大实现，是一种数据定义、数据操作和控制语言，是SQLServer中的重要组成元素。,8.1.1SQL与Transact-SQLSQL是一种介于关系代数与关系演算之间的结构化查询语言，其功能不仅仅是查询，SQL语言是一个通用的、功能及其强大的关系数据库语言。按实现的功能来分，SQL可以分为以下三类。(1)数据定义语句SQL的数据定义语言（DataDefinitionLanguage，DDL）用来定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。(2)数据操作语句SQL的数据操纵语句（DataManipulationLanguage，DML）包括数据查询和数据更新两种数据操作语句。其中，数据查询指对数据库中的数据查询、统计、分组、排序、检索操作，数据更新指数据的插入、删除、修改等数据维护操作。(3)数据控制语句数据控制指数据的操作权限控制。SQL通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。,SQL语言是关系数据库系统的标准语言，标准的SQL语句几乎可以在所有的关系数据库系统中使用，如Oracle、SQLServer、Sybase等数据库系统。不同的数据库软件商在采纳SQL语言作为自己的数据库的操作语言的同时，又对SQL语言进行了不同程度的扩展。Transact-SQL（简称T-SQL）语言正是微软在其SQLServer系列关系数据库系统中的实现。T-SQL语言是一系列操作数据库与数据库对象的命令语句，所以需要基本语法元素，主要包括常量和变量、运算符、表达式、流程控制语句、注释等。,8.1.2T-SQL语法格式,8.3常量和变量,常量是在程序运行过程中保持不变的量；变量是在程序运行过程中，值可以发生变化的量，通常用来保存程序运行过程中的录入数据、中间结果和最终结果。,常量也称为字面值或标量值，是表示一个特定数据值的符号。常量的值在程序运行过程中不会改变的。,8.3.1常量,常量类型,常量类型,局部变量全局变量,提问:变量定义?常见种类？,SQLServer2008系统中，存在两种类型的变量：一种是系统定义和维护的全局变量；另一种是用户定义以保存中间结果的局部变量。局部变量是用来存储指定数据类型的单个数据值的对象。用户可以创建。全局变量是由系统提供且预先声明的用来保存SQLServer系统运行状态数据值的变量。用户不可以创建。,全局变量,全局变量全局变量由SQLServer系统提供并赋值的变量，名字由符号开始。用户不能建立全局变量，也不可能使用SET语句去修改全局变量的值。通常应该将全局变量的值赋给在同一个批中的局部变量，以便保存和处理。全局变量分为两类：与连接有关的全局变量，如：rowcount-影响的行数；与系统内部信息有关的全局变量。如：version-SQL的版本号。,【例8-1】使用全局变量VERSION查看当前数据库的版本。PRINTVERSION,使用全局变量时应注意以下几点：(1)全局变量不是由用户的程序定义的，它们是在服务器级定义的。(2)用户只能使用预先定义的全局变量。(3)引用全局变量时，必须以标识符开头。(4)局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。,局部变量,局部变量用DECLARE语句声明，声明时它被初始化为NULL，并由SET语句或SELECT语句赋值，它只能用在声明该变量的过程实体中，即使用范围是定义它的批、存储过程和触发器等。其名字由一个符号开始。定义：DECLARE局部变量名数据类型,nDECLARE命令可以定义多个局部变量，之间用逗号分隔。赋值：用SET或SELECT命令对其赋值SELECT局部变量=变量值SET局部变量=变量值使用范围：同一个批,使用PRINT、SELECT输出（查看）局部变量的值，语法格式是：PRINT表达式SELECT表达式1,表达式2,其中，使用PRINT只能有一个表达式，其值在查询后的“消息”窗口中显示；使用SELECT相当于进行无数据源检索，可以有多个表达式，其结果在查询后的“网格”子窗口中显示；在一个脚本中，最好不要混合使用两种输出方式，因为这样的话需要切换两个窗口来查看输出结果。,结果是字符串表达式类型,局部变量,【例】声明一个长度为6个字符的变量编号，并赋值。DECLARE编号CHAR(6)SELECT编号=010101SELECT编号-查看局部变量的数据类型可以是用户自定义的数据类型，也可以是系统数据类型，但不能将其定义为TEXT或IMAGE数据类型。,-多个返回值赋给变量：保留最后一个USE图书管理信息库GODECLAREovar1char(8)/*声明局部变量*/SELECTovar1=读者姓名/*为局部变量赋初值*/SELECTovar1=姓名/*最后一个*/FROM读者信息表/*显示局部变量结果*/SELECTovar1思考题：1.查询某班的人数，赋值给变量，查看变量的值？2.用此变量的值修改班级信息表的人数？,局部变量案例,案例：例变量的多个返回值.sql,【例8-2】局部变量的使用。声明两个变量x和d，分别给其赋值后输出查看结果代码清单如下：DECLARExint,ddatetimeSETx=5SELECTd=getdate()SELECTx,d在查询页中输入以上代码，单击按钮，执行结果如图8-2所示。,运算符与表达式,什么是表达式？由变量、常量、运算符、函数等组成的有结果的式子，可以在查询语句中的任何位置使用。什么是运算符？在T-SQL编程语言中常用的运算有算术运算、字符串连接运算、比较运算、逻辑运算。,SQLServer中的运算符,算符主要分算术运算符、赋值运算符、位运算符、关系运算符、逻辑运算符以及字符串连接运算符，见表，课本P115-116,算术运算,算术运算符：加(+)、减(-)、乘(*)、除(/)和取余(%)参与运算的数据是数值类型数据，其运算结果也是数值类型数据。加(+)和减()运算符也可用于对日期型数据进行运算，还可进行数值性字符数据与数值类型数据进行运算。,字符串连接运算,字符串连接运算：(+)字符串之间的连接。参与字符串连接运算的数据只能是字符数据类型：char、varchar、nchar、nvarchar、text、ntext，其运算结果也是字符数据类型。,比较运算,常用比较运算符：大于()、大于等于(=)、等于(=)、不等于()、小于(定价ENDELSESETmessage=不存在高于8000元的书PRINTmessage,3WHILE、BREAK和CONTINUEWHILE语句用来实现循环结构，其语法为：WHILE条件表达式语句块功能：当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。BREAK语句退出WHILE循环，CONTINUE语句跳过语句块中的所有其他语句，开始下一次循环。,3WHILE、BREAK和CONTINUE例如：WHILE条件表达式1BENIN语句1IF条件表达式2Continue-有条件使用，无条件转移语句2IF条件表达式3Break-有条件使用，无条件退出语句3END,【例】求1+2+3+100的总和。DECLAREsumINT,iSMALLINTSETi=1Setsum=0While(i=90and分数=80and分数=70and分数=60and分数=5THEN夏天WHENDATEPART(mm,s)=3THEN春天END在查询页中输入以上代码，单击按钮，运行结果如图8-5所示。,5RETURNRETURN语句实现无条件退出批命令、存储过程或触发器。RETURN表达式6GOTO其语法形式为：GOTOlabel,【例】求1+2+3+100的总和。DECLAREsumSMALLINT，iSMALLINTSETi=1Setsum=0BEG:IF(i=100)BEGINSETsum=sum+iSETi=i+1GOTOBEGENDPRINTsum,7.PRINTPRINT语句的作用是在屏幕上显示用户信息。其语法形式为：PRINT字符串|全局变量|局部变量|函数可以输出的数据类型只有：char、nchar、varchar、nvarchar以及符合前述数据类型的全局变量如:VERSION、局部变量、函数等。其他类型数据用函数CAST和CONVERT转换后输出。练习：例PRINT语句.sql,8WAITFOR命令产生一个延时或定时其语法形式为：WAITFORDELAYtime|TIMEtimeDELAY指定等候的时间长度,最长24小时TIME指定要等到的时刻使用的时间格式为HH:MM:SS练习：例延时语句(1).sql例延时语句(2).sql,实训准备作业：,1.使用Case语句输入月份，用print输出该月份对应的季节，要求使用IF语句对月份加以约束。2.用While循环语句编写10以内的阶乘和。3.求1-100中能被7整除的数的和（提示：取模）。4.求除2余1，除3余2，除4余3，除5余4，除6余5的最小自然数。（提示：IF语句+Break应用，取模）,8.6.6注释注释是在程序设计中经常要使用的一种文本字符串，它是在程序代码中不执行的部分，是对程序的说明，恰当使用注释可以提高程序的可读性，使程序代码更易于维护。SQLServer支持两种方式的注释。1)“-”：行注释，以两个减号开始直到本行结束的全部内容是注释部分。可以单独一行，也可以跟在SQL语句之后，允许嵌套使用。2)“/*/”：块注释，以“/*”作为注释文字的开头，“*/”作为注释文字的结尾，其间无论多少行内容，都被作为注释部分。块注释可以从一行开头开始，也可以跟在SQL语句之后，注释内容中允许有“/*”字符组合，但是不允许“*/”的组合。例如：/*程序编号：eg5-10程序说明：查询所有的图书借阅情况*/USELibrary-打开数据库GO-查询图书借阅信息SELECT*FROMLendingGO,谢谢！,End！,8.7游标的使用,关系数据库中的操作会对整个行集起作用。由SELECT语句返回的行集包括满足该语句的WHERE子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。,8.7.1游标概述在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。用户可以通过单独处理每一行来逐条收集信息并对数据逐行进行操作。数据库中的游标类似于高级语言中的指针。一个游标是一个对象，它可以指向一个集合中的某个特定的数据行，并执行用户给定的操作。游标通过以下方式来扩展结果处理：1)允许定位在结果集的特定行。2)从结果集的当前位置检索一行或一部分行。3)支持对结果集中当前位置的行进行数据修改。4)为由其他用户对实现在结果集中的数据库数据所做的更改提供不同级别的可见性支持。5)提供脚本、存储过程和触发器中用于访问结果集中的数据的Transact-SQL语句。,MicrosoftSQLServer支持三种游标实现：1.Transact-SQL游标基于DECLARECURSOR语法，主要用于Transact-SQL脚本、存储过程和触发器。Transact-SQL游标在服务器上实现并由从客户端发送到服务器的Transact-SQL语句管理。它们还可能包含在批处理、存储过程或触发器中。2.应用程序编程接口（API）服务器游标支持OLEDB和ODBC中的API游标函数。API服务器游标在服务器上实现。每次客户端应用程序调用API游标函数时，SQLServerNativeClientOLEDB访问接口或ODBC驱动程序会把请求传输到服务器，以便对API服务器游标进行操作。由于Transact-SQL游标和API服务器游标都在服务器上实现，所以它们统称为服务器游标。,3.客户端游标由SQLServerNativeClientODBC驱动程序和实现ADOAPI的DLL在内部实现。客户端游标通过在客户端高速缓存所有结果集行来实现。每次客户端应用程序调用API游标函数时，SQLServerNativeClientODBC驱动程序或ADODLL会对客户端上高速缓存的结果集行执行游标操作。,8.7.2游标的基本操作使用游标有5个基本的步骤：声明游标、打开游标、提取数据、关闭游标和释放游标。1.声明游标在使用游标之前，首先需要声明游标。使用DECLARECURSOR语句可以定义Transact-SQL服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集查询。具体的语法形式如下：DECLAREcursor_nameINSENSITIVESCROLLCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_name1,column_name2,其中：cursor_name：是所定义的Transact-SQL服务器游标的名称且必须符合标识符的命名规则。INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。SCROLL：制定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVEABSOLUTE）均可用。FIRST取第一行数据；LAST取最后一行数据；PRIOR取前一行数据；NEXT取后一行数据；RELATIVE按相对位置取数据；ABSOLUTE按绝对位置取数据。select_statement：是定义游标结果集的标准SELECT语句。在游标声明的select_statement中不允许使用关键字COMPUTE、COMPUTEBY和INTO。READONLY：禁止通过该游标进行更新。UPDATEOFcolumn_name1,column_name2,：定义游标中可更新的列。如果指定了OFcolumn_name1,column_name2,，则只允许修改所列出的列。如果指定了UPDATE，但为指定列的列表，则可以更新所有列。,【例8-7】声明一个游标cursor1，语句使用：DECLAREcursor1SCROLLCURSORFORSELECT*FROMBook在查询页中输入以上代码，单击按钮，执行结果如图8-7所示。,2.打开游标在使用游标提取数据之前，需要先将游标打开，其语法如下：OPENGLOBALcursor_name|cursor_variable_name其中：GLOBAL：指定cursor_name是指全局游标。cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标；否则cursor_name指的是局部游标。cursor_variable_name：游标变量的名称，该变量引用一个游标。例如：打开在【例8-7】中创建的游标cursor1。OPENcursor在游标打开后，可以使用全局变量CURSOR_ROWS查看打开的游标的数据行。【例8-8】查看游标“cursor1”返回的行数。程序如下：SELECTCURSOR_ROWScursor1游标行数在查询页中输入以上代码，单击按钮，执行结果如图8-8所示。,3.提取数据游标打开之后，便可以使用游标提取某一行的数据。FETCH语句可以通过Transact-SQL服务器游标检索特定行，其语法如下：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name1,variable_name2,其中：NEXT：紧跟当前行返回结果行，并且当前行递增为返回行。如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。PRIOR：返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。FIRST：返回游标中的第一行并将其作为当前行。LAST：返回游标中的最后一行并将其作为当前行。,ABSOLUTEn|nvar：如果n或nvar为正，则返回从游标头开始向后的第n行，并将返回行变成新的当前行。如果n或nvar为负，则返回从游标末尾开始向前的第n行，并将返回行变成新的当前行。如果n或nvar为0，则不返回行。n必须是整数常量，并且nvar的数据类型必须为smallint、tinyint或int。RELATIVEn|nvar：如果n或nvar为正，则返回从当前行开始向后的第n行，并将返回行变成新的当前行。如果n或nvar为负，则返回从当前行开始向前的第n行，并将返回行变成新的当前行。如果n或nvar为0，则返回当前行。在对游标进行第一次提取时，如果在将n或nvar设置为负数或0的情况下指定FETCHRELATIVE，则不返回行。n必须是整数常量，nvar的数据类型必须为smallint、tinyint或int。,INTOvariable_name1,variable_name2,：允许将提取操作的据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。变量的数目必须与游标选择列表中的列数一致。在提取数据过程中，常常需要用到全局变量FETCH_STATUS返回针对连接当前打开的任何游标发出的上一条游标FETCH语句的状态。其返回值为整型0、-1、-2。返回值0表明FETCH语句成功；返回值-1表明FETCH语句失败或行不在结果集中；返回值-2表明提取的行不存在。,【例8-9】在简单的游标中使用FETCH，遍历游标结果集。USELibraryGODECLAREuser_cursorCURSORFORSELECTUserNameFROMUserTbWHERECateName=研究生ORDERBYUserRegOPENuser_cursor-打开游标FETCHNEXTFROMuser_cursor-执行第一次提取WHILEFETCH_STATUS=0-判断是否可以继续提取BEGINFETCHNEXTFROMuser_cursorENDCLOSEuser_cursor-关闭游标DEALLOCATEuser_cursor-释放游标GO,【例8-10】类似于【例8-9】，但FETCH语句的输出存储于局部变量而不是直接返回到客户端。PRINT语句将变量组合成单一字符串并将其返回到客户端。USELibraryGODECLAREnamevarchar(8)-声明变量存放FETCH返回的值DECLAREuser_cursor1CURSOR-声明游标FORSELECTUserNameFROMUserTbWHERECateName=研究生ORDERBYUserRegOPENuser_cursor1-打开游标FETCHNEXTFROMuser_cursor1-执行第一次提取并保存在变量中INTOnameWHILEFETCH_STATUS=0-判断是否可以继续提取BEGINPRINT读者名：+nameFETCHNEXTFROMuser_cursor1INTOnameENDCLOSEuser_cursor1-关闭游标DEALLOCATEuser_cursor1-释放游标GO,【例8-11】声明SCROLL游标并使用其他FETCH选项。USELibraryGOSELECTUserNameFROMUserTb-单独执行查询语句作为使用游标的参照ORDERBYUserRegDECLAREuser_cursor2SCROLLCURSOR