TSQL编程基础.docx
- 文档编号:9800560
- 上传时间:2023-02-06
- 格式:DOCX
- 页数:43
- 大小:32.58KB
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按课程、统计所有专业的学生平均成绩
参照上节
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- TSQL 编程 基础