游标和异常处理

set serveroutput on; //首先将此语句打开才能使得pl/sql程序能够输出结果 

（一） 游标

1.显式游标

（1）不带参数的显式游标（强调游标的4个步骤）

声明一个游标，读取学生的学号
declare
  cursor c_1 is select xh from xs; //定义游标
  v_xh xs.xh%type;
begin
  open c_1; //打开游标
  fetch c_1 into v_xh; //检索游标
  dbms_output.put_line(v_xh);
  dbms_output.put_line(v_xh||' '||c_1%rowcount);
  close c_1; //关闭游标
end;

(2)游标与表的%rowtype结合

declare
  cursor c_2 is select * from xs;
  v_1 xs%rowtype;
begin
  open c_2;
  fetch c_2 into v_1;
  dbms_output.put_line(v_1.xh); //括号内必须具体
  close c_2;
end;  

（3）带参数的游标

declare
  cursor c_3(v_xb xs.xb%type) is select * from xs where xb=v_xb;
  v_2 xs%rowtype;
begin
  open c_3('男');  //打开游标，参数为‘男’，表示读取信息为男同学信息
  fetch c_3 into v_2;
  dbms_output.put_line(v_2.xh||' '||c_3%rowcount);
  close c_3;
end;

(4)游标的%rowtype类型

declare
   cursor c_4 is select xs.xh,xm,kch,cj from xs,xs_kc where xs.xh=xs_kc.xh;
   v_3 c_4%rowtype;
begin
  open c_4;
  fetch c_4 into v_3;
  dbms_output.put_line(v_3.xh||' '||v_3.xm||' '||v_3.kch||' '||v_3.cj);
  close c_4;
end;

2.显式游标的属性 

1）%FOUND

布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;

2）%NOTFOUND

布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;

3）%ROWCOUNT

数值型，返回到目前为止从游标缓冲区检索的元组数。

4）%ISOPEN

布尔型。如果游标已经打开，返回TRUE,否则为FALSE。

例如：游标的%isopen 属性练习
declare
   cursor  c_1 is select * from xs;
   v_1 c_1%rowtype;
begin 
  if c_1%isopen=false then
         open c_1;
  end if;
  fetch c_1 into v_1;
  dbms_output.put_line(v_1.xh||v_1.xm||v_1.zxf);
  close c_1;
end;

 3.游标的遍历

Ⅰ.while循环

1)用游标遍历xs表中的xh

declare
  cursor c_6 is select xh from xs;
  v_xh xs.xh%type;
begin
  open c_6;
  fetch c_6 into v_xh; //打开游标后用fetch语句先取一行到变量
  while c_6%found
    loop
      dbms_output.put_line(v_xh);
      fetch c_6 into v_xh;
    end loop;
    close c_6;
end;

2）利用游标while循环统计并输出scott.emp表各个部门的平均工资；若平均工资大于2000,则输出“该部门平均工资较高。” 

declare
   cursor c1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
   v_dept c1%rowtype;
begin
  open c1;
  fetch c1 into v_dept;
  while c1%found 
    loop
        dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
      if (v_dept.avgsal>=2000) then
        dbms_output.put_line(v_dept.deptno||'号部门工资较高');
      end if; 
        fetch c1 into v_dept;
    end loop;
    close c1;
end;

Ⅱ.for循环 

1)遍历xs表中的xh

declare
  cursor c_5 is select xh from xs;
  v_xh xs.xh%type;
begin 
  open c_5;
  loop
    fetch c_5 into v_xh;
    exit when c_5%notfound;
    dbms_output.put_line(v_xh);
  end loop;
  close c_5;
end;

  2）利用for循环统计并输出各个部门的平均工资

⑴
declare
   cursor c1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
   v_dept c1%rowtype;
begin
    for v_dept in c1
    loop
        dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
    end loop;
end;

（2）//比第一种省略了v_dept,系统会自动将v_dept看作c1%rowtype类型
declare
   cursor c1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
begin
    for v_dept in c1
    loop
        dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
    end loop;
end;

(3)//更简化，直接使用select 子查询代替游标名，但不推荐使用此种方式
declare
begin
    for v_dept in(select deptno,avg(sal) avgsal from scott.emp group by deptno)
    loop
        dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
    end loop;
end;

4.使用游标更新数据（带for update ）

1）在Scott.emp表中，利用游标，给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新”。

declare
   v_1 scott.emp.empno%type;
   v_sal scott.emp.sal%type;
   cursor c1 is select empno,sal from scott.emp where sal<1200 for update; //此处要for update
begin
   open c1;
   loop
     fetch c1 into v_1,v_sal;
     exit when c1%notfound;
        update scott.emp set sal=sal+50 where current of c1; //注意
        dbms_output.put_line('雇员号为'||v_1||'工资已更新');
        dbms_output.put_line('记录数：'||c1%rowcount);
   end loop;
   close c1;
end;

2）修改scott.emp表员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。  （与分支结构结合）

declare
   cursor c1 is select * from scott.emp for update;
   v_1 c1%rowtype;
   v_zl scott.emp.sal%type;
begin
  for v_1 in c1
   loop
     case v_1.deptno
       when 10 then v_zl:=100;
       when 20 then v_zl:=150;
       when 30 then v_zl:=200;
       else   v_zl:=250;
     end case;
    update scott.emp set sal=sal+v_zl where current of c1;
   end loop;
end;

3）修改emp表的工资，工资不足1000的，调整为1500，工资高于1000的，调整为原来工资的1.5倍，调整后，若工资〉10000，则设其为10000。

declare
   cursor c1 is select empno,sal from scott.emp for update of sal nowait; //注意
   v_sal scott.emp.sal%type;
begin
  for v1 in c1
   loop
     if v1.sal<=1000 then v_sal:=1500;
     else v_sal:=v1.sal*1.5;
       if v_sal>10000 then v_sal:=10000;
       end if;
     end if;
    update scott.emp set sal=v_sal where current of c1;
   end loop;
end;

5.游标变量

创建游标变量的两个步骤

①定义REF CURSOR 类型 即引用游标类型

②声明这种类型的游标变量

declare
    type t_dept is ref cursor; //注意定义方式
    c_1 t_dept;
    v_row  scott.emp%rowtype;
begin
    open c_1 for select * from scott.emp where deptno=10; //注意
    fetch c_1 into v_row;
    dbms_output.put_line(v_row.empno||' ' ||v_row.job);
    close c_1; 
       
    open c_1 for select * from scott.emp where sal>=2000;
    fetch c_1 into v_row;
    dbms_output.put_line(v_row.deptno||' ' ||v_row.job);
    close c_1;
end;

（二）异常处理

（1）预定义的oracle异常

1)
declare
   x number;
begin
   x:='aaaa';
   exception
     when value_error then dbms_output.put_line('数据类型错误');
end;

2)
declare
  v_result xs.xm%type;
begin
  select xh into v_result from xs where xm='李    明';
  dbms_output.put_line('The student number is '||v_result);
  exception
    when too_many_rows then 
      dbms_output.put_line('there has too_many_rows error!');
    when no_data_found then
      dbms_output.put_line('there has no_data_found error!');
    when others then
      dbms_output.put_line(sqlcode||' '||sqlerrm);
end;

3)      
declare
  v_result xs.zxf%type;
begin
  select xh into v_result from xs where xm='李明';
  dbms_output.put_line('The student number is '||v_result);
  exception
    when too_many_rows then 
      dbms_output.put_line('there has too_many_rows error!');
    when no_data_found then
      dbms_output.put_line('there has no_data_found error!');
    when others then
      dbms_output.put_line(sqlcode||' '||sqlerrm);
end;

（2）自定义异常处理

declare
  e_1 exception;
  v_sal scott.emp.sal%type;
begin
  update scott.emp set sal=sal+4000 where empno=7788;
  select sal into v_sal from scott.emp where empno=7788;
  if v_sal>6000 then raise e_1; //抛出异常
  end if;
  exception
     when e_1 then dbms_output.put_line('The salary is too large!');
     rollback; //回滚，还是回到原来的值
end;