CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-update
user5059264
user5059264

Reputation:

foreach rows of my table and update my ROW_NUMBER column

I would like to create a script pl/sql where I can modify the value of my column ROW_NUMBER (the first time the value of ROW_NUMBER equal NULL).

This is the structure of my table 'A' :

CREATE TABLE A
   (
    "NAME" VARCHAR2(25 BYTE), 
    "NUM" NUMBER(10,0)
   )

I would like to foreach all rows of table A and increment my Column 'NUM' by 1 if Column 'NAME' equal 'DEB'.

I would like to get the result like :

enter image description here

I created one pl/sql script :

DECLARE
 INcrmt NUMBER(4):=1;
 line WORK_ODI.TEST_SEQ%ROWTYPE;--before fetch it returns 0
 CURSOR c_select IS
 SELECT ROW_NUMBER,VALUE FROM WORK_ODI.TEST_SEQ;
BEGIN
 OPEN c_select;
  LOOP
   FETCH c_select INTO line;
   DBMS_OUTPUT.PUT_LINE(line.VALUE);
   if line.VALUE like '%DEB%'
   then
    UPDATE WORK_ODI.TEST_SEQ SET ROW_NUMBER = INcrmt WHERE VALUE=line.VALUE;
    INcrmt := INcrmt + 1;
   end if;
   if line.VALUE not like '%DEB%'
    then
    UPDATE WORK_ODI.TEST_SEQ SET ROW_NUMBER = INcrmt WHERE VALUE=line.VALUE;
   end if;
   EXIT WHEN c_select%NOTFOUND;
  END LOOP;
 CLOSE c_select;
 COMMIT;
END;

DECLARE
 INcrmt NUMBER(4):=1;
 line WORK_ODI.TEST_SEQ%ROWTYPE;--before fetch it returns 0
 CURSOR c_select IS
 SELECT ROW_NUMBER,VALUE FROM WORK_ODI.TEST_SEQ;
BEGIN
 OPEN c_select;
  LOOP
   FETCH c_select INTO line;
   DBMS_OUTPUT.PUT_LINE(line.VALUE);
   if line.VALUE like '%DEB%'
   then
    UPDATE WORK_ODI.TEST_SEQ SET ROW_NUMBER = INcrmt WHERE VALUE=line.VALUE;
    INcrmt := INcrmt + 1;
   end if;
   if line.VALUE not like '%DEB%'
    then
    UPDATE WORK_ODI.TEST_SEQ SET ROW_NUMBER = INcrmt WHERE VALUE=line.VALUE;
   end if;
   EXIT WHEN c_select%NOTFOUND;
  END LOOP;
 CLOSE c_select;
 COMMIT;
END;

but this is not work well , please take a look at what it gives me as result :

enter image description here

please anybody can help me

Upvotes: 0

Views: 276

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

First, you should have an Aid column of some sort. In Oracle 12+, you can use an identity. In earlier versions, you can use a sequence. This provides an ordering for the rows in the table, based on insert order.

Second, you can do what you want on output:

select a.*,
       sum(case when a.name like 'DEB%' then 1 else 0 end) over (order by aid) as row_number
from a;

If you really need to keep the values in the table, then you can use a merge statement to assign values to existing rows (the aid column is very handy for this). You will need a trigger afterwards to maintain it.

My suggestion is to do the calculation on the data, rather than storing the value in the data. Maintaining the values with updates and deletes seems like a real pain.

Upvotes: 6

Related Questions