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.

Search for inserting updating records using pl sql cursor:

inserting updating records using pl sql cursor-45

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “inserting updating records using pl sql cursor”

  1. One major thing that people usually do during anniversaries is that they send goodwill messages and wishes to the celebrant/celebrants. Even though another year has passed, the love you share continues to last. But a couple like you enjoys all its flavors, whether dull or romantic. Regardless of what it is, I hope your lives overflow with bliss. But for me, it is a real thing which exists between you both.

  2. Melani normally charges 0 for this workshop, but she’s doing it for less than half price and every dime goes directly to help rescue horses saved from the slaughterhouse. Melani Robinson is a multiple award-winning blogger, podcaster, writer and online dating expert.