Stored Procedures & Functions in PL/SQL

TL;DR

Procedures are reusable blocks of code stored in the database. They don't return a value; they perform actions (DML, loops, data processing). Functions return a value and can be used in SQL queries.

Use CREATE OR REPLACE PROCEDURE and CREATE OR REPLACE FUNCTION. Parameters are IN (input), OUT (output), or IN OUT (both). Call procedures with EXEC or from PL/SQL blocks.

In Callidus Commissions, procedures drive the calculation pipeline and data extracts. Functions compute scalar values (commission percentages, attainment %) and can be embedded in SELECT queries.

Why Procedures and Functions?

So far, you've written anonymous blocks — code that runs once and is discarded. Stored procedures and functions are persistent database objects. Once created, they live in the database and can be called repeatedly from other PL/SQL blocks, reports, ETL tools, or applications.

In Callidus Commissions, the entire calculation pipeline is built from procedures. The schema has procedures like CS_CALC_PROCESS, CS_EXTRACT_RESULTS, CS_VALIDATE_DATA — each one encapsulates a specific part of the compensation calculation. By the end of this lesson, you'll understand how these fit together.

Stored Procedures: CREATE OR REPLACE PROCEDURE

A procedure is a named PL/SQL block that performs actions but doesn't return a value (unless via OUT parameters).

-- Create or replace a procedure
CREATE OR REPLACE PROCEDURE proc_mark_results_processed
  (
    p_period_id IN VARCHAR2,
    p_rows_affected OUT NUMBER,
    p_status_msg OUT VARCHAR2
  )
AS
BEGIN
  -- Update results to processed status
  UPDATE CS_RESULTS
  SET    STATUS = 'PROCESSED',
           PROCESSED_DATE = SYSDATE
  WHERE  PERIOD_ID = p_period_id
    AND  STATUS = 'DRAFT';

  -- Set output parameters
  p_rows_affected := SQL%ROWCOUNT;
  p_status_msg := 'Processed ' || p_rows_affected || ' results';

  -- Commit transaction
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    p_status_msg := 'ERROR: ' || SQLERRM;

END proc_mark_results_processed;
/

Key elements:

CREATE OR REPLACE PROCEDURE: Creates the procedure if it doesn't exist, or replaces it if it does. This makes the statement idempotent (safe to run repeatedly).
Parameters: In parentheses. Each parameter has a name, a direction (IN, OUT, IN OUT), and a data type. You can't use %TYPE directly in procedure signatures, so use explicit types.
IN: Input parameter. Caller passes a value in. Procedure reads it but doesn't modify it.
OUT: Output parameter. Caller passes a variable, and procedure sets its value. The input value is ignored.
IN OUT: Bi-directional. Caller passes a value, procedure reads and modifies it.
AS or IS: Both are equivalent. Introduces the body.
EXCEPTION / END / /: Same as anonymous blocks — exception handling, END to close, and / to execute.

Calling Procedures

You call procedures from SQL Developer using EXEC or from within a PL/SQL block using just the procedure name.

-- Method 1: From SQL Developer (EXEC command)
EXEC proc_mark_results_processed('2026-Q1', NULL, NULL);

-- Method 2: From PL/SQL block (anonymous block)
DECLARE
  v_rows_affected NUMBER;
  v_status_msg    VARCHAR2(200);

BEGIN
  -- Call procedure, passing IN parameter and capturing OUT parameters
  proc_mark_results_processed(
    '2026-Q1',       -- IN parameter: period_id
    v_rows_affected, -- OUT parameter: rows_affected
    v_status_msg     -- OUT parameter: status_msg
  );

  -- Use the OUT parameters
  DBMS_OUTPUT.PUT_LINE(v_status_msg);

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

END;
/

When calling, the order of parameters matters. The procedure definition says p_period_id IN VARCHAR2, p_rows_affected OUT NUMBER, p_status_msg OUT VARCHAR2, so you pass them in that order.

ℹ️Named parameters: You can also call procedures with named parameters (order doesn't matter): proc_mark_results_processed(p_period_id => '2026-Q1', p_rows_affected => v_rows, p_status_msg => v_msg); This is safer for procedures with many parameters.

Stored Functions: CREATE OR REPLACE FUNCTION

A function is like a procedure, but it returns a single value. Functions can be used in SQL SELECT statements, making them more powerful than procedures for data transformation.

-- Function: returns VARCHAR2 (commission tier based on quota attainment)
CREATE OR REPLACE FUNCTION get_commission_tier
  (
    p_participant_id IN NUMBER,
    p_period_id      IN VARCHAR2
  )
RETURN VARCHAR2
AS
  v_earned  NUMBER;
  v_quota   NUMBER;
  v_pct     NUMBER(5,2);

BEGIN
  -- Fetch earned results for this participant/period
  SELECT NVL(SUM(RESULT_AMOUNT), 0)
  INTO   v_earned
  FROM   CS_RESULTS
  WHERE  PARTICIPANT_ID = p_participant_id
    AND  PERIOD_ID = p_period_id;

  -- Fetch quota
  SELECT NVL(SUM(QUOTA_AMOUNT), 0)
  INTO   v_quota
  FROM   CS_QUOTA
  WHERE  PARTICIPANT_ID = p_participant_id
    AND  PERIOD_ID = p_period_id;

  -- Handle zero quota
  IF v_quota = 0 THEN RETURN 'NO_QUOTA'; END IF;

  -- Calculate percentage attained
  v_pct := ROUND(v_earned / v_quota * 100, 2);

  -- Return tier based on percentage
  RETURN CASE
    WHEN v_pct >= 100 THEN 'GOLD'
    WHEN v_pct >= 80  THEN 'SILVER'
    WHEN v_pct >= 60  THEN 'BRONZE'
    ELSE 'NONE'
  END;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in get_commission_tier: ' || SQLERRM);
    RETURN 'ERROR';

END get_commission_tier;
/

Key differences from procedures:

RETURN datatype: Specifies what type the function returns.
RETURN value: Inside the function, use RETURN to return a value (not an OUT parameter).
Functions can be used in SELECT statements (procedures cannot).

Calling Functions

Functions are called like any other function. You can embed them in SQL:

-- Call function from SELECT statement
SELECT
  p.ID,
  p.NAME,
  get_commission_tier(p.ID, '2026-Q1') AS TIER
FROM   CS_PARTICIPANT p
WHERE  p.STATUS = 'ACTIVE'
ORDER BY ID;

-- Call function from PL/SQL block
DECLARE
  v_tier VARCHAR2(20);

BEGIN
  v_tier := get_commission_tier(1001, '2026-Q1');
  DBMS_OUTPUT.PUT_LINE('Tier: ' || v_tier);

END;
/

This is powerful — you can now use your business logic directly in SELECT queries. Any tool that can query the database (reports, ETL, dashboards) can call your functions.

Comparison: Procedures vs Functions

Feature	Procedure	Function
Return value	None (uses OUT parameters)	Single value with RETURN
Used in SQL SELECT?	No	Yes, like any column or expression
Parameters	IN, OUT, IN OUT	Usually IN only (can have IN, but rarely OUT)
Use case	Perform actions: DML, loops, complex processing	Compute values: transformations, calculations, lookups
Callidus example	CS_CALC_PROCESS (runs calculation pipeline)	GET_ATTAINMENT_PCT (compute percentage)

Real-World Callidus Procedure: Commission Calculation

Here's a realistic procedure that processes a full compensation period — iterating participants, calculating commissions, and inserting results.

CREATE OR REPLACE PROCEDURE calc_period_compensation
  (
    p_period_id IN VARCHAR2,
    p_processed_count OUT NUMBER,
    p_error_msg OUT VARCHAR2
  )
AS
  CURSOR cur_active_participants IS
    SELECT ID, NAME, PLAN_ID
    FROM   CS_PARTICIPANT
    WHERE  STATUS = 'ACTIVE';

  v_commission NUMBER(18,2);
  v_tier       VARCHAR2(20);
  v_count      PLS_INTEGER := 0;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting compensation calculation for ' || p_period_id);

  FOR rec IN cur_active_participants LOOP
    -- Get commission tier for this participant
    v_tier := get_commission_tier(rec.ID, p_period_id);

    -- Determine base commission rate by tier
    v_commission := CASE v_tier
      WHEN 'GOLD'   THEN 0.10
      WHEN 'SILVER' THEN 0.075
      WHEN 'BRONZE' THEN 0.05
      ELSE 0
    END;

    -- Insert compensation summary record
    INSERT INTO CS_COMP_SUMMARY
      (PARTICIPANT_ID, PERIOD_ID, COMMISSION_RATE, COMMISSION_TIER,
       PROCESSED_DATE, STATUS)
    VALUES
      (rec.ID, p_period_id, v_commission, v_tier, SYSDATE, 'CALCULATED');

    v_count := v_count + 1;

  END LOOP;

  -- Commit all inserts
  COMMIT;

  -- Set output parameters for caller
  p_processed_count := v_count;
  p_error_msg := 'SUCCESS: Processed ' || v_count || ' participants';

  DBMS_OUTPUT.PUT_LINE(p_error_msg);

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    p_processed_count := 0;
    p_error_msg := 'ERROR: ' || SQLERRM;
    DBMS_OUTPUT.PUT_LINE(p_error_msg);

END calc_period_compensation;
/

-- Call the procedure
DECLARE
  v_count NUMBER;
  v_msg   VARCHAR2(500);

BEGIN
  calc_period_compensation('2026-Q1', v_count, v_msg);
  DBMS_OUTPUT.PUT_LINE(v_msg);

END;
/

This procedure demonstrates:

Cursor loop to iterate participants.
Calling a function (get_commission_tier) from within a procedure.
CASE expression to determine commission rates.
DML (INSERT) within the loop.
COMMIT on success, ROLLBACK on error.
OUT parameters to communicate results to the caller.

Viewing and Managing Procedures/Functions

Once created, procedures and functions are database objects. You can view them:

-- List all procedures and functions in your schema
SELECT OBJECT_NAME, OBJECT_TYPE
FROM   USER_OBJECTS
WHERE  OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION')
ORDER BY OBJECT_NAME;

-- View source code of a procedure
SELECT LINE, TEXT
FROM   USER_SOURCE
WHERE  NAME = 'CALC_PERIOD_COMPENSATION'
AND    TYPE = 'PROCEDURE'
ORDER BY LINE;

-- View procedure dependencies (what it calls)
SELECT REFERENCED_NAME, REFERENCED_TYPE
FROM   USER_DEPENDENCIES
WHERE  NAME = 'CALC_PERIOD_COMPENSATION';

These data dictionary queries help you understand the Callidus schema — what procedures exist, what code they contain, and what they depend on.

⚠️Recompilation: If you change a table definition (add/drop a column), any procedures that reference that table become INVALID. Oracle recompiles them on the next call, but if the changes are incompatible, the procedure fails at runtime. Always test after schema changes.

Key Takeaways

Procedures perform actions (DML, loops, processing). Functions return values and can be used in SQL.
Use CREATE OR REPLACE to create or update procedures and functions safely.
IN parameters pass values in. OUT parameters return values. IN OUT do both.
Call procedures with EXEC or from PL/SQL blocks. Call functions like SQL functions (in SELECT, or assigned to variables).
In Callidus Commissions, procedures drive the calculation pipeline; functions compute values used in calculations.
Always include exception handling (EXCEPTION / WHEN OTHERS) in procedures and functions to handle errors gracefully.

Next: Packages, BULK COLLECT & FORALL →

Learn how to organise procedures and functions into packages, and how to use BULK COLLECT and FORALL for massive performance gains.

Lesson 5: Packages, BULK COLLECT & FORALL →