Writing Stored Procedures and Triggers - by Bill Todd

By: Sriram Balasubramanian

Abstract: Writing Stored Procedures and Triggers - by Bill Todd

    Writing Stored Procedures and Triggers

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.

 

 

 

Server Response from: ETNASC03