Inserting updating records using pl sql cursor
However if you really have to use a cursor to do an update then you should mark it as insensitive: I haven't confirmed this on 2008 but certainly on 2005 and below, you can expect all kinds of weirdness if you update the data your cursor is defined against, without marking the cursor as insensitive. FOR rec IN ( SELECT food_name, food_type, food_qty FROM food_tbl WHERE food_type = 'C') LOOP INSERT INTO candy_tbl(candy_name, candy_type, candy_qty) VALUES(rec.food_name, rec.food_type, rec.food_qty) END LOOP; This will be going into a PL/SQL package.I have highlighted the areas which is differ from compared with a normal cursor.Summary: in this tutorial, you will learn about the PL/SQL record that is a composite data structure, which allows you to manage your data in program more efficiently.
Is it possible to update a column or a row using a cursor?
In these type of situations it is ideal to use a Update Cursor, than using the default read only one.
Consider the following table : Here is how you use the Update Cursor.
SQL DECLARE 2 CURSOR employee_cur IS 3 SELECT * FROM employee 4 FOR UPDATE OF salary; 5 BEGIN 6 FOR employee_rec IN employee_cur 7 LOOP 8 UPDATE employee 9 SET salary = 10000 10 WHERE CURRENT OF employee_cur; 11 END LOOP; 12 END; 13 / PL/SQL procedure successfully completed.
SQL SELECT * FROM employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 10000 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 10000 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 10000 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 10000 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 10000 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 10000 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 10000 New York Manager 08 James Cat 17-SEP-96 15-APR-02 10000 Vancouver Tester 8 rows selected.