Other Derived Tables - Firebird
Interview Questions
All Tutorials
Job SearchFirebird currently supports two other forms of derived table: the selectable stored procedure and the external virtual table (EVT).
Selectable Stored Procedures
Firebird’s PSQL extensions provide syntax for defining a stored procedure that outputs a derived set of data from virtually anywhere: from the database, from context variables (even from input variables alone), from external tables, or from any combination. PSQL and DSQL SELECT syntax provides for these virtual tables to be retrieved just as though they were real tables.
The output set for a selectable stored procedure is defined as a set of output variables, using the RETURNS clause of a CREATE PROCEDURE statement. The output data is created by looping through a cursor set, defined by a SELECT statement, and reading the values of the specified columns into these output variables or into declared local variables. Within the loop, almost anything can be done to manipulate the data, including processing embedded loops. A selectable stored procedure can be called from (embedded in) another stored procedure. Anything that can be selected, calculated, or derived can be transformed to output.
As a simple illustration, the following stored procedure declaration sets up a loop and proceeds to pass processed rows, one at a time, to the output buffer:
CREATE PROCEDURE SHOW_JOBS_FOR_COUNTRY (COUNTRY VARCHAR(15))
RETURNS ( /* the virtual table */
CODE 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
When the stored procedure is compiled, it is ready for action. Retrieval of the set is by way of a slightly specialized SELECT statement that can, if required, take constant arguments as input parameters:
External Virtual Tables
An external virtual table (EVT) is a table that gets its data from some external data source rather than from the database. The results of a query on an EVT are treated in exactly the same way as the results of any other query, and they look exactly as if they came from a database table. This allows the integration of external data such as real-time data feeds, formatted data in operating system files, other databases (even non-SQL databases), and any other tabular data sources.
Firebird implements EVTs by means of the EXTERNAL FILE clause of the CREATE TABLE statement. External data is read from fixed-format text records into regular Firebird data columns.
Firebird external tables can also insert records into EVTs.
