SQL procedural language (SQL PL)

SQL procedures and SQL functions are collectively referred to as SQL routines. SQL procedures are created by specifying an SQL routine body on the CREATE PROCEDURE statement. SQL functions are created by specifying an SQL routine body on the CREATE FUNCTION statement. SQL triggers are created by specifying an SQL routine body on the CREATE TRIGGER statement. A compound (dynamic) statement is defined by specifying an SQL routine body on the compound (dynamic) statement.

An SQL procedure can also be altered. The OR REPLACE option on the CREATE PROCEDURE or CREATE FUNCTION statement can be used to replace an SQL routine with a new version. You can also specify a new SQL routine body on the ALTER PROCEDURE statement or the ALTER FUNCTION statement.

An SQL routine body must be a single SQL statement which may be an SQL control statement.

The SQL routine body is the executable part of the procedure, function, or trigger that is transformed by the database manager into a program or service program. When an SQL routine, trigger, or global variable is created, SQL creates temporary source files (QTEMP/QSQLSRC and QTEMP/QSQLT00000) that will contain C source code with embedded SQL statements. When SQL creates these temporary source files, a record length of 160 is used and the CCSID value of the source statement is set as the CCSID value for the new file. If either of these source files exist, they will be modified if needed to have the same CCSID as the source. The record length of these source files should be 160 or unexpected results may occur. The name of the source file member is the same as the system name of the routine, trigger, or global variable. If DBGVIEW(*SOURCE) is specified, SQL creates the root source for the routine or trigger in source file QSQDSRC in the same library as the procedure, function, or trigger.

An SQL procedure is created as a program (*PGM) or service program (*SRVPGM) object using the CRTPGM or CRTSRVPGM command. An SQL function is created as a service program object. An SQL trigger is created as a program object. The program or service program is created in the library that is the implicit or explicit qualifier of the procedure, function, or trigger name.

When the program or service program is created, the SQL statements, including most control statements, become embedded SQL statements in the program or service program. The CALL, SIGNAL, RESIGNAL, and GET DIAGNOSTIC control statements also become embedded SQL statements in the program or service program.

The specified procedure or function is registered in the SYSROUTINES and SYSPARMS catalog tables, and an internal link is created from SYSROUTINES to the program. When the procedure is called using the SQL CALL statement or when the function is invoked in an SQL statement, the program associated with the routine is called. The specified SQL trigger is registered in the SYSTRIGGERS catalog table.