2006年01月16日

DECLARE
  CURSOR C1 is
    select empno, ename from emp;
  cursor c2 is
    select * from dept where deptno = 10;
  v_empno  emp.empno%type;
  v_ename  emp.ename%type;
  v_deptno dept.deptno%type;
  v_dname  dept.dname%type;
  v_loc    dept.loc%type;
BEGIN
  if not c1%isopen then
    open c1;
  end if;
  loop
    fetch c1
      into v_empno, v_ename;
    exit when c1%notfound;
    dbms_output.put_line('Employee id is:' || v_empno);
    dbms_output.put_line('Employee Name is:' || v_ename);
  end loop;
  close c1;
  open c2;
  loop
    fetch c2
      into v_deptno, v_dname, v_loc;
    exit when c2%notfound;
    dbms_output.put_line('Dept No is:' || v_deptno);
    dbms_output.put_line('Dept Name is:' || v_dname);
    dbms_output.put_line('Dept Loc is:' || v_loc);
  end loop;
  close c2;
END;
/

 

DECLARE
  CURSOR C1 is
    select empno, ename from emp;
  cursor c2 is
    select * from dept where deptno = 10;
  v_empno  emp.empno%type;
  v_ename  emp.ename%type;
  v_dept dept%rowtype;
BEGIN
  if not c1%isopen then
    open c1;
  end if;
  loop
    fetch c1
      into v_empno, v_ename;
    exit when c1%notfound;
    if v_empno = 7369 then
      update emp_t set sal = sal * 1.5 where empno = 7369;
    elsif v_ename = 'SCOTT' then
      delete from emp_t where ename = 'SCOTT';
    end if;
    dbms_output.put_line('Employee id is:' || v_empno);
    dbms_output.put_line('Employee Name is:' || v_ename);
  end loop;
  close c1;
  open c2;
  loop
    fetch c2--select * 语句定义的游标可以使用整个变量
      into v_dept;
    exit when c2%notfound;
    dbms_output.put_line('Dept No is:' || v_dept.deptno);
    dbms_output.put_line('Dept Name is:' || v_dept.dname);
    dbms_output.put_line('Dept Loc is:' || v_dept.loc);
  end loop;
  close c2;
  for v_dept in c2 loop --for循环会默认打开、关闭游标,也会自动fetch
    --fetch c2
      --into v_dept;
    dbms_output.put_line('Dept No is:' || v_dept.deptno);
    dbms_output.put_line('Dept Name is:' || v_dept.dname);
    dbms_output.put_line('Dept Loc is:' || v_dept.loc);
  end loop;
  if c2%isopen then--发现for循环后游标已经关闭
    dbms_output.put_line('Cursor is open.');
  else
    dbms_output.put_line('Cursor is close');
  end if;
END;
/

DECLARE
  CURSOR C1 (v_deptno Number, v_job varchar2) is
    select empno, ename
      from emp
     where deptno = v_deptno
       and job = v_job;
  v_empno number;
  v_ename varchar2(20);
BEGIN
  open c1(10, 'CLERK');
  loop
    fetch c1
      into v_empno, v_ename;
    exit when c1%notfound;
    dbms_output.put_line('The employy No is:' || v_empno);
    dbms_output.put_line('The employy Name is:' || v_ename);
  end loop;
  close c1;
  for v_emp in c1(20,'CLERK') loop
    dbms_output.put_line('The employy No is:' || v_emp.empno);
    dbms_output.put_line('The employy Name is:' || v_emp.ename);
  end loop;
END;
/