由于小弟才接触ORACLE 有很多地方不懂 请教各位了 先谢谢了 ^_^
题目如下:
CREATE TABLE salary_details
(
EMP_CODE VARCHAR2(10),
TIME_OFF_DAYS NUMBER,
OVER_TIME_DAYS NUMBER,
SALARY NUMBER
);INSERT INTO salary_details VALUES ('E001', 1, 2, 4000);
INSERT INTO salary_details VALUES ('E002', 2, 0, 3000);
INSERT INTO salary_details VALUES ('E003', 0, 4, 2500);
INSERT INTO salary_details VALUES ('E004', 0.5, 1, 2000);
INSERT INTO salary_details VALUES ('E005', 0, 1.5, 1800);
INSERT INTO salary_details VALUES ('E006', 1.5, 2, 1500);
COMMIT;编写一个程序包,根据员工编号计算员工的月出勤天数和月工资。
1. 日工资 = 工资 / 该月工作日(工作日即周一到周五)
2. 请假一天扣 1 倍的日工资
3. 加班一天加 1.5 倍的日工资
4. 工资精确到整数(四舍五入)
题目如下:
CREATE TABLE salary_details
(
EMP_CODE VARCHAR2(10),
TIME_OFF_DAYS NUMBER,
OVER_TIME_DAYS NUMBER,
SALARY NUMBER
);INSERT INTO salary_details VALUES ('E001', 1, 2, 4000);
INSERT INTO salary_details VALUES ('E002', 2, 0, 3000);
INSERT INTO salary_details VALUES ('E003', 0, 4, 2500);
INSERT INTO salary_details VALUES ('E004', 0.5, 1, 2000);
INSERT INTO salary_details VALUES ('E005', 0, 1.5, 1800);
INSERT INTO salary_details VALUES ('E006', 1.5, 2, 1500);
COMMIT;编写一个程序包,根据员工编号计算员工的月出勤天数和月工资。
1. 日工资 = 工资 / 该月工作日(工作日即周一到周五)
2. 请假一天扣 1 倍的日工资
3. 加班一天加 1.5 倍的日工资
4. 工资精确到整数(四舍五入)
type total_type is record(
my_code salary_details.emp_code%type,
my_offdays salary_details.time_off_days%type,
my_overdays salary_details.over_time_days%type,
my_salary salary_details.salary%type);
my_total total_type;
total number;--最终工资
workday number := 22;--当月的总工作天数
begin
select emp_code, TIME_OFF_DAYS, OVER_TIME_DAYS, SALARY
into my_total
from salary_details
where emp_code = '&no';
total := my_total.my_overdays * (my_total.my_salary / workday) -
my_total.my_offdays * 2 * (my_total.my_salary / workday) +
(workday - my_total.my_offdays - my_total.my_overdays) *
(my_total.my_salary / workday);
dbms_output.put_line(total);
end;
type total_type is record(
my_code salary_details.emp_code%type,
my_offdays salary_details.time_off_days%type,
my_overdays salary_details.over_time_days%type,
my_salary salary_details.salary%type);
my_total total_type;
total number;--最终工资
workday number := 22;--当月的总工作天数
begin
select emp_code, TIME_OFF_DAYS, OVER_TIME_DAYS, SALARY
into my_total
from salary_details
where emp_code = '&no';
total := my_total.my_overdays * (my_total.my_salary / workday) -
my_total.my_offdays * 2 * (my_total.my_salary / workday) +
(workday - my_total.my_offdays - my_total.my_overdays) *
(my_total.my_salary / workday);
dbms_output.put_line('员工该月工资为: '||round(total));
end;