## pSqlArgument
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlArgument_start((START))
pSqlArgument_start --> pSqlArgument_0_0[pSqlVariableName]
pSqlArgument_0_0 --> pSqlArgument_0_1[pSqlDataType]
pSqlArgument_0_1 --> pSqlArgument_end((END))
```
## pSqlArgumentList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlArgumentList_start((START))
pSqlArgumentList_start --> pSqlArgumentList_0_0[pSqlArgument]
pSqlArgumentList_0_0 --> pSqlArgumentList_0_1[","]:::quoted
pSqlArgumentList_0_1 --> pSqlArgumentList_0_2[pSqlArgument]
pSqlArgumentList_0_2 --> pSqlArgumentList_end((END))
```
## pSqlVariableList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlVariableList_start((START))
pSqlVariableList_start --> pSqlVariableList_0_0[pSqlVariableName]
pSqlVariableList_0_0 --> pSqlVariableList_0_1[","]:::quoted
pSqlVariableList_0_1 --> pSqlVariableList_0_2[pSqlVariableName]
pSqlVariableList_0_2 --> pSqlVariableList_end((END))
```
## pSqlBlock
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlBlock_start((START))
pSqlBlock_start --> pSqlBlock_0_0[DECLARE]:::quoted
pSqlBlock_0_0 --> pSqlBlock_0_1[pSqlDeclareSection]
pSqlBlock_0_1 --> pSqlBlock_0_2[pSqlBody]
pSqlBlock_0_2 --> pSqlBlock_end((END))
```
## Purpose
A PSQL block is a structure to define procedural logic. It can contain both procedural logic as well as SQL statements like "select".
## pSqlBlockNoBatchSeparator
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlBlockNoBatchSeparator_start((START))
pSqlBlockNoBatchSeparator_start --> pSqlBlockNoBatchSeparator_0_0[DECLARE]:::quoted
pSqlBlockNoBatchSeparator_0_0 --> pSqlBlockNoBatchSeparator_0_1[pSqlDeclareSection]
pSqlBlockNoBatchSeparator_0_1 --> pSqlBlockNoBatchSeparator_0_2[pSqlBodyNoBatchSeparator]
pSqlBlockNoBatchSeparator_0_2 --> pSqlBlockNoBatchSeparator_end((END))
```
## pSqlBlockOptionalDeclare
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlBlockOptionalDeclare_start((START))
pSqlBlockOptionalDeclare_start --> pSqlBlockOptionalDeclare_0_0[DECLARE]:::quoted
pSqlBlockOptionalDeclare_0_0 --> pSqlBlockOptionalDeclare_0_1[pSqlDeclareSection]
pSqlBlockOptionalDeclare_0_1 --> pSqlBlockOptionalDeclare_0_2[pSqlBody]
pSqlBlockOptionalDeclare_0_2 --> pSqlBlockOptionalDeclare_end((END))
```
## pSqlDeclareSection
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> pSqlDeclareSection
pSqlDeclareSection -->| | pSqlDeclaration
pSqlDeclaration --> End((END))
```
## Purpose
A PSQL declare section defines one or more local variables, which are available in the block and nested blocks.
## pSqlDeclaration
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> pSqlDeclaration
pSqlDeclaration -->| | pSqlItemDeclaration
pSqlItemDeclaration --> End((END))
```
## pSqlItemDeclaration
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlItemDeclaration_start((START))
pSqlItemDeclaration_start --> pSqlItemDeclaration_0_0[pSqlVariableName]
pSqlItemDeclaration_0_0 --> pSqlItemDeclaration_0_1[CONSTANT]:::quoted
pSqlItemDeclaration_0_1 --> pSqlItemDeclaration_0_2[pSqlDataType]
pSqlItemDeclaration_0_2 --> pSqlItemDeclaration_0_3[ASSIGNMENT_OPERATOR]:::quoted
pSqlItemDeclaration_0_3 --> pSqlItemDeclaration_0_4[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlItemDeclaration_0_4 --> pSqlItemDeclaration_0_5[BATCHSEPARATOR]:::quoted
pSqlItemDeclaration_0_5 --> pSqlItemDeclaration_0_6[pSqlCallProcedureStatement]
pSqlItemDeclaration_0_6 --> pSqlItemDeclaration_0_7[BATCHSEPARATOR]:::quoted
pSqlItemDeclaration_0_7 --> pSqlItemDeclaration_end((END))
```
## Purpose
An item declaration defines one named variable, based upon data type. The initial value can be added as a constant.
## pSqlBodyNoBatchSeparator
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlBodyNoBatchSeparator_start((START))
pSqlBodyNoBatchSeparator_start --> pSqlBodyNoBatchSeparator_0_0[BEGIN]:::quoted
pSqlBodyNoBatchSeparator_0_0 --> pSqlBodyNoBatchSeparator_0_1[pSqlStatement]
pSqlBodyNoBatchSeparator_0_1 --> pSqlBodyNoBatchSeparator_0_2[pSqlExceptionBlock]
pSqlBodyNoBatchSeparator_0_2 --> pSqlBodyNoBatchSeparator_0_3[END]:::quoted
pSqlBodyNoBatchSeparator_0_3 --> pSqlBodyNoBatchSeparator_end((END))
```
## pSqlBody
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlBody_start((START))
pSqlBody_start --> pSqlBody_0_0[pSqlBodyNoBatchSeparator]
pSqlBody_0_0 --> pSqlBody_0_1[BATCHSEPARATOR]:::quoted
pSqlBody_0_1 --> pSqlBody_end((END))
```
## Purpose
A PSQL body contains the procedural logic as well as SQL statements. Variables must have been declared beforehand.
## pSqlExceptionBlock
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExceptionBlock_start((START))
pSqlExceptionBlock_start --> pSqlExceptionBlock_0_0[EXCEPTION]:::quoted
pSqlExceptionBlock_0_0 --> pSqlExceptionBlock_0_1[pSqlExceptionWhenName]
pSqlExceptionBlock_0_1 --> pSqlExceptionBlock_0_2[pSqlExceptionWhenOthers]
pSqlExceptionBlock_0_2 --> pSqlExceptionBlock_end((END))
```
## pSqlExceptionWhenName
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExceptionWhenName_start((START))
pSqlExceptionWhenName_start --> pSqlExceptionWhenName_0_0[WHEN]:::quoted
pSqlExceptionWhenName_0_0 --> pSqlExceptionWhenName_0_1[pSqlVariableName]
pSqlExceptionWhenName_0_1 --> pSqlExceptionWhenName_0_2[THEN]:::quoted
pSqlExceptionWhenName_0_2 --> pSqlExceptionWhenName_0_3[pSqlStatement]
pSqlExceptionWhenName_0_3 --> pSqlExceptionWhenName_end((END))
```
## pSqlExceptionWhenOthers
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExceptionWhenOthers_start((START))
pSqlExceptionWhenOthers_start --> pSqlExceptionWhenOthers_0_0[WHEN]:::quoted
pSqlExceptionWhenOthers_0_0 --> pSqlExceptionWhenOthers_0_1[OTHERS]:::quoted
pSqlExceptionWhenOthers_0_1 --> pSqlExceptionWhenOthers_0_2[THEN]:::quoted
pSqlExceptionWhenOthers_0_2 --> pSqlExceptionWhenOthers_0_3[pSqlStatement]
pSqlExceptionWhenOthers_0_3 --> pSqlExceptionWhenOthers_end((END))
```
## pSqlStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlStatement_start((START))
pSqlStatement_start --> pSqlStatement_0_0[pSqlAssignmentStatement]
pSqlStatement_0_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_1_0[pSqlContinueStatement]
pSqlStatement_1_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_2_0[pSqlExecuteImmediateStatement]
pSqlStatement_2_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_3_0[pSqlExecuteNativeStatement]
pSqlStatement_3_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_4_0[pSqlExitStatement]
pSqlStatement_4_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_5_0[pSqlGotoStatement]
pSqlStatement_5_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_6_0[pSqlLabelStatement]
pSqlStatement_6_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_7_0[pSqlIfStatement]
pSqlStatement_7_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_8_0[pSqlLoopStatement]
pSqlStatement_8_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_9_0[pSqlNullStatement]
pSqlStatement_9_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_10_0[pSqlBlock]
pSqlStatement_10_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_11_0[pSqlRaiseStatement]
pSqlStatement_11_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_12_0[pSqlReturnStatement]
pSqlStatement_12_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_13_0[pSqlProcedureStatement]
pSqlStatement_13_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_14_0[pSqlCallProcedureStatement]
pSqlStatement_14_0 --> pSqlStatement_end((END))
pSqlStatement_start --> pSqlStatement_15_0[sqlStatement]
pSqlStatement_15_0 --> pSqlStatement_15_1[";"]
pSqlStatement_15_1 --> pSqlStatement_end((END))
```
## Purpose
A number of basic PSQL statements are available.
## pSqlBlockOrStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> pSqlBlockOrStatement
pSqlBlockOrStatement -->| | pSqlBlock
pSqlBlock --> End((END))
pSqlBlockOrStatement -->| | pSqlStatement
pSqlStatement --> End((END))
```
## Purpose
A PSQL block or statement defines a procedural step or a SQL statement to be executed.
## pSqlBlockOrStatements
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> pSqlBlockOrStatements
pSqlBlockOrStatements -->| | pSqlBlockOrStatement
pSqlBlockOrStatement --> End((END))
```
## pSqlNullStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlNullStatement_start((START))
pSqlNullStatement_start --> pSqlNullStatement_0_0[NULL]:::quoted
pSqlNullStatement_0_0 --> pSqlNullStatement_0_1[";"]
pSqlNullStatement_0_1 --> pSqlNullStatement_end((END))
```
## Purpose
The null-statement is a NOP-statement (No Operator). The use of the null-statement is necessary when a PSQL statement is needed, but no activity needs to be performed such as with an if statement. The null-statement also makes explicit that a developer has considered the actions needed and found that no action applies to a specific scenario. This leads to improved code documentation.
## pSqlReturnStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlReturnStatement_start((START))
pSqlReturnStatement_start --> pSqlReturnStatement_0_0[RETURN]:::quoted
pSqlReturnStatement_0_0 --> pSqlReturnStatement_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlReturnStatement_0_1 --> pSqlReturnStatement_0_2[";"]
pSqlReturnStatement_0_2 --> pSqlReturnStatement_end((END))
```
## Purpose
Return a value from a function to the calling code.
## pSqlAssignmentStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlAssignmentStatement_start((START))
pSqlAssignmentStatement_start --> pSqlAssignmentStatement_0_0[pSqlVariableName]
pSqlAssignmentStatement_0_0 --> pSqlAssignmentStatement_0_1[triggerRecordVariableExpression]
pSqlAssignmentStatement_0_1 --> pSqlAssignmentStatement_0_2[ASSIGNMENT_OPERATOR]:::quoted
pSqlAssignmentStatement_0_2 --> pSqlAssignmentStatement_0_3[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlAssignmentStatement_0_3 --> pSqlAssignmentStatement_0_4[BATCHSEPARATOR]:::quoted
pSqlAssignmentStatement_0_4 --> pSqlAssignmentStatement_0_5[pSqlCallProcedureStatement]
pSqlAssignmentStatement_0_5 --> pSqlAssignmentStatement_end((END))
```
# Purpose
The assignment statement assign a new value to a variable. To assign the results of a SQL query to a value, use a select ... into ... statement.
## pSqlContinueStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlContinueStatement_start((START))
pSqlContinueStatement_start --> pSqlContinueStatement_0_0[CONTINUE]:::quoted
pSqlContinueStatement_0_0 --> pSqlContinueStatement_0_1[WHEN]:::quoted
pSqlContinueStatement_0_1 --> pSqlContinueStatement_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlContinueStatement_0_2 --> pSqlContinueStatement_0_3[BATCHSEPARATOR]:::quoted
pSqlContinueStatement_0_3 --> pSqlContinueStatement_end((END))
```
## Purpose
This PSQL continue statement continues execution of the innermost loop at the start of the loop. When a boolean expression is specified, the innermost loop is only started at the start when the boolean expression evaluates to true.
## pSqlExecuteImmediateStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExecuteImmediateStatement_start((START))
pSqlExecuteImmediateStatement_start --> pSqlExecuteImmediateStatement_0_0[pSqlExecuteImmediateStatementNS]
pSqlExecuteImmediateStatement_0_0 --> pSqlExecuteImmediateStatement_0_1[BATCHSEPARATOR]:::quoted
pSqlExecuteImmediateStatement_0_1 --> pSqlExecuteImmediateStatement_end((END))
```
## Purpose
The execute immediate PSQL statement enables the use of SQL statements that are compiled at runtime. For instance dynamic DDL statements can not always be executed on compiled time and the execute immediate enables these.
## pSqlExecuteImmediateStatementNS
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExecuteImmediateStatementNS_start((START))
pSqlExecuteImmediateStatementNS_start --> pSqlExecuteImmediateStatementNS_0_0[EXECUTE]:::quoted
pSqlExecuteImmediateStatementNS_0_0 --> pSqlExecuteImmediateStatementNS_0_1[IMMEDIATE]:::quoted
pSqlExecuteImmediateStatementNS_0_1 --> pSqlExecuteImmediateStatementNS_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlExecuteImmediateStatementNS_0_2 --> pSqlExecuteImmediateStatementNS_0_3[INTO]:::quoted
pSqlExecuteImmediateStatementNS_0_3 --> pSqlExecuteImmediateStatementNS_0_4[pSqlVariableList]
pSqlExecuteImmediateStatementNS_0_4 --> pSqlExecuteImmediateStatementNS_0_5[pSqlExecuteImmediateUsingClause]
pSqlExecuteImmediateStatementNS_0_5 --> pSqlExecuteImmediateStatementNS_end((END))
```
## pSqlExecuteImmediateUsingClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExecuteImmediateUsingClause_start((START))
pSqlExecuteImmediateUsingClause_start --> pSqlExecuteImmediateUsingClause_0_0[USING]:::quoted
pSqlExecuteImmediateUsingClause_0_0 --> pSqlExecuteImmediateUsingClause_0_1[pSqlExecuteImmediateUsingItem]
pSqlExecuteImmediateUsingClause_0_1 --> pSqlExecuteImmediateUsingClause_0_2[","]:::quoted
pSqlExecuteImmediateUsingClause_0_2 --> pSqlExecuteImmediateUsingClause_0_3[pSqlExecuteImmediateUsingItem]
pSqlExecuteImmediateUsingClause_0_3 --> pSqlExecuteImmediateUsingClause_end((END))
```
## pSqlExecuteImmediateUsingItem
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExecuteImmediateUsingItem_start((START))
pSqlExecuteImmediateUsingItem_start --> pSqlExecuteImmediateUsingItem_0_0[IN]:::quoted
pSqlExecuteImmediateUsingItem_0_0 --> pSqlExecuteImmediateUsingItem_0_1[OUT]:::quoted
pSqlExecuteImmediateUsingItem_0_1 --> pSqlExecuteImmediateUsingItem_0_2[pSqlVariableName]
pSqlExecuteImmediateUsingItem_0_2 --> pSqlExecuteImmediateUsingItem_end((END))
```
## pSqlExecuteNativeStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExecuteNativeStatement_start((START))
pSqlExecuteNativeStatement_start --> pSqlExecuteNativeStatement_0_0[pSqlExecuteNativeStatementNS]
pSqlExecuteNativeStatement_0_0 --> pSqlExecuteNativeStatement_0_1[BATCHSEPARATOR]:::quoted
pSqlExecuteNativeStatement_0_1 --> pSqlExecuteNativeStatement_end((END))
```
## Purpose
Execute a statement directly on a specific data container. The statement must match the native SQL or API code for the data container specified. Output of the statement is retrieved into PSQL variables.
## pSqlExecuteNativeStatementNS
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlExecuteNativeStatementNS_start((START))
pSqlExecuteNativeStatementNS_start --> pSqlExecuteNativeStatementNS_0_0[EXECUTE]:::quoted
pSqlExecuteNativeStatementNS_0_0 --> pSqlExecuteNativeStatementNS_0_1[NATIVE]:::quoted
pSqlExecuteNativeStatementNS_0_1 --> pSqlExecuteNativeStatementNS_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlExecuteNativeStatementNS_0_2 --> pSqlExecuteNativeStatementNS_0_3[INTO]:::quoted
pSqlExecuteNativeStatementNS_0_3 --> pSqlExecuteNativeStatementNS_0_4[pSqlVariableList]
pSqlExecuteNativeStatementNS_0_4 --> pSqlExecuteNativeStatementNS_0_5[DATACONTAINER]:::quoted
pSqlExecuteNativeStatementNS_0_5 --> pSqlExecuteNativeStatementNS_0_6[stringConstant]
pSqlExecuteNativeStatementNS_0_6 --> pSqlExecuteNativeStatementNS_end((END))
```
## pSqlExitStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlExitStatement_start((START))
pSqlExitStatement_start --> pSqlExitStatement_0_0[EXIT]:::quoted
pSqlExitStatement_0_0 --> pSqlExitStatement_0_1[WHEN]:::quoted
pSqlExitStatement_0_1 --> pSqlExitStatement_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlExitStatement_0_2 --> pSqlExitStatement_0_3[BATCHSEPARATOR]:::quoted
pSqlExitStatement_0_3 --> pSqlExitStatement_end((END))
```
## Purpose
This PSQL exit statement exits execution of the innermost loop. When a boolean expression is specified, the innermost loop is only exited when the boolean expression evaluates to true.
## pSqlGotoStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlGotoStatement_start((START))
pSqlGotoStatement_start --> pSqlGotoStatement_0_0[GOTO]:::quoted
pSqlGotoStatement_0_0 --> pSqlGotoStatement_0_1[pSqlVariableName]
pSqlGotoStatement_0_1 --> pSqlGotoStatement_0_2[BATCHSEPARATOR]:::quoted
pSqlGotoStatement_0_2 --> pSqlGotoStatement_end((END))
```
## Purpose
Continue execution at the location of the specified label.
## pSqlLabelStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlLabelStatement_start((START))
pSqlLabelStatement_start --> pSqlLabelStatement_0_0[LT]:::quoted
pSqlLabelStatement_0_0 --> pSqlLabelStatement_0_1[LT]:::quoted
pSqlLabelStatement_0_1 --> pSqlLabelStatement_0_2[pSqlVariableName]
pSqlLabelStatement_0_2 --> pSqlLabelStatement_0_3[GT]:::quoted
pSqlLabelStatement_0_3 --> pSqlLabelStatement_0_4[GT]:::quoted
pSqlLabelStatement_0_4 --> pSqlLabelStatement_end((END))
```
## Purpose
Specificy location of a label.
## pSqlIfStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlIfStatement_start((START))
pSqlIfStatement_start --> pSqlIfStatement_0_0[IF]:::quoted
pSqlIfStatement_0_0 --> pSqlIfStatement_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlIfStatement_0_1 --> pSqlIfStatement_0_2[THEN]:::quoted
pSqlIfStatement_0_2 --> pSqlIfStatement_0_3[pSqlBlockOrStatements]
pSqlIfStatement_0_3 --> pSqlIfStatement_0_4[pSqlElsIfExpression]
pSqlIfStatement_0_4 --> pSqlIfStatement_0_5[ELSE]:::quoted
pSqlIfStatement_0_5 --> pSqlIfStatement_0_6[pSqlBlockOrStatements]
pSqlIfStatement_0_6 --> pSqlIfStatement_0_7[END]:::quoted
pSqlIfStatement_0_7 --> pSqlIfStatement_0_8[IF]:::quoted
pSqlIfStatement_0_8 --> pSqlIfStatement_0_9[BATCHSEPARATOR]:::quoted
pSqlIfStatement_0_9 --> pSqlIfStatement_end((END))
```
## Purpose
The if-statement performs conditional logic. When the boolean expression after if holds, the PSQL block after the 'then' will be executed. Other branches can be specified using an elsif. Otherwise, and only when specified, the logic after the else is executed.
## pSqlElsIfExpression
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlElsIfExpression_start((START))
pSqlElsIfExpression_start --> pSqlElsIfExpression_0_0[ELSIF]:::quoted
pSqlElsIfExpression_0_0 --> pSqlElsIfExpression_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlElsIfExpression_0_1 --> pSqlElsIfExpression_0_2[THEN]:::quoted
pSqlElsIfExpression_0_2 --> pSqlElsIfExpression_0_3[pSqlBlockOrStatements]
pSqlElsIfExpression_0_3 --> pSqlElsIfExpression_end((END))
```
## pSqlLoopStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> pSqlLoopStatement
pSqlLoopStatement -->| | pSqlForNumberLoopStatement
pSqlForNumberLoopStatement --> End((END))
pSqlLoopStatement -->| | pSqlForRecordLoopStatement
pSqlForRecordLoopStatement --> End((END))
pSqlLoopStatement -->| | pSqlWhileLoopStatement
pSqlWhileLoopStatement --> End((END))
pSqlLoopStatement -->| | pSqlRepeatUntilLoopStatement
pSqlRepeatUntilLoopStatement --> End((END))
```
## Purpose
A variety of PSQL statements for loops are available.
## pSqlForNumberLoopStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlForNumberLoopStatement_start((START))
pSqlForNumberLoopStatement_start --> pSqlForNumberLoopStatement_0_0[FOR]:::quoted
pSqlForNumberLoopStatement_0_0 --> pSqlForNumberLoopStatement_0_1[pSqlVariableName]
pSqlForNumberLoopStatement_0_1 --> pSqlForNumberLoopStatement_0_2[IN]:::quoted
pSqlForNumberLoopStatement_0_2 --> pSqlForNumberLoopStatement_0_3[REVERSE]:::quoted
pSqlForNumberLoopStatement_0_3 --> pSqlForNumberLoopStatement_0_4[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlForNumberLoopStatement_0_4 --> pSqlForNumberLoopStatement_0_5[pSqlVariableName]
pSqlForNumberLoopStatement_0_5 --> pSqlForNumberLoopStatement_0_6["."]:::quoted
pSqlForNumberLoopStatement_0_6 --> pSqlForNumberLoopStatement_0_7["."]:::quoted
pSqlForNumberLoopStatement_0_7 --> pSqlForNumberLoopStatement_0_8[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlForNumberLoopStatement_0_8 --> pSqlForNumberLoopStatement_0_9[pSqlVariableName]
pSqlForNumberLoopStatement_0_9 --> pSqlForNumberLoopStatement_0_10[STEP]:::quoted
pSqlForNumberLoopStatement_0_10 --> pSqlForNumberLoopStatement_0_11[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlForNumberLoopStatement_0_11 --> pSqlForNumberLoopStatement_0_12[pSqlVariableName]
pSqlForNumberLoopStatement_0_12 --> pSqlForNumberLoopStatement_0_13[LOOP]:::quoted
pSqlForNumberLoopStatement_0_13 --> pSqlForNumberLoopStatement_0_14[pSqlBlockOrStatements]
pSqlForNumberLoopStatement_0_14 --> pSqlForNumberLoopStatement_0_15[END]:::quoted
pSqlForNumberLoopStatement_0_15 --> pSqlForNumberLoopStatement_0_16[LOOP]:::quoted
pSqlForNumberLoopStatement_0_16 --> pSqlForNumberLoopStatement_0_17[BATCHSEPARATOR]:::quoted
pSqlForNumberLoopStatement_0_17 --> pSqlForNumberLoopStatement_end((END))
```
## Purpose
This PSQL integer loop statement iterates over a range of integer values, executing PSQL statements for each iterated value. The iterations goes from the first value to the last value in increments of specified step size. Default step size is 1. The iterations go backward in decrements of 1 when 'reverse' is specified.
## pSqlForRecordLoopStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlForRecordLoopStatement_start((START))
pSqlForRecordLoopStatement_start --> pSqlForRecordLoopStatement_0_0[FOR]:::quoted
pSqlForRecordLoopStatement_0_0 --> pSqlForRecordLoopStatement_0_1[pSqlVariableName]
pSqlForRecordLoopStatement_0_1 --> pSqlForRecordLoopStatement_0_2[IN]:::quoted
pSqlForRecordLoopStatement_0_2 --> pSqlForRecordLoopStatement_0_3["("]:::quoted
pSqlForRecordLoopStatement_0_3 --> pSqlForRecordLoopStatement_0_4[selectStatement]
pSqlForRecordLoopStatement_0_4 --> pSqlForRecordLoopStatement_0_5[pSqlExecuteImmediateStatementNS]
pSqlForRecordLoopStatement_0_5 --> pSqlForRecordLoopStatement_0_6[pSqlExecuteNativeStatementNS]
pSqlForRecordLoopStatement_0_6 --> pSqlForRecordLoopStatement_0_7[")"]:::quoted
pSqlForRecordLoopStatement_0_7 --> pSqlForRecordLoopStatement_0_8[LOOP]:::quoted
pSqlForRecordLoopStatement_0_8 --> pSqlForRecordLoopStatement_0_9[pSqlBlockOrStatements]
pSqlForRecordLoopStatement_0_9 --> pSqlForRecordLoopStatement_0_10[END]:::quoted
pSqlForRecordLoopStatement_0_10 --> pSqlForRecordLoopStatement_0_11[LOOP]:::quoted
pSqlForRecordLoopStatement_0_11 --> pSqlForRecordLoopStatement_0_12[BATCHSEPARATOR]:::quoted
pSqlForRecordLoopStatement_0_12 --> pSqlForRecordLoopStatement_end((END))
```
## Purpose
This PSQL result set loop statement iterates over a result set returned by an Invantive UniversalSQL query. The PSQL statements are executed for each record. The record's specific values can be retrieved using the variable.
## pSqlRepeatUntilLoopStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlRepeatUntilLoopStatement_start((START))
pSqlRepeatUntilLoopStatement_start --> pSqlRepeatUntilLoopStatement_0_0[LOOP]:::quoted
pSqlRepeatUntilLoopStatement_0_0 --> pSqlRepeatUntilLoopStatement_0_1[pSqlBlockOrStatements]
pSqlRepeatUntilLoopStatement_0_1 --> pSqlRepeatUntilLoopStatement_0_2[END]:::quoted
pSqlRepeatUntilLoopStatement_0_2 --> pSqlRepeatUntilLoopStatement_0_3[LOOP]:::quoted
pSqlRepeatUntilLoopStatement_0_3 --> pSqlRepeatUntilLoopStatement_0_4[BATCHSEPARATOR]:::quoted
pSqlRepeatUntilLoopStatement_0_4 --> pSqlRepeatUntilLoopStatement_end((END))
```
## Purpose
This PSQL loop statement (or 'repeat until') executes PSQL statements as long as the specified loop is not terminated using an 'exit' statement.
## pSqlWhileLoopStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlWhileLoopStatement_start((START))
pSqlWhileLoopStatement_start --> pSqlWhileLoopStatement_0_0[WHILE]:::quoted
pSqlWhileLoopStatement_0_0 --> pSqlWhileLoopStatement_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
pSqlWhileLoopStatement_0_1 --> pSqlWhileLoopStatement_0_2[LOOP]:::quoted
pSqlWhileLoopStatement_0_2 --> pSqlWhileLoopStatement_0_3[pSqlBlockOrStatements]
pSqlWhileLoopStatement_0_3 --> pSqlWhileLoopStatement_0_4[END]:::quoted
pSqlWhileLoopStatement_0_4 --> pSqlWhileLoopStatement_0_5[LOOP]:::quoted
pSqlWhileLoopStatement_0_5 --> pSqlWhileLoopStatement_0_6[BATCHSEPARATOR]:::quoted
pSqlWhileLoopStatement_0_6 --> pSqlWhileLoopStatement_end((END))
```
## Purpose
This PSQL while loop statement executes PSQL statements as long as the specified boolean condition evaluates to true at loop end.
## pSqlRaiseStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlRaiseStatement_start((START))
pSqlRaiseStatement_start --> pSqlRaiseStatement_0_0[RAISE]:::quoted
pSqlRaiseStatement_0_0 --> pSqlRaiseStatement_0_1[INVALID_NUMBER]:::quoted
pSqlRaiseStatement_0_1 --> pSqlRaiseStatement_0_2[NO_DATA_FOUND]:::quoted
pSqlRaiseStatement_0_2 --> pSqlRaiseStatement_0_3[PROGRAM_ERROR]:::quoted
pSqlRaiseStatement_0_3 --> pSqlRaiseStatement_0_4[TOO_MANY_ROWS]:::quoted
pSqlRaiseStatement_0_4 --> pSqlRaiseStatement_0_5[VALUE_ERROR]:::quoted
pSqlRaiseStatement_0_5 --> pSqlRaiseStatement_0_6[ZERO_DIVIDE]:::quoted
pSqlRaiseStatement_0_6 --> pSqlRaiseStatement_0_7[RAISE_APPLICATION_ERROR]:::quoted
pSqlRaiseStatement_0_7 --> pSqlRaiseStatement_0_8["("]:::quoted
pSqlRaiseStatement_0_8 --> pSqlRaiseStatement_0_9[numberedOrNamedExpressionList]
pSqlRaiseStatement_0_9 --> pSqlRaiseStatement_0_10[")"]:::quoted
pSqlRaiseStatement_0_10 --> pSqlRaiseStatement_0_11[BATCHSEPARATOR]:::quoted
pSqlRaiseStatement_0_11 --> pSqlRaiseStatement_end((END))
```
## Purpose
The raise statement raises one from a list of pre-defined exceptions:
- INVALID_NUMBER: typically used when a string is converted to a number, but does not contain a valid number.
- NO_DATA_FOUND: typically used when a 'select into' statement returns zero rows.
- PROGRAM_ERROR: typically used to signal an (internal) system error.
- TOO_MANY_ROWS: typically used when a 'select into' statement returns two or more rows.
- VALUE_ERROR: typically used when during evaluation of an expression an error occurs.
- ZERO_DIVIDE: typically used when the right-hand side of a divide is zero.
The raise_application_error statement raises an exception, which consists of a non-unique text or numeric identification plus a message text. Optionally, you can add a kind request and the natural key as parameters.
## pSqlProcedureStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlProcedureStatement_start((START))
pSqlProcedureStatement_start --> pSqlProcedureStatement_0_0[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>]
pSqlProcedureStatement_0_0 --> pSqlProcedureStatement_0_1["("]:::quoted
pSqlProcedureStatement_0_1 --> pSqlProcedureStatement_0_2[numberedOrNamedExpressionList]
pSqlProcedureStatement_0_2 --> pSqlProcedureStatement_0_3[")"]:::quoted
pSqlProcedureStatement_0_3 --> pSqlProcedureStatement_0_4[";"]:::quoted
pSqlProcedureStatement_0_4 --> pSqlProcedureStatement_end((END))
```
## pSqlCallProcedureStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlCallProcedureStatement_start((START))
pSqlCallProcedureStatement_start --> pSqlCallProcedureStatement_0_0[pSqlCallProcedureStatementNS]
pSqlCallProcedureStatement_0_0 --> pSqlCallProcedureStatement_0_1[BATCHSEPARATOR]:::quoted
pSqlCallProcedureStatement_0_1 --> pSqlCallProcedureStatement_end((END))
```
## pSqlCallProcedureStatementNS
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pSqlCallProcedureStatementNS_start((START))
pSqlCallProcedureStatementNS_start --> pSqlCallProcedureStatementNS_0_0[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>]
pSqlCallProcedureStatementNS_0_0 --> pSqlCallProcedureStatementNS_0_1["."]:::quoted
pSqlCallProcedureStatementNS_0_1 --> pSqlCallProcedureStatementNS_0_2[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>]
pSqlCallProcedureStatementNS_0_2 --> pSqlCallProcedureStatementNS_0_3[distributedAliasDirective]
pSqlCallProcedureStatementNS_0_3 --> pSqlCallProcedureStatementNS_0_4["("]:::quoted
pSqlCallProcedureStatementNS_0_4 --> pSqlCallProcedureStatementNS_0_5[numberedOrNamedExpressionList]
pSqlCallProcedureStatementNS_0_5 --> pSqlCallProcedureStatementNS_0_6[")"]:::quoted
pSqlCallProcedureStatementNS_0_6 --> pSqlCallProcedureStatementNS_end((END))
```
## pSqlVariableName
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> pSqlVariableName
pSqlVariableName -->| | identifier[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>]
identifier --> End((END))
```