Programming Constructs - Firebird

The following sections examine the general programming constructs that are recognized in PSQL.

BEGIN ... END Blocks

PSQL is a structured language. Once variables are declared, the procedural statements are bounded by the keywords BEGIN and END. In the course of developing the logic of the procedure, other blocks can be embedded, and any block can embed another block bounded by a BEGIN ... END pair.

No terminator symbol is used for the BEGIN or END keywords, except for the final END keyword that closes the procedure block and terminates the CREATE PROCEDURE or CREATE TRIGGER statement. This final END keyword takes the special terminator that was defined with the SET TERM statement before the definition began.

Conditional Blocks

PSQL recognizes two types of conditional structures:

  • Branching, controlled by IF .. . THEN and, optionally, ELSE blocks
  • Looping through and executing a block repeatedly until a WHILE condition becomes false

From v.1.5 on, the Boolean INSERTING, UPDATING, and DELETING, and integer ROW_COUNT context variables are available for predicates within a block that performs a data state-changing operation. Refer to Chapter Triggers for details of using the Boolean context variables in multi-event triggers.

The IF ... THEN ... ELSE Construct

The IF . .. THEN . .. ELSE construct branches to alternative courses of action by testing a specified condition. The syntax is

IF (<condition>)
THEN <compound_statement>
[ELSE <compound_statement>]
<compound_statement> = {<block>|<statement>;}

The condition clause is a predicate that must evaluate to true in order to execute the statement or block following THEN. The optional ELSE clause specifies an alternative statement or block to be executed if condition is false. Condition can be any valid predicate.

When you code conditional branching with SQL, use of the ELSE clause is sometimes a necessary “fallback” for situations where the predicate tested by IF might not evaluate to either true or false. This could occur where runtime data conditions caused the IF predicate to compare two nulls and a logical result of true or false was impossible. An ELSE branch is the means for your code to guarantee that the block produces an outcome whenever the IF clause fails to do so.

The following code snippet illustrates the use of IF ... THEN, assuming FIRST_NAME, LAST_NAME, and LINE2 have been previously declared as variables or arguments:

...
IF (FIRST_NAME IS NOT NULL) THEN
LINE2 = FIRST_NAME || ' ' || LAST_NAME;
ELSE
BEGIN
IF (LAST_NAME IS NOT NULL) THEN
LINE2 = LASTNAME;
ELSE
LINE2 = 'NO NAME SUPPLIED';
END
...

About CASE

PSQL does not yet support CASE logic as a programming construct. The CASE expression logic is, of course, available in PSQL. For more information, refer to Chapter Expressions and Predicates.

The WHILE ... DO Construct

WHILE . .. DO is a looping construct that repeats a statement or block of statements as long as the predicating condition is true. The condition is tested at the start of each loop. WHILE . .. DO uses the following syntax:

...
WHILE (<condition>) DO
BEGIN
<execute one or more statements> ;
<change the value of an operand of the predicating condition> ;
END
/* Continue execution here */
...

In the following simple procedure, WHILE tests the value of a variable, i, that is initialized as an input argument. The looping block decrements i on each iteration and, as long as i remains greater than zero, the value of the output argument, r, is raised by 1. When the procedure exits, the value of r is returned.

SET TERM ^;
CREATE PROCEDURE MORPH_ME (i INTEGER) RETURNS (r INTEGER)
AS
BEGIN
r = 0;
WHILE (i > 0) DO
BEGIN
r = r +i;
i = i - 1;
END
END^

Calling the procedure from isql

SQL> EXECUTE PROCEDURE MORPH_ME(16);

we get

R ========== 136

Variables

Five types of variables can be used in the body of a code module, with some restrictions according to whether the module is a stored procedure or a trigger:

  • Local variables, used to hold values used only within the trigger or procedure.
  • The NEW.ColumnName and OLD.ColumnName context variables, restricted to use in triggers, which store the new and old values of each column of the table when a DML operation is pending.
  • Other context variables—those specific to PSQL as well as those available to isql and DSQL.
  • Input arguments, used to pass constant values to a stored procedure from a client application, another stored procedure, or a trigger. Not available for triggers.
  • Output arguments, used to return values from a stored procedure back to the requester. Not available for triggers

Any of these types of variables can be used in the body of a stored procedure where an expression can appear. They can be assigned a literal value or assigned a value derived from queries or expression evaluations.

Use of Domains

Because domain definitions have the potential to be changed, they cannot be used in place of native SQL data types for declaring variables and stored procedure arguments. PSQL modules are compiled into a binary form at creation time and changes to domains would break them if domain use were allowed.

The Colon (:) Marker for Variables

In SQL statements, prefix the variable’s name with a colon character (:) whenever

  • The variable is used in an SQL statement.
  • The variable is receiving a value from a [FOR] SELECT ... INTO construct. Omit the colon in all other situations.

Never prefix context variables with a colon.

Assignment Statements

A procedure assigns values to variables with the syntax

variable = expression;

The expression can be any valid combination of variables, operators, and expressions, and can include calls to external functions (UDFs) and SQL functions, including the GEN_ID() function for stepping and returning a generator value.

The following code snippet performs several assignments:

...
WHILE (SI < 9) DO
BEGIN
SI = SI + 1; /* arithmetic expression */ IF (SUBSTRING(SMONTH FROM 1 FOR 1) = 'R') THEN
BEGIN
RESPONSE = 'YES'; /* simple constant */
LEAVE;
END
SMONTH = SUBSTRING(SMONTH FROM 2); /* function expression */
END
...

ariables and arguments should be assigned values of the data type that they are declared to be. Numeric variables should be assigned numeric values, and character variables assigned character values. Although Firebird provides automatic type conversion in some cases, it is advisable to use explicit casting to avoid unanticipated type mismatches.

Local Variables

Local variables are declared, one line per variable, preceding the first BEGIN statement. They have no effect outside the procedure or trigger, and their scope does not extend to any procedures they call. They must be declared before they can be used.

You should always ensure that your variables are initialized as early as possible in your procedure. From Firebird 1.5 you can declare and initialize in a single statement. For example, each of the following statements is valid to declare a counter variable and initialize it to 0:

...
DECLARE VARIABLE COUNTER1 INTEGER DEFAULT 0;
DECLARE VARIABLE COUNTER2 INTEGER = 0;

Example Using Local Variables

The following procedure is a piece of superstitious fun, assuming there is truth in the proverb “Never eat pork when there is an ‘R’ in the month,” it returns an opinion about a given date. For the sake of illustration, it declares one local variable that is used to get a starting condition for a WHILE loop and another to control the number of times the loop will execute:

CREATE PROCEDURE IS_PORK_SAFE(CHECK_MONTH DATE)
RETURNS (RESPONSE CHAR(3))
AS
DECLARE VARIABLE SMONTH VARCHAR(9); DECLARE VARIABLE SI SMALLINT;
BEGIN
SI = 0;
RESPONSE = 'N0 ';
SELECT CASE (EXTRACT (MONTH FROM :CHECK_MONTH)) WHEN 1 THEN 'JANUARY'
WHEN 2 THEN 'FEBRUARY'
WHEN 3 THEN 'MARCH'
WHEN 4 THEN 'APRIL'
WHEN 5 THEN 'MAY'
WHEN 6 THEN 'JUNE'
WHEN 7 THEN 'JULY' WHEN 8 THEN 'AUGUST'
WHEN 9 THEN 'SEPTEMBER'
WHEN 10 THEN 'OCTOBER'
WHEN 11 THEN 'NOVEMBER'
WHEN 12 THEN 'DECEMBER' END
FROM RDB$DATABASE
INTO :SMONTH;
WHILE (SI < 9) DO BEGIN
SI = SI + 1;
IF (SUBSTRING(SMONTH FROM 1 FOR 1) = 'R') THEN
BEGIN
RESPONSE = 'YES';
LEAVE;
END
SMONTH = SUBSTRING(SMONTH FROM 2);
END
END ^
COMMIT ^ SET TERM ;^

Is it safe for the author to eat pork on her birthday?

EXECUTE PROCEDURE IS_PORK_SAFE ('2004-05-16');
RESPONSE
========
NO

Input Arguments

Input arguments (also known as parameters) are used to pass values from an application to a procedure or from one PSQL module to another. They are declared in a comma-separated list in parentheses following the procedure name. Once declared, they can be used in the procedure body anywhere an expression can appear. For example, the following procedure snippet specifies one input argument, to tell the procedure which country the caller wants to be used in a search.

CREATE PROCEDURE SHOW_JOBS_FOR_COUNTRY (
COUNTRY VARCHAR(15))
...

Input parameters are passed by value from the calling program to a stored procedure. This means that if the procedure changes the value of an input parameter, the change has effect only within the procedure. When control returns to the calling program, the input parameter still has its original value.

Input arguments are not valid in triggers.

Output Arguments

An output argument (parameter) is used to specify a value that is to be returned from a procedure to the calling application or PSQL module. If there are to be multiple return values, declare the arguments in a comma-separated list in parentheses, following the keyword RETURNS in the procedure header. Once declared, they can be used in the procedure body anywhere an expression can appear.

The following code completes the procedure definition shown in the previous snippet. It defines three items of data to be returned to the caller as a virtual table:

CREATE PROCEDURE SHOW_JOBS_FOR_COUNTRY (
COUNTRY VARCHAR(15))
RETURNS (
ODE VARCHAR(11),
TITLE VARCHAR(25),
GRADE SMALLINT)
AS
BEGIN
FOR SELECT JOB_CODE, JOB_TITLE, JOB_GRADE FROM job
WHERE JOB_COUNTRY = :COUNTRY INTO :CODE, :TITLE, :GRADE
DO
BEGIN /* begin the loop */
CODE = 'CODE: ' || CODE; /* mess about with the value a little */
SUSPEND; /* this outputs one row per loop */
END
END ^

If you declare output parameters in the procedure header, the procedure must assign values to them to return to the calling application. Values can be derived from any valid expression in the procedure.

NEW and OLD Context Variables

Triggers can use two complete sets of context variables representing the “old” and “new values of each column in the owning table. OLD. column-name refers to the current or previous values of the named column in a row being updated or deleted. It has no meaning for inserts. NEW. column-name refers to the values submitted by the request to update, insert, or delete. It has no meaning for deletes. If an update does not change some columns, the NEW variable will have the same value as the OLD for each of those columns. Context variables are often used to compare the values of a column before and after it is modified.

Context variables can be used anywhere a regular variable can be used. NEW values for a row can be altered only by before actions. OLD values are read-only.

SELECT ... INTO Statements

Use a SELECT statement with an INTO clause to retrieve column values from tables and store them into local variables or output arguments.

Singleton SELECTs

An ordinary SELECT statement in PSQL must return at most one row from the data-base—a standard singleton SELECT. An exception is thrown if the statement returns more than one row. An ORDER BY clause is not valid for a singleton select unless it is a quantified SELECT FIRST 1 statement.

Normal rules apply to the input list and the WHERE clause and to the GROUP BY clause, if used. The INTO clause is required and must be the last clause in the statement.

For example, the following is a singleton SELECT statement in a parameterized DSQL query in an application:

SELECT SUM(BUDGET), AVG(BUDGET) FROM DEPARTMENT WHERE HEAD_DEPT = :head_dept;

To use this statement in a procedure, declare local variables or output arguments and add the INTO clause at the end as follows:

...
DECLARE VARIABLE TOT_BUDGET NUMERIC(18,2);
DECLARE VARIABLE AVG_BUDGET NUMERIC(18,2);
...
SELECT SUM(BUDGET), AVG(BUDGET) FROM DEPARTMENT WHERE HEAD_DEPT = :head_dept INTO :tot_budget, :avg_budget;

Multiple-Row SELECTs

Any PSQL module can operate on multiple rows of input, acquired through a SELECT statement, as long as it provides a looping structure that can “move” through the set and perform identical processing on each row. PSQL cannot handle multi-row sets otherwise and, in the absence of looping logic context, a multi-row select will cause an exception (“Multiple rows in singleton select”).

FOR SELECT .. . Loops

The main method for implementing a looping structure for handling multi-row input sets is the FOR SELECT .. . INTO ... DO structure. The abridged syntax is as follows:

FOR SELECT

<set-specification-list>
FROM table-name
[JOIN..]
[WHERE..]
[GROUP BY..]
[ORDER BY..]
INTO <list-of-variables> DO
BEGIN
<process-block>
...
[SUSPEND];
END
...

As an example, the following procedure defines and acquires a set using a SELECT statement that brings the rows, one at a time, in the procedure’s cursor buffer. It courses through the set, massaging each set of variables to fit a table specification. At the end of the loop, it inserts a record into the external table:

CREATE PROCEDURE PROJECT_MEMBERS
AS
DECLARE VARIABLE PROJ_NAME CHAR(23); DECLARE VARIABLE EMP_NO CHAR(6);
DECLARE VARIABLE LAST_NAME CHAR(23);
DECLARE VARIABLE FIRST_NAME CHAR(18);
DECLARE VARIABLE HIRE_DATE CHAR(12); DECLARE VARIABLE JOB_TITLE CHAR(27); DECLARE VARIABLE CRLF CHAR(2); BEGIN CRLF = ASCII_CHAR(13)||ASCII_CHAR(10); /* Windows EOL */ FOR SELECT DISTINCT P.PROJ_NAME, E.EMP_NO, E.LAST_NAME, E.FIRST_NAME, E.HIRE_DATE, J.JOB_TITLE FROM EMPLOYEE E JOIN JOB J ON E.JOB_CODE = J.JOB_CODE JOIN EMPLOYEE_PROJECT EP ON E.EMP_NO = EP.EMP_NO JOIN PROJECT P ON P.PROJ_ID = EP.PROJ_ID ORDER BY P.PROJ_NAME, E.LAST_NAME, E.FIRST_NAME INTO /* column variables */ :PROJ_NAME, :EMP_NO, :LAST_NAME, :FIRST_NAME, :HIRE_DATE, :JOB_TITLE DO BEGIN /* starts the loop that massages the variable */
PROJ_NAME = '"'||CAST(PROJ_NAME AS CHAR(20))||'"'||',';
EMP_NO = CAST(EMP_NO AS CHAR(5))||',';
LAST_NAME = '"'||CAST(LAST_NAME AS CHAR(20))||'"'||',';
FIRST_NAME = '"'||CAST(FIRST_NAME AS CHAR(15))||'"'||','; HIRE_DATE = CAST(HIRE_DATE AS CHAR(11))||',';
JOB_TITLE = '"'||CAST(JOB_TITLE AS CHAR(25))||'"';
INSERT INTO EXT_FILE
VALUES (:PROJ_NAME, :EMP_NO, :LAST_NAME,
:FIRST_NAME,:HIRE_DATE, :JOB_TITLE,
:CRLF);
END /* completes the DO-loop */
END ^

SUSPEND

The SUSPEND statement has a specific use with the FOR .. . SELECT ... INTO .. . DO construct just described. If SUSPEND is included in the DO loop, after the SELECT row has been read into the row variables, it causes the loop to wait until that row has been output to the server’s row cache before fetching the next row from the SELECT cursor. This is the operation that enables Firebird’s selectable stored procedures feature.

In the next chapter, we take a closer look at using SELECT statements that return multiple rows to a stored procedure, particularly the technique for writing selectable stored procedures.

The SUSPEND statement is not valid in triggers. In executable stored procedures it has the same effect as an EXIT statement—that is, it terminates the procedure immediately and any statements following it will never execute.

By contrast, if a select procedure has executable statements following the last SUSPEND statement in the procedure, all of those statements are executed, even though no more rows are returned to the calling program. This style of procedure terminates with the final END statement.

Flow of Control Statements

PSQL provides a number of statements that affect the flow of control through code modules. The SUSPEND statement just discussed effectively passes control back to the calling procedure or client program, waiting for a just-processed row to be fetched from the server’s row cache.

EXIT

In both selectable an executable procedures, EXIT causes program control to jump to the final END statement in the procedure. It is not meaningful in triggers.

The behaviors of SUSPEND, EXIT, and END statements are summarized in Table.

SUSPEND, EXIT, and END

SUSPEND, EXIT, and END

LEAVE

Firebird 1.5 introduced the LEAVE statement for breaking out of code blocks. It deprecates the BREAK statement that was partly implemented in v.1.0.x. An example of its use is in the WHILE loop of our IS_PORK_SAFE procedure:

...
WHILE (SI < 9) DO
BEGIN
SI = SI + 1; /* arithmetic expression */
IF (SUBSTRING(SMONTH FROM 1 FOR 1) = 'R') THEN
BEGIN
RESPONSE = 'YES'; /* simple constant */
LEAVE;
END
SMONTH = SUBSTRING(SMONTH FROM 2); /* function expression */
END
...

LEAVE causes execution to leave the loop—in this case, to stop testing the letters of a word for the character “R.” If the branching containing the LEAVE statement is not executed, execution continues to the end of the loop.

EXCEPTION

The EXCEPTION statement stops execution and passes control to the first exception handler block (a block starting with the keyword WHEN) that can handle the exception. If no handler is found for the exception, control passes to the final END statement and the procedure aborts. When this happens, one or more exception codes are passed back to the client via the error status vector (array).

The EXCEPTION statement is used in an IF . .. THEN . .. ELSE block to invoke custom exceptions that have been previously declared as database objects. The Firebird engine throws its own exceptions for SQL and context errors. Flow of control in these cases is the same as when a custom exception is called.

The syntax and techniques for calling and handling exceptions are addressed in Chapter Error Handling and Events.

Execute Statement

Firebird 1.5 introduced a PSQL extension supporting the executable string. An application or procedure can pass a DSQL statement (DML or DDL) as a string input argument, or the procedure definition can construct one as a local variable within its own boundaries, for execution using the EXECUTE STATEMENT command.

EXECUTE STATEMENT adds a degree of extra flexibility to stored procedures and triggers, along with a high risk of errors. Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error. Beyond that, the string cannot be parsed or validated at compile time.

This is the syntax pattern:

[FOR] EXECUTE STATEMENT <string> [INTO :var1 [, var2 [, :varn]]] DO <compound-statement>]; <compound-statement> = {statement | block-of-statements}

The construction of the expression or string variable that is to form the DSQL statement string argument must be complete at the time EXECUTE STATEMENT is executed. The DSQL statement string argument to be executed cannot contain any replaceable parameters.

In its simplest form, EXECUTE STATEMENT executes an SQL statement requesting an operation that does not return any data rows, namely

  • INSERT, UPDATE, DELETE
  • EXECUTE PROCEDURE
  • Any DDL statement except CREATE/DROP DATABASE

For example:

CREATE PROCEDURE EXEC_PROC (PROC_NAME VARCHAR(31)) AS DECLARE VARIABLE SQL VARCHAR(1024); DECLARE VARIABLE ...; BEGIN ... SQL = 'EXECUTE PROCEDURE '||PROC_NAME; EXECUTE STATEMENT SQL; END ^

calling it

EXECUTE PROCEDURE EXEC_PROC('PROJECT_MEMBERS');

Variable Values from a Singleton SELECT

The next snippet shows how to execute a SELECT statement string that returns a single row into a set of variables. As with any SELECT statement in a PSQL module, it will throw an exception if the statement returns multiple rows. Here, we are also able to do something that is not possible in regular PSQL: perform an operation involving a table or column whose name is not known at compile time.

CREATE PROCEDURE SOME_PROC
(TABLE_NAME VARCHAR(31), COL_NAME VARCHAR(31))
AS
DECLARE VARIABLE PARAM DATE;
BEGIN
EXECUTE STATEMENT 'SELECT MAX(' || COL_NAME || ') FROM '|| TABLE_NAME
INTO :PARAM;
...
FOR SELECT .... FROM ....
WHERE END_DATE = :PARAM
INTO ... DO
...
END ^

Variable Values from a Multi-Row SELECT

The EXECUTE STATEMENT syntax also supports executing a SELECT statement string inside a FOR loop, to return output a row at a time into a list of variables. There are no restrictions on the SELECT statement that is used, but remember that the compile time parser cannot validate the contents of the string.

CREATE PROCEDURE DYNAMIC_SAMPLE (
TEXT_COL VARCHAR(31),
TABLE_NAME VARCHAR(31)) RETURNS (LINE VARCHAR(32000))
AS DECLARE VARIABLE ONE_LINE VARCHAR(100); DECLARE VARIABLE STOP_ME SMALLINT; BEGIN LINE = ''; STOP_ME = 1; FOR EXECUTE STATEMENT 'SELECT ' || TEXTCOL || ' FROM ' || TABLE_NAME INTO :ONE_LINE DO BEGIN IF (STOP_ME > 320) THEN EXIT; IF (ONE_LINE IS NOT NULL) THEN LINE = LINE || ONE_LINE || ' '; STOP_ME = STOP_ME + 1;
END
SUSPEND;
END ^

Caveats

The EXECUTE STATEMENT feature is intended only for very cautious use and should be used with all factors taken into account. Operations using it are slow and risky. Make a rule to use it only when it is impossible to achieve the objective by other means or in the unlikely event that it actually improves the performance of the statement. Be aware of the risks:

  • There is no way to validate the syntax of the statement string argument.
  • No dependency checks or protections exist to prevent tables or columns from being dropped or altered.
  • Operations will be slow because the embedded statement has to be prepared every time it is executed.
  • If the stored procedure has special privileges on some objects, the dynamic statement submitted in the EXECUTE STATEMENT string does not inherit them. Privileges are restricted to those granted to the user who is executing the procedure.

POST_EVENT

Firebird events provide a signaling mechanism that enables applications to listen for database changes made by concurrently running applications without the need for applications to incur CPU cost or consume bandwidth to poll one another directly. The statement syntax is

POST_EVENT event_name;

It causes the event event_name to be “posted” to a stack. Event_name can be any string up to 78 characters and is not pre-defined on the server. Stacked events will be broadcast to clients “listening” via event alerter host routines.

When the transaction commits, any events that occurred during triggers or stored procedures can be delivered to listening client applications. An application can then respond to the event by, for example, refreshing its open datasets upon being alerted to changes.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics