TSQL编程基础.docx

1、TSQL编程基础TSQL编程基础目录T-SQL编程之一，变量和基本语句 2存储过程和触发器 10SQL Server2000自定义函数 25T-SQL编程之一，变量和基本语句一个标准的计算机语言，大概要提供的必要主要功能是：变量说明、分支判断、循环和输入输出结果。T-SQL也一样，具有这些功能，只不过T-SQL的输入和输出不是界面，而是表。完全精确描述一个计算机语言，大概要很厚的书才能做到，好在目前这些书籍的发行也很多，许多书描述的都很详细。以下让我们按计算机语言的一般过程，介绍T-SQL计算机语言。1 变量说明在T-SQL中，变量命名是：变量名称变量名称同一般的计算机语言变量命名没差异，都是

2、英文字母开头。而类型则同数据库系统的字段类型，不区分大小写，如：DECLARE A CHAR(10)DECLARE B VARCHAR(50DECLARE F FLOAT数据库上有的类型都可以这么说明，同一般计算机语言不同的是，变量前的，是有说法的，有一个，表示局部变量，有两个则是全局变量。如：DECLARE N INT /*说明局部变量N */DECLARE M INT /*说明全局变量M */全局变量是可以跨数据库访问的变量，而局部变量仅仅使用在本数据库的本程序中。在SQLSERVER中，都提供了一些标准全局变量，如：IDENTITY : 返回最后插入行的标识列的列值。 ERROR : 返

3、回最后执行的Transact-SQL语句的错误代码。没有错误则为零ROWCOUNT : 返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为0。 DBTS : 返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的。 上面的全局变量是最常用的SERVERNAME: 返回运行SQL Server 2000本地服务器的名称。 REMSERVER: 返回登录记录中记载的远程SQL Server服务器的名称。 CONNECTIONS : 返回自上次启动SQL Server以来连接或试图连接的次数，用其可让管理人员方便地了解今天所有试图连接服务器的次数。 CURSOR_ROWS : 返回最后

4、连接上并打开的游标中当前存在的合格行的数量。 VERSION: 返回SQL Server当前安装的日期、版本和处理器类型。 CPU_BUSY: 返回自SQL Server最近一次启动以来CPU的工作时间其单位为毫秒。 DATEFIRST: 返回使用SET DATEFIRST命令而被赋值的DATAFIRST参数值。SET DATEFIRST命令用来指定每周的第一天是星期几。 FETCH_STATUS : 返回上一次FETCH语句的状态值。 IDLE: 返回自SQL Server最近一次启动以来CPU处于空闭状态的时间长短，单位为毫秒。 IO_BUSY: 返回自SQL Server最后一次启动以来

5、CPU执行输入输出操作所花费的时间(毫秒)。 LANGID: 返回当前所使用的语言ID值。 LANGUAGE: 返回当前使用的语言名称。 LOCK_TIMEOUT: 返回当前会话等待锁的时间长短其单位为毫秒。 MAX_CONNECTIONS : 返回允许连接到SQL Server的最大连接数目。 MAX_PRECISION : 返回decimal 和 numeric数据类型的精确度。 NESTLEVEL: 返回当前执行的存储过程的嵌套级数，初始值为0。 OPTIONS: 返回当前SET选项的信息。 PACK_RECEIVED : 返回SQL Server通过网络读取的输入包的数目。 PACK_

6、SENT: 返回SQL Server写给网络的输出包的数目。 PACKET_ERRORS : 返回网络包的错误数目。 PROCID: 返回当前存储过程的ID值。 SERVICENAME: 返回SQL Server正运行于哪种服务状态之下：如 MS SQLServer、MSDTC、SQLServerAgent。 SPID: 返回当前用户处理的服务器处理ID值。 TEXTSIZE: 返回SET语句的TEXTSIZE选项值SET语句定义了SELECT语句中text或image。数据类型的最大长度基本单位为字节。 TIMETICKS: 返回每一时钟的微秒数。 TOTAL_ERRORS : 返回磁盘读写

7、错误数目。 TOTAL_READ: 返回磁盘读操作的数目。 TOTAL_WRITE: 返回磁盘写操作的数目。 TRANCOUNT: 返回当前连接中处于激活状态的事务数目。对于系统的全局变量，很容易使用，如：print SERVICENAME则告诉你数据库服务器的类型print LANGUAGE则回答本服务器的默认语言，一般是简体中文对变量说明完成后，变量的赋值则是简单的，如：例1：SELECT A=abcdefSELECT F=3.141592654也可以有：SELECT A=SNO,N=SAGE FROM STUDENT WHERE SNO=200215125这样的语句是经常使用的，表示变量

8、来自一个查询的结果。接着：例2：DECLARE N INTDECLARE S VARCHAR(100)SELECT N=20SELECT S=SELECT * FROM STUDENT WHERE SAGE=+CONVERT(CHAR(2),N)EXEC（S）则在S中就是：SELECT * FROM STUDENT WHERE SAGE=20实际这是个合法的SQL语句，所以可以执行，于是有：EXEC（S）其结果就是查找表STUDENT中年龄等于20岁的学生记录。CONVERT()是个函数，专门用于类型转换，这里的例子是将整数转换成2字节字符。变量也可以在查询分析器中输出，就是：例3：PRINT

9、 A,F这些写法都是对的。由于有变量的引入，所以导致T-SQL成为一种功能很强大的计算机语言。2 条件判断以及GOTO语句条件判断是计算机语言的重要功能，在T-SQL中，条件判断的语句是：if 条件else或者是：if 条件类型，注意写法和C类似，但条件描述不使用（）也可以。一个标准的条件判断如：例4：DECLARE A FLOATDECLARE B FLOATDECLARE c FLOATSELECT A=3SELECT B=4SELECT C=5IF (A+B)C AND (B+C)A AND (C+A)B AND A0 AND B0 AND C0 PRINT 是三角形ELSE PRINT

10、 不是三角形在实际使用中，经常是一个条件满足后要执行多个语句，这个情况下，就要使用复合语句，复合语句的写法是：BEGIN.END于是有：例5：DECLARE A FLOATDECLARE B FLOATDECLARE c FLOATDECLARE S FLOATSELECT A=3SELECT B=4SELECT C=5IF (A+B)C AND (B+C)A AND (C+A)B AND A0 AND B0 AND C0 BEGIN SELECT S=(A+B+C)/2 SELECT S=sqrt(S*(S-A)*(S-B)*(S-C) PRINT 三角形面积是：+CONVERT(VARCH

11、AR(10),S) PRINT 三角形面积是：+S ENDELSE PRINT 不是三角形，不能计算对于复杂的计算，很多情况下是要求有跳转的，尽管实际使用中总被人诟病、并很认真修改成为循环，但这个功能依然是很有价值的，如下面的计算：例6DECLARE N INTDECLARE S FLOATSELECT N=0SELECT S=0L0:SELECT S=S+NIF N=100 BEGIN SELECT N=N+1 GOTO L0 ENDPRINT S这个例子中，L0是标号，其写法同其他语言是一样的。此时，IF语句使用的是：IF 条件类型，这里没有ELSE。例7IF EXISTS(SELECT

12、* FROM SC WHERE GRADE IS NULL) DELETE FROM SC WHERE GRADE IS NULL这个语句撤消了一些没成绩的选课记录。例8IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=MYTABLE AND XTYPE=U) DROP TABLE MYTABLEELSE BEGIN CREATE TABLE MYTABLE( A INT, B VARCHAR(10), C FLOAT ) END这是一个建立表的程序，在数据库中，能找到SYSOBJECTS这样的系统表，这个表里有本数据库的所有对象，其中列NAME是对象

13、名称，包含用户建立的表名称，XTYPE是对象类型，其中U代表的是用户建立的对象，如表。这个程序的含义是：如果已经有表MYTABLE存在，则删除，如果不存在，则建立新表MYTABLE。注意这个程序，当你已经有表MYTABLE的时候，删除后没有建立新表。这仅仅是个范例。T-SQL编程中很少用到GOTO，除非是在做游标方式的逐行处理，这显然是一个非常缓慢的过程，不鼓励大家使用，在后面的例子中，我们会介绍到游标这种技术。4 循环处理，BREAK，CONTINUE语句T-SQL语言里，循环的结构是：WHILE 条件循环体循环体一般是复合语句，在BEGIN。END之中例9DECLARE N INTDECL

14、ARE S FLOATSELECT N=0SELECT S=0WHILE N=100 BEGIN SELECT N=N+1 SELECT S=S+N ENDPRINT CONVERT(VARCHAR(10),S)PRINT CONVERT(VARCHAR(3),N)注意循环变量的结果。BREAK语句会彻底中断循环语句，一般由IF语句引起，下面这个例子则只循环一次例10DECLARE N INTDECLARE S FLOATSELECT N=0SELECT S=0WHILE N=100 BEGIN SELECT N=N+1 SELECT S=S+N BREAK ENDPRINT S=+CONVE

15、RT(VARCHAR(10),S)PRINT N=+CONVERT(VARCHAR(3),N)注意结果，BREAK的意义实际和C、VB等语言的意义一致CONTINUE也一样，下面的例子是：例11DECLARE N INTDECLARE S FLOATSELECT N=0SELECT S=0WHILE N=100 BEGIN SELECT N=N+1 CONTINUE SELECT S=S+N ENDPRINT S=+CONVERT(VARCHAR(10),S)PRINT N=+CONVERT(VARCHAR(3),N)我们可以分析出CONTINUE提前循环，使得N在不断累加而S不再累加。这个语

16、句含义同C、VB等也是一样的含义。5 CASE语句这是个功能非常强大的语句，如：例12select sno,Cname=case cno when 2 then 数学 when 3 then 信息系统 when 4 then 操作系统end,gradefrom sc其中：CName=case cno when 2 then 数学 when 3 then 信息系统 when 4 then 操作系统end代表一个字段假名Cname的取值，然后将代码换成后面的名称，如“数学”。更加复杂和完整的应该是：例13select sno,课程=case cno when 2 then (select cnam

17、e from course where cno=2) when 3 then (select cname from course where cno=3) when 4 then (select cname from course where cno=4)end, grade from sc实际中，这个范例并不经常使用，一般用诸如：select student.sno,student.sname,ame,sc.grade from student,course,sc where student.sno=sc.sno and o=o这样的三表等值合并即可，而且适应性更好。例13的过程显然是复杂了

18、，这里仅仅是提供个范例，或许一些特殊场合下会用到这个范例。6 EXECUTE语句一般写做EXEC也可以，用来执行一些SQL语句，如：例16：DECLARE S VARCHAR(100)SELECT S=SELECT * FROM STUDENTEXEC (S)这个语句很有用，但一些写法要注意，例如：SELECT * FROM STUDENT WHERE SNO=200215121写入一个字符串变量里则要注意这个字符的写法，符号代表本身，如同C语言的/代表/一样的道理，这个地方很难凑合好一个字符串，一定要注意。如下例例16：DECLARE S VARCHAR(100)SELECT S=SELEC

19、T * FROM STUDENT WHERE SNO=200215121EXEC (S)再看下面的范例例17：DECLARE S VARCHAR(100)DECLARE SNO CHAR(10)SELECT SNO=200215121SELECT S=SELECT * FROM STUDENT WHERE SNO=+SNO+PRINT S这个范例中，在SNO=中，前面的两个代表本身，后一个是和SELECT前的配对；语句最后是4个，其中第一个和最后一个是字符串常量配对的，中间两个说明是本身。做这个工作，头脑中的个数、以及配对情况一定要清楚。6 游标游标是一种逐行处理数据库的手段，不鼓励经常使用，

20、但个别情况下，如果按行处理问题，也需要游标技术，诸如按人创建视图，建立特殊的报表等等，先请注意以下表的结果。FETCH_STATUS =0FETCH 语句成功。FETCH_STATUS =-1FETCH 语句失败或此行不在结果集中。FETCH_STATUS =-2被提取的行不存在。一个游标的典型应用如下：首先是说明一个游标变量，游标标量肯定是一个查询的结果，如：DECLARE MYCURSOR CURSOR FOR SELECT SNO FROM STUDENT然后打开游标，用OPEN MYCURSOR其次是执行FETCH NEXT FROM，将查询结果送入一个变量，循环查询，直到FETCH_

21、STATUS 2最后，则要释放游标。完整的过程入下：例18DECLARE SNO CHAR(10)DECLARE MYCURSOR CURSOR FOR SELECT SNO FROM STUDENTOPEN MYCURSORFETCH NEXT FROM MYCURSOR INTO SNOPRINT SNOWHILE (FETCH_STATUS-1) BEGIN FETCH NEXT FROM MYCURSOR INTO SNO PRINT SNO ENDDEALLOCATE MYCURSOR /*释放游标，切记不释放的话，拖服务器速度*/这个游标处理程序，是按行逐个显示学生的学号。上面的例

22、子，很容易修改成一个按学号建立视图的程序，如：例19DECLARE MYCURSOR CURSOR FOR SELECT SNO FROM STUDENTDECLARE SNO CHAR(10)DECLARE S VARCHAR(200)OPEN MYCURSORFETCH NEXT FROM MYCURSOR INTO SNOSELECT S=CREATE VIEW VSCC+SNO+ AS SELECT Student.SNO,STUDENT.SNAME, SC.Cno AS Cno, SC.Grade AS Gade FROM Student,sc where Student.Sno =

23、 SC.Sno and student.sno=+SNO+PRINT sEXEC (S)WHILE (FETCH_STATUS-1) BEGIN FETCH NEXT FROM MYCURSOR INTO SNO SELECT S=CREATE VIEW VSCC+SNO+ AS SELECT Student.SNO,STUDENT.SNAME, SC.Cno AS Cno, SC.Grade AS Gade FROM Student,sc where Student.Sno = SC.Sno and student.sno=+SNO+ PRINT S EXEC (S) ENDDEALLOCA

24、TE MYCURSOR这个程序，按每个学号，先凑一个建立视图的SQL语言、并保存在S中，然后EXEC（S）。这种凑SQL字符串并执行的方法中，不断使用PRINT打印显示SQL字符串、是非常重要的手段，对于排错很有必要。本节关于T-SQL的编程，到此结束，有关T-SQL构造子程序（存储过程、出发器）的概念，下节继续讨论。涉及变量定义、基本语句的编程，请务必记下。存储过程和触发器无论哪个计算机的编程工具，编写出好的子程序、一直是很多程序员的梦想。C语言之所以流行一时，与C语言下庞大的函数库密不可分。使用T-SQL语言，编程写出子程序/函数的手段，就是编写存储过程和触发器，下面逐一介绍。一 存储过程

25、存储过程的格式是：CREATE PROCEDURE 存储过程名称 (参数) AS一些SQL语句例1 有表AVGRADE，表结构如下：SDEPT VARCHAR(50)GRADE1 INTGRADE2 INTGRADE3 INTGRADE4 INTGRADE5 INT该表用来保存各个专业的平均成绩，其中GRADE1，GRADE5，分别代表CNO=1-5号课程的平均成绩。设计子程序或者函数的思想，和其他语言是一样的，子程序仅仅处理好一个专业一个课程的平均成绩即可，其余的，就让主程序循环吧。于是有：CREATE PROC GETAVGRADE(MYDEPT VARCHAR(20) ASDECLARE

26、 A1 INTDECLARE A2 INTDECLARE A3 INTDECLARE A4 INTDECLARE A5 INTDECLARE S VARCHAR(100)SELECT A1=AVG(GRADE) FROM SC WHERE CNO=1 AND SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT=MYDEPT)SELECT A2=AVG(GRADE) FROM SC WHERE CNO=2 AND SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT=MYDEPT)SELECT A3=AVG(GRADE) FR

27、OM SC WHERE CNO=3 AND SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT=MYDEPT)SELECT A4=AVG(GRADE) FROM SC WHERE CNO=4 AND SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT=MYDEPT)SELECT A5=AVG(GRADE) FROM SC WHERE CNO=5 AND SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT=MYDEPT)INSERT INTO AVGRADE(SDEPT,GRADE1, G

28、RADE2, GRADE3, GRADE4, GRADE5) VALUES (MYDEPT,A1,A2,A3,A4,A5)注意此处，使用CREATE PROC、而不是CREATE PROCEDURE，这个写法也是正确的，这个存储过程有个参数MYDEPT，就是所在院系。上面的这个范例中，表AVGRADE纯粹是用户报表在数据库系统中的翻版，和任何数据库设计方法没关系，这个把数据库表自身内模式结构转换成用户用户外模式的做法，很有代表性，经常会有把竖表（数据库内模式表）转换成横表（用户外模式表）的要求，这个要求下，例1就是个典型范例。一些大型的报表系统，也是利用这样的表作为报表的基础，例如著名的CRY

29、STAL报表系统。上述存储过程，写入查询分析器后，并不会有什么现象，但如果我们新开一个查询窗口，写入：DELETE FROM AVGRADEEXEC GETAVGRADE CSSELECT * FROM AVGRADE则会看到这个存储过程执行的结果，的确很简单。在大多计算机开发程序中，如C#，也可以使用如：String SQL=”Exec GETAVGRADE CS”;MyDB.Execute(SQL);这样的语句来执行这个存储过程，假如权限对，结果就有了，这比在C#、VB等程序中实现要高效的多。实际做法中，上例不仅仅CS专业，也可能是所有专业，那么就需要一个循环来读每个专业，无重复读专业是用：select distinct sdept from student但逐一读出，则需要游标方式：例2 按课程、统计所有专业的学生平均成绩参照上节