游标应用
USE 学生数据库;
SELECT * FROM 学生表;
SELECT * FROM 课程表;
SELECT * FROM 学生选课表;
---------------游标----------------------
/**
用SELECT 语句的查询 ,结果是一个数据集,若想对某一行数据进行处理,例如
想删除某一行数据,修改某一行数据,这时就要使用游标。
游标是对数据表按特定要求进行的一种操作
**/
--从学生选课表中提取两行数据
DECLARE MYCURSOR CURSOR FOR
SELECT * FROM 学生选课表 --创建游标
OPEN MYCURSOR --打开游标
FETCH MYCURSOR --从游标中提取第一行数据
FETCH MYCURSOR --从游标中提取第二行数据
CLOSE MYCURSOR --关闭游标;
/**
1、用DECLARE ...CURSOR FOR ...语句,定义一个名字为MYCURSOR的只进游标
式中的SELECT * FROM 学生选课表 查询语句不立即执行
2、用OPEN MYCURSOR语句,打开名字为MYCURSOR的游标,其中OPEN是关键字,
此时执行游标定义中的查询语句SELECT * FROM 学生选课表
3、第一个FETCH MYCURSOR语句表示,提取MYCURSOR 游标指向的第一行数据,其结果
由【网络】选项的第一行看出,FETCH是关键字
4、第二个FETCH MYCURSOR语句表示,提取MYCURSOR 游标指向的第二行数据,其结果
由【网络】选项的第二行看出
5、可以根据学生选课表看出,提取MYCURSOR游标指向的两行数据,就是学生选课表的第一行及第二行数据
6、用CLOSE MYCURSOR语句,关闭名字为MYCURSOR的游标,其中CLOSE是关键字
7、用DEALLOCAT MYCURSOR语句,释放名字为MYCURSOR的游标所占用的内存空间,其中DEALLOCAT是关键字
所谓只进游标,是指提取数据时,必须从第一行到最后一行指定的顺序提取数据,提取数据的唯一关键字是FETCH
**/
--练习从学生表中提取三行女学生的数据。即:
DECLARE 我的游标 CURSOR FOR
SELECT * FROM 学生表 WHERE SEX='女';
OPEN 我的游标
FETCH 我的游标
FETCH 我的游标
FETCH 我的游标
CLOSE 我的游标
DEALLOCATE 我的游标 ---释放游标
/*游标释放之后将不存在,需要重新建立 。*/
--练习用DECLARE...SCROLL CURSOR FOR ...语句定义卷游标
/**
只进游标只能在查询结果中,从头到尾一行行提取数据。使用SCROLL CURSOR(卷游标,或称可滚动游标),
及关键字NEXT,PRIOR,FIRST,LAST,RELATIVE,ABSOLUTE 可以在查询结果中,提取指定行数据
**/
DECLARE MYCURSOR1 SCROLL CURSOR FOR
SELECT * FROM 学生表
OPEN MYCURSOR1
FETCH FIRST FROM MYCURSOR1 --从游标中提取第一行数据
CLOSE MYCURSOR1
DEALLOCATE MYCURSOR1
DECLARE MYCURSOR1 SCROLL CURSOR FOR
SELECT * FROM 课程表
OPEN MYCURSOR1
FETCH LAST FROM MYCURSOR1 --从游标中提取第末行数据
CLOSE MYCURSOR1
DEALLOCATE MYCURSOR1
--用ABSOLUTE把游标移向查询结果的某一行
--练习从学生选课表中提取第二行数据
DECLARE MYCURSOR1 SCROLL CURSOR FOR
SELECT * FROM 学生选课表
OPEN MYCURSOR1
FETCH ABSOLUTE 2 FROM MYCURSOR1 --从游标中提取第2行数据
CLOSE MYCURSOR1
DEALLOCATE MYCURSOR1
--从学生选课表中提取第10行数据
DECLARE MYCURSOR1 SCROLL CURSOR FOR
SELECT * FROM 学生选课表
OPEN MYCURSOR1
FETCH ABSOLUTE 10 FROM MYCURSOR1
CLOSE MYCURSOR1
DEALLOCATE MYCURSOR1;
--从学生表中提取第二 行女学生的数据
DECLARE 我的游标 SCROLL CURSOR FOR
SELECT * FROM 学生表 WHERE SEX='女'
OPEN 我的游标
FETCH ABSOLUTE 2 FROM 我的游标
CLOSE 我的游标
DEALLOCATE 我的游标
--用PRIOR把游标从当前位置返回一行
--练习从学生选课表中提取第12行及第11行数据
DECLARE 我的游标 SCROLL CURSOR FOR
SELECT * FROM 学生选课表
OPEN 我的游标
FETCH ABSOLUTE 12 FROM 我的游标
FETCH PRIOR FROM 我的游标 --从当前位置返回一行
CLOSE 我的游标
DEALLOCATE 我的游标;
--用NEXT把游标从当前位置推进一行
--从学生表中提取第3行及第3行的下一行数据
DECLARE 学生游标 SCROLL CURSOR FOR
SELECT * FROM 学生表 WHERE SEX='男'
OPEN 学生游标
FETCH ABSOLUTE 3 FROM 学生游标
FETCH NEXT FROM 学生游标
CLOSE 学生游标
DEALLOCATE 学生游标;
--用RELATIVE把游标从当前位置推进若干行,再返回若干行
--练习从学生选课表中提取第7行及从第7行推进3行,再倒退4行,共3行数据
DECLARE 我的游标 SCROLL CURSOR FOR
SELECT * FROM 学生选课表
OPEN 我的游标
FETCH ABSOLUTE 7 FROM 我的游标 --第7行
FETCH RELATIVE 3 FROM 我的游标 --再第7行的基础上推进3行,即第10行
FETCH RELATIVE -4 FROM 我的游标 --在第10行的基础上倒退4行,即第6行
CLOSE 我的游标
DEALLOCATE 我的游标;
-----------在循环语句中使用游标函数@@FETCH_STATUS------------
--从学生表中提取所有学生名字,并显示
DECLARE MY_CURSOR CURSOR FOR
SELECT SN FROM 学生表
DECLARE @SN CHAR(10) --声明变量
OPEN MY_CURSOR
PRINT '@@FETCH_STATUS 之值 =' + CONVERT(VARCHAR(4),@@FETCH_STATUS)
FETCH MY_CURSOR INTO @SN --把游标取数据放到变量@SN中
PRINT '@@FETCH_STATUS 之值 =' + CONVERT(VARCHAR(4),@@FETCH_STATUS) + ',@SN= '+ @SN
WHILE(@@FETCH_STATUS=0) --如果@@FETCH_STATUS=-1循环终止
BEGIN
FETCH MY_CURSOR INTO @SN
PRINT '@@FETCH_STATUS 之值1 =' + CONVERT(VARCHAR(4),@@FETCH_STATUS) + ',@SN= ' + @SN --打印结果
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR;
/**
1、本题应用了TRANSACT -SQL语言
例如:声明变量语句,DECLARE @SN CHAR(1)
PRINT语句
循环语句 WHILE(...) BEGIN ...END
TRANSACT...SQL(T-SQL)语言是SQL语言的一个扩展,它除了提供标准的SQL语言之外,还提供了类似C,BASIC语言的语句
2、用DECLARE<游标名> CURSOR FRO(SQL查询语句) 定义一个名为MY_CURSOR的游标,游标的功能是
操作一条查询语句:SELECT * FROM 学生表
3、用OPEN MY_CURSOR语句,打开名字为MY_CURSOR的游标,其中OPEN 是关键字
4、用DECLARE @SN CHAR(10) T-SQL语句声明@SN是一个字符变量,其中DECLARE是关键字,@SN是变量名
CHAR(10),表示字符变量@SN不能超过10个字符
5、FETCH MY_CURSOR INTO @SN语句的作用是:
1.使用系统游标函数@@FETCH_STATUS=0
2.用游标从查询结果中取数据放到变量@SN中,@SN的数据是学生表中的学生名
6、WHILE(条件) BEGIN ...END
循环语句,BEGIN 表示循环开始,END表示循环结束,循环运行条件式被放到括号中,当条件式为真(不为-1)
循环继续,当条件式不为真(为-1)时,循环终止,即
1- @@FETCH_STATUS =0 循环继续
2- @@FETCH_STATUS =-1 循环终止
7、@@FETCH_STATUS 是系统游标函数,@@FETCH_STATUS 的初始值为-1,@@FETCH_STATUS 与FETCH 联合使用,
当FETCH从查询结果中取了数据放到变量@SN中,则@@FETCH_STATUS =0,如果FETCH提取的数据行是查询结果的末端
则@@FETCH_STATUS =-1,循环终止,因此不能再提取任何数据
8、从‘消息’选项看出:
1- 执行FETCH MY_CURSOR INTO @SN 语句之前,@@FETCH_STATUS=-1
2- 执行FETCH MY_CURSOR INTO @SN 语句之后,@@FETCH_STATUS=0
3- 当@@FETCH_STATUS=0 循环语句运转,FETCH MY_CURSOR INTO @SN 语句,一行一行地从查询结果中提取数据到变量@SN中
PRINT语句逐行打印结果,直到@@FETCH_STATUS=-1,循环终止
9、PRINT语句,用于打印变量或者常量
1- @@FETCH_STATUS 之值=是常量,要用单引号括住
2- CONVERT(VARCHAR(4),@@FETCH_STATUS) 是变量,@@FETCH_STATUS是整数变量,用CONVERT函数,把整数转换变成字符
3- ,@=是常量 ,要用单引号括住
4- 变量与常量之间用加号连接
**/
--从学生表中提取每一行数据,并显示
DECLARE MY_CURSOR CURSOR SCROLL FOR
SELECT * FROM 学生表
DECLARE @SNO CHAR(4)
DECLARE @SN CHAR(10)
DECLARE @SEX CHAR(4)
DECLARE @AGE INT
DECLARE @DEPT INT
OPEN MY_CURSOR
PRINT'@@FETCH_STATUS 之值 ='+ CONVERT(VARCHAR(4),@@FETCH_STATUS)
FETCH MY_CURSOR INTO @SNO,@SN,@SEX,@AGE,@DEPT --从游标取数据放到变量中
PRINT'@SNO,@SN,@SEX,@AGE,@DEPT ='+ @SNO+@SN+@SEX+ CONVERT(VARCHAR(4),@AGE) +''+CONVERT(VARCHAR(4),@DEPT)
WHILE(@@FETCH_STATUS =0)
BEGIN
FETCH FROM MY_CURSOR INTO @SNO,@SN,@SEX,@AGE,@DEPT
PRINT'@@FETCH_STATUS 之值 ='+CONVERT(VARCHAR(4),@@FETCH_STATUS)
PRINT'@SNO1,@SN1,@SEX1,@AGE1,@DEPT1='+@SNO+@SN+@SEX+CONVERT(VARCHAR(4),@AGE)+''+CONVERT(VARCHAR(4),@DEPT)
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR;
--不用游标方法找出选学课程数据最多的学生名及选学课程总数
select 学生表.sn 学生名,COUNT(*) 选课总数 from 学生表,学生选课表 WHERE 学生表.SNO=学生选课表.SNO GROUP BY 学生选课表.SNO,学生表.SN
HAVING COUNT(*)>=ALL (SELECT COUNT(*) FROM 学生选课表 GROUP BY 学生选课表.SNO)
--用游标修改某一行数据及删除某一行数据
--修改学生表的第三行的数据,把学生名改成张三
DECLARE 我的游标 CURSOR FOR
SELECT * FROM 学生表
OPEN 我的游标
FETCH 我的游标
FETCH 我的游标
FETCH 我的游标
UPDATE 学生表 SET SN='张三' WHERE CURRENT OF 我的游标
CLOSE 我的游标
DEALLOCATE 我的游标
--执行了UPDATE ...SET WHERE CURRENT OF ...语句之后,学生表的第三行的第二列数据改变了。
DECLARE 我的游标 SCROLL CURSOR FOR
SELECT * FROM 学生表
OPEN 我的游标
FETCH ABSOLUTE 3 FROM 我的游标
UPDATE 学生表 SET SN='张三' WHERE CURRENT OF 我的游标
CLOSE 我的游标
DEALLOCATE 我的游标
--修改学生选课表的第二行的数据,把分数加5分
DECLARE 我的游标 CURSOR FOR
SELECT * FROM 学生选课表
OPEN 我的游标
FETCH 我的游标
FETCH 我的游标
UPDATE 学生选课表 SET GRADE=GRADE+5 WHERE CURRENT OF 我的游标
CLOSE 我的游标
DEALLOCATE 我的游标
DECLARE 我的游标 SCROLL CURSOR FOR
SELECT * FROM 学生选课表
OPEN 我的游标
FETCH ABSOLUTE 3 FROM 我的游标
UPDATE 学生选课表 SET GRADE=GRADE+5 WHERE CURRENT OF 我的游标
CLOSE 我的游标
DEALLOCATE 我的游标
--用游标删除学生选课表的第一行的数据
DECLARE 学生选课表游标 CURSOR FOR
SELECT * FROM 学生选课表
OPEN 学生选课表游标
FETCH 学生选课表游标
DELETE 学生选课表 WHERE CURRENT OF 学生选课表游标
CLOSE 学生选课表游标
DEALLOCATE 学生选课表游标
--注释:关于@@FETCH_STATUS http://www.yesky.com/imagesnew/software/tsql/ts_globals_1c1f.htm
--CONVERT :http://baike.baidu.com/view/954125.htm
/*
convert 函数 用来转换数据类型
例子:SELECT CONVERT (VARCHAR(5),12345)
返回:字符串 '12345'
*/
环境:SQL SERVER 2000