PL/SQL control structures and iterators

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- IF ... ELSIF ... ELSE sets the delivery fee of the three practice orders from their values (qty x unit_price):
-- 360.00, 420.00 and 900.00. An order not priced yet has the value NULL, fails every comparison and lands in ELSE,
-- so the second version tests IS NULL first. control_model.py prints the same fees. Nothing is stored.
SET SERVEROUTPUT ON

DECLARE
  PROCEDURE show_fee (p_order PLS_INTEGER, p_value NUMBER) IS
    v_fee NUMBER(6, 2);
  BEGIN
    IF p_value < 400 THEN
      v_fee := 49.00;
    ELSIF p_value < 800 THEN                      -- ELSIF: one word, no E before IF
      v_fee := 29.00;
    ELSE
      v_fee := 0;
    END IF;
    DBMS_OUTPUT.PUT_LINE('order ' || p_order || ': value ' || NVL(TO_CHAR(p_value, 'FM9990.00'), 'NULL')
                         || ', fee ' || TO_CHAR(v_fee, 'FM990.00'));
  END show_fee;
BEGIN
  show_fee(1, 360.00);     -- 2 x TEA-250 at 180.00
  show_fee(2, 420.00);     -- 1 x RICE-5K at 420.00
  show_fee(3, 900.00);     -- 5 x TEA-250 at 180.00
  show_fee(4, NULL);       -- an order whose price is not known yet
END;
/
-- Expected output (Conditional Selection Statements: the first TRUE condition wins; no TRUE condition runs ELSE):
--   order 1: value 360.00, fee 49.00
--   order 2: value 420.00, fee 29.00
--   order 3: value 900.00, fee 0.00
--   order 4: value NULL, fee 0.00          <- NULL < 400 is not TRUE, and neither is NULL < 800
--   PL/SQL procedure successfully completed.

DECLARE
  PROCEDURE show_fee (p_order PLS_INTEGER, p_value NUMBER) IS
    v_fee NUMBER(6, 2);
  BEGIN
    IF p_value IS NULL THEN
      v_fee := NULL;                              -- unknown on purpose: the caller must price the order first
    ELSIF p_value < 400 THEN
      v_fee := 49.00;
    ELSIF p_value < 800 THEN
      v_fee := 29.00;
    ELSE
      v_fee := 0;
    END IF;
    DBMS_OUTPUT.PUT_LINE('order ' || p_order || ': fee ' || NVL(TO_CHAR(v_fee, 'FM990.00'), 'not set'));
  END show_fee;
BEGIN
  show_fee(3, 900.00);
  show_fee(4, NULL);
END;
/
-- Expected output:
--   order 3: fee 0.00
--   order 4: fee not set
--   PL/SQL procedure successfully completed.

"""if_branches.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("branches/if_branches.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @branches/if_branches.sql
order 1: value 360.00, fee 49.00
order 2: value 420.00, fee 29.00
order 3: value 900.00, fee 0.00
order 4: value NULL, fee 0.00

PL/SQL procedure successfully completed.

order 3: fee 0.00
order 4: fee not set

PL/SQL procedure successfully completed.

"""The choices and loops of the lesson's first three sections, worked out the way PL/SQL runs them.

1. IF ... ELSIF ... ELSE runs the first branch whose condition is TRUE. A
   comparison with NULL is unknown, never TRUE, so an order whose value is NULL
   skips every test and lands in ELSE (None stands for NULL here).
2. A CASE expression with no matching WHEN and no ELSE gives NULL; a CASE
   statement raises CASE_NOT_FOUND (ORA-06592) instead.
3. The loops of loops.sql: FOR with CONTINUE WHEN and EXIT WHEN, and a WHILE
   loop that rounds to the paisa each year, as ROUND(x, 2) does.
Edit ORDERS, KINDS or the balances and run it again.
"""
from decimal import ROUND_HALF_UP, Decimal

PAISA = Decimal("0.01")
ORDERS = [(1, Decimal("360.00")), (2, Decimal("420.00")), (3, Decimal("900.00")), (4, None)]
KINDS = ["savings", "current", "deposit"]
RATES = {"savings": Decimal("3"), "current": Decimal("0")}
BALANCES = {1: Decimal("2500.00"), 2: Decimal("18000.00"), 3: Decimal("760.25"), 4: Decimal("41250.50")}


def below(value, limit):
    """value < limit with NULL: None (unknown) when value is None."""
    return None if value is None else value < limit


def fee(value):
    if below(value, 400) is True:
        return "IF", Decimal("49.00")
    elif below(value, 800) is True:
        return "ELSIF", Decimal("29.00")
    else:
        return "ELSE", Decimal("0.00")


print("1. Delivery fee by order value")
for order_id, value in ORDERS:
    branch, amount = fee(value)
    shown = "NULL" if value is None else value
    print(f"  order {order_id}: value {shown} -> {branch}, fee {amount}")

print()
print("2. Interest rate by account kind")
for kind in KINDS:
    expression = RATES.get(kind)
    print(f"  {kind}: expression gives [{'' if expression is None else expression}]")
    if kind in RATES:
        print(f"  {kind}: statement assigns {RATES[kind]}")
    else:
        print(f"  {kind}: statement raises CASE_NOT_FOUND (ORA-06592)")

print()
print("3a. FOR id IN 1..4, CONTINUE WHEN balance < 1000, EXIT WHEN total > 50000")
total = Decimal("0")
for account_id in range(1, 5):
    balance = BALANCES[account_id]
    if balance < 1000:
        print(f"  account {account_id}: {balance} skipped by CONTINUE")
        continue
    total += balance
    print(f"  account {account_id}: {balance} added, total {total}")
    if total > 50000:
        print(f"  EXIT after account {account_id}")
        break

balance, years = Decimal("760.25"), 0
while balance < 1000:
    balance = (balance * Decimal("1.03")).quantize(PAISA, rounding=ROUND_HALF_UP)
    years += 1
print()
print(f"3b. WHILE balance < 1000 at 3 % a year: {years} years, ending at {balance}")

1. Delivery fee by order value
  order 1: value 360.00 -> IF, fee 49.00
  order 2: value 420.00 -> ELSIF, fee 29.00
  order 3: value 900.00 -> ELSE, fee 0.00
  order 4: value NULL -> ELSE, fee 0.00

2. Interest rate by account kind
  savings: expression gives [3]
  savings: statement assigns 3
  current: expression gives [0]
  current: statement assigns 0
  deposit: expression gives []
  deposit: statement raises CASE_NOT_FOUND (ORA-06592)

3a. FOR id IN 1..4, CONTINUE WHEN balance < 1000, EXIT WHEN total > 50000
  account 1: 2500.00 added, total 2500.00
  account 2: 18000.00 added, total 20500.00
  account 3: 760.25 skipped by CONTINUE
  account 4: 41250.50 added, total 61750.50
  EXIT after account 4

3b. WHILE balance < 1000 at 3 % a year: 10 years, ending at 1021.71

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- The same rule twice, interest rates by account kind: once as a CASE expression and once as a CASE statement,
-- neither with ELSE. For the kind 'deposit', which no WHEN names, the expression gives NULL and the statement
-- raises CASE_NOT_FOUND (ORA-06592). The rates are made up; control_model.py prints the same lines.
SET SERVEROUTPUT ON

DECLARE
  PROCEDURE show_rate (p_kind VARCHAR2) IS
    v_rate NUMBER(5, 2);
  BEGIN
    v_rate := CASE p_kind                          -- a CASE expression: it has a value
                WHEN 'savings' THEN 3.00
                WHEN 'current' THEN 0
              END;
    DBMS_OUTPUT.PUT_LINE(p_kind || ': expression gives [' || v_rate || ']');
    CASE p_kind                                    -- a CASE statement: it runs statements
      WHEN 'savings' THEN v_rate := 3.00;
      WHEN 'current' THEN v_rate := 0;
    END CASE;
    DBMS_OUTPUT.PUT_LINE(p_kind || ': statement assigns ' || v_rate);
  EXCEPTION
    WHEN CASE_NOT_FOUND THEN
      DBMS_OUTPUT.PUT_LINE(p_kind || ': statement raised CASE_NOT_FOUND, SQLCODE ' || SQLCODE);
  END show_rate;
BEGIN
  show_rate('savings');
  show_rate('current');
  show_rate('deposit');
END;
/
-- Expected output (Expression page: a CASE expression without ELSE returns NULL; CASE Statement page: without ELSE,
-- no match raises CASE_NOT_FOUND):
--   savings: expression gives [3]
--   savings: statement assigns 3
--   current: expression gives [0]
--   current: statement assigns 0
--   deposit: expression gives []
--   deposit: statement raised CASE_NOT_FOUND, SQLCODE -6592
--   PL/SQL procedure successfully completed.

"""case_choice.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("case/case_choice.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @case/case_choice.sql
savings: expression gives [3]
savings: statement assigns 3
current: expression gives [0]
current: statement assigns 0
deposit: expression gives []
deposit: statement raised CASE_NOT_FOUND, SQLCODE -6592

PL/SQL procedure successfully completed.

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Three loops on the practice accounts: a FOR loop that skips small balances with CONTINUE WHEN and stops with
-- EXIT WHEN, a WHILE loop that grows 760.25 at 3 % a year until it reaches 1000.00, and two nested FOR loops that
-- a labelled EXIT leaves together. control_model.py prints the same numbers. Nothing is stored.
SET SERVEROUTPUT ON

DECLARE
  v_balance accounts.balance%TYPE;
  v_total   NUMBER(14, 2) := 0;
BEGIN
  FOR v_id IN 1 .. 4 LOOP                          -- v_id: declared by the loop, PLS_INTEGER, read-only
    SELECT balance INTO v_balance FROM accounts WHERE account_id = v_id;
    CONTINUE WHEN v_balance < 1000;                -- skip the rest of this pass only
    v_total := v_total + v_balance;
    DBMS_OUTPUT.PUT_LINE('account ' || v_id || ' added, total ' || TO_CHAR(v_total, 'FM99999990.00'));
    EXIT WHEN v_total > 50000;                     -- leave the loop for good
  END LOOP;
END;
/
-- Expected output (accounts 1 to 4 hold 2500.00, 18000.00, 760.25 and 41250.50):
--   account 1 added, total 2500.00
--   account 2 added, total 20500.00
--   account 4 added, total 61750.50
--   PL/SQL procedure successfully completed.
-- Account 3 is skipped by CONTINUE WHEN, and EXIT WHEN ends the loop after account 4.

DECLARE
  v_balance NUMBER(14, 2) := 760.25;
  v_years   PLS_INTEGER := 0;
BEGIN
  WHILE v_balance < 1000 LOOP                      -- tested before every pass
    v_balance := ROUND(v_balance * 1.03, 2);
    v_years   := v_years + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_years || ' years, ending at ' || TO_CHAR(v_balance, 'FM99990.00'));
END;
/
-- Expected output: 10 years, ending at 1021.71

BEGIN
  <<rows>>
  FOR r IN 1 .. 3 LOOP
    FOR c IN 1 .. 3 LOOP
      EXIT rows WHEN r * c = 4;                    -- leaves BOTH loops: the label names the outer one
      DBMS_OUTPUT.PUT_LINE(r || ' x ' || c || ' = ' || r * c);
    END LOOP;
  END LOOP rows;
  DBMS_OUTPUT.PUT_LINE('left both loops');
END;
/
-- Expected output:
--   1 x 1 = 1
--   1 x 2 = 2
--   1 x 3 = 3
--   2 x 1 = 2
--   left both loops
-- Writing r := 2; inside the loop would not compile (PLS-00363: the index cannot be an assignment target).

"""loops.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("loops/loops.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @loops/loops.sql
account 1 added, total 2500.00
account 2 added, total 20500.00
account 4 added, total 61750.50

PL/SQL procedure successfully completed.

10 years, ending at 1021.71

PL/SQL procedure successfully completed.

1 x 1 = 1
1 x 2 = 2
1 x 3 = 3
2 x 1 = 2
left both loops

PL/SQL procedure successfully completed.