Oracle updating cursor

Having said that, I can identify two circumstances in which using a cursor FOR loop would do little harm.First, if you expect to retrieve a relatively small number of rows (in the hundreds at most), a cursor FOR loop’s performance in Oracle Database 10 and above will likely meet your requirements.But there’s a problem with using a cursor FOR loop for a single-row fetch: the resulting code is very misleading. However, from a best practices standpoint, writing code that is transparent in purpose and easy to read and understand is most important.It looks like you expect to retrieve multiple rows, yet you get just one. You should not write code that appears to do one thing while it, in fact, does another.Syntax sql cursor ::= Description of the illustration sql_Keyword and Parameter Description %BULK_ROWCOUNT A composite attribute designed for use with the .The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. If you want to save an attribute value for later use, assign it to a variable immediately.In particular, you will be able to take advantage of Oracle Database 11’s function result cache feature more quickly and smoothly.BULK COLLECT into a varray when you know the upper limit.

END process_employee; The implicit SELECT INTO offers the most-efficient means of returning that single row of information to your PL/SQL program.Using the cursor FOR loop construct is a declarative way of asking Oracle Database to fetch each of the rows specified by your cursor and then execute the loop body for that row.You don’t have to explicitly open the cursor, code the fetch, check to see if the cursor is exhausted, or close the cursor. The problem with using a cursor FOR loop is that either it isn’t the appropriate construct for querying data (as is the case for a single-row fetch) or it isn’t the most efficient approach (BULK COLLECT offers a faster way of querying data).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.

Leave a Reply