Hello, I just started a new job and I'm new to Oracle and PL/SQL. I have a simple task of multiplying all rows from specific columns from specific table with specific amount.
While I worked this out with a simple cursor and executed it through anonymous block, I can't figure out how to put cursor into procedure and execute it upon calling procedure.
Example 1 (working with simple cursor):
DECLARE
amount integer;
CURSOR c_cursor
IS
SELECT
column1, clumn2, column3, column4
FROM
t
ORDER BY
column1
BEGIN
amount := 2;
FOR r_cursor IN c_cursor
LOOP
UPDATE t
SET column2 = r_cursor.column2 * amount, column3 = r_cursor.column3 * amount, column4 = r_cursor.column4 * amount
WHERE column1 = r_cursor.column1;
END LOOP;
END;
If I put the same code in procedure, it doesn't work. Now my question is why and how to approach this by using a procedure? (doesn't work)
CREATE OR REPLACE PROCEDURE t_multiply AS
amount integer;
CURSOR c_cursor
IS
SELECT
column1, clumn2, column3, column4
FROM
t
ORDER BY
column1
BEGIN
amount := 2;
FOR r_cursor IN c_cursor
LOOP
UPDATE t
SET column2 = r_cursor.column2 * amount, column3 = r_cursor.column3 * amount, column4 = r_cursor.column4 * amount
WHERE column1 = r_cursor.column1;
END LOOP;
END;
/