Stored Functions, Cursors and Procedural Data Processing

1. Stored Functions

A stored function is a named database program that returns one value of a declared type. Unlike a procedure, it is invoked within an expression:

SELECT product_id,
       product_name,
       discounted_price(price, discount_rate)
FROM product;

Functions suit reusable scalar calculations. Because a query may call a function once for every examined row, its cost and side effects must be controlled.

Use built-in SQL functions when they already express the operation. A custom function adds deployment, privileges, compatibility and performance considerations.

2. Creating a Function

DELIMITER //

CREATE FUNCTION amount_with_tax(
    p_amount DECIMAL(12, 2),
    p_rate DECIMAL(7, 6)
)
RETURNS DECIMAL(12, 2)
DETERMINISTIC
NO SQL
BEGIN
    IF p_amount IS NULL OR p_rate IS NULL THEN
        RETURN NULL;
    END IF;

    RETURN ROUND(p_amount * (1 + p_rate), 2);
END//

DELIMITER ;

RETURNS declares the result type. RETURN supplies the result and ends execution. Every possible path must return a compatible value or signal an error.

3. Invoking and Dropping Functions

A stored function participates in expressions:

SELECT amount_with_tax(100.00, 0.18);

It can appear in a select list, predicate, ordering expression or procedural assignment where MySQL permits it.

Remove it with:

DROP FUNCTION IF EXISTS amount_with_tax;

Function calls in predicates can affect index use and per-row calls can add substantial overhead. Keep simple logic in direct SQL when clearer.

4. Function Parameters

MySQL stored-function parameters are input values; functions do not declare procedure-style OUT or INOUT parameter modes. The declared return value is the function's output.

Use parameter names that cannot be confused with columns:

p_amount
p_customer_id

Document units, valid ranges, collation expectations, rounding and NULL behavior. A numeric function returning money must specify currency assumptions and precision rather than treating DECIMAL syntax as the complete business rule.

5. Routine Characteristics

Routine characteristics describe behavior:

DETERMINISTIC means the same inputs yield the same result;
NOT DETERMINISTIC means they may not;
NO SQL means no SQL data access;
CONTAINS SQL permits SQL that does not read or modify data as categorized;
READS SQL DATA indicates reading;
MODIFIES SQL DATA indicates modification where allowed.

These declarations must be truthful. They help replication, validation and human reasoning but do not magically enforce every behavioral promise.

6. Determinism

A pure arithmetic function can be deterministic. A function using current time, random values, session state or data that can change independently is generally not deterministic from its arguments alone.

A database-reading function may return different results for identical input after another transaction changes a table. Labeling it deterministic would be misleading.

Determinism is about result dependence, not speed. A deterministic function can still be expensive.

7. Function Side Effects

Functions used inside queries should be treated as value computations. MySQL restricts statements that would produce problematic side effects or result sets from functions.

Even allowed data access can be surprising when a function is evaluated many times or in an optimizer-dependent context. Do not rely on a particular call count or order for side effects.

Use procedures for explicit commands and functions for values. This division makes query behavior easier to understand.

8. Built-in Function Families

Before writing a custom function, examine MySQL's built-ins:

text: CONCAT, SUBSTRING, TRIM, UPPER, LOWER;
numeric: ABS, ROUND, CEIL, FLOOR, MOD;
temporal: DATE_ADD, DATEDIFF, TIMESTAMPDIFF, DATE_FORMAT;
NULL: COALESCE, NULLIF, IFNULL;
conversion: CAST and CONVERT;
JSON: extraction, construction and modification functions.

Built-ins are recognized by the optimizer and understood by other SQL readers. Wrap them only when a stable domain abstraction justifies it.

9. Cursors

A cursor lets a stored program process a query result one row at a time. The ordinary lifecycle is:

declare the cursor;
open it;
fetch rows into variables;
detect end of data;
close it.

MySQL stored-program cursors are asensitive, read-only and nonscrollable. They traverse forward and do not update the current row through the cursor itself.

10. Asensitive, Read-Only and Nonscrollable

Asensitive means the server may or may not materialize a copy of the result. It is unrelated to case sensitivity.

Read-only means the cursor cannot issue positioned updates or deletes against its current row. Separate SQL statements can modify tables using fetched key values.

Nonscrollable means FETCH advances forward; it cannot fetch the previous row or jump arbitrarily. If backward access is required, redesign the process or materialize data in an appropriate temporary structure.

11. Cursor Declarations

Cursor declarations name a SELECT:

DECLARE cur_order CURSOR FOR
    SELECT order_id, total_amount
    FROM order_header
    WHERE status = 'READY'
    ORDER BY order_id;

The query can refer to routine parameters and variables in scope. The cursor is not opened by DECLARE.

MySQL requires declaration order within a block: local variables and conditions first, cursors next, handlers after cursors and executable statements last.

12. End-of-Data Handlers

Fetching past the final row raises the NOT FOUND condition. A CONTINUE handler commonly sets a flag:

DECLARE v_done BOOLEAN DEFAULT FALSE;
DECLARE v_order_id BIGINT;
DECLARE v_amount DECIMAL(12, 2);

DECLARE cur_order CURSOR FOR
    SELECT order_id, total_amount
    FROM order_header
    WHERE status = 'READY'
    ORDER BY order_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

The handler allows control to continue after the failed final fetch so the loop can terminate cleanly.

13. A Complete Cursor Loop

OPEN cur_order;

read_loop: LOOP
    FETCH cur_order INTO v_order_id, v_amount;

    IF v_done THEN
        LEAVE read_loop;
    END IF;

    INSERT INTO order_audit(order_id, observed_amount)
    VALUES (v_order_id, v_amount);
END LOOP;

CLOSE cur_order;

Test the completion flag immediately after FETCH and before processing variables. An unsuccessful fetch does not provide fresh row values; processing first can reuse values from the preceding successful row.

14. OPEN, FETCH and CLOSE

OPEN prepares the cursor and its result according to server behavior. FETCH obtains the next row and assigns its columns positionally to variables.

The number and compatible types of target variables must match the selected columns. Implicit conversion can lose information, so declare suitable types.

CLOSE releases the opened cursor. Cursors also close when their enclosing block ends, but explicit closure makes the lifecycle clear and releases resources promptly.

15. Handler Scope and Interactions

A NOT FOUND handler can be activated by operations other than cursor FETCH, including SELECT ... INTO returning no row. If both occur in the same block, a shared handler may set the cursor-completion flag unexpectedly.

Use nested blocks or separate control structure to isolate handler scope. Understand which statements can raise each handled condition.

Handlers can CONTINUE, EXIT or perform recovery logic. They should not convert unexpected database failures into false success.

16. Set-Based SQL Before Cursors

A cursor that totals rows:

fetch amount
add amount to total

should usually be:

SELECT SUM(amount)
INTO v_total
FROM payment
WHERE customer_id = p_customer_id;

Set-based operations reduce procedural context switching and let the optimizer choose joins, indexes and aggregation strategies.

Use a cursor only when each row requires procedural sequencing that a clear set operation cannot represent.

17. Replacing Cursor Updates

A cursor loop applying the same formula to every qualifying row:

UPDATE account
SET service_charge = balance * p_rate
WHERE status = 'ACTIVE';

is clearer and usually faster as one UPDATE.

Conditional differences can often use CASE. Relationships can use joined updates. Aggregation can compute summaries. Window functions can handle many position-based calculations in supported MySQL versions.

Row-by-row logic should be the conclusion after considering relational forms, not the default starting point.

18. Legitimate Cursor Uses

A cursor can be reasonable when:

each row invokes distinct procedural validation;
processing depends on state produced by the previous row;
a legacy interface accepts one item at a time;
controlled administrative work needs detailed per-row handling;
a complex migration cannot be expressed safely in one set operation.

Even then, define deterministic ordering if sequence matters. Without ORDER BY, fetch order is not guaranteed.

19. Transactions and Cursor Work

Row-by-row processing does not remove the need for transaction design. A loop performing a thousand updates can hold locks for a long time, generate large logs and increase deadlock probability.

Decide whether all rows form one atomic unit. If work may commit in batches, define restartability, progress tracking and idempotency so failure does not skip or duplicate effects.

A cursor's result and visible changes depend on transaction isolation and its asensitive implementation. Avoid assuming it is a frozen snapshot unless the actual transaction behavior provides that property.

20. Error Handling and Cleanup

A procedure using a cursor and transaction may declare an EXIT handler:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

Block exit closes declared cursors, while explicit close on normal paths remains clear. The handler should preserve the original error with RESIGNAL unless the routine intentionally translates it.

Do not commit partial work in a generic error handler unless partial completion is the documented contract.

21. Performance

Cursor cost includes fetching, procedural branching and individual statements for each row. The routine may also hold a transaction open across the entire result.

Measure row counts, total duration, lock waits, statements executed and log volume. Add indexes for the cursor query and for any key-based updates inside the loop.

If performance degrades with row count, reconsider a set-based rewrite or controlled batching before adding more procedural complexity.

22. Choosing Functions, Procedures and Cursors

Use a function for one reusable scalar value. Use a procedure for an explicit operation, multiple results or coordinated data changes. Use a cursor inside a stored program only for necessary row-by-row state.

For every stored function, define determinism, SQL data access, result type, precision and NULL behavior. For every cursor, define result order, end-of-data handling, transaction scope and restart behavior.

Procedural SQL is valuable when it complements the relational model. It becomes harmful when ordinary set operations are decomposed into slow loops without a semantic need.