oracle cursor
-------- begining of the structure of test --------
/*
userId userName
1 John1
2 John2
3 John3
4 John4
5 John5
*/
---------------- part 1 ----------------
declare
cursor v_cursor is select * from test;
v_record test%rowtype;
begin
open v_cursor;
fetch v_cursor into v_record;
while v_cursor%FOUND LOOP
dbms_output.put_line(v_record.userid || ',' ||v_record.username);
fetch v_cursor into v_record;
end loop;
close v_cursor;
end;
-- result
/*
1,John1
2,John2
3,John3
4,John4
5,John5
*/
---------------- part 2 ----------------
declare
cursor v_cursor(userId number, userName varchar2) is select userid, userName from test where userId = 1;
v_userId test.userid%type;
v_userName test.username%type;
begin
-- 这里temp是随便输入的一个字串，
-- 可能是由于上面游标是针对字段定义的.
open v_cursor(1,'temp');
loop
fetch v_cursor into v_userId, v_userName;
exit when v_cursor%NOTFOUND;
dbms_output.put_line(v_userId || '-' || v_userName);
end loop;
close v_cursor;
end;
---------------- part 3 ----------------
--由于不需要open cursor，因为最高效。
declare
cursor v_cursor(userName varchar2) is select userName from test where userId = 1;
begin
for v_temp_cursor in v_cursor('temp') loop
dbms_output.put_line(v_temp_cursor.username);
end loop;
end;
---------------- ISOPEN ----------------
declare
cursor v_cursor(username varchar2) is select userName from test where userId = 1;
v_userName test.username%type;
begin
if v_cursor%ISOPEN then
dbms_output.put_line('cursor is opened');
else
open v_cursor('temp');
end if;
fetch v_cursor into v_userName;
close v_cursor;
dbms_output.put_line(v_userName);
END;
---------------- ROWCOUNT ----------------
declare
cursor v_cursor(username varchar2) is select userName from test where userId > 1;
v_userName test.username%type;
begin
open v_cursor('temp');
loop
fetch v_cursor into v_userName;
exit when v_cursor%NOTFOUND OR v_cursor%NOTFOUND IS null;
dbms_output.put_line('record number is '||v_cursor%ROWCOUNT);
end loop;
close v_cursor;
END;
---------------- 更新数据 ----------------
declare
cursor v_cursor is select username from test for update;
v_userName test.username%type;
begin
open v_cursor;
fetch v_cursor into v_userName;
while v_cursor%FOUND Loop
update test set username = v_userName || userId where current of v_cursor;
fetch v_cursor into v_userName;
end loop;
close v_cursor;
end;
---------------- 隐式游标 ----------------
begin
for v_cursor in(select username from test) loop
dbms_output.put_line(v_cursor.username);
END LOOP;
end;
