CREATE PROCEDUREおよびREPLACE PROCEDURE構文(SQL形式) - Advanced SQL Engine - Teradata Database

{ 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
}