PL-SQL经典试题

15. 利用游标 for 循环完成 14. 

 

declare

    --定义游标

    cursor emp_sal_cursor is select salary, employee_id id from employees;

    

    --定义基数变量

    temp number(4, 2);

begin

    --处理游标的循环操作

    for c in emp_sal_cursor loop

          --判断员工的工资, 执行 update 操作

          --dbms_output.put_line(c.employee_id || ': ' || c.salary);

            

          if c.salary <= 5000 then

             temp := 0.05;

          elsif c.salary <= 10000 then

             temp := 0.03;   

          elsif c.salary <= 15000 then

             temp := 0.02;

          else

             temp := 0.01;

          end if;

          

          --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);

          update employees set salary = salary * (1 + temp) where employee_id = c.id;

    end loop;

end;

 

16*. 带参数的游标

 

declare

    --定义游标

    cursor emp_sal_cursor(dept_id number, sal number) is

           select salary + 1000 sal, employee_id id

           from employees

           where department_id = dept_id and salary > sal;

    

    --定义基数变量

    temp number(4, 2);

begin

    --处理游标的循环操作

    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop

          --判断员工的工资, 执行 update 操作

          --dbms_output.put_line(c.id || ': ' || c.sal);

          

          if c.sal <= 5000 then

             temp := 0.05;

          elsif c.sal <= 10000 then

             temp := 0.03;   

          elsif c.sal <= 15000 then

             temp := 0.02;

          else

             temp := 0.01;

          end if;

          

          dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);

          --update employees set salary = salary * (1 + temp) where employee_id = c.id;

    end loop;

end;

 

17. 隐式游标: 更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息

 

begin

         update employees set salary = salary + 10 where employee_id = 1005;

         

         if sql%notfound then

            dbms_output.put_line('查无此人!');

         end if;

end;

 

*******************************************************************************

异常处理

*******************************************************************************

[预定义异常]

declare

 

  v_sal employees.salary%type;

begin

  select salary into v_sal

  from employees

  where employee_id >100;

  

  dbms_output.put_line(v_sal);

 

exception

  when Too_many_rows then dbms_output.put_line('输出的行数太多了');

end;

 

[非预定义异常]

declare

 

  v_sal employees.salary%type;

  --声明一个异常

  delete_mgr_excep exception;

  --把自定义的异常和oracle的错误关联起来

  PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);

begin

  delete from employees

  where employee_id = 100;

  

  select salary into v_sal

  from employees

  where employee_id >100;

  

  dbms_output.put_line(v_sal);

 

exception

  when Too_many_rows then dbms_output.put_line('输出的行数太多了');

  when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');

end;

 

[用户自定义异常]

declare

 

  v_sal employees.salary%type;

  --声明一个异常

  delete_mgr_excep exception;

  --把自定义的异常和oracle的错误关联起来

  PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);

  

  --声明一个异常

  too_high_sal exception;

begin

 

  select salary into v_sal

  from employees

  where employee_id =100;

  

  if v_sal > 1000 then

     raise too_high_sal;

  end if;

     

  delete from employees

  where employee_id = 100;

 

  dbms_output.put_line(v_sal);

 

exception

  when Too_many_rows then dbms_output.put_line('输出的行数太多了');

  when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');

  --处理异常

  when too_high_sal then dbms_output.put_line('工资过高了');

end;

 

18. 异常的基本程序:

通过 select ... into ... 查询某人的工资, 若没有查询到, 则输出 "未找到数据"

 

declare

  --定义一个变量

  v_sal employees.salary%type;

begin

  --使用 select ... into ... 为 v_sal 赋值

  select salary into v_sal from employees where employee_id = 1000;

  dbms_output.put_line('salary: ' || v_sal);

exception

  when No_data_found then

       dbms_output.put_line('未找到数据');

end;

 

或

 

declare

  --定义一个变量

  v_sal employees.salary%type;

begin

  --使用 select ... into ... 为 v_sal 赋值

  select salary into v_sal from employees;

  dbms_output.put_line('salary: ' || v_sal);

exception

  when No_data_found then

       dbms_output.put_line('未找到数据!');

  when Too_many_rows then

       dbms_output.put_line('数据过多!');     

end;

 

19. 更新指定员工工资，如工资小于300，则加100；对 NO_DATA_FOUND 异常, TOO_MANY_ROWS 进行处理.

declare

   v_sal employees.salary%type;

begin

   select salary into v_sal from employees where employee_id = 100;

   

   if(v_sal < 300) then update employees set salary = salary + 100 where employee_id = 100;

   else dbms_output.put_line('工资大于300');

   end if;

exception

   when no_data_found then dbms_output.put_line('未找到数据');

    when too_many_rows then dbms_output.put_line('输出的数据行太多');

end;

 

20. 处理非预定义的异常处理: "违反完整约束条件"

 

declare

  --1. 定义异常

  temp_exception exception;

 

  --2. 将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 EXCEPTION_INIT 语句

  PRAGMA EXCEPTION_INIT(temp_exception, -2292);

begin

  delete from employees where employee_id = 100;

 

exception

  --3. 处理异常

  when temp_exception then

       dbms_output.put_line('违反完整性约束!');

end;

 

 

本教程由尚硅谷教育大数据研究院出品，如需转载请注明来源。