CREATE MODULE
NAME
CREATE MODULE: define an SQL-server module, a set of routines and temporary tables of their own
SYNOPSIS
<SQL-server module definition> ::=
CREATE MODULE <SQL-server module name>
[ NAMES ARE <character set name> ]
[ SCHEMA <unqualified schema name> ]
[ PATH <schema name> [ { , <schema name> }... ] ]
[ <temporary table declaration>... ]
<module routine> ; [ { <module routine> ; }... ]
END MODULE
<module routine> ::=
[ DECLARE ] { <SQL-invoked procedure> | <SQL-invoked function> }
A <temporary table declaration> is that of DECLARE LOCAL TEMPORARY
TABLE. An <SQL-invoked procedure>
or <SQL-invoked function> is what follows CREATE in CREATE
PROCEDURE or CREATE FUNCTION.
DESCRIPTION
CREATE MODULE defines a module of a schema: its routines, which are invoked as the schema's routines are, by their names in the module's schema, and temporary tables its routines alone see. The module is a schema element. DROP MODULE drops it whole, with its routines and tables.
A module's routine is no schema-level routine: DROP ROUTINE does not
drop it, and no privilege is granted on it. EXECUTE on the module
invokes each of its routines. The owner of the module's schema holds
it, granted by _SYSTEM, with grant option once it holds with grant
option every privilege the routines need.
A temporary table the module declares is named MODULE.T within the module. Each session has its own instance of it, as of a declared local temporary table, and ON COMMIT DELETE ROWS, the default, empties it as each transaction ends. Outside the module MODULE.T names the session's own declared tables, and no view of the Information Schema shows the module's.
SCHEMA names the schema a name written without one stands for within the module: a table's, a type's, a sequence generator's. It is the module's own schema if not written. PATH lists the schemas a routine name written without a schema is looked for in, in order. A function invoked is of the first schema of the path that has one the arguments fit, and a procedure called the first of its name and number of arguments. Without PATH, the path is the module's schema.
NAMES ARE names the module's character set, SQL_TEXT, the one there is.
CREATE MODULE runs within the session's transaction: ROLLBACK undoes it. INFORMATION_SCHEMA.MODULES shows the module, and ROUTINES its routines, with MODULE_CATALOG, MODULE_SCHEMA and MODULE_NAME the module's name and SCHEMA_LEVEL_ROUTINE NO.
RULES
- The schema has no module of the name, and its user owns the schema. In a CREATE SCHEMA, the module is of the schema defined.
- Each routine is of the module's schema, and the schema has no other routine of its specific name.
- The module declares no table twice.
- The path names each schema once, each of this catalog.
- NAMES ARE names SQL_TEXT.
- The transaction is not READ ONLY.
PRIVILEGES
The schema is its user's. Its user holds the privileges each routine's body needs, as for CREATE FUNCTION and CREATE PROCEDURE.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -102 | The text is not a module definition: no routine, no semicolon after one, no END MODULE |
| 2A000 | -103 | A rule above is broken, or a routine's body breaks a rule of CREATE FUNCTION or CREATE PROCEDURE |
| 2A000 | -104 | Its user does not own the schema, or holds not a privilege a routine's body needs |
| 0A000 | -900 | NAMES ARE names a character set other than SQL_TEXT |
| 25000 | -917 | The transaction is READ ONLY |
EXAMPLES
CREATE SCHEMA LIB CREATE FUNCTION TAX (AMOUNT DECIMAL(9,2)) RETURNS DECIMAL(9,2) CONTAINS SQL RETURN AMOUNT * 0.2;
CREATE TABLE ORDERS (ID INTEGER, AMOUNT DECIMAL(9,2));
CREATE MODULE SHOP PATH LIB, ANN
DECLARE LOCAL TEMPORARY TABLE MODULE.PLACED (ID INTEGER) ON COMMIT PRESERVE ROWS
PROCEDURE PLACE (IN ID INTEGER, IN AMOUNT DECIMAL(9,2)) MODIFIES SQL DATA
BEGIN
INSERT INTO ORDERS VALUES (ID, AMOUNT + TAX(AMOUNT));
INSERT INTO MODULE.PLACED VALUES (ID);
END;
FUNCTION PLACED () RETURNS INTEGER READS SQL DATA
RETURN (SELECT COUNT(*) FROM MODULE.PLACED);
END MODULE;
CALL PLACE(1, 10);
CALL PLACE(2, 20);
COMMIT;
SELECT ID, AMOUNT, PLACED() AS PLACED FROM ORDERS ORDER BY ID;
SELECT ID FROM MODULE.PLACED;
SELECT ROUTINE_NAME, MODULE_NAME, SCHEMA_LEVEL_ROUTINE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'ANN';
SELECT MODULE_NAME, DEFAULT_SCHEMA_NAME, SQL_PATH FROM INFORMATION_SCHEMA.MODULES;
OK
OK
OK
OK
OK
OK
ID | AMOUNT | PLACED
--------------+-------------+--------------
1 | 12.00 | 2
2 | 24.00 | 2
(2 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): table MODULE.PLACED is not defined [X3.135-1986 §5.4 SR5] (at character 15)
ROUTINE_NAME | MODULE_NAME | SCHEMA_LEVEL_ROUTINE
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
PLACE | SHOP | NO
PLACED | SHOP | NO
(2 rows)
MODULE_NAME | DEFAULT_SCHEMA_NAME | SQL_PATH
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
SHOP | ANN | PATH LIB, ANN
(1 row)
STANDARD
ISO/IEC 9075-4:2006 draft §9.21, §9.24, §12.8, Feature P001. SQL-86, SQL-92 and ISO/IEC 9075-2 have no SQL-server modules.
SEE ALSO
DROP MODULE, CREATE PROCEDURE, CREATE FUNCTION, GRANT, Information Schema