Updating incremental values in a plsql cursor wiggle match dating
My mentor told me that when querying data I should always use a cursor FOR loop, even for a single row lookup. It’s great to learn from others, and it’s especially wonderful when the lesson you learn is simple and easy to remember.
CREATE SEQUENCE gokhan.sample_seq START WITH 1000; SELECT last_number FROM user_sequences WHERE sequence_name = 'SAMPLE_SEQ'; LAST_NUMBER ----------- 1000 ALTER SEQUENCE gokhan.sample_seq INCREMENT BY -500; SELECT gokhan.sample_seq.
NEXTVAL FROM dual; ALTER SEQUENCE gokhan.sample_seq INCREMENT BY 1; SELECT last_number FROM user_sequences WHERE sequence_name = 'SAMPLE_SEQ'; LAST_NUMBER ----------- 500 CREATE OR REPLACE PROCEDURE SYS.
If you really really have to use a loop, of course you need a where condition to make sure you are only updating the record you really want to update.
A possible way that always works (even if there is no unique key available) is to use the rowid pseudocolumn: You need to put a constraint on your update statement.
After the SELECT INTO statement completes, Oracle closes the implicit cursor.