CREATE PROCEDURE/FUNCTION

Defines a new stored routine.

NOTE: User Defined Functions implemented in 3.4.

NOTE: valentina_python and valentina_JS SP/UDF implemented in v.14.0

create_procedure_stmt
    :    CREATE PROCEDURE [ OR REPLACE ] proc_name sql_proc_parameter_declaration_list
         [ proc_characteristics ]		// new in v14.0
         {    AS character_string_literal	// new in v14.0, NSTD,
         |    sql_routine_body			// up to v13.3 here there was block_nstd_stmt.
         }
	
create_function_stmt
    :    CREATE FUNCTION [ OR REPLACE ] proc_name sql_func_parameter_declaration_list
         returns_clause
         [ routine_characteristics ]		// new in v13.5
         {   AS character_string_literal	// new in v14.0, NSTD,
         |   sql_routine_body			// up to  v13.3 here there was block_nstd_stmt.
         }


// -------------------------
// details
// -------------------------

sql_proc_parameter_declaration_list
    :    ( [ sql_proc_parameter_declaration, ... ] )

sql_func_parameter_declaration_list
    :    ( [ sql_func_parameter_declaration, ... ] )
		
sql_proc_parameter_declaration 
    :    [sql_proc_parameter_mode] param_name data_type [ = default_option ]

sql_func_parameter_declaration
    :    param_name data_type [ = default_option ]

sql_proc_parameter_mode
    :    IN | OUT | INOUT

returns_clause
    :    RETURNS data_type

proc_characteristics					// NSTD
    :    language_clause

routine_characteristics
    :    routine_characteristic, ...

routine_characteristic
    :    deterministic_characteristic
    |    language_clause


deterministic_characteristic
    :    CONST | IMMUTABLE | STABLE | VOLATILE

language_clause
    :    LANGUAGE
         {    SQL
         |    VALENTINA_PYTHON
         |    VALENTINA_JS
         )


// --- 13.5 <SQL_procedure_stmt> [1992] --------------------------------------------------------
//
// Define all of the SQL-statements that are <SQL procedure statement>s.
//
sql_routine_body										// by trigger, event, procedure
	:	block_nstd_stmt			// BEGIN - END block
	|	sql_statement			// single SQL command
	;

Recreate the procedure if it already exists. You can use this option to change the definition of an existing procedure without first dropping it.

NOTE: 'OR REPLACE' syntax Valentina has taken from Oracle/Postgre world.

The name of a procedure. It must be unique in the scope of the database.

The name of the parameter. Must be unique in the scope of this procedure.

• IN - this parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns.
• OUT - this parameter passes a value outside of the procedure. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns.
• INOUT - this parameter passes a value in both directions. It is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns

For each OUT or INOUT parameter, pass a user-defined variable, so that you can obtain its value when the procedure returns.

If not specified, then IN is used. For FUNCTION(s) parameters always are IN.

New in v13.5. Not standard.

Every expression has a deterministic classification, it can be CONST, IMMUTABLE, STABLE, or VOLATILE (VOLATILE is the default one). The deterministic level is a promise to the VKernel about the behavior of the expression:

• A VOLATILE (not deterministic). Indicates that the expression result can be changed even within a single table scan, so no optimizations can be made. Relatively few SQL functions are volatile in this sense. But note, any function that has side effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away.

    Example: rand(), now(), timeofday()....
	
    - possibly side-effects (send mails, notifications, change db...)
    - or at least one arg is VOLATILE.
    - or indeterministic implementation.

• A STABLE - state-sensitive-determinism (conditionally deterministic).Indicates that the expression has no side effects, and within a single table scan it will consistently return the same result for the same argument values (but its result could be changed across SQL statements).

This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current date-time format), etc.

    Examples: version(), format(), userID(), any aggregation function...

    - no side-effects (send mails, notifications, change db...);
    - args are at least STABLE.	

Note - It is fine to calculate it only once per SQL statement instead of many times because future calculations would produce the same result. But a materialized calculated column or index can't accept this function as deterministic because a little change in the state would turn invalid all its pre-calculated and stored values.

• An IMMUTABLE - state-insensitive-determinism (a strict form of determinism). The expression cannot modify the database and always returns the same result when given the same argument values. It does not do database lookups or otherwise use information not directly present in its argument list (including special hidden arguments like RecID).

    Example: "f1+f2", SIN(f1), SIN(f1+f2), SIN(f1+0) ...
	
    - no side-effects (send mails, notifications, change db...);
    - args are at least IMMUTABLE.

So, we can use it in storable things like index, materialized views, and cache results in optimizers.

• A CONST - the strongest form of determinism, because the result does not depend even on hidden arguments (like RecID) - it is always the same.

    Example: "1+2", SIN(PI()), SIN(0), SIN(1-1) ...
	
    - no side-effects (send mails, notifications, change db...);
    - args are CONST;
    - always the same result regardless of args or "hidden args".	

So, we can use it in storable things like index, and materialized views, and we can optimize the expression caching its result.

Each internal SQL function has a predefined deterministic level, but you should set it yourself for user-defined functions (UDFs). The default level is VOLATILE. There are a few reasons why Valentina would not calculate this level itself:

• it could be time-consuming calculations;
• any possible branch must be calculated, and all of them are equally important. So if you do something like “SEND MAIL” (side effect) in some “guard” (some “else” or “catch” block which is merely executed) the whole UDF will be marked as a VOLATILE. But, most probably you want to ignore those rare cases and consider UTF as IMMUTABLE.

So, it's up to you - what level of determinism to choose for your UDF.

Note, currently, you will receive a warning like

WARNING: Calculated field should not be indexed because it is not deterministic.

in case of using STABLE or VOLATILE expressions in indexes or constraints. You should fix it either by marking UDF as an IMMUTABLE or CONST or dropping such index or constraint. On the other hand if you mark UDF as CONST - VKernel may optimize it to call only once.

New in v13.3. Not standard.

Added to support syntax e.g. in MS SQL and PostgreSQL.

Any valid SQL type that we can use in the definition of a Table Field.

For functions, this specifies the type of returned value.

SQL Standard expects a single SQL command at this place, such as INSERT, or SELECT.

In the Valentina SQL is allowed to have here a COMPOUND STATEMENT, i.e. one or few SQL statements wrapped by BEGIN/END. Compound statements can contain declarations, if, loops, exception handlers, and other control structure statements.

Since v14 ValentinaDB supports SP/UDF's body not only in SQL but also in Python or JavaScript. If language_clause is omitted - “SQL” is the default value.

• User-defined SQL FUNCTIONs can be used in all places where can be used built-in Valentina functions:
  • in the EXPRESSIONS, e.g. SELECT func(fld)…
  • in Table Methods.

[New in v14]

Starting with v14 you can write Python/JS Stored Procedures (SP) and User-Defined Functions (UDF) in the Valentina DB.

• Since there are no configurable security restrictions (especially for valentina_python) the user must take care that it cannot be used to do anything unwanted.
• The Python/JS code that is given as the body of the SP/UDF definition is internally transformed into a Python/JS function.
• When the SP/UDF is called, its “SQL arguments” are mapped to the “python/JS generated function arguments”.
• The result value (for UDF) is also transferred back to the SQL.
• IN, IO, OUT params behave like in SQL SP/UDF.
• The correspondence type for SQL-null is “None” for valentina_python, and “null” for valentina_JS.

In Valentina, SP/UDF bodies (referred to as sp_body) must be marked using the following syntax:

• For SQL-based SP/UDF we continue to use BEGIN-END block. Notice that the 'LANGUAGE SQL' clause is optional.

... LANGUAGE SQL
BEGIN
    sp_body
END

• For Valentina Python or Valentina JS bodies we use character_string_literal, i.e. single-quoted text:

... LANGUAGE valentina_python/valentina_JS
AS 
'
    sp_body
'

In v14 we have added Dollar-Quoted Literal, similar to PostgreSQL. This can be more comfortable to avoid many escapes.

... LANGUAGE valentina_python/valentina_JS
AS 
$$
    sp_body
$$

• There is always a hidden param - “valentina.database”, which is the current database reference. So you may execute Valentina's SQL statement within Python/JS bodies.

-- Creating an SP that sets a "user/session" variable:
CREATE PROCEDURE sp1() LANGUAGE valentina_python AS $$valentina.database.sqlExecute( 'set @v1 = 10;' )$$;
CALL sp1();
SELECT @v1;
-- Output: 10

CREATE PROCEDURE sp1() LANGUAGE valentina_python AS 
$$
    valentina.database.sqlExecute( 'CREATE TABLE t1 ( f1 long )' );
    cursor = valentina.database.sqlSelect( 'select 10;' )
    valentina.database.sqlExecute( F'INSERT INTO t1 VALUES ({cursor[0][0]})' );
$$;
CALL sp1();
SELECT * FROM t1;
-- Output: 10

-- Return array of values:
CREATE FUNCTION sp1() RETURNS BOOLEAN[3] LANGUAGE valentina_python
AS $$
  return [True,None,False] 
$$

CREATE FUNCTION sp2() RETURNS BOOLEAN[3] LANGUAGE valentina_JS
AS 
$$
  return [true,null,false] 
$$

--
SELECT sp1(), sp2()
-- Output: 2 columns of array type:
-- [True,Null,False], [True,Null,False] 

• Raising exceptions from JS/Python:

CREATE PROCEDURE sp1() LANGUAGE valentina_python AS $$raise NameError('HiThere')$$;
CREATE PROCEDURE sp2() LANGUAGE valentina_JS AS $$throw 'HiThere';$$;
-- You will get Valentina's exception with error_code==ERR_USER_EXCEPTION (0x50000) and error_message=="HiThere"
 
CREATE PROCEDURE sp3() LANGUAGE valentina_python AS $$valentina.throwException( 50001, 'Some user error' )$$;
CREATE PROCEDURE sp4() LANGUAGE valentina_JS AS $$valentina.throwException( 50001, 'Some user error' )$$;
-- You will get Valentina's exception with error_code==0x50001 and error_message=="Some user error"
 
CREATE TABLE t1 ( id long UNIQUE );
INSERT INTO t1 VALUES( 1 );
CREATE PROCEDURE sp5() LANGUAGE valentina_python AS $$valentina.database.sqlExecute( 'INSERT INTO t1 VALUES (1)' )$$;
CREATE PROCEDURE sp6() LANGUAGE valentina_JS AS $$valentina.database.sqlExecute( 'INSERT INTO t1 VALUES (1)' )$$;
-- You will get Valentina's exception with error_code==ERR_CONSTRAINT_UNIQUE_VIOLATION and error_message=="Unique constraint violation occurred, object t1.id, unique index U_t1_id."

Example

CREATE PROCEDURE sp1()
BEGIN
    INSERT INTO t1(f1) VALUES( 1 );
END

CREATE PROCEDURE sp1( IN inValue long )
BEGIN
    SET inValue = inValue + 1;
    INSERT INTO t1(f1) VALUES( inValue );
END

Example

Call of one procedure from another:

CREATE PROCEDURE sp1( IN inValue long )
BEGIN
    INSERT INTO t1(f1) VALUES( inValue );
END
 
CREATE PROCEDURE sp2( IN inValue long )
BEGIN
    CALL sp1( inValue );
END

Example

CREATE PROCEDURE ABS( INOUT ioValue long )
BEGIN
    IF ioValue < 0 THEN
         ioValue = -ioValue; 
    END IF
END
 
CREATE PROCEDURE sp2( IN inValue long )
BEGIN
    DECLARE a INT;
 
    SET a = -100;
    CALL ABS( a );
 
    PRINT a;  // printed VALUE IS 100.
END

CREATE FUNCTION ABS22( inValue LONG )
RETURNS LONG IMMUTABLE
BEGIN
    IF inValue >= 0 THEN
         RETURN inValue;
    ELSE 
         RETURN -inValue;
    END IF
END
 
SELECT ABS22(-68);

CREATE FUNCTION sp1() RETURNS VARIANT 
BEGIN
    DECLARE var VARIANT;
    SET var = ARRAY[TRUE,TRUE,NULL,FALSE,FALSE,FALSE,FALSE];
    RETURN var;
END

CREATE FUNCTION sp1( arg LONG ) RETURNS VARIANT
BEGIN 
    DECLARE var VARIANT; 
    SET var = SELECT fldArray FROM T WHERE RecID = arg;
    RETURN var;
END

If a stored routine contains SELECT stmt, which is single or the last, then such routine will return you a VCursor if you call it from some ADK using VDatabase.SqlSelect() or VDatabase.SqlQuery() function.

Example

Let you have a Stored Procedure with the SELECT command.

CREATE PROCEDURE sp1( IN param Long ) 
BEGIN 
    SELECT f1, f2 FROM t1 WHERE f1 = param; 
END

Later, in the code of some Valentina ADK, you call it using VDatabase.SqlSelect() or VDatabase.SqlQuery() function. You will get a VCursor object, which you can use in the “normal” way.

VCursor curs = pSqlDb.SqlSelect( "CALL sp1(10)" );
 
if( curs.recordCount > 0 )
  ...

See also: Valentina API Reference