1.显示游标
cursor cursor_name is select_statement; open cursor_name; fetch cursor_name into variable1,variable2,...; --fetch cursor_name into collect1,collect2,...; close cursor_name;
显示游标属性:%ISOPEN,%FOUND,%NOTFOUNT,%ROWCOUNT
2.参数游标:
declare cursor emp_cursor(no number) is select ename from emp where deptno=no; v_ename emp.ename%type; begin open emp_cursor(10); loop fetch emp_cursor into v_ename; exit when emp_cursor%notfound; dbms_output.put_line(v_name); end loop; close emp_cursor; end;
3.使用游标更新或删除数据
declare cursor emp_cursor is select ename,sal from emp for update; v_ename emp.ename%type; v_sal emp.sal%type; begin open emp_cursor; loop fetch emp_cursor into v_ename,v_oldsal; exit when emp_cursor%notfound; if v_oldsal < 2000 then --update update emp set sal=sal+100 where current of emp_cursor; --delete delete from emp where current of emp_cursor; end if; end loop; close emp_cursor; end;
4.游标for循环(简化了对游标的处理,oracle隐含的打开游标、提取数据、关闭游标)
declare cursor emp_cursor is select ename,sal from emp; begin for emp_cursor in emp_cursor loop ... ... end loop; end;
5、使用游标变量
declare type emp_cursor_type is ref cursor; emp_cursor emp_cursor_type; emp_record emp%rowtype; begin open emp_cursor for select * from emp where deptno = 10; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; ... ... end loop; close emp_cursor; end;
6、使用CURSOR表达式
语法:CURSOR(subquery)