Variables, Data Types & Control Flow in PL/SQL

TL;DR

Declare variables with explicit types (VARCHAR2, NUMBER, DATE) or anchored types (%TYPE, %ROWTYPE). Anchored types are safer — if the database column type changes, your code still works.

Use IF/ELSIF/ELSE for conditional logic, CASE expressions for cleaner multi-branch logic, and loops (LOOP, WHILE, FOR) to iterate. EXIT WHEN breaks out of loops.

In Callidus PL/SQL, always anchor variables to CS_ table columns using %TYPE. This protects your code when schema changes happen during patching or upgrades.

Beyond Basic Data Types

In lesson 1, we covered the basic data types: NUMBER, VARCHAR2, DATE, BOOLEAN. Now we go deeper — specifically, the anchoring patterns that make your PL/SQL code resilient to schema changes.

The key insight: if you hardcode v_commission NUMBER(18,2), and the database owner changes CS_RESULTS.RESULT_AMOUNT from NUMBER(18,2) to NUMBER(20,4), your variable definition now doesn't match the column. This can cause silent data loss or rounding errors. Instead, anchor your variables to the column definition with %TYPE. When the column changes, your variable automatically adapts.

%TYPE: Anchoring Variables to Column Types

%TYPE tells PL/SQL to use the data type of a specific column. When you declare a variable with %TYPE, the variable's type is tied to the column's current definition.

DECLARE
  -- BAD: Hardcoded type (brittle, breaks if column type changes)
  v_result_amount NUMBER(18,2);

  -- GOOD: Anchored to column type (adapts automatically)
  v_result_amount  CS_RESULTS.RESULT_AMOUNT%TYPE;
  v_participant_id CS_PARTICIPANT.ID%TYPE;
  v_plan_id        CS_PLAN.PLAN_ID%TYPE;

  -- Anchored variables can be initialised
  v_commission     CS_COMP_SUMMARY.COMMISSION_AMOUNT%TYPE := 0;
  v_calc_date      CS_CALCULATION.CALC_DATE%TYPE := SYSDATE;

BEGIN
  NULL;
END;
/

The syntax is table.column%TYPE. This tells Oracle to use the same data type as that column. If CS_RESULTS.RESULT_AMOUNT is NUMBER(18,2), your variable automatically becomes NUMBER(18,2). If a future patch changes it to NUMBER(20,4), your variable adapts without any code change.

%ROWTYPE: Anchoring Variables to Full Table Rows

%ROWTYPE declares a variable that matches an entire row's structure. It's useful when you're fetching multiple columns from a single table and want to store them together.

DECLARE
  -- %ROWTYPE variable: contains all columns from CS_PARTICIPANT
  rec_participant  CS_PARTICIPANT%ROWTYPE;

  -- You can then access individual fields with dot notation
  -- rec_participant.ID, rec_participant.NAME, rec_participant.STATUS, etc.

BEGIN
  -- Fetch a full row
  SELECT *
  INTO   rec_participant
  FROM   CS_PARTICIPANT
  WHERE  ID = 12345;

  -- Access individual columns via dot notation
  DBMS_OUTPUT.PUT_LINE(
    'Name: ' || rec_participant.NAME ||
    ', Status: ' || rec_participant.STATUS
  );

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Participant not found');
END;
/

%ROWTYPE is powerful because it automatically includes all columns, current and future. If someone adds a new column to CS_PARTICIPANT, your rec_participant variable will automatically include it.

💡Best practice: Always use %TYPE and %ROWTYPE in production code. Never hardcode data types. This pattern, called "anchoring", is the professional standard in enterprise PL/SQL — it makes your code maintainable and resilient to schema evolution.

IF / ELSIF / ELSE: Conditional Logic

Conditional logic lets you execute different code based on whether a condition is true or false.

DECLARE
  v_attainment_pct  NUMBER(5,2);
  v_bonus_tier      VARCHAR2(20);

BEGIN
  v_attainment_pct := 95.5;

  -- Simple IF ... THEN ... END IF
  IF v_attainment_pct >= 100 THEN
    DBMS_OUTPUT.PUT_LINE('Quota achieved');
  END IF;

  -- IF ... ELSIF ... ELSE ... END IF
  IF v_attainment_pct >= 100 THEN
    v_bonus_tier := 'GOLD';
  ELSIF v_attainment_pct >= 80 THEN
    v_bonus_tier := 'SILVER';
  ELSIF v_attainment_pct >= 60 THEN
    v_bonus_tier := 'BRONZE';
  ELSE
    v_bonus_tier := 'NONE';
  END IF;

  DBMS_OUTPUT.PUT_LINE('Bonus Tier: ' || v_bonus_tier);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

Operators: Use = (equals), != or <> (not equals), < (less than), > (greater than), <= (less than or equal), >= (greater than or equal), AND, OR, NOT.

NULL comparisons: Be careful with NULL. The expression v_value = NULL is never true, even if v_value is NULL. Use v_value IS NULL or v_value IS NOT NULL instead.

CASE Expressions: Cleaner Multi-Branch Logic

When you have many branches (many ELSIF clauses), a CASE expression is cleaner and more readable.

DECLARE
  v_plan_type  VARCHAR2(20);
  v_commission NUMBER(5,2);

BEGIN
  v_plan_type := 'FIELD_SALES';

  -- CASE with exact value matching
  v_commission := CASE v_plan_type
    WHEN 'FIELD_SALES'   THEN 8.5
    WHEN 'INSIDE_SALES'  THEN 5.0
    WHEN 'RETAIL'        THEN 3.0
    ELSE 0
  END;

  DBMS_OUTPUT.PUT_LINE('Commission %: ' || v_commission);

  -- CASE with conditions (like if/elsif)
  CASE
    WHEN v_commission >= 8 THEN
      DBMS_OUTPUT.PUT_LINE('Tier: PREMIUM');
    WHEN v_commission >= 5 THEN
      DBMS_OUTPUT.PUT_LINE('Tier: STANDARD');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Tier: BASIC');
  END CASE;

END;
/

CASE is often more readable than nested IF/ELSIF when you have 3+ branches. The syntax is either CASE value WHEN ... THEN ... END (for exact matching) or just CASE WHEN condition THEN ... END (for conditions).

Loops: LOOP, WHILE, and FOR

Loops let you repeat code multiple times. PL/SQL has three loop types.

Basic LOOP

An infinite loop that you must exit explicitly with EXIT or EXIT WHEN.

DECLARE
  v_counter PLS_INTEGER := 1;

BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Processing participant ' || v_counter);

    EXIT WHEN v_counter = 5;  -- Exit when counter reaches 5

    v_counter := v_counter + 1;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Done');

END;
/

The EXIT WHEN condition is checked at the end of each iteration. If true, the loop exits. Without EXIT or EXIT WHEN, the loop runs forever (infinite loop — not what you want).

WHILE LOOP

Loops as long as a condition is true. The condition is checked at the start of each iteration.

DECLARE
  v_counter PLS_INTEGER := 1;
  v_max     PLS_INTEGER := 5;

BEGIN
  WHILE v_counter <= v_max LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Done');

END;
/

FOR LOOP

Loops a fixed number of times. The counter increments automatically. This is the most common loop type in PL/SQL.

BEGIN
  -- FOR loop: counter automatically increments 1 to 5
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration ' || i);
  END LOOP;

  -- FOR loop with custom range (reverse iteration)
  FOR i IN REVERSE 5..1 LOOP
    DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
  END LOOP;

END;
/

In a FOR loop, the counter variable (i) is automatically declared and incremented. You don't need to declare or increment it manually. The syntax is FOR counter IN start..end LOOP. Use REVERSE to count backwards.

Putting It Together: A Callidus Commission Calculation

Here's a realistic example combining variables, anchoring, conditionals, and loops — the kind of code you'll write in production Callidus support.

DECLARE
  -- Anchored variables (safe, production-ready)
  v_participant_id CS_PARTICIPANT.ID%TYPE;
  v_plan_id        CS_PLAN.PLAN_ID%TYPE;
  v_total_earned   CS_RESULTS.RESULT_AMOUNT%TYPE := 0;
  v_quota_pct      NUMBER(5,2);
  v_tier           VARCHAR2(20);
  v_payout         NUMBER(18,2);

BEGIN
  -- Iterate over a few participant IDs (simplified for demo)
  FOR v_participant_id IN 1001..1005 LOOP
    -- Get the plan for this participant
    SELECT PLAN_ID
    INTO   v_plan_id
    FROM   CS_PARTICIPANT
    WHERE  ID = v_participant_id;

    -- Sum their earned results for the period
    SELECT NVL(SUM(RESULT_AMOUNT), 0)
    INTO   v_total_earned
    FROM   CS_RESULTS
    WHERE  PARTICIPANT_ID = v_participant_id
      AND  PERIOD_ID = '2026-Q1';

    -- Calculate quota achievement percentage
    v_quota_pct := 75.0;  -- Simplified (normally queried from CS_QUOTA)

    -- Determine tier based on quota achievement
    CASE
      WHEN v_quota_pct >= 100 THEN
        v_tier := 'GOLD';
        v_payout := v_total_earned * 0.10;  -- 10% commission
      WHEN v_quota_pct >= 80 THEN
        v_tier := 'SILVER';
        v_payout := v_total_earned * 0.075;  -- 7.5% commission
      WHEN v_quota_pct >= 60 THEN
        v_tier := 'BRONZE';
        v_payout := v_total_earned * 0.05;  -- 5% commission
      ELSE
        v_tier := 'NONE';
        v_payout := 0;
    END CASE;

    DBMS_OUTPUT.PUT_LINE(
      'Participant ' || v_participant_id ||
      ' (Plan ' || v_plan_id ||
      '): Earned $' || v_total_earned ||
      ', Tier ' || v_tier ||
      ', Payout $' || ROUND(v_payout, 2)
    );

  END LOOP;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Participant not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

This block demonstrates everything from this lesson:

Anchored variables with %TYPE (safe, production-ready).
FOR loop to iterate over a range.
SELECT ... INTO to fetch data from the database.
CASE expression to calculate the tier based on quota percentage.
String concatenation and DBMS_OUTPUT to report results.
Exception handling for errors.

Comparison: Loop Types

Loop Type	Best Used For	Example
LOOP ... EXIT WHEN	When exit condition is complex or checked mid-loop	Process rows until a condition is met
WHILE	When you need to check a condition before iterating	Read records while status = 'ACTIVE'
FOR	Fixed number of iterations (most common)	Process 100 participants, or a range of IDs

⚠️Infinite loops: If you use LOOP without an EXIT condition, your block will hang. Same with WHILE if the condition never becomes false. Always ensure your loops have a clear exit path. Use PLS_INTEGER (not NUMBER) for loop counters — it's faster for integer arithmetic.

Key Takeaways

Always anchor variables to database columns with %TYPE (or %ROWTYPE for full rows). This protects your code from schema changes.
Use IF/ELSIF/ELSE for conditional logic, or CASE for cleaner multi-branch logic.
Use FOR loops for fixed iterations, WHILE for condition-based iteration, and basic LOOP with EXIT WHEN for complex exit conditions.
Test all code paths: write blocks that exercise the happy path and error cases.
In Callidus PL/SQL, always anchor. It's the professional standard.

Next: Cursors & Row Processing →

Learn cursors — the pattern for iterating over multiple rows and processing them individually.

Lesson 3: Cursors & Row Processing →