FirebirdSQL logo

Writing the Body Code

This section takes a closer look at the procedural SQL language constructs and statements that are available for coding the body of a stored procedure, functions, trigger, and PSQL blocks.

Colon Marker (‘:’)

The colon marker prefix (‘:’) is used in PSQL to mark a reference to a variable in a DML statement.The colon marker is not required before variable names in other PSQL code.

The colon prefix can also be used for the NEW and OLD contexts, and for cursor variables.

Assignment Statements

Assigns a value to a variable

Syntax
varname = <value_expr>;
Table 1. Assignment Statement Parameters
Argument Description

varname

Name of a parameter or local variable

value_expr

An expression, constant or variable whose value resolves to the same data type as varname

PSQL uses the equal symbol (‘=’) as its assignment operator.The assignment statement assigns a SQL expression value on the right to the variable on the left of the operator.The expression can be any valid SQL expression: it may contain literals, internal variable names, arithmetic, logical and string operations, calls to internal functions, stored functions or external functions (UDFs).

Example using assignment statements

CREATE PROCEDURE MYPROC (
  a INTEGER,
  b INTEGER,
  name VARCHAR (30)
)
RETURNS (
  c INTEGER,
  str VARCHAR(100))
AS
BEGIN
  -- assigning a constant
  c = 0;
  str = '';
  SUSPEND;
  -- assigning expression values
  c = a + b;
  str = name || CAST(b AS VARCHAR(10));
  SUSPEND;
  -- assigning expression value built by a query
  c = (SELECT 1 FROM rdb$database);
  -- assigning a value from a context variable
  str = CURRENT_USER;
  SUSPEND;
END

BREAK

Exits a loop

Syntax
[label:]
<loop_stmt>
BEGIN
  ...
  BREAK;
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list> DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>)} DO
Table 1. BREAK Statement Parameters
Argument Description

label

Label

select_stmt

SELECT statement

condition

A logical condition returning TRUE, FALSE or UNKNOWN

The BREAK statement immediately terminates the inner loop of a WHILE or FOR looping statement.Code continues to be executed from the first statement after the terminated loop block.

BREAK is similar to LEAVE, except it doesn’t support a label.