Transact-SQL程序设计

Transact SQL 程序程序设计设计 Transact SQLTransact SQL 基本语句基本语句 1 1 定义批处理语句 定义批处理语句 语句格式 GO 注 一个批处理中任何一条语句有语法错误 整个批处理将不 能编译和执行 2 2 变量变量 1 1 局部变量局部变量 声明局部变量语句 DECLAREDECLARE 声明一个 datetime 类型的局部变量 DECLARE date var datetime 声明两个局部变量 DECLARE var1 int var2 money 局部变量赋值语句 SET SELECTSET SELECT 用 SET 语句和 SELECT 语句为局部变量赋值 DECLARE var1 datetime var2 char 10 SET var1 getdate SELECT var2 convert char 10 var1 102 用 SET 语句将查询的结果赋给局部变量 并用 SELECT 语句显示 局部变量的值 Declare date var datetime Set date var select min birthday from s SELECT date var AS min birthday 局部变量名的命名 首字符为字母 长度不超过 128 的字母 数字和特殊符号 组成的字符串 并且不能与全局变量同名 局部变量声明后的值初始化为 NULL 局部变量的作用域仅限于一个批处理中 2 2 全局变量全局变量 在 SQL Server 2000 中 将全局变量改称为函数 由于这些函 数不需要任何参数 所以又称为无参函数 SQL Server 2000 无参函数见表 1 全局变量是 SQL Server 系统内部变量 以 开头 表 1 SQL Server 2000 无参函数 无参函数无参函数描述描述 CONNECTIONS SQL Server 最近一次启动后连接或尝试连接的次 数 CPU BUSY SQL Server 最近一次启动后 CPU 工作时间 单位 ms CURSOR ROWS 最近一次打开的游标中当前存在的记录行数 DATEFIRST SET DATEFIRST 参数的当前值 DBTS 当前数据库最后使用的时间戳值 ERROR 最近一次执行的 Transact SQL 语句的错误代码 FETCH STATUS 最近一次对游标所执行 FETCH 语句的状态 IDENTITY 最近一次插入数据库表的标识值 即自动编号值 IDLE SQL Server 上次启动后闲置的时间 单位 ms IO BUSY SQL Server 上次启动后用于执行 I O 操作的时间 单位 ms LANGID 当前所使用语言的本地语言标识符 ID LANGUAGE 返回当前使用的语言名称 LOCK TIMEOUT 当前会话的当前锁超时设置 单位 ms MAX CONNECTIONS SQL Server 上允许用户同时连接的最大数 MAX PRECISION decimal 和 numeric 数据类型所用的精度级别 NESTLEVEL 当前存储过程执行的嵌套层次 OPITONS 当前 SET 选项的信息 PACK RECEIVED SQL Server 上次启动后从网络上读取的输入数据 包数 PACK SENT SQL Server 上次启动后写到网络上的输出数据包 数 PACK ERRORS SQL Server 上次启动后 SQL Server 连接上发生的 网络数据包错误数 PROCID 当前过程的存储过程标识符 ID REMSERVER 当远程 SQL Server 数据库服务器在登录记录中出 现时的名称 ROWCOUNT 受上一个语句影响的记录行数 SERVERNAME 运行 SQL Server 的本地服务器名称 SERVICENAME SQL Server 正在运行的注册表键名 当前实例为默 认实例 返回 MSSQLServer 当前实例是命名实例 返回实例名 SPID 当前用户进程的服务器进程标识符 ID TEXTSIZE SET TEXTSIZE 选项当前值 指定 SELECT 返回的 text 或 image 数据的最大长度 以字节为单位 TIMETICKS 一刻度的微秒数 操作系统一刻度是 31 25ms TOTAL ERRORS SQL Server 上次启动后所遇到的磁盘读 写错误数 TOTAL READ SQL Server 上次启动后读取磁盘 非高速缓存 数 TOTAL WRITE SQL Server 上次启动后写入磁盘数 TRANCOUNT 当前连接的活动事务数 VERSION SQL Server 当前安装的日期 版本和处理器类型 用全局变量查看 SQL Server 的版本 当前所使用的 SQL Server 服务器的名称以及所使用服务的名称等信息 print 目前所用 SQL Server 的版本信息如下 print version Print 目前所用 SQL Server 服务器的名称为 servername print 目前所用服务为 servicename 3 3 注释语句注释语句 语句格式 或 4 4 输出语句输出语句 语句格式 PRINTPRINT 输出变量的值 Declare date var datetime Set date var select min birthday from s Print date var 5 5 定义语句块语句定义语句块语句 语句格式 BEGINBEGIN ENDEND 第第 2 2 小节小节 Transact SQLTransact SQL 流程控制语句流程控制语句 1 1 选择结构 选择结构 IFIF 1 ELSE ELSE 2 查找学号为 1001 的学生 if exists select sno from s where sno 1001 print 找到学号为 1001 的学生 else print 没有找到学号为 1001 的学生 条件语句的嵌套 DECLARE score smallint SET score SELECT score FROM sc WHERE sno 1001 and cno c002 IF score 90 PRINT 优秀 ELSE IF score 70 PRINT 良好 ELSE IF score 60 PRINT 及格 ELSE PRINT 不及格 注 一条 IF 或 ELSE 后必须为一条 Transact SQL 语句 如果有 多条 Transact SQL 语句 必须使用 BEGIN END 2 2 循环结构循环结构 循环语句 循环语句 WHILEWHILE 中断语句 中断语句 BREAKBREAK 短路语句 短路语句 CONTINUECONTINUE 例 5 9 求 1 10 之间的奇数和 declare i smallint sum smallint set i 0 set sum 0 while i 0 begin set i i 1 if i 10 if i 2 0 continue else set sum sum i else begin print sum str sum break end end 求 100 200 之间的全部素数 declare m tinyint i tinyint set m 101 while m 200 begin set i 2 while isqrt m print m set m m 2 end 注 WHILE 后必须为一条 Transact SQL 语句 如果有多条 Transact SQL 语句 必须使用 BEGIN END 3 3 转移语句转移语句 语句格式 GOTOGOTO 例 5 11 利用转移语句和条件语句求 10 的阶乘 declare s int times int set s 1 set times 1 label1 set s s times set times times 1 if times 10 goto label1 print 结果为 str s 4 4 等待语句 等待语句 语句格式 WAITFORWAITFOR DELAYDELAY TIME TIME 其中 时间间隔和时间均为 datetime 类型 但不包括日期 其 格式为 hh mm ss 分别说明等待的时间长度和时间点 设置等待一小时后执行查询 begin waitfor delay 1 00 00 select from s end 设置到十点整执行查询 begin waitfor time 10 00 00 select from s end 5 5 返回语句返回语句 语句格式 RETURNRETURN 其中 如果没有指定返回值 返回值为 0 时表示程序成功执行 返回值为负数时表示不同的出错原因 第第 3 3 小节小节 游标游标 1 1 游标概述 游标概述 1 游标的概念 是一种能从包括多条记录的结果集中每次提取一条记录的机制 2 游标的种类 Transtact SQL 游标 在脚本中使用最多的一种类型 重点 介绍 API 游标 API 游标使用在客户端与服务器端的连接程序中 客户游标 主要用在客户端的应用程序中 2 2 使用游标使用游标 1 声明游标 declaredeclare insensitive insensitive scroll scroll cursorcursor forfor select for for read read onlyonly updateupdate of of n n 例 5 14 声明标准游标 declare cur c cursor for select cno cname credit from c 例 5 15 声明只读游标 declare cur c cursor for select cno cname credit from c for read only 例 5 16 声明更新游标 declare cur c cursor for select cno cname credit from c for update 2 声明游标变量 declaredeclare cursorcursor 建立游标变量与游标之间的关联 先声明游标和游标变量 再用 SET 语句将游标赋给游标变量 不声明游标 直接用 SET 语句将游标赋给游标变量 3 打开游标 openopen global global 4 读取游标中的数据 fetchfetch next next priorprior firstfirst lastlast absoluteabsolute n nvar n nvar relativerelative n nvar n nvar FROMFROM global global into into n n 5 关闭游标 使用 close 语句关闭 closeclose global global 自动关闭 声明游标与释放游标之间使用事务 则结束事务 时游标自动关闭 取消自动关闭 SET CURSOR CLOSE ON COMMIT OFF 6 释放游标 deallocatedeallocate global global 注 游标关闭指释放游标结果集所占用资源 游标释放指释放游 标占用的所有资源 例 5 17 DEALLOCATE 的作用 USE student GO 创建一个全局游标 游标名称为 abc 在该批处理之外也可 用 DECLARE abc CURSOR GLOBAL SCROLL FOR SELECT FROM s OPEN abc GO 声明游标变量 MyCrsrRef1 与游标关联 DECLARE MyCrsrRef1 CURSOR SET MyCrsrRef1 abc 用 DEALLOCATE 释放游标变量 MyCrsrRef1 和游标的关联 DEALLOCATE MyCrsrRef1 此时游标还存在 还可以从中提取数据 FETCH NEXT FROM abc GO 重新声明一个游标变量 MyCrsrRef2 与游标关联 DECLARE MyCrsrRef2 CURSOR SET MyCrsrRef2 abc 用 DEALLOCATE 释放游标名称 abc 和游标的关联 DEALLOCATE abc 此时游标还存在还可以从中提取数据 注意此时对该游标的引用只能通过游标变量 MyCrsrRef2 FETCH NEXT FROM MyCrsrRef2 当该批处理结束 游标变量 MyCrsrRef2 被释放 即对游标的最后一个引用被释放游标即被释放 GO 3 3 应用实例应用实例 UPDATE 语句用于游标时的基本语法格式 update set null n where current of DELETE 语句用于游标时的基本语法格式 delete from where current of 例 5 18 游标的定位修改 声明游标 DECLARE cur sc SCROLL CURSOR FOR SELECT FROM sc FOR UPDATE OF score 打开游标 OPEN cur sc 提取数据 FETCH FROM cur sc 修改当前行 score 列 UPDATE sc SET score 100 WHERE CURRENT OF cur sc 关闭释放游标 CLOSE cur sc DEALLOCATE cur sc GO