急！！在线等！！oracle 存储过程加入对工资求最大值，最小值，平均值，总值

[luyinghao159]

create or replace procedure pro_payroll4(
rnt int)is
f_no char(20);
f_date date;
cursor cur_1 is select emp_no,emp_date from payroll;
begin
open cur_1;
loop
fetch cur_1 into f_no,f_date;
exit when cur_1%notfound;
if to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=5 then
update payroll set emp_salary=emp_salary*1.05 where
emp_no=f_no;
elsif to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=6 then
update payroll set emp_salary=emp_salary*1.06 where

emp_no=f_no;
elsif to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=7 then
update payroll set emp_salary=emp_salary*1.07 where
emp_no=f_no;
end if;
commit;
end loop;
close cur_1;
end;

在这段代码的基础上加入对工资（emp_salary）求最大值，最小值，平均值，总值
大概功能
求大神解答

[luyinghao159]

我改好了 create or replace procedure pro_payroll4( rnt int, min1 out decimal, max1 out decimal, avg1 out decimal, sum1 out decimal)is f_no char(20); f_date date; cursor cur_1 is select emp_no,emp_date from payroll; begin open cur_1; loop fetch cur_1 into f_no,f_date; exit when cur_1%notfound; if to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=5 then update payroll set emp_salary=emp_salary*1.05 where emp_no=f_no; elsif to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=6 then update payroll set emp_salary=emp_salary*1.06 where emp_no=f_no; elsif to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=7 then update payroll set emp_salary=emp_salary*1.07 where emp_no=f_no; end if; commit; end loop; close cur_1; select min(emp_salary) into min1 from payroll; select max(emp_salary) into max1 from payroll; select avg(emp_salary) into avg1 from payroll; select sum(emp_salary) into sum1 from payroll; dbms_output.put_line('最小值:'||min1); dbms_output.put_line('最小值:'||max1); dbms_output.put_line('平均值:'||avg1); dbms_output.put_line('平均值:'||sum1); end; 但是怎么用call调用，请大神回答下

[luyinghao159]

我又改了下，求什么用像上图一样用select来看每个值 create or replace procedure pro_payroll4( rnt int, min1 out decimal, max1 out decimal, avg1 out decimal, sum1 out decimal)is f_no char(20); f_date date; cursor cur_1 is select emp_no,emp_date from payroll; begin open cur_1; loop fetch cur_1 into f_no,f_date; exit when cur_1%notfound; if to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=5 then update payroll set emp_salary=emp_salary*1.05 where emp_no=f_no; elsif to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=6 then update payroll set emp_salary=emp_salary*1.06 where emp_no=f_no; elsif to_char(sysdate,'yyyy') - to_char(f_date,'yyyy')=7 then update payroll set emp_salary=emp_salary*1.07 where emp_no=f_no; end if; commit; end loop; close cur_1; select min(emp_salary) as "工资最小值" into min1 from payroll; select max(emp_salary) as "工资最大值" into max1 from payroll; select avg(emp_salary) as "工资平均值" into avg1 from payroll; select sum(emp_salary) as "工资总值" into sum1 from payroll; end;

[mayanzs]

如果仅仅只是上述更新语句好象不必要用游标，直接一个语句就可以解决了 update payroll set emp_salary=emp_salary*（1+extract(year from (sysdate-emp_date) year to month)/100) where extract(year from (sysdate-emp_date) year to month) between 5 and 7; commit;