当前位置：Gxlcms > 数据库问题 > PLSQL语句编写步骤

PLSQL语句编写步骤

时间：2021-07-01 10:21:17 帮助过：21人阅读

serveroutput on 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; --退出条件:not found 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 loop; close cemp; dbms_output.put_line(‘Total:‘||(count80+count81+count82+count87)) dbms_output.put_line(‘count81:‘||count81) dbms_output.put_line(‘count82:‘||count82) dbms_output.put_line(‘count87:‘||count87) end; /

实例2:为员工涨工资

　　　　从最低工资调起每人涨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) from salTotal from emp;

　　　　　　　　2.涨后=涨前*0.1

<span style="color: #0000ff">set</span> serveroutput <span style="color: #0000ff">on</span>
<span style="color: #0000ff">declare</span>
    <span style="color: #0000ff">cursor</span> cemp <span style="color: #0000ff">is</span> <span style="color: #0000ff">select</span> empno,sal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> sal;
    pempno  emp.empno</span><span style="color: #808080">%</span><span style="color: #000000">type;
    psal  emp.sal</span><span style="color: #808080">%</span><span style="color: #000000">type;
    </span><span style="color: #008080">--</span><span style="color: #008080">涨工资的人数：</span>
    countEmp <span style="color: #0000ff">number</span> :<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">;
    </span><span style="color: #008080">--</span><span style="color: #008080"> 涨后的工资总额：</span>
    salTotal <span style="color: #0000ff">number</span><span style="color: #000000">;
</span><span style="color: #0000ff">begin</span>
    <span style="color: #008080">--</span><span style="color: #008080">得到初始的工资总额</span>
    <span style="color: #0000ff">select</span> <span style="color: #ff00ff">sum</span>(sal) <span style="color: #0000ff">into</span> salTotal <span style="color: #0000ff">from</span><span style="color: #000000"> emp;
    </span><span style="color: #0000ff">open</span><span style="color: #000000"> cemp;
    loop
        </span><span style="color: #008080">--</span><span style="color: #008080">1.总额>5w</span>
        <span style="color: #0000ff">exit</span>  <span style="color: #0000ff">when</span> salTotal<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">50000</span><span style="color: #000000">;
        </span><span style="color: #008080">--</span><span style="color: #008080">取一个员工</span>
        <span style="color: #0000ff">fetch</span> cemp <span style="color: #0000ff">into</span><span style="color: #000000"> pempno,psal;
        </span><span style="color: #008080">--</span><span style="color: #008080">2.notfound</span>
        <span style="color: #0000ff">exit</span> <span style="color: #0000ff">when</span> cemp<span style="color: #808080">%</span><span style="color: #000000">notfound
         
       </span><span style="background-color: #ff0000"><span style="color: #008080">--</span><span style="color: #008080">涨工资(涨工资之前进行判断一下,如果多于50000就不涨)</span></span>
       <span style="color: #0000ff">update</span> emp <span style="color: #0000ff">set</span> sal <span style="color: #808080">=</span>sal<span style="color: #808080">*</span><span style="color: #800000; font-weight: bold">1.1</span>  <span style="color: #0000ff">where</span> empno <span style="color: #808080">=</span><span style="color: #000000">pempno;
       </span><span style="color: #008080">--</span><span style="color: #008080">人数+1</span>
       countEmp :<span style="color: #808080">=</span>countEmp <span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
       </span><span style="color: #008080">--</span><span style="color: #008080">2.涨后=涨前+sal*0.1(当没有必要进行操作数据库时就不进行操作)</span>
       salTotal :<span style="color: #808080">=</span>salTotal<span style="color: #808080">+</span>psal<span style="color: #808080">*</span><span style="color: #800000; font-weight: bold">0.1</span>
    <span style="color: #0000ff">end</span><span style="color: #000000"> loop;
    </span><span style="color: #0000ff">close</span><span style="color: #000000"> cemp;
    
    </span><span style="color: #0000ff">commit</span><span style="color: #000000">;
    dbms_output.put_line(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">人数:</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span>countEmp<span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">总金额:</span><span style="color: #ff0000">‘</span><span style="color: #000000">salTotal)
</span><span style="color: #0000ff">end</span><span style="color: #000000">;
</span><span style="color: #808080">/</span>

　　实例3：实现按部门分段（6000以上，（6000，3000），3000以下）统计各工资段的职工人数，以及各部门的工资总额

　　SQL语句：

　部门:select deptno from dept;

部门中员工的薪水：select sal from emp where deptno =???

　　变量：1.初始值 2.最终得到

　　每个段的人数：

　　count1 number; count2 number ;count3 number

　　部门的工资总额： salTatal number :=0

　　1,select sum(sal) into salTotal from emp where deptno=???

　　2.将部门当中所有员工的薪水都加起来　　　

<span style="color: #0000ff">set</span> serveroutput <span style="color: #0000ff">on</span>
<span style="color: #0000ff">declare</span>
    <span style="color: #008080">--</span><span style="color: #008080">部门(定义光标就要定义变量)</span>
    <span style="color: #0000ff">cursor</span> cdept <span style="color: #0000ff">is</span> <span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span><span style="color: #000000"> dept;
    pdeptno dept.deptno</span><span style="color: #808080">%</span><span style="color: #000000">type;
    </span><span style="color: #008080">--</span><span style="color: #008080">部门中员工的薪水</span>
    <span style="color: #0000ff">cursor</span> cemp(dno <span style="color: #0000ff">number</span>) <span style="color: #0000ff">is</span> <span style="color: #0000ff">select</span> sal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> deptno <span style="color: #808080">=</span><span style="color: #000000">dno;
    psal emp.sal</span><span style="color: #808080">%</span><span style="color: #000000">type;
    </span><span style="color: #008080">--</span><span style="color: #008080">每个段的人数：</span>
　　count1 <span style="color: #0000ff">number</span>; count2 <span style="color: #0000ff">number</span> ;count3 <span style="color: #0000ff">number</span>
　 <span style="color: #008080">--</span><span style="color: #008080">部门的工资总额： </span>
    salTatal <span style="color: #0000ff">number</span> :<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>
<span style="color: #0000ff">begin</span>
    <span style="color: #0000ff">open</span><span style="color: #000000"> cdept;
    loop
        </span><span style="color: #008080">--</span><span style="color: #008080">取一个部门(取完就应该想到退出条件)</span>
        <span style="color: #0000ff">fetch</span> cdept <span style="color: #0000ff">into</span><span style="color: #000000"> pdeptno;    
        </span><span style="color: #0000ff">exit</span> <span style="color: #0000ff">when</span> cdept<span style="color: #808080">%</span><span style="color: #000000">notfound;
        
        </span><span style="color: #008080">--</span><span style="color: #008080">初始化</span>
        count1:<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>;count2:<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>;count3:<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">;
        </span><span style="color: #008080">--</span><span style="color: #008080">部门的工资总额</span>
        <span style="color: #0000ff">select</span> <span style="color: #ff00ff">sum</span>(sal) <span style="color: #0000ff">into</span> salTotal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> deptno <span style="color: #808080">=</span><span style="color: #000000"> pdeptno
        </span><span style="color: #008080">--</span><span style="color: #008080">取部门中员工的薪水</span>
        <span style="color: #0000ff">open</span><span style="color: #000000"> cemp(pdeptno);
        loop         
            </span><span style="color: #0000ff">fetch</span> cemp <span style="color: #0000ff">into</span><span style="color: #000000"> psal;
            </span><span style="color: #0000ff">exit</span> <span style="color: #0000ff">when</span>  cemp<span style="color: #808080">%</span><span style="color: #000000">notfound;
            </span><span style="color: #008080">--</span><span style="color: #008080">判断</span>
            <span style="color: #0000ff">if</span> psal<span style="color: #808080"><</span><span style="color: #800000; font-weight: bold">3000</span> <span style="color: #0000ff">then</span> count1:<span style="color: #808080">=</span>count1<span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
              elsif psal</span><span style="color: #808080">>=</span><span style="color: #800000; font-weight: bold">3000</span>  <span style="color: #808080">and</span> psal<span style="color: #808080"><</span><span style="color: #800000; font-weight: bold">6000</span> <span style="color: #0000ff">then</span> count2:<span style="color: #808080">=</span>count2<span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span>
               <span style="color: #0000ff">else</span> count3:<span style="color: #808080">=</span>count3<span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span>
            <span style="color: #0000ff">end</span> <span style="color: #0000ff">if</span><span style="color: #000000">;
        </span><span style="color: #0000ff">end</span><span style="color: #000000"> loop;
        </span><span style="color: #0000ff">close</span><span style="color: #000000"> cemp;
        </span><span style="color: #008080">--</span><span style="color: #008080">保存结果</span>
        <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> msg <span style="color: #0000ff">values</span>(pdeptno,count1,count2,count3,<span style="background-color: #00ff00">nvl(saltotal,<span style="color: #800000; font-weight: bold">0</span></span><span style="color: #000000"><span style="background-color: #00ff00">)</span>)
    </span><span style="color: #0000ff">end</span><span style="color: #000000"> loop;
    </span><span style="color: #0000ff">close</span><span style="color: #000000"> cdept;
    </span><span style="color: #0000ff">commit</span><span style="color: #000000">;
    dbms_output.put_line(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">完成</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
</span><span style="color: #0000ff">end</span><span style="color: #000000">;
</span><span style="color: #808080">/</span>

PLSQL语句编写步骤

标签：server 语句 loop put round div style salt varchar

PLSQL语句编写步骤

人气教程排行