Oracle-游标和异常

1.使用游标主要遵循4个步骤——声明游标、打开游标、检索游标和关闭游标

declare
  cursor c_1 is select * from xs;--声明游标
  v_1 xs%rowtype;
begin
  open c_1;--打开游标
  fetch c_1 into v_1;--检索游标
  dbms_output.put_line(v_1.xh || '-------' || c_1%rowcount);
  fetch c_1 into v_1;--检索游标
  dbms_output.put_line(v_1.xh || '-------' || c_1%rowcount);
  close c_1;--关闭游标
  end;

2.带参数的游标

declare
  cursor c_1(v_xb xs.xb%type) is select * from xs
    where  xb=v_xb;
  v_1 xs%rowtype;
begin
  open c_1('男');
  fetch c_1 into v_1;
  dbms_output.put_line(v_1.xh || '-------' || c_1%rowcount);
  fetch c_1 into v_1;
  dbms_output.put_line(v_1.xh || '-------' || c_1%rowcount);
  fetch c_1 into v_1;
  dbms_output.put_line(v_1.xh || '-------' || c_1%rowcount);
  close c_1;
  end;

3.游标的%rowtype

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

4.

--for循环中使用游标
declare
  cursor c_1 is select xh from xs;
  v_xh xs.xh%type;
begin
  open c_1;
  loop
    fetch c_1 into v_xh;
    dbms_output.put_line(v_xh || '-------' || c_1%rowcount);
    exit when c_1%notfound;
    end loop;
   close c_1;
  end;

--while循环中使用游标
declare
 v_xh xs.xh%type;
 v_zxf xs.zxf%type;
 cursor c_1 is select xh,zxf from xs;
begin
  open c_1;
  fetch c_1 into v_xh,v_zxf;
  while c_1%found
    loop
      dbms_output.put_line(v_xh || '-------' ||v_zxf);
      fetch c_1 into v_xh,v_zxf;
      end loop;
      close c_1;
end;

--利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资；若平均工资大于3000,则输出“该部门平均工资较高。”
declare 
  cursor c_dept_stat is select deptno,avg(sal) avgsal from scott.emp group by deptno;
  v_dept c_dept_stat%rowtype;
begin
  OPEN c_dept_stat;
  FETCH c_dept_stat INTO v_dept; 
  WHILE c_dept_stat%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 c_dept_stat INTO v_dept;
  END LOOP;
  CLOSE c_dept_stat;
  end;

5.带异常处理的游标

 declare 
        cursor my_cursor is  select xh from xs;
         v_xh xs.xh%type;
begin
  if my_cursor%isopen=false then open my_cursor;
  end if;
         fetch my_cursor into v_xh;
          dbms_output.put_line(v_xh);
          dbms_output.put_line(my_cursor%rowcount);
          close my_cursor;  
   exception
       when others then 
          dbms_output.put_line(sqlcode||sqlerrm);
end;

6.游标的数量要与fetch中into的变量类型数量一致

DECLARE
     varId  NUMBER; 
     varName VARCHAR2(50);   
     CURSOR MyCur(v_xb  xs.xb%type)   IS SELECT xh, xm FROM xs WHERE xb=v_xb;
BEGIN 
      OPEN MyCur('男'); --打开游标，参数为‘男’，表示读取信息为男同学信息
      FETCH MyCur INTO varId, varName;
      dbms_output.put_line('学生编号:'|| varId ||'学生名:'||varName ) ;
      CLOSE MyCur;
END;

7.异常处理

exception 
  when 异常1 or 异常2 then 语句1;
    when 异常3 then 语句2;
      when others then 语句n;
        end;

8.VALUE_ERROR异常处理的例子

declare
     x number;
begin
  x:='aaa';
exception
  when value_error then
    dbms_output.put_line('数据类型异常') ;
  end;

9.查找“李明”同学的学号

declare
 v_xh xs.xh%type;
begin
  select xh into v_xh from xs where xs.xm='李明';
  dbms_output.put_line('李明同学的学号——' || v_xh) ;
  exception 
    when too_many_rows then
      dbms_output.put_line('结果返回了太多行') ;
      when no_data_found then
        dbms_output.put_line('没有找到数据') ;
        when others then 
          dbms_output.put_line('未知异常' || sqlcode || '------' || sqlerrm ) ;
  end;

10.用户定义异常

declare
 myexception exception;
begin
  exception 
    when myexception then 
      when others then
  end;

11.修改7844员工的工资(增加1000)，保证修改后工资不超过4000

declare
    myexception exception;
    v_sal scott.emp.sal%type;
begin
  update scott.emp set sal=sal+1000 where empno = 7844;
  select sal into v_sal from scott.emp where empno = 7844;
  if v_sal>=4000 then raise myexception;
  end if;
  exception
    when myexception then
      DBMS_OUTPUT.PUT_LINE('修改后工资超过了4000！！！');
      rollback;
  end;

12.更新scott.emp中7788员工的工资，若没有成功，请抛出异常

declare
    v_empno  scott.emp.empno%type;
    no_result  EXCEPTION;
Begin
   v_empno:=&a;
    update scott.emp  set sal=sal+100 where empno=v_empno;
    if  SQL%NOTFOUND   then
         raise no_result;
    end if;
exception
    when  no_result then
       dbms_output.put_line('数据没有更新');
    when others then
       dbms_output.put_line(sqlcode||' '||sqlerrm);
end;