PLSQL Day4

--使用显式游标更新行，对所有salesman增加500奖金：
declare
  cursor s_cursor is
  select * from emp 
  where job = 'SALESMAN'
  for update;
begin
  for e_s in s_cursor loop
    update emp set comm = nvl(comm,0)+500 
    where current of s_cursor;
  end loop;
end;

--3.定义游标：显示所有部门编号与名称，以及其所拥有的员工人数：
declare
  cursor i_cursor is
  select d.deptno,d.dname,count(e.empno) cnt
  from dept d  left join emp e
  on d.deptno = e.deptno
  group by d.deptno,d.dname;
begin
  for e in i_cursor loop
    dbms_output.put_line(e.deptno||' '||e.dname||' '||e.cnt);
  end loop;
end;

--4.用游标属性%rowcount实现输出前十个员工的信息：

declare
  cursor a_cursor is
  select * from emp;
begin
  for e in a_cursor loop
    if a_cursor%rowcount < 11 
       then dbms_output.put_line(e.empno||' '||e.ename||' '||e.job
         ||' '||e.mgr||' '||to_char(e.hiredate,'yyyy-MM-dd')
         ||' '||e.sal||' '||e.comm
         ||' '||e.deptno);
    end if;
  end loop;
end;
--5.通过使用游标来显示dept表中的部门名称，
--及其相应的员工列表（提示：可以使用双重循环）：
declare
  cursor a_cur is select * from dept;
  cursor b_cur is select * from emp;
begin
  for a in a_cur loop
    for b in b_cur loop
      if a.deptno=b.deptno then 
        dbms_output.put_line(a.dname||' '||b.empno||' '
        ||b.ename||' '||b.job||' '||b.mgr||
        ' '||to_char(b.hiredate,'yyyy-mm-dd')||' '
        ||b.sal||' '||b.comm);
      end if;
    end loop;
  end loop;
end;

--6.定义游标：接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水：
declare
  cursor c_cur(dno number) is select * from emp where deptno = dno;
begin
  for e in c_cur(&部门号) loop
    dbms_output.put_line(e.ename||' '||e.job||' '||e.sal);
  end loop;
end;

--7.定义游标：将emp表中前5人的名字，及其工资等级（salgrade）显示出来：
declare
  cursor d_cur is select e.ename,s.grade 
  from emp e join salgrade s
  on e.sal between s.losal and s.hisal;
begin
  for e in d_cur loop
    if d_cur%rowcount < 6
      then dbms_output.put_line(e.ename||' '||e.grade)  ;  
    end if;
  end loop;
end;

--8.定义游标：在emp表中对所有雇员按他们基本薪水的10%给他们加薪，
--如果所增加后的薪水大于5000，则取消加薪：

declare
  cursor e_cur is select * from emp for update;
begin
  for e in e_cur loop    
    e.sal := e.sal*1.1;
    if e.sal<=5000 then
       update emp set sal = sal*1.1 where current of e_cur;
    end if;
  end loop;
end;

select * from emp;
--9.按照salgrade表中的标准，给员工加薪，
--1：5%，2：4%，3：3%，4：2%，5：1%，
--并打印输出每个人，加薪前后的工资：

declare
  cursor f_cur is 
  select e.empno,e.ename,e.sal,s.grade 
  from emp e join salgrade s
  on e.sal between s.losal and s.hisal
  for update;
  e_sal number;
begin
  for e in f_cur loop
    if e.grade = 1 
    then e_sal := e.sal*1.05  ;        
    elsif e.grade = 2 
    then e_sal := e.sal*1.04  ;  
    elsif e.grade = 3 
    then e_sal := e.sal*1.03  ; 
    elsif e.grade = 4 
    then e_sal := e.sal*1.02  ;
    else e_sal := e.sal*1.01  ; 
    end if;
    dbms_output.put_line(e.ename||' - 前：'||e.sal||'  后：'||e_sal);
    update emp set sal = e_sal  
    where empno = e.empno;
  end loop;
end;

select * from emp;
select * from salgrade;

-----

declare
  cursor f_cur is select * from emp for update;
  cursor g_cur is select * from salgrade;
  e_sal number;  
begin
  for f in f_cur loop
    for g in g_cur loop
      if f.sal between g.losal and g.hisal and g.grade = 1
      then e_sal := f.sal * 1.05;
      elsif f.sal between g.losal and g.hisal and g.grade = 2
      then e_sal := f.sal * 1.04;   
      elsif f.sal between g.losal and g.hisal and g.grade = 3
      then e_sal := f.sal * 1.03;  
      elsif f.sal between g.losal and g.hisal and g.grade = 4
      then e_sal := f.sal * 1.02;   
      elsif f.sal between g.losal and g.hisal and g.grade = 5
      then e_sal := f.sal * 1.01; 
      end if; 
    end loop;
    dbms_output.put_line(f.ename||' - 前：'||f.sal||'  后：'||e_sal);
    update emp set sal = e_sal  where current of f_cur;
  end loop;
end;

--10.用游标获取所有收入（sal+comm）超过2000的 salesman：

declare
  cursor h_cur is select * from emp where sal+nvl(comm,0)>2000 and job = 'SALESMAN';
begin
  for h in h_cur loop
    dbms_output.put_line(h.empno||' '
        ||h.ename||' '||h.job||' '||h.mgr||
        ' '||to_char(h.hiredate,'yyyy-mm-dd')||' '
        ||h.sal||' '||h.comm||' '||h.deptno);
  end loop;
end;
--11.定义游标：按工号从小到大的顺序输出雇员名字、工资以及工资与所在部门平均工资的差额：

declare
  cursor i_cur is 
  select e.ename,e.sal,e.sal-t.avg c from emp e,
  (select deptno,round(avg(sal),2) avg from emp group by deptno) t 
  where t.deptno = e.deptno order by e.sal;
begin
  for i in i_cur loop
    dbms_output.put_line(i.ename||' '
        ||i.sal||' '||i.c);
  end loop;
end;

--12.定义游标：以提升两个资格最老的‘职员’（CLERK）为‘高级职员’（HIGHCLERK）：（工作时间越长，优先级越高）

declare
  cursor j_cur is 
  select * from emp where job = 'CLERK' order by hiredate
  for update;
begin
  for j in j_cur loop
    if j_cur%rowcount < 3
      then
        update emp set job = 'HIGHCLERK' where current of j_cur;
    end if;
  end loop;
end;

select * from emp;
--13.使用显式游标更新行，删除薪资最低的那个员工：
declare
  cursor k_cur is select * from emp for update;
  min_sal number;
begin
  select min(sal) into min_sal from emp;
  for k in k_cur loop
    if k.sal = min_sal then
       delete from emp where current of k_cur;
    end if;
  end loop;
end;

----
declare
  cursor k_cur is select * from emp order by sal for update;
begin
  for k in k_cur loop
    if k_cur%rowcount = 1 then
       delete from emp where current of k_cur;
    end if;
  end loop;
end;

with soucre as ( 
    select 1 as id , 3 as score from dual
    union all 
    select 2 as id , 4 as score from dual
    union all 
    select 3 as id , null as score from dual
    union all 
    select 4 as id , 3 as score from dual
    union all 
    select 5 as id , null as score from dual
    union all 
    select 6 as id , null as score from dual
    union all 
    select 7 as id , 5 as score from dual) 
select t.id,
nvl(t.score,lag(t.score)over(order by t.id)) score 
from (
select s.id,
nvl(s.score,lag(s.score)over(order by s.id)) score 
from soucre s)t
    
-- 测试数据表创建
with soucre as ( 
    select 1 as id , 3 as score from dual
    union all 
    select 2 as id , 4 as score from dual
    union all 
    select 3 as id , null as score from dual
    union all 
    select 4 as id , 3 as score from dual
    union all 
    select 5 as id , null as score from dual
    union all 
    select 6 as id , null as score from dual
    union all 
    select 7 as id , 5 as score from dual) -- 测试数据表创建
select id,score,nvl(score,lag(score ignore nulls) over(order by id)) a from soucre;

/*create table customer(
cust_id number
,certificate_no char(18));
create table application(
apply_id number
,cust_id number
,amount number);
insert into customer values(1,370284199611045316);
insert into customer values(2,370284198011045316);
insert into customer values(3,370284196511045316);
insert into application values(11,1,700);
insert into application values(12,2,500);
insert into application values(13,3,200);*/
select * from customer;
select* from application;

select nvl(区间,'总计')区间,count(cust_id) 总人数,count(apply_id) 交易笔数,sum(amount)交易总金额 from(
select case when months_between(sysdate,d)/12 between 0 and 30 then '0-30岁' 
            when months_between(sysdate,d)/12 > 30 and months_between(sysdate,d)/12 <= 50 then '30-50岁'
            when months_between(sysdate,d)/12 > 50 then '50岁以上' end 区间,
cust_id,apply_id,amount from
(with t as
(select c.*,a.amount,a.apply_id from customer c,application a 
where a.cust_id = c.cust_id)
select cust_id,apply_id,to_date(substr(t.certificate_no,7,8),'yyyy-MM-dd') d,amount from t)
)group by rollup(区间);

------
with t as (select cust_id,apply_id,amount ,
            case when age between 0 and 30 then '0-30岁' 
            when age > 30 and age <= 50 then '30-50岁'
            when age > 50 then '50岁以上' end 区间
from (select c.cust_id,months_between(sysdate,to_date(substr(certificate_no,7,8),'yyyy-MM-dd'))/12 age,a.amount,a.apply_id 
from customer c,application a 
where a.cust_id = c.cust_id) 
)
select nvl(区间,'总计')区间,count(cust_id) 总人数,count(apply_id) 交易笔数,sum(amount)交易总金额 from t group by rollup(区间);
-----
with ca as
(select cust_id,amount,apply_id,
case when year between 0 and 30 then '0-30岁' 
     when year between 30 and 50 then '30-50岁'
     when year > 50 then '50岁以上'end age
from (select a.*,
to_char(sysdate,'yyyy')-substr(certificate_no,7,4) year
from customer c
join application a on c.cust_id=a.cust_id))
select nvl(age,'总计') 区间,count(cust_id) 总人数,count(*) 交易笔数,sum(amount) 交易总金额 
from ca group by rollup(age);

------------------------------------------------------------------------------------------------------------------

declare
  v_emp emp_bak%rowtype;
begin
  update emp_bak set comm=100 where deptno=&deptno;
  dbms_output.put_line('修改的数据条数：'||sql%rowcount);
  if sql%found then
    dbms_output.put_line('aaaaaaaaaaaaaaa');
  end if;
  delete from emp_bak where deptno=&dno;
  dbms_output.put_line('删除了'||sql%rowcount||'条数据'); 
end;

select * from emp_bak;