CREATE PROCEDURE statement (PL/SQL)
The CREATE PROCEDURE statement defines a procedure that is stored in the database.
Invocation
This statement can be executed from the Db2® command line processor (CLP), any supported interactive SQL interface, an application, or a routine.
Authorization
- If the schema name of the procedure does not exist, IMPLICIT_SCHEMA authority on the database
- If the schema name of the procedure refers to an existing schema, CREATEIN privilege on the schema
- DBADM authority
The authorization ID of the statement must be the owner of the matched procedure if OR REPLACE is specified (SQLSTATE 42501).
Syntax
Description
- PROCEDURE procedure-name
- Specifies an identifier for the procedure. The unqualified form
of procedure-name is an SQL identifier with a maximum
length of 128. In dynamic SQL statements, the value of the CURRENT
SCHEMA special register is used to qualify an unqualified object name.
In static SQL statements, the QUALIFIER precompile or bind option
implicitly specifies the qualifier for unqualified object names. The
qualified form of procedure-name is a schema name
followed by a period character and an SQL identifier. If a two-part
name is specified, the schema name cannot begin with 'SYS'; otherwise,
an error is returned (SQLSTATE 42939).
The name (including an implicit or explicit qualifier), together with the number of parameters, must not identify a procedure that is described in the catalog (SQLSTATE 42723). The unqualified name, together with the number of parameters, is unique within its schema, but does not need to be unique across schemas.
- parameter-name
- Specifies the name of a parameter. The parameter name must be unique for this procedure (SQLSTATE 42734).
- data-type
- Specifies one of the supported PL/SQL data types.
- READS SQL DATA
- Indicates that SQL statements that do not modify SQL data can be included in the procedure. This clause is a Db2 data server extension.
- IS or AS
- Introduces the procedure body definition.
- declaration
- Specifies one or more variable, cursor, or REF CURSOR type declarations.
- BEGIN
- Introduces the executable block. The BEGIN-END block can contain an EXCEPTION section.
- statement
- Specifies a PL/SQL or SQL statement. The statement must be terminated by a semicolon.
- EXCEPTION
- An optional keyword that introduces the exception section.
- WHEN exception-condition
- Specifies a conditional expression that tests for one or more types of exceptions.
- statement
- Specifies a PL/SQL or SQL statement. The statement must be terminated by a semicolon.
- END
- A mandatory keyword that ends the block. You can optionally specify the name of the procedure.
Notes
The CREATE PROCEDURE statement can be submitted in obfuscated form. In an obfuscated statement, only the procedure name is readable. The rest of the statement is encoded in such a way that it is not readable, but can be decoded by the database server. Obfuscated statements can be produced by calling the DBMS_DDL.WRAP function.
Examples
CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END simple_procedure;
<<label>>
):CREATE OR REPLACE PROCEDURE test_goto
( p1 IN INTEGER, out1 OUT VARCHAR2(30) )
IS
BEGIN
<<LABEL2ABOVE>>
IF p1 = 1 THEN
out1 := out1 || 'one';
GOTO LABEL1BELOW;
END IF;
if out1 IS NULL THEN
out1 := out1 || 'two';
GOTO LABEL2ABOVE;
END IF;
out1 := out1 || 'three';
<<LABEL1BELOW>>
out1 := out1 || 'four';
END test_goto;