PL/SQL游标

--------------------------------------------------------
declare
  cursor c is
    select * from emp;
  v_emp emp%rowtype;
begin
  open c;
  fetch c
    into v_emp;
  while (c%found) loop
    dbms_output.put_line(v_emp.ename);
    fetch c
      into v_emp;
  end loop;
  close c;
end;
--------------------------------------------------------
declare
  cursor c is
    select * from emp;
begin
  for v_emp in c loop
    dbms_output.put_line(v_emp.ename);
  end loop;
end;
--------------------------------------------------------
declare
  cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is
    select ename, sal
      from emp
     where deptno = v_deptno
       and job = v_job;
begin
  for v_temp in c(30, 'clerk') loop
    dbms_output.put_line(v_temp.ename);
  end loop;
end;
--------------------------------------------------------
declare
  cursor c is
    select * from emp2 for update;
begin
  for v_temp in c loop
    if (v_temp.sal < 2000) then
      update emp2 set sal = sal * 2 where current of c;
    elsif (v_temp.sal = 5000) then
      delete from emp2 where current of c;
    end if;
  end loop;
  commit;
end;
--------------------------------------------------------
delete from emp2;
select * from emp;
select * from emp2;
--------------------------------------------------------
declare
  cursor c is
    select * from emp;
begin
  for v_temp in c loop
    insert into emp2
      (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values
      (v_temp.empno,
       v_temp.ename,
       v_temp.job,
       v_temp.mgr,
       v_temp.hiredate,
       v_temp.sal,
       v_temp.comm,
       v_temp.deptno);
  end loop;
  commit;
end;
--------------------------------------------------------
select * from emp2;

create or replace package AA_CURSOR_PACKAGE is

  procedure test_cursor(varType in NUMBER);
  procedure test_cursor_1(varType in NUMBER);
  procedure test_cursor_2(varType in NUMBER);
  procedure test_cursor_3(varType in NUMBER);
  procedure test_cursor_4(varType in NUMBER);
  procedure test_cursor_5(varType in NUMBER);

end AA_CURSOR_PACKAGE;

----------------------------------------------------------------------
create or replace package body AA_CURSOR_PACKAGE is
  varId   NUMBER;
  varName VARCHAR2(50);
  TYPE User_Record_Type IS RECORD(
    UserId   AA_USERS.USERID%TYPE,
    UserName AA_USERS.USERNAME%TYPE);
  var_UserRecord  User_Record_Type;
  var_UserRecord1 AA_USERS%ROWTYPE;
  CURSOR MyCur(varType NUMBER) IS
    SELECT USERID, USERNAME FROM AA_USERS WHERE USERTYPE = varType;
----------------------------------------------------------------------  
procedure test_cursor(varType in number) as
  begin
    open MyCur(1);
    IF MyCur%ISOPEN = FALSE THEN
      OPEN MyCur(2);
    END IF;
    fetch MyCur
      into varId, varName;
    close MyCur;
    dbms_output.put_line('UserId:' || varId || ' , UserName:' || varName);
  end;
----------------------------------------------------------------------  
procedure test_cursor_1(varType in NUMBER) as
  BEGIN
    IF MyCur%ISOPEN = FALSE THEN
      OPEN MyCur(1);
    END IF;
    FETCH MyCur
      INTO varId, varName;
    WHILE MyCur%FOUND LOOP
      dbms_output.put_line('UserId:' || varId || ' , UserName:' || varName);
      FETCH MyCur
        INTO varId, varName;
    END LOOP;
    CLOSE MyCur;
  END;
----------------------------------------------------------------------  
procedure test_cursor_2(varType in NUMBER) as
  BEGIN
    IF MyCur%ISOPEN = FALSE THEN
      OPEN MyCur(1);
    END IF;
    FETCH MyCur
      INTO varId, varName;
    WHILE MyCur%FOUND LOOP
      dbms_output.put_line('UserId:' || varId || ' , UserName:' || varName);
      IF MyCur%ROWCOUNT = 2 THEN
        EXIT;
      END IF;
      FETCH MyCur
        INTO varId, varName;
    END LOOP;
    CLOSE MyCur;
  END;
----------------------------------------------------------------------  
procedure test_cursor_3(varType in NUMBER) as
  BEGIN
    IF MyCur%ISOPEN = FALSE THEN
      OPEN MyCur(1);
    END IF;
    LOOP
      FETCH MyCur
        INTO var_UserRecord;
      EXIT WHEN MyCur%NOTFOUND;
      dbms_output.put_line('UserId:' || var_UserRecord.UserId ||
                           ' , UserName:' || var_UserRecord.UserName);
    END LOOP;
    CLOSE MyCur;
  END;

----------------------------------------------------------------------  
procedure test_cursor_4(varType in NUMBER) as
  BEGIN
    FOR var_UserRecord IN MyCur(1) LOOP
      dbms_output.put_line('UserId:' || var_UserRecord.UserId ||
                           ' , UserName:' || var_UserRecord.UserName);
    END LOOP;
  END;
----------------------------------------------------------------------  
procedure test_cursor_5(varType in NUMBER) as
  BEGIN
    FOR var_UserRecord IN (SELECT UserId,UserName FROM AA_USERS WHERE UserType=1) LOOP
      dbms_output.put_line('UserId:' || var_UserRecord.UserId ||
                           ' , UserName:' || var_UserRecord.UserName);
    END LOOP;
  END;
----------------------------------------------------------------------
end AA_CURSOR_PACKAGE;