精通Oracle10编程SQL(9)使用游标

数据库  /  管理员 发布于 5年前   266

/* *使用游标 */--显示游标--在显式游标中使用FETCH...INTO语句DECLARE   CURSOR emp_cursor is       select ename,sal from emp where deptno=1;   v_ename emp.ename%TYPE;   v_sal emp.sal%TYPE;begin   open emp_cursor;   loop      fetch emp_cursor into v_ename,v_sal;      exit when emp_cursor%NOTFOUND;      DBMS_OUTPUT.put_line(v_ename||':'||v_sal);   end loop;   close emp_cursor;END;SELECT * FROM emp;--在显示游标中，使用FETCH...BULK COLLECT INTO语句提取所有数据DECLARE  CURSOR emp_cursor is     select ename from emp where deptno=1;  type ename_table_type is table of varchar2(10);  ename_table ename_table_type;begin  open emp_cursor;  fetch emp_cursor bulk collect into ename_table;  for i in 1..ename_table.count loop     dbms_output.put_line(ename_table(i));  end loop;  close emp_cursor;END;--在显示游标中使用FETCH...BULK COLLECT INTO ..LIMIT语句提取部分数据--下面以每次提取5行数据为例，说明使用LIMIT子句限制行的方法DECLARE   TYPE name_array_type is varray(5) of varchar2(10);   name_array name_array_type;   cursor emp_cursor is select ename from emp;   rows int:=5;   v_count int:=0;BEGIN   OPEN emp_cursor;   loop      fetch emp_cursor bulk collect into name_array limit rows;      dbms_output.put('雇员名：');      DBMS_OUTPUT.put_line('影响行数'||emp_cursor%ROWCOUNT);      dbms_output.put_line(v_count);      for i in 1..(emp_cursor%ROWCOUNT-v_count) loop         dbms_output.put(name_array(i)||' ');      end loop;      dbms_output.new_line;      v_count:=emp_cursor%ROWCOUNT;      EXIT WHEN emp_cursor%NOTFOUND;  END LOOP;  CLOSE emp_cursor;END;--使用游标属性DECLARE   CURSOR emp_cursor is select ename from emp where deptno=1;   TYPE ename_table_type is table of varchar2(10);   ename_table ename_table_type;BEGIN   if not emp_cursor%ISOPEN THEN  --如果游标未打开，则打开游标      open emp_cursor;   end if;   fetch emp_cursor bulk collect into ename_table;   dbms_output.put_line('提取的总计行数：'||emp_cursor%ROWCOUNT);  --显示总计行数   close emp_cursor;END;--基于游标定义记录变量DECLARE  CURSOR emp_cursor is select ename,sal from emp;  emp_record emp_cursor%ROWTYPE;begin  open emp_cursor;  loop     fetch emp_cursor into emp_record;     exit when emp_cursor%NOTFOUND;     dbms_output.put_line('雇员名:'||emp_record.ename||',雇员工资：'||emp_record.sal);  end loop;  close emp_cursor;end;--参数游标--参数游标是指带有参数的游标，定义参数游标时，需要指定参数名及其数据类型--注意：定义参数游标时，游标参数只能指定数据类型，而不能指定长度。另外，定义参数游标时，一定要在游标子查询的WHERE子句中引用该参数，否则失去了定义参数游标的意义DECLARE   CURSOR emp_cursor(no NUMBER) IS       SELECT ename from emp where deptno=no;   v_ename emp.ename%TYPE;begin   open emp_cursor(1);   loop     fetch emp_cursor into v_ename;     exit when emp_cursor%NOTFOUND;     dbms_output.put_line(v_ename);   end loop;   close emp_cursor;end;--使用游标更新或删除数据--使用游标更新数据DECLARE  CURSOR emp_cursor is      select ename,sal from emp for update;  v_ename emp.ename%TYPE;  v_oldsal emp.ename%TYPE;begin  open emp_cursor;  loop     fetch emp_cursor into v_ename,v_oldsal;     exit when emp_cursor%NOTFOUND;     if v_oldsal<1000 then        update emp set sal=sal+100 where current of emp_cursor;     end if;  end loop;  close emp_cursor;end;select * from emp;--使用游标删除数据--下面以解雇部门30的所有雇员为例，说明使用显式游标删除数据的方法DECLARE  CURSOR emp_cursor is      select ename,sal,deptno from emp for update;  v_ename emp.ename%TYPE;  v_oldsal emp.sal%TYPE;  v_deptno emp.deptno%TYPE;begin  OPEN emp_cursor;  loop     fetch emp_cursor into v_ename,v_oldsal,v_deptno;     exit when emp_cursor%NOTFOUND;     if v_deptno = 3 then        delete from emp where current of emp_cursor;     end if;  end loop;  close emp_cursor;end;--使用OF子句在特定表上加上行共享锁--如果游标子查询涉及到多张表，那么在默认情况下会在所有修改表行上加行共享锁。--为了只在特定表上加行共享锁，需要在FOR UPDATE子句后带有OF子句DECLARE  CURSOR emp_cursor is      select ename,sal,dname,emp.deptno from emp,dept     where emp.deptno = dept.deptno     for update of emp.deptno;  emp_record emp_cursor%ROWTYPE;begin  open emp_cursor;  loop     fetch emp_cursor into emp_record;     exit when emp_cursor%NOTFOUND;     if emp_record.deptno=1 then        update emp set sal=sal+100 where current of emp_cursor;     end if;     dbms_output.put_line('雇员名：'||emp_record.ename||',工资：'||emp_record.sal||',部门名：'||emp_record.dname);  end loop;  close emp_cursor;END;--使用NOWAIT子句--使用FOR UPDATE语句对被作用行加锁，如果其他会话已经在被作用行上加锁，那么在默认情况下当前会话会要一直等待对方释放锁--通过在FOR UPDATE子句中指定NOWAIT语句，可以避免等待锁。--当指定了NOWAIT子句之后，如果其他会话已经在被作用行加锁，那么当前会话会显示错误提示信息，并退出PL/SQL块DECLARE   CURSOR emp_cursor is       select ename,sal from emp for update nowait;   v_ename emp.ename%TYPE;   v_oldsal emp.sal%TYPE;begin   open emp_cursor;   loop      fetch emp_cursor into v_ename,v_oldsal;      exit when emp_cursor%NOTFOUND;      if v_oldsal<1000 then         update emp set sal=sal+100 where current of emp_cursor;      end if;   end loop;   close emp_cursor;end;select * from emp;--游标FOR循环--使用游标FOR循环--以顺序显示EMP表的所有雇员为例，说明使用游标FOR循环的方法DECLARE    CURSOR emp_cursor IS SELECT ename,sal from emp;BEGIN   for emp_record in emp_cursor loop      dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.ename);   end loop;END;--在游标FOR循环中直接使用子查询--如果在使用游标FOR循环时不需要使用任何游标属性，那么可以直接在游标FOR循环中使用子查询--下面以显示EMP表的所有雇员名为例，说明在游标FOR循环中直接使用子查询的方法BEGIN   FOR emp_record in       (select ename,sal from emp) loop      dbms_output.put_line(emp_record.ename);   end LOOP;END;--使用游标变量--在定义REF CURSOR类型时不指定RETURN子句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=1;   loop      fetch emp_cursor into emp_record;      exit when emp_cursor%NOTFOUND;      DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.ename);   end loop;   close emp_cursor;end;--在定义REF CURSOR类型时指定RETURN子句--如果在定义REF CURSOR类型时指定了RETURN子句，在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型相匹配--下面以顺序地显示部门20的所有雇员名称为例，说明使用游标变量（包含RETURN子句）的方法DECLARE  TYPE emp_record_type is record(     name VARCHAR2(10),salary NUMBER(6,2));  type emp_cursor_type is ref cursor     return emp_record_type;  emp_cursor emp_cursor_type;  emp_record emp_record_type;BEGIN  open emp_cursor for select ename,sal from emp where deptno=2;  loop    fetch emp_cursor into emp_record;    exit when emp_cursor%NOTFOUND;    dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.name||',工资：'||emp_record.salary);  end loop;  close emp_cursor;END;--使用CURSOR表达式--CURSOR表达式用于返回嵌套游标--通过使用CURSOR表达式，开发人员可以在PL/SQL块中处理更加复杂的基于多张表的关联数据--为了在PL/SQL块中取得嵌套游标的数据，需要使用嵌套循环--下面以显示部门名、雇员名和工资为例，说明在PL/SQL块中使用CURSOR表达式的方法DECLARE   TYPE refcursor is ref cursor;   cursor dept_cursor(no number) is       select a.dname,cursor(select ename,sal from emp where deptno=a.deptno) from dept a where a.deptno=no;   empcur refcursor;   v_dname dept.dname%TYPE;   v_ename emp.ename%TYPE;   v_sal emp.sal%TYPE;begin   open dept_cursor(&no);   loop      fetch dept_cursor into v_dname,empcur;     exit when dept_cursor%NOTFOUND;     dbms_output.put_line('部门名:'||v_dname);     loop       fetch empcur into v_ename,v_sal;       exit when empcur%NOTFOUND;       DBMS_OUTPUT.put_line('雇员名：'||v_ename||',工资：'||v_sal);     end loop;   end loop;   close dept_cursor;end;select * from emp;select * from dept;