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
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
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.sal2000 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
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 '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 '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;
