[PL/SQL相关] 游标使用示例

------------------------------------------------------------
-- 显式游标
-- 每次提取一条数据 (带参数的游标)
declare
    cursor emp_cursor(no emp.deptno%type) is
        select ename,sal from emp where deptno=no;
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    open emp_cursor(10);
    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;
/

-- 一次提取所有数据
declare
    cursor emp_cursor is
        select ename from emp where deptno=10;
    type ename_table_type is table of emp.ename%type;
    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;
/

-- 一次提取部分数据
declare
    type name_array_type is varray(5) of emp.ename%type;
    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('name: ');
        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,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('name: '||emp_record.ename
            ||' sal: '||emp_record.sal);
    end loop;
    close emp_cursor;
end;
/

-- 使用游标更新数据
declare
    cursor emp_cursor is
        select ename,sal from emp for update of emp.deptno 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<2000 then
            update emp set sal=sal+100 where current of emp_cursor;
        end if;
    end loop;
    close emp_cursor;
    commit;
end;
/

------------------------------------------------------------
-- 隐式游标
-- 游标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;
/

-- 可以直接使用子查询代替显式游标
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=10;
    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子句
declare
    type emp_record_type is record (
        name emp.ename%type,sal emp.sal%type);
    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;
    loop
        fetch emp_cursor into emp_record;
        exit when emp_cursor%notfound;
        dbms_output.put_line(emp_cursor%rowcount||':'
            ||emp_record.name||' '||emp_record.sal);
    end loop;
    close emp_cursor;
end;
/

-- 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(v_ename||': '||v_sal||' ';
        end loop;
        dbms_output.new_line;
    end loop;
    close dept_cursor;
end;
/
------------------------------------------------------------