InterBase Quick Start: Part V - Creating a Simple Select Procedure
From InterBase
Go Up to InterBase Quick Start: Part V - Advanced Topics
Creating a Simple SELECT Procedure
- 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 theEmployee_project
table. This statement retrieves values just as a normalSELECT
statement does, but retrieves them one at a time into the variable listed afterINTO
, and then performs the statements followingDO
. In this case, the only statement isSUSPEND
, which suspends execution of the procedure and sends values back to the calling application.
- The convention for variable names is to prepend them with
- 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:
- At first it seems as though there is a table
Creating a Complex Select Procedure
- 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
- To test this procedure, execute the following query:
SELECT * FROM GET_EMP_PROJ2(71)
You can see the following output:
- If everything is OK, we recommend that you commit your work.