Pl sql updating current record in cursor

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. INTG) 8 FOR UPDATE NOWAIT; 9 10 BEGIN 11 12 FOR get_cur_upd IN cur_upd(700) LOOP 13 14 UPDATE SALESREPS 15 SET SALESREPS. QUOTA - 50 16 WHERE CURRENT OF cur_upd; 17 18 END LOOP; 19 20 COMMIT; 21 22 END; 23 / PL/SQL . EMPL_NUM; -- Is not FOR UPDATE -- COMMIT WORK; END LOOP; COMMIT; END; / INTG); 8 9 10 BEGIN 11 12 FOR get_cur_upd IN cur_upd(700) LOOP 13 14 UPDATE SALESREPS 15 SET SALESREPS. QUOTA; 9 10 BEGIN 11 12 FOR get_cur_upd IN cur_upd(700) LOOP 13 14 UPDATE SALESREPS 15 SET SALESREPS. QUOTA + 50 16 WHERE CURRENT OF cur_upd; 17 18 END LOOP; 19 20 COMMIT; 21 22 END; 23 / PL/SQL . EMPL_NUM; 17 18 -- Is not FOR UPDATE -- 19 COMMIT WORK; 20 21 END LOOP; 22 23 COMMIT; 24 25 END; 26 / PL/SQL . MANAGER%TYPE) IS SELECT * FROM SALESREPS WHERE MANAGER = INMG FOR UPDATE; . INTG) 8 FOR UPDATE NOWAIT; 9 10 get_cur_upd cur_upd%ROWTYPE; 11 12 BEGIN 13 14 OPEN cur_upd(700); 15 16 FETCH cur_upd INTO get_cur_upd; 17 18 COMMIT WORK; 19 20 FETCH cur_upd INTO get_cur_upd; 21 22 END; 23 / DECLARE * 1: ORA-01002: ORA-06512: line 20 INTG); BEGIN FOR get_cur_upd IN cur_upd(700) LOOP UPDATE SALESREPS SET SALESREPS. DECLARE 2 CURSOR my Cursor IS 3 SELECT * FROM sec_product FOR UPDATE OF product_description; 4 v_product_description VARCHAR2(20); 5 BEGIN 6 FOR idx IN my Cursor LOOP 7 v_product_description :=UPPER(idx.product_description); 8 UPDATE sec_product 9 SET product_description =v_product_description 10 WHERE CURRENT OF my Cursor; 11 END LOOP; 12 COMMIT; 13 END; 14 / PL/SQL procedure successfully completed.create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.

SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.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.We must declare the cursor with the FOR UPDATE clause to use this feature.Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

WHERE CURRENT OF & FOR UPDATE The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted.

terrem43.ru

71 Comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>