Control flow

Use IF and LOOP inside a procedure.

Control flow

Inside a procedure you write control flow with IF and LOOP.

CREATE PROCEDURE apply_discount(rate DECIMAL)
LANGUAGE SQL
AS $$
BEGIN
  IF rate > 0 AND rate < 1 THEN
    UPDATE products SET price = price * (1 - rate);
  END IF;
END;
$$;

This query applies a discount.

In products, price = price * (1 - rate) is updated.

Control flow — details

IF sets a condition, LOOP a cycle, BEGIN-END a block. The procedure language varies by database.

PostgreSQL uses PL/pgSQL, MySQL its own procedural language and SQL Server T-SQL; the logic is the same but keywords can differ.

Exercise Control flow

Write a procedure: IF rate > 0 AND rate < 1 THEN UPDATE ....

Show example solution

CREATE PROCEDURE apply_discount(rate DECIMAL)
LANGUAGE SQL
AS $$
BEGIN
  IF rate > 0 AND rate < 1 THEN
    UPDATE products SET price = price * (1 - rate);
  END IF;
END;
$$;

Test yourself

Add IF

BEGIN
  ____ rate > 0 AND rate < 1 THEN
    UPDATE products SET price = price * (1 - rate);
  END IF;

Show answer

BEGIN
  IF rate > 0 AND rate < 1 THEN
    UPDATE products SET price = price * (1 - rate);
  END IF;

Control flow: match the terms

IF

condition
LOOP

loop
BEGIN END

block
control

flow
What sets a condition in a procedure?
- CASE
- WHERE
- HAVING
- IF
Show answer

IF — IF sets a condition in a procedure.

Next lesson: Summary - Stored procedure

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies