Oracle综合练习三

一、统计公司10年来每个人每年月平均工资，排序要求从大到小

要求：存储过程完成；

输入：部门，姓名，每页显示条数，当前显示页码；（部门和姓名无输入，则显示全部）

输出：总记录数，满足条件的记录集；

Emp_sal

序号	姓名 EMPNO	发放年月 SALNY	工资项目 SALITEMNAME	金额 MONEY	部门 DEPTNO
1	张三	2011-3	基本工资	1000	001
2	张三	2011-3	绩效工资	1800	001
3	张三	2011-3	探亲补助	200	001
4	张三	2011-3	交通补助	300	001
5	张三	2011-3	其他补助	400	001
2	李四	2011-4	基本工资	2000	002
3	……

• 首先计算10年来每个人的月平均工资（先按年月汇总，然后AVG求平均）
• 总记录数：步骤一计算的总条目数
• 根据输入当前页以及每页显示记录数得出该页记录是在数据库的哪一段

rownum>= (当前页码 - 1) * 每页条目数+1;

rownum<= 当前页码* 每页条目数;

• 满足条件的记录集：将步骤一的结果带上步骤三的条件过滤

-- Create table

create table EMP_SAL

(

  empno       VARCHAR2(8),

  salny       VARCHAR2(6),

  salitemname VARCHAR2(100),

  money       NUMBER,

  deptno      VARCHAR2(12)

)

--获取10年来每个人每年的月平均工资

select empno, deptno, year, avg(money) avg_mon

          from (select empno,

                       deptno,

                       substr(salny, 0, 4) year,

                       salny,

                       sum(money) money

                  from EMP_SAL

                 where salny >=

                       to_char(add_months(sysdate, -10 * 12), 'yyyymm')

                 group by empno, deptno, salny)

         group by empno, deptno, year;

CREATE OR REPLACE PROCEDURE PROC_QUERYALL(PI_DEPTNO      IN varchar2, --部门名称（号）

                                          PI_EMPNO       IN varchar2, --员工姓名（号）

                                          PI_RECORD      IN INT, --每页显示数

                                          PI_CURRENTPAGE IN INT, --当前显示页码

                                          PO_SUMRECORD   OUT INT, --总记录数

                                          PO_CUR         OUT SYS_REFCURSOR --满足条件结果集

                                          ) AS

  V_sql      varchar2(1000);

  v_sql1     varchar2(2000);

  V_contions varchar2(1000);

  startnum   int;--获取当前页第一条

  endnum     int;--获取当前页最后一条

BEGIN

  If PI_DEPTNO is not null then

    V_contions := 'and deptno =''' || pi_deptno || '''';

  End if;

  If PI_EMPNO is not null then

    V_contions := V_contions || 'and empno =''' || PI_EMPNO || '''';

  End if;

  V_sql := 'select deptno, empno, year,avg(money) money from

           (select deptno, empno,substr(salny, 0, 4) year, salny, sum(money) money

              from emp_sal

             where salny >= to_char(add_months(sysdate, -10 * 12), ''yyyymm'')

             ' || V_contions || '

             group by deptno, empno, salny)

             group by deptno, empno,year

             order by  year desc,money desc';

  DBMS_OUTPUT.PUT_LINE(v_sql);

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || V_sql || ') '

    INTO PO_SUMRECORD;

  DBMS_OUTPUT.PUT_LINE(PO_SUMRECORD);

  startnum := (PI_CURRENTPAGE - 1) * PI_RECORD + 1;

  endnum   := PI_CURRENTPAGE * PI_RECORD;

  v_sql1   := 'select deptno, empno,year, money from (select deptno, empno,year, money,rownum rn from (' ||

              V_sql || ') )where rn>=' || startnum || 'and rn<=' || endnum;

  DBMS_OUTPUT.PUT_LINE(v_sql1);

  open PO_CUR for v_sql1;

END;

二、工资每月发放明细

要求：存储过程完成；

输入：姓名，年月

输出：发放结果（第一行为工资项目，每个人工资项目不同，没有的项目不显示，并在最后加一列"合计"，第二行为“金额”）

工资发放表见第一题

用decode函数实现行转列，与上午讲的类似

输入：张三，201103

结果展示：

姓名  基本工资 绩效工资 探亲补助 交通补助 其他补助 合计

张三   1000     1800     200       300      400    3700

CREATE OR REPLACE PROCEDURE PROC_GETDETAILSAL(

        PI_EMPNO     IN VARCHAR2,

        PI_NY        IN VARCHAR2,

        DETAILRESULT OUT SYS_REFCURSOR)

IS

  VAR_SUMSAL NUMBER;

BEGIN

  OPEN DETAILRESULT FOR

    SELECT EMPNO 员工号,

        SUM(DECODE(s.SALITEMNAME, '基本工资', S.MONEY, 0)) 基本工资,

        SUM(DECODE(s.SALITEMNAME, '绩效工资', S.MONEY, 0)) 绩效工资,

        SUM(DECODE(s.SALITEMNAME, '探亲补助', S.MONEY, 0)) 探亲补助,

        SUM(DECODE(s.SALITEMNAME, '交通补助', S.MONEY, 0)) 交通补助,

        SUM(DECODE(s.SALITEMNAME, '其他补助', S.MONEY, 0)) 其他补助,

        sum(S.MONEY) 总计

      FROM emp_sal S

     WHERE EMPNO = PI_EMPNO

       AND SALNY = PI_NY

     GROUP BY EMPNO;

END PROC_GETDETAILSAL;