--使用顯式游標更新行，對所有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;
?