Syntax Summary

SQL_DML_Statement:
               Statements already supported in RDM SQL
       |       create_procedure
       |       create_function
       |       drop_routine
       |       declare_variable

create_procedure:
          CREATE PROCEDURE [database_name.]proc_name [(proc_arg_decl[, proc_arg_decl]...)]
          [LANGUAGE SQL] 
          [[NOT] DETERMINISTIC] 
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA] 
          sqlproc_stmt

proc_arg_decl:
          [IN | OUT | INOUT] arg_name var_type

var_type:
          non_numeric_vartype | numeric_vartype

non_numeric_vartype:
          {CHAR | VARCHAR} [(length_num)]
     |    {BINARY | VARBINARY} (length_num)
     |    DATE | TIME | TIMESTAMP
     |    UUID
     |    ROWID

numeric_vartype:
          REAL | FLOAT | DOUBLE
     |    BOOLEAN | TINYINT | SMALLINT | INTEGER | BIGINT
     |    DECIMAL [(precision_num [, scale_num)]

create_function:
          CREATE FUNCTION [database_name.]func_name ( [fcn_arg_decl [, fcn_arg_decl]...] )
          RETURNS var_type
          [LANGUAGE SQL]
          [[NOT] DETERMINISTIC]
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA]
          sqlproc_stmt

fcn_arg_decl:
          arg_name var_type

drop_routine:
          drop_proc | drop_func

drop_proc:
          DROP PROCEDURE proc_name

drop_func:
          DROP FUNCTION func_name

compound_stmt:
          [ label_name:] BEGIN [[[NOT]] ATOMIC | TRANSACTION]
               declarations
               statement_list
          END [ label_name]

declarations:
          [{declare_variable | declare_conditions} ;]...
     |    [declare_cursor ;]...
     |    [declare_handler ;]...

statement_list:
          sqlproc_stmt ; [sqlproc_stmt ;]...

sqlproc_stmt:
          compound_stmt
     |    select_stmt
     |    update_stmt
     |    delete_stmt
     |    insert_stmt
     |    trans_stmt
     |    call_stmt
     |    asgt_stmt
     |    prepare_stmt
     |    execute_stmt
     |    deallocate_stmt
     |    open_stmt
     |    fetch_stmt
     |    getdiags_stmt
     |    control_stmt

sqlproc_stmt:
          compound_stmt
     |    select_stmt
     |    update_stmt
     |    delete_stmt
     |    insert_stmt
     |    trans_stmt
     |    call_stmt
     |    asgt_stmt
     |    prepare_stmt
     |    execute_stmt
     |    deallocate_stmt
     |    open_stmt
     |    fetch_stmt
     |    getdiags_stmt
     |    control_stmt

call_stmt:
          CALL [database_name.]proc_name [(proc_arg[, proc_arg]...)]

declarations:
          [{declare_variable | declare_conditions} ;]...
     |    [declare_cursor ;]...
     |    [declare_handler ;]...

declare_variable:
          DECLARE var_name[, var_name]... var_type [DEFAULT constant]

declare_cursor:
          DECLARE cursor_name CURSOR [{WITH | WITHOUT} RETURN] FOR select_stmt

declare_condition:
          DECLARE cond_name CONDITION [FOR SQLSTATE [VALUE] "string"]
     |    DECLARE cond_name CONDITION [FOR SQLCODE [VALUE] code_name]

declare_handler:
          DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR condition_value_list sqlproc_stmt

condition_value_list:
          condition_value[, condition_value]...

condition_value:
          specific_condition | SQLEXCEPTION | SQLWARNING | NOT FOUND

specific_condition:
          cond_name
     |    FOR SQLSTATE [VALUE] "string"
     |    FOR SQLCODE [VALUE] {code_name | code_num}

asgt_stmt:
          SET var_ref = value_expr 
     |    SET ( var_ref [, var_ref]... ) = ( value_expr [, value_expr]... )

var_ref:
          [@]var_name

if_stmt:
          IF conditional_expr THEN statement_list
          [ELSEIF conditional_expr THEN statement_list]...
          [ELSE statement_list]
          END  IF

case_stmt:
          CASE value_expr
               {WHEN = value_expr  THEN statement_list}...
               [ELSE {OKAY | statement_list}]
          END CASE
     |    CASE
               {WHEN conditional_expr  THEN statement_list}...
               [ELSE {OKAY | statement_list}]
          END CASE

loop_stmt:
          [ label_name:] LOOP statement_list END  LOOP [ label_name]

while_stmt:
          [ label_name:] WHILE conditional_expr
               statement_list
          END WHILE [ label_name]

repeat_stmt:
          [ label_name:] REPEAT
               statement_list
          UNTIL conditional_expr END REPEAT [ label_name]

for_stmt:
          [ label_name:] FOR [for_loop_var_name AS] [cursor_name CURSOR FOR]
               select_stmt
               DO
                      statement_list
               END FOR [ label_name]

iterate_stmt:
          ITERATE  label_name

leave_stmt:
          LEAVE  label_name

getdiags_stmt:
          GET [CURRENT | STACKED] DIAGNOSTICS stmt_info[, stmt_info]...
     |    GET [CURRENT | STACKED] DIAGNOSTICS {EXCEPTION | CONDITION} cond_info[, cond_info}...

stmt_info:
          var_name = stmt_item

stmt_item:
          COMMAND_FUNCTION | COMMAND_FUNCTION_CODE
     |    DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE
     |    MORE | NUMBER | ROW_COUNT | TRANSACTION_ACTIVE

cond_info:
          var_name = cond_item

cond_item:
          RETURNED_SQLSTATE | RETURNED_SQLCODE | MESSAGE_LENGTH | MESSAGE_TEXT
     |    SQLCODE_NAME | CONDITION_IDENTIFIER | CONDITION_NUMBER
     |    CURSOR_NAME | ROUTINE_NAME | SOURCE_FILE
     |    STATEMENT_NUMBER | LINE_NUMBER | COLUMN_NUMBER

signal_stmt:
          SIGNAL specific_condition [SET MESSAGE_TEXT = "string"]

resignal_stmt:
          RESIGNAL specific_condition [SET MESSAGE_TEXT = "string"]

return_stmt:
          RETURN {value_expr | NULL}

prepare_stmt:
          PREPARE stmt_name FROM {var_name | string_expr}

execute_stmt:
          EXECUTE stmt_name [INTO var_name[, var_name]...]

deallocate_stmt:
          DEALLOCATE PREPARE stmt_name

open_stmt:
          OPEN cursor_name

fetch_stmt:
          FETCH [NEXT] [FROM] cursor_name INTO var_name[, var_name]...

close_stmt:
          CLOSE cursor_name