隐式游标
隐式游标的属性:
- SQL%ROWCOUNT 成功操作的行的数量
- SQL%FOUND 发现复合条件的行返回TRUE
- SQL%NOTFOUND 没有发现复合条件的行回TRUE
- SQL%ISOPEN 游标打开状态(boolean)
演示:打印隐式游标属性
declare v_count number; begin select count(*) into v_count from scott.emp; dbms_output.put_line(chr(10)||‘select return ‘||sql%rowcount||‘ rows!‘); end; / begin delete emp; dbms_output.put_line(chr(10)||sql%rowcount||‘ rows deleted!‘); rollback; end; /
显式游标
显式游标使用流程:
- 声明 declare:定义cursor c1 is select ename,sal from emp;
- 打开 open:打开后才会执行上述查询语句
- 获取 fetch:由内存中逐行获取,获取一行少一行
- 关闭 close:没有关闭会一直占着内存
练习 1: 基本loop循环 显示游标的使用
DECLARE v_empno employees.employee_id%TYPE; v_ename employees.last_name%TYPE; CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; --声明 BEGIN OPEN emp_cursor; --打开 LOOP FETCH emp_cursor INTO v_empno, v_ename; --获取 exit when emp_cursor%rowcount>20; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||‘ ‘|| v_ename); END LOOP; CLOSE emp_cursor; --关闭 END ; /
练习 2: for循环 显示游标的使用
DECLARE v_empno employees.employee_id%TYPE; v_ename employees.last_name%TYPE; CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; BEGIN OPEN emp_cursor; FOR i IN 1..10 LOOP --此时i为数字类型 FETCH emp_cursor INTO v_empno, v_ename; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||‘ ‘|| v_ename); END LOOP; CLOSE emp_cursor; END ; /
游标for循环: 可以省略打开,获取和关闭操作。
declare cursor emp_cursor is select rownum,employee_id,last_name from employees; --声明 begin for emp_record in emp_cursor loop --隐式打开隐式获取 此时i为record类型 exit when emp_cursor%rowcount>23; dbms_output.put_line(emp_record.rownum||‘ ‘|| emp_record.employee_id||‘ ‘|| emp_record.last_name); end loop; --隐式关闭 end; /
省略游标定义
可以省略游标的定义,在for....in中使用查询。但不能使用游标控制,如%rowcount等,因为没有定义游标名字。所以要使用游标的属性,就不要缺少定义。
begin for r in (select last_name from employees) loop dbms_output.put_line(r.last_name); end loop; end; /
高级显式游标(带参数的游标)
练习 1:通过传入不同的参数使打开游标时取到不同的结果集
declare cursor c1 (p_deptno number,p_job varchar2) is select empno,ename from emp where deptno=p_deptno and job=p_job; begin Dbms_output.put_line(‘first fetch cursor!‘); for r_c1 in c1(10,‘MANAGER‘) loop --open cursor时传入不同的实际参数得到不同的游标上下文! Dbms_output.put_line(r_c1.empno||‘ ‘||r_c1.ename); end loop; Dbms_output.put_line(‘second fetch cursor!‘); for r_c1 in c1(20,‘MANAGER‘) loop Dbms_output.put_line(r_c1.empno||‘ ‘||r_c1.ename); end loop; Dbms_output.put_line(‘third fetch cursor!‘); for r_c1 in c1(30,‘MANAGER‘) loop Dbms_output.put_line(r_c1.empno||‘ ‘||r_c1.ename); end loop; end; /
练习:获取每个部门前两个雇员的信息
获取10部门前两个人的信息
declare cursor c1 is select * from scott.emp where deptno=10; begin for r1 in c1 loop exit when c1%rowcount=3 or c1%notfound; dbms_output.put_line(r1.ename||‘ ‘||r1.deptno); end loop; end; /
使用替代变量取指定部门的前两个人的信息
declare cursor c1 is select * from scott.emp where deptno=&p_deptno; begin for r1 in c1 loop exit when c1%rowcount=3 or c1%notfound; dbms_output.put_line(r1.ename||‘ ‘||r1.deptno); end loop; end; /
使用高级游标代替替代变量
declare cursor c1(p_deptno number) is select * from scott.emp where deptno=p_deptno; begin for r1 in c1(10) loop exit when c1%rowcount=3 or c1%notfound; dbms_output.put_line(r1.ename||‘ ‘||r1.deptno); end loop; for r1 in c1(20) loop exit when c1%rowcount=3 or c1%notfound; dbms_output.put_line(r1.ename||‘ ‘||r1.deptno); end loop; for r1 in c1(30) loop exit when c1%rowcount=3 or c1%notfound; dbms_output.put_line(r1.ename||‘ ‘||r1.deptno); end loop; end; /
使用循环嵌套简化上面的代码
declare cursor c2 is select distinct deptno from scott.emp; cursor c1(p_deptno number) is select * from scott.emp where deptno=p_deptno; begin for r2 in c2 loop for r1 in c1(r2.deptno) loop exit when c1%rowcount=3 or c1%notfound; dbms_output.put_line(r1.ename||‘ ‘||r1.deptno); end loop; end loop; end; /
练习 2:将每个部门工资小于2000的职员工资涨10%
declare cursor c1 is select deptno from scott.dept; cursor c2 (p_deptno number,p_job varchar2) is select empno,ename,sal from emp where deptno=p_deptno and job=p_job for update of sal; --指明锁哪张表(其中of sal指明所emp表,不加的话会锁两张表,当然多张表才有意义) begin for r_c1 in c1 loop dbms_output.put_line(‘第‘||c1%rowcount||‘次获取游标c1‘ || ‘修改‘||r_c1.deptno||‘部门职员的工资‘); for r_c2 in c2(r_c1.deptno,‘CLERK‘) loop /* 参数游标的好处的就是可以使锁定行更少 更有利于并发 */ if r_c2.sal<2000 then update scott.emp set sal=sal*1.1 where current of c2; /* 只锁C2游标所涉及到的行 */ end if; end loop; end loop; end; /