by Bill Todd
Bill Todd is President of The Database Group, Inc., a
database consulting and development firm based near Phoenix. He is co-author of
four database programming books and author of over 100 articles. He is a member
of Team Borland, providing technical support on the Borland Internet newsgroups
and is a frequent speaker at Borland Developer Conferences in the U.S. and
Europe. Bill is also a nationally known trainer and has taught programming
classes across the country and overseas. Bill can be reached at bill@dbginc.com
or (480) 802-0177.
Introduction to Interbase Stored
Procedures and Triggers
Advantages of Stored Procedures
When Should You Use Stored Procedures?
Creating a Stored Procedure
Using Create Procedure
Declaring Local Variables
Writing the Body of the Procedure
Creating and Modifying Stored Procedures
Calling Stored Procedures
Triggers
A stored procedure is a program
written in Interbase�s procedure and trigger language that is stored as part of
the database. Stored procedures can be called by client applications or by
other stored procedures or triggers. Triggers are almost identical to stored
procedures with one exception, the way they are called. Triggers are called
automatically when a change to a row in a table occurs. This paper examines
stored procedures first followed by triggers. As you will see, most of what is
said about stored procedures applies to triggers as well.
Advantages of Stored Procedures
The biggest advantage of stored procedures is reduced network traffic. Since
stored procedures are executed by Interbase on the database server machine you
can use them to move much of your data manipulation code to the server. This
eliminates transferring data from the server across the network to the client
for manipulation, and reduced network traffic means increased performance,
particularly across a WAN or any low speed connection.
Stored procedures improve performance in another way. While you can use
queries to do many of the same things that could be done in a stored procedure
a query has a significant disadvantage. Each time the client application sends
a SQL statement to the database server for execution the SQL statement must be
parsed, submitted to the query optimizer and analyzed to formulate an execution
plan. Stored procedures are parsed, optimized and stored in executable form
when they are added to the database. Since a stored procedure does not have to
be parsed and optimized each time it is called it will execute faster than an
equivalent query. Stored procedures can also perform much more complex
operations than a single query.
If more than one application will access the database, stored procedures
will also save maintenance and development time since any application can call
a stored procedure. Maintenance is easier because you can change the stored
procedure without having to change or even recompile any of the client
applications.
Finally, stored procedures are a valuable part of database security since
store procedures can access tables the user does not have rights to. For
example, suppose a user needs to run reports that show total salary by
department and pay grade. While this information must come from the employee
salary table you do not want this user to have access to the salary table and
the individual salaries of all employees. The solution is to write a stored
procedure to calculate the summary information from the salary table and grant
the procedure read access to the table. You can then grant the user execute
access to the stored procedure. The user does not need any rights to the salary
table.
When Should You Use Stored Procedures?
The short answer is, any time you can. There is no disadvantage to using a
stored procedure. There are only two limitations. First, you must be able to
pass any variable information to the stored procedure as parameters or place
the information in a table that the stored procedure can access. Second, the
procedure and trigger language may be too limited for complex calculations.
Using
Create Procedure
Stored procedures are created using the CREATE PROCEDURE statement that has
the following syntax.
CREATE PROCEDURE ProcedureName
<input parameters>
RETURNS
<output parameters>
AS
<local variable declarations>
BEGIN
<procedure statements>
END
Input
parameters provide a way to pass values that are used to modify the procedure�s
behavior from the client application to the procedure. For example, if the
purpose of the procedure is to compute the total monthly payroll for a
department the department number would be passed to the procedure as an input
parameter. Output, or return, parameters provide a way for the procedure to
return information to the client applications. In this example the total
monthly payroll for the department could be returned in an output parameter. A
parameter can have any Interbase data type except BLOB or array. The following
procedure shows the use of both input and output parameters.
CREATE PROCEDURE SUB_TOT_BUDGET
(
HEAD_DEPT CHAR(3)
)
RETURNS (
TOT_BUDGET NUMERIC (15, 2),
AVG_BUDGET NUMERIC (15, 2),
MIN_BUDGET NUMERIC (15, 2),
MAX_BUDGET NUMERIC (15, 2)
)
AS
BEGIN
SELECT
SUM(budget), AVG(budget), MIN(budget), MAX(budget)
FROM
department
WHERE
head_dept = :head_dept
INTO
:tot_budget, :avg_budget, :min_budget, :max_budget;
SUSPEND;
END ^
This
stored procedure declares one input parameter, HEAD_DEPT whose type is CHAR(3)
and four output parameters, TOT_BUDGET, AVG_BUDGET, MIN_BUDGET, and MAX_BUDGET
all of type NUMERIC(15, 2). Both the input parameter and output parameter
declarations must be enclosed in parentheses. The SUSPEND command pauses the
stored procedure until the client fetches the values of the output parameters.
Suspend is covered in more detail later in this paper.
Declaring Local Variables
You
can declare local variables of any Interbase type within a stored procedure.
These variables exist only while the stored procedure is executing and their
scope is local to the procedure. Note that there are no global variables
available when working with stored procedures and triggers and they are not
needed. If you have values that must be shared by two or more procedures either
pass them as parameters or store them in a table.
Local
variables are declared after the AS keyword and before the BEGIN that
identifies the beginning of the body of the procedure. To declare a variable
use the DECLARE VARIABLE statement. For example:
DECLARE VARIABLE OrderCount Integer;
DECLARE VARIABLE TotalAmount NUMERIC(15,2);
Note
that each DECLARE VARIABLE statement can only declare one variable. The
following procedure illustrates the use of the DECLARE VARIABLE statement. It
declares four local variables, ord_stat, hold_stat, cust_no and any_po. Notice
that when a variable is used in the INTO clause of a SELECT statement that a
colon must be added as the first character of the variable name, however, when
the variable is used in any other statement the colon is not required.
CREATE PROCEDURE SHIP_ORDER
(
PO_NUM CHAR(8)
)
AS
DECLARE
VARIABLE ord_stat CHAR(7);
DECLARE
VARIABLE hold_stat CHAR(1);
DECLARE
VARIABLE cust_no INTEGER;
DECLARE
VARIABLE any_po CHAR(8);
BEGIN
SELECT
s.order_status, c.on_hold, c.cust_no
FROM sales
s, customer c
WHERE
po_number = :po_num
AND
s.cust_no = c.cust_no
INTO :ord_stat,
:hold_stat, :cust_no;
/* This
purchase order has been already shipped. */
IF (ord_stat
= 'shipped') THEN
BEGIN
EXCEPTION
order_already_shipped;
SUSPEND;
END
/* Customer
is on hold. */
ELSE IF
(hold_stat = '*') THEN
BEGIN
EXCEPTION
customer_on_hold;
SUSPEND;
END
/*
* If there
is an unpaid balance on orders shipped over 2 months ago,
* put the
customer on hold.
*/
FOR SELECT
po_number
FROM sales
WHERE
cust_no = :cust_no
AND
order_status = 'shipped'
AND paid =
'n'
AND
ship_date < CAST(CURRENT_DATE AS DATE) - 60
INTO
:any_po
DO
BEGIN
EXCEPTION
customer_check;
UPDATE
customer
SET on_hold
= '*'
WHERE
cust_no = :cust_no;
SUSPEND;
END
/*
* Ship the
order.
*/
UPDATE sales
SET
order_status = 'shipped', ship_date = CURRENT_DATE
WHERE
po_number = :po_num;
SUSPEND;
END ^
Writing the Body of the Procedure
The
body of a stored procedure consists of a compound statement. A compound
statement is any number of Interbase procedure and trigger language statements
enclosed in a BEGIN/END block. The body of the following procedure consists of
a SELECT statement and a SUSPEND between the BEGIN and END keywords.
CREATE PROCEDURE SUB_TOT_BUDGET
(
HEAD_DEPT CHAR(3)
)
RETURNS (
TOT_BUDGET NUMERIC (15, 2),
AVG_BUDGET NUMERIC (15, 2),
MIN_BUDGET NUMERIC (15, 2),
MAX_BUDGET NUMERIC (15, 2)
)
AS
BEGIN
SELECT
SUM(budget), AVG(budget), MIN(budget), MAX(budget)
FROM
department
WHERE
head_dept = :head_dept
INTO
:tot_budget, :avg_budget, :min_budget, :max_budget;
SUSPEND;
END ^
Each
statement in the body of the procedure must end with a semicolon.
Other
Language Elements
The
Interbase procedure and trigger language includes all of the constructs of a
basic structured programming language as well as statements unique to working
with data in tables. The following sections describe these elements.
Comments
You
can place comments anywhere in a stored procedure using the /* this is a
comment */ syntax. A comment can span multiple lines but one comment cannot be
nested inside another.
Compound
Statements (BEGIN/END)
The
procedure and trigger language resembles Pascal in that constructs such as if �
then � else and while loops can only contain a single statement. However, the
keywords BEGIN and END can be used to enclose several statements and form a
single compound statement. Do not place a semicolon after either BEGIN or END.
Assignment
Statements
The
procedure and trigger language supports assignment statements of the form
Var1 = Var2 * Var3;
where
Var1 is either a local variable or an output parameter and Var2 and Var3 are
either local variables or input parameters. The expression on the right side of
the assignment can be as complex as you wish and you can use parentheses to
group operations to any depth.
If�Then�Else
The
syntax of the Interbase IF statement is:
IF <conditional expression> THEN
<statement>;
ELSE
<statement>;
where
<statement> can be either a single statement or multiple statements
enclosed in a BEGIN/END block. In addition to the usual logical cooperators (=,
<, >, <=, >=, <>) you can also use the following SQL
operators in the conditional expression.
Conditional Expression
|
Description
|
value BETWEEN value AND value
|
An inclusive range
|
value LIKE value
|
Value on right includes one or more wildcards. Use % for 0 or more
characters and _ for one character.
|
value IN (value1, value2, value3, �)
|
Member of a list of values.
|
value EXISTS (subquery)
|
True if the value matches one of the values returned by the subquery.
|
value ANY (subquery)
|
True if the value matches any of the rows returned by the subquery.
|
value ALL (subquery)
|
True if the value matches all of the rows returned by the subquery.
|
value IS NULL
|
True if value is null.
|
value IS NOT NULL
|
True if value is not null.
|
value CONTAINING value
|
Case insensitive substring search.
|
value STARTING WITH value
|
Case sensitive match at beginning of string.
|
Examples
of valid IF statements are:
IF (any_sales > 0) THEN
BEGIN
EXCEPTION
reassign_sales;
SUSPEND;
END
IF (first IS NOT NULL) THEN
line2 = first ||
' ' || last;
ELSE
line2 = last;
Note
in the example above that the string concatenation operator in the Interbase
procedure and trigger language is || (a double vertical bar) not the + that is
used in many programming languages.
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;
While�Do
The
WHILE�DO statement provides for looping in stored procedures and triggers. The
syntax is:
WHILE (<conditional expression>) DO
<statement>
where
<statement> can be a compound statement enclosed in a BEGIN/END pair.
Note that the parentheses around the conditional expression are required. For
example:
WHILE (i <= 5)
DO
BEGIN
SELECT
language_req[:i] FROM joB
WHERE ((job_code
= :code) AND (job_grade = :grade) AND (job_country = :cty)
AND
(language_req IS NOT NULL))
INTO
:languages;
IF (languages =
' ') THEN /* Prints 'NULL' instead of
blanks */
languages =
'NULL';
i = i +1;
SUSPEND;
END
Using
SQL Statements In Stored Procedures
You
can use SQL SELECT, INSERT, UPDATE and DELETE statements in stored procedures
exactly as you would in a query with only minor changes in syntax. For all of
these statements you can use local variables or input parameters in any place
that a literal value is allowed. For example, in the following INSERT statement
the inserted values are supplied by the input parameters.
CREATE PROCEDURE ADD_EMP_PROJ
(
EMP_NO SMALLINT,
PROJ_ID CHAR(5)
)
AS
BEGIN
BEGIN
INSERT INTO
employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
WHEN SQLCODE -530
DO
EXCEPTION
unknown_emp_id;
END
SUSPEND;
END ^
The
second difference is the addition of the INTO clause to the SELECT statement so
you can select values directly into variables or output parameters as shown in
the following example.
CREATE PROCEDURE CUSTOMER_COUNT
RETURNS (
CUSTOMERCOUNT INTEGER
)
AS
BEGIN
SELECT COUNT(*)
FROM CUSTOMER INTO :CustomerCount;
SUSPEND;
END ^
You
cannot use SQL DDL statements in stored procedures. This restriction applies to
CREATE, ALTER, DROP, SET, GRANT, REVOKE, COMMIT and ROLLBACK.
Using
FOR SELECT�DO
The
preceding example of a SELECT statement that selects one or more values into
variables is fine as long as the SELECT returns a single row. When you need to
process multiple rows returned by a SELECT you must use the FOR SELECT�DO
statement as shown below.
CREATE PROCEDURE ORDER_LIST
(
CUST_NO INTEGER
)
RETURNS (
PO_NUMBER CHAR(8)
)
AS
BEGIN
FOR SELECT
PO_NUMBER FROM SALES
WHERE CUST_NO =
:CUST_NO
INTO :PO_NUMBER
DO
SUSPEND;
END ^
This
procedure takes a customer number as its input parameter and returns all of the
purchase order numbers for that customer from the sales table. Note that the
purchase order numbers are all returned in a single output variable. Here is
how it works. The FOR keyword tells Interbase to open a cursor on the result
set of the SELECT statement. The SELECT statement must include an INTO clause
that assigns each field returned by the select to a local variable or output
parameter. The statement following the keyword DO is executed for each row
returned by the SELECT. The statement following DO can be a compound statement
enclosed in a BEGIN/END block.
Using
Suspend
In
the example above, the SUSPEND command tells the stored procedure to suspend
execution until a fetch is received from the client then load the first project
id into the output parameter and return it to the client. Each time the client
issues a fetch the next project id is loaded into the output parameter and
returned to the client. This continues until all of the rows returned by the
SELECT have been processed. SUSPEND is not used just with FOR SELECT. It is
used anytime a stored procedure returns values to the client to prevent the
stored procedure from terminating until the client has fetched the results. The
following is a very simple example of a procedure that returns a single value
in an output parameter.
CREATE PROCEDURE CUSTOMER_COUNT
RETURNS (
CUSTOMERCOUNT INTEGER
)
AS
BEGIN
SELECT COUNT(*)
FROM CUSTOMER INTO :CustomerCount;
SUSPEND;
END ^
Creating and Modifying Stored Procedures
The
normal method of creating databases and database objects with Interbase is to
create a SQL script in IBConsole or a text editor and then use IBConsole to
execute the script. This creates a problem since both IBConsole and the stored
procedure use a semicolon to terminate statements.
Dropping
and Changing Stored Procedures
To
remove a stored procedure use the DROP PROCEDURE statement as follows:
DROP PROCEDURE Procedure_Name;
Only
SYSDBA or the procedure�s owner can drop a stored procedure. Use the ALTER
PROCEDURE statement to change a stored procedure. ALTER PROCEDURE has exactly
the same syntax as CREATE PROCEDURE, just change the first word from
CREATE to ALTER. At first glance it
would appear that you don�t need ALTER PROCEDURE since you could drop the
procedure then recreate it to make any changes. However, this will not work if
the procedure you are trying to change is called by another procedure. If
procedure One calls procedure Two you cannot drop Two because One depends on
its existence.
If
you use IBConsole to display the metadata for your database and examine the
code that creates the stored procedures you will see that Interbase first
creates all of the procedures with the body empty as shown in the following
example.
CREATE PROCEDURE ADD_EMP_PROJ
(
EMP_NO SMALLINT,
PROJ_ID CHAR(5)
)
AS
BEGIN EXIT;END ^
CREATE PROCEDURE ALL_LANGS
RETURNS (
CODE VARCHAR(5),
GRADE VARCHAR(5),
COUNTRY VARCHAR(15),
LANG VARCHAR(15)
)
AS
BEGIN EXIT;END ^
After
all of the procedures have been created the script generated by Interbase uses ALTER
PROCEDURE to add the body to each stored procedure. For example:
ALTER PROCEDURE ADD_EMP_PROJ
(
EMP_NO SMALLINT,
PROJ_ID CHAR(5)
)
AS
BEGIN
BEGIN
INSERT INTO
employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
WHEN SQLCODE -530
DO
EXCEPTION
unknown_emp_id;
END
SUSPEND;
END ^
By
doing this Interbase eliminates any dependencies between procedures when they
are being created. Since the body of every procedure is empty there can be no
dependencies where one procedure calls another. When the ALTER PROCEDURE
statements are executed they can be run in any order because the shell of any
procedure that the one being altered might call already exists.
Calling Stored Procedures
You
can call stored procedures from other stored procedures or triggers, from IB
Console and from your applications. Interbase stored procedures are divided
into two groups with respect to how they are called. Procedures that return
result values through output parameters are called select procedures because
they can be used in place of a table name in a SQL SELECT statement.
Calling
Select Procedures
Select
procedures assign values to output parameters and call SUSPEND to return those
values. The following is a simple example of a select procedure.
CREATE PROCEDURE CUSTOMER_COUNT
RETURNS (
CUSTOMERCOUNT INTEGER
)
AS
BEGIN
SELECT COUNT(*)
FROM CUSTOMER INTO :CustomerCount;
SUSPEND;
END ^
Figure
1 below shows this procedure being called from IB Console using a SELECT
statement. Note that one row and one column are returned and the column name is
the name of the output parameter.

Figure
1 � Calling a select procedure from IB Console
You
can also call a procedure that requires input parameters from IB Console.
Figure 2 shows an example of calling the following procedure.
ALTER PROCEDURE ORDER_LIST
(
CUST_NO INTEGER
)
RETURNS (
PO_NUMBER CHAR(8)
)
AS
BEGIN
FOR SELECT
PO_NUMBER FROM SALES WHERE CUST_NO = :CUST_NO INTO :PO_NUMBER
DO SUSPEND;
END ^

Figure
2 � Executing a select procedure with input parameters
The
input parameter, CUST_NO, is passed to the procedure in parentheses immediately
following the procedure name in the SELECT statement. Note also that this
SELECT statement includes both a WHERE and an ORDER BY clause. This allows you
to call a stored procedure and return a subset of rows and columns ordered as
you wish. In fact, you can treat a select procedure exactly like a table in the
SELECT statement that calls it and use all of the features of the SQL SELECT
statement to control the result that you get.
You
use exactly the same syntax to call a select procedure from another stored
procedure, a trigger, or your application. The following SQL statement is from
the SQL property of the IBQuery component in the sample application that
accompanies this paper. The only difference here is that the parameters
:CUST_NO and :PO_NUMBER are used to supply the value of the input parameters.
SELECT * FROM ORDER_LIST(:CUST_NO)
WHERE (PO_NUMBER > :PO_NUMBER)
ORDER BY PO_NUMBER DESC
Figure
3 shows the form used to gather the input data from the user and execute the
stored procedure.

Figure
3 � The sample app that runs a select procedure
The
following code is from the Execute button�s OnClick event handler and shows how
the values are assigned to the input parameters before executing the stored
procedure.
procedure TProcForm.SelectBtnClick(Sender: TObject);
begin
with ProcDm.OrderListQry do
begin
Params.ParamByName('CUST_NO').Value := CustomerNoEdit.Text;
Params.ParamByName('PO_NUMBER').Value := OrderNoEdit.Text;
Open;
end; //with
end;
Calling
Non-Select Procedures
The
following stored procedure is an example of a non-select procedure, that is, a
procedure that does not return any results. This procedure takes a single input
parameter, FACTOR, and adjusts the minimum and maximum salaries in the Job
table by that factor.
CREATE PROCEDURE ADJUST_SALARY_RANGE
(
FACTOR FLOAT
)
AS
BEGIN
UPDATE JOB SET
MIN_SALARY = MIN_SALARY * :FACTOR, MAX_SALARY = MAX_SALARY * :FACTOR;
END ^
Use
the EXECUTE PROCEDURE command to run this stored procedure from a trigger,
another stored procedure, or IB Console. For example:
EXECUTE PROCEDURE ADJUST_SALARY_RANGE(1.1);
To
run the procedure from your application use an IBStoredProc component and the
following code.
with ProcDm.AdjustSalRngProc do
begin
Params.ParamByName('Factor').Value := StrToFloat(FactorEdit.Text);
ExecProc;
end; //with
At
design time set the Database property of the IBStoredProc component to the
IBDatabase component for the database that contains the stored procedure. Set
the StoredProcName property to the name of the stored procedure you want to
execute. Use the Params property�s property editor to create any required input
parameter objects and assign their data types and default values.
Getting
the Top N or Bottom N Records
In
InterBase 6.5 you can easily get the top N or bottom N rows using the ROWS
clause. The ROWS clause is an extension to standard SQL that lets you control
the number of rows returned by SELECT or the number of rows affected by UPDATE
and DELETE. The general syntax is:
ROWS
<lower_value> [TO <upper_value>] [ BY <step_value>] [PERCENT]
[WITH TIES]
The
location of the ROWS clause in the hierarchy of clauses in a SQL statement is
shown below.
<from_clause>
<where_clause>
<group_by_clause>
<having_clause>
<order_by_clause>
<rows_clause>
The
best way to understand what the ROWS clause can do for you is to look at some
examples. All of the examples in this article use the sample Employee database
that comes with InterBase. The first example shows the use of ROWS in
conjunction with the ORDER BY clause to select the five records with the
largest discount.
SELECT
* FROM SALES
ORDER
BY DISCOUNT DESC
ROWS
1 TO 5
The
ORDER BY clause sorts the returned records in descending order by DISCOUNT and
the ROWS clause limits the number of rows returned to the first five. Although
you can use ROWS without ORDER BY it would be unusual to do so since limiting
the number of rows infers that you want the first N rows based on some order.
As long as the first row you want returned is the first row in the result set
you can shorten the query to
SELECT
* FROM SALES
ORDER
BY DISCOUNT DESC
ROWS
5
If
you want all of the rows that have the same values in the fields in the ORDER
BY clause as the first five rows use the WITH TIES option shown below.
SELECT
* FROM SALES
ORDER
BY DISCOUNT DESC
ROWS
1 TO 5 WITH TIES
This
query returns eight rows instead of five because some of the values that appear
in the DISCOUNT field in the first five rows also appear in other rows. Note
that you must use an ORDER BY clause when you use WITH TIES.
Instead
of a fixed number of rows you can also request a percentage of the rows in the
result set as shown in the following query which returns the top 10% of the
rows in SALES by TOTAL_VALUE.
SELECT
* FROM SALES
ORDER
BY TOTAL_VALUE DESC
ROWS
10 PERCENT
Another
interesting use of the PERCENT option is to select an evenly distributed subset
of rows. This query returns five rows with each of those rows separated from
its neighbor by 20% of the rows in the table. Although this query does not use
an ORDER BY clause it could. For example, to obtain an even distribution of
rows geographically you could order the table by state or zip code.
SELECT
* FROM CUSTOMER
ROWS
100 BY 20 PERCENT
You
can also use the ROWS clause in conjunction with aggregates. The following query returns the top three
sales reps based on the total value of their orders while the second query
returns the top 25% of sales reps based on the total value of their orders.
SELECT
SALES_REP, SUM(TOTAL_VALUE) AS TOTAL
FROM SALES
GROUP BY SALES_REP
ORDER BY 2 DESC
ROWS 3
SELECT
SALES_REP, SUM(TOTAL_VALUE) AS TOTAL
FROM SALES
GROUP BY SALES_REP
ORDER BY 2 DESC
ROWS 25 PERCENT
The
ROWS clause can also be used with UPDATE to give the ten highest paid employees
a raise.
UPDATE
EMPLOYEE
SET
SALARY = SALARY * 1.01
ORDER
BY SALARY DESC
ROWS
10
You
can also use ROWS with DELETE to delete the ten highest paid employees. If you
want to delete everyone whose salary is equal to one of the ten highest paid
employees just add the WITH TIES option.
DELETE
FROM EMPLOYEE
ORDER
BY SALARY DESC
ROWS
10
For
earlier versions of InterBase you can easily get the top N or bottom N rows
using stored procedures. The following stored procedure returns the employees
with the top salaries. The Top_Salaries stored procedure takes a single
parameter which is the number of records to return. A local integer variable,
I, is used to count the number of records returned. The FOR SELECT statement
gets all of the employee records sorted in descending order by salary. The
statements in the DO clause increment the counter variable I and check to see
if I is greater than the requested number of records. If so the call to EXIT
causes the procedure to end. If this record is to be returned the SUSPEND
command pauses the procedure until the record is fetched by the client.
CREATE PROCEDURE TOP_SALARIES
(
NUM_RECORDS INTEGER
)
RETURNS
(
LAST_NAME VARCHAR(20),
FIRST_NAME VARCHAR(15),
DEPT_NO CHAR(3),
SALARY NUMERIC(15, 2)
)
AS
DECLARE VARIABLE I INT;
BEGIN
I = 0;
FOR SELECT LAST_NAME, FIRST_NAME, DEPT_NO, SALARY
FROM EMPLOYEE ORDER BY SALARY DESC
INTO :LAST_NAME, :FIRST_NAME, :DEPT_NO, :SALARY
DO BEGIN
I = I + 1;
IF (I > NUM_RECORDS) THEN EXIT;
SUSPEND;
END
END
Getting the bottom N records is done in exactly the same
way except the FOR SELECT statement sorts the records in ascending order
instead of descending order. The sample application uses an IBQuery component
with the following SQL statement to call the stored procedure.
procedure TProcForm.TopBtnClick(Sender: TObject);
begin
with ProcDm.TopQry do
begin
SQL.Clear;
SQL.Add('SELECT * FROM TOP_SALARIES(' + NumEdit.Text + ')');
Open;
end; //with
end;
Using
BLOBs In Stored Procedures
Although
the Interbase manuals say that you cannot use BLOBs in stored procedures this
is not true. The following select procedure returns records that include a BLOB
field.
CREATE PROCEDURE GET_JOBS
RETURNS
(
JOB_CODE VARCHAR(5),
JOB_GRADE SMALLINT,
JOB_COUNTRY VARCHAR(15),
JOB_TITLE VARCHAR(25),
MIN_SALARY NUMERIC(15, 2),
MAX_SALARY NUMERIC(15, 2),
JOB_REQUIREMENT BLOB
)
AS
begin
for select JOB_CODE, JOB_GRADE, JOB_COUNTRY, JOB_TITLE,
MIN_SALARY, MAX_SALARY, JOB_REQUIREMENT
from JOB into :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :JOB_TITLE,
:MIN_SALARY, :MAX_SALARY, :JOB_REQUIREMENT do
suspend;
end
The
following procedure lets you insert a new record that includes a BLOB field.
CREATE PROCEDURE ADD_JOB
(
JOB_CODE VARCHAR(5),
JOB_GRADE SMALLINT,
JOB_COUNTRY VARCHAR(15),
JOB_TITLE VARCHAR(25),
MIN_SALARY NUMERIC(15, 2),
MAX_SALARY NUMERIC(15, 2),
JOB_REQUIREMENT BLOB
)
AS
begin
insert into JOB(JOB_CODE, JOB_GRADE, JOB_COUNTRY, JOB_TITLE,
MIN_SALARY, MAX_SALARY, JOB_REQUIREMENT)
values(:JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :JOB_TITLE,
:MIN_SALARY, :MAX_SALARY, :JOB_REQUIREMENT);
end
The
following code is from the sample application�s Add Job button�s OnClick event
handler. An IBStoredProc component is used to run the stored procedure. The
code below assigns values to each of the input parameters then calls the
IBStoredProc�s ExecProc method to call the stored procedure.
procedure TProcForm.AddJobBtnClick(Sender: TObject);
begin
with ProcDm.AddJobProc do
begin
ParamByName('Job_Code').AsString := JobCodeEdit.Text;
ParamByName('Job_Grade').AsInteger := StrToInt(JobGradeEdit.Text);
ParamByName('Job_Country').AsString := JobCountryEdit.Text;
ParamByName('Job_Title').AsString := JobTitleEdit.Text;
ParamByName('Min_Salary').AsFloat := StrToFloat(MinSalaryEdit.Text);
ParamByName('Max_Salary').AsFloat := StrToFloat(MaxSalaryEdit.Text);
ParamByName('Job_Requirement').AsString := JobReqMemo.Text;
ExecProc;
end; //with
end;
However,
there is no way to manipulate the BLOB field value within a stored procedure.
Using
Recursion
Hierarchical
data structures such as bills of material and organization charts are common in
business. You can retrieve hierarchical data easily using recursive stored
procedures. The following shows the hierarchical structure of the records from
the Department table in the Employee database.
Corporate Headquarters
Sales and Marketing
Marketing
Field Office: East Coast
Field Office: Canada
Pacific Rim Headquarters
Field Office: Japan
Field Office: Singapore
European Headquarters
Field Office: Switzerland
Field Office: France
Field Office: Italy
Engineering
Software Products Div.
Software Development
Quality Assurance
Customer Support
Consumer Electronics Div.
Research and Development
Customer Services
Finance
Each
row in the Department table contains its number in the DEPT_NO column, which is
the primary key. Each row also contains the department number of its parent
department in the HEAD_DEPT column. The following stored procedure walks the
tree structure and returns the record hierarchy. In addition to the name,
number and parent number of each department the department�s indentation level
in the tree is returned in the DEPT_LEVEL output parameter and the name
preceded by the correct number of spaces to produce an indented list is
returned in the PADDED_NAME output parameter. The input parameters let you
specify the department number of the record whose children should be returned
and the starting level number, normally zero.
CREATE PROCEDURE "DEPT_TREE"
(
"PARENT_DEPT" CHAR(3),
"PARENT_LEVEL" INTEGER
)
RETURNS
(
"DEPT_NAME" VARCHAR(25),
"DEPT_NUMBER" CHAR(3),
"PARENT_NUMBER" CHAR(3),
"DEPT_LEVEL" INTEGER,
"PADDED_NAME" VARCHAR(50)
)
AS
DECLARE VARIABLE PAD VARCHAR(25);
DECLARE VARIABLE I INTEGER;
BEGIN
/* SELECT THE FIRST LEVEL DEPARTMENTS */
FOR SELECT D.DEPARTMENT, D.DEPT_NO, D.HEAD_DEPT
FROM DEPARTMENT D
WHERE D.HEAD_DEPT = :PARENT_DEPT
INTO :DEPT_NAME, :DEPT_NUMBER, :PARENT_NUMBER
DO
BEGIN
/* INCREMENT THE LEVEL IN THE TREE */
DEPT_LEVEL = PARENT_LEVEL + 1;
/* PUT TWO SPACES IN PAD FOR EACH LEVEL DOWN IN THE TREE */
PAD = '';
I = 0;
WHILE (I < DEPT_LEVEL) DO
BEGIN
I = I + 1;
PAD = PAD || ' ';
END
/* CONCATENATE THE SPACES AND THE DEPARTMENT NAME */
PADDED_NAME = PAD || DEPT_NAME;
/* RETURN THE ROW */
SUSPEND;
/* FOR EACH TOP LEVEL ROW CALL THIS PROCEDURE AGAIN GET TO THE ROW'S CHILDREN */
FOR SELECT P.DEPT_NAME, P.DEPT_NUMBER, P.PARENT_NUMBER, DEPT_LEVEL
FROM DEPT_TREE(:DEPT_NUMBER, :DEPT_LEVEL) P
INTO :DEPT_NAME, :DEPT_NUMBER, :PARENT_NUMBER, :DEPT_LEVEL
DO
BEGIN
/* BUILD THE PAD STRING FOR THE CHILD ROW */
PAD = '';
I = 0;
WHILE (I < DEPT_LEVEL) DO
BEGIN
I = I + 1;
PAD = PAD || ' ';
END
/* CONCATENATE THE SPACES AND THE DEPARTMENT NAME */
PADDED_NAME = PAD || DEPT_NAME;
/* RETURN THE CHILD ROW */
SUSPEND;
END
END
END;
The
problem with this procedure is that it returns all the children of the row
whose department number is passed in the PARENT_DEPT parameter but does not
return the PARENT_DEPT row itself. You
can solve this problem by calling the DEPT_TREE procedure from the following
procedure. This procedure takes a single input parameter, TOP_DEPT, which
contains the department number of the department you want to start with. It
returns the data for this department then calls DEPT_TREE to return all of the
child rows.
CREATE PROCEDURE "DEPT_CHART"
(
"TOP_DEPT" CHAR(3)
)
RETURNS
(
"DEPT_NAME" VARCHAR(25),
"DEPT_NUMBER" CHAR(3),
"PARENT_NUMBER" CHAR(3),
"DEPT_LEVEL" INTEGER,
"PADDED_NAME" VARCHAR(50)
)
AS
BEGIN
/* SELECT THE DEPARTMENT TO START WITH */
FOR SELECT D.DEPARTMENT, D.DEPT_NO, D.HEAD_DEPT
FROM DEPARTMENT D
WHERE D.DEPT_NO = :TOP_DEPT
INTO :DEPT_NAME, :DEPT_NUMBER, :PARENT_NUMBER
DO
BEGIN
DEPT_LEVEL = 0;
PADDED_NAME = DEPT_NAME;
SUSPEND;
/* GET ALL OF THE CHILDREN OF THE STARTING DEPARTMENT */
FOR SELECT P.DEPT_NAME, P.DEPT_NUMBER, P.PARENT_NUMBER, P.DEPT_LEVEL, P.PADDED_NAME
FROM DEPT_TREE(:DEPT_NUMBER, :DEPT_LEVEL) P
INTO :DEPT_NAME, :DEPT_NUMBER, :PARENT_NUMBER, :DEPT_LEVEL, :PADDED_NAME
DO
BEGIN
SUSPEND;
END
END
END;
Building
Complex Data Summaries
Suppose
you need a report that shows the name of each sales representative with their
sales for each of four date ranges and the total sales for all four data
ranges. With most databases you would have to build a temporary table to get
this result set. With InterBase you can do it easily with the following stored
procedure.
This
procedure has eight input parameters that supply the starting and ending date
for each of the four periods. The procedure begins with a FOR SELECT statement
that selects each employee whose JOB_CODE is SRep from the EMPLOYEE table. The
FOR SELECT statement puts the value from the FULL_NAME field into the FULL_NAME
output parameter and the value of the EMP_NO field into the EMP_NO variable.
The
DO clause is executed once for each employee record that is returned by the FOR
SELECT statement. In the DO clause four SELECT statements get the total sales
for each of the four periods into the respective output variables (SALES1,
SALES2, SALES3 and SALES4). Next, four IF statements check the four totals to
see if they are null. Any nulls are changed to zero. This is necessary because
null plus anything is null so if any one of the four sales totals was null the
total would be null, regardless of the values for the other three periods.
Finally, the total is calculated by summing the four period totals and a call
to SUSPEND returns the output parameters to the caller.
CREATE PROCEDURE SALES_HISTORY_REPORT
(
PERIOD1_START TIMESTAMP,
PERIOD1_END TIMESTAMP,
PERIOD2_START TIMESTAMP,
PERIOD2_END TIMESTAMP,
PERIOD3_START TIMESTAMP,
PERIOD3_END TIMESTAMP,
PERIOD4_START TIMESTAMP,
PERIOD4_END TIMESTAMP
)
RETURNS
(
FULL_NAME VARCHAR(40),
SALES1 NUMERIC(15, 2),
SALES2 NUMERIC(15, 2),
SALES3 NUMERIC(15, 2),
SALES4 NUMERIC(15, 2),
SALES_TOTAL NUMERIC(15, 2)
)
AS
DECLARE VARIABLE EMP_NO INTEGER;
BEGIN
/* Select each employee from the EMPLOYEE table whose JOB_CODE is SRep. */
FOR SELECT E.EMP_NO, E.FULL_NAME FROM EMPLOYEE E
WHERE E.JOB_CODE = 'SRep'
INTO :EMP_NO, :FULL_NAME
DO
BEGIN
/* For each sales rep selected from the employee table select the total sales for each
period into the appropriate output parameter.
*/
SELECT SUM(S.TOTAL_VALUE)
FROM SALES S
WHERE S.ORDER_DATE >= :PERIOD1_START AND S.ORDER_DATE <= :PERIOD1_END AND S.SALES_REP = :EMP_NO
INTO :SALES1;
SELECT SUM(S.TOTAL_VALUE)
FROM SALES S
WHERE S.ORDER_DATE >= :PERIOD2_START AND S.ORDER_DATE <= :PERIOD2_END AND S.SALES_REP = :EMP_NO
INTO :SALES2;
SELECT SUM(S.TOTAL_VALUE)
FROM SALES S
WHERE S.ORDER_DATE >= :PERIOD3_START AND S.ORDER_DATE <= :PERIOD3_END AND S.SALES_REP = :EMP_NO
INTO :SALES3;
SELECT SUM(S.TOTAL_VALUE)
FROM SALES S
WHERE S.ORDER_DATE >= :PERIOD4_START AND S.ORDER_DATE <= :PERIOD4_END AND S.SALES_REP = :EMP_NO
INTO :SALES4;
/* If the sales for any one period is null the total for all periods will be null. To
prevent this set any null period values to zero.
*/
IF (SALES1 IS NULL) THEN SALES1 = 0;
IF (SALES2 IS NULL) THEN SALES2 = 0;
IF (SALES3 IS NULL) THEN SALES3 = 0;
IF (SALES4 IS NULL) THEN SALES4 = 0;
SALES_TOTAL = SALES1 + SALES2 + SALES3 + SALES4;
SUSPEND;
END
END;
If
you want to test this procedure add it to the sample Employee database and run
the following query from IBConsole.
SELECT * FROM SALES_HISTORY_REPORT('1/1/1993', '3/31/1993', '4/1/1993', '6/30/1993',
'7/1/1993', '9/30/1993', '10/1/1993', '12/31/1993')
String
Functions
Here
are some handy string manipulation functions written in the InterBase procedure
and trigger language by Ivan Prenosil (http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm)
and reproduced here with his permission.
RightTrim
CREATE PROCEDURE TrimRight (str VARCHAR(10))
RETURNS (ret VARCHAR(10)) AS
BEGIN
ret = str;
IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END
IF (str = '') THEN BEGIN ret = ''; SUSPEND; EXIT; END
BEGIN
ret = CAST (str AS char(9));
ret = CAST (str AS char(8));
ret = CAST (str AS char(7));
ret = CAST (str AS char(6));
ret = CAST (str AS char(5));
ret = CAST (str AS char(4));
ret = CAST (str AS char(3));
ret = CAST (str AS char(2));
ret = CAST (str AS char(1));
SUSPEND;
WHEN ANY DO SUSPEND;
END
END
This
procedure works because casting a VARCHAR or CHAR to a smaller size truncates
the string if the dropped characters are spaces and will raise an exception if
the characters to be dropped are not spaces.
Truncate
a String
CREATE PROCEDURE Trunc10To5 (a varchar(10))
RETURNS (ret varchar(5)) AS
BEGIN
ret = '';
ret = a;
WHEN ANY DO EXIT;
END
When
you assign a string to a shorter variable the string is truncated and assigned to
the variable but an exception is raised. All you have to do is use WHEN ANY DO
to ignore the exception. The limitation of this procedure is that the size you
are truncating to is fixed.
String
Length
CREATE PROCEDURE Len (str VARCHAR(100))
RETURNS (len INTEGER) AS
DECLARE VARIABLE pat VARCHAR(100);
BEGIN
len = null;
IF (str IS NULL) THEN EXIT;
pat = '';
len = 0;
WHILE (NOT str LIKE pat) DO BEGIN
pat = pat || '_';
len = len + 1;
END
END
This
procedure uses a WHILE loop and the LIKE operator to count the characters in
the string.
Position
of a Substring Within a String
CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
IF (SubStr IS NULL OR Str IS NULL) THEN
BEGIN
Pos = NULL;
EXIT;
END
SubStr2 = SubStr || '%';
Tmp = '';
Pos = 1;
WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
SubStr2 = '_' || SubStr2;
Tmp = Tmp || '_';
Pos = Pos + 1;
END
IF (Str LIKE Tmp) THEN Pos = 0;
END
Here
the tmp variable stops the loop when the number of iterations equals the length
of the string. This works because when the number of underscore characters in
tmp equals the length of the string Str NOT LIKE Tmp will be false. With each iteration through the loop an
underscore is prepended to SubStr2 to test a new location in Str.
Triggers
Triggers
are identical to stored procedures with the following exceptions.
1.
Triggers are called automatically when the data in the table
the trigger is attached to changes.
2.
Triggers take no input parameters.
3.
Triggers do not return values
4.
Triggers are created by the CREATE TRIGGER statement.
Using
CREATE TRIGGER
The
following CREATE TRIGGER statement shows all of the elements of the CREATE
TRIGGER syntax. The keywords CREATE TRIGGER are followed by the trigger name,
the keyword FOR then the name of the table the trigger is attached to. Next
comes either ACTIVE or INACTIVE to indicate whether the trigger will function
or not. If the trigger is inactive it will not execute. You will see how to
activate and deactivate a trigger later in this paper. The next element of the
CREATE TRIGGER statement indicates when the trigger will fire. This will be one
of the following six conditions.
1.
BEFORE UPDATE
2.
AFTER UPDATE
3.
BEFORE INSERT
4.
AFTER INSERT
5.
BEFORE DELETE
6.
AFTER DELETE
Next
comes the optional POSITION keyword followed by an integer. Interbase allows
you to attach any number of triggers to the same event. For example, you could
have four triggers for the employee table all of which fire after update. This
is a great feature because it allows you to modularize your code. However, the
order in which the triggers fire may be important. The POSITION keyword lets
you control the firing order based on the integer number supplied. In this
example the trigger shown below would fire first because its position is zero.
If there were three other triggers you might assign them positions of 10, 20
and 30. It is a good idea to leave a gap in the numbering sequence so you can
easily insert another trigger at any point in the firing order in the future.
CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
ACTIVE
AFTER UPDATE
POSITION 0
AS
BEGIN
IF (old.salary
<> new.salary) THEN
INSERT INTO
salary_history
(emp_no, change_date, updater_id, old_salary, percent_change)
VALUES (
old.emp_no,
CURRENT_DATE,
user,
old.salary,
(new.salary - old.salary) * 100 / old.salary);
END ^
After
the keyword AS comes any local variable declarations using the same DECLARE
VARIABLE syntax that was used for a stored procedure. Finally, comes the
procedure body enclosed in a BEGIN/END block.
One
thing to keep in mind as you begin using triggers is that a single change to a
record in a database can cause many triggers to fire. A change to table A can
fire a trigger that updates table B. The update to table B can fire a trigger
that inserts a record in table C. Inserting a new record in table C can fire a
trigger that updates table D and so on. The second important point about
triggers is that a trigger is part of the transaction that caused it to fire.
This means that if you start a transaction and update a row which causes a
trigger to fire and that trigger changes a table which causes another trigger
to fire which updates another table and you then rollback the transaction your
change and all of the changes made by all of the triggers fired by your change
will be rolled back.
Before
or After?
A
trigger should fire before the row is updated if you want to change the value
of one or more of the columns in the row being updated or if you may want to
block the change to the row by raising an exception. For example, you might use
a before delete trigger to prevent the user from deleting a customer record if
the customer has placed an order within the last two years.
After
triggers are used when you want to ensure that the row action as been
successfully completed before you take any other action. The trigger above is a
good example. This trigger inserts a row into the salary_history table any time
an employee�s salary is changed. The history row contains both the old salary
and the percent change. Since the update to the employee record could fail for
many reasons, such as a value in a field that violates a constraint, you do not
want to create the history record until after the update has successfully
completed.
Using
Old and New
In
the sample trigger above you see column names preceded by the �old� and �new�
modifiers. In the body of the trigger Interbase makes both the old and new
value of each column available, for example old.salary and new.salary. Using
the old and new values you can easily create history records, calculate the
amount or percentage of change in a numeric value, find records in another
table that match either the old or new value or do anything else you can think
of.
Raising
Exceptions
In
a BEFORE trigger you can prevent the row update that fired the trigger from
taking place by raising an exception. Before you can raise an exception you
must create the exception using the CREATE EXCEPTION statement. For example,
CREATE EXCEPTION CUSTOMER_STILL_CURRENT �This customer has ordered within the last two years.�
where
the keywords CREATE EXCEPTION are followed by the exception�s name and the text
of the error message for the exception. To raise this exception in a trigger or
stored procedure use the EXCEPTION keyword as shown below.
EXCEPTION CUSTOMER_STILL_CURRENT;
When
you raise an exception execution of the trigger or stored procedure terminates.
Any statements in the trigger or stored procedure that follow the EXCEPTION
statement are not executed. In the case of a BEFORE trigger the update that
fired the trigger is aborted. Finally, the exception�s error message is
returned to the application. You can drop an exception using the DROP EXCEPTION
statement and alter the message associated with an exception using the ALTER
EXCEPTION STATEMENT. For example:
ALTER EXCEPTION CUSTOMER_STILL_CURRENT �This customer is still active.�;
DROP EXCEPTION CUSTOMER_STILL_CURRENT;
Using
Generators
Interbase
does not have an autoincrementing field type. Instead it has a more flexible
tool called a generator. A generator returns an incrementing value every time
you call it. To create a generator use CREATE GENERATOR as follows:
CREATE GENERATOR CUSTOMER_ID;
Note
that there is no DROP GENERATOR statement. Once you create a generator it is
part of the database forever. This is not really a problem since a generator is
just a single four byte entry in one of the system tables. To get the next value
from a generator use the GEN_ID function. For example:
GEN_ID(CUSTOMER_ID, 1);
The
first parameter is the name of the generator and the second is the increment.
In the example above the value returned will be one greater than the last
value. The increment can be set to any value including zero, which is very
handy for obtaining the current value of the generator without changing it. You
can also change the current value of a generator any time you wish using the
SET GENERATOR statement as follows:
SET GENERATOR CUSTOMER_ID TO 1000;
Note
that a generator will not be rolled back if you call GEN_ID from within a
transaction and then rollback the transaction. Generators are frequently used
in triggers to provide a unique surrogate primary key value as in the following
example.
CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.emp_no =
gen_id(emp_no_gen, 1);
END ^
You
can also call GEN_ID in a stored procedure that returns the value from the
generator in an output parameter to the client application. The client can then
assign the value to the primary key and display it to the user when the client
creates a new record but before it has been posted. In this case you might also
want to include a trigger, like the one above, in case a record is inserted by
some client that does not provide the primary key value. All you need to do is
alter the code to:
IF new.emp_no IS NULL THEN new.emp_no = gen_id(emp_no_gen, 1);
Now
the trigger will supply a value for the key field only if it is null.
Changing
and Deleting Triggers
You
can use the ALTER TRIGGER statement to alter either the header information or
the body of the trigger. The most common use of altering the header information
is to activate or deactivate a trigger. Another use is to change the position
of the trigger. The following statement will inactivate the trigger.
ALTER TRIGGER SET_EMP_NO INACTIVE;
To
alter just the body of the trigger supply its name with no other header
information and the new body as shown below. You can also change the header and
body at the same time.
ALTER TRIGGER SET_EMP_NO
AS
BEGIN
IF new.emp_no IS NULL THEN new.emp_no = gen_id(emp_no_gen, 1);
END ^
To
drop a trigger use DROP TRIGGER. For example:
DROP
TRIGGER SET_EMP_NO;
Summary
Stored
procedures and triggers are at the heart of developing client/server
applications. Using stored procedures and triggers you can:
1.
Reduce network traffic.
2.
Create a common set of business rules in the database that
apply to all client applications.
3.
Provide common routines that are available to all client
applications thereby reducing application development and maintenance time.
4.
Centralize processing on the server and reduce the hardware
requirements of the client.
5.
Improve application performance.
To download the sample code for this paper click here.
For
more information on Interbase stored procedures and triggers see the Data
Definition Guide and the Language Guide as well as the stored procedures and
triggers in the sample EMPLOYEE.GDB database.
Connect with Us