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.
Thus, any TCL operation on the cursor record set has to be done only after fetching all the rows from the cursor context area using a loop process similar to the above listing example.
The row limiting clause introduced in the Oracle version 12c, Fetch First ..
Could you please explain to me why someone would want to get an an exclusive lock on the rows that are to be deleted ?
Do we care if someone tries to update them at the same time ?
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.
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.
SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement.
The syntax of this statement is The PL/SQL Challenge question in last issue’s “Wrap Up Your Code in a Neat Package” article focused on how the values assigned to package-level variables persist in your session and asked, “Which of the choices will display ‘3’ after execution?
” In the print version of the article, only choice a was correct.