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

1. The executable procedure that you create in the next step of this exercise, add_emp_proj, makes use of an exception, a named error message, that you define with CREATE EXCEPTION. Execute the following SQL statement to create the UNKNOWN_EMP_ID exception:

   CREATE EXCEPTION unknown_emp_id  
    'Invalid employee number or project ID.'
   

   Once defined, this exception can be raised in a trigger or stored procedure with the EXCEPTION clause. The associated error message is then returned to the calling application.
2. 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 then raises the previously-defined exception when this occurs.

3. Practice using this procedure by executing the following SQL statement:

   EXECUTE PROCEDURE add_emp_proj(20, 'DGPII')
   

   To confirm that this worked, execute the following SELECT statement:

   SELECT * FROM Employee_project where emp_no = 20
   

   You should see that employee 20 is now assigned to both the DGPII project and the GUIDE project.

4. Now try adding a non-existent employee to a project, for example:

   EXECUTE PROCEDURE add_emp_proj(999, 'DGPII')
   

   The statement fails and the exception message displays on the screen.

   

Recursive Procedures

Stored procedures support recursion, that is, they can call themselves. This 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 its input parameter, and returns the budget of the department and all departments that are under it in the corporate hierarchy. It uses local variables declared with DECLARE VARIABLE statements. These variables are used only within the context of the procedure.

First, the procedure retrieves the budget of the department given as the input parameter from the Department table 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

EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb

This statement executes dept_budget with input parameter rdno, and puts the output value in sumb. Notice that when using EXECUTE PROCEDURE within a procedure, the input parameters are not put in parenthesis, and the variable into which to put the resultant output value is specified after the RETURNING_VALUES keyword. The value of sumb is then added to total_budget, to keep a running total of the budget. The result is that the procedure returns the total of the budgets of all the reporting departments given as the input parameter plus the budget of the department itself.

1. 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
   

2. To find the total budget for department 620, including all its subdepartments, execute the following SQL statement:

   EXECUTE PROCEDURE dept_budget(620)
   

   The result is:

   

A Little about Datatype Conversion

Notice that the dept_budget procedure is defined to take a CHAR(3) as its input parameter, but that you can get away with giving it an integer (without quotes). This is because of InterBase automatic type conversion, which converts data types, where possible, to the required data type. It automatically converts the integer 620 to the character string “620”. The automatic type conversion wil not work for department number 000, however, because it would convert to the string “0”, which is not a department number.

More Procedures

There are a number of other procedures, some quite complex, defined in Procs.sql for the TUTORIAL database. Now that you have a basic understanding of procedures, it will be worth your while to read them over so that you understand them. Then try using them. Notice that comments are often included within the text of a statement to make it easier for people to understand what the code is doing.

End of Quick Start

This completes the InterBase Quick Start. For additional information on each of these topics, please refer to InterBase User Guides.