How to get old variable value from PL/SQL for loop?

I am trying to get old value from variable in PL/SQL for loop.

For instance:

I have l_sequnce variable with increment of 10.

for x in (select name 
          from test_names
          order by position)
loop 

 -- print l_sequnce variable

 -- when name is already was in loop then write same value from l_sequence as for first one

end loop;

I hope I described the problem well 🙂

Answer

Use ORDER BY in the SELECT statement and then you only need to check if the value has changed between the previous and current values:

DECLARE
  v_name     TEST_NAMES.NAME%TYPE := NULL;
  l_sequence PLS_INTEGER := 0;
BEGIN
  FOR x IN (select name from test_names ORDER BY name)
  LOOP
    IF    x.name <> v_name
       OR (x.name IS NULL AND v_name IS NOT NULL)
       OR (x.name IS NOT NULL AND v_name IS NULL)
       OR l_sequence = 0
    THEN
      l_sequence := l_sequence + 10;
      v_name := x.name;
    END IF;

    DBMS_OUTPUT.PUT_LINE(l_sequence || ': ' || x.name);
  END LOOP;
END;
/

Or, don’t use PL/SQL and do it all in an SQL statement using the DENSE_RANK analytic function:

SELECT name,
       10 * DENSE_RANK() OVER (ORDER BY name) AS seq_value
FROM   test_names
ORDER BY name; -- or whatever other column you want to order by.

---

If you want to do it entirely in PL/SQL and cannot use ORDER BY then use an associative array:

DECLARE
  TYPE t_name_seq_map IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(30);

  v_names    t_name_seq_map;
  l_sequence PLS_INTEGER := 0;
BEGIN
  FOR x IN (SELECT name FROM test_names ORDER BY position)
  LOOP
    IF NOT v_names.EXISTS(x.name) THEN
      l_sequence := l_sequence + 10;
      v_names(x.name) := l_sequence;
    END IF;

    DBMS_OUTPUT.PUT_LINE(v_names(x.name) || ': ' || x.name);
  END LOOP;
END;
/

or:

BEGIN
  FOR x IN (
    SELECT name,
           DENSE_RANK() OVER (ORDER BY name) AS l_sequence
    FROM   test_names
    ORDER BY position
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE(x.l_sequence || ': ' || x.name);
  END LOOP;
END;
/