Oracle备忘录（十一）--游标，异常，PL/SQL

PL/SQL 是面向对象的语言
是操作数据库最快的语言
操作数据库（增删改查） 写成pl/sql，提高性能
而不是写在jdbc中

光标（游标）-->相当于是ResultSet

show parameters cursor  --查看带有cursor字样的参数



-------**********------pl/sql输入------*************--------
--判断用户输入的数字
set serveroutput on

--接收键盘输入
--num: 地址值，在该地址上，存了输入的值
accept num prompt '请输入一个数字';

declare
  --定义变量保存输入的数字
  --隐式转换
  pnum number := #
begin

  if pnum = 0 then dbms_output.put_line('您输入的是0');
    elsif pnum = 1 then dbms_output.put_line('您输入的是1');
    elsif pnum = 2 then dbms_output.put_line('您输入的是2');
    else dbms_output.put_line('其他数字');
  end if;
end;
/


---**********----带参数的光标----************--------
--查询某个部门中员工的姓名
set serveroutput on

declare
  --带参数的光标
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;  --属于emp.ename的一个类型变量
begin
  open cemp(20);
  loop  
    fetch cemp into pename;
    exit when cemp%notfound;
    dbms_output.put_line(pename);
  end loop;
  close cemp;
end;
/


-----********--------****综合案例****---------*********----------
--涨工资 总裁1000 经理800 其他400
set serveroutput on

declare
  cursor cemp is select empno,empjob from emp;
  pempno emp.empno%type;
  pjob   emp.empjob%type;
begin
  rollback;
  open cemp;
  loop
    --取一个员工 涨工资
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;
    
    --判断职位
    if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
      elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
      else update emp set sal=sal+400 where empno=pempno;
    end if;
  end loop;
  close cemp;
  --事务的隔离级别，这个界面提交后，另外一个窗口才能检查的到
  commit;
  dbms_output.put_line('涨工资完成');
end;
/


------************------光标------------***********---------
--使用游标查询员工姓名和工资，并打印

/*
1. 光标的属性
%isopen: 是否打开
%rowcount: 行数
%notfound: 没有记录

2. 默认允许一次打开300个光标(修改光标： 第四天 管理方案)
SQL> show parameters cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20
*/



set serveroutput on
declare
  --定义光标代表员工集合
  cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal emp.sal%type;
begin
  open cemp;
  loop
    --取一个员工
    fetch cemp into pename,psal;
    --退出条件
    exit when cemp%notfound;
    dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
  close cemp;
end;
/


/*
实例1：统计每年入职的员工个数。
SQL语句
1. select to_char(hiredate,'RR') from emp; --> 光标  --> 循环(notfound)
2. count80 number := 0;
   count81 number := 0;
   count82 number := 0;
   count87 number := 0;
*/
set serveroutput on 

declare
  cursor cemp is select to_char(hiredate,'RR') from emp;
  phiredate varchar2(4);
  
  --计数器
  count80 number := 0;
  count81 number := 0;
  count82 number := 0;
  count87 number := 0;
begin
  open cemp;
  loop
    --取一个员工
    fetch cemp into phiredate;
    exit when cemp%notfound;

    --判断年份
    if phiredate = '80' then count80:=count80+1;
      elsif phiredate = '81' then count81:=count81+1;
      elsif phiredate = '82' then count82:=count82+1;
      else count87:=count87+1;
    end if;

  end loop;
  close cemp;
  
  dbms_output.put_line('Total:'||(count80+count81+count82+count87));
  dbms_output.put_line('80:'||count80);
  dbms_output.put_line('81:'||count81);
  dbms_output.put_line('82:'||count82);
  dbms_output.put_line('87:'||count87);
end;
/


set serveroutput on;
--从最低工资开始调，每个人加上10%，但是工资总额不要超过100000，打印出涨工资的人数和总额
declare 
  cursor mycur is select empno,sal from mytest order by sal;
  mynum mytest.empno%type;
  mysal mytest.sal%type;
  mycount number := 0;  --用于人数
  amount number :=0 ;   --用于计算总额
begin 
  select sum(sal) into amount from mytest;
  open mycur;
    loop
        exit when amount>=100000;
        fetch mycur into mynum,mysal;
        exit when mycur%notfound;
        
        if (amount + mysal * 0.1< 100000) then
         amount := amount + mysal * 0.1;
        
         update mytest set sal = sal *1.1 where empno = mynum;
         mycount := mycount + 1;
        end if ;
    end loop;
  close mycur;
  commit;
  dbms_output.put_line('人数:'||mycount||'  工资总额:'||amount);
end;
/
set serveroutput on;

rollback;



/*
用PL/SQL语言编写一程序，实现按部门分段（6000以上、(6000，3000)、3000元以下)
统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)

SQL语句
1. 部门: select deptno from dept; --> 光标 --> 循环
2. 部门中员工的薪水: select sal from emp where deptno=??? --> 带参数的光标 -->  循环
3. count1 number; count2 number; count3 number;
4. 部门工资总额: salTotal number;
               select sum(sal) into salTotal from emp where deptno=???
*/
set serveroutput on

declare
  --部门
  cursor cdept is select deptno from dept;
  pdeptno dept.deptno%type;
  
  --部门中员工的薪水
  cursor cemp(dno number) is select sal from emp where deptno=dno;
  psal emp.sal%type;
  
  --计数器
  count1 number; count2 number; count3 number;
  
  --部门的工资总额
  salTotal number;
begin
  open cdept;
  loop
    --取一个部门
    fetch cdept into pdeptno;
    exit when cdept%notfound;
    
    --初始化 
    count1:=0;count2:=0;count3:=0;
    
    --部门的工资总额
    select sum(sal) into salTotal from emp where deptno=pdeptno;
    
    --部门中员工的薪水
    open cemp(pdeptno);
    loop
      --取一个员工的薪水cl
      fetch cemp into psal;
      exit when cemp%notfound;
      
      --判断
      if psal< 3000 then count1:=count1+1;
        elsif psal>=3000 and psal<6000 then count2:=count2+1;
        else count3:=count3+1;
      end if;        

    end loop;
    close cemp;
    
    -- 保存当前结果
    insert into msg1 values(pdeptno,count1,count2,count3,nvl(salTotal,0));

  end loop;
  close cdept;
  
  commit;
  
  dbms_output.put_line('完成');
end;
/
  
  
--*********---定义异常---********-------
--被0除
set serveroutput on

declare
  pnum number;
begin
  pnum := 1/0;

exception
  when Zero_Divide then dbms_output.put_line('1:0不能做被除数');
                        dbms_output.put_line('2:0不能做被除数');
  when Value_error then dbms_output.put_line('算术或转换错误');                 
  when others then dbms_output.put_line('其他例外');      
end;
/
  

----------------*********自定义异常**********-------------------------------
--查询50号部门的员工

set serveroutput on

declare
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;
  
  --自定义例外
  no_emp_found exception;
begin
  open cemp;

  --取一个员工
  fetch cemp into pename;
  
  if cemp%notfound then
    --抛出例外
    raise no_emp_found;
  end if;

  --当抛出例外，自动关闭
  close cemp;
  
exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外');   
end;
/