FrançaisGet started

Procedural blocks

The block of a database procedural language (PL/pgSQL): DECLARE, BEGIN and END sections, variables and types taken from a column, SELECT INTO, IF, loops and output.

  • 33 cards
  • 5 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. Scenarios
  5. Understand

A few cards from the deck

  • Anonymous block: what is it?

    Show the answer

    A piece of procedural code sent to the database and run once, with no name and no return value: it is not stored. In PostgreSQL, it is written with DO.

    To keep the code and call it later, you create a stored procedure or function; the anonymous block is for one-off scripts and experiments.

  • What is total at the end, and so what does the message show?

    ```sql
    DO $$
    DECLARE
    total integer := 0;
    BEGIN
    FOR i IN 1..4 LOOP
    total := total + i;
    END LOOP;
    RAISE NOTICE 'total = %', total;
    END $$;
    `

    • total = 10
    • total = 6
    • total = 15
    • total = 4
    Show the answer

    total = 10

    The loop runs four times with i = 1, 2, 3 then 4, bounds included: 1 + 2 + 3 + 4 = 10. 6 would skip the last pass, 15 would add one.

  • A block is written in this order: … for the variables, … for the statements, then ….

    Show the answer

    A block is written in this order: DECLARE for the variables, BEGIN for the statements, then END.

    DECLARE is optional when there is nothing to declare. An EXCEPTION section, also optional, goes between the statements and END.

  • Declaration section: what is it?

    Show the answer

    The part of the block, introduced by DECLARE, where variables are named and typed before use, with an optional starting value.

    A variable declared without a starting value is NULL, not 0: total integer; followed by total := total + 1 leaves NULL.

  • In a block, the statement SELECT prix FROM produit WHERE id = 3; makes the database answer ERROR: query has no destination for result data. Which fix lets you use the price?

    • End the block with a RETURN followed by the price read
    • Write EXECUTE in front of the SELECT to run it
    • Add INTO v_prix to store the result in a variable
    • Add FROM dual at the end of the query
    Show the answer

    Add INTO v_prix to store the result in a variable

    In a block, a SELECT must say where its result goes: a variable with INTO, or nowhere with PERFORM if the result is ignored. FROM dual is an Oracle habit, absent from PostgreSQL.

Study this deck