oracle sql游标代替,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