PL/SQL using cursors for rewriting values in table-CodePudding

iam trying to rewrite values in my table with help of cursor but without success. For example in table testik i need to rewrite values 'N' in column alcohol to 'NO'. Can someone give me some advice?

DECLARE
CURSOR kurzor IS SELECT * FROM testik ORDER BY datum DESC;
ttest kurzor%ROWTYPE;

BEGIN
FOR ttest IN kurzor LOOP
    IF (ttest.alcohol = 'N') THEN
            INSERT INTO testik(alcohol) VALUES ('NO');
    END IF;
    END LOOP;
END;
/

When i run script it will give back that procedure successfully completed but without change in table testik.

CodePudding user response：

Very confusing and I can only offer an educated guess. Your explanation sounds as if you would want to update but your code does actually insert.

Maybe what you want to do is actually just:

UPDATE testik SET alcohol = 'NO' WHERE alcohol = 'N';
COMMIT;

PS: No need for PL/SQL for a simple update.

CodePudding user response：

As you're learning cursors and stuff, here's one option you might consider.

Sample table:

SQL> select * from testik;

        ID ALC
---------- ---
         1 Y
         2 N         --> should be updated to NO
         3 YES
         4 NO
         5 N         --> should be updated to NO

Cursor fetches only rows you're interested in; no sense in looping through the whole table if you don't want to do anything with rows whose alcohol column value is different from N.

Note that it is declared for update so that you could utilize update with the where current of clause; it'll update row you've just fetched.

I'm also displaying number of updated rows (you didn't ask for that, but no harm in doing it).

SQL> set serveroutput on;
SQL> declare
  2    cursor c1 is select id, alcohol
  3                 from testik
  4                 where alcohol = 'N'
  5                 order by id
  6                 for update;
  7    c1r   c1%rowtype;
  8    l_cnt number := 0;
  9  begin
 10    open c1;
 11    loop
 12      fetch c1 into c1r;
 13      exit when c1%notfound;
 14
 15      update testik set
 16        alcohol = 'NO'
 17        where current of c1;
 18      l_cnt := l_cnt   1;
 19    end loop;
 20    close c1;
 21    dbms_output.put_line('Updated ' || l_cnt || ' row(s)');
 22  end;
 23  /
Updated 2 row(s)

PL/SQL procedure successfully completed.

Result:

SQL> select * from testik;

        ID ALC
---------- ---
         1 Y
         2 NO
         3 YES
         4 NO
         5 NO

SQL>