The Ins and Outs of InterBase SQL (Tutorial presented at BorCon 2003)

The Ins and Outs of InterBase SQL

Preconference Tutorial

by Wayne Niddery, Logic Fundamentals, Inc.

with contributions from Craig Stuntz, Vertex Systems Corporation

Abstract

The different join types (SQL-92 standard) are explained and demonstrated including some multi-table joins. All advanced SQL features available in InterBase are covered with strong emphasis on how to write well-tuned SQL queries. Other features such as the powerful ROWS operator are shown, not only for Select statements, but with Update and Delete statements. Lots of samples demonstrate best practices for getting good performance from InterBase including tips on construction of indexes. Tips on structuring queries for best performance in InterBase and common sources of performance problems are included. You’ll also see examples of Stored Procedures and Triggers demonstrating the powerful yet elegant features of InterBase.

Assumptions

This tutorial assumes some basic familiarity with simple SQL statements executed against single tables at a time. This includes Select, Insert, Update, and Delete statements. Most examples are based on the existing Employees example database that ships with InterBase.

Joins

One of the most powerful abilities of a relational database server are Joins. Joins allow the developer to combine data from multiple tables in to a single result set - building virtual tables based on the relationship between physical tables. It is common when designing databases to use a lot of separate tables for various attributes of a single entity. For example, you might store records of an employee’s salary, residence, etc., in separate tables rather than expanding the EMPLOYEE table. There are a lot of advantages to doing this (referred to as normalization), for example the employee might have more than one residence, or you need to keep a history of past salary changes.Without being able to combine data from these different tables it would be very difficult to perform many commonly needed tasks and difficult to get good performance.

A Join is the way to tell the database server how to match rows from two different tables. You can then return columns from both tables in your result set.

Equijoins

InterBase, as most or all SQL databases, accepts equijoins. An example of an equijoin:

SELECT
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID
FROM EMPLOYEE, EMPLOYEE_PROJECT
WHERE
  EMPLOYEE_PROJECT.EMP_NO = EMPLOYEE.EMP_NO
ORDER BY
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID

Any number of tables can be listed in the FROM clause with conditions in the WHERE clause making the necessary relationships. The above example is equivalent to an INNER JOIN (shown below). Many databases (but not InterBase) also provide a means of adding modifiers to equijoins in order to produce results equivalent to OUTER JOINS, typically by adding a "+" or "*" to one side or the other of the condition relating the two tables.

While the above example is clear enough and can certainly be used, as a query becomes more complex and requires more tables, using the Equijoin syntax can become difficult to read and understand. This is a result of the conditions representing the relationships being separated by distance from the naming of the tables; and by these conditions being mixed together with other conditions in the WHERE clause that are in addition to the relations. For this reason it is strongly recommended to use the SQL-92 standard of specifying joins.

SELECT *
  FROM TABLEA, TABLEB, TABLEC
WHERE
  TABLEA.KEY = TABLEB.KEY AND
  TABLEB.COUNTRY = 'US' AND
  TABLEA.NAME LIKE 'Smith%' AND
  TABLEB.STATE = TABLEC.STATE AND
  TABLEC.STATUS = 'ACTIVE'

How quickly can you identify the relation between TABLEB and TABLEC or which conditions are specifying such relations? This example is still relatively tame, I have seen much worse! Under SQL-92 it would be rewritten as:

SELECT *
  FROM TABLEA
INNER JOIN TABLEB
  ON TABLEA.KEY = TABLEB.KEY
INNER JOIN TABLEC
  ON TABLEB.STATE = TABLEC.STATE
WHERE
  TABLEB.COUNTRY = 'US' AND
  TABLEA.NAME LIKE 'Smith%' AND
  TABLEC.STATUS = 'ACTIVE'

Here it is far more clear what the relationship between the tables are as they are specified along with the tables and separated from other non- relational conditions in the WHERE clause.

The rest of this tutorial will use only SQL-92 examples.

Under the SQL-92 standard there are two basic join types, Inner Joins and Outer Joins

Inner Joins

Let’s imagine we want to return a result set with employee names and the project IDs of the projects they’re working on. We need to tell the database server to match EMPLOYEE_PROJECT rows with rows in the EMPLOYEE table. The relation between these two tables is made via the EMP_NO column. For each EMPLOYEE_PROJECT row, we want to find the row in the EMPLOYEE table with the same EMP_NO value.

The following query uses an inner join to produce the desired result:

SELECT
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID
FROM EMPLOYEE
INNER JOIN EMPLOYEE_PROJECT
  ON EMPLOYEE_PROJECT.EMP_NO = EMPLOYEE.EMP_NO
ORDER BY
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID

Note that with an inner join, it does not matter which table is specified first as far as the results are concerned; the above join could also be written as:

FROM EMPLOYEE_PROJECT
INNER JOIN EMPLOYEE
  ON EMPLOYEE_PROJECT.EMP_NO = EMPLOYEE.EMP_NO

INNER JOIN means that one or more records with a given EMP_NO must be present in both the EMPLOYEE_PROJECT and EMPLOYEE tables in order to produce a row in the result set. For example, if there is a row in the EMPLOYEE table for employee #10 but there is no row in the EMPLOYEE_PROJECT table for employee #10 then employee #10.will not be returned by this query.

As mentioned above, an inner can be written with either table first and you will get the same results. As a general rule though, you should place whichever table is primary to the query, e.g. if the basic query is intended to select Employee information then EMPLOYEE is the primary table that should appear in the FROM clause. This makes the query more understandable and usually also contributes to better performance.

Form most databases, including Interbase, INNER is the default type of join, so if no join type is specified in the query (if we write JOIN EMPLOYEE instead of INNER JOIN EMPLOYEE) then an INNER JOIN will be used.

Outer Joins

Outer joins can be further divided into LEFT, RIGHT and FULL outer joins. Of these, LEFT joins are by far the most common, and FULL joins are rarely used. An Outer Join must always specify which of the three types is to be used, but because of this, most databases allow the term OUTER to be omitted (just as INNER can be omitted as mentioned above). Thus one can simply refer to Left Joins, etc.

Left Joins

This type of JOIN returns at least one row for each row in the "left" table, whether or not there is a corresponding row in the "right" table.

The terms "left" and "right" may seem confusing at first, but that’s what the SQL Standard calls them. The terms are based on the fact that we (at least most of the western world) read from left to right, therefore just remember that left refers to the table mentioned first and right refers to the second table being joined to the first. In the example that follows, the EMPLOYEE table is the left table, and the EMPLOYEE_PROJECT table is the right table.

As previously mentioned, the inner join example we saw won’t return any rows in its result set for employees having no projects. What if we wanted to see these employees anyway? Use a LEFT OUTER JOIN instead of an INNER JOIN:

SELECT
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID
FROM EMPLOYEE
LEFT OUTER JOIN EMPLOYEE_PROJECT
  ON EMPLOYEE_PROJECT.EMP_NO = EMPLOYEE.EMP_NO
ORDER BY
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID

In this result set, the column EMPLOYEE_PROJECT.PROJ_ID will be NULL in rows for employees who don’t have any projects. This might seem confusing since there are no records in the EMPLOYEE_PROJECT table with a NULL state in the PROJ_ID field, but it simply indicates that there is no matching record. In other words, if a column is NULL in the result set of a joined query, one of two things is going on:

There was a matching row in the joined table, and the state of the corresponding column for that row was NULL, or
There was no matching row in the joined table.

Right Joins

A RIGHT join, as it implies, is opposite of a LEFT join - all records are selected from the table on the right, and only matching records are selected from the table on the left. When there are only two tables involved in a joined query and a result as described above for the a left join is desired, it can be written as either a left or right join. For example the above left join can be turned into a right join with the same results:

SELECT
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID
FROM EMPLOYEE_PROJECT
RIGHT OUTER JOIN EMPLOYEE
  ON EMPLOYEE_PROJECT.EMP_NO = EMPLOYEE.EMP_NO
ORDER BY
  EMPLOYEE.LAST_NAME, EMPLOYEE_PROJECT.PROJ_ID

In practice, right joins are quite rare. This is simply because, when logically thinking out a query, one will usually place the most important table first - which usually also means that’s the table records are actually being selected from, and the joined table is therefore to look up related information that may or may not exist. Right joins are typically only seen in complex cases where there are also left joins.

Full Joins

A FULL join is very rarely ever desired in application code, though it can often be valuable for certain reporting requirements. It will select all records from both tables whether or not there is a match in the other. For example, if there were records in the EMPLOYEE_PROJECT table having no match in EMPLOYEE, the above query using a FULL join might return a set of rows as follows:

Smith, PR01
Jones, <null>
<null>, PR02
Green, PR03

This result set indicates there is an employee, Jones, currently without a project, and a project PR02 currently not assigned to any employee.

Using aliases for field and table names

As we get used to the idea of having multiple tables in a SELECT statement, specifying the table names can get to be a lot of typing and for large queries it can also hinder readability. SQL allows us to specify an alias for table names:

SELECT
  E.LAST_NAME, EP.PROJ_ID
FROM EMPLOYEE E
LEFT OUTER JOIN EMPLOYEE_PROJECT EP
  ON EP.EMP_NO = E.EMP_NO
ORDER BY
  E.LAST_NAME, EP.PROJ_ID

The alias for a table is defined by specifying it following the table name in the FROM or JOIN clause. SQL allows you to use the alias before it’s defined. Aliases can make a statement much easier to read. Aliases are also used to resolve ambiguous field references, and make it possible to join the same table more than once.

Important: When writing queries involving more than one table, it cannot be emphaiszed too strongly that one should always prefix every field name, wherever it appears, with the table name or table alias. Interbase currently does not enforce this (some databases do), but failure to do so can cause wrong results to be returned from your query. As of version 7.1, once an alias is introduced, Interbase will no longer allow the full table name to be used as a qualifier for field references, however it will still allow the alias to be omitted.

Tip: You may see examples in other documents that use arbitrary aliases, e.g.

SELECT
  A.LAST_NAME, B.PROJ_ID
FROM EMPLOYEE A
LEFT OUTER JOIN EMPLOYEE_PROJECT B
  ON B.EMP_NO = A.EMP_NO
ORDER BY
  A.LAST_NAME, B.PROJ_ID

Such naming of aliases retard readability since they give no hint to which table they represent, forcing one to keep referring back to their introduction in order to remember. Name aliases such that they easily suggest the table they are aliasing. Better to have longer alias names than meaningless ones.

Multiple Joins, tips and optimizations

Any number of tables can be joined in the same query, and each join can be of a different type. The examples so far have returned an employee name and the project IDs they are connected with. The project ID may not be very meaningful, perhaps the project name is desired. But that name is in a third table, the PROJECTS table. Therefore we need to include the third table in our query:

SELECT
  E.LAST_NAME, P.PROJ_NAME
FROM EMPLOYEE E
LEFT OUTER JOIN EMPLOYEE_PROJECT EP
  ON EP.EMP_NO = E.EMP_NO
INNER JOIN PROJECT P
  ON P.PROJ_ID = EP.PROJ_ID
ORDER BY
  E.LAST_NAME, P.PROJ_NAME

Logically, we know that there should always be a match in the PROJECTS table for every row in the EMPLOYEE_PROJECT table, and therefore it makes sense to specify an INNER join for the PROJECT table. But you must always check to be sure you are getting the results you expect! Note that we have specified a LEFT join on EMPLOYEE_PROJECT (as per previous examples) because we want to see all employees whether or not they are currently assigned to any projects. Our current query does not do this though. If you run this example you will see the results are as though you had specified an INNER join on.EMPLOYEE_PROJECT as well as the one we did specify for PROJECT! The reason is simple, an INNER join requires there to be a match in order to return a row, but there can be no match in the PROJECTS table where a NULL has been returned for PROJ_ID by the preceding LEFT join. This demonstrates that joins are processed sequentially, each join executing against the result set returned by the previous join.

Since our goal is to return project names and still be able to return employees having no projects, how do we solve this problem? There are three ways:

1. Because we know there will always be a match between PROJECT and EMPLOYEE_PROJECT, we can change the INNER join to a LEFT join between these tables without causing any undesirable results. By doing this, it allows the results of the first LEFT join to continue through, no rows will be eliminated by the second LEFT join, it will simply match up any PROJECT records it can. The downside of this solution is someone in future may look at this query and recognize the relationship between PROJECT and EMPLOYEE_PROJECT should be represented by an INNER join and attempt to "correct" it, this query would not be formally correct.

2. We can move the INNER join on PROJECT to be inside the LEFT join on EMPLOYEE_PROJECT. This makes it a nested join and would look like this:

SELECT
  E.LAST_NAME, P.PROJ_NAME
FROM EMPLOYEE E
LEFT OUTER JOIN EMPLOYEE_PROJECT EP
  INNER JOIN PROJECT P
    ON P.PROJ_ID = EP.PROJ_ID
  ON EP.EMP_NO = E.EMP_NO
ORDER BY
  E.LAST_NAME, P.PROJ_NAME

Nesting a join like this tells the database server that the nested join should be executed before the nesting join is processed against other tables. So in this example, it will perform the INNER join between EMPLOYEE_PROJECT and PROJECT first. The result set from that join will then be LEFT joined against the EMPLOYEE table. Nesting joins is analogous to specifying parenthesis in arithmetic operations, it specifies precedence of operations. In general, nested joins makes a query harder to understand, and in some cases it can be more process intensive for the database server - resulting in slower performance.

3. We can rewrhavingite the query to select from PROJECT and EMPLOYEE_PROJECT first using an INNER join and then successfully use a RIGHT join against the result.

SELECT
  E.LAST_NAME, P.PROJ_NAME
FROM PROJECT P
INNER JOIN EMPLOYEE_PROJECT EP
  ON P.PROJ_ID = EP.PROJ_ID
RIGHT OUTER JOIN EMPLOYEE E
  ON EP.EMP_NO = E.EMP_NO
ORDER BY
  E.LAST_NAME, P.PROJ_NAME

By placing the INNER join first, any following OUTER joins will work as expected. This keeps the query more straightforward and easier to understand and can also result very often in better performance. This leads to a general rule: INNER joins first if possible . Because of the flexibility of SQL, it is almost always possible to formulate a query to follow this rule.

We’ll do one more example for multiple joins. In addition to our current query, the PROJECT table contains a column called TEAM_LEADER. This column is a foreign key referencing the EMPLOYEE table. Our new goal is to add the team leader’s last name to our existing example. The challenge is that we will need to include the EMPLOYEE table a second time in our query.

SELECT E.LAST_NAME, P.PROJ_NAME, TL.LAST_NAME AS TEAM_LEADER
FROM PROJECT P
INNER JOIN EMPLOYEE_PROJECT EP
  ON P.PROJ_ID = EP.PROJ_ID
INNER JOIN EMPLOYEE TL
  ON TL.EMP_NO = P.TEAM_LEADER
RIGHT OUTER JOIN EMPLOYEE E
  ON EP.EMP_NO = E.EMP_NO
ORDER BY E.LAST_NAME, P.PROJ_NAME

This example demonstrates the importance of specifying aliases for tables. Without table aliasing, this query would not be possible, there’d be no way to distinguish between the two different intended uses of the EMPLOYEE table. As an aside this query also shows an example of aliasing a column in the result set; LAST_NAME is returned as TEAM_LEADER.

Sub-queries

A subquery is a SELECT inside of another SQL statement and is used to select values from another table to be used for qualifying or setting values in the outer SQL statement. Subqueries can also be used in other statements too but are most commonly used in Select statements:

SELECT
  E.EMP_NO, E.LAST_NAME
FROM EMPLOYEE E
WHERE
  E.EMP_NO IN (SELECT EP.EMP_NO FROM EMPLOYEE_PROJECT EP)

This query will return the list of employees who have at least one project. More importantly it will only return each employee one time even if they have matches in more than one project (the JOIN examples above return a row for every match).

This query shows an example of using the IN operator with a subquery. We can’t use the = comparison here, because the subquery may return many rows, and we can’t use an equality comparison to compare against multiple values. If we had tried to use = instead of IN, we’d receive the charming error message, "Multiple rows in singleton select." The = comparison could only be used if we were absolutely certain the subquery would return only one row.

Sub-queries can also be used to provide field values in the result set:

SELECT E.EMP_NO, E.LAST_NAME,
  (SELECT MAX(H.NEW_SALARY)
   FROM SALARY_HISTORY H
   WHERE H.EMP_NO = E.EMP_NO) AS MAX_SALARY
FROM EMPLOYEE E
WHERE
  E.EMP_NO IN (SELECT EP.EMP_NO FROM EMPLOYEE_PROJECT EP)

Note this this example could be rewritten to use a LEFT JOIN and would be much more efficient as a result. While sometimes sub-queries like this are required, most often they can be rewritten as a join and always should be if possible.

Subqueries can also be used in UPDATE statements. Let’s imagine you want to set the project start date on every row in the EMPLOYEE_PROJECT table (if it had such a column) equal to the HIRE_DATE for the corresponding employee in the EMPLOYEE table. The following statement would do the trick:

UPDATE EMPLOYEE_PROJECT
  SET EMPLOYEE_PROJECT.START_DATE =
    (SELECT
      EMPLOYEE.HIRE_DATE
     FROM EMPLOYEE
     WHERE
       EMPLOYEE.EMP_NO = EMPLOYEE_PROJECT.EMP_NO
    )

(This query won’t actually work since the EMPLOYEE_PROJECT doesn’t have a field called START_DATE.)

This kind of statement works fairly well for tables which are not especially large. For very large tables, use a stored procedure to do the update. Attempting this on a large table, especially if more than one subquery must be used to set field values, will perform very poorly because the subqueries must be executed once for each record to be updated.

The following statement will delete any employees with no projects:

DELETE
FROM EMPLOYEE
WHERE
  EMPLOYEE.EMP_NO NOT IN
    (SELECT
      EMPLOYEE_PROJECT.EMP_NO
     FROM EMPLOYEE_PROJECT
    )

Correlated Sub-queries

A correlated sub-query is one where execution of the sub-query depends on a value in each record of the outer query. An example might be:

SELECT
  E.EMP_NO, E.LAST_NAME
FROM EMPLOYEE E
WHERE EXISTS (
    SELECT D.DEPT_NO FROM DEPARTMENT D
    WHERE D.DEPT_NO = E.DEPT_NO

In such queries, the sub-query must be executed once for every row in the outer query. This will most often result in very poor performance on tables of any size. Try to find other ways to accomplish the goal. Possible solutions include rewriting as joins, or stored procedures. The above (very contrived) example could easily be rewritten as an INNER JOIN.

ALL, ANY, SOME, EXISTS, SINGULAR

These operators are used with sub-queries. ALL allows you to perform comparisons other than equality. For example, if you want to find those employees with a salary greater than the salary of all employees in your department, you might try:

SELECT
  LAST_NAME, SALARY
FROM EMPLOYEE
WHERE
  SALARY > ALL
    (SELECT
      SALARY
     FROM EMPLOYEE
     WHERE
       DEPT_NO = 623
    )

ANY and SOME are synonyms, use whichever you like. To find employees in your department with salaries less than the lowest in any other department, you could find them with:

SELECT
  LAST_NAME, SALARY
FROM EMPLOYEE
WHERE
  SALARY < ANY
    (SELECT
       SALARY
     FROM EMPLOYEE
     WHERE
       DEPT_NO <>  623
    )

You should be able to imagine an UPDATE statement that uses the above WHERE clause to give those same people a raise!

EXISTS is very valuable. Use it anywhere you need to know something exists but do not need to know the count. If you want to see a list of Projects where no employees are currently assigned you could do this:

SELECT
  PROJ_ID, PROJ_NAME
FROM PROJECT P
WHERE
  NOT EXISTS
    (SELECT
       EMP_NO
     FROM EMPLOYEE_PROJECT EP
     WHERE
       EP.PROJ_ID = P.PROJ_ID
    )

Tip: There are often cases where you want to know if any records exist that meet a certain criteria, and its very common to think of using the SQL COUNT operator to determine this. In InterBase, COUNT is very inefficient due to its versioning engine which requires each physical record to be visited in order to get an accurate count. Whenever you need to know simply whether any records exist but don’t actually need a count, you can use EXISTS instead for much faster performance:

SELECT
  1
FROM RDB$DATABASE
WHERE
  EXISTS
    (SELECT
       1
     FROM EMPLOYEE E
     WHERE
       E.JOB_COUNTRY = 'England'
    )

If the result set will contain no records if none exist, one record if any exist.

SINGULAR returns true if exactly one row from the sub-select matches your test. The following will return projects where there is exactly one employee assigned.

SELECT
  PROJ_ID, PROJ_NAME
FROM PROJECT P
WHERE
  SINGULAR
    (SELECT
       EMP_NO
     FROM EMPLOYEE_PROJECT EP
     WHERE
       EP.PROJ_ID = P.PROJ_ID
    )

Distinct

Distinct allows you to select unique values, or combinations of values, when there are duplicates. For example, you may want to see all the different cities your customers are from. If you execute the following:

SELECT CITY FROM CUSTOMER
ORDER BY CITY

You will indeed see all the different cities, but each city will be repeated as many times as there are customers from that city. What is really wanted is each unique city:

SELECT DISTINCT CITY FROM CUSTOMER
ORDER BY CITY

Cities are not necessarily unique across different provinces or states, so if your customers are international then the above won’t be good enough. Distinct works on multiple columns:

SELECT DISTINCT CITY, STATE_PROVINCE, COUNTRY FROM CUSTOMER
ORDER BY CITY, STATE_PROVINCE, COUNTRY

Note the Order By is not required to include the same columns (or be present at all) but it is very common to do so.

Tip: A common mistake is to specify DISTINCT as part of a sub-query used in a WHERE clause. Do not do this, it serves no purpose but invariably hurts performance.

Aggregate

The MAX, MIN, COUNT, SUM, and AVG (average) functions do exactly what their names imply. They are called "aggregate" functions because they work on the entire result set at once instead of a single row.

COUNT deserves special mention because it works in two different ways. COUNT(*) will count the number of rows in the table after JOINs are performed, the WHERE clause and GROUP BY are applied, etc. COUNT(FIELD_NAME) counts the number of distinct values there are in a particular column. For example, if you had a table with four rows in it, containing the following data:

ID	SOME_VALUE
1	1
2	1
3	2
4	2

Then the following query:

SELECT
  COUNT(*), COUNT(SOME_VALUE), MAX(ID)

Would return this result set:

COUNT	COUNT_2	MAX
4	2	4

Group By, Having

Since aggregate functions operate on all rows of the table at once and produce a single row as a result set, they can’t be combined with "normal" selection of fields without grouping the results somehow. In other words, you can’t SELECT employee names and the count of employees in the same statement, because the employee names selection produces many rows and the count of employees produces only a single row.

You might, however, want to know how many employees have the same name. The GROUP BY clause allows you to tell the aggregate function that it should run once for each distinct value of a certain field or group of fields in the table.

SELECT
  LAST_NAME, COUNT(LAST_NAME)
FROM EMPLOYEE
GROUP BY
  LAST_NAME
ORDER BY
  LAST_NAME

The GROUP BY clause must contain all columns mentioned in the fields list except for the aggregate functions. So in this example, LAST_NAME must be in the Group By clause.

This query tells us what we want — for each name, how many people share the name. But the value for most names will be "1" and these ones would likely be uninteresting to the user of this query. So we need a way to eliminate the rows with unique names. There is no way to do this using a WHERE clause because a WHERE clause executes before grouping occurs and cannot refer to any aggregated values (such as the count column). This leads us to the HAVING clause.

HAVING

The Having clause qualifies the Group By clause (and can only refer to aggregated columns or columns included in the GROUP BY Clause. Most SQL databases will reject HAVING if it does not accompany a GROUP BY clause. So we can improve our query as follows:

SELECT

  LAST_NAME, COUNT(LAST_NAME)

FROM EMPLOYEE

GROUP BY

  LAST_NAME

HAVING

  LAST_NAME

  COUNT(LAST_NAME) > 1

ORDER BY

Now we’ll only see Last Names where at least two people share that name. This is accomplished by specifying the Count aggregate function on Last_Name.

Like

Like is the standard SQL wildcard processor, it allows you to do pattern matching. Be sure to remember that LIKE is case-sensitive! There are two wild cards:

% - This wildcard can stand in for any number of characters (including zero)

_ - This wildcard stands in for a single character

SELECT
  LAST_NAME,
  COUNT(LAST_NAME) 
FROM EMPLOYEE 
WHERE 
  LAST_NAME LIKE 'Smith%'

This finds all names starting with Smith so will include that and ‘Smithson’, ‘Smithsonian’, etc.

SELECT
  LAST_NAME,
  COUNT(LAST_NAME) 
FROM EMPLOYEE 
WHERE 
  LAST_NAME LIKE 'Sm_th'

This finds names starting with ‘Sm’ and ending with ‘th’ and with any single character in place of the wildcard.

SELECT
  LAST_NAME,
  COUNT(LAST_NAME) 
FROM EMPLOYEE 
WHERE 
  LAST_NAME LIKE '%ith%'

This finds names containing ‘ith’ anywhere within the name.

ESCAPE

What if the value you are looking for contains one of the wildcard characters? As an example, let’s say you have a a database that stores information from a UML application and the application allows class fields to include the underscore character, and you want to find all class fields ending with ‘_Num’. The following WHERE clause will not work correctly:

WHERE 
  LAST_NAME LIKE '%_NUM'

The results will include the rows we’re looking for, but will also include any rows that have other characters in place of the underscore because the underscore is acting as a wild card. We need a way to say we want to match on the literal character instead of that character acting as a wildcard. We can do so like this:

WHERE 
  LAST_NAME LIKE '%\_NUM' ESCAPE '\'

ESCAPE allows us to specify a character that acts as an indicator to tell Interbase that the very next character is to be treated literally. Thus in this example, the underscore will be treated as a character to match on instead of a wildcard. The result is that only rows ending with ‘_NUM’ will be returned. Although any character can be used as the escape character, using the ‘\’ is considered standard and should only be substituted if that happens to also be a character you need to search for.

Starting With

Use this instead of LIKE wherever possible. This is most important for parameterized queries as a parameterized LIKE cannot use an index while STARTING WITH can. STARTING WITH , like LIKE, is case-sensitive. It does not accept wildcards. Note that this is an Interbase-specific feature, not part of the SQL standard.

Containing

CONTAINING is similar to using LIKE with wildcards at both ends, e.g. LIKE ‘%BASE%’. However CONTAINING is case-insensitive and can also be used to search within text blob columns. As with LIKE in this case, CONTAINING cannot make use of indexes. Note that this is an Interbase-specific feature, not part of the SQL standard.

Rows

Rows is a new feature starting with version 6.5. Rows is very flexible and provides a great deal of power. Note that this is an Interbase-specific feature, not part of the SQL standard.

At its simplest, ROWS lets you select the top n rows from a query.

SELECT
  LAST_NAME,
  FIRST_NAME, SALARY
FROM EMPLOYEE
ORDER BY
  SALARY DESC, LAST_NAME,FIRST_NAME
ROWS 1 TO 10

This query returns only the first 10 records of the result set (You can also specify just ROWS 10). Strictly speaking, ROWS can be used without the Order By, but this would be of no practical value and should not be done in practice. In this example, the 10 employees with the highest salaries will be returned. It’s possible you may want all the employees with the 10 highest salaries (more than one employee may have the same salary), in this case you can use the WITH TIES option:

SELECT
  LAST_NAME,
  FIRST_NAME, SALARY
FROM EMPLOYEE
ORDER BY
  SALARY DESC, LAST_NAME,FIRST_NAME
ROWS 1 TO 10 WITH TIES

So if there were 17 employees sharing the top 10 salaries, all 17 would be returned.

Rows can also be specified as a percent:

SELECT
  LAST_NAME,
  FIRST_NAME, SALARY
FROM EMPLOYEE
ORDER BY
  SALARY DESC, LAST_NAME,FIRST_NAME
ROWS 10 PERCENT

This returns the first 10% of the result set.

Finally, Rows can be used for sampling. The following example returns every 20th record from the result set up to a maximum of 100 records:

SELECT
  LAST_NAME,
  FIRST_NAME, SALARY
FROM EMPLOYEE
ORDER BY
  SALARY DESC, LAST_NAME,FIRST_NAME
ROWS 100 BY 20

Probably one of the most sought after uses of this feature is to allow for very efficient paging of result sets as commonly seen on many web sites. Some number of records, typically 10, are displayed with Next and Previous buttons. Rows allows you to return any slice of a result set

SELECT
  LAST_NAME,
  FIRST_NAME, SALARY
FROM EMPLOYEE
ORDER BY
  SALARY DESC, LAST_NAME,FIRST_NAME
ROWS 30 to 39

This returns 10 rows from the result set starting at row 30.

The Rows feature can also be used in Update and Delete statements. Normally, the Order By clause is only valid in a Select statement. However, it becomes valid for other statements as long as it is used along with the Rows feature (otherwise using rows in these statements would be of little value). For example, the following gives a raise to the employees hired before 2002 having the 10 highest salaries.:

UPDATE EMPLOYEE SET
SALARY = SALARY * 1.10
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2002
ORDER BY SALARY DESC
ROWS 10 WITH TIES

The following deletes the 10 employees with the newest hire date:

DELETE FROM EMPLOYEE
ORDER BY HIRE_DATE DESC
ROWS 10

Date and Time

InterBase provides a trio of variables that return the date, time, and timestamp (date + time) values. These are:

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

These can be used as field values in any select statement. They can be selected and they can be used to assign values in INSERT and UPDATE statements. Applications can get the current time from the database server with the following:

SELECT CURRENT_TIME FROM RDB$DATABASE

Extract

Extract is a built-in function that allows parts to be extracted from date, time, and timestamp values. Examples of using Extract:

SELECT EMP_NO, HIRE_DATE FROM EMPLOYEE

WHERE EXTRACT(MONTH FROM HIRE_DATE) = 6

INSERT INTO YEARSUM

  (PRODUCT_NO, SALES_YEAR)

VALUES

  ('123', EXTRACT(YEAR FROM SUMMARYDATE))

Indexes

Most, having worked with other database products, will be familiar with indexing. Indexes allow much faster access to commonly required data lookups. For example, it would be common to define an index or indexes on the name fields in the Customers table. Proper definition of indexes is crucial to good performance of queries.

Another difference between a relational database like Interbase and flat-file databases is that the application does not have to be concerned with indexes or even know about them, whereas with most flat-file systems the application must set the appropriate index as active in order to do things such as ordering records or looking up values. This also indicates another difference: flat-file systems can only have a single active index at a time while Interbase and other relational databases can employ any number of indexes as needed to perform a query.

Generators

Generators are a feature of Interbase that provide the ability to acquire unique values for various uses. They are introduced here because their most common and valuable use is for generating values to be used as unique or primary keys (see the discussion of Surrogate keys further below). Generators return 64 bit integers. They are safe to use with multi-user applications and they live outside any specific transaction. This means they can be relied on to always return unique values (this can be defeated but it takes deliberate action to do so).

Generators are not usable in cases where a sequential series of numbers without gaps is required - once a generator has returned a value, that value cannot be put back in any safe manner and because generators live outside transactions, increments to generators cannot be rolled back.

Similar features in other products include Identity Fields in MS SQL, and Sequences in Oracle.

The following creates a generator called NEWROWID:

CREATE GENERATOR NEWROWID

A generator’s value can be set with the following:

SET GENERATOR NEWROWID TO 100

Caveat: Use with care. Once a generator is in use for the purpose of acquiring unique values, its value should never be altered in this way.

To select a value from a generator directly, the following can be used:

SELECT GEN_ID(NEWROWID, 1) FROM RDB$DATABASE

The above example tells Interbase to increment the generator by one and return the new value. You can specify a higher number in order to reserve a block of numbers with a single call, and you can get the current value without incrementing it by passing zero.

Generators can also be called as part of other SQL statements such as insert statements:

INSERT INTO CUSTOMER

  (CUSTOMERID, FIRSTNAME, LASTNAME)

VALUES

  (GEN_ID(NEWROWID, 1), 'JOHN', 'DOE')

Unique Indexes

Unique indexes guarantee that only a single row with a specific key value can exist. An attempt to insert a row with a duplicate value for the key will result in a Key-Violation exception. It is very good practice to make sure all tables have at least one unique key as there is really no justification for the existence of identical records. An example of a unique index definition is:

CREATE UNIQUE INDEX UIEMPLOYEE ON EMPLOYEE(LAST_NAME, FIRST_NAME);

This index ensures only one employee can exist for the combination of these two columns. Note that all columns that are part of a unique index must include the NOT NULL directive. You can create as many unique keys for a table as desired.

Primary Keys

A Primary Key is, in fact, a unique constraint, not an index. However in creating a Primary Key, InterBase creates a matching unique index in order to implement that constraint. A Primary Key can be the target of a Foreign Key constraint (see below).

If you are familiar with Paradox, do not confuse the use of the this term. In Paradox a Primary Key is more like what is called a clustered index in most RDBMS products, it means that the physical rows of data are actually stored in the order specified by the Primary Key. Interbase does not support clustered indexes. Because a unique index is created for the Primary Key, all columns specified in a Primary Key must include the NOT NULL directive. Only a single Primary Key can be created for a table. Primary Keys can be created as part of the table definition:

CREATE TABLE CUSTOMERS ( 
  CUSTNO CUSTNO NOT NULL, 
  FIRSTNAME NAME, 
  LASTNAME NAME, 
  PHONE PHONE
  PRIMARY KEY (CUSTNO) 
);

If the Primary Key is to consist of two or more columns, the above form must be used. Because our primary key in this example is on a single column, it can also be specified in the column definition like this:

CREATE TABLE CUSTOMERS ( 
  CUSTNO CUSTNO NOT NULL PRIMARY KEY, 
  FIRSTNAME NAME, 
  LASTNAME NAME, 
  PHONE PHONE
);

Surrogate Keys

As mentioned above, there is never any justification for duplicate rows in a table, and thus there should always be some combination of actual data that uniquely identifies each row. This unique combination of columns can then be specified as a unique index or as the Primary Key constraint and be used for relating other tables (Foreign Key constraints), etc. However, it has become more and more accepted as a best practice to always include a separate surrogate key in each table. A surrogate key is a key that is not part of the business data and is therefore arbitrary, but unique. In most cases the surrogate key can be a simple integer (for InterBase, a data type of INTEGER which is 32 bits or NUMERIC(18, 0) which provides 64 bit integer) and this works well with InterBase’s Generators. For certain applications, using 128 bit GUIDs may make sense but unless the application really calls for that measure, integer keys are recommended.

The advantages of a Surrogate key are:

Small and thus very efficient and performant.
Only a single column is needed to specify SQL joins
The key never has to change. Using business data as a Primary Key leads to the possibility of the Primary Key changing in future, and thus all references to that key in related tables also need to be updated. Interbase does provide mechanisms to handle this (see Foreign Keys, below), but many databases do not and even with these mechanisms available, it can be argued that it is an unnecessary and wasteful practice.

Our above example with a surrogate Primary Key:

CREATE TABLE CUSTOMERS ( 
  ID INTEGER NOT NULL PRIMARY KEY, 
  CUSTNO CUSTNO NOT NULL UNIQUE, 
  FIRSTNAME NAME, 
  LASTNAME NAME, 
  PHONE PHONE
);

The ID column can now be (and should be) used as a Foreign Key in all other related tables, and as the related key in all SQL Joins.

Since the CUSTNO column also needs to be unique among all customers, this example specifies a UNIQUE constraint on that column. This then makes CUSTNO the Alternate key for this table. An Alternate key represents the column or columns of business data that uniquely represent each row. This provides the necessary business logic for ensuring uniqueness of data.

Non-unique Indexes

Non-unique indexes are commonly used to enhance query performance for selecting, ordering, and grouping records. Tables can have any number if non- unqiue indexes. The syntax is:

CREATE INDEX IEMPLOYEE ON EMPLOYEE(LAST_NAME, FIRST_NAME);

This index makes it easy to query for rows having a specific value or range of values for Last Name or a combination of Last Name and First Name. It is important to understand that this index cannot be used if you are only searching on First Name (this is true for all indexes) – one or more columns starting with the first must be used in a query in order for that index to be employed.

Tip: Avoid creating indexes on columns that have very few distinct values. For example there is never a benefit in having an index defined on a boolean column because, at best, such an index can only eliminate half the records, with the rest still subject to a table scan (in practice it will usually be very lopsided in favour of one of the values).

Foreign Keys (Referential Integrity)

Like Primary Keys, Foreign Keys are actually constraints implemented via indexes. Referential Integrity is a feature that ensures the integrity of related data in the database. Common examples are typically, though not exclusively, master-detail relationships such as a Customer (master) and a Customer’s Orders (detail). If the database were to allow a user to delete a Customer without deleting Orders related to the Customer, integrity would be lost. Likewise an Order should not be deleted without deleting the Order Items belonging to that Order. It would also violate integrity if an Order were inserted with a non-existent CustNo value.

Interbase provides two basic ways to implement referential integrity. One is through the use of Triggers (covered later) and the other is the use of Foreign Keys. Foreign keys can be created as part of a table definition, so to create a foreign key into the Customers table on the Orders table it would look like this:

CREATE TABLE ORDERS ( 
  ORDERNO ORDERNO NOT NULL, 
  CUSTNO CUSTNO, 
  ORDERDATE DATE, 
  ORDERSTATUS CHAR(5) 
  PRIMARY KEY (ORDERNO) 
  FOREIGN KEY (CUSTNO) REFERENCES CUSTOMERS(CUSTNO) 
);

In keeping with the above discussion on Surrogate keys, the Orders table should refer to that Surrogate key instead of CUSTNO (and of course, should have a Surrogate key of its own):

CREATE TABLE ORDERS ( 
  ID INTEGER NOT NULL PRIMARY KEY, 
  ORDERNO ORDERNO NOT NULL UNIQUE, 
  CUSTOMERID INTEGER NOT NULL, 
  ORDERDATE DATE, 
  ORDERSTATUS CHAR(5) 
  FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMERS 
);

Note that in this second example the foreign key does not specify a column in the CUSTOMERS table. That is because it is unnecessary when referencing the Primary Key. When no column is specfied, Interbase will automatically look for and use the referenced table’s Primary Key. If there is no Primary Key or the Foreign Key needs to reference columns other than the Primary Key, then the columns must be included in the Foreign Key specification.

By itself, this foreign key will raise an exception if a CustNo is specified that does not exist in the Customers table, or if a Customer is deleted while related Orders exist. It can be further defined to indicate a different action should be taken when a Customer is deleted or updated. For example, if you allow users to change the CustNo column of an existing Customer, then you will want to add the On Update directive:

  FOREIGN KEY (CUSTNO) REFERENCES CUSTOMERS(CUSTNO) 
  ON UPDATE CASCADE

This causes Interbase to automatically update the CustNo column in all Orders related to the changed Customer (this feature is never needed if using Surrogate Keys consistently).

Likewise if, instead of raising an exception when a Customer is deleted, you want all related Order records to be deleted then add the On Delete rective:

  FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMERS 
  ON DELETE CASCADE

Alternatively, if you want to keep Order records but indicate the Customer is no longer on file, this can be done with:

  FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMERS 
  ON DELETE SET NULL

Tip: The maximum length of an index key is rather short, less than 255 bytes. Note that is not the same as characters as InterBase supports character sets that require 2 or 3 bytes per character. Therefore, you must keep your index definitions short. Avoid indexing long VARCHAR fields.

Tip: Currently, Interbase has problems choosing indexes correctly when there are more than one starting with the same column. Therefore, always avoid this. Do not forget that Primary Keys and Foreign Keys include indexes. It is important to remember in this context that Interbase can use multiple indexes to process a query so it is not necessary to repeat the same combination of fields in different indexes. For example, since there is already a Primary Key on EMP_NO in the EMPLOYEE table, you should not create any other index starting with that EMP_NO.

Views

Views are essentially canned SQL statements. Like an SQL Select statement, a View can be complex with many joins and conditions. If a a complex query is needed often then it can pay to turn it into a view. This provides a simpler view of the database from the application level.

For example, if a feature of your application is to show a list of customers who have unpaid orders then the query may look like this:

SELECT
  C.CUST_NO, C.CUSTOMER, C.PHONE_NO,
  S.PO_NUMBER, S.ORDER_DATE 
FROM CUSTOMER C
JOIN SALES S 
  ON S.CUST_NO = C.CUST_NO
WHERE 
  S.ORDER_STATUS = 'UNPAID'

Since this query may be executed frequently, it works very well as a View definition. You might name this view ‘UNPAIDORDERS’. The view can be created like this:

CREATE VIEW UNPAIDORDERS (
  CUST_NO,
  CUSTOMER,
  PHONE_NO,
  PO_NUMBER,
  ORDER_DATE
) AS
SELECT 
  C.CUST_NO, C. CUSTOMER, C.PHONE_NO,
  S.PO_NUMBER, S.ORDER_DATE 
FROM CUSTOMER C
JOIN SALES S 
  ON S.CUST_NO = C.CUST_NO
WHERE 
  S.ORDER_STATUS = 'UNPAID';

An application can now ask for this with a much simpler select statement:

SELECT * FROM UNPAIDORDERS

The view is seen as though it were another table. This means you can combine it with joins and conditions like any other table.

Exceptions

InterBase allows you to define exceptions and then raise these via code in triggers and stored procedures. When raised, these exceptions will be passed on to your application.

Defining exceptions is very simple, a definition might be:

CREATE EXCEPTION NOINVENTORY 
  'Insufficient inventory to complete your request'

Since it is common for applications to require multi-language operation, a good alternative would be use a coded value for the second parameter instead of a user-oriented string. Simply repeating the exception name works well:

CREATE EXCEPTION NOINVENTORY 
  'NOINVENTORY'

Alternately, use numeric codes either by themselves or with text:

CREATE EXCEPTION NOINVENTORY 
  '037:NOINVENTORY'

This makes it easy to test this value in an application and then substitute a descriptive message in the required language.

Triggers

Triggers allow you to perform data manipulations or checks automatically in response to other manipulations (external applications, stored procedures, or other triggers). For example, when the status of an Order is set to ‘Shipped’, a trigger can be used to update the product inventory levels affected by that order – relieving the application of having to manage this maintenance. A trigger could also be used to stop an action from taking place. In the same example, an attempt to set the Order status to ‘Shipped” can be rejected with an exception if there is insufficient inventory available (i.e. using a trigger to enforce integrity constraints as was mentioned in the section on Indexes). A query to perform such a check, if executed from an application, might be:

SELECT COUNT(*) 
FROM ORDERLINES OL
JOIN PRODUCTS P 
  ON P.PRODUCTNO = OL.PRODUCTNO
WHERE 
  OL.ORDERNO = 123 AND
  P.INSTOCK < OL.QUANTITY

This example checks all the inventory levels for Order Number 123. If the query returns greater than zero than one or more orderlines cannot be shipped. If it returns 0 then another query is required to update the inventory. This can be turned into a Trigger as follows:

CREATE TRIGGER BU_SHIPCHECK FOR ORDERS 
BEFORE UPDATE POSITION 0 AS
BEGIN
  IF (NEW.STATUS = 'SHIPPED' AND
  NEW.STATUS <> OLD.STATUS AND
  EXISTS (
    SELECT 1 
      FROM ORDERLINES OL
    JOIN PRODUCTS P 
      ON P.PRODUCTNO = OL.PRODUCTNO
    WHERE 
      OL.ORDERNO = OLD.ORDERNO AND
      P.INSTOCK < OL.QUANTITY
  )) THEN
  EXCEPTION NOINVENTORY
END

This trigger automatically works on whichever Order is updated. This example demonstrates the use of conditional logic so that the constraint only fires if the Status column is changed to ‘SHIPPED’ by testing the old and new field values. It also demonstrates use of the EXISTS operator; one should always use this feature where possible as it avoids needing to ask for a count – it can return True as soon as it encounters the first matching row. Code to update inventory could also be placed in this same trigger, however, the update on the order record hasn’t actualy completed yet. Therefore it is more logically correct to place such code in a separate trigger that fires after the update completes:

CREATE TRIGGER AU_SHIPORDER FOR ORDERS 
AFTER UPDATE POSITION 0 AS
DECLARE VARIABLE LINEID INTEGER;
DECLARE VARIABLE QUANTITY INTEGER;

BEGIN
  IF (NEW.STATUS = 'SHIPPED' AND
  NEW.STATUS <> OLD.STATUS) THEN
  BEGIN
    FOR SELECT OL.LINEID, OL.QUANTITY
      FROM ORDERLINES OL
      WHERE 
        OL.ORDERNO = OLD.ORDERNO
    INTO :LINEID, :QUANTITYcondi
    DO BEGIN
      UPDATE INVENTORY SET
      INSTOCK = INSTOCK - :QUANTITY
      WHERE LINEID = :LINEID;
    END
  END
END

The above example shows the use of local variables, and selecting values from a query into those variables as well as an example of InterBase’s very powerful FOR SELECT syntax. This latter will be discussed more in the section on Stored Procedures.

Tip: Conditions in IF statements must always be contained in a single set of parenthesis. Forgetting this will result in somewhat less than helpful error messages from InterBase such as "Token unknown". Additional parenthesis are allowed as well.

Basic Trigger Layout

All triggers follow the following layout:

CREATE TRIGGER <NAME> FOR <TABLE> 
BEFORE|AFTER <OPERATION> POSITION n AS
BEGIN
END;

A trigger must be defined as either before or after the:specified operation. If the position is not specified then it is zero. There can be multiple triggers for the same operation on the same table, thus the position parameter allows you to control the order those triggers fire, if it matters to your application. Triggers can share the same position in which case order of execution is arbitrary (there may be an order always followed by Interbase, possibly alphabetic by name, but nothing that is published and thus nothing that should be relied on).

Before or After?

In InterBase, a trigger can be before or after the event. In many cases it really doesn’t matter which you choose, but a general rule is any trigger that is performing checks and may wish to abort the action should be placed in Before triggers, while any consequent updates to other fields or tables should be placed in After triggers. Our two example triggers above observe this rule.

Old and New Values

In order to perform needed logic, triggers provide you with both the original values and the new values of all fields in the table subject to that trigger. Both are available in Update triggers, only Old values are available in Delete triggers and only New values in Insert triggers. The examples above shows a test between old and new values to determine if this update has set the status to ‘SHIPPED’.

Tip: Triggers need to be as short as possible, do not try to perform intensive processing in a trigger or you will cause severeperformance problems.

Stored Procedures

Another feature of relational databases is the ability to write procedures that execute directly within the database and can be called as required from an application. Interbase stored procedures can be used in two different ys:

1. They can be executed liked functions, optionally returning result values. All databases that support stored procedures support this use.

2. They can be treated as though they were tables, i.e. they can be selected from with a normal Select query, but with the added feature that parameters can be passed to them.

Stored procedures have many advantages.

Because, like triggers, they are part of the database definition they are precompiled into a binary form directly executable by Interbase (prepared) thus saving this overhead that is required when executing queries from the client.
They provide a means of data integrity; procedures can define many database- level rules. By defining them on the database, all applications have automatic access to them without the worry of anyone incorrectly coding the rules in an application.
If needed, applications can be limited to only using stored procedures (through Grant privileges) thus making it impossible to bypass the processes and rules defined in the stored procedures.
They can dramatically enhance performance of complex operations since it can all be done without an application executing many statements individually and moving data back and forth over the network.
They can simplify many operations that are very difficult to do, or which are performance problems when done in a single SQL statement (e.g. Left Joins can be “unrolled”).

Basic Procedure Layout

All procedures follow the same basic layout:

CREATE PROCEDURE <NAME> 
AS BEGIN 
END;

Passing Parameters

Parameters follow the procedure name:

CREATE PROCEDURE <NAME> 
( 
  PARAM1 VARCHAR(4),   PARAM2 VARCHAR(20) ) AS BEGIN 
  SELECT LAST_NAME FROM EMPLOYEE 
  WHERE EMP_NO = :PARAM1
  INTO :PARAM2; 

  IF (PARAM1 IS NOT NULL) THEN 
    PARAM2 = PARAM1 || ' ' || PARAM2; 
END;

The above example actually accomplishes nothing as written. To be useful it needs to do something further with PARAM2. It demonstrates use of input variables, conditional logic, and string concatenation.

Returning Values

We’ll modify the last example to do something more, although still trivial.

CREATE PROCEDURE FORMALNAME 
( 
  EMPNO INTEGER,
  PREFIX VARCHAR(4)) RETURNS (  FORMALNAME VARCHAR(30) )AS BEGIN 
  SELECT LAST_NAME FROM EMPLOYEE 
  WHERE EMP_NO = :EMPNO
  INTO :FORMALNAME; 

  IF (PREFIX IS NOT NULL) THEN 
    FORMALNAME = PREFIX || ' ' || FORMALNAME; 
END;

We’ve now defined a return parameter (you can define as many return parameters as you need). The code is essentially the same (with better naming) but the result is now being stored in the return parameter. Upon executing a procedure, a program can retrieve the value of any return parameters.

An important point of use for all variables (local variables and parameters) is to remember where you are using them. When referring to them within any SQL statement, they must be indicated by using a colon as a prefix. This allows Interbase to recognize you are referring to a variable or parameter rather than a field name. Outside of SQL statements, the use of the colon is permitted but not required.

Local Variables

As with triggers shown earlier, you can define local variables for use in a stored procedure. The syntax is the same, they must be placed between the AS and BEGIN statements:

AS BEGIN

DECLARE VARIABLE LINEID INTEGER;
DECLARE VARIABLE QUANTITY INTEGER;

BEGIN

Variables can be of any valid Interbase type including BLOB types. Note that Domain types cannot be used.

Iterating Records and Returning a Result Set

Probably the most important feature in Interbase Stored Procedures is the FOR SELECT syntax. This syntax iterates through each record specified by the Select and allows you to perform any additional logic you need. Most importantly it allows an elegant way to return rows of data to the application - making it a selectable stored procedure. The syntax is:

FOR SELECT 
  CUSTNO, FIRSTNAME, LASTNAME 
  FROM CUSTOMERS C
INTO
  :CUSTNO, :FIRSTNAME, :LASTNAME
DO BEGIN
  IF NOT EXISTS(
    SELECT 1
    FROM SECRETCUSTOMERS SC
    WHERE
      SC.CUSTNO = C.CUSTNO  ) THEN
    SUSPEND;END

Note that this contrived example could’ve been done better using a join in the main query, then the second query would not be needed. But it serves to demonstrate the ability to execute additional queries and conditionally decide whether to return a row to the user of the procedure. In order to return one row, the SUSPEND statement needs to be executed. As demonstrated here, by simply by not calling SUSPEND, a row can be skipped. If it is decided by some condition that no more records should be returned, EXIT can be called to end the procedure.

A selectable procedure can be called using normal Select syntax. If the above example procedure was named SPCUSTOMER then it would be called as:

SELECT * FROM SPCUSTOMER

If the procedure required parameters, then it would be called as:

SELECT * FROM SPCUSTOMER(PARAM1, PARAM2)

If the procedure is not selectable, then it is call using the EXECUTE mmand:

EXECUTE PROCEDURE SPCUSTOMER(PARAM1, PARAM2)

Calling Another Procedure

A procedure can call other procedures using the same syntax as above if no return values are expected. It can call a selectable procedure by using the FOR SELECT statement (or just SELECT if you can guarantee only one row will be returned). Finally, it can call another procedure that returns parameter values as follows:

EXECUTE PROCEDURE FORMALNAME(123, 'Mr.')

RETURNING VALUES INTO :LOCAL_OR_RETURN_PARAMETER;

Further Resources

This is a short list of resources on the web:

General Articles:
http://blogs.teamb.com/craigstuntz/category/21.aspx
http://www.dbginc.com/pubs.shtml
http://mers.com
http://www.borland.com/interbase
http://community.borland.com/interbase

Cache Settings:
http://www.volny.cz/iprenosil/interbase/ip_ib_cache.htm

Blob vs Char vs VarChar:
http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm

Optimize Interbase
http://community.borland.com/article/interbase/makeibscream.pdf
http://community.borland.com/article/0,1410,27569,00.html

InterBase Installation Information
http://ibinstall.defined.net

Issues with Windows XP
http://community.borland.com/article/0,1410,28142,00.html

Share This | Email this page to a friend

Posted by Wayne Niddery on June 19th, 2004 under General |

Server Response from: blog1.codegear.com

Categories:

Archives:

Blogs: Borland

Blogs: TeamB

Other Links

The Ins and Outs of InterBase SQL

Preconference Tutorial

by Wayne Niddery, Logic Fundamentals, Inc.

Abstract

Topics

Assumptions

Assumptions

Equijoins

Inner Joins

Outer Joins

Left Joins

Full Joins

Using aliases for field and table names

Multiple Joins, tips and optimizations

Correlated Sub-queries

ALL, ANY, SOME, EXISTS, SINGULAR

HAVING

Like

ESCAPE

Starting With

Containing

Date and Time

Extract

Unique Indexes

Primary Keys

Surrogate Keys

Non-unique Indexes

Foreign Keys (Referential Integrity)

Exceptions

Basic Trigger Layout

Before or After?

Old and New Values

Stored procedures have many advantages.

Basic Procedure Layout

Passing Parameters

Returning Values

Local Variables

Iterating Records and Returning a Result Set

Calling Another Procedure

Further Resources

Leave a Comment

Meta