# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Virtuoso PL Modules
Syntax Security
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

## 9.17. Virtuoso PL Modules

Modules are packages of procedures which compile together. Procedure names in module definitions are not fully qualified names, but consist only of a single identifier that it is appended to the name of the module (which is a 3-part name) to make the 4-part module procedure name.

Module procedures do not appear in SQLProcedures output. Module names are in the same domain as the procedure names, so it is not possible to have a procedure with the same name as an existing module.

### 9.17.1. Syntax

    CREATE MODULE
      m_name
    {
      [PROCEDURE|FUNCTION] p_name1 (...) { ...};
      [PROCEDURE|FUNCTION] p_name2 (...) { ...};
      ...
      [PROCEDURE|FUNCTION] p_nameN (...) { ...};
    }

    DROP MODULE m_name;

Procedure Modules

    create module
      DB.DBA.MOD
    {
      function MOD1 () returns varchar {
        return ('MOD1');
      };

      procedure MOD2 () {
        return concat (MOD1(), 'MOD2');
      };
    };

This example creates a module, MOD, with 2 procedures: MOD1 & MOD2. Their fully-qualified names are DB.DBA.MOD.MOD1 and DB.DBA.MOD.MOD2.

Note the call to MOD1 in MOD2 - it is not fully qualified, but it resolves to the module procedure MOD1, instead of any procedure external to the module.

A single part procedure name in a call inside a module is first matched against procedures defined in the module. If the above example were executed by DBA (in the DB qualifier), then the below statements are equivalent:

    select DB.DBA.MOD.MOD1()
    select DB..MOD.MOD1()

The statement:

    select MOD.MOD1()

will result in calling the DB.DBA.MOD.MOD1() only if a function DB.MOD.MOD1 does not exist. If it exists, it will be preferred over DB.DBA.MOD.MOD1 when using this notation.

### 9.17.2. Security

Module procedures can be granted to users. Modules can also be granted to users. Granting execute to a module is equivalent to granting execute for all of the module's procedures.

Previous Virtuoso/PL Scrollable Cursors | Chapter Contents | Next Handling Conditions In Virtuoso/PL Procedures