Subprograms

A subprogram is a procedural language (PL) unit that contains a collection of SQL statements and PL statements. You can use a subprogram to resolve specific issues or execute a group of related tasks. A subprogram can contain parameters, and callers pass values to these parameters. A subprogram can be a stored procedure or a function. In most cases, you can use a stored procedure to perform an operation and a function to calculate and return a value.

A stored subprogram is a subprogram stored in a database to perform complex logic operations for different database applications. The MySQL mode supports only standalone subprograms that are created at the schema level.

Notice

Subprograms in MySQL mode meet the requirements for stored programs in the SQL standard and differ from PL subprograms in Oracle mode in terms of syntax and features.

Structure of a subprogram

The structure of a subprogram is the same as that of a PL block, which consists of the following parts:

• Declarative part (optional)

  This part contains declarations for types, constants, variables, exceptions, explicit cursors, and nested subprograms. These items are local to the subprogram and cease to exist after the subprogram is executed.

• Executable part (required)

  This part contains statements for value assignment, execution control, and data manipulation.

• Exception-handling part (optional)

  This part contains code for handling exceptions or runtime errors.

You can add comments anywhere in a subprogram to improve the readability of the subprogram. The comments will be ignored by the compiler. A single-line comment starts with double hyphens (--) and extends to the end of the line. A multi-line comment starts and ends with a combination of a forward slash and an asterisk (/*).

The following sample code shows the structure of a stored procedure:

PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

Compared with a stored procedure, a function contains at least one RETURNS clause. Structure of a function:

FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

Benefits of subprograms

Compared with client applications, subprograms have the following benefits:

• Better performance

  The cost generated by network transmission is reduced. Code can be compiled in advance. You can also use the cache mechanism to reduce performance consumption during execution.

• Lower memory consumption

  The shared memory mechanism of the database reduces memory consumption if multiple users execute the same stored procedure.

• Increased productivity

  A stored procedure can help reduce code logic and increase productivity.

• Enhanced security

  You can enhance security by specifying the privilege information of stored procedures.

• Inheritability

  You can access stored procedures that are defined by other users after you obtain relevant privileges.

Execution of a subprogram

You can use one of the following methods to execute a subprogram:

• Use MySQL tools.

• Use database applications.

• Use another stored procedure or a trigger.

Metadata of a subprogram

You can use one of the following methods to obtain subprogram metadata:

• Query the ROUTINES view in INFORMATION_SCHEMA to obtain all subprograms.

• Execute the SHOW CREATE PROCEDURE statement to view the definition of a stored procedure.

• Execute the SHOW CREATE FUNCTION statement to view the definition of a function.

• Execute the SHOW CREATE TRIGGER statement to view the definition of a trigger.