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.Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

plsql updating in cursor-9

Plsql updating in cursor online dating gone bad

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.

We must declare the cursor with the FOR UPDATE clause to use this feature.

statement, but I don't see how that can fit into my current code. I want to allow a calling program to update a row in the cursor (I want to update the race location) returned by the query in my current code.

Here's my code so far: DECLARE l_race_rec race%rowtype; CURSOR Query1 IS SELECT * FROM RACE WHERE Race_Time='22-SEP-14 .000000000'; BEGIN OPEN Query1; LOOP FETCH query1 INTO l_race_rec; EXIT WHEN query1%notfound; dbms_output.put_line( l_race_rec.raceid || ', ' || l_race_rec.race_location || ', ' || l_race_rec.race_type || ', ' || l_race_rec.race_time || ', ' || l_race_|| ', ' || l_race_rec.minage || ', ' || l_race_rec.maxage ); END LOOP; CLOSE Query1; END; DECLARE l_race_rec race%rowtype; CURSOR Query1 IS SELECT * FROM RACE WHERE Race_Time = '22-SEP-14 .000000000'; n Some_value NUMBER := 42; BEGIN OPEN Query1; LOOP FETCH query1 INTO l_race_rec; EXIT WHEN query1%notfound; dbms_output.put_line(l_race_rec.raceid || ', ' || l_race_rec.race_location || ', ' || l_race_rec.race_type || ', ' || l_race_rec.race_time || ', ' || l_race_|| ', ' || l_race_rec.minage || ', ' || l_race_rec.maxage ); UPDATE RACE SET SOME_FIELD = n Some_value WHERE CURRENT OF QUERY1; END LOOP; CLOSE Query1; END; ...

Ben's proposed solution would work fine if stud Id is unique in both stud Load and student tables.

MERGE INTO stud Load l USING ( SELECT stud Id, stud Name FROM student ) s ON (Id = Id) WHEN MATCHED THEN UPDATE SET Name = Name WHERE Name !The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows: WHERE [CURRENT OF cursor_name | search_condition] The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.It declares a type, which you place the data within in bulk, 10,000 rows at a time. However, as I say this will not be as efficient as Name ; type t__data is table of c_data%rowtype index by binary_integer; t_data t__data; begin open c_data; loop fetch c_data bulk collect into t_data limit 10000; exit when t_data.count = 0; for idx in t_data.first ..t_loop if t_data(idx)is null then insert into stud Load (stud Id, stud Name) values (t_data(idx)Id, t_data(idx)Name); else update stud Load set stud Name = t_data(idx)Name where rowid = t_data(idx); end if; end loop; end loop; close c_data; end; / To limit the number of changes (with consequent reduction of Undo space, redo logs and execution time) I suggest to add a condition for new_stud Name !CREATE TABLE employee 2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 ); Table created.