---------------------------------使用游标------------------------------------- --显示游标 1:显示游标属性 %ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT 2:使用显示游标 分四个阶段: 定义游标 CURSOR cursor_name IS select_statement; 打开游标 OPEN cursor_name; 提取数据 FETCH cursor_name INTO variable[,variable2,.....]; 关闭游标 CLOSE cursor_name; 2.1:使用标量变量接受数据 declare cursor emp_cursor is select ename,job,sal from emp where deptno=&no; v_ename emp.ename%type; v_sal emp.sal%type; v_job emp.job%type; begin open emp_cursor; loop fetch emp_cursor into v_ename,v_job,v_sal; exit when emp_cursor%notfound; dbms_output.put_line('姓名:'||v_ename||',岗位:'||v_job||',工资:'||v_sal); end loop; close emp_cursor; end; 2.2:使用pl/sql记录变量 declare cursor emp_cursor is select ename,job,sal from emp order by sal desc; emp_record emp_cursor%rowtype; begin open emp_cursor; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound or emp_cursor%rowcount>&n; dbms_output.put_line('姓名:'||emp_record.ename||',岗位:'||emp_record.job||',工资:'||emp_record.sal); end loop; end; 3:游标FOR循环 3.1:在for循环中直接引用已定义游标 ???? declare cursor emp_cursor is select ename,sal from emp order by sal desc; begin for emp_record in emp_cursor loop dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal); end loop; end; 3.2:在for循环中直接引用子查询语句 不需要引用游标属性 begin for emp_record in (select ename,sal,rownum from emp order by sal desc) loopg dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal); exit when emp_record.rownum=&n; end loop; end; 4:参数游标 必须在select语句的where子句中引用游标参数,否则失去了定义参数游标饿意义 declare cursor emp_cursor(dno number) is select ename,sal from emp where deptno=dno; begin for emp_record in emp_cursor(&dno) loop dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal); end loop; end; 5:更新或删除游标行