隨便舉個例子﹕ 這個例子的作用就是從部門表中抓取部門編號﹐再按部門編號給雇員加薪水。 CREATE PROCEDURE [pro_dept_cur] AS DECLARE @dept_no VARCHAR(8), @sal_add integer SELECT @sal_add = 100 --定義游標 DECLARE cur_dept CURSOR LOCAL SCROLL STATIC READ_ONLY FOR SELECT dept_no FROM dept ORDER BY dept_no --打開游標 OPEN cur_dept FETCH FIRST FROM cur_dept INTO @dept_no WHILE @@FETCH_STATUS=0 BEGIN UPDATE employee SET emp_sal=1000 + @sal_add WHERE dept_no = @dept_no SELECT @sal_add = @sal_add + 100 FETCH NEXT FROM cur_dept INTO @dept_no END CLOSE cur_dept DEALLOCATE cur_dept
Tab_student: name--姓名 record-- 成绩以下用游标打开成绩表,依次PRINT出名次,姓名: declare @name varchar(20) declare @i int set @i=1 declare Cur_test for select name from Tab_student order by record open Cur_testfetch next from Cur_test into @name while @@fetch_status<>-1 begin print 'No.'+cast(@i as varchar(10))+' name is: '+@name set @i=@i+1 fetch next from Cur_test into @name end
其实最简单是的应该这样: deffine :SQL,RS SQL="SELECT NAME,ID,SEX,CLASS,AGE,ADDRESS FROM STUDENT " SET RS=EXECUTE(SQL) RS就是游标了
這個例子的作用就是從部門表中抓取部門編號﹐再按部門編號給雇員加薪水。
CREATE PROCEDURE [pro_dept_cur]
AS
DECLARE @dept_no VARCHAR(8),
@sal_add integer
SELECT @sal_add = 100
--定義游標
DECLARE cur_dept CURSOR
LOCAL SCROLL STATIC READ_ONLY
FOR
SELECT dept_no FROM dept ORDER BY dept_no
--打開游標
OPEN cur_dept
FETCH FIRST FROM cur_dept INTO @dept_no
WHILE @@FETCH_STATUS=0 BEGIN
UPDATE employee SET emp_sal=1000 + @sal_add WHERE dept_no = @dept_no
SELECT @sal_add = @sal_add + 100
FETCH NEXT FROM cur_dept INTO @dept_no
END
CLOSE cur_dept
DEALLOCATE cur_dept
name--姓名
record-- 成绩以下用游标打开成绩表,依次PRINT出名次,姓名:
declare @name varchar(20)
declare @i int
set @i=1
declare Cur_test for
select name from Tab_student order by record
open Cur_testfetch next from Cur_test into @name while @@fetch_status<>-1
begin
print 'No.'+cast(@i as varchar(10))+' name is: '+@name
set @i=@i+1
fetch next from Cur_test into @name
end
deffine :SQL,RS
SQL="SELECT NAME,ID,SEX,CLASS,AGE,ADDRESS FROM STUDENT "
SET RS=EXECUTE(SQL)
RS就是游标了