I need to write a procedure that will select records from one table A(dept ,grade) and insert it into another B(dept,grade seq) and if a matching record ie dept is found the column seq in tab B must number the in sequence order,the no of records found;then move on to diff. dept and restart the sequence. eg
Dept Grade seq
Maths Lecturer 1
Maths T.A 2
Econs Lecturer 1
Econs T.A 2
I have managed to code this procedure which does the job but it must accept aparameter. This will delay processing therefore i need to write a procedure without accepting any parameter to run once only so as to do the same job.
Here is the procedure:
“procedure seqA(pdept varchar2)
as
cnt number;
counter number :=0;
begin
declare CURSOR C IS select dept,pgrade from godwin
where dept=pdept;
begin
select count(distinct a.dept) into cnt from godwin a,godwin b where a.dept=pdept;
FOR CURSOR in C loop
if cnt >= 0 then
FOR i in 1 .. cnt loopcounter:=counter+1;
insert into mytable values(cursor.dept,cursor.pgrade,counter);
commit;
end loop;
end if;
end loop;
end;
end;
”
Can u please provide me with the code that will not accept a parameter and yet still do the job as above?
Thank U.