Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DML Syntax/ CALL
Updated on 2025-07-22 GMT+08:00
View PDF
Share

CALL

Function

CALL calls defined functions or stored procedures.

Precautions

If the name of a user-defined function is the same as that of a system function, you need to specify a schema when invoking the user-defined function. Otherwise, the system preferentially invokes the system function.

Syntax

1
 
CALL [schema.] {func_name| procedure_name} ( param_expr );

Parameter Description

Table 1 CALL parameters

Parameter

Description

Value Range

schema

Specifies the name of the schema to which a function or stored procedure belongs.

A string, which must comply with the naming convention. For details, see Identifier Naming Conventions.

func_name

Specifies the name of the function or stored procedure to be called.

Use an existing function name.

param_expr

Specifies a list of parameters in the function. You can use ":=" or "=& >" to separate a parameter name from its value. By doing so, parameters can be arranged in any order. If the parameter list contains only parameter values, the sequence of the parameter values must be the same as that defined in the function or stored procedure.

NOTE:

The parameters include input parameters (whose name and type are separated by IN) and output parameters (whose name and type are separated by OUT). When you run the CALL statement to call a function or stored procedure, the parameter list must contain an output parameter for non-overloaded functions. You can set the output parameter to a variable or any constant. For details, see Examples. For an overloaded package function, the parameter list can have no output parameter, but the function may not be found. If an output parameter is contained, it must be a constant.

Names of existing function parameters or stored procedure parameters.

Examples

Create the func_add_sql function to compute the sum of two integers and return the result:

1
2
3
4
5
6
 
CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
AS
BEGIN
RETURN num1 + num2;
END;
/

Transfer based on parameter values:

1
 
CALL func_add_sql(1, 3);

Transfer based on the naming flags:

1
2
 
CALL func_add_sql(num1 => 1,num2 => 3);
CALL func_add_sql(num2 := 2, num1 := 3);

Delete a function:

1
 
DROP FUNCTION func_add_sql;

Create a function with output parameters:

1
2
3
4
5
6
7
 
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:

1
 
CALL func_increment_sql(1,2,1);

Set output parameters to variables:

1
2
3
4
5
6
7
 
DECLARE
res int;
BEGIN
func_increment_sql(1, 2, res);
dbms_output.put_line(res);
END;
/

Create overloaded functions:

1
2
3
4
5
6
7
8
9
 
create or replace procedure package_func_overload(col int, col2 out int) package
as
declare
    col_type text;
begin
     col := 122;
         dbms_output.put_line('two out parameters ' || col2);
end;
/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
create or replace procedure package_func_overload(col int, col2 out varchar)
package
as
declare
    col_type text;
begin
     col2 := '122';
         dbms_output.put_line('two varchar parameters ' || col2);
end;
/

Call a function:

1
2
 
call package_func_overload(1, 'test'); 
call package_func_overload(1, 1);

Delete a function:

1
 
DROP FUNCTION func_increment_sql;
Parent Topic: DML Syntax