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.
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.
:
’)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.
Assigns a value to a variable
varname = <value_expr>;
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).
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
[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
Argument | Description |
---|---|
label |
Label |
select_stmt |
|
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.