ORACLE和SQL SERVER中游标的使用

sql server中定义cursor： DECLARE cursor_name CURSOR FOR select_statement oracle中定义cursor： DECLARE CURSOR cursor_name IS select_statement; sql server中使用cursor: OPEN cursor_name FETCH NEXT FROM cursor_name INTO var_name WHILE @@FETCH_STATUS=0 print var_name CLOSE cursor_name DEALLOCATE cursor_name 实例： 下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。 SET NOCOUNT ON DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40), @message varchar(80), @title varchar(80) PRINT "-------- Utah Authors report --------" DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lnameFROM authorsWHERE state = "UT" ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS 0 PRINT " " WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title END CLOSE titles_cursor DEALLOCATE titles_cursor -- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameEND CLOSE authors_cursor DEALLOCATE authors_cursorGO -------- Utah Authors report ------------- Books by Author: Anne Ringer The Gourmet Microwave Is Anger the Enemy?----- Books by Author: Albert Ringer Is Anger the Enemy? Life Without Fear oracle中使用游标的格式： BEGIN OPEN cursor_name; LOOP FETCH cursor_name INTO var_name EXIT WHEN cursor_name%NOTFOUND; END LOOP; CLOSE cursor_name; END oracle的游标实例：下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。 declare v_au_id varchar2(11); v_au_fname varchar2(20); v_au_lname varchar2(40); v_title varchar2(80); cursor authors_cursor is select au_id, au_fname, au_lname from authors where state = "UT" order by au_id;begin open authors_cursor; loop fetch authors_cursor into v_au_id,v_au_fname,v_au_lname; exit when authors_cursor%notfound ; dbms_output.put_line( '----- Books by Author: ' ||v_au_fname || ' ' || v_au_lname); begin declare cursor titles_cursor is select t.title from titleauthor ta, titles t where ta.title_id = t.title_id and ta.au_id = v_au_id; begin open titles_cursor; loop fetch titles_cursor into v_title; exit when titles_cursor%notfound； dbms_output.put_line(v_title); end loop end end end loop end

本次参考后使用事例其中游标为动态sql open p_rc1 for 'select distinct code_brief                 from oa_assist_code                where code_des in (' || strdist || ')';       loop       fetch p_rc1         into varpara;       temp_sql := strSqlCount;       select replace(temp_sql, '@datalink', varpara)         into temp_sql         from dual;       if p_rc1%found then         select decode(CreateSql,                       null,                       temp_sql,                       CreateSql || ' union all ' || temp_sql)           into CreateSql           from dual;       end if;       exit when p_rc1%notfound;     end loop;     open p_rc1 for 'select sum(con) from (' || CreateSql || ')';