Which sequence of commands correctly opens the cursor, fetches all rows one by one, and closes it after processing?
hard📝 Application Q15 of 15
PostgreSQL - Advanced PL/pgSQL
You want to process a large table orders row by row using a cursor in a PL/pgSQL function. Which sequence of commands correctly opens the cursor, fetches all rows one by one, and closes it after processing?
ADECLARE cur CURSOR FOR SELECT * FROM orders; OPEN cur; FETCH ALL FROM cur; CLOSE cur;
BOPEN cur; DECLARE cur CURSOR FOR SELECT * FROM orders; FETCH cur INTO rec; CLOSE cur;
CDECLARE cur CURSOR FOR SELECT * FROM orders; OPEN cur; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur;
DDECLARE cur CURSOR FOR SELECT * FROM orders; FETCH cur INTO rec; OPEN cur; CLOSE cur;
Step-by-Step Solution
Solution:
Step 1: Understand correct cursor usage in PL/pgSQL
Declare cursor, open it, then loop fetching rows until no more rows (EXIT WHEN NOT FOUND), then close cursor.
Step 2: Evaluate options
DECLARE cur CURSOR FOR SELECT * FROM orders; OPEN cur; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur; correctly shows DECLARE, OPEN, LOOP with FETCH and EXIT, then CLOSE. Others misuse order or FETCH ALL which fetches all rows at once.
Final Answer:
DECLARE cur CURSOR FOR SELECT * FROM orders; OPEN cur; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; -- process rec END LOOP; CLOSE cur; -> Option C
Quick Check:
Cursor loop with FETCH and EXIT WHEN NOT FOUND [OK]
Quick Trick:Use LOOP with FETCH and EXIT WHEN NOT FOUND to process cursor rows [OK]
Common Mistakes:
Opening cursor after FETCH
Using FETCH ALL instead of looping FETCH
Closing cursor before processing all rows
Master "Advanced PL/pgSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
