Reference Guide › Command Reference › SQL Statements › CREATE PROCEDURE

CREATE PROCEDURE

                                      ┌─ , ──────────────────────┐
►►─ CREATE PROCEDURE ─ proc-name ─ ( ─▼─┬──────────────────────┬─┴─ ) ────────►
                                        ├─┬─ IN ◄ ──┬──────────┤
                                        │ ├─ OUT ───┤          │
                                        │ └─ INOUT ─┘          │
                                        ├─ SQL-parameter-name ─┤
                                        └─ datatype ───────────┘

 ►─┬───────────────────┬─ proc-body ──────────────────────────────────────────►
   └─ proc-attributes ─┘

 ►─┬────────────────────────────────────┬─────────────────────────────────────►◄
   └─ RUN OPTIONS ─ run-options-string ─┘

Expansion of Where proc-attributes is defined as

   ┌────────────────────────────────┐
├──▼─┬─ language ─────────────────┬─┴──────────────────────────────────────────┤
     ├─ parameter-style ──────────┤
     ├─ SPECIFIC ─ name ──────────┤
     ├─┬───────┬ ─ DETERMINISTIC ─┤
     │ └─ NOT ─┘                  │
     └─ data-access ──────────────┘

Expansion of Where language is defined as

├── LANGUAGE ─┬─ COBOL ─────┬──────────────────────────────────────────────────┤
              ├─ PLI ───────┤
              ├─ C ─────────┤
              ├─ ASSEMBLER ─┤
              └─ SQL ───────┘

Expansion of Where parameter-style is defined as

├── PARAMETER STYLE ─┬─ GENERAL ────────────┬──────────────────────────────────┤
                     ├─ GENERAL WITH NULLS ─┤
                     ├─ DATACOM SQL ────────┤
                     └─ SQL ────────────────┘

Expansion of Where data-access is defined as

├──┬─ MODIFIES SQL DATA ─┬─────────────────────────────────────────────────────┤
   ├─ READS SQL DATA ────┤
   ├─ CONTAINS SQL ──────┤
   └─ NO SQL ────────────┘

Middle View of proc-body syntax

►►─┬─ proc-external ─┬───────────────────┬─┬──────────────────────────────────►◄
   |                 └─ parameter-style ─┘ |
   └─ proc-SQL-stmt ───────────────────────┘

Expansion of Where proc_external is defined as

├── EXTERNAL ─┬───────────────────┬────────────────────────────────────────────┤
              └─ NAME ─ ext-name ─┘

Expansion of Where proc-SQL-stmt is defined as

├──┬─ executable-DML-stmt ─┬───────────────────────────────────────────────────┤
   ├─ DDL-stmt ────────────┤
   ├─ control-stmt ────────┤
   └─ diagnostics-stmt ────┘

Expansion of Where diagnostics-stmt is defined as

├──┬─ signal-stmt ──────────┬──────────────────────────────────────────────────┤
   ├─ get-diagnostics-stmt ─┤
   ├─ datacom-dump-stmt ────┤
   ├─ raise-error-stmt ─────┤
   └─ resignal-stmt ────────┘

Expansion of Where control-stmt is defined as

├──┬─ call-proc ──────────────┬────────────────────────────────────────────────┤
   ├─ execute-proc ───────────┤
   └─ proc-only-control-stmt ─┘

Expansion of Where proc-only-control-stmt is defined as

├──┬─ compound-stmt ─────────────────────────┬─────────────────────────────────┤
   └─ compound-stmt-only-control-statements ─┘

Expansion of Where compound-stmt-only-control-statements are defined as

├──┬─ assignment-stmt ─┬───────────────────────────────────────────────────────┤
   ├─ case-stmt ───────┤
   ├─ if-then-stmt ────┤
   ├─ iterate-stmt ────┤
   ├─ leave-stmt ──────┤
   ├─ loop-stmt ───────┤
   ├─ repeat-stmt ─────┤
   └─ while-do-stmt ───┘

External Procedure definitions and SQL Procedures are created with this statement.

SQL Statements that Support SQL Procedures

• Assignment Statement
• CASE Statement
• Compound Statement
• Condition Declaration
• DATACOM DUMP Statement
• GET DIAGNOSTICS Statement
• IF-THEN Statement
• ITERATE Statement
• LEAVE Statement
• SQL variable declaration
• Looping constructs
  • LOOP Statement
  • REPEAT-UNTIL Statement
  • WHILE Statement
• RAISE ERROR Statement
• RESIGNAL Statement
• SIGNAL Statement
• SIMULATE DATACOM PROCEDURE Statament (DBSQLPR only)