Hey Guys
I appreciate if someone help me out.
I have the following sample data:
Sample data
CREATE SEQUENCE file_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER NOCYCLE ;
/
create table test4
(
file_number number,
seq_no number
);
/
insert into test4
select 10,null from dual union all
select 10,null from dual union all
select 10,null from dual union all
select 20,null from dual union all
select 20,null from dual union all
select 30,null from dual
;
/
select file_number
from test4
;
/
Query Output
File_Number
10
10
10
20
20
30
Requirement
What I want is that I want to create a procedure that contain a cursor that loop through all file_number 1 by 1 and assign current sequence on the basis of file_number ,
means assign the same sequence for all repeated file_number, something like below:
Note:- My supervisor don't want me to use any sort of analytical function and he want me to do that programmatically.
File_Number Seq_no
10 100
10 100
10 100
20 101
20 101
30 102
So that flow of the program should be something like below:
--Create cursor
--Get nextval from sequence
--update seq_no column against file_number
--if the file_number is different then get next_val from sequence and then update seq_no again.
I know that its easy to do it in simple sql but want to do it in plsql.