Oracle初探（六）

今天进行了游标的学习，学习笔记如下：

--游标
--定义游标
--打开游标
--读取数据
--关闭游标
declare
  cursor v_cursor is select ename,job from emp;
  v_name varchar2(20);
  v_job varchar2(20);
begin
  open v_cursor;
  fetch v_cursor into v_name,v_job;
  dbms_output.put_line(v_name||'    '||v_job);
  close v_cursor; 
end;
/

-------------输出emp所有数据
declare
  cursor v_cursor is select ename,job from emp;
  v_name emp.ename%type;
  v_job emp.job%type;
  v_id emp.empno%type;
begin
  open v_cursor;
  for v_id in 1..14 loop
      fetch v_cursor into v_name,v_job;
      dbms_output.put_line(v_name||'    '||v_job);
  end loop;
  close v_cursor; 
end;
/
----------------定义游标变量读取数据
declare
  cursor v_cursor is select e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno;
  cursor_record v_cursor%rowtype;
begin
  open v_cursor;
  loop
      fetch v_cursor into cursor_record ;
      exit when v_cursor%notfound;
      dbms_output.put_line(cursor_record.ename||'    '||cursor_record.job||'    '||cursor_record.dname);
  end loop;
  close v_cursor; 
end;
/
---------------------带参游标
declare
  cursor v_cursor(v_deptno number) is select e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno=v_deptno;
  cursor_record v_cursor%rowtype;
begin
  open v_cursor(&no);
  loop
      fetch v_cursor into cursor_record ;
      exit when v_cursor%notfound;
      dbms_output.put_line(cursor_record.ename||'    '||cursor_record.job||'    '||cursor_record.dname);
  end loop;
  close v_cursor; 
end;
/
--练习
---------------------------显示各个部门员工的姓名、工资及部门总工资
declare
    cursor cursor_dept is select deptno,loc from dept;
    cursor cursor_emp(v_deptno number) is select ename,sal from emp where deptno = v_deptno;
    
    v_deptno number(8);
    v_loc varchar2(20);
    
    v_ename varchar2(20);
    v_sal number(8);
    
    v_sum number(8) :=0;
    
begin
  
    open cursor_dept;

    loop
          fetch cursor_dept into v_deptno,v_loc;
          exit when cursor_dept%notfound;
          dbms_output.put_line('部门编号:' || v_deptno || ' ====================== ' || '部门地址：' || v_loc);
          
          open cursor_emp(v_deptno);
          
          loop
            fetch cursor_emp into v_ename,v_sal;
            exit when cursor_emp%notfound;
             dbms_output.put_line('姓名：' || v_ename || '工资：' || v_sal);
             
             v_sum := v_sum + v_sal;
          end loop;
          close cursor_emp;
          dbms_output.put_line(v_deptno || '号部门的总工资:' || v_sum);
          
          v_sum :=0;
          dbms_output.put_line(' ');
    end loop;
    close cursor_dept;
end;
/
-------------------ref游标
declare
    type ref_cursor is ref cursor;
    v_cursor ref_cursor;
    v_emp emp%rowtype;
begin
    open v_cursor for select * from emp;
    loop
         fetch v_cursor into v_emp;
         exit when v_cursor%notfound;
         dbms_output.put_line('编号：'||v_emp.ename||'工资：'||v_emp.sal);
    end loop;
end;
/
-------------------隐式游标
declare
    cursor v_cursor is select * from emp;
begin
    for i in v_cursor loop
      dbms_output.put_line('编号：'||i.ename||'工资：'||i.sal);
    end loop;
end;
/
---------------------练习
declare
    cursor cursor_dept is select deptno,loc from dept;
    cursor cursor_emp(v_deptno number) is select ename,sal from emp where deptno = v_deptno;
    v_deptno number(8);
    v_sum number(8) :=0;
begin
    for i in cursor_dept loop
          exit when cursor_dept%notfound;
          dbms_output.put_line('部门编号:' ||i.deptno || '============ ' || '部门地址：' || i.loc);
          
          for j in cursor_emp(i.deptno) loop
            exit when cursor_emp%notfound;
             dbms_output.put_line('姓名：' || j.ename || '工资：' || j.sal);
             
             v_sum := v_sum + j.sal;
          end loop;
          dbms_output.put_line(v_deptno || '号部门的总工资:' || v_sum);
          
          v_sum :=0;
          dbms_output.put_line(' ');
    end loop;
end;
/