CREATE/REPLACE PROCEDURE Syntax | Teradata Vantage - 17.10 - CREATE PROCEDURE and REPLACE PROCEDURE Syntax (SQL Form) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
{ 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
}