pl/sql procedure language/sql

pl/sql是面向过程的语言

plsql是oracle对sql语言的过程化

比在应用程序中调用访问数据库的效率要高，是学习plsql的目的。

-------------------PL/SQL程序设计方法-------------------

--瀑布模型

/*

1.需求分析

2.设计

  1）概要设计

  2）详细设计

3.编码 (Coding)

4.测试（Testing）

5.上线

SQL语句

变量：

    1.初始值是多少

    2.最终值如何得到

*/

------案例1、统计每年入职的员工人数-----

--SQL语句

select to_char(hiredate,'yyyy') from emp;

--> 游标 --> 循环 --> 退出条件：notfound

/*

变量：1.初始值    2.如何得到

每年入职的员工人数：

count80 number := 0;

count81 number := 0;

count82 number := 0;

count87 number := 0;

*/

set 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; --取出一个员工的入职年份

      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;

/

/*

---案例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) into salTotal from emp;

   2.涨后的工资总额=涨前的工资总额  + sal * 0.1

*/

set serveroutput on;

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

      exit when salTotal >50000; --合计工资大于5w退出循环

      fetch cemp into pempno,psal; --从游标取值赋值到变量

      exit when cemp%notfound; --取不到值退出循环

      if  salTotal + psal * 0.1 <= 50000 then --涨后的工资小于5w才执行涨工资

      update emp set sal = sal * 1.1 where emp.empno = pempno; --执行涨工资

      countEmp := countEmp + 1; --统计张工资的人数

      salTotal := salTotal + psal * 0.1; --涨后的工资总额

      end if;

     end loop;

    close cemp;

    --commit; 提交最终涨工资事务

    dbms_output.put_line('涨工资的人数为：'||countEmp);

    dbms_output.put_line('涨工资后的工资总额为：'||salTotal);

end;

/

/*

案例3、用PL/SQL语言编写程序，实现按部门分段（6000以上、（6000、3000）3000以下）统计各工资段的职工人数、以及各部门的工资总额（工资总额中不包括奖金）

SQL语句

1.有哪些部门

    select deptno from dept; --游标 --> 循环 --> 退出条件：notfound

2.部门中员工的薪水

    select sal from emp where deptno = ? 带一个参数的游标 --> 循环 --> 退出条件：notfound

变量：1.初始值       2.如何得到

每个段的员工人数：

count1 number;

count2 number;

count3 number;

每个部门的工资总额： 有2中方式获取

saltotal number;

1.select sum(sal) into saltotal from emp where deptno = ???

2.累加

*/

--创建一张表保存各工资段数据

create table msg(

       deptno number,

       count1 number,

       count2 number,

       count3 number,

       saltotal number);

SELECT * FROM msg;

--程序第一种实现方式，SQL统计

DECLARE

  CURSOR cdept IS SELECT deptno FROM dept; --定义游标，取出部门

  pdeptno dept.deptno%TYPE; --定义部门变量

  CURSOR cemp(dno NUMBER) IS SELECT sal FROM emp WHERE deptno = pdeptno; --部门中员工的薪水

  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;

/

--程序第二种实现方式，算数累加

DECLARE

  CURSOR cdept IS SELECT deptno FROM dept; --定义游标，取出部门

  pdeptno dept.deptno%TYPE; --定义部门变量

  CURSOR cemp(dno NUMBER) IS SELECT sal FROM emp WHERE deptno = pdeptno; --部门中员工的薪水

  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;

    saltotal := 0;

    --取部门中员工的薪水

    OPEN cemp(pdeptno); --打开员工游标

    LOOP

      FETCH cemp  INTO psal;

      EXIT WHEN cemp%NOTFOUND;

      saltotal := saltotal + psal;

      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;

/

SELECT * FROM msg;

/*

案例3、用plsql语言编写一个程序。按系（系名）分段统计（成绩小于60分，60-85分，85分以上）“大学物理”课程各分数段的学生人数，及各系学生的平均成绩。

1.SQL.语句

       1).得到有哪些系

              select dno,dname from dep;  --> 游标 -- 循环 --> 退出条件：notfound

       2).得到系中，选修了“大学物理”课程学生的成绩

              select grade from sc where cno = (select cno from course where cname = ???)

                 and sno in (select sno from student where dno = ???);

                  --> 带参数的光标 --> 循环 --> 退出条件：notfound

2.变量：1).初始值      2).如何得到

       count1 number;   count2 number;   count3 number;    每个分数段的人数

       avggrade number;        每个系选修了“大学物理”学生平均成绩

                1.算术运算

                2.select avg(grade) into avggrade from sc where cno = (select cno from course where cname=???)

                      and sno in (select  sno from student  where  dno = ???);

*/

--创建一个表存储结果

CREATE TABLE msg1(

       coursename   VARCHAR2(20),

       dnma             VARCHAR2(20),

       count1           NUMBER,

       count2           NUMBER,

       count3           NUMBER,

       avggrade        NUMBER

);

--程序

SET serveroutput ON;

DECLARE

    --定义系的游标

    CURSOR cdept IS select dno,dname from dep;

    pdno dep.dno%TYPE;  

    pdname dep.dname%TYPE;

    --定义成绩游标

    CURSOR cgrade(coursename VARCHAR2, depno NUMBER)

            IS select grade  from sc where cno = (select cno from course where cname=coursename)

                                                    and sno in (select  sno from student  where  dno = depno);

    pgrade sc.grade%TYPE;

    count1 number;   count2 number;   count3 number;    --每个分数段的人数

    avggrade NUMBER;        --每个系选修了“大学物理”学生平均成绩

    pcourseName VARCHAR2(20) := '大学物理';  --课程名称

BEGIN

    OPEN cdept; --打开系的游标

    LOOP

         --取出一个系的信息

         FETCH cdept INTO pdno,pdname;

         EXIT WHEN cdept%NOTFOUND;

         count1 := 0; count2 := 0; count3 := 0;   --初始值

         --系的平均成绩

         select avg(grade) into avggrade from sc where cno = (select cno from course where cname=pcourseName)

                      and sno in (select  sno from student  where  dno = pdno);

         --取出系中，选修了大学物理的学生成绩

         OPEN cgrade (pcourseName,pdno);

         LOOP

              --取出一个学生的成绩

              FETCH cgrade INTO pgrade;

              EXIT WHEN cgrade%NOTFOUND;

              --判断成绩范围

                   IF pgrade < 60 THEN count1 := count1 + 1;

                   ELSIF pgrade >= 60 AND pgrade < 85 THEN count2 := COUNT2 + 1;

                   ELSE count3 := count3 + 1;

                   END IF;

         END LOOP;

         CLOSE cgrade;

         --保存当前的结果

         INSERT INTO msg1 VALUES(pcourseName,pdname,count1,count2,count3,avggrade);

    END LOOP;

    CLOSE cdept;

    --COMMIT;

    dbms_output.put_line('统计完成');

END;

/

SELECT * FROM msg1;

set serveroutput on;

declare

cursor c1 is select dno,dname from dep;

pdno dep.dno%TYPE;

pdname dep.dname%type;

cursor c2(aa varchar2,bb number) is select grade from sc where cno=(select cno from course where cname=aa)

and sno in (select sno from student where dno=bb);

pgrade sc.grade%type;

count1 number;count2 number;count3 number;avgg number;

coursename varchar2(100):='大学物理';

begin

open c1;

loop

fetch c1 into pdno,pdname;

exit when c1%notfound;

count1 :=0;count2 :=0;count3 :=0;

select avg(grade) into avgg from sc where cno=(select cno from course where cname=coursename)

and sno in (select sno from student where dno=pdno);

open c2(coursename,pdno);

loop

fetch c2 into pgrade;

exit when c2%notfound;

if pgrade <60 then count1:=count1+1;

elsif pgrade >60 and pgrade <=80 then count2:=count2+1;

else count3:=count3+1;

end if;

end loop;

close c2;

insert into msg values (coursename,pdname,count1,count2,count3,avgg);

end loop;

close c1;

dbms_output.put_line('统计完成');

end;

/