Ch5 PL/SQL使用游标获取数据

游标 1、隐式游标 见上章内容

          2、显式游标 静态

                                动态

 

 特殊情况，批量集合减少plsql和sql的上下开关，进而加速了数据获取的数据

 --1、使用静态游标，查询emp 所有数据，判断数据，如果工资少于2500
    --就添加 10%。
declare
cursor cur_emp is
    select * from emp;
v_emp_row emp%rowtype;
begin
  open cur_emp;
  loop
    fetch cur_emp into v_emp_row;
    if v_emp_row.sal<2500 then
        update emp set emp.sal = v_emp_row.sal*1.1 where emp.empno =v_emp_row.empno;
       end if;
    dbms_output.put_line(v_emp_row.ename);
  exit when cur_emp%notfound;
  end loop;
  close cur_emp;
end;
 --2、 使用引用游标实现上面功能。
 declare
type  cur_emp is ref cursor;
v_cursor cur_emp;
v_emp_row emp%rowtype;
begin
  open v_cursor for select * from emp;
  loop
    fetch v_cursor into v_emp_row;
    if v_emp_row.sal<2500 then
        update emp set emp.sal = v_emp_row.sal*1.1 where emp.empno =v_emp_row.empno;
       end if;
    dbms_output.put_line(v_emp_row.ename);
  exit when v_cursor%notfound;
  end loop;
  close v_cursor;
end;
 --3、 如果每页10条数据，查询第二页的数据
    --要求数据以员工的工资排序

create or replace procedure pro_split_by_item_and_page
      (v_item_num int,v_page_num int) is
   cursor cur_emp is
      select *  from
            (select innertable.*,rownum outrn  from
                (  select emp.*
                   from emp
                    order by emp.sal ) innertable 
            where rownum <= v_item_num * v_page_num) outertable
      where outrn > v_item_num *(v_page_num -1);
   v_emp_row cur_emp%rowtype;
begin
    open cur_emp;
    dbms_output.put_line('员工号  员工名字 职位   '||
            'MGR  工作时间 薪水   COMM 部门编号    ');
      loop
      fetch cur_emp  into v_emp_row;
        exit when cur_emp%notfound;
        dbms_output.put_line(v_emp_row.empno||'  '||v_emp_row.ename||' '
        ||v_emp_row.job||'  '||v_emp_row.mgr||'  '
        ||v_emp_row.hiredate||'  '||v_emp_row.sal||'  '
        ||v_emp_row.deptno);
      end loop;
   close cur_emp;
end;
call pro_split_by_item_and_page(10,2);
--或者
select *  from
            (select innertable.*,rownum outrn  from
                (  select emp.*
                   from emp
                    order by emp.sal ) innertable 
            where rownum <= 20) outertable
      where outrn >10;

create or replace procedure
  pro_split_by_item_and_page(v_item_num int,v_page_num int) is
  v_
begin
     select * from emp;
    (select innertable.*,rownum outrn from
        (  select emp.*
          from emp
           order by emp.sal ) innertable 
    where rownum <=3)outertable where outrn >2;
end;
 --4、 创建一个部门表(dept_copy)，
    -- 将人数少于5人的员工部门信息放在dept_copy中
create table dept_copy(
  DEPTNO NUMBER(2) not null,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
);
declare
  type deptno_type is table of dept.deptno%type index by binary_integer;
  v_deptno deptno_type;
  v_dept_row   dept%rowtype;
begin
   select dept.deptno bulk collect into v_deptno --查找满足条件的部门编号放到集合中
      from emp left join dept
      on emp.deptno = dept.deptno
      group by dept.deptno
      having count(*) <5;
    for i in 1..v_deptno.count loop--依次提取集合中的部门编号
      select * into v_dept_row--获得部门编号的行数据放到变量中
         from dept
         where dept.deptno = v_deptno(i);
      insert into dept_copy --添加数据到新表中
        values(v_dept_row.DEPTNO,v_dept_row.DNAME,v_dept_row.LOC);
    end loop; 
end;
 5、 查询每个部门中最低工资有哪些员工
declare
  type emp_dno_type is table of emp.deptno%type index by binary_integer;
  v_empno emp_dno_type;
  v_emp_ename emp.ename%type;
  v_emp_sal   emp.sal%type;
  v_dept_dname dept.dname%type;
begin
   select emp.deptno bulk collect into v_empno --部门分组
      from emp
      group by emp.deptno ;
    for i in 1..v_empno.count loop--依次提取集合中的部门编号
       select t1.ename,t1.sal into v_emp_ename,v_emp_sal
        from (select *
                from emp
                where emp.deptno = v_empno(i)
                order by emp.sal) t1
           where rownum <2;
      dbms_output.put_line(v_emp_ename||'  '||v_emp_sal||'  '||v_empno(i) );
    end loop; 
end;