PL/SQL執行個體

前言

    無論是寫PL/SQL還是在寫程式的時候，最忌諱的就是不通過構思，直接寫程式。本篇文章主要是認識到寫PL/SQL的思路，以及思考方式是如何的。

思路

模闆如下：

SQL語句

變量：1. 初始值  2.最終得到
           

優化：

1.在選擇方法的時候，當出現累加和操作資料庫2種方式都可以實作，那麼優先選擇累加，避免多次操作資料庫。

執行個體

• 題目：在scott賬号下，統計每年入職的員工個數。

1. SQL語句是什麼？
2. 是否需要光标=》循環》退出條件：notfound
3. 變量：1.初始值 2.最終得到的值

/*
SQL語句
select to_char(hiredate,'yyyy') from emp;
--->光标 --> 循環 --> 退出條件:notfound

變量：1. 初始值  2.最終得到
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
declare 
  --入職年份
  cursor cemp is select to_char(hiredate,'yyyy') 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 = '1980' then count80:=count80+1;
          elsif phiredate = '1981' then count81:=count81+1;
          elsif phiredate = '1982' 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('1980:'||count80);
  dbms_output.put_line('1981:'||count81);
  dbms_output.put_line('1982:'||count82);
  dbms_output.put_line('1987:'||count87);  
end;
           

• 題目：在scott使用者下的em表。為員工漲工資，從最低工資調起每人長10%，但工資總額不能超過5萬元，請計算漲工資的人數和漲工資後的工資總額，并輸出漲工資人數及工資總額。

/*
SQL語句
select empno,sal from emp order by sal
---> 光标 --> 循環  --> 退出條件:1. 總額>5w   2. notfound

變量：1. 初始值  2.最終得到
漲工資的人數: countEmp number := 0;
漲後的工資總額：salTotal number;
1.select sum(sal) into salTotal from emp;
2.漲後=漲前+sal*0.1

練習：漲後的工資總額：50205.325   人數:8
*/
declare 
  cursor cemp is select empno,sal from emp order by sal;
  pempno emp.empno%type;
  psal   emp.sal%type;
  
  --漲工資的人數: 
  countEmp number := 0;
  --漲後的工資總額：
  salTotal number;
begin
  -- 得到初始的工資總額
  select sum(sal) into salTotal from emp;
  
  open cemp;
  loop
       --1. 總額>5w
       exit when salTotal > 50000;
       --取一個員工漲工資
       fetch cemp into pempno,psal;
       --2. notfound
       exit when cemp%notfound;
       
       --漲工資
       update emp set sal=sal*1.1 where empno=pempno;
       --人數+1
       countEmp:= countEmp+ 1;
       
       --2.漲後=漲前+sal*0.1
       salTotal := salTotal + psal*0.1;
  
  end loop;
  close cemp;
  
  commit;
  dbms_output.put_line('漲後的工資總額：'||salTotal||'   人數:'||countEmp);
  
end;
           

• 題目：實作按部門分段（6000以上，（6000，3000），3000以下）統計各工資段的職勞工數，以及各部門的工資總額（工資總額中不包括獎金）。

rem PL/SQL Developer Test Script

set feedback off
set autoprint off

rem Execute PL/SQL Block
/*
SQL語句
部門：select deptno from dept;
查部門中的員工薪水: select sal from emp where deptno=??

變量：1. 初始值  2.最終得到
每個段的人數
count1 number; count2 number; count3 number;
部門的工資總額
salTotal number;
1. select sum(sal) into salTotal  from emp where deptno=??
2. 累加
*/
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
            --取一個員工的薪水
            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 msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));
  
  end loop;
  close cdept;
  
  commit;
  dbms_output.put_line('完成');
  
end;
/