SQLSERVER2000教程

第七章Transact-SQL编程第一节变量（1）局部变量A．TSQL局部变量：是可以保存特定类型的单个数据值的对象。一般说来在一个批处理中被声明、定义，在这个批处理（也可能是存储过程或触发器）中用SQL语言对这个变量赋值，或使用这个变量已经被赋予的值。当批处理结束后，这个局部变量的生命周期就结束了。作用：作为计数器在循环中使用；作为返回值；保存临时数据等；B．声明局部变量：格式：Declare@变量名1数据类型，@变量名2数据类型……说明：Declare的使用：（1）指派名称：第一个字符必为@+变量名；（2）指派系统提供或用户定义的数据类型和长度；（3）将值设置为NULL;示例：Declare@myCountintC．给变量赋值：Set语句：如Set@myCount=100Select语句：如Select@myCount=100综合示例UsePubsGoDeclare@findvarchar(30)Set@find=’Ring%’Selectau_lname,au_fname,phoneFromauthorsWhereau_lnamelike@find(2)全局变量A)全局变量：变量名前用@@表示；在SQLServer7.0及以后的版本中,全局变量作为函数据形式被使用；B)一共30多个对于一般的用户来说，一般不用全局变量；C)SQLServer提供的系统全局变量不用声明直接使用使用：也可声明自定义的全局变量（类似局部变量）；常用全局变量如下@@CONNECTIONS:返回自上次启动SQLServer以来连接或试图连接的次数，用其可让管理人员方便地了解今天所有试图连接服务器的次数。@@DATEFIRST:返回使用SETDATEFIRST命令而被赋值的DATAFIRST参数值。SETDATEFIRST命令用来指定每周的第一天是星期几。@@SERVICENAME:返回SQLServer正运行于哪种服务状态之下：如MSSQLServer、MSDTC、SQLServerAgent。@@TEXTSIZE:返回SET语句的TEXTSIZE选项值SET语句定义了SELECT语句中text或image。数据类型的最大长度基本单位为字节。@@ROWCOUNT:返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为0。@@IDLE:返回自SQLServer最近一次启动以来CPU处于空闭状态的时间长短，单位为毫秒。@@IO_BUSY:返回自SQLServer最后一次启动以来CPU执行输入输出操作所花费的时间(毫秒)。@@CPU_BUSY:返回自SQLServer最近一次启动以来CPU的工作时间其单位为毫秒。@@DBTS:返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的。@@CURSOR_ROWS:返回最后连接上并打开的游标中当前存在的合格行的数量。@@REMSERVER:返回登录记录中记载的远程SQLServer服务器的名称。@@VERSION:返回SQLServer当前安装的日期、版本和处理器类型。@@MAX_CONNECTIONS:返回允许连接到SQLServer的最大连接数目。@@PACK_RECEIVED:返回SQLServer通过网络读取的输入包的数目。@@NESTLEVEL:返回当前执行的存储过程的嵌套级数，初始值为0。@@LOCK_TIMEOUT:返回当前会话等待锁的时间长短其单位为毫秒。@@MAX_PRECISION:返回decimal和numeric数据类型的精确度。@@SERVERNAME:返回运行SQLServer2000本地服务器的名称。@@PACK_SENT:返回SQLServer写给网络的输出包的数目。@@ERROR:返回最后执行的Transact-SQL语句的错误代码。@@TRANCOUNT:返回当前连接中处于激活状态的事务数目。@@FETCH_STATUS:返回上一次FETCH语句的状态值。@@SPID:返回当前用户处理的服务器处理ID值。@@IDENTITY:返回最后插入行的标识列的列值。@@PACKET_ERRORS:返回网络包的错误数目。@@TOTAL_ERRORS:返回磁盘读写错误数目。@@TOTAL_WRITE:返回磁盘写操作的数目。@@LANGID:返回当前所使用的语言ID值。@@LANGUAGE:返回当前使用的语言名称。@@TOTAL_READ:返回磁盘读操作的数目。@@TIMETICKS:返回每一时钟的微秒数。@@OPTIONS:返回当前SET选项的信息。@@PROCID:返回当前存储过程的ID值。本文出自51CTO.COM技术博客SQLSERVER2000教程-第七章Transact-SQL编程第二节流程控制命令1)块结构：Begin----End2)If-else-条件分支：如：UsePubsGo--这是批处理标识符,表示上面的代码交给服务器编译If(selectavg(price)fromtitleswheretype=’mod_cook’)15--判断表中有此记录BeginPrint‘下列书是优秀的烹调书籍’Selectsubstring(title,1,35)astitleFromtitlesWheretype=’mod_book’EndElsePrint‘这是价格较为昂贵的烹调书籍’3).waitforWaitfordelay‘时间’延迟多长时间Waitfortime‘时间’具体某个时间执行例1：waitfordelay’00:00:03’--将在3秒钟之后执行select语句select*fromstudent例2：waitfortime’22:30:02’--将在22:30:02s时执行select语句select*fromstudent14)While（条件语句）条件循环示例：Createtabletest(idint,namechar(3))GoDeclare@fcountintset@fcount=0while(@fcount6)Begin--向字段id编号及name添加字符并转换为ASCII码对应的字符InsertintoTestvalues(@Fcount,CHAR(@Fcount+ASCII(‘a’)))Set@Fcount=@Fcount+1End5)Goto说明：用来改变程序执行的流程，使程序跳到标有标识和程序继续执行；语法：Goto标识符示例：分行打印字符1至5Declare@FcountintBeginSelect@Fcount=1Label:PrintCast(@Fcountasvarchar)Select@Fcount=@Fcount+1While@Fcount6GotoLabelEnd6)Return语句说明：无条件终止查询、存储过程或批处理处理。存储过程或批处理中Return语句后面的语句都不执行；当在存储过程中使用Return语句时，此语句可以指定返回给调用的应用程序、批处理或过程的整数值。如Return未指定值，则存储过程返回0。大多数存储过程按常规使用返回代码表示存储过程的成功或失败。没有发生错误时存储过程返回0。任何非零值表示有错误发生。语法：Return[integer_expression]（注：此处到讲解存储过程时再介绍）7)Break退出while循环8)Continue结束本次循环9)Case表达式格式：case判断表达式（变量）/选择条件表达式when表达式/条件then语句………………when表达式then语句end例：selectname,chengji=casewhenprice60then‘不及格’whenprice60then‘及格’endfromstudent10)批处理语句Go本文出自51CTO.COM技术博客SQLSERVER2000教程-第七章Transact-SQL编程第三节存储过程1．存储过程的概述存储过程是SQL语句的预编译集合，它存储在数据库内，可由应用程序通过一个调用执行，而且充许用户声明变量、有条件执行以及其它强大的功能。使用存储过程可以使程序模块化，可以在服务器端更快的执行，可以减少网络流量，还可以增强安全性。2．存储过程的创建createprocedure存储过程名[参数列表]asSQL语句3．存储过程的执行execprocedure参数1、参数2。。。。。。4．修改存储过程alterprocedure存储过程名5．删除存储过程dropprocedure存储过程名6．例题学生表（学号、姓名、住址、年级）成绩表（考号、学号、成绩）例1、创建立不带参数的存储过程--查询所有学生的考试成绩createprocproc_student1asselect姓名，成绩from学生表，成绩表where学生表.学号=成绩表.学号执行：EXECproc_student1例2、创建带输入参数的存储过程--查询某位学生的成绩createprocproc_student2@stuNamevarchar(8)asselect姓名，成绩from学生表innerjoin成绩表on学生表.学号=成绩表.学号where姓名=@stuName执行：EXECproc_student2‘Mary’例3、创建带输出参数的存储过程--按照所要查找班级求出平均分createprocproc_student3@Yearchar(20),@SumCjintoutputasselect@SumCj=sum(成绩)from学生表innerjoin成绩表on学生表.学号=成绩表.学号where年级=@Year执行：declare@sumCj1intEXECproc_student3‘一年二班’,@sumCj1outputPrint@sumCj1例4、创建带返回值的存储过程Return语句可以带回一个值，也可带回结果集。程序如果遇到return，那么return语句以下的所有语句将不再执行，程序返回存储过程执行的起点。--将所以查找的班级返回平均值createprocproc_student4@Yearchar(2)asbegindeclare@avgCjintifexists(select*fromstudentwhere年级=@Year)beginselect@avgCj=avg(成绩)from学生表innerjoin成绩表on学生表.学号=成绩表.学号where年级=成绩表.学号endelseset@avgCj=-1return@avgCjend本文出自51CTO.COM技术博客SQLSERVER2000教程-第七章Transact-SQL编程第四节游标1．游标概述由于SQLServer是使用结果集来处理数据，因此当需要逐条处理表中的记录时就必须使用游标来处理。游标一般被定义和使用在服务器端，当游标开启后，服务器为每一个游标保持一个指针，用来前后检索数据。当游标大量的使用时，服务器端的负担很重。2．游标的特性A．种类：动态游标、静态游标和、键集游标和仅向前游标；动态游标（Dynamic）：可以监测对结果集的所有操作（增、删、改），前后滚动。当滚动时，动态游标反映结果集中所做的修改。静态游标（Static）：不能监测其他用户的所有操作，前后滚动。以快照形式把当前表存到Tempdb临时表中，执行指令后将结果集带给游标，新的数据值不会显示在静态游标中。键集游标（Keyset）：可以监测用户对数据的修改，前后滚动。在Tempdb中利用主键实现对数据检索。对于仅向前游标（Fast_only）：该游标只能向前滚动。B．功能：可设置游标结果为只读的，或可更新的；游标的移动类型等；3游标的状态--声明Declarecursor_nameCursorforSelect语句--打开Opencursor_name--提取数据Fetchnextfromcursor_nameinto变量1，变量2--关闭游标Closecursor_name--释放游标Deallocatecursor4．例题事例1、查询某个书店的订单的数量--存储过程名称S