InterBase Quick Start: Part V - Creating a Simple SELECT Procedures

From InterBase
Jump to: navigation, search

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

Image 025.jpg Creating a Simple SELECT Procedure

1. Execute the following code to create the get_emp_proj procedure. There is a useful convention of starting variable names with “v_” to help make the code readable, but it is not required. You can name variables anything you wish. The following code is a single SQL statement. Enter the whole thing and then execute it:

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
This is a select procedure that takes an employee number as its input parameter (v_empno, specified in parentheses after the procedure name) and returns all the projects to which the employee is assigned (project_id, specified after RETURNS). The variables are named in the header as varname and then referenced in the body as :varname.
It 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 (in this case, InterBase Windows > SQL).

2. See how the procedure works by entering the following query:

SELECT * FROM get_emp_proj(71)
This query looks at first as though there were a table named get_emp_proj, but you can tell that it is a procedure rather than a table because of the input parameter in parentheses following the procedure name. The results are:


TableQuery28.png

These are the projects to which employee number 71 is assigned. Try it with some other employee numbers.

Image 025.jpg Create a More Complex Select Procedure

3. The next exercise starts with the code for the previous procedure and adds an output column that counts the line numbers. Execute the following code. (You can display the previous query, if you wish, and add the new code.)

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

4. To test this new procedure, execute the following query:

SELECT * FROM get_emp_proj2(71)

You should see the following output:

TableQuery30.png

5. If your procedure returns the correct result set, commit your work.

Advance To:

Image 025.jpg Creating a Simple Executable Procedure