oracle_12游标.docVIP

--游标-- select empno,ename,hiredate,sal from emp; -- declare cursor cur_emp is select empno,ename,hiredate,sal from emp; v_empno emp.empno%type; v_ename emp.ename%type; v_hiredate emp.hiredate%type; v_sal emp.sal%type; begin --游标的操作需要三步 --1、打开游标 open cur_emp; loop --2、跳动游标指针，并取出当前游标指向行数据 fetch cur_emp into v_empno,v_ename,v_hiredate,v_sal; dbms_output.put_line(v_ename|| ||v_empno|| ||v_sal); exit when cur_emp%notfound; end loop; --3、关闭游标 close cur_emp; end; --使用游标定义行类型 declare cursor cur_emp is select empno,ename,hiredate,sal from emp; v_row cur_emp%rowtype; begin --对游标的操作，需要做三步： --1、打开游标 open cur_emp; loop --2、跳动游标指针，并取出当前游标指向行数据 fetch cur_emp into v_row; dbms_output.put_line(v_row.ename|| ||v_row.empno|| ||v_row.hiredate|| ||v_row.sal); exit when cur_emp%notfound; end loop; --关闭游标 close cur_emp; end; --对游标状态的操作： declare cursor cur_emp is select empno,ename,hiredate,sal from emp; v_row cur_emp%rowtype; begin if not cur_emp%isopen then open cur_emp; end if; if cur_emp%isopen then loop fetch cur_emp into v_row; dbms_output.put_line(v_row.ename|| ||v_row.empno|| ||v_row.hiredate|| ||v_row.sal); exit when cur_emp%notfound; end loop; end if; if cur_emp%isopen then close cur_emp; end if; end; --while循环中调用游标 declare cursor cur_emp_dept is select empno,ename,sal,d.deptno,dname from emp e,dept d where e.deptno=d.deptno; v_row cur_emp_dept%rowtype; begin open cur_emp_dept; fetch cur_emp_dept into v_row; while(cur_emp_dept%found) loop dbms_output.put_line(cur_emp_dept%rowcount|| ||v_row.empno|| ||v_row.ename|| ||v_row.dname); fetch cur_emp_dept into v_row; end loop; close cur_emp_dept; end; --游标 for 循环 --注意：游标 for 循环会隐含调用游标的 open fetch close declare cursor cur_emp is select empno,ename,sal from emp; begin for v_row in cur_emp loop dbms_output.put_line(cur_emp%rowcount|| ||v_row.empno|| ||v_row.ename|| ||v_row.sal); end loop; end; --使用游标 for