PolarDB:Subprograms

Introduction

A program includes functions and procedures. A program that is created in a PL/SQL block is called a nested subprogram. You can create a subprogram in a PL/SQL block or a package. The PL/SQL block can be another subprogram. You can also globally create a subprogram. You can declare and define a program at the same time. Alternatively, you can declare a program before you define it in the same block, which is called forward declaration. You can declare a global program by using CREATE FUNCTION or CREATE PROCEDURE. You can also declare and define a program in a package. In most cases, the program is declared at the package header and defined at the package body. Whether you declare a global program or declare and define a program in a package, the program is stored in the system table. And other nested subprograms are referred to as subprograms. The subprogram is not stored in the system table, and the subprogram is compiled and executed only if it is called in the nested block.

Declaration of subprograms

You can declare a subprogram by using the following syntax:

DECLARE -- Outer anonymous block, which can also be a global procedure.

  [FUNCTION] -- Subfunction.
      name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  {RETURNS | RETURN} rettype 
  {AS | IS}
  [DECLARE] -- The declaration section of the subfunction.
    ...
  BEGIN -- The program section of the subfunction.
    ...
    RETURN ...
  END;

BEGIN -- Outer program section.

END;

Use of subprograms

The following sample code provides an example on how to use subprograms.

DECLARE
  a INT;
  FUNCTION local_func RETURN INT -- Declares a subfunction.
  IS
  BEGIN
    RETURN 10;
  END;
BEGIN
  a := local_func(); -- Calls the subfunction.
  RAISE NOTICE 'a: %', a;
END;

Functions can be overloaded, which means that functions with the same function name can have different parameter lists. For this reason, the system usually searches for a series of candidate functions in the system table based on local_func, and then selects the most matching parameter list to execute. In some cases, no function is matched. During this process, the system preferentially searches for the matching subfunction by layer. The following sample code shows how the system searches for the most matching subfunction.

DECLARE
    PROCEDURE local_proc1 IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure1';
    END;

    PROCEDURE local_proc2 IS
        proc2_str VARCHAR(50);
        FUNCTION local_func RETURN VARCHAR IS
            func_str VARCHAR(50) := 'raise inner local_func';
        BEGIN
            local_proc1(); -- The outer local_proc1 is found.
            RETURN func_str;
        END;
    BEGIN
        proc2_str := local_func();
        raise notice '%', proc2_str;
    END;
BEGIN
    local_proc2();
END;

A response that is similar to the following one is returned:

NOTICE:  call outer local_procedure1
NOTICE:  raise inner local_func

If the system finds at least one subfunction with the same name in a certain layer, the system stops the search and selects the most matching one among the subfunctions to execute. However, if the system finds the subfunctions with the same name but fails to select the most matching one, an error is reported.

DECLARE
    PROCEDURE local_proc1 IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure1';
    END;

    PROCEDURE local_proc2 IS
        proc2_str VARCHAR(50);
        FUNCTION local_func RETURN VARCHAR IS
            func_str VARCHAR(50) := 'raise inner local_func';
        BEGIN
            local_proc1(1); -- The outer local_proc1 is found but cannot be matched.
            RETURN func_str;
        END;
    BEGIN
        proc2_str := local_func();
        raise notice '%', proc2_str;
    END;
BEGIN
    local_proc2();
END;

A response that is similar to the following one is returned:

ERROR:  wrong number or types of arguments in call to local function local_proc1

Forward declaration

If nested subprograms in the same PL/SQL block call each other, forward declaration is required because the subprogram must be declared before it can be called. If the subprogram is not defined in the same PL/SQL block after the declaration, an error is reported.

DECLARE
    FUNCTION func_test(id INT) return INT;  -- Only declares the subprogram, without defining it.
BEGIN
END;

A response that is similar to the following one is returned:

ERROR:  subroutine body must be defined for forward-declared function "func_test"

The following sample code provides an example on how to use forward declaration.

DECLARE
  PROCEDURE proc1(number1 NUMBER); -- Declares proc1.

  PROCEDURE proc2(number2 NUMBER) IS -- Declares and defines proc2.
  BEGIN
    proc1(number2); -- Calls proc1.
  END;

  -- Define proc 1:
  PROCEDURE proc1(number1 NUMBER) IS -- Defines proc1.
  BEGIN
    proc2 (number1); -- Calls proc2.
  END;

BEGIN
  NULL;
END;

Use outer program variables

In the Use of subprograms section of this topic, an example on how to use the outer subfunction is provided. All the variables, cursors, and custom exceptions that are declared before the subprogram is declared can be directly used. You can set the values of the variables or assign values to the variables, fetch rows from the cursor you enable, or use an outer custom exception to throw an exception. The following sample code shows the design of variable visibility.

DECLARE
    a INT := 1; -- The variable a is available.
    PROCEDURE local_proc IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure';
        RAISE NOTICE 'inner raise a: %', a;
        a := 10;
    END;
    b INT; -- The variable b is unavailable.
BEGIN
    RAISE NOTICE 'outer raise a: %', a;
    local_proc();
    RAISE NOTICE 'outer raise a: %', a;
END;

A response that is similar to the following one is returned:

NOTICE:  outer raise a: 1
NOTICE:  call outer local_procedure
NOTICE:  inner raise a: 1
NOTICE:  outer raise a: 10

If you use the variables that are declared after local_proc, a syntax error occurs. The following sample code shows an example.

DECLARE
    a INT := 1; -- The variable a is available.
    PROCEDURE local_proc IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure';
        RAISE NOTICE 'inner raise a: %', a;
        a := 10;
    END;
    b INT; -- The variable b is unavailable.
BEGIN
    local_proc();
END;

A response that is similar to the following one is returned:

ERROR:  "b" is not a known variable