Question

I have the following tables:

Employees with level (rank or wtv you may call it): income_value,id, etc.
INCOME that stores the rank vs. the income value (very tinny and static)
REGIST_INCOME table with the following columns:
- ID_REG;
- ID_EMPLOYEE;
- MONTH_and_Year_OF_PAYMENT DATE (I want to format to yy.mm);
- DATE_OF_PAYMENT DATE (format yy.mm.dd);
- INCOME; /*which equals the income registered in INCOME */.

I want to create a function that generates a registry/line in a x day of the month.
Something like this: On each 8th of the year, add a line to the table REGIST_INCOME for each employee that is still active (aka still works):

ID_REG | ID_EMPLOYEE  | MONTH_and_Year_OF_PAYMENT | PAYMENT DATE
--------------------------------------------------------------------
1      | 121          | 10.01                     | NULL
2      | 20345        | 10.01                     | NULL

... and so on...

I want something simple, because in Java I want to click on a button that fetches the current date and puts it into PAYMENT DATE. Basically, I want to generate the registrations "automatically" and then confirm just the payment date.

I would appreciate an example with focus on oracle s date convertions and date functions.

Answer 1

I ve created or tried to create a regularly scheduled job like this:

declare
  lv_job number;
  lv_proc varchar2(1000);
  begin_date   DATE := to_date( 10.01.08 );
  begin
  lv_proc:= begin regIncomePerMonth; end; ;
  dbms_job.submit(lv_job,
  lv_proc,
  begin_date,
   begin_date+30 
  );
  dbms_output.put_line( the job created with job number:  || lv_job);
  exception
  when others then
  dbms_output.put_line( error ||sqlcode||sqlerrm);
end;

( see http://oracle.ittoolbox.com/documents/regularly-scheduled-plsql-procedure-14568) but it can t be like this, because it would be always 8Jan+30, and I want to begin in 8Jan and go on in a 30 days step until December.

and my procedure is: create or replace procedure regIncomePerMonth

cursor activeemps is select f.id_pessoa, f.escalao, s.salario_actual from funcionario f , salario s where f.removed != 1 and f.escalao = s.escalao;

begin

for emp in activeemps loop
    INSERT INTO registo_salarial values(S_REGISTO_SALARIAL.nextval,emp.id_pessoa,
    trunc(sysdate, MM ),emp.salario_actual,NULL);
end loop;

end;

友情链接