{ CREATE | REPLACE } PROCEDURE [ database_name. | user_name. ] procedure_name
( [ parameter_specification [,...] ] )
SQL_data_access
[ DYNAMIC RESULT SETS number_of_sets ]
[ SQL SECURITY privilege_option ]
statement [;]
-
parameter_specification
-
[ IN | OUT | INOUT ] parameter_name data_type
-
SQL_data_access
-
{ CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA }
-
privilege_option
-
{ CREATOR | DEFINER | INVOKER | OWNER }
-
statement
-
{ SQL_statement |
BEGIN SQL_multistatement_request END REQUEST |
compound_statement |
open_statement |
fetch_statement |
assignment_statement |
condition_statement |
[ label_name : ] iteration_statement [ label_name ] |
diagnostic_statement |
ITERATE label_name |
LEAVE label_name
}
-
data_type
-
{ INTEGER | SMALLINT | BIGINT | BYTEINT | DATE |
{ TIME | TIMESTAMP } [( fractional_seconds_precision)] [WITH TIME ZONE] |
INTERVAL YEAR [(precision)] [TO MONTH] |
INTERVAL MONTH [(precision)] |
INTERVAL DAY [(precision)]
[TO { HOUR | MINUTE | SECOND [(fractional_seconds_precision)] } ] |
INTERVAL HOUR [(precision)]
[TO { MINUTE | SECOND [(fractional_seconds_precision)] } ] |
INTERVAL MINUTE [(precision)]
[ TO SECOND [(fractional_seconds_precision)] ] |
INTERVAL SECOND [( precision [, fractional_seconds_precision ] ) |
PERIOD (DATE) |
PERIOD ({ TIME | TIMESTAMP } [(precision)] [ WITH TIME ZONE ]) |
REAL |
DOUBLE PRECISION |
FLOAT [(integer)] |
NUMBER [({ integer | *} [, integer ]...)] |
{ DECIMAL | NUMERIC } [(integer [, integer ]...)] |
{ CHAR | BYTE | GRAPHIC } [(integer)] |
{ VARCHAR | CHAR VARYING | VARBYTE | VARGRAPHIC } [(integer)] |
LONG VARCHAR |
LONG VARGRAPHIC |
{ BINARY LARGE OBJECT | BLOB | CHARACTER LARGE OBJECT | CLOB }
(integer [ G | K | M ]) |
[SYSUDTLIB.] { XML | XMLTYPE }
[(integer [ G | K | M ])] [ INLINE LENGTH integer ] |
[SYSUDTLIB.] JSON [(integer [ K | M ])] [ INLINE LENGTH integer ]
[ CHARACTER SET { UNICODE | LATIN } ] |
[SYSUDTLIB.] ST_GEOMETRY [(integer [ K | M ])] [ INLINE LENGTH integer ] |
[SYSUDTLIB.] DATASET [(integer [ K | M ])]
[ INLINE LENGTH integer ] storage_format |
[SYSUDTLIB.] { UDT_name | MBR | ARRAY_name | VARRAY_name }
}
-
compound_statement
-
[ label_name : ] BEGIN
[ local_declaration [...] ]
[ cursor_declaration [...] ]
[ condition_handler [...] ]
[ statement [...] ]
END [ label_name ]
-
open_statement
-
OPEN cursor_name [ USING { SQL_identifier | SQL_parameter } [,...] ] ;
-
fetch_statement
-
FETCH [ [ NEXT | FIRST ] FROM ] cursor_name
INTO { local_variable_name | parameter_reference } [,...] ;
-
assignment_statement
-
SET assignment_target = assignment_source
-
condition_statement
-
{ case_statement | if_statement }
-
iteration_statement
-
{ while_statement | loop_statement | for_statement | repeat_statement }
-
diagnostic_statement
-
{ { SIGNAL signal_specification | RESIGNAL [ signal_specification ] }
[ SET condition_information_item = value ] |
GET DIAGNOSTICS [ EXCEPTION condition_number ] diagnostic_assignment [,...]
} ;
-
storage_format
-
STORAGE FORMAT { Avro | CSV [ CHARACTER SET { UNICODE | LATIN } ] }
[ WITH SCHEMA [ database. ] schema_name ]
-
local_declaration
-
DECLARE { variable_name [,...] data_type [ DEFAULT { literal | NULL } ] |
condition_name CONDITION [ FOR sqlstate_code ]
}
-
cursor_declaration
-
DECLARE cursor_name [ [NO] SCROLL ] CURSOR
[ WITHOUT RETURN | WITH RETURN [ ONLY ] [ TO { CALLER | CLIENT } ] ]
FOR { cursor_specification [ FOR { READ ONLY | UPDATE } ] | statement_name }
[ PREPARE statement_name
FROM { 'statement_string' | statement_string_variable } ] [;]
-
condition_handler
-
DECLARE { { CONTINUE | EXIT } HANDLER | condition_name CONDITION }
[ FOR
{ sqlstate_specification [,...] handler_action_statement |
{ SQLEXCEPTION | SQLWARNING | NOT FOUND | condition_name } [,...]
handler_action_statement |
sqlstate_specification [,...]
}
] ;
-
case_statement
-
CASE { operand_1 when_operand_clause [...] |
when_condition_clause [...]
}
[ ELSE statement; [...] ]
END CASE
-
if_statement
-
IF conditional_expression THEN statement; [...]
[ ELSEIF conditional_expression THEN statement; [...] ][...]
[ ELSE statement; [...] ]
END IF
-
while_statement
-
WHILE conditional_expression
DO statement; [...]
END WHILE
-
loop_statement
-
LOOP
statement; [...]
END LOOP
-
for_statement
-
FOR for_loop_variable AS [ cursor_name CURSOR FOR ] cursor_specification
DO statement; [...]
END FOR
-
repeat_statement
-
REPEAT
statement; [...] UNTIL conditional_expression
END REPEAT
-
signal_specification
-
{ condition_name | SQLSTATE [ VALUE ] SQLSTATE_code }
-
diagnostic_assignment
-
{ parameter_name | variable_name } = statement_information_item
-
cursor_specification
-
SELECT selection [,...] FROM source WHERE_clause [ other_SELECT_clause [...] ]
-
sqlstate_specification
-
SQLSTATE [ VALUE ] sqlstate_code
-
when_operand_clause
-
WHEN operand_2 THEN statement; [...]
-
when_condition_clause
-
WHEN conditional_expression THEN statement; [...]
-
selection
-
{ * | column_name [ [AS] alias_name ] | expression [AS] alias_name }
-
source
-
{ table_name [,...] |
table_name { INNER | { LEFT | RIGHT | FULL } [ OUTER ] } JOIN
table_name ON condition
}