Question

oracle 在存储过程中加入对加工后的工资从小到大排序，并求和然后把所有结果放入一张表中

表
create table payroll(emp_no number(10) primary key,emp_date date,emp_salary decimal(10,2));
insert into payroll values(102041,to_date('2008-6-5','yyyy-mm-dd'),6000);
insert into payroll values(102042,to_date('2009-3-2','yyyy-mm-dd'),7000);
insert into payroll values(102043,to_date('2008-6-3','yyyy-mm-dd'),5000);
insert into payroll values(102044,to_date('2010-3-5','yyyy-mm-dd'),4000);

存储过程
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;

Answer 1

在close cur_1后面加一句
execute immediate 'create table temp_payroll_'||to_char(trunc(sysdate),'yyyymmdd')||' as
select emp_no,emp_date,emp_salary,sum(emp_salary) over(order by 1) salecount from payroll order by emp_salary asc';