Cursors pl sql updating

” In the print version of the article, only choice a was correct.In the online version (which also matches the quiz offered at PL/SQL Challenge, both a and b are correct.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.Part 12 in a series of articles on understanding and using PL/SQL The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database.An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit.Every Oracle error has a number, but exceptions must be handled by name.

What are the repercussions if we don't use SELECT FOR UPDATE for updating/deleting the data ?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.This clause can also be extended to lock only the intended table in the SELECT query containing more than one table in joins using the FOR UPDATE OF clause.This clause becomes meaningless if we use a single table SELECT clause as the minimum Oracle has provided us with the WHERE CURRENT OF clause for both DELETE and UPDATE statements inside a cursor’s range to make changes to the last fetched row(s) from the cursor with an ease.Also, will the performance of stored procedures without SELECT FOR UPDATEs be better than the ones with simple update/delete statements as there is no additional overhead of locking rows. Oracle forms for example will issue a SELECT for UPDATE on any row you attempt to update or delete from the GUI. Oracle_Username = 'SCOTT' / COMMIT; select (select object_name from user_objects where object_id = lock_id1) obj_name, 2 lock_type, mode_held 3 from dba_locks 4 where session_id = (select sid from v$mystat where rownum=1) 5 / OBJ_NAME LOCK_TYPE MODE_HELD ---------- -------------------------- ---------------------------------------- T1 DML Row-S (SS) That shows the name of the mode -- going to the server concepts manual: we can see that locks the table (due to the SELECT FOR UPDATE just being issued) in Row share mode -- this prevents another session from locking that table in exclusive mode but nothing else.


Leave a Reply