Plsql updating in cursor

Rated 4.19/5 based on 911 customer reviews

PROCEDURE (dept_id ) AS BEGIN FOR r IN (SELECT * FROM emp WHERE comm IS NULL AND extra IS NOT NULL AND deptno = dept_id) LOOP UPDATE emp SET comm = extra WHERE comm IS NULL AND extra IS NOT NULL AND deptno = dept_id; INSERT INTO changed_comm (deptno, oldval, newval) VALUES (dept_id, r.comm, r.extra); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; CREATE OR REPLACE PROCEDURE pro_cedure( p_dept_id number ) IS TYPE changed_table_type IS TABLE OF changed%ROWTYPE; changed_buff changed_table_type; BEGIN SELECT deptno, comm, extra BULK COLLECT INTO changed_buff FROM emp WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id FOR UPDATE; UPDATE emp SET comm = extra WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id; FORALL i IN 1 ..changed_buff.count INSERT INTO changed VALUES changed_buff( i ); END; / can contain ten thousands and more rows, then this procedure might be slow, becasue it will consume a huge amount of PGA memory.So far in this series we've been concerned with simply reading data from tables using a cursor.In this final part we'll look at how you can use a cursor to modify data as well.

Next, we'll declare our cursor, open it and move it to the first record.In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.To demonstrate how to use a cursor to update records we'll create a running total of the number of Oscars our films have won.To begin with we'll declare a couple of variables and set their default values.

Leave a Reply