oracle练习四
2021/7/30 19:37:28
本文主要是介绍oracle练习四，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.表结构描述：
DEPT表中：
DEPT_NO为部门编号，主键
DEPT_NAME为部门名称
EMP表中：
EMP_NO 为员工编号，主键
EMP_NAME 为员工姓名，如“赵钱孙”，“周吴郑”等
DEPTNO为员工的部门编号
SAL 表中：
EMP_NO为员工编
BEGIN_DATE 为该工资开始时间
SALARY 为工资金额数
建表语句：
drop table dept;
drop table emp;
drop table salary;
create table dept(
dept_no number,
dept_name varchar2(20),
constraint pk_dept primary key(dept_no)
)
insert into dept values(1,'研发部');
insert into dept values(2,'销售部');
insert into dept values(3,'维护部');
insert into dept values(4,'技术部');
commit;
select * from dept;
drop sequence seq_emp;
create sequence seq_emp
maxvalue 0
maxvalue 99999
start with 0
increment by 1;
create table emp(
emp_no number,
emp_name varchar2(10),
dept_no number,
constraint pk_emp primary key(emp_no),
foreign key(dept_no) references dept(dept_no)
)
insert into emp values(seq_emp.nextval,'赵一',1);
insert into emp values(seq_emp.nextval,'赵二',1);
insert into emp values(seq_emp.nextval,'赵三',1);
insert into emp values(seq_emp.nextval,'赵四',1);
insert into emp values(seq_emp.nextval,'赵钱孙',1);
insert into emp values(seq_emp.nextval,'张一',2);
insert into emp values(seq_emp.nextval,'张二',2);
insert into emp values(seq_emp.nextval,'张三',2);
insert into emp values(seq_emp.nextval,'张四',2);
insert into emp values(seq_emp.nextval,'张钱孙',2);
insert into emp values(seq_emp.nextval,'李一',3);
insert into emp values(seq_emp.nextval,'李二',3);
insert into emp values(seq_emp.nextval,'李三',3);
insert into emp values(seq_emp.nextval,'李四',3);
insert into emp values(seq_emp.nextval,'李钱孙',3);
insert into emp values(seq_emp.nextval,'王一',4);
insert into emp values(seq_emp.nextval,'王二',4);
insert into emp values(seq_emp.nextval,'王三',4);
insert into emp values(seq_emp.nextval,'王四',4);
insert into emp values(seq_emp.nextval,'王钱孙',4);
commit;
select * from emp;
drop table salary;
create table sal(
emp_no number,
begin_date date,
salary number,
constraint pk_salary primary key(emp_no,begin_date),
foreign key(emp_no) references emp(emp_no)
)
select * from sal;
select to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss') from dual;
insert into sal values(1,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3908);
insert into sal values(1,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3191);
insert into sal values(2,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2969);
insert into sal values(2,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3069);
insert into sal values(3,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3698);
insert into sal values(3,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3555);
insert into sal values(4,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2773);
insert into sal values(4,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3131);
insert into sal values(5,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3255);
insert into sal values(5,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2702);
insert into sal values(6,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3474);
insert into sal values(6,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2267);
insert into sal values(7,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2525);
insert into sal values(7,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3053);
insert into sal values(8,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3078);
insert into sal values(8,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3394);
insert into sal values(9,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2410);
insert into sal values(9,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2818);
insert into sal values(10,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2536);
insert into sal values(10,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2675);
insert into sal values(11,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2213);
insert into sal values(11,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2592);
insert into sal values(12,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3346);
insert into sal values(12,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2317);
insert into sal values(13,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3577);
insert into sal values(13,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3658);
insert into sal values(14,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2079);
insert into sal values(14,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2474);
insert into sal values(15,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2117);
insert into sal values(15,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3727);
insert into sal values(16,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2670);
insert into sal values(16,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3654);
insert into sal values(17,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3336);
insert into sal values(17,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3965);
insert into sal values(18,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3254);
insert into sal values(18,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2973);
insert into sal values(19,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2457);
insert into sal values(19,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3365);
insert into sal values(20,to_date('2008/09/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2883);
insert into sal values(20,to_date('2008/09/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2552);
insert into sal values(1,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2336);
insert into sal values(1,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2812);
insert into sal values(2,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2542);
insert into sal values(2,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2432);
insert into sal values(3,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3787);
insert into sal values(3,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3483);
insert into sal values(4,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2554);
insert into sal values(4,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3476);
insert into sal values(5,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2285);
insert into sal values(5,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2647);
insert into sal values(6,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2666);
insert into sal values(6,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3917);
insert into sal values(7,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2323);
insert into sal values(7,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2378);
insert into sal values(8,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3908);
insert into sal values(8,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2420);
insert into sal values(9,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3468);
insert into sal values(9,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3188);
insert into sal values(10,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2654);
insert into sal values(10,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2852);
insert into sal values(11,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2563);
insert into sal values(11,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3212);
insert into sal values(12,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3225);
insert into sal values(12,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3313);
insert into sal values(13,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2457);
insert into sal values(13,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2177);
insert into sal values(14,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3844);
insert into sal values(14,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2371);
insert into sal values(15,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3140);
insert into sal values(15,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2107);
insert into sal values(16,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3789);
insert into sal values(16,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2989);
insert into sal values(17,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2229);
insert into sal values(17,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3505);
insert into sal values(18,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3176);
insert into sal values(18,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3122);
insert into sal values(19,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2378);
insert into sal values(19,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2542);
insert into sal values(20,to_date('2008/10/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2975);
insert into sal values(20,to_date('2008/10/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3608);
insert into sal values(1,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3378);
insert into sal values(1,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3707);
insert into sal values(2,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3579);
insert into sal values(2,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2653);
insert into sal values(3,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2458);
insert into sal values(3,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3322);
insert into sal values(4,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3518);
insert into sal values(4,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3911);
insert into sal values(5,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3615);
insert into sal values(5,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2333);
insert into sal values(6,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3776);
insert into sal values(6,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2008);
insert into sal values(7,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3678);
insert into sal values(7,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3870);
insert into sal values(8,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3555);
insert into sal values(8,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3184);
insert into sal values(9,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3259);
insert into sal values(9,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2226);
insert into sal values(10,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2325);
insert into sal values(10,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3662);
insert into sal values(11,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2723);
insert into sal values(11,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3095);
insert into sal values(12,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2503);
insert into sal values(12,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2336);
insert into sal values(13,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2907);
insert into sal values(13,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2177);
insert into sal values(14,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3227);
insert into sal values(14,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3442);
insert into sal values(15,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3409);
insert into sal values(15,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2603);
insert into sal values(16,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2340);
insert into sal values(16,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2940);
insert into sal values(17,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3984);
insert into sal values(17,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3000);
insert into sal values(18,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2901);
insert into sal values(18,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3558);
insert into sal values(19,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2833);
insert into sal values(19,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3505);
insert into sal values(20,to_date('2008/11/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2366);
insert into sal values(20,to_date('2008/11/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3654);
insert into sal values(1,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3360);
insert into sal values(1,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2514);
insert into sal values(2,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2869);
insert into sal values(2,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2367);
insert into sal values(3,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2389);
insert into sal values(3,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2578);
insert into sal values(4,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2009);
insert into sal values(4,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3878);
insert into sal values(5,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3739);
insert into sal values(5,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3450);
insert into sal values(6,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2953);
insert into sal values(6,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3030);
insert into sal values(7,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3575);
insert into sal values(7,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2041);
insert into sal values(8,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2750);
insert into sal values(8,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3050);
insert into sal values(9,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2010);
insert into sal values(9,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2779);
insert into sal values(10,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2318);
insert into sal values(10,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3658);
insert into sal values(11,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3062);
insert into sal values(11,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3695);
insert into sal values(12,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2732);
insert into sal values(12,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2447);
insert into sal values(13,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2259);
insert into sal values(13,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3486);
insert into sal values(14,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2170);
insert into sal values(14,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2121);
insert into sal values(15,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3945);
insert into sal values(15,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2700);
insert into sal values(16,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2133);
insert into sal values(16,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2887);
insert into sal values(17,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2629);
insert into sal values(17,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2284);
insert into sal values(18,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2440);
insert into sal values(18,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+3111);
insert into sal values(19,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+2638);
insert into sal values(19,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2730);
insert into sal values(20,to_date('2008/12/01 10:00:00','yyyy/MM/dd hh24:mi:ss'),+3860);
insert into sal values(20,to_date('2008/12/01 11:00:00','yyyy/MM/dd hh24:mi:ss'),+2206);
commit;
对于工资表一个月重复输入的信息，按照最后一次修改作为其工资
如图：把2008/9/1 11：00：00作为其工资
查看2008年九月至十二月的有效工资，按照员工编号升序：
select emp_no,
max(case to_char(begin_date, 'MM')
when '09' then
begin_date
end) as 九月,
max(case to_char(begin_date, 'MM')
when '10' then
begin_date
end) as 十月,
max(case to_char(begin_date, 'MM')
when '11' then
begin_date
end) as 十一月,
max(case to_char(begin_date, 'MM')
when '12' then
begin_date
end) as 十二月
from sal
where to_char(begin_date, 'yyyy')='2008'
group by emp_no
也可以创建视图：
create or replace view r_sal as
select emp_no,
max(case to_char(begin_date, 'MM')
when '08' then
begin_date
end) as 八月,
max(case to_char(begin_date, 'MM')
when '09' then
begin_date
end) as 九月,
max(case to_char(begin_date, 'MM')
when '10' then
begin_date
end) as 十月,
max(case to_char(begin_date, 'MM')
when '11' then
begin_date
end) as 十一月,
max(case to_char(begin_date, 'MM')
when '12' then
begin_date
end) as 十二月
from sal
where to_char(begin_date, 'yyyy')='2008'
group by emp_no
with read only ;
2.表结构如下：
1、员工基本信息管理表 employee，字段分别为
员工编号，工作证号，姓名，性别，年龄，电话，基本工资，所属部门
2、 部门基本信息 depart
部门编号，部门名称，平均基本工资 ，部门工资上限，部门工资下限
3、 员工工资收入表 salary
员工编号，月份，基本工资，奖金，个人所得税
drop sequence seq_employee_work_id;
create sequence seq_employee_work_id
minvalue 0
maxvalue 9999999
start with 0
increment by 1;
drop table employee;
create table depart;
create table employee(
employee_id varchar(20) primary key,
work_id number,
employee_name varchar2(10),
employee_sex varchar2(2) check(employee_sex='男' or employee_sex='女'),
employee_age number check(employee_age>18),
employee_phone varchar2(11),
employee_base_salary number,
depart_id number,
foreign key (depart_id) references depart(depart_id)
);
create table depart(
depart_id number primary key,
depart_name varchar2(10),
depart_avgsalary number,
depart_maxsalary number,
depart_minsalary number
);
create table salary(
employee_id varchar(20),
salary_date date,
employee_base_salary number,
employee_base_bonus number,
salary_pit number,
primary key(employee_id,salary_date)
)
--自动编号，规则为每天第一个号为DP+年月日+ 000001,第二个为DP+年月日+ 000002
create or replace function getemployee_id
return varchar2
is
begin
return 'DP'||to_char(sysdate,'yyyyMMdd')||lpad(to_char(seq_employee_work_id.nextval),7,'0');
end;
--输入参数：收入（即基本工资+奖金），返回：个人所得税和实际工资发放）
create or replace procedure endsalsry(v_salary in number,salary_pit out number,salary_end out number)
is
begin
salary_pit:=0;
salary_end:=0;
if (v_salary-2000) > 100000 then salary_pit:=(v_salary-2000-100000)*0.45+(100000-80000)*0.4+(80000-60000)*0.35+(60000-40000)*0.3+(40000-20000)*0.25+(20000-5000)*0.2+(5000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <100000 and (v_salary-2000)> 80000 then salary_pit:=(v_salary-2000-80000)*0.4+(80000-60000)*0.35+(60000-40000)*0.3+(40000-20000)*0.25+(20000-5000)*0.2+(5000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <80000 and (v_salary-2000)> 60000 then salary_pit:=(v_salary-2000-60000)*0.35+(60000-40000)*0.3+(40000-20000)*0.25+(20000-5000)*0.2+(5000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <60000 and (v_salary-2000)> 40000 then salary_pit:=(v_salary-2000-40000)*0.3+(40000-20000)*0.25+(20000-5000)*0.2+(5000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <40000 and (v_salary-2000)> 20000 then salary_pit:=(v_salary-2000-20000)*0.25+(20000-5000)*0.2+(5000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <20000 and (v_salary-2000)> 5000 then salary_pit:=(v_salary-2000-5000)*0.2+(5000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <5000 and (v_salary-2000)> 2000 then salary_pit:=(v_salary-2000-2000)*0.15+(2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <2000 and (v_salary-2000)> 500 then salary_pit:=(v_salary-2000-500)*0.1+(500)*0.05;
elsif (v_salary-2000) <500 and (v_salary-2000)> 0 then salary_pit:=(v_salary-2000-500)*0.05;
end if;
salary_end :=v_salary-salary_pit;
end;
这篇关于oracle练习四的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议，共探开源教育未来
- 2024-05-13PingCAP 戴涛：构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性：Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准（DoD）与验收条件（AC）究竟有什么不同？
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起，轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持，CodeGeeX的@repo功能效果提升
