/*阶段项目案例
第一部分 案例描述
案例目的
学习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 必做
第一部分 案例描述
案例目的
学习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 必做