Views, Procedures, Functions, Triggers, Cursors and Window Functions — Data Collection and DBMS

view

A view is a stored query exposed as a virtual table, used to simplify access, limit columns or rows and provide a stable interface.

stored procedure

A stored procedure is named server-side program logic that can accept parameters.

stored function

A named database routine that accepts parameters and returns a value for use in expressions, subject to DBMS rules.

trigger

A trigger is database code invoked automatically by a defined data or schema event.

cursor

A procedural database handle that fetches query-result rows one at a time or in controlled batches.

exception handler

A procedural block that intercepts a declared database error and performs recovery, translation or cleanup.

window function

A window function computes a value across a related set of rows while retaining each input row, using OVER with optional partition, order and frame clauses.

partition and frame

In a window function, PARTITION BY forms groups and the frame selects rows around the current row within each group.

database programmability

Procedures, functions, triggers, events and related language features used to execute controlled logic near stored data.

audit logic

Database logic that records who changed which data, when it changed and, when required, its old and new values.

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.

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.

Triggers

A trigger is a stored program that the DBMS activates automatically when a declared data-change event occurs on its table. MySQL table triggers respond to INSERT, UPDATE or DELETE and run BEFORE or AFTER each affected row.

Unlike a procedure, a trigger is not invoked with CALL. Its execution is an implicit part of the statement that caused the event.

Triggers can enforce specialized rules, normalize incoming values, maintain tightly related derived data and record audits. Their implicit nature also makes overuse difficult to understand and debug.

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.

CONTINUE Handlers

A CONTINUE handler performs its body and resumes control according to MySQL block semantics after the statement that raised the condition.

It is suitable for expected conditions such as cursor exhaustion:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

The following code must inspect the flag before using fetch variables.

Do not continue after an error unless subsequent state is well defined. Ignoring a duplicate key or arithmetic exception can create misleading partial results.

Condition Handling

Stored procedures, functions and triggers can declare handlers for conditions:

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

A handler definition states the action and condition category. Scope begins in its declaring block after declaration rules are satisfied.

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.

BEFORE Triggers

A BEFORE trigger runs before the row change is applied. It can validate proposed data and, for permitted events and columns, assign NEW values:

CREATE TRIGGER bi_customer_normalize BEFORE INSERT ON customer FOR EACH ROW SET NEW.email = LOWER(TRIM(NEW.email));

Use such normalization only when it represents a universal database rule. Hidden modification can surprise clients that expect the exact supplied value to be stored.

Prefer column defaults, generated columns and CHECK constraints when they express the rule directly.

Trigger Design Method

First ask whether a constraint, generated column, procedure or application transaction expresses the rule more visibly. If a trigger remains appropriate, define its event, timing, row images, transaction behavior and performance at multirow scale.

Keep the body focused. Signal invalid state with a documented condition. Use handlers only when the routine can truly recover or must clean up before propagating failure.

Triggers and handlers are reliable when their implicit effects are small, discoverable, transactional and tested. They become dangerous when they hide broad workflows or convert failures into silent success.

Handler Scope

Handlers belong to blocks. A nested block can handle a condition locally while an outer handler covers the larger operation.

This structure is useful when one optional lookup may be absent but any modification error must abort:

BEGIN BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_label = NULL; SELECT label INTO v_label FROM lookup WHERE lookup_id = p_lookup_id; END;

-- other operations use v_label END;

Narrow scope prevents a NOT FOUND handler intended for one statement from accidentally catching cursor exhaustion elsewhere.

Designing Stored Procedures

Use a procedure when centralized database behavior adds clear value. Keep it focused on one business responsibility. Prefer set-based statements, constrain parameters, use unambiguous names and handle every affected-row count.

Decide who owns the transaction before writing COMMIT. Propagate failures honestly. Limit privileges and document results.

Stored procedures work best as deliberate database interfaces. They become difficult when they mix unrelated workflows, hidden commits, broad privileges, loops over set operations and undocumented result sets.

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.

Audit Integrity and Transactions

An audit row written by a trigger to a transactional table rolls back with the business change. This keeps the audit consistent with committed state.

It does not record attempted changes that failed or were rolled back. Capturing attempts requires logging outside the rolled-back transaction, application audit or database/server auditing facilities.

Writing audit data to a nontransactional table to survive rollback can break atomic behavior and create inconsistent recovery. Choose audit semantics explicitly.

Constraints Before Triggers

Use declarative constraints for rules they can express:

price DECIMAL(12, 2) NOT NULL CHECK (price >= 0)

Constraints are visible in metadata, understood by tools, uniformly enforced and often easier to optimize and maintain.

Triggers are appropriate for rules involving change history, coordinated side effects inside the database or logic unavailable in a constraint. Do not reimplement primary keys, foreign keys, uniqueness or simple checks in triggers.

Trigger Limitations

MySQL restricts operations a trigger can perform, including problematic modification of the table already being changed. Triggers cannot control transactions with COMMIT or ROLLBACK.

Trigger chains across related tables can create recursion-like cycles, lock-order problems and diagnostics far from the initiating statement.

Keep trigger logic small, deterministic where practical and documented beside the table contract.

Testing Triggers and Handlers

Test each event and timing with:

valid and invalid single-row changes;
multirow statements;
NULL transitions;
unchanged values in UPDATE;
foreign-key cascades as applicable;
statement failure midway through multiple rows;
explicit rollback;
concurrent changes and lock conflicts;
missing privileges and changed definer accounts.

Verify both business tables and audit or derived tables after commit and rollback.

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);

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

Handler Conditions

Handlers can target:

a specific numeric MySQL error code;
a specific SQLSTATE value;
a named condition;
SQLWARNING, SQLSTATE class 01;
NOT FOUND, SQLSTATE class 02;
SQLEXCEPTION, other exception classes.

Specific conditions generally take precedence according to MySQL rules. A named condition gives domain meaning:

DECLARE duplicate_customer CONDITION FOR SQLSTATE '23000';

Broad handlers should not hide precise failures that need different treatment.

Error Handling and Cleanup

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

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.

AFTER Triggers

An AFTER trigger runs after the row change has succeeded within the current statement:

CREATE TRIGGER au_product_audit AFTER UPDATE ON product FOR EACH ROW INSERT INTO product_audit( product_id, old_price, new_price, changed_at, changed_by ) VALUES( NEW.product_id, OLD.price, NEW.price, CURRENT_TIMESTAMP, CURRENT_USER() );

The audit insertion remains part of the same transaction for transactional tables. A later rollback removes both the original update and its audit row.

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.

Trigger Security

Triggers execute under a defined security context and can access data the invoking account may not access directly. This supports central rules but increases review requirements.

Control who can create or alter triggers. Record definitions in migrations, review their definer accounts and avoid dynamic SQL or untrusted context where applicable.

Privileges on base statements do not make hidden trigger work harmless. A user permitted to update one table may indirectly cause changes to an audit or summary table through its trigger.

Audit Tables

A useful audit record can include:

target entity identifier;
operation type;
old and new relevant values;
database account;
application actor identifier supplied through a controlled context;
transaction or request identifier;
timestamp;
reason or source.

Store only what is needed for accountability and investigation. Audit tables can contain sensitive history and need their own access control, retention, indexing and backup policies.

CURRENT_USER may identify the routine definer or authenticated database context differently from an application end user. Application identity must be propagated securely.

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.

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.

Events That Do Not Fire a Trigger

An ordinary SELECT does not fire INSERT, UPDATE or DELETE triggers. TRUNCATE TABLE is DDL-like and does not activate row DELETE triggers as a DELETE statement would.

Referential cascades and other internal actions have product-specific trigger behavior and should be verified rather than inferred.

If auditing must cover administrative DDL, reads or every possible privileged access path, table DML triggers alone are insufficient.

Error Handling

Stored routines can declare handlers for SQL conditions. A transactional procedure often needs an exit handler that rolls back and rethrows:

The handler must be declared before executable statements. Swallowing an error and returning apparent success leaves callers unable to distinguish completion from partial failure.

Detailed handlers, SIGNAL and RESIGNAL are developed with trigger and handler design, but every procedure contract should already define its failures.

Stored Programs

A stored procedure is a named program stored in the database and invoked with CALL. It can accept parameters, declare local variables, execute SQL, use conditional and loop statements, return result sets and coordinate transactional work.

Stored procedures are useful for operations that belong close to shared data: controlled updates, data validation, administrative processing and a stable database API for several clients.

They are not automatically faster or easier to maintain than application code. Version control, deployment, testing, observability and portability must be planned.

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.

Trigger Definition

DELIMITER //

CREATE TRIGGER bi_product_validate BEFORE INSERT ON product FOR EACH ROW BEGIN IF NEW.price < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be nonnegative'; END IF; END//

DELIMITER ;

The name identifies the trigger. BEFORE INSERT defines timing and event. FOR EACH ROW means a multirow INSERT activates it separately for every affected row.

EXIT Handlers

An EXIT handler runs and then leaves the block in which it was declared, even when the error arose in a nested block.

It is suitable for failure cleanup:

Transaction ownership must still be correct. A low-level routine should not roll back a transaction it does not own unless its contract states that behavior.

Derived Data

A trigger can maintain a stored total or summary, but concurrency and every write path must be considered. Updating an order total after each line change may work only if INSERT, UPDATE, DELETE, bulk loads and correction processes all activate complete logic.

Derived data adds write contention and the possibility of drift. Recompute from source rows when cost permits. If materialization is necessary, define one authoritative source and a repair process.

Generated columns or scheduled summary refreshes may be clearer alternatives.

Calling and Removing Procedures

CALL invokes a procedure:

CALL find_customer_orders(42);

A procedure may return zero or more result sets in addition to OUT or INOUT values. Client code must consume all results according to its database driver.

DROP PROCEDURE removes a definition:

DROP PROCEDURE IF EXISTS find_customer_orders;

CREATE OR REPLACE support varies by stored-object type and MySQL version, so deployments often use explicit drop-and-create migrations.

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.

Multirow Statements

MySQL triggers are row-level. One UPDATE affecting 10,000 rows activates its matching trigger 10,000 times.

Trigger logic must therefore be efficient and correct for every row. Per-row queries can turn one set-based statement into thousands of hidden operations and increase locking, logging and deadlock risk.

Do not assume row processing order unless the database explicitly provides a relevant guarantee; business correctness should not depend on it.

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.

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.

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.

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.

Transactions in Procedures

A procedure can execute START TRANSACTION, COMMIT and ROLLBACK where permitted, but transaction ownership must be explicit.

If a reusable procedure commits internally, a caller cannot combine it atomically with additional work. A procedure intended as one complete business command may own its transaction; a lower-level composable routine often should let the caller own it.

Document whether the routine begins, commits, rolls back or assumes an existing transaction. Remember that DDL can cause implicit commits.

SIGNAL

SIGNAL raises an explicit condition:

IF p_quantity <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity must be positive', MYSQL_ERRNO = 30001; END IF;

SQLSTATE 45000 is commonly used for an unhandled user-defined exception. A clear message and documented application error mapping make the failure actionable.

SIGNAL stops normal execution unless an applicable handler catches it.

Creating a Function

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//

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

Procedure Contracts

A useful procedure contract states:

parameter names, modes, types and NULL rules;
result sets and OUT values;
modified tables and invariants;
required privileges;
transaction ownership and isolation expectations;
errors and retryable conditions;
idempotency behavior.

A procedure that sends external effects or creates nonrepeatable business identifiers needs an explicit retry design.

Creating a Procedure

CREATE PROCEDURE count_products(OUT p_total BIGINT) BEGIN SELECT COUNT(*) INTO p_total FROM product; END//

CALL count_products(@total); SELECT @total;

The procedure assigns the row count to its OUT parameter. The caller supplies a session variable beginning with @ to receive the value.

Compound Statements

BEGIN and END form a compound statement containing declarations and executable statements:

BEGIN DECLARE v_count BIGINT DEFAULT 0; SELECT COUNT(*) INTO v_count FROM product; SET p_total = v_count; END

Nested blocks create nested scope. Local variables cease to exist when their block ends.

Declarations must appear before executable statements in a block and follow MySQL's required declaration ordering among variables, conditions, cursors and handlers.

Deployment and Observability

Store routine definitions in version-controlled migration files. Deploy them in a known order with dependent views, grants and application releases.

Log calls at the appropriate application boundary, monitor duration and lock waits and inspect slow statements executed inside routines. A single CALL can hide several expensive statements from superficial application timing.

Test parameter boundaries, NULL inputs, missing rows, duplicate rows, concurrent calls, handler paths and privilege behavior.

SQLSTATE

SQLSTATE is a five-character standard condition code. Its first two characters identify a class. Class 00 means successful completion, 01 indicates warnings, 02 indicates no data and other classes represent exceptions.

MySQL error numbers provide vendor detail, while SQLSTATE offers portable categorization. Client contracts can expose a stable domain error instead of depending on message text.

Messages help people but are poor programmatic identifiers because wording may change.

IF Statements

IF p_amount < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be nonnegative'; ELSEIF p_amount = 0 THEN SET p_category = 'ZERO'; ELSE SET p_category = 'POSITIVE'; END IF;

IF uses THEN, optional ELSEIF branches, optional ELSE and END IF. Conditions follow SQL three-valued logic; UNKNOWN does not act as TRUE.

This procedural IF statement differs from MySQL's scalar IF() function.

OLD and NEW Row Images

Trigger row references depend on event:

Event	OLD	NEW
INSERT	unavailable	proposed inserted row
UPDATE	row before change	row after change
DELETE	deleted row	unavailable

OLD values are read-only. NEW can be assigned in an appropriate BEFORE trigger, subject to MySQL restrictions. In an AFTER trigger, the change has already occurred and NEW is used for observation.

Qualify every row value with OLD or NEW so its time perspective is unmistakable.

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.

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.

Diagnostics

GET DIAGNOSTICS retrieves condition information where supported:

GET DIAGNOSTICS CONDITION 1 v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT;

A handler can log or translate structured information. Diagnostic logging must avoid leaking secrets, personal data or full dynamic statements containing credentials.

Logging inside the same transaction rolls back with it. Decide whether diagnostics need application logging or server facilities outside that transaction.

RESIGNAL

RESIGNAL propagates the current handled condition:

Rollback restores transactional state, while RESIGNAL informs the caller that the operation failed. Without propagation, a client may report success.

RESIGNAL can preserve the condition or change selected diagnostic attributes to add abstraction-level context. Do not destroy useful underlying information without a stable replacement.

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.

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.

Local and Session Variables

Local variables are declared with DECLARE and exist only within their block. Parameters are also local to the routine invocation.

Session user variables use @name and remain associated with the connection:

SET @customer_id = 42; CALL customer_order_count(@customer_id, @count);

Connection pools can reuse sessions, so application logic should not assume session variables begin empty. Prefer parameterized calls and explicit initialization.

Local variables are usually safer for routine implementation because their scope and type are declared.

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.

Naming and Scope

Ambiguous names can silently refer to a parameter or column differently than intended:

WHERE customer_id = customer_id

This might become a tautology rather than a parameter comparison. Use conventions and aliases:

WHERE c.customer_id = p_customer_id

Qualify columns in multi-table statements. Clear naming prevents logic defects that remain syntactically valid.

The Client Delimiter

Statements inside a stored-program body end with semicolons. An interactive client that normally sends text at the first semicolon would split the CREATE PROCEDURE definition prematurely.

DELIMITER changes which token that client recognizes as the end of the complete definition. It is a client command, not part of the stored procedure and not a server-side transaction statement.

Application drivers can usually send the complete definition through their APIs without embedding an interactive delimiter command.

IN Parameters

IN is the default parameter mode and supplies a value to the procedure:

CREATE PROCEDURE list_orders(IN p_customer_id BIGINT) BEGIN SELECT order_id, ordered_at, total_amount FROM order_header WHERE customer_id = p_customer_id ORDER BY ordered_at DESC, order_id DESC; END;

Assignments inside the procedure do not return a changed IN value to the caller. Prefixing parameters with p_ helps distinguish them from columns.

Dynamic SQL

MySQL prepared statements can construct SQL dynamically when object names or query shape must vary. Data values should still use parameters rather than string concatenation.

Dynamic object identifiers cannot always be bound as ordinary values and need strict allow-list validation. Concatenating untrusted text creates SQL injection risk even inside a stored procedure.

Prefer static SQL when the schema objects are known. It is easier to validate, authorize, analyze and optimize.

OUT Parameters

An OUT parameter carries a result back:

CREATE PROCEDURE customer_order_count( IN p_customer_id BIGINT, OUT p_order_count BIGINT ) BEGIN SELECT COUNT(*) INTO p_order_count FROM order_header WHERE customer_id = p_customer_id; END;

The caller provides a variable capable of receiving the output. Its incoming value is not the procedure's input contract.

OUT parameters suit small fixed result sets. A SELECT result set is often clearer for tabular output.

INOUT Parameters

An INOUT parameter carries an initial value into the routine and a final value out:

CREATE PROCEDURE add_tax( INOUT p_amount DECIMAL(12, 2), IN p_rate DECIMAL(6, 4) ) BEGIN SET p_amount = ROUND(p_amount * (1 + p_rate), 2); END;

The caller initializes a session variable, passes it to CALL and reads the changed value afterward.

Use INOUT when modifying one logical caller value is natural. Separate IN and OUT parameters can be clearer when input and result have different meanings.

Security Context

Stored objects execute under a SQL SECURITY context, commonly DEFINER or INVOKER as supported. A definer-security routine can expose a narrowly controlled operation without granting clients direct table privileges.

This is powerful and must be reviewed. Definer accounts, object ownership, dynamic SQL and input validation affect privilege boundaries.

Grant EXECUTE only to roles that need the routine. Avoid creating routines under an unnecessarily powerful permanent account.

Set-Based Operations Before Loops

This loop-oriented intention:

for every expired session, delete it

is naturally expressed as:

DELETE FROM session WHERE expires_at < CURRENT_TIMESTAMP;

The set-based statement gives the optimizer freedom to select an access plan and avoids per-row statement overhead.

Loops remain useful for cursor-driven external-style sequencing or procedural algorithms that cannot be expressed clearly as relational operations. Their use should be justified by semantics rather than familiarity with general-purpose programming.

LOOP, LEAVE and ITERATE

LOOP has no built-in condition:

process_loop: LOOP SET v_counter = v_counter + 1;

IF v_counter > p_limit THEN LEAVE process_loop; END IF;

IF MOD(v_counter, 2) = 0 THEN ITERATE process_loop; END IF;

INSERT INTO odd_number(value) VALUES (v_counter); END LOOP;

LEAVE exits the named loop or block. ITERATE skips to the next iteration of the named loop. Labels make nested control flow explicit.

REPEAT

REPEAT executes its body and checks UNTIL afterward:

SET v_counter = 1; REPEAT SET v_total = v_total + v_counter; SET v_counter = v_counter + 1; UNTIL v_counter > p_limit END REPEAT;

The body runs at least once. The UNTIL condition ends the loop when it becomes TRUE, which is the opposite wording from a WHILE continuation condition.

Consider behavior when the condition is NULL; an UNKNOWN termination condition does not supply TRUE.

WHILE

WHILE checks its condition before each iteration:

SET v_counter = 1; WHILE v_counter <= p_limit DO INSERT INTO number_log(value) VALUES (v_counter); SET v_counter = v_counter + 1; END WHILE;

The body can execute zero times. Every loop needs a clear progress step and termination condition.

Set-based SQL is usually preferable for transforming sets of rows. Use procedural loops when iterations truly depend on prior procedural state.

CASE Statements

The simple CASE compares one expression:

CASE p_status WHEN 'N' THEN SET p_label = 'NEW'; WHEN 'P' THEN SET p_label = 'PAID'; ELSE SET p_label = 'OTHER'; END CASE;

The searched CASE evaluates separate predicates:

CASE WHEN p_total >= 10000 THEN SET p_band = 'HIGH'; WHEN p_total >= 5000 THEN SET p_band = 'MEDIUM'; ELSE SET p_band = 'STANDARD'; END CASE;

Procedural CASE controls statements. The CASE expression used inside SELECT returns a value.

Assignment

SET performs direct assignment:

SET v_subtotal = v_quantity * v_price;

SELECT ... INTO assigns query columns:

SELECT customer_name, status INTO v_name, v_status FROM customer WHERE customer_id = p_customer_id;

The query must have the expected cardinality. Multiple rows cause an error. No rows raises a not-found condition that can be handled or prevented through an aggregate or existence check.