SQL MODULE

What is SQL MODULE?

MODULE defines a named container that groups SQL stored procedures, functions, variables and other routines for easier organization, encapsulation and privilege management.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MODULE: IBM Db2 LUW / zOS: full support. MariaDB 10.3+: partial support (SQL/PSM MODULE). Firebird 3+: similar concept. Standard SQL: defined. Not supported in PostgreSQL, MySQL community, SQL Server, Oracle (uses PACKAGES instead).

SQL MODULE Full Explanation

In the SQL/PSM portion of the ISO standard, a MODULE is a top-level program unit that bundles related routines, user-defined types, global variables, cursors and exception handlers under a single namespace. Similar to a package in Oracle or a schema-bound library in other languages, a module helps you:- Separate application logic from table schemas- Avoid name collisions by introducing an extra qualifier (module_name.routine_name)- Grant or revoke EXECUTE privileges on all contained routines in one step- Version or deploy business logic as an atomic unitA module is created with CREATE MODULE, optionally specifying an AUTHORIZATION clause to set the definer and an implicit owner. Inside the module, you declare routines with the ordinary CREATE PROCEDURE / CREATE FUNCTION syntax but omit the schema prefix. Those internal objects become implicitly part of the module and can be referenced by external callers using the qualified name .. Modules live in the data dictionary like schemas. Dropping a module removes every contained object. Because MODULE is standardized but not universally implemented, behaviour details (such as whether a module can access objects outside itself by default, or whether variables persist across sessions) differ by vendor.

SQL MODULE Syntax

CREATE MODULE module_name
    [AUTHORIZATION definer]
    [LANGUAGE SQL]
BEGIN
    -- optional variable, cursor, condition declarations
END;

-- inside the module file or immediately after:
CREATE PROCEDURE module_name.proc1 (IN id INT)
BEGIN ATOMIC
    -- logic
END;

SQL MODULE Parameters

• module_name (identifier) - Required name of the module.
• AUTHORIZATION (role name) - Optional. Owner that implicitly receives privileges on all contained objects.
• LANGUAGE (identifier) - Optional language of routines. Default is SQL.
• Declarations block - Optional BEGIN…END section for global variables, cursors or conditions.

Example Queries Using SQL MODULE

-- 1. Create a module with one procedure
CREATE MODULE accounting
BEGIN
    DECLARE stmt_date DATE DEFAULT CURRENT_DATE;
END;

CREATE PROCEDURE accounting.add_invoice (IN cust_id INT, IN amount DECIMAL(10,2))
BEGIN ATOMIC
    INSERT INTO invoices(customer_id, total, created_on)
    VALUES (cust_id, amount, CURRENT_DATE);
END;

-- 2. Call the procedure from outside the module
CALL accounting.add_invoice(42, 199.99);

-- 3. Drop the whole module
DROP MODULE accounting;

Expected Output Using SQL MODULE

CREATE MODULE returns "Module ACCOUNTING created" (or vendor equivalent) and registers the namespace.
Calling the procedure inserts a row into invoices and returns success.
DROP MODULE removes the module and every routine defined in it.

Use Cases with SQL MODULE

Deploying business-domain logic (e.g., invoicing) as a self-contained unit.
Granting EXECUTE on all related procedures to a role with one GRANT statement.
Avoiding naming conflicts when multiple teams create procedures with identical routine names.
Versioning or shipping application code across environments via CREATE MODULE scripts.

Common Mistakes with SQL MODULE

Assuming every DBMS supports MODULE – most do not (see compatibility list).
Forgetting to qualify routine calls with the module name from outside the module.
Mixing schema and module qualifiers, leading to "object not found" errors.
Dropping a module without realising all internal routines will be removed.

First Introduced In

SQL:1999 (SQL/PSM)

Frequently Asked Questions

What is the difference between a schema and a module?

A schema groups database objects such as tables and views, while a module specifically groups programmatic objects like procedures, functions and variables. A module can live inside a schema.

Do variables inside a module persist across sessions?

No. Global variables declared in the module exist only for the duration of the session or routine that references them. Their lifetime is not permanent like table data.

Can I grant EXECUTE on all routines in a module at once?

Yes. GRANT EXECUTE ON MODULE module_name TO role grants the privilege on every contained procedure and function in compliant databases like Db2.

How do I drop a module safely?

Use DROP MODULE module_name; only after confirming that external applications no longer depend on its routines, because the command deletes all contained objects.