Control flow
- SQL course
- Advanced
- Lesson 40/81
- 4 min
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.
Practice Control flow
Write a procedure: IF rate > 0 AND rate < 1 THEN UPDATE ....
Starter code
-- control flow
CREATE PROCEDURE apply_discountSample 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;
$$;Expected output
Procedure oluşturuldu
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add IF
Question
BEGIN
____ rate > 0 AND rate < 1 THEN
UPDATE products SET price = price * (1 - rate);
END IF;
Answer
BEGIN
IF rate > 0 AND rate < 1 THEN
UPDATE products SET price = price * (1 - rate);
END IF;Explanation: An IF … THEN … END IF block runs the update only when the condition holds.
2 Control flow: match the terms
| Term | Meaning |
|---|---|
IF |
condition |
LOOP |
loop |
BEGIN END |
block |
control |
flow |
3 What sets a condition in a procedure?
- CASE
- WHERE
- HAVING
- IF Correct answer
Explanation: IF sets a condition in a procedure.