# 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
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

## 9.9. CREATE PROCEDURE Syntax - External hosted procedures

Virtuoso provides a syntax shortcut for calling static method from hosted user defined types without first defining a Virtuoso external hosted user defined type:

    CREATE (PROCEDURE|FUNCTION) <local_name> ([<arg_def1>, ...])
    	[returns <sql_datatype>] LANGUAGE [JAVA|CLR] EXTERNAL NAME '<external_static_proc_name_literal>'

    <arg_def> := [IN|OUT|INOUT] param_name <data_type_spec>

    <external_static_proc_name_literal> = <external_type_name_literal>.<static_proc_name>

This compiles into an functional equivalent of :

    create procedure <local_name) ([<arg_def1>, ....])
    {
      declare ret any;

      exec ('
        create type <local_name>
          temporary self as ref
            static method m1 ([<arg_def1>, ....])
               returns <datatype> EXTERNAL NAME ''<static_proc_name>''
       ');

      ret := <local_name>::m1 (....);

      exec ('drop type <local_name>');

      return ret;
      }

For more details see CREATE TYPE and Runtime hosting chapters.

CREATE PROCEDURE for a Java method:

Here is an example for CREATE PROCEDURE and the hosted Java VM:

    create procedure get_property (in x varchar) returns varchar language java external name 'java.lang.System.getProperty';

Here's how that procedure is called:

    SQL> select get_property ('java.vm.name');
    callret
    VARCHAR
    _______________________________________________________________________________

    Java HotSpot(TM) Client VM

CREATE PROCEDURE for a CLR method:

Here is an example for CREATE PROCEDURE and the hosted CLR:

    create procedure curr_thr_id () returns integer language CLR external name 'mscorlib/System.AppDomain.GetCurrentThreadId';

Here's how that procedure is called:

    SQL> select curr_thr_id();
    callret
    INTEGER
    _______________________________________________________________________________

    2156

Previous CREATE ASSEMBLY Syntax - External Libraries | Chapter Contents | Next Asynchronous Execution and Multithreading in Virtuoso/PL