WL#3416: Modules

Add CREATE|ALTER|DROP MODULE, with appropriate privileges and metadata.
Modules are schema objects which contain routines.

Syntax
------

CREATE MODULE module_name
module_contents ...
END MODULE

DROP MODULE module_name [ RESTRICT | CASCADE ];

ALTER MODULE module_name
[ ADD | DROP ]
module_contents ...; /* or just procedure name */

This is SQL/PSM Feature P001 "Stored Modules".

I removed a few of the SQL:2003 clauses (for character set,
schema, path, and temporary tables).

The 'module_contents' are routines, that is, procedure
or function descriptions. Inside a module definition,
one says DECLARE PROCEDURE instead of CREATE PROCEDURE
and DECLARE FUNCTION instead of CREATE FUNCTION. But
the rest is the same.

This example is from SQL-99 Complete, Really
(copied by permission of co-author):

CREATE MODULE Employees_Module /* This begins and names the module */
  DECLARE PROCEDURE Delete_Employees()   /* This is an SQL routine */
    MODIFIES SQL DATA
    DELETE FROM Employees;
  DECLARE PROCEDURE Upate_Employees()    /* This is an SQL routine */
    MODIFIES SQL DATA
    UPDATE Employees SET col=NULL;
  DECLARE PROCEDURE Insert_Employees()   /* This is an SQL routine */
    MODIFIES SQL DATA
    INSERT INTO Employees VALUES (5);
END MODULE

The namespace of the procedure is the schema, not the module.
So you would say "CALL Update_Employees()" not
"CALL module_name.Update_Employees()", and the CREATE
MODULE statement would fail if there was already a
procedure named Update_Employees().

If you're using delimiters, then the final delimiter comes after
END MODULE, not at the end of each routine.

A module does not have all the conventional programming features,
for example you cannot DECLARE variables at the start which will
be accessible to all routines. The only advantage of a module is
that it makes it possible to treat all routines as a unit for
ALTER, DROP, GRANT, REVOKE. Modules are "containers" within
schemas.

Privileges
----------

Granting a privilege on a package means granting a privilege on
all routines in the package. Example:

GRANT EXECUTE ON module_name TO joe@localhost;

Metadata
--------

There will be a MODULES view, containing:
CATALOG             NULL
MODULE_SCHEMA       VARCHAR
MODULE_NAME         VARCHAR
MODULE_DEFINITION   VARCHAR

SQL:2003 also mentions MODULE_COLUMN_USAGE, MODULE_TABLE_USAGE,
MODULE_PRIVILEGES.

Oracle's CREATE PACKAGE
-----------------------

SQL:2003's CREATE MODULE and Oracle's CREATE PACKAGE have the
same general purpose, but Oracle emphasizes the specification
part -- one can create the names and parameters in the CREATE
PACKAGE and the routine implementations outside it. This can
make it possible to define interface before implementation.
An example from the Oracle manual:
CREATE OR REPLACE PACKAGE emp_mgmt AS 
FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2, 
   manager_id NUMBER, salary NUMBER, 
   commission_pct NUMBER, department_id NUMBER) 
   RETURN NUMBER; 
FUNCTION create_dept(department_id NUMBER, location_id NUMBER) 
   RETURN NUMBER; 
PROCEDURE remove_emp(employee_id NUMBER); 
PROCEDURE remove_dept(department_id NUMBER); 
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER); 
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER); 
no_comm EXCEPTION; 
no_sal EXCEPTION; 
END emp_mgmt; 
/ 

We will not do CREATE PACKAGE.

References
----------

CREATE MODULE (Mimer):
http://developer.mimer.com/documentation/html_92/Mimer_SQL_Mobile_DocSet/SQL_Statements25.html

CREATE MODULE (Oracle Rdb)
http://www.kuzbass.ru:8086/docs/rdb702/oraclerdb/sqlrm7/sql_pro_092.html#createmodulestatement

CREATE PACKAGE (Oracle):
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6006.htm#sthref5148

SQL-99 Complete, Really, pages 500-502

Feature requests in bugs.mysql.com:
#3503: CREATE PACKAGE with procedures and functions
#11696 Please add CREATE MODULE syntax, or Oracle PACKAGE equivalent