Oracle 游标

游标逐行处理表中数据。
分类: 显式、隐式
属性: %FOUND,%ISOPEN,%NOTFOUND,%ROWCOUNT

使用游标：
--loop
declare
    cursor c is
        select * from emp;
    v_emp c%rowtype;
begin
    open c;  --打开游标
    loop
        fetch c into v_emp;
        exit when (c%notfound);
        dbms_output.put_line(v_emp.ename);
    end loop;
    close c; --关闭游标
end;
/

--while
declare
    cursor c is
        select * from dept;
    v_emp c%rowtype;
begin
    open c;
    fetch c into v_emp;
    while(c%found) loop
        dbms_output.put_line(v_emp.deptno||','||v_emp.dname||','||v_emp.loc);
        fetch c into v_emp;
    end loop;
    close c;
end;
/

--for：不需要打开、关闭游标
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 cur_para(no varchar2) is                --定义的参数不需要宽度
    select dname from dept where deptno >= no; --使用参数
    t_name dept.dname%TYPE;                    --指定一列类型
begin
    open cur_para('10');                       --指定参数
    loop
       fetch cur_para into t_name;
       exit when cur_para%NOTFOUND;
       dbms_output.put_line(t_name);
    end loop;
    close cur_para;
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;
/

游标的%ISOPEN属性：
declare
    cursor t_cur(no number) is                 --参数不给精度，长度
        select dname from dept where deptno=no;
    t_name dept.dname%TYPE;
begin
    if t_cur%ISOPEN then
        dbms_output.put_line('游标已被打开');
    else
        open t_cur(20);
    end if;
    fetch t_cur into t_name;
    close t_cur;
    dbms_output.put_line(t_name);
end;
/

游标的%ROWCOUNT属性：
declare
    cursor mycur is
        select dname from dept;
    t_name varchar2(10);
begin
    open mycur;
    loop
        fetch mycur into t_name;
        --未做fetch时%NOTFOUND为空。
        exit when mycur%NOTFOUND or mycur%NOTFOUND is null;
        dbms_output.put_line('游标mycur的ROWCOUNT是：'||mycur%ROWCOUNT);
    end loop;
    close mycur;
end;
/

使用游标修改数据：
--创建emp2表，然后使用游标。
create table emp2 as select * from emp;
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;
/
--比较：
select emp.sal, emp2.sal as sal2 from emp left join emp2 on emp.empno=emp2.empno;


隐式游标：
begin
    for cur in (select dname from dept) loop
      dbms_output.put_line(cur.dname);
    end loop;
end;
/