TSQL编程基础.docx
《TSQL编程基础.docx》由会员分享，可在线阅读，更多相关《TSQL编程基础.docx（43页珍藏版）》请在冰豆网上搜索。
TSQL编程基础
TSQL编程基础
目录
T-SQL编程之一，变量和基本语句2
存储过程和触发器10
SQLServer2000自定义函数25
T-SQL编程之一，变量和基本语句
一个标准的计算机语言，大概要提供的必要主要功能是：
变量说明、分支判断、循环和输入输出结果。
T-SQL也一样，具有这些功能，只不过T-SQL的输入和输出不是界面，而是表。
完全精确描述一个计算机语言，大概要很厚的书才能做到，好在目前这些书籍的发行也很多，许多书描述的都很详细。
以下让我们按计算机语言的一般过程，介绍T-SQL计算机语言。
1变量说明
在T-SQL中，变量命名是：
@变量名称
变量名称同一般的计算机语言变量命名没差异，都是英文字母开头。
而类型则同数据库系统的字段类型，不区分大小写，如：
DECLARE@ACHAR（10）
DECLARE@BVARCHAR（50
DECLARE@FFLOAT
数据库上有的类型都可以这么说明，同一般计算机语言不同的是，变量前的@，是有说法的，有一个@，表示局部变量，有两个则是全局变量。
如：
DECLARE@NINT/*说明局部变量@N*/
DECLARE@@MINT/*说明全局变量@@M*/
全局变量是可以跨数据库访问的变量，而局部变量仅仅使用在本数据库的本程序中。
在SQLSERVER中，都提供了一些标准全局变量，如：
@@IDENTITY :
返回最后插入行的标识列的列值。
@@ERROR :
返回最后执行的Transact-SQL语句的错误代码。
没有错误则为零
@@ROWCOUNT :
返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为0。
@@DBTS :
返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的。
上面的全局变量是最常用的
@@SERVERNAME:
返回运行SQLServer2000本地服务器的名称。
@@REMSERVER:
返回登录记录中记载的远程SQLServer服务器的名称。
@@CONNECTIONS:
返回自上次启动SQLServer以来连接或试图连接的次数，用其可让管理人员方便地了解今天所有试图连接服务器的次数。
@@CURSOR_ROWS:
返回最后连接上并打开的游标中当前存在的合格行的数量。
@@VERSION:
返回SQLServer当前安装的日期、版本和处理器类型。
@@CPU_BUSY:
返回自SQLServer最近一次启动以来CPU的工作时间其单位为毫秒。
@@DATEFIRST:
返回使用SETDATEFIRST命令而被赋值的DATAFIRST参数值。
SETDATEFIRST命令用来指定每周的第一天是星期几。
@@FETCH_STATUS:
返回上一次FETCH语句的状态值。
@@IDLE:
返回自SQLServer最近一次启动以来CPU处于空闭状态的时间长短，单位为毫秒。
@@IO_BUSY:
返回自SQLServer最后一次启动以来CPU执行输入输出操作所花费的时间（毫秒）。
@@LANGID:
返回当前所使用的语言ID值。
@@LANGUAGE:
返回当前使用的语言名称。
@@LOCK_TIMEOUT:
返回当前会话等待锁的时间长短其单位为毫秒。
@@MAX_CONNECTIONS:
返回允许连接到SQLServer的最大连接数目。
@@MAX_PRECISION:
返回decimal和numeric数据类型的精确度。
@@NESTLEVEL:
返回当前执行的存储过程的嵌套级数，初始值为0。
@@OPTIONS:
返回当前SET选项的信息。
@@PACK_RECEIVED:
返回SQLServer通过网络读取的输入包的数目。
@@PACK_SENT:
返回SQLServer写给网络的输出包的数目。
@@PACKET_ERRORS:
返回网络包的错误数目。
@@PROCID:
返回当前存储过程的ID值。
@@SERVICENAME:
返回SQLServer正运行于哪种服务状态之下：
如MSSQLServer、MSDTC、SQLServerAgent。
@@SPID:
返回当前用户处理的服务器处理ID值。
@@TEXTSIZE:
返回SET语句的TEXTSIZE选项值SET语句定义了SELECT语句中text或image。
数据类型的最大长度基本单位为字节。
@@TIMETICKS:
返回每一时钟的微秒数。
@@TOTAL_ERRORS:
返回磁盘读写错误数目。
@@TOTAL_READ:
返回磁盘读操作的数目。
@@TOTAL_WRITE:
返回磁盘写操作的数目。
@@TRANCOUNT:
返回当前连接中处于激活状态的事务数目。
对于系统的全局变量，很容易使用，如：
print@@SERVICENAME
则告诉你数据库服务器的类型
print@@LANGUAGE
则回答本服务器的默认语言，一般是简体中文
对变量说明完成后，变量的赋值则是简单的，如：
例1：
SELECT@A=’abcdef’
SELECT@F=3.141592654
也可以有：
SELECT@A=SNO,@N=SAGEFROMSTUDENTWHERESNO=’200215125’
这样的语句是经常使用的，表示变量来自一个查询的结果。
接着：
例2：
DECLARE@NINT
DECLARE@SVARCHAR（100）
SELECT@N=20
SELECT@S=’SELECT*FROMSTUDENTWHERESAGE=’+CONVERT（CHAR
（2）,@N）
EXEC（@S）
则在@S中就是：
SELECT*FROMSTUDENTWHERESAGE=20
实际这是个合法的SQL语句，所以可以执行，于是有：
EXEC（@S）
其结果就是查找表STUDENT中年龄等于20岁的学生记录。
CONVERT（）是个函数，专门用于类型转换，这里的例子是将整数转换成2字节字符。
变量也可以在查询分析器中输出，就是：
例3：
PRINT@A,@F
这些写法都是对的。
由于有变量的引入，所以导致T-SQL成为一种功能很强大的计算机语言。
2条件判断以及GOTO语句
条件判断是计算机语言的重要功能，在T-SQL中，条件判断的语句是：
if条件
…
else
…
或者是：
if条件
…
类型，注意写法和C类似，但条件描述不使用（）也可以。
一个标准的条件判断如：
例4：
DECLARE@AFLOAT
DECLARE@BFLOAT
DECLARE@cFLOAT
SELECT@A=3
SELECT@B=4
SELECT@C=5
IF（@A+@B）>@CAND（@B+@C）>@AAND（@C+@A）>@BAND@A>0AND@B>0AND@C>0
PRINT‘是三角形’
ELSE
PRINT‘不是三角形’
在实际使用中，经常是一个条件满足后要执行多个语句，这个情况下，就要使用复合语句，复合语句的写法是：
BEGIN
…..
END
于是有：
例5：
DECLARE@AFLOAT
DECLARE@BFLOAT
DECLARE@cFLOAT
DECLARE@SFLOAT
SELECT@A=3
SELECT@B=4
SELECT@C=5
IF（@A+@B）>@CAND（@B+@C）>@AAND（@C+@A）>@BAND@A>0AND@B>0AND@C>0
BEGIN
SELECT@S=（@A+@B+@C）/2
SELECT@S=sqrt（@S*（@S-@A）*（@S-@B）*（@S-@C））
PRINT‘三角形面积是：
’+CONVERT（VARCHAR（10）,@S）
PRINT‘三角形面积是：
’+@S
END
ELSE
PRINT‘不是三角形，不能计算’
对于复杂的计算，很多情况下是要求有跳转的，尽管实际使用中总被人诟病、并很认真修改成为循环，但这个功能依然是很有价值的，如下面的计算：
例6
DECLARE@NINT
DECLARE@SFLOAT
SELECT@N=0
SELECT@S=0
L0:
SELECT@S=@S+@N
IF@N<=100
BEGIN
SELECT@N=@N+1
GOTOL0
END
PRINT@S
这个例子中，L0是标号，其写法同其他语言是一样的。
此时，IF语句使用的是：
IF条件
…
类型，这里没有ELSE。
例7
IFEXISTS（SELECT*FROMSCWHEREGRADEISNULL）
DELETEFROMSCWHEREGRADEISNULL
这个语句撤消了一些没成绩的选课记录。
例8
IFEXISTS（SELECT*FROMSYSOBJECTSWHERENAME='MYTABLE'ANDXTYPE='U'）
DROPTABLEMYTABLE
ELSE
BEGIN
CREATETABLEMYTABLE（
AINT,
BVARCHAR（10）,
CFLOAT
）
END
这是一个建立表的程序，在数据库中，能找到SYSOBJECTS这样的系统表，这个表里有本数据库的所有对象，其中列NAME是对象名称，包含用户建立的表名称，XTYPE是对象类型，其中U代表的是用户建立的对象，如表。
这个程序的含义是：
如果已经有表MYTABLE存在，则删除，如果不存在，则建立新表MYTABLE。
注意这个程序，当你已经有表MYTABLE的时候，删除后没有建立新表。
这仅仅是个范例。
T-SQL编程中很少用到GOTO，除非是在做游标方式的逐行处理，这显然是一个非常缓慢的过程，不鼓励大家使用，在后面的例子中，我们会介绍到游标这种技术。
4循环处理，BREAK，CONTINUE语句
T-SQL语言里，循环的结构是：
WHILE条件
循环体
循环体一般是复合语句，在BEGIN。
。
。
END之中
例9
DECLARE@NINT
DECLARE@SFLOAT
SELECT@N=0
SELECT@S=0
WHILE@N<=100
BEGIN
SELECT@N=@N+1
SELECT@S=@S+@N
END
PRINTCONVERT（VARCHAR（10）,@S）
PRINTCONVERT（VARCHAR（3）,@N）
注意循环变量的结果。
BREAK语句会彻底中断循环语句，一般由IF语句引起，下面这个例子则只循环一次
例10
DECLARE@NINT
DECLARE@SFLOAT
SELECT@N=0
SELECT@S=0
WHILE@N<=100
BEGIN
SELECT@N=@N+1
SELECT@S=@S+@N
BREAK
END
PRINT'S='+CONVERT（VARCHAR（10）,@S）
PRINT'N='+CONVERT（VARCHAR（3）,@N）
注意结果，BREAK的意义实际和C、VB等语言的意义一致
CONTINUE也一样，下面的例子是：
例11
DECLARE@NINT
DECLARE@SFLOAT
SELECT@N=0
SELECT@S=0
WHILE@N<=100
BEGIN
SELECT@N=@N+1
CONTINUE
SELECT@S=@S+@N
END
PRINT'S='+CONVERT（VARCHAR（10）,@S）
PRINT'N='+CONVERT（VARCHAR（3）,@N）
我们可以分析出CONTINUE提前循环，使得@N在不断累加而@S不再累加。
这个语句含义同C、VB等也是一样的含义。
5CASE语句
这是个功能非常强大的语句，如：
例12
selectsno,Cname=
casecnowhen2then'数学'
when3then'信息系统'
when4then'操作系统'
end,
grade
fromsc
其中：
CName=
casecnowhen2then'数学'
when3then'信息系统'
when4then'操作系统'
end
代表一个字段假名Cname的取值，然后将代码换成后面的名称，如“数学”。
更加复杂和完整的应该是：
例13
selectsno,课程=
casecnowhen2then（selectcnamefromcoursewherecno=2）
when3then（selectcnamefromcoursewherecno=3）
when4then（selectcnamefromcoursewherecno=4）
end,gradefromsc
实际中，这个范例并不经常使用，一般用诸如：
selectstudent.sno,student.sname,ame,sc.gradefromstudent,course,scwherestudent.sno=sc.snoando=o
这样的三表等值合并即可，而且适应性更好。
例13的过程显然是复杂了，这里仅仅是提供个范例，或许一些特殊场合下会用到这个范例。
6EXECUTE语句
一般写做EXEC也可以，用来执行一些SQL语句，如：
例16：
DECLARE@SVARCHAR（100）
SELECT@S=’SELECT*FROMSTUDENT’
EXEC（@S）
这个语句很有用，但一些写法要注意，例如：
SELECT*FROMSTUDENTWHERESNO=‘200215121’
写入一个字符串变量里则要注意’这个字符的写法，符号’’代表’本身，如同C语言的//代表‘/’一样的道理，这个地方很难凑合好一个字符串，一定要注意。
如下例
例16：
DECLARE@SVARCHAR（100）
SELECT@S='SELECT*FROMSTUDENTWHERESNO=''200215121'''
EXEC（@S）
再看下面的范例
例17：
DECLARE@SVARCHAR（100）
DECLARE@SNOCHAR（10）
SELECT@SNO='200215121'
SELECT@S='SELECT*FROMSTUDENTWHERESNO='''+@SNO+''''
PRINT@S
这个范例中，在SNO=’’’中，前面的两个’’代表’本身，后一个是和SELECT前的’配对；
语句最后是4个’’’’，其中第一个和最后一个是字符串常量配对的，中间两个说明是’本身。
做这个工作，头脑中的’个数、以及配对情况一定要清楚。
6游标
游标是一种逐行处理数据库的手段，不鼓励经常使用，但个别情况下，如果按行处理问题，也需要游标技术，诸如按人创建视图，建立特殊的报表等等，先请注意以下表的结果。
@@FETCH_STATUS=0
FETCH语句成功。
@@FETCH_STATUS=-1
FETCH语句失败或此行不在结果集中。
@@FETCH_STATUS=-2
被提取的行不存在。
一个游标的典型应用如下：
首先是说明一个游标变量，游标标量肯定是一个查询的结果，如：
DECLAREMYCURSORCURSORFORSELECTSNOFROMSTUDENT
然后打开游标，用OPENMYCURSOR
其次是执行FETCHNEXTFROM，将查询结果送入一个变量，循环查询，直到@@FETCH_STATUS–2
最后，则要释放游标。
完整的过程入下：
例18
DECLARE@SNOCHAR（10）
DECLAREMYCURSORCURSORFORSELECTSNOFROMSTUDENT
OPENMYCURSOR
FETCHNEXTFROMMYCURSORINTO@SNO
PRINT@SNO
WHILE（@@FETCH_STATUS<>-1）
BEGIN
FETCHNEXTFROMMYCURSORINTO@SNO
PRINT@SNO
END
DEALLOCATEMYCURSOR/*释放游标，切记不释放的话，拖服务器速度*/
这个游标处理程序，是按行逐个显示学生的学号。
上面的例子，很容易修改成一个按学号建立视图的程序，如：
例19
DECLAREMYCURSORCURSORFORSELECTSNOFROMSTUDENT
DECLARE@SNOCHAR（10）
DECLARE@SVARCHAR（200）
OPENMYCURSOR
FETCHNEXTFROMMYCURSORINTO@SNO
SELECT@S='CREATEVIEWVSCC'+@SNO+'ASSELECTStudent.SNO,STUDENT.SNAME,SC.CnoASCno,SC.GradeASGadeFROMStudent,scwhereStudent.Sno=SC.Snoandstudent.sno='''+@SNO+''''
PRINT@s
EXEC（@S）
WHILE（@@FETCH_STATUS<>-1）
BEGIN
FETCHNEXTFROMMYCURSORINTO@SNO
SELECT@S='CREATEVIEWVSCC'+@SNO+'ASSELECTStudent.SNO,STUDENT.SNAME,SC.CnoASCno,SC.GradeASGadeFROMStudent,scwhereStudent.Sno=SC.Snoandstudent.sno='''+@SNO+''''
PRINT@S
EXEC（@S）
END
DEALLOCATEMYCURSOR
这个程序，按每个学号，先凑一个建立视图的SQL语言、并保存在@S中，然后EXEC（@S）。
这种凑SQL字符串并执行的方法中，不断使用PRINT打印显示SQL字符串、是非常重要的手段，对于排错很有必要。
本节关于T-SQL的编程，到此结束，有关T-SQL构造子程序（存储过程、出发器）的概念，下节继续讨论。
涉及变量定义、基本语句的编程，请务必记下。
存储过程和触发器
无论哪个计算机的编程工具，编写出好的子程序、一直是很多程序员的梦想。
C语言之所以流行一时，与C语言下庞大的函数库密不可分。
使用T-SQL语言，编程写出子程序/函数的手段，就是编写存储过程和触发器，下面逐一介绍。
一存储过程
存储过程的格式是：
CREATEPROCEDURE存储过程名称（参数）AS
一些SQL语句
例1有表AVGRADE，表结构如下：
SDEPTVARCHAR（50）
GRADE1INT
GRADE2INT
GRADE3INT
GRADE4INT
GRADE5INT
该表用来保存各个专业的平均成绩，其中GRADE1，…GRADE5，分别代表CNO=1-5号课程的平均成绩。
设计子程序或者函数的思想，和其他语言是一样的，子程序仅仅处理好一个专业一个课程的平均成绩即可，其余的，就让主程序循环吧。
于是有：
CREATEPROCGETAVGRADE（@MYDEPTVARCHAR（20））AS
DECLARE@A1INT
DECLARE@A2INT
DECLARE@A3INT
DECLARE@A4INT
DECLARE@A5INT
DECLARE@SVARCHAR（100）
SELECT@A1=AVG（GRADE）FROMSCWHERECNO=1ANDSNOIN（SELECTSNOFROMSTUDENTWHERESDEPT=@MYDEPT）
SELECT@A2=AVG（GRADE）FROMSCWHERECNO=2ANDSNOIN（SELECTSNOFROMSTUDENTWHERESDEPT=@MYDEPT）
SELECT@A3=AVG（GRADE）FROMSCWHERECNO=3ANDSNOIN（SELECTSNOFROMSTUDENTWHERESDEPT=@MYDEPT）
SELECT@A4=AVG（GRADE）FROMSCWHERECNO=4ANDSNOIN（SELECTSNOFROMSTUDENTWHERESDEPT=@MYDEPT）
SELECT@A5=AVG（GRADE）FROMSCWHERECNO=5ANDSNOIN（SELECTSNOFROMSTUDENTWHERESDEPT=@MYDEPT）
INSERTINTOAVGRADE（SDEPT,GRADE1,GRADE2,GRADE3,GRADE4,GRADE5）VALUES（@MYDEPT,@A1,@A2,@A3,@A4,@A5）
注意此处，使用CREATEPROC、而不是CREATEPROCEDURE，这个写法也是正确的，这个存储过程有个参数@MYDEPT，就是所在院系。
上面的这个范例中，表AVGRADE纯粹是用户报表在数据库系统中的翻版，和任何数据库设计方法没关系，这个把数据库表自身内模式结构转换成用户用户外模式的做法，很有代表性，经常会有把竖表（数据库内模式表）转换成横表（用户外模式表）的要求，这个要求下，例1就是个典型范例。
一些大型的报表系统，也是利用这样的表作为报表的基础，例如著名的CRYSTAL报表系统。
上述存储过程，写入查询分析器后，并不会有什么现象，但如果我们新开一个查询窗口，写入：
DELETEFROMAVGRADE
EXECGETAVGRADE'CS'
SELECT*FROMAVGRADE
则会看到这个存储过程执行的结果，的确很简单。
在大多计算机开发程序中，如C#，也可以使用如：
StringSQL=”ExecGETAVGRADE‘CS’”;
MyDB.Execute（SQL）;
这样的语句来执行这个存储过程，假如权限对，结果就有了，这比在C#、VB等程序中实现要高效的多。
实际做法中，上例不仅仅CS专业，也可能是所有专业，那么就需要一个循环来读每个专业，无重复读专业是用：
selectdistinctsdeptfromstudent
但逐一读出，则需要游标方式：
例2按课程、统计所有专业的学生平均成绩
参照上节
升级会员 