PLSQL Day5

-- 14.使用显式游标更新行，提升两个资格最老的员工为manager：
declare
  cursor e_cur is select * from emp order by hiredate desc for update;
begin
  for e in e_cur loop
    if e_cur%rowcount<3 then
      update emp set job = 'MANAGER' where empno = e.empno;
    end if;
  end loop;
end;

select * from emp order by hiredate desc
-- 15.使用显式游标更新行，对员工的薪水判断，如果高于所在部门的平均薪水，则薪水减50：
declare 
  cursor e_cur is select * from emp for update;
  avg_sal number;
begin
  for e in e_cur loop
    select avg(sal) into avg_sal from emp where deptno = e.deptno;
    dbms_output.put_line(e.ename||' '||avg_sal);
    if e.sal > avg_sal then
      update emp set sal = nvl(sal,0) - 50 where empno= e.empno;
    end if;
  end loop;
end;
select * from emp;
-- 6.使用隐式游标属性，修改员工号为7654的员工工资，将其工资增加500元。
-- 如果该员工不存在，则向员工表中插入一条员工号为7654的记录（其他列为空）:
begin
  update emp set sal = sal + 500 where empno = 7654;
  if sql%notfound then
    dbms_output.put_line('Not Found!');
    insert into emp(empno) values(7654);
  end if;
end;
select * from emp;

--17.将名字中带有‘S’的员工删除，并使用隐式游标统计删除几人，还剩几人：
declare
  cnt number;
begin
  delete from emp where ename like '%S%';
  dbms_output.put_line('删除人数：'||sql%rowcount);
  select count(*) into cnt from emp;
  dbms_output.put_line('剩余人数：'||cnt);
end;
select * from emp;

--18.要求根据输入的不同表名进行不同处理，若输入的表名为emp，则显示高于10号部门平均工资的员工信息；
--若表名为 dept，则显示各个部门的人数。若输入其他表名，则直接提示‘请输入员工表或部门表！’：

-- REF游标
declare
  tb_name varchar2(50) := '&表名';
  type e_d_cur is ref cursor;
  cur e_d_cur;
  e emp%rowtype;
  type d_type is record(
    dno number,
    cnt number
  );
  d d_type;
begin
  if tb_name = 'emp' then
    open cur for select * from emp where sal > (select avg(sal) from emp where deptno = 10);
    loop 
      fetch cur into e;
      exit when cur%notfound;
      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 loop;
    close cur;
   elsif tb_name = 'dept' then
     open cur for select deptno,count(*) from emp group by deptno ;
     loop
       fetch cur into d;
       exit when cur%notfound;
       dbms_output.put_line(d.dno||'号部门人数: '||d.cnt);
     end loop;
     close cur;
   else
     dbms_output.put_line('请输入员工表或部门表!');
   end if;
end;

---- 显式游标
declare
  cursor e_cur is select * from emp where sal > (select avg(sal) from emp where deptno = 10);
  cursor d_cur is select deptno,count(*) cnt from emp group by deptno;
  tb_name varchar2(50) := '&部门号';
begin
  if tb_name = 'emp' then
    for e in e_cur loop
      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 loop;
  elsif tb_name = 'dept' then
    for d in d_cur loop
      dbms_output.put_line(d.deptno||'号部门人数: '||d.cnt);
    end loop;
  else
    dbms_output.put_line('请输入员工表或部门表!');
  end if;
end;

/*
create table c_staff(
staff_no varchar2(23),
staff_name varchar2(34),
staff_sex varchar2(34),
staff_age number,
come_date date,
group_name varchar2(34),
super_statff_no varchar2(43)
);

insert into c_staff values(34065,'杨志军','男',25,to_date(20130716,'yyyymmdd'),'交付运维部','于波');
insert into c_staff values(43533,'宋非凡','男',18,to_date(20201117,'yyyymmdd'),'交付运维部','杨志军');

create table c_check_record(
staff_no varchar2(12),
ymd varchar2(8),
is_come varchar2(12));

insert into c_check_record values(34065,20230201,'出勤');
insert into c_check_record values(34065,20230202,'出勤');
insert into c_check_record values(34065,20230203,'缺勤');
insert into c_check_record values(34065,20230204,'出勤');
insert into c_check_record values(34065,20230205,'出勤');

create table e_month_chek(
ym varchar2(16),
full_num number(8),
defect_num number(8),
full_rate number(8,2),
come_date number(8,2)
);*/
select * from c_staff;
select * from c_check_record;

-- 1、查询2021年一共入职了几名员工，平均年龄是多少（5分）。
select count(*) 人数,avg(staff_age) 平均年龄 from (select * from c_staff where extract(year from come_date) = '2021');
-- 2、查询于波管理的所有员工清单，包括下级和下下级员工，结果字段包括：员工编号、员工姓名、上级领导编号、上级领导姓名（15分）。
select b.staff_no,b.staff_name,a.staff_no,a.staff_name from
(select staff_no,staff_name from c_staff where staff_name = '于波' ) a
join
(select staff_no,staff_name,super_statff_no from c_staff
start with super_statff_no = '于波'
connect by prior super_statff_no = staff_name) b
on a.staff_name = b.staff_name;
-- 3、统计杨志军在2023年2月的出勤情况，即出勤几天，缺勤几天（10分）。
select sum(decode(is_come,'出勤',1,0)) 出勤,
sum(decode(is_come,'缺勤',1,0)) 缺勤 from
(select cs.staff_no,cs.staff_name,cc.ymd,cc.is_come from c_staff cs,c_check_record cc 
where cs.staff_name = '杨志军' and cs.staff_no = cc.staff_no and substr(cc.ymd,5,2) = '02');

-- 5、统计2023年2月所有人的出勤天数，并按照出勤天数从高到低进行排序，要求字段：员工姓名、所在部门、上级领导姓名、出勤天数（20分）。

select staff_no,staff_name,group_name,mgr_name,sum(decode(is_come,'出勤',1,0)) 出勤天数 from
(select c1.staff_no, c1.staff_name,c1.group_name,c2.staff_name mgr_name,cc.is_come from c_staff c1 left join c_staff c2 on c1.super_statff_no = c2.staff_name
 left join c_check_record cc on c1.staff_no = cc.staff_no where substr(cc.ymd,5,2) = '02')
 group by staff_no,staff_name,group_name,mgr_name order by sum(decode(is_come,'出勤',1,0));

-- 6、编写多个脚本实现以下统计结果：统计每个月全勤的人数、存在缺勤的人数、
-- 全勤率（全勤率=全勤人数/总人数）、环比变化（本月全勤率-上月全勤率）（30分）。
-- 将统计结果插入下表。
select * from e_month_chek
-- 每个人每个月份的出勤情况
insert into e_month_chek
select g.*,g.全勤cnt/e.c 全勤率,nvl((g.全勤cnt/e.c-lag(g.全勤cnt/e.c)over(order by g.month)),0) 环比 from(
with t as
(select cs.staff_no,cs.staff_name,substr(cc.ymd,1,6) month,
nvl(sum(decode(cc.is_come,'出勤',1,0)),0)出勤,
nvl(sum(decode(cc.is_come,'缺勤',1,0)),0)缺勤 
from c_staff cs 
left join c_check_record cc 
on cs.staff_no = cc.staff_no 
group by cs.staff_no,cs.staff_name,substr(cc.ymd,1,6))

-- 不同月份全勤的人数
/*
select staff_no,staff_name,month from t 
where t.缺勤 = 0 and t.出勤 != 0     -- 不同月份全勤的人的名单
*/
--select b.month,nvl(a.全勤cnt,0) 全勤cnt,b.缺勤cnt from
(select month,count(staff_name) 全勤cnt from t 
where t.缺勤 = 0 and t.出勤 != 0
group by month) a full join

-- 不同月份缺勤的人数
-- 不同月份缺勤的人的名单
/*select staff_no,staff_name,month from t 
where t.缺勤 != 0 */
 (select month,count(staff_name) 缺勤cnt from t 
 where t.缺勤 != 0
group by month) b
on a.month = b.month) g,
(select count(*) c from c_staff) e
-- 公司总人数
/*select count(*) from c_staff;*/

----------------------------------------------------QTingLJQ-------------------------------------------------------------------
--insert into e_month_chek
select mon,全勤人数,缺勤人数,全勤人数/n 全勤率,全勤人数/n-nvl(lag(全勤人数/n)over(order by mon),0) 环比变化 from
(select mon,sum(b) 全勤人数,sum(c) 缺勤人数 from 
(select staff_no,mon,a,case when a=m then 1 else 0 end b,
case when a<m then 1 else 0 end c from -- 出勤a<m 说明没有全勤、如果a=m，说明全勤
(select staff_no,mon,sum(decode(is_come,'出勤',1,0)) a,
substr(to_char(last_day(to_date(mon,'yyyy-mm')),'yyyymmdd'),7,2) m -- 找到当前月的最后一天
from 
(select staff_no,substr(ymd,1,6) mon,is_come from c_check_record) -- 截取时间到月
group by staff_no,mon)) 
group by mon),(select count(*) n from c_staff)

select* from e_month_chek