Updating tables within cursor loop
This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code. When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.DECLARE get Majors From Each Dept CURSOR FORSELECT description FROM major WHERE deptid = dept_id; -- Record not found means for inner loop no more major record left for that dept_id and for outer loop no more record left for dept-id DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = ' END';-- Open cursor OPEN get Dept;-- loop to extract each major of each department and insert into messages table-- until no record left.WHILE flag1 ' END' THEN SET concat_description = CONCAT(dept_id,'*',major_description); INSERT INTO messages VALUES (concat_description); END IF; END WHILE; -- free memory CLOSE get Majors From Each Dept; SET flag1 = flag2; END IF; END WHILE;-- Loop end.-- free memory CLOSE get Dept;--- Display all records of Messages Table---------SELECT * FROM messages; END;//DELIMITER ; Purvi Just as a response to that last comment, wouldn't it be easier just to do this?
DECLARE a VARCHAR(255);/* you declare a variable "a" without a specified default value, a=NULL */DECLARE cur1 CURSOR FOR SELECT a FROM table1;/* You declare a cursor that selects "a" FROM a table */OPEN cur1;/* You execute your cursor query, a warning is raised because a is ambiguously defined but you don't see it */FETCH cur1 INTO a;/* you put your unique field in your unique row into a (basically you do "SET a=a;") so a is still NULL */There is no bug report, just a misunderstanding.
Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock.
After a TCL operation is performed, the cursor pointer gets reset and the cursor will be no longer accessible, thus results in an error when fetched further as shown below.
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 ..
Search for updating tables within cursor loop: