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

From InterBase
Jump to: navigation, search

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

Image 025.jpgCreating a Simple Executable Procedure

  1. 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.
  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 raises the previously-defined exception if necessary.
  3. 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:

    TutorialProcedure3.png
  4. You can run this procedure by executing the following statement:
    EXECUTE PROCEDURE add_emp_proj(20, 'DGPII')
    
  5. 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:

    TutorialProcedure4.png
  6. 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.

    TutorialProcedure5.png

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.

Image 025.jpgCreating 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.

  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
    

    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.

  2. 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:

    TutorialProcedure6.png

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.