游标对表数据的增，删，改及引用游标

declare
  cursor emp_cursor is select ename,salary from emp for update;
  v_ename emp.ename%type;
  v_salary emp.salary%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into v_ename,v_salary;
    exit when emp_cursor%notfound;
    if v_salary < 1000 then
      update emp set salary = 1000 where current of emp_cursor;
    end if;
  end loop;
  close emp_cursor;
end;     
     
declare
  cursor emp_cursor is select deptno,ename,salary from emp for update;
  v_deptno emp.deptno%type;
  v_ename emp.ename%type;
  v_salary emp.salary%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into v_deptno,v_ename,v_salary;
    exit when emp_cursor%notfound;
    if v_deptno = 3 then
      delete from emp;
    end if;
  end loop;
  close emp_cursor;
end;     

declare
  cursor emp_cursor is select ename,salary,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 = 30 then
      update emp set salary = salary + 100 where current of emp_cursor;
    end if;
  end loop;
  close emp_record;
end;      

declare
  cursor emp_cursor is select ename,salary from emp for update nowait;
  v_ename emp.ename%type;
  v_salary emp.salary%type;
begin
  open emp_cursor;
  loop
    fetch  emp_cursor into v_ename,v_salary;
    exit when emp_cursor%notfound;
    if v_salary < 1000 then
      update emp set salary = salary * 1.1 where current of emp_cursor;
    end if;
  end loop;
  close emp_cursor;
end;      

declare
  cursor emp_cursor is select ename,salary from emp;
begin
  for emp_record in emp_cursor loop
    dbms_output.put_line(emp_record.ename);
  end loop;
end;

begin
  for emp_record in (select ename,salary from emp) loop
    dbms_output.put_line(emp_record.ename);
  end loop;
end;       

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 empno = '2';
   loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line(emp_record.ename);
   end loop;
   close emp_cursor;
end;

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,salary from emp where empno='2';
  loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line(emp_record.name);
  end loop;
  close emp_cursor;
end;      
 
declare
  type refcursor is ref cursor;
  cursor dept_cursor(no number) is
    select a.dname,cursor(select ename,salary from emp where deptno = a.deptno)
    from dept a where a.deptno = no;   
  empcursor ref cursor;
  v_dname dept.dname%type;
  v_ename dept.ename%type;
  v_sal emp.salary%type;
begin
  open dept_cursor(&no);
  loop
    fetch dept_cursor into v_dname,empcursor;
    exit when dept_cursor%notfound;
    dbms_output.put_line(v_dname);
    loop
      fetch empcursor into v_ename,v_sal;
      exit when empcursor%notfound;
      dbms_output.put_line(v_ename||','||v_sal);
    end loop;
   end loop
   close dept_cursor;
end;