# What are the uses of selectable Stored Procedures? - Firebird

Selectable stored procedures are so-called because they are designed to be executed using a SELECT statement. To readers accustomed to the server programming techniques available to other DBMSs, the concept of a stored procedure that outputs rows directly to the calling application or procedure, without any form of intermediate “temporary” table, will be much less familiar than the executable procedure.

Uses for Selectable Procedures

Selectable procedures can be used to specify virtually any set, but they are especially useful when you need a set that cannot be extracted, or is slow or difficult to extract, in a single DSQL statement.

Esoteric Sets

The selectable stored procedure technique offers enormous flexibility for extracting sets that defeat the logic available through regular SELECT statement specifications. It literally makes it possible to form a reproducible set from any combination of data you store. You can use calculations and transformations across a number of columns or rows in the set to condition what appears in the output. For example, output sets with running totals are difficult or impossible to achieve from a dynamic query but can be generated and managed speedily and efficiently with a selectable stored procedure.

Selectable procedures can come in handy for generating sets of data that are not stored in the database at all. It is a technique for which we all find a use sometime. In the following trivial example, a comma-separated list of strings, each of 20 or fewer characters, is fed in as input. The procedure returns each string to the application as numbered row:

CREATE PROCEDURE BREAKAPART(
INPUTLIST VARCHAR(1024))
RETURNS (
NUMERO SMALLINT,
ITEM VARCHAR(20)
)
AS
DECLARE CHARAC CHAR;
DECLARE ISDONE SMALLINT = 0;
BEGIN
NUMERO = 0;
ITEM = '';
WHILE (ISDONE = 0) DO
BEGIN
CHARAC = SUBSTRING(INPUTLIST FROM 1 FOR 1);
IF (CHARAC = '') THEN
ISDONE = 1;
IF (CHARAC = ',' OR CHARAC = '') THEN
BEGIN
NUMERO = NUMERO + 1;
SUSPEND; /* Sends a row to the row buffer */
ITEM = '';
END
ELSE
ITEM = ITEM || CHARAC;
INPUTLIST = SUBSTRING(INPUTLIST FROM 2);
END
END ^
COMMIT;
/* */
SELECT * FROM BREAKAPART('ALPHA,BETA,GAMMA,DELTA'); NUMERO ITEM
------ --------------------
1 ALPHA
2 BETA
3 GAMMA
4 DELTA

Performance Gain for Complex Sets

Often, complex queries involving many joins or subqueries are too slow to be satisfactory for interactive applications. Some queries can be slow because of unpropitious indexes on foreign keys. Because of its ability to operate on sets in nested loops, a stored procedure is capable of producing the required sets much more quickly and, especially, to begin returning rows much sooner than the conventional SQL sequences permit.

The Technique

The technique for extracting and manipulating the data for the output set uses a cursor to read each row in turn from a SELECT statement into a pre-declared set of variables. Often, it may be the output arguments that the column values are read into, but it can be local variables. Inside the loop, the variables are operated on in whatever manner is required: transformed by calculations, if need be, or used as search arguments for nested loops to pick up values from further queries. At the end of the loop, when all output arguments have their final values, a SUSPEND statement causes execution to pause while the set is passed to the row cache. Execution resumes when the next fetch is called.

As we saw in the previous BREAKAPART example, the SUSPEND statement is the element that causes the procedure to deliver a row.

The FOR SELECT ... DO Construct

To retrieve multiple rows in a procedure, we use the FOR SELECT . .. DO construct. The syntax is

FOR
<select-expression>
INTO <:variable [, :variable [, ...]]
DO
<compound_statement>;

The select-expression can be any select query, using joins, unions, views, other select procedures, expressions, function calls, and so on, in any valid combination.

A FOR SELECT differs from a standard SELECT statement in that it requires variables into which to receive the columns and fields specified.

The <compound-statement> can be a single SUSPEND statement or a block of two or more statements. The <compound-statement> can nest other compound statements.

FOR SELECT ... DO is a loop construct that retrieves the row specified in the select-expression and performs the statement or block following DO for each row in turn.

The INTO <variables> clause is required and must come last.

Processing in the Loop

Figure illustrates some typical activities that can be performed inside loops to generate output for a selectable stored procedure.

Operations in a selectable procedure

In the following examples, we will take a look at how combinations of the operations available in PSQL can meet your less ordinary SQL challenges.

A Simple Procedure with Nested SELECTs

The selectable procedure ORG_CHART, which is in the example employee database, takes no input arguments. It uses a FOR ... SELECT loop to line up a set from a self-referencing join on the DEPARTMENT table and pass column values, one row at a time, to a set of variables—some local, some declared as output arguments.

CREATE PROCEDURE ORG_CHART
RETURNS (
DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20),
TITLE CHAR(5),
EMP_CNT INTEGER )
AS
DECLARE VARIABLE mngr_no INTEGER;
DECLARE VARIABLE dno CHAR(3);
BEGIN
FOR SELECT h.department, d.department, d.mngr_no, d.dept_no
FROM department d
LEFT OUTER JOIN department h ON d.head_dept = h.dept_no
ORDER BY d.dept_no
DO

Each time the loop processes a row, it picks up a key value (MNGR_NO) into the local variable mngr_no. If the variable is null, the procedure fabricates values for the output arguments MNGR_NAME and TITLE. If the variable has a value, it is passed as a search argument to a nested query on the EMPLOYEE table, uniquely targeting a row and extracting the name and job code of a department manager. These values are passed to the remaining output arguments.

BEGIN
IF (:mngr_no IS NULL) THEN
BEGIN
mngr_name = '--TBH--';
title = '';
END
ELSE
SELECT full_name, job_code
FROM employee
WHERE emp_no = :mngr_no
INTO :mngr_name, :title;
SELECT COUNT(emp_no)
FROM employee
WHERE dept_no = :dno
INTO :emp_cnt;

Then all of the outputs for one row are assigned, a SUSPEND statement passes the row to the cache. Execution resumes back at the start of the loop when the next fetch request is made.

SUSPEND;
END
END^
COMMIT^

Notice how the nested query neatly bypasses the problem we have in DSQL with subqueries—we can return one and only one value to a subquery. If we want multiple values and left join logic will not work, we have to use multiple subqueries with multiple sets of aliases to extract each value from its own cursor.

Calling a Selectable Procedure

The syntax pattern for calling a selectable procedure is very similar to that for a table or view. The one difference is that a procedure may have input arguments:

SELECT <column-list> from name ([argument [, argument ...]])
WHERE <search-conditions>
ORDER BY <order-list>;

The procedure name must be specified.

Input argument rules are identical to those for executable procedures—see the earlier section titled “Calling Rules About Input Argument Values.”

The column-list is a comma-separated list of one or more of the output parameters returned by the procedure, or * to select all columns.

The output set can be limited by a search-condition in a WHERE clause and sorted by an ORDER BY clause.

Calling the ORG_CHART Procedure

This procedure has no input parameters, so the SELECT call looks like a simple select on a table, namely

SELECT * FROM ORG_CHART;

Selecting Aggregates from Procedures

In addition to selecting values from a procedure, you can use aggregate functions. For example, to use our procedure to display a count of the number of departments, use the following statement:

SELECT COUNT(DEPARTMENT) FROM ORG_CHART;

Similarly, to use ORG_CHART to display the maximum and average number of employees in each department, use the following statement:

SELECT
MAX(EMP_CNT),
AVG(EMP_CNT)
FROM ORG_CHART;

Nested Procedures

A stored procedure can itself execute a stored procedure. Each time a stored procedure
calls another procedure, the call is said to be nested because it occurs in the context of a previous and still active call to the first procedure. A stored procedure called by another stored procedure is known as a nested procedure.

The following procedure returns a listing of users, roles, and privileged objects in a database, with their SQL privileges. Inside the procedure, two calls to another procedure are nested. It is necessary to begin by defining and committing the nested procedure —otherwise, the outer procedure will fail to commit. You should always start at the bottom of the “chain” when creating procedures that nest other procedures.

As it happens, this nested procedure performs no SQL. It simply takes an obscure constant from a set used internally by Firebird to represent object types, and returns a string that is more meaningful to humans:

SET TERM ^ ;
CREATE PROCEDURE SP_GET_TYPE (
IN_TYPE SMALLINT )
RETURNS (
STRING VARCHAR(7) )
AS
BEGIN
STRING = 'Unknown';
IF (IN_TYPE = 0) THEN STRING = 'Table';
IF (IN_TYPE = 1) THEN STRING = 'View';
IF (IN_TYPE = 2) THEN STRING = 'Trigger';
IF (IN_TYPE = 5) THEN STRING = 'Proc';
IF (IN_TYPE = 8) THEN STRING = 'User';
IF (IN_TYPE = 0) THEN STRING = 'Table';
IF (IN_TYPE = 9) THEN STRING = 'Field';
IF (IN_TYPE = 13) THEN STRING = 'Role';
END^
COMMIT^

Now for the outer procedure. The table it queries for its data is the system table RDB$USER_PRIVILEGES. It uses a number of manipulation techniques, including calls to the internal SQL function CAST( ) and to an external function RTRIM( ), from the standard external function library ib_udf, to massage CHAR(31) items into VARCHAR(31). We do this because we want to concatenate some of these strings and we do not want the trailing blanks. SET TERM ^ ; CREATE PROCEDURE SP_PRIVILEGES RETURNS ( Q_ROLE_NAME VARCHAR(31), ROLE_OWNER VARCHAR(31), USER_NAME VARCHAR(31), Q_USER_TYPE VARCHAR(7), W_GRANT_OPTION CHAR(1), PRIVILEGE CHAR(6), GRANTOR VARCHAR(31), QUALIFIED_OBJECT VARCHAR(63), Q_OBJECT_TYPE VARCHAR(7) ) AS DECLARE VARIABLE RELATION_NAME VARCHAR(31); DECLARE VARIABLE FIELD_NAME VARCHAR(31); DECLARE VARIABLE OWNER_NAME VARCHAR(31); DECLARE VARIABLE ROLE_NAME VARCHAR(31); DECLARE VARIABLE OBJECT_TYPE SMALLINT; DECLARE VARIABLE USER_TYPE SMALLINT; DECLARE VARIABLE GRANT_OPTION SMALLINT; DECLARE VARIABLE IS_ROLE SMALLINT; DECLARE VARIABLE IS_VIEW SMALLINT; BEGIN First we loop through the table RDB$USER_PRIVILEGES, extracting and massaging some values directly into output arguments and others into local variables:

FOR SELECT
RTRIM(CAST(RDB$USER AS VARCHAR(31))), RDB$USER_TYPE,
RTRIM(CAST(RDB$GRANTOR AS VARCHAR(31))), RTRIM(CAST(RDB$RELATION_NAME AS VARCHAR(31))),
RTRIM(CAST(RDB$FIELD_NAME AS VARCHAR(31))), RDB$OBJECT_TYPE, RTRIM(CAST(RDB$PRIVILEGE AS VARCHAR(31))), RDB$GRANT_OPTION
FROM RDB$USER_PRIVILEGES INTO :USER_NAME, :USER_TYPE, :GRANTOR, :RELATION_NAME, :FIELD_NAME, :OBJECT_TYPE, :PRIVILEGE, :GRANT_OPTION Taking the current value of the output variable USER_NAME, we query RDB$ROLES to get the matching role owner and name, in case the “user” in the current row is actually a role. If it is not a role, these fields will be represented in the output by dashes:

DO BEGIN
SELECT
RTRIM(CAST(RDB$OWNER_NAME AS VARCHAR(31))), RTRIM(CAST(RDB$ROLE_NAME AS VARCHAR(31)))
FROM RDB$ROLES WHERE RDB$ROLE_NAME = :USER_NAME
INTO :ROLE_OWNER, :ROLE_NAME;
IF (ROLE_NAME IS NOT NULL) THEN
Q_ROLE_NAME = ROLE_NAME; ELSE
BEGIN Q_ROLE_NAME = '-'; ROLE_OWNER = '-'; END

WITH GRANT OPTION is a special privilege that we want to know about in our output so, next, we convert this attribute to 'Y' if it is positive (1) or a blank if it is not:

IF (GRANT_OPTION = 1) THEN
W_GRANT_OPTION = 'Y';
ELSE
W_GRANT_OPTION = '';

Now another query into RDB$ROLES, this time to find out whether the object that the privilege applies to is a role. If it is, we add a helpful prefix to its object name. If it is not a role, we go on to test whether our object is a column in a table and give it a qualified name if it is. IS_ROLE = NULL; SELECT 1 FROM RDB$ROLES
WHERE RDB$ROLE_NAME = :RELATION_NAME INTO :IS_ROLE; IF (IS_ROLE = 1) THEN QUALIFIED_OBJECT = '(Role) '||RELATION_NAME; ELSE BEGIN IF ( (FIELD_NAME IS NULL) FOR (RTRIM(FIELD_NAME) = '')) THEN FIELD_NAME = ''; ELSE FIELD_NAME = '.'||FIELD_NAME; QUALIFIED_OBJECT = RELATION_NAME||FIELD_NAME; END In RDB$USER _PRIVILEGES, tables and views are both object type 0. That is not good enough for us, so the next query checks the table RDB$RELATIONS to discover whether this particular object is a view: IF (OBJECT_TYPE = 0) THEN BEGIN IS_VIEW = 0; SELECT 1 FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME = :RELATION_NAME AND RDB$VIEW_SOURCE IS NOT NULL
INTO :IS_VIEW;
IF (IS_VIEW = 1) THEN
OBJECT_TYPE = 1;
END

At this point in our loop, we have almost everything we need. But our object still has its internal number and we still do not know what sort of “user” we have. Users can be other things besides people. Here is where we make the nested calls to get the internal numbers translated to meaningful strings. When that is done, the record is ready to output to the row cache and we call SUSPEND to complete the loop.

Return Values

Nested procedure calls in triggers or stored procedures are almost identical to the calls we make from DSQL to execute them. Where the syntax differs is in the handling of the return values. In DSQL, the engine transports the return values back to the client in a record structure. In stored procedures, we use the PSQL keyword RETURNING _VALUES and provide variables to receive the values.

EXECUTE PROCEDURE SP_GET_TYPE(:OBJECT_TYPE)
RETURNING_VALUES (:Q_OBJECT_TYPE);
EXECUTE PROCEDURE SP_GET_TYPE (:USER_TYPE)
RETURNING_VALUES (:Q_USER_TYPE);
SUSPEND;
END
END^

Calling the Procedure

This is another simple call:

SELECT * FROM SP_PRIVILEGES;

If we do not want all of the columns, or we want them in a special order, we can do that. For example, suppose we just want to look at the privileges for all humanoid users other than SYSDBA:

SELECT
USER_NAME,
QUALIFIED_OBJECT,
PRIVILEGE
FROM SP_PRIVILEGES
WHERE Q_USER_TYPE = 'User'
AND USER_NAME <> 'SYSDBA'
ORDER BY USER_NAME, QUALIFIED_OBJECT;

Replaceable search parameters can be used:

SELECT
USER_NAME,
QUALIFIED_OBJECT,
PRIVILEGE
FROM SP_PRIVILEGES
WHERE Q_USER_TYPE = ?
ORDER BY USER_NAME, QUALIFIED_OBJECT;

A Procedure with Running Totals

In this procedure, we process records from the SALES table in the EMPLOYEE database. We keep two running totals: one for each sales representative and one for overall sales. As inputs we have just a start and end date for the group of sales records we want.

SET TERM ^;
CREATE PROCEDURE LOG_SALES (
START_DATE DATE,
END_DATE DATE)
RETURNS (REP_NAME VARCHAR(37),
CUST VARCHAR(25),
ORDDATE TIMESTAMP,
ITEMTYP VARCHAR(12),
ORDTOTAL NUMERIC(9,2),
REPTOTAL NUMERIC(9,2),
RUNNINGTOTAL NUMERIC(9,2))
AS
DECLARE VARIABLE CUSTNO INTEGER;
DECLARE VARIABLE REP SMALLINT;
DECLARE VARIABLE LASTREP SMALLINT DEFAULT -99;
DECLARE VARIABLE LASTCUSTNO INTEGER DEFAULT -99;
BEGIN
RUNNINGTOTAL = 0.00;
FOR SELECT
CUST_NO,
SALES_REP,
ORDER_DATE,
TOTAL_VALUE,
ITEM_TYPE
FROM SALES
WHERE ORDER_DATE BETWEEN :START_DATE AND :END_DATE + 1
ORDER BY 2, 3
INTO :CUSTNO, :REP, :ORDDATE, :ORDTOTAL, :ITEMTYP

Notice that we are using an ordered set. If you are making a virtual table from a selectable stored procedure and you want an ordered set, it can be valuable to do the ordering set inside the procedure code. The optimizer can help performance here if it has useful indexes to work with, whereas ordering applied to the output set has, by its nature, no indexes to work with.

Once inside the loop, we begin massaging the data for our row and for the two running totals. We do a little magic to avoid repeating the rep’s name —it looks neater on a read -only display —although you would not do this if your application needed to target a row at random and use that column as a search key! We control the customer name in a similar way, to avoid an unnecessary search when the same customer occurs in consecutive records.

DO
BEGIN
IF(REP = LASTREP) THEN
BEGIN
REPTOTAL = REPTOTAL + ORDTOTAL;
REP_NAME = '"';
END
ELSE
BEGIN
REPTOTAL = ORDTOTAL;
LASTREP = REP;
SELECT FULL_NAME FROM EMPLOYEE
WHERE EMP_NO = :REP
INTO :REP_NAME;
END
IF (CUSTNO <> LASTCUSTNO) THEN
BEGIN
SELECT CUSTOMER FROM CUSTOMER
WHERE CUST_NO = :CUSTNO
INTO :CUST;
LASTCUSTNO = CUSTNO;
END
RUNNINGTOTAL = RUNNINGTOTAL + ORDTOTAL;
SUSPEND;

Our row is now complete and it goes to the row cache with the two running totals updated.

END
END^
SET TERM ;^

Calling the Procedure

Our input arguments are of DATE type, a start date and an end date. The procedure is searching a TIMESTAMP to select the rows for the cursor. It adds a day to the input end date, to ensure that we get every record up to the end of that day. That keeps things simple: When we call the procedure, we need only provide the first and last dates, without having to worry about any records with timestamps later than midnight of the final day.

This call is certain to return the whole table:

SELECT * FROM LOG_SALES ('16.05.1970', CURRENT_DATE);

We might want a procedure like this to be parameterized:

SELECT * FROM LOG_SALES (?, ?);

Viewing an Array Through a Stored Procedure

If a table contains columns defined as arrays, you cannot view the data in the column with a simple SELECT statement, since only the array ID is stored in the table. A stored procedure can be used to display array values, as long as the dimensions and data type of the array column are known in advance.

The JOB table in the sample database has a column named LANGUAGE_REQ containing the languages required for the position. The column is defined as an array of five VARCHAR(15) elements.

The following example uses a stored procedure to view the contents of the column. The procedure uses a FOR ... SELECT loop to retrieve each row from JOB for which LANGUAGE_REQ is not null. Then a WHILE loop retrieves each element of the array and returns the value to the calling application.

SET TERM ^;
CREATE PROCEDURE VIEW_LANGS
RETURNS (
code VARCHAR(5),
cty VARCHAR(15),
lang VARCHAR(15))
AS
DECLARE VARIABLE i INTEGER;
BEGIN
FOR SELECT
JOB_CODE,
JOB_COUNTRY
FROM JOB
WHERE LANGUAGE_REQ IS NOT NULL
DO
BEGIN
i = 1;
WHILE (i <= 5) DO
BEGIN
SELECT LANGUAGE_REQ[:i] FROM JOB
WHERE ((JOB_CODE = :code)
AND (JOB_COUNTRY = :cty))
INTO :lang;
i =i + 1;
SUSPEND;
END
END
END ^
SET TERM ; ^

Invoking it:

SELECT * FROM VIEW_LANGS;

CODE GRADE CTY LANG ===== ===== ============ ========= Eng 3 Japan Japanese Eng 3 Japan Mandarin Eng 3 Japan English Eng 3 Japan Eng 3 Japan Eng 4 England English Eng 4 England German Eng 4 England French ...

The procedure could be modified to take input arguments and return a different combination of data as output.

Testing Procedures

It should be unnecessary to remind developers of the need to test PSQL modules with rigor and skepticism before launching them into production environments where, on a bad day, they could really do some harm. The parser will spank you for PSQL coding errors but, as programs, your modules are at least as vulnerable to logic and runtime errors as any application code you write.

For example, our procedure LOG_SALES works fine as long as every sales record has a non-null SALES_REP value. However, this is a nullable column. The procedure happens to be one that generates a result set in which each output row depends on values in the preceding rows. If we do not address the potential effects of null occurring in that key, our procedure is vulnerable to inconsistent results. In the section “Changing Stored Procedures,” later in this chapter, we add a safeguard to the logic to deal with this particular problem.