Call a Stored Procedure

Registered stored procedures and stored functions can be called using the CALL statement or in SQL statements. In general, stored procedures are called using the CALL statement, and stored functions are used in SQL statements where a return value is needed.

This chapter provides a detailed explanation of the considerations when calling stored procedures and stored functions.

CALL Statement

You can call a stored procedure using the CALL statement as follows in the CSQL interpreter.

-- Execute in csql
CREATE PROCEDURE hello ()
AS
BEGIN
    DBMS_OUTPUT.put_line('Hello, CUBRID!');
END;

;server-output on

-- Call
CALL hello();

  Result
======================
  NULL

<DBMS_OUTPUT>
====
Hello, CUBRID!

Calling in SQL Statements

In the case of calling in SQL statements, stored functions are typically used to return values. The following example shows how to calculate the number of medals for a specific country using a stored function.

CREATE FUNCTION count_medals(nation STRING) RETURN INT
AS
cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt
    FROM game
    WHERE nation_code = nation;
    RETURN cnt;
END;

SELECT count_medals('USA');
SELECT count_medals('KOR');

  count_medals('USA')
=====================
                 1118

  count_medals('KOR')
=====================
                  316

Limitations on Nested Procedure Calls

The maximum allowed nested procedure call limit is 16. Nested procedure call refers to calling another procedure from within a procedure. In the case of recursive calls, the limit is not applied.

The following is an example of the limitations on nested procedure calls.

CREATE OR REPLACE FUNCTION test_factorial_query(n BIGINT) RETURN BIGINT
AS
k BIGINT;
BEGIN
    IF n = 0 THEN
        RETURN 1;
    ELSE
        SELECT test_factorial_query(n - 1) INTO k;
        RETURN n * k;
    END IF;
END;

SELECT test_factorial_query(15);
SELECT test_factorial_query(16);

  test_factorial_query(15)
==========================
             1307674368000


-- SELECT test_factorial_query(16);
ERROR: Stored procedure execute error:
  ...
  (line 8, column 13) Stored procedure execute error:
  (line 8, column 13) Stored procedure execute error: Too many nested stored procedure call.

The following is an example of a recursive call that does not go through a query. In this case, the nested call limit is not applied. Recursively calling the following example may result in an overflow of the BIGINT value or excessive use of system resources, so be careful.

CREATE OR REPLACE FUNCTION test_factorial_constant(n BIGINT) RETURN BIGINT
AS
k BIGINT;
BEGIN
    IF n = 0 THEN
        RETURN 1;
    ELSE
        RETURN n * test_factorial_constant(n - 1);
    END IF;
END;

SELECT test_factorial_constant(16);
SELECT test_factorial_constant(25);

  test_factorial_constant(16)
=============================
               20922789888000

-- SELECT test_factorial_constant(25);
ERROR: Stored procedure execute error:
  (line 8, column 20) data overflow in multiplication of BIGINT values