Updating incremental values in a plsql cursor

item.object_name updating incremental values in a plsql cursor-55

CREATE table1 AS SELECT object_name, object_type FROM user_objects; BEGIN INSERT INTO table1(col1, col2) VALUES('value1','value2'); UPDATE table1 SET col1 = 'another value' WHERE col2 IS NULL; DELETE FROM table1 WHERE col1 = col2; COMMIT; END; / DROP table1; CREATE table1 AS SELECT object_name, object_type FROM user_objects; DECLARE x VARCHAR2(128) := 'value1'; y NUMBER := 10; BEGIN INSERT INTO table1(col1, col2) VALUES(x, x); UPDATE table1 SET col1 = x WHERE col3 The values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is.

It might be in a different scope (for example, in a sub-block).

When you use expressions rather than explicit column names, use column aliases so that you can refer to the corresponding values inside the loop: clauses, depending on the situation.

Or you might want to process some rows differently than others, and so need more than a simple loop.

DECLARE @saldo_Q_previous FLOAT DECLARE @ID INTEGER DECLARE cursor3 CURSOR FOR SELECT ID FROM @my Table FOR UPDATE OF col2 OPEN cursor3 FETCH NEXT FROM cursor3 INTO @ID FETCH NEXT FROM cursor3 INTO @ID WHILE (@@FETCH_STATUS WITH my Table2 AS ( SELECT col2, col3, ROW_NUMBER() OVER (ORDER BY col2) AS sequence FROM my Table ), new Table AS ( SELECT t1.col2, SUM(t2.col2) - SUM(t2.col3) AS col3 FROM my Table2 t1 LEFT OUTER JOIN my Table2 t2 ON t1.sequence FWIW The major, compelling reason to use a CURSOR is when not doing so, will put too much of a hit on your rdbms.

Comments