CREATE PROCEDURE/FUNCTION

Defines a new stored routine.

NOTE: User Defined Functions implemented in 3.4.

procedure_definition
    :    CREATE [OR REPLACE] PROCEDURE proc_name ( [proc_parameter [, ...]] )
         routine_body

function_definition
    :    CREATE [OR REPLACE] FUNCTION func_name ( [func_parameter [, ...]] )
         RETURNS type
         routine_body

proc_parameter : [ IN | OUT | INOUT ] param_name type

func_parameter : param_name type

type
    : any valid Valentina data type

routine_body
    : valid SQL procedure statement

Recreates 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 have taken from Oracle/Postgre world.

The name of a procedure. Must be unique in the scope o database.

The name of 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.

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

For functions, this specifies the type of returned value.

This is a valid SQL procedure statement. This can be a simple statement such as or INSERT, or, usually, this is a COMPOUND STATEMENT, i.e. one or few SQL statements wrapped by BEGIN/END. Compound statements can contain declarations, loops, and other control structure statements.

• 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.

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
BEGIN
    IF inValue >= 0 THEN
         RETURN inValue;
    ELSE 
         RETURN -inValue;
    END IF
END
 
SELECT ABS22(-68);