Use the following procedure to create an SQL procedure. Steps 4, 5 and 6 apply only when a compound statement is specified.
- Identify and specify the procedure name after the keywords CREATE PROCEDURE. The procedure_name can optionally be qualified with database_name.
- Identify the parameters, if desired, and specify them immediately after the procedure name.The entire set of parameters is in a comma-separated list, and must be enclosed by LEFT PARENTHESIS and RIGHT PARENTHESIS characters.
- Each parameter consists of 3 elements, in the following left-to-right order.
- Parameter type (optional)
- Parameter name (mandatory)
- Data type of the parameter (mandatory)
You cannot specify a character parameter data type with a server character set of KANJI1. Otherwise, the database aborts the request and returns an error to the requestor.
- Specify either a single statement to perform the main task, or the BEGIN keyword for a compound statement.
If you have specified a single statement, terminate that with a final SEMICOLON character to complete the CREATE PROCEDURE statement.
If you have started to specify a compound statement, go to step 4.
- Following the BEGIN keyword, specify any variable declarations, cursor declarations, and condition handler declarations, in that order.
Within each handler declaration, specify a single statement or a BEGIN-END compound statement as the handler action.
- Specify the statements to perform the main tasks.
These statements must be specified after the variable, cursor, and condition handler declarations.
- Terminate the CREATE PROCEDURE statement with a final SEMICOLON character after the END keyword.
You can specify an optional ending label for the BEGIN-END block after the END keyword. If you specify the ending label, then you must specify an equivalent beginning label suffixed with a COLON (:) character before the BEGIN keyword.