SQL Server-T-SQL程序设计与游标设计

本篇主要介绍T-SQL的语法、功能，以及用其进行复杂程序设计的思想和方法。

当需要对多行结果集进行逐行处理时，可以使用SQL Server提供的游标实现，本篇亦提供了游标的概念和采用T-SQL定义和使用游标方法，以便进一步提高使用T-SQL处理数据的能力。

T-SQL程序设计基本知识

Transact-SQL是SQL Server对标准SQL语言的扩充。它引入了程序设计的思想，增加了程序的流程控制语句。Transact-SQL语言最主要的用途是设计服务器端的能够在后台执行的程序块，如存储过程、触发器等。

1.变量

Transact-SQL中可以使用两种变量：局部变量和全局变量。

局部变量。局部变量是用户可自行定义的变量，它的作用范围是在程序内部，一般用来存储从表中查询到的数据，或作为程序执行过程中的暂存变量。局部变量必须以@开头，且必须先用DECLARE命令加以说明后才可使用。
全局变量。全局变量是SQL Server 系统内容使用的变量，其作用范围并不局限于某一程序，而是所有程序都可随时调用。全局变量通常存储一些SQL Server的配置设定值和效能统计数据。引用全局变量必须以@@开头。

2.流程控制命令

BEGIN… END
IF…ELSE …
CASE
WHILE … CONTINUE… BREAK
WAITFOR
GOTO
RETURN

3.其他命令

BACKUP
CHECKPOINT
DBCC
DECLARE
EXECUTE
KILL
PRINT
RAISERROR
READTEXT
RESTORE
SELECT
SET
SHUTDOWN
WRITETEXT
USE

4.常用函数

统计函数
算术函数
字符串函数
数据类型转换函数
日期函数
TEXT函数和IMAGE函数
用户自定义函数

游标简介

关系数据库中的操作会对整个行集起作用。由SELECT语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。

1.游标的作用

允许定位在结果集的特定行。
从结果集的当前位置检索一行或一部分行。
支持对结果集中当前位置的行进行数据修改。
为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。
提供脚本，存储过程和触发器中用于访问结果集中的数据的Transact-SQL语句。

2.请求游标的方法

Microsoft SQL Server 2005支持两种请求游标的方法：

①Transact-SQL。Transact-SQL语言支持使用根据SQL-92游标语法制定的游标的语法。

②数据库应用程序编程接口(API)游标函数。SQL Server支持以下数据库API的游标功能:

ADO(Microsoft ActiveX数据对象)
OLEDB
ODBC(开放式数据库连接)

3.游标的类型

①客户端游标。

②服务器游标，包括以下两种。

Transact-SQL游标。
应用编程接口(API)服务器游标。

4.使用T-SQL游标过程

Transact-SQL游标主要用于存储过程、触发器和Transact-SQL脚本中，它们使结果集的内容可用于其他Transact-SQL语句。

在存储过程或触发器中使用Transact-SQL游标的典型过程为:

声明Transact-SQL变量包含游标返回的数据。
使用DECLARE CURSOR语句将Transact-SQL游标与SELECT语句相关联。另外，DECLARE CURSOR语句还定义游标的特性，例如游标名称以及游标是只读还是只进。
使用OPEN语句执行SELECT语句并填充游标。
使用FETCH INTO语句提取单个行，并将每列中的数据移至指定的变量中。
使用CLOSE语句结束游标的使用。关闭游标可以释放某些资源。

5.FETCH返回值及其状态

SQL Server中提供了一个全局变量@@FETCH_STATUS，用于存储最近执行的FETCH语句返回的状态，如下表所示。

返回值	说明
0	FETCH语句成功
-1	FETCH语句失败或行不在结果集中
-2	提取的行不存在

T-SQL程序设计逻辑

1.计算1～100之间所有能被3整除的数的个数和总和

declare @sum smallint, @i smallint, @nums smallint
set @sum = 0
set @i = 1
set @nums = 0
while (@i <= 100)
  begin
  if (@i % 3 = 0)
    begin
    set @sum = @sum + @i
    set @nums = @nums + 1
    end
  set @i = @i + 1
  end
print '个数是：' + ltrim(str(@nums))
print '总和是：' + ltrim(str(@sum))
go

2.计算1!+2!+...+100！的总和

declare @factorial float, @sum float, @i smallint
set @i = 1
set @factorial = 1
set @sum = 0
while(@i <=100)
  begin
  set @factorial = @factorial * @i
  set @sum = @sum + @factorial
  set @i = @i +1
  end
print @factorial
print @sum
go

3.case 语句

select SNO 学号, SN 姓名,
       性别 = case SEX
               when '男' then 'M'
               when '女' then 'F'
             end
from S
go

面向复杂T-SQL程序设计

1.查询所有同学选课成绩情况:姓名、课程名、成绩

要求:凡成绩为空者输出“未考”;小于60分的输出“不及格”;60~70分的输出“及格”;70~80分的输出“中”;80～90分的输出“良好”;90~100分的输出“优秀”。并且输出记录按下列要求排序:先按SNO升序,再按CNO升序,最后按成绩降序。

select SN 姓名, CN 课程名,
       成绩 = case
               when SCORE is null then '未考'
               when SCORE < 60 then '不及格'
               when SCORE >= 60 and SCORE <70 then '及格'
               when SCORE >= 70 and SCORE <80 then '中'
               when SCORE >= 80 and SCORE <90 then '良好'
               when SCORE >= 90 then '优'
             end
from SC, S, C
where S.SNO = SC.SNO and C.CNO = SC.CNO
order by S.SNO asc, C.CNO, SCORE desc
go

2.给教师增加工资

要求:必须任2门以上课程且涨幅按总收入分成三个级别:4000元以上涨300;3000元以上涨200;3000元以下涨100。只任一门课程的涨50。其他情况不涨。

update T
set SAL = SAL + case
                  when T.TNO in (select TC.TNO
                                 from T, TC
                                 where T.TNO = TC.TNO and (SAL+COMM) >= 4000
                                 group by TC.TNO having count(*) >= 2) then 300
                  when T.TNO in (select TC.TNO
                                 from T, TC
                                 where T.TNO = TC.TNO and (SAL+COMM) >= 3000 and (SAL+COMM) < 4000
                                 group by TC.TNO having count(*) >= 2) then 200
                  when T.TNO in (select TC.TNO
                                 from T, TC
                                 where T.TNO = TC.TNO and (SAL+COMM) < 3000
                                 group by TC.TNO having count(*) >= 2) then 100
                  when T.TNO in (select TC.TNO
                                 from T, TC
                                 where T.TNO = TC.TNO
                                 group by TC.TNO having count(*) = 1) then 50
                  else 0
                end
go

使用游标

针对数据库jiaoxuedb，进行下面的实验:

1.定义一个游标

要求：该游标返回所有女同学的基本信息，在游标中查找并显示牛莉的记录。

declare @SNO char(6), @SNAME char(8), @SEX char(2), @AGE tinyint, @DEPT char(10)
declare Cursor_Female cursor
  for select Sno, Sname, Sex, Age, Dept from Student
open Cursor_Female
fetch next from Cursor_Female
  into @SNO, @SNAME, @SEX, @AGE, @DEPT
while @@fetch_status = 0
  begin
  if @SNAME = '牛莉'
    begin
    print '找到牛莉的信息如下:'
    print @SNO+' '+@SNAME+' '+@SEX+' '+convert(char(2),@AGE)+' '+@DEPT
    break
    end
  fetch next from Cursor_Female
    into @SNO, @SNAME, @SEX, @AGE, @DEPT
  end
if @@fetch_status != 0
  print '很抱歉,没有找到牛莉的信息!'
close Cursor_Female
deallocate Cursor_Female
go

2.创建一个存储过程

要求：存储过程返回一个包含所有选修数据库课程的学生基本信息的游标。利用该存储过程，通过学生姓名查找学生王一山是否选修了数据库课程及其成绩。

create procedure Pro_C
  @C_cursor cursor varying output
as
set @C_cursor = cursor
  for
  select Sname, Score from Student, SC, Course
  where Student.Sno = SC.Sno and SC.Cno = Course.Cno
        and Course.Cname = '数据库'
open @C_cursor
go

declare @MyCursor cursor
declare @Name char(10)
declare @Name_in char(10)
declare @Score int
select @Name_in = '王一山'
exec Pro_C @C_cursor = @MyCursor output
fetch next from @MyCursor
  into @Name, @Score
while (@@fetch_status = 0)
  begin
  if @Name = @Name_in
    begin
    print rtrim(@Name)+'选修了数据库课程,成绩是:'+convert(char(2),@Score)
    break
    end
  fetch next from @MyCursor
    into @Name, @Score
  end
if (@@fetch_status != 0)
  print rtrim(@Name_in)+'没有选修数据库课程。'
close @MyCursor
deallocate @MyCursor
go

posted @ 2022-05-26 11:33 Khru 阅读(311) 评论(0) 编辑收藏举报

刷新页面返回顶部

khrushchefox