Selectable stored procedures.

    Proper use of the Client/Server paradigm demands that the heavy duties must be carried by the database server and not by the client application. This assumes the server's machine is much more powerful than the thin client. While this idea may change as three tiered developments become mainstream, the basic premise will hold true for many years to come. In the early days were the mainframes, running a database on the machine with several dumb terminals attached. Then PCs appeared and they had adapters to connect to the mainframes. As the products evolved, several little PC databases tried to replace the old centric model. However, for cooperating, the PCs had to be networked and again, the idea of a centralized repository -first a file server and after that, an application server- brought C/S databases to the scenario. The latest idea of the NC (Network Computer) and a powerful operating system running on a PC Server or UNIX workstation only confirms databases are a key piece of the computing era.

    On one side, thin browsers open a bunch of connections to the web server and the web server in turn sends many commands to the database server. With three tiered applications, the intermediate layer holds the business rules for flexibility and reliability. On the other side, it's not so far in the future the day the home computer may be able to send commands to some appliances and security systems.

    For the vast majority of the aforementioned, the client and the server share a network. If one aim is let the server handle the hard work, other aim is maintaining as low as possible the data flux in the network. This enhances response time and avoid crashing the client with excessive memory utilization. All vendors of real and decent RDBMS products offer a sort of programming language in the server itself. While a few of them, like IBM's DB/2 require the use of the C language, the most common practice is providing a proprietary procedural language as opposed to SQL that's a declarative language. This language allows the user to write code that's kept in the server (stored procedures) and triggers (stored procedures that are invoked automatically by the server when some predefined condition is met).

One obvious advantage of stored procedures and triggers is they are compiled and kept as part of the database itself and run on the server itself, so they have direct access to the data, namely, there's no need for the data to travel over the network to be seen by the stored procedures or triggers.

In Interbase, there are executable and selectable procedures. The former are the most common across all database products: they are invoked to carry some actions on a set of tuples and they usually return a condition to signal if the operations were successful. Also, they can return a set of tuples that usually are the result of calculations that cannot be done with a select statement. But Interbase also has selectable procedures. This is a special feature. Selectable procedures can be included in a select statement as if they were tables. Better, they can be joined with tables, views and other stored procedures. This gives the user a powerful tool for many creative solutions. For example, it's possible to construct a continuum range of dates and use it to gather information from another table in a day to day basis.

Use of selectable procedures is not limited to end user commands: both stored procedures and triggers may invoke selectable procedures using the same syntax. And in all these cases, selectable procedures may receive input parameters when needed.

There's an special construct, FOR/INTO/SUSPEND that is used commonly to create the body of selectable procedures. This is because often the rows proceed from an SQL commands against a table or view and further procedural operations are applied to the original result before giving it to the client. Indeed, if you want to return a value in a selectable procedure, you don't need an SQL command: an assignment to the output parameters followed by the SUSPEND clause will do the job.

A typical stored procedure has a form like this generic example:

set term ^;
create procedure p(p0 type, ..., pN type)
returns(field1 type, ..., fieldN type)
as
declare variable v1 type;
begin
   v1 = 0;
   FOR select_clause
   INTO :field1, ..., :fieldN
   DO begin
      v1 = v1 + 1;
      if (fieldX is NULL)
      then fieldX = fieldY || v1;
      /* comments */
      p0 = v1;
      SUSPEND;
   end;
end ^
set term ;^

Given that example, it can be invoked by means of

select * from p(param0, ..., paramN)

or can be restricted like a record set from a table

select field1, fieldN
from p(param0, ..., paramN)
where field1 = value1 and field2 = value2

or it can be joined with another selectable stored procedure, a table or a view. Such procedure can invoke other procedures to do calculations. If a select with named column is done (like the prior example), the names of the fields are the ones declared after the RETURNS clause. All input parameters (those that appear next to the name of the procedure) must be specified; there's no default input parameters as in C++. Also, a view cannot be based on selectable stored procedures. One thing that you must remember is: this kind of procedures exist to solve complex output that's expensive or impractical to perform on the client side or for reporting requirements, but they are still procedures, namely, they aren't updateable nor have a primary key, even if you know one of the fields returned is a unique row identifier. They aren't views so you cannot attach triggers to them. If you know a selectable procedure returns a primary key of some table, use an insert/delete/update statement directly against this table with the primary key to change the underlying data. Of course, to see the change through the procedure, you need to invoke again the procedure.

This page was last updated on 2000-05-26 04:28:46