• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CALL

CALL

Function

CALL calls defined functions.

Precautions

None

Syntax

CALL [schema.] func_name ( param_expr );

Parameter Description

  • schema

    Specifies the function schema.

  • func_name

    Specifies the name of a specific function to be called.

    Value range: An existing function name.

  • param_expr

    Specifies a list of parameters in the function. Use := or => to separate a parameter name and its value. This method allows parameters to be placed in any order. If only expr is in the parameter list, the parameter order must be the same as that defined in the function.

    Value range: An existing function parameter name.

    NOTE:

    Parameters of a function include input parameters (whose name and type are separated by IN) and output parameters (whose name and type are separated by OUT). When you execute the CALL statement to call a function, the parameter list must contain an output parameter. You set the output parameter to a variable or any constant. For details, see the following example.

Examples

-- Create the func_add_sql function to compute the sum of two integers and return the result:
CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
AS
BEGIN
RETURN num1 + num2;
END;
/

-- Transfer based on parameter values:
CALL func_add_sql(1, 3);

-- Transfer based on the naming flags:
CALL func_add_sql(num1 => 1,num2 => 3);
CALL func_add_sql(num2 := 2, num1 := 3);

-- Delete the function:
DROP FUNCTION func_add_sql;

-- Create a function with output parameters:
CREATE FUNCTION func_increment_sql(num1 IN integer, num2 IN integer, res OUT integer)
RETURN integer
AS
BEGIN
res := num1 + num2;
END;
/

-- Set output parameters to constants:
CALL func_increment_sql(1,2,1);

-- Set output parameters to variables:
DECLARE
res int;
BEGIN
func_increment_sql(1, 2, res);
dbms_output.put_line(res);
END;
/

-- Delete the function:
DROP FUNCTION func_increment_sql;