This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The CREATE MODULE statement creates an SQL-server Module: a Module that belongs to a Schema. The required syntax for the CREATE MODULE statement is as follows.

CREATE MODULE <Module name>
   [ NAMES ARE <Character set name> ]
   [ SCHEMA default <Schema name>]
   [ PATH <Schema name> [ {,<Schema name>}... ] ]
   [ DECLARE TABLE statement(s) ]
   <SQL-invoked routine>; ...
   END MODULE

CREATE MODULE defines a new SQL-server Module don't get this mixed up with the simple MODULE statement that is part of essential SQL, it defines an SQL- client Module and, although the two are similar, PSM statements won't work on anything but a PSM Module.

The <Module name> identifies the Module and the Schema that it belongs to. A <Module name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Module name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <Module name> must be unique within the Schema that contains it.

If CREATE MODULE is part of a CREATE SCHEMA statement, the <Module name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn't possible to create a Module belonging to a different Schema from within CREATE SCHEMA.

The optional NAMES ARE clause provides the name of the Character set that is used to express the names of all Schema Objects mentioned in the Module's definition. If you omit the clause, the Module's Character set is the default Character set of the Schema to which it belongs.

The optional SCHEMA clause names the default Schema for the Module that is, the name of the Schema that owns the Schema Objects referred to in the Module. If you omit the clause, the default <Schema name> is the name of the Schema that owns the Module.

The optional PATH clause names the Module's default path: the path used to qualify unqualified <Routine name>s that identify <routine invocation>s that are part of this CREATE MODULE statement. You must include the name of the Schema being created in the PATH clause and, if you include multiple names, all of the Schemas named must belong to the same Catalog. If you omit the clause, your DBMS will give the Module a default path that includes the name of the Schema to which the Module belongs.

The Module can contain zero or more DECLARE TABLE statements, each declaring a local temporary Table that will only be visible to this Module.

The Module can contain one or more SQL-invoked routines these do the Module's work. Here's a simple example:

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

If your memory stretches back to our chapter on SQL routines, you'll recognize the PROCEDURE statements here procedures and functions are part of essential SQL. What the CREATE MODULE Statement allows you to do is construct a "package" of procedures, in the same way that a C implementation allows the construction of a library. Our example is a rather crude attempt to "package" the main SQL-data change statements that can happen with the EMPLOYEES Table. Note: The DECLARE <keyword> before "PROCEDURE" is optional.

Comments

Comments loading...