MS-SQL游标的使用及理解windows或DOS的......
2）当语句中使用了Order By子句后，将不能用游标来执行定位DELETE/UPDATE语句；如何解决这个问题，首先在原表上创建索引，在创建游标时指定使用此索引来实现；例如：
Declare cur_Depart Cursor
For Select cDeptID,cDeptName From Department With INDEX(idx_ID)
For Update Of cDeptID,cDeptName
通过在From子句中增加With Index来实现利用索引对表的排序；
3）在游标中可以包含计算好的值作为列；
4）利用@@Cursor_Rows确定游标中的行数；
ALTER FUNCTION SEL_KEYAR(@YEARNUM INT,@f_k_lessonid VARCHAR(15))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @NIAN VARCHAR(8000),@NUMS INT,@NIANS VARCHAR(8000)
SET @NUMS=1
DECLARE GETYEAR CURSOR FOR SELECT f_year FROM t_kejianol WHERE f_k_lessonid=@f_k_lessonid GROUP BY f_year ORDER BY f_year DESC
OPEN GETYEAR
FETCH NEXT FROM GETYEAR INTO @NIAN
WHILE @@FETCH_STATUS=0
BEGIN
IF @YEARNUM=@NUMS
SET @NIANS=ISNULL(@NIANS ',','') RTRIM(@NIAN)
SELECT @NUMS=@NUMS 1
FETCH NEXT FROM GETYEAR INTO @NIAN
--FETCH ABSOLUTE 3 FROM GETYEAR INTO @NIAN
END
CLOSE GETYEAR
DEALLOCATE GETYEAR
--PRINT @NIANS
RETURN @NIANS
END
ALTER FUNCTION SEL_KEYAR(@YEARNUM INT,@f_k_lessonid VARCHAR(15))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @NIAN VARCHAR(8000),@NIANS VARCHAR(8000)
DECLARE GETYEAR CURSOR FOR SELECT f_year FROM t_kejianol WHERE f_k_lessonid=@f_k_lessonid GROUP BY f_year ORDER BY f_year ASC
OPEN GETYEAR
FETCH ABSOLUTE @YEARNUM FROM GETYEAR INTO @NIAN
CLOSE GETYEAR
DEALLOCATE GETYEAR
RETURN @NIANS
END
3/3 首页 上一页 1 2 3 |
