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

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

Creating a Simple SELECT Procedure

1. Execute the following code to create the get_emp_proj procedure:

   CREATE PROCEDURE get_emp_proj (v_empno SMALLINT) 
   returns                       (project_id CHAR(5))
   AS
   BEGIN
     FOR
       SELECT proj_id
       FROM   employee_project
       WHERE  emp_no = :v_empno
       INTO
         :project_id 
     DO
       suspend;
   END
   

   • The convention for variable names is to prepend them with _v for readability, but it is not required.
   • The procedure takes an input parameter (v_empno) and returns all the projects to which the employee is assigned (project_id, specified after RETURNS). The variables are defined in the header and then referenced in the body as :<varname>.
   • The procedure uses a FOR SELECT … DO statement to retrieve multiple rows from the Employee_project table. This statement retrieves values just as a normal SELECT statement does, but retrieves them one at a time into the variable listed after INTO, and then performs the statements following DO. In this case, the only statement is SUSPEND, which suspends execution of the procedure and sends values back to the calling application.
2. See how the procedure works by entering the following query:

   SELECT *
   FROM   get_emp_proj(71)
   

   • At first it seems as though there is a table get_emp_proj, but you can see that it is a procedure because of the input parameter in parentheses following the procedure name.

   The image below shows the expected result:

   

Creating a Complex Select Procedure

1. This exercise modifies the previous previous procedure to add an output column that counts the line numbers:

   CREATE PROCEDURE get_emp_proj2 (v_empno SMALLINT)
   returns                        (line_no INTEGER,
                                   project_id CHAR(5))
   AS
   BEGIN
     line_no = 0;
     FOR
       SELECT proj_id
       FROM   employee_project
       WHERE  emp_no = :v_empno
       INTO
         :project_id
     DO
       BEGIN
   	  line_no = line_no+1;  
         suspend;
       END
   END
   

2. To test this procedure, execute the following query:

   SELECT *
   FROM   GET_EMP_PROJ2(71)
   

   You can see the following output:

   

3. If everything is OK, we recommend that you commit your work.

Advance To:

• Creating a Simple Executable Procedure