InterBase Quick Start: Part V - Creating a Simple Executable Procedure

Go Up to InterBase Quick Start: Part V - Advanced Topics

Creating a Simple Executable Procedure

The executable procedure that you create in this exercise, add_emp_proj, makes use of an exception that you define with CREATE EXCEPTION. Execute the following statement to create the UNKNOWN_EMP_ID exception:
```
CREATE
EXCEPTION
  unknown_emp_id 'Invalid employee number or project ID.'
```
You can now raise this exception in a trigger or stored procedure with the EXCEPTION clause. The calling application then receives the associated error message.

Execute the following statement to create the add_emp_proj stored procedure:

CREATE PROCEDURE ADD_EMP_PROJ (v_empno  SMALLINT,
                               v_projid CHAR(5))
AS
BEGIN
  INSERT INTO employee_project
             (emp_no,
              proj_id)
  VALUES
             (:v_empno,
              :v_projid);
  WHEN SQLCODE -530
  DO
    EXCEPTION
      unknown_emp_id;
END

This procedure takes an employee number and project ID as input parameters and adds the employee to the specified project using an INSERT statement. The error-handling WHEN statement checks for SQLCODE -530 (violation of foreign key constraint) and raises the previously-defined exception if necessary.

To test the procedure, first enter the following statement:
```
SELECT *
FROM   employee_project
WHERE  emp_no = 20
```
The employee 20 is assigned only to project GUIDE.
The image below shows the expected result:
You can run this procedure by executing the following statement:
```
EXECUTE PROCEDURE add_emp_proj(20, 'DGPII')
```
To confirm that it worked, execute the following SELECT statement:
```
SELECT *
FROM   employee_project
WHERE  emp_no = 20
```
You can see that employee 20 is now assigned to both DGPII and GUIDE projects.
The image below shows the expected result:
Now try adding a non-existent employee to a project:
```
EXECUTE PROCEDURE add_emp_proj(999, 'DGPII')
```
The statement fails and the exception message displays on the screen.

Recursive Procedures

Stored procedures support recursion. Recursion is a powerful programming technique that is useful in performing repetitive tasks across hierarchical structures such as corporate organizations or mechanical parts.

Creating a Recursive Procedure

In this exercise, you create a stored procedure called dept_budget that takes a department number as an input parameter and returns the budget of a department and all departments that are under it in the corporate hierarchy. This procedure also uses local variables declared with DECLARE VARIABLE statements.

Execute the following SQL statement:

CREATE PROCEDURE dept_budget (v_dno CHAR(3))
returns                      (total_budget NUMERIC(15, 2))
AS
  DECLARE
    variable sumb DECIMAL(12, 2);
    DECLARE
      variable rdno CHAR(3);
      DECLARE
        variable cnt INTEGER;
BEGIN
  total_budget = 0;
  SELECT budget
  FROM   department
  WHERE  dept_no = :v_dno
  INTO   
    :total_budget;
  SELECT count(budget)
  FROM   department
  WHERE  head_dept = :v_dno
  INTO   
    :cnt;
  IF (cnt = 0) THEN
    suspend;
  FOR
    SELECT dept_no
    FROM   department
    WHERE  head_dept = :v_dno
    INTO
      :rdno
  DO
    BEGIN
      EXECUTE PROCEDURE dept_budget :rdno returning_values :sumb;  
      total_budget = total_budget + sumb;
    END
END

First, the procedure retrieves the budget of the department from the Department table, specified as the input parameter, and stores it in the total_budget variable. Then it retrieves the number of departments reporting to that department using the COUNT aggregate function. If there are no reporting departments, it returns the value of total_budget with SUSPEND. Using a FOR SELECT … DO loop, the procedure then retrieves the department number of each reporting department into the local variable rdno, and then recursively calls itself with the following statement:

EXECUTE PROCEDURE dept_budget :rdno returning_values :sumb

This statement executes dept_budget with input parameter rdno, and inserts the output value in sumb. When you use EXECUTE PROCEDURE within a procedure, you do not put the input parameters in parenthesis, and the variable and you specify the variable for the output value after the RETURNING_VALUES keyword.

To find the total budget for the department 620, including all its subdepartments, execute the following statement:
```
EXECUTE PROCEDURE dept_budget(620)
```
The result is:

About Data Type Conversion

The definition of the dept_budget procedure takes CHAR(3) as its input parameter. Because of InterBase automatic type conversion, you can actually provide an integer and that integer is converted. However, there are certain limitations. For example, you cannot provide an integer 000 because the conversion to CHAR(3) results in a string '0', which is not a valid department number. To test this, execute the procedure and specify the parameter as an integer 000 and then again as a string '000'.

More Procedures

There are a number of other procedures, some quite complex, defined in PROCS.SQL script. Now that you have a basic understanding of procedures, we recommend that you take some time to examine them and try using them. The PROCS.SQL script has many inline comments to make it easier for you to understand the code.

Conclusion

This completes the InterBase Quick Start tutorial. See InterBase User Guides for more guides and tutorials.