FrançaisGet started

Cursors

The explicit cursor and its cycle (declare, open, fetch a row, test for the end, close), the FOR loop over a query, the parameterised cursor, the current row and row-by-row against set-based processing.

  • 32 cards
  • 6 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. The steps
  5. Scenarios
  6. Understand

A few cards from the deck

  • Explicit cursor: what is it?

    Show the answer

    An object the program declares itself for a query that returns several rows, and drives row by row: open, fetch, close.

    An ordinary variable only holds one row. The cursor keeps the position in the result and moves forward on each fetch.

  • The livre table holds two rows, 'Dune' then 'Emma' (id order). What does this block show?

    ```sql
    DO $$
    DECLARE
    c CURSOR FOR SELECT titre FROM livre ORDER BY id;
    v_titre text;
    BEGIN
    OPEN c;
    LOOP
    FETCH c INTO v_titre;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE '%', v_titre;
    END LOOP;
    CLOSE c;
    END $$;
    `

    • Two lines: Dune then Emma
    • Three lines: Dune, Emma then <NULL>
    • Only one line: Dune
    • An error on the third FETCH, for lack of a row
    Show the answer

    Two lines: Dune then Emma

    The first two FETCH calls read a row. The third finds none: FOUND is false, EXIT WHEN NOT FOUND leaves the loop before the RAISE, and no error is raised.

  • An explicit cursor is …, … with OPEN, read with … and closed with CLOSE.

    Show the answer

    An explicit cursor is declared, opened with OPEN, read with FETCH and closed with CLOSE.

    The cursor's query only runs at opening. Forgetting CLOSE keeps the resources until the end of the transaction, and a second OPEN of the same cursor fails.

  • Implicit cursor: what is it?

    Show the answer

    A cursor the engine opens and closes itself for an SQL statement or a FOR loop over a query, without the code declaring it.

    FOR r IN SELECT … LOOP is the short form: it does OPEN, FETCH, the end test and CLOSE in the programmer's place.

  • Walking a result with an explicit cursor: which step comes after "Declare the cursor and its query in DECLARE"?

    • FETCH c INTO: read the next row
    • OPEN c: the query runs
    • EXIT WHEN NOT FOUND: leave if there is no row left
    • Process the row read, then go back to the FETCH
    Show the answer

    OPEN c: the query runs

    The end test comes right after the FETCH, before the processing; the CLOSE comes after the loop is left. The cursor can only be read once opened.

Study this deck