【安博培训笔记】Oracle销售员工资计算系统-L2_阶段项目案例20130917

/*阶段项目案例
第一部分 案例描述
  案例目的
学习Oracle数据库的编程技术，PL-SQL的使用，存储过程、触发器的使用和系统的用户和权限管理，培养学生关于数据库的管理能力和数据库断的编程技术。
  案例难度
  ★★★
  案例覆盖技能点
1、  Oracle数据库系统结构
2、  用户和权限管理
3、  PL-SQL
1)  基本语法
2)  游标
3)  过程、函数
4)  触发器
  推荐案例完成时间
0.5天


第二部分  需求和开发环境
  使用技术和开发环境
Oracle10g
背景
  随着计算机制造水平的不断提高和成本的不断降低，计算机已经深入到千家万户，在我国，大部分的企事业单位和相当一部分家庭都拥有了计算机，这就为计算机的广泛应用奠定了良好的基础。通过计算机对企事业单位的生产经营及业务活动进行管理，已经成为计算机应用的一个重要组成部分。本案例就是一个使用计算机管理销售员工资的系统，是企事业单位微机化管理的一个典型体现。
案例需求
本项目是为一家销售公司设计的，主要完成自动计算销售员工资的功能。计算销售员工资时依据的是根据其职位提前设定好的基本工资、销售员的回款，以及销售员受到的奖励和处罚。项目共分四个模块，分别是回款管理、奖罚管理、工资管理和用户管理。
系统要实现的主要业务活动有三部分，分别是回款管理、奖罚管理和工资管理。回款管理包括回款的登记、统计和查询。当有回款发生时，会计登记这些回款信息，月末进行统计并打印，同时可以随时查询任何一名销售员任何一个月的回款情况。奖罚管理包括奖罚信息的登记、统计和查询。当发生某些奖罚情况时，会计对这些信息进行登记，月末进行统计并打印，同时可以随时查询任何一名销售员任何一个月的奖罚情况。工资管理包括工资的计算、统计和查询。在每个月的月初，根据上个月的回款和奖罚情况，以及提前设定好的基本工资和提成比例，计算每名销售员的工资，计算完成后打印工资条和工资汇总单，同时可以随时查询任何一名销售员任何一个月的工资情况。此外，回款管理和奖罚管理是工资管理的前提，只有平时登记好回款和奖罚信息，才能为工资的计算提供完整且正确的信息。整个业务活动可以用下图表示：
*/
/*表名  spsalary_t_User（用户表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
userid  用户编号  Number  非空  主键（自增）
username  用户名称  VARCHAR2(20)  非空  
userpassword  密码  VARCHAR2(20)  非空  
userpower  权限  Char(1)  非空  


表名  spsalary_t_Salesperson（销售员表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
spid  销售员编号  CHAR(4)  非空  主键
spname  销售员姓名  VARCHAR2(20)  非空  


表名  spsalary_t_Receivedmoney（回款记录表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
recid  回款编号  Number  非空  主键（自增）
recdate  回款日期  DATE  空  
recmoney  回款金额  Number(12,2)  空  
whodeliver  交款人  VARCHAR2(20)  空  
whoreceive  收款人  VARCHAR2(20)  空  
spname  销售员姓名  VARCHAR2(20)  空  
spid  销售员编号  CHAR(4)  空  外键


表名  spsalary_t_Rewardpunish（奖罚记录表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
rewid  奖罚编号  Number  空  主键（自增）
rewdate  奖罚日期  DATE  空  
rewmoney  奖罚金额  Number(12,2)  空  
who  被奖罚人  VARCHAR2(20)  空  
why  奖罚事由  VARCHAR2(100)  空  
spid  销售员编号  CHAR(4)  空  外键


表名  spsalary_t_Job（职位表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
jobid  职位编号  CHAR(4)  非空  主键
jobname  职位名称  VARCHAR2(20)  非空  
basesalary  基本工资  Number(12,2)  空  
scale  提成比例  Number(5,4)  空  


表名  spsalary_t_Salary（工资表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
spid  销售员编号  CHAR(4)  非空  主键、外键
salyear  年  Number  非空  主键
salrmonth  月  Number  非空  主键
basesalary  基本工资  Number(12,2)  空  
prize  提成  Number(12,2)  空  
rewardpunish  奖罚项  Number(12,2)  空  
sumsalary  工资合计  Number(12,2)  空 */ 




第三部分 问题分析和推荐步骤
1.  创建数据库和相应的表
1)  创建数据库
  利用现有的数据库，利用示例用户scott的身份登录。在scott模式下准备新建表（本来应该新建数据库，为新数据库创建用户并为新用户授权，以新的新用户登录来创建数据表）
  利用命令创建数据库比较繁琐，可以不要求使用
2)  创建数据表
  创建用户表、销售员表、回款记录表、奖罚记录表、工资表
-----------------------------------------------------------------------------
create table spsalary_t_User(--（用户表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
    userid Number primary key not null,--  非空  主键（自增）
    username VARCHAR2(20) not null,--  非空  
    userpassword VARCHAR2(20) not null,--  非空  
    userpower Char(1) not null--  非空  
);
---------
create sequence seq_user;
create or replace trigger tri_user
before insert on spsalary_t_User for each row
begin
  select seq_user.nextval
         into :new.userid from dual;
end;
---------
insert into spsalary_t_User(username,Userpassword,Userpower) values 
       ('shihua','shihua','1');
select * from spsalary_t_User;
update spsalary_t_User set Userpassword = 'shihuafu' where userid = 10021;
delete spsalary_t_User where userid = 10023;
-----------------------------------------------------------------------------------
create table spsalary_t_Salesperson(--（销售员表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
    spid CHAR(4) primary key not null,--  非空  主键 销售员编号
    spname VARCHAR2(20) not null--  非空 销售员姓名
);
insert into spsalary_t_Salesperson values ('0001','shihua');
select * from spsalary_t_Salesperson;
update spsalary_t_Salesperson set spname = '付仕华' where spid = '0001';
insert into spsalary_t_Salesperson values ('0002','shihua');
delete spsalary_t_Salesperson where spid = '0002';
-----------------------------------------------------------------------------------
create table spsalary_t_Receivedmoney(--（回款记录表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
    recid Number primary key not null,-- 非空  主键（自增）  回款编号 
    recdate DATE,  --空   回款日期 
    recmoney Number(12,2),--  空   回款金额 
    whodeliver VARCHAR2(20),--  空    交款人 
    whoreceive VARCHAR2(20),--  空   收款人 
    spname VARCHAR2(20),--  空   销售员姓名  
    spid CHAR(4) references spsalary_t_Salesperson(spid) --空  外键 销售员编号 
);
----------------
create sequence seq_receivedmoney;
create or replace trigger tri_receivedmoney
before insert on spsalary_t_Receivedmoney for each row
begin
  select seq_receivedmoney.nextval
         into :new.recid from dual;
end;
----------------
insert into spsalary_t_Receivedmoney(recdate,recmoney,whodeliver,whoreceive,spname,spid) values
       (sysdate,7777,'张大勇','付仕华','shihua','0001');
select * from spsalary_t_Receivedmoney;
update spsalary_t_Receivedmoney set spname = '付仕华' where recid = 1;
delete spsalary_t_Receivedmoney where recid = 2;
-----------------------------------------------------------------------------------
create table spsalary_t_Rewardpunish(--（奖罚记录表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
    rewid Number primary key,-- 空  主键（自增）  奖罚编号 
    rewdate DATE,--  空    奖罚日期 
    rewmoney Number(12,2),--  空    奖罚金额 
    who VARCHAR2(20),--  空    被奖罚人 
    why VARCHAR2(100),--  空    奖罚事由 
    spid CHAR(4) references spsalary_t_Salesperson(spid)--  空  外键  销售员编号 
);
---------------
create sequence seq_rew;
create or replace trigger tri_rew
before insert on spsalary_t_Rewardpunish for each row
begin
  select seq_rew.nextval
         into :new.rewid from dual;
end;
---------------------
insert into spsalary_t_Rewardpunish(rewdate,rewmoney,who,why,spid) values
       (sysdate,7777,'付仕华','各种优秀','0001');
select * from spsalary_t_Rewardpunish;
update spsalary_t_Rewardpunish set why = '相聚离开 都有时候' where rewid = 2;
delete spsalary_t_Rewardpunish where rewid = 4;
--------------------------------------------
create table spsalary_t_Job(--（职位表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
    jobid CHAR(4) primary key not null,--  非空  主键  职位编号 
    jobname VARCHAR2(20) not null,--  非空    职位名称 
    basesalary Number(12,2),--  空    基本工资 
    scale Number(5,4)--  空    提成比例 
);
--------------------------------------------
create table spsalary_t_Salary(--（工资表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
    spid CHAR(4) not null references spsalary_t_Salesperson(spid),--非空  主键、外键  销售员编号 
    salyear Number not null,--  非空  主键  年 
    salrmonth Number not null,--  非空  主键  月 
    basesalary Number(12,2),--  空    基本工资 
    prize Number(12,2),--  空    提成 
    rewardpunish Number(12,2),--  空    奖罚项 
    sumsalary Number(12,2),--  空   工资合计 
    primary key  (spid,salyear,salrmonth)
);
---------
insert into spsalary_t_Salary(spid,salyear,salrmonth,basesalary,prize,rewardpunish) values
       ('0001',2017,7,7777,7777,7777);
select * from spsalary_t_Salary;
update spsalary_t_Salary set sumsalary = 77777 where spid=0001 and salyear=2017 and salrmonth=7;
insert into spsalary_t_Salary(spid,salyear,salrmonth,basesalary,prize,rewardpunish) values
       ('0001',2017,8,7777,7777,7777);
delete spsalary_t_Salary where spid=0001 and salyear=2017 and salrmonth=8;
--------------------------------------------
/*3)  创建表的约束
  五个表各创建自己的主键，尤其是工资表的联合主键
  每个表的外键
2.. 增加数据表的数据
1)  增加用户表的数据
2)  增加销售员表数据
3)  增加回款记录表的数据
4)  增加奖罚记录表数据
5)  增加每个员工的基本工资的信息
6)  对增加的数据就行修改、删除已保证数据的正确性*/
3．存储过程和触发器
1)  为回款表添加一个行级触发器实现添加汇款时，自动计算提成，自动将提成记入工资表
create or replace trigger tri_rec_prize
after insert on SPSALARY_T_RECEIVEDMONEY for each row
begin
  insert into SPSALARY_T_SALARY (spid,salyear,salmonth,prize) 
         values (:new.spid,to_number(to_char(:new.recdate,'yyyy')),to_number(substr(to_char(:new.recdate,'mm'),2,1)),:new.recmoney);
end;  
2)  为奖罚记录表添加一个触发器：实现当添加奖罚记录时，自动修改工资表的奖罚项
create or replace trigger tri_punish_salary
after insert on SPSALARY_T_REWARDPUNISH for each row
begin
  update SPSALARY_T_SALARY set rewardpunish = :new.rewmoney
  where spid = :new.spid and to_char(salyear) = to_char(:new.rewdate,'yyyy') and to_char(salmonth) = substr(to_char(:new.rewdate,'mm'),2,1);
end;


3)  添加一个存储过程：实现计算每个销售员的工资总额（工资总额=基本工资+提成+奖罚项）
create or replace procedure pro_sumsalary
is
  cursor spsalary_t_salary_cur is select distinct spid from SPSALARY_T_SALARY;
  s_spid SPSALARY_T_SALARY.spid%type;
  s_sumsalary SPSALARY_T_SALARY.sumsalary%type;
begin
  dbms_output.put_line('雇员编号'||'    '||'工资总额');
  for spsalary in spsalary_t_salary_cur loop
    select spid,basesalary+prize+rewardpunish into s_spid,s_sumsalary
    from SPSALARY_T_SALARY where spid = spsalary.spid;
    dbms_output.put_line(s_spid||'        '||s_sumsalary);
  end loop;
end;


4．数据的查询
1)  查询每个销售员的回款明细和总和
select spid,sum(recmoney) from SPSALARY_T_RECEIVEDMONEY 
    group by spid;
2)  查询某个时段每个销售员的回款总和
select spid,sum(recmoney) from SPSALARY_T_RECEIVEDMONEY where recdate between '11-9月-2013' and '14-9月-2013' 
  group by spid;
3)  查询每个销售员的奖罚总和
select  spid,sum(rewmoney) from SPSALARY_T_REWARDPUNISH 
  group by spid;
4)  查询每个销售员的的工资总和
select  sts.spid,sts.sbp+nvl(strew.srew,0)+nvl(strec.recmoney,0) as 工资总额
        from (select spid,sum(rewmoney) srew from SPSALARY_T_REWARDPUNISH group by spid) strew,
        (select spid,sum(basesalary+prize) sbp from SPSALARY_T_SALARY group by spid) sts,
        SPSALARY_T_RECEIVEDMONEY strec
        where sts.spid = strew.spid(+) and strec.spid(+) = sts.spid
        order by sts.spid;
5)  查询每个销售员每个月的回款总额
Case。。。when。。或decode函数  实现交叉表查询的结果样式
select str.spid,sum(str.recmoney),str.datesub
    from (select spid,recmoney,substr(recdate,4,6) datesub
    from SPSALARY_T_RECEIVEDMONEY) str
    group by str.spid,str.datesub
    order by str.spid;
select spid,substr(recdate,4,6),decode(substr(recdate,4,6),'9月-13',sum(recmoney),'8月-12',sum(recmoney),'9月-03',sum(recmoney),sum(recmoney)) "回款总额"
    from 
    SPSALARY_T_RECEIVEDMONEY
    group by spid,substr(recdate,4,6)
    order by spid;


5．创建视图、序列
1) 将以上查询里的5个查询分别建立视图
create view  strec_view as
    select spid,sum(recmoney) summon
    from SPSALARY_T_RECEIVEDMONEY 
    group by spid;
create view receivedmoney as
    select spid,sum(recmoney) summon
    from SPSALARY_T_RECEIVEDMONEY where recdate between '11-9月-2013' and '14-9月-2013' 
    group by spid;
create view  rewardpunish_view as 
    select  spid,sum(rewmoney) summon
    from SPSALARY_T_REWARDPUNISH 
    group by spid;
create view sumsalary_view as 
    select  sts.spid,sts.sbp+nvl(strew.srew,0)+nvl(strec.recmoney,0) as 工资总额
    from (select spid,sum(rewmoney) srew from SPSALARY_T_REWARDPUNISH group by spid) strew,
    (select spid,sum(basesalary+prize) sbp from SPSALARY_T_SALARY group by spid) sts,
    SPSALARY_T_RECEIVEDMONEY strec
    where sts.spid = strew.spid(+) and strec.spid(+) = sts.spid
    order by sts.spid;


2)  创建三个序列以及相应的触发器
create sequence spsalary_t_user_seq
    start with 10001
      increment by 1
      maxvalue 999999999
      nocycle
    cache 10;
    create or replace trigger spsalary_t_user_seq_tri
    before insert or update on spsalary_t_user for each row
    begin
      select spsalary_t_user_seq.nextval into :new.userid from dual;
    end;
create sequence spsalary_t_receivedmoney_seq
    start with 10001
    increment by 1
    maxvalue 999999999
    nocycle
    cache 10;
    create or replace trigger strec_seq_tri
    before insert or update on spsalary_t_receivedmoney for each row
    begin
      select spsalary_t_receivedmoney_seq.nextval into :new.recid from dual;
    end;
create sequence spsalary_t_rewardpunish_seq
    start with 10001
    increment by 1
    maxvalue 999999999
    nocycle
    cache 10;
    create or replace trigger  strew_seq_tri
    before insert or update on spsalary_t_rewardpunish for each row
    begin
      select spsalary_t_rewardpunish_seq.nextval into :new.rewid from dual;
    end;


第四部分  考核评价点
序号  功能列表  功能描述  分数  说明
1  正确设计数据库/数据表，添加约束  根据题目要求正确创建数据库、数据库，并正确添加所有约束  15  必做
2  考核数据表的添加  正确对五个表的数据进行添加、修改和删除功能  20  必做
3  考核触发器和存储过程  正确实现2个触发器和1个存储过程  20  必做
4  考核数据查询  正确完成所有的数据查询  25  必做
5  考核视图、序列  正确对考核项表中的数据进行添加功能  10  必做
6  数据库命名规范  数据库中的表名，列名，都要符合规范要求，不能任意命名  10  必做