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

From InterBase
Jump to: navigation, search

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


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

    TutorialPocedure1.png

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

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

Advance To: