Filter
Top Products
Introduction to procedures
234
3 Create a variable to hold the procedure output. In this case, the output
variable is numeric, with three decimal places, so create a variable as
follows:
CREATE VARIABLE Average NUMERIC(20,3)
4 Call the procedure, using the created variable to hold the result:
CALL AverageSalary(Average)
The DBISQL statistics window displays the message "Procedure completed"
if the procedure was created and run properly.
Look at the value of the output variable Average. The DBISQL Data window
displays the value 49988.623 for this variable, the average employee salary.
Returning procedure results in result sets
In addition to returning results to the calling environment in individual
parameters, procedures can return information in result sets. A result set is
typically the result of a query. The following procedure returns a result set
containing the salary for each employee in a given department:
CREATE PROCEDURE SalaryList (IN department_id INT)
RESULT ( "Employee ID" INT, "Salary" NUMERIC(20,3) )
BEGIN
SELECT emp_id, salary
FROM employee
WHERE employee.dept_id = department_id;
END
If this procedure is called from DBISQL, the names in the RESULT clause are
matched to the results of the query and used as column headings in the
displayed results.
To test this procedure from DBISQL, you can
CALL it, specifying one of the
departments of the company. The results are displayed in the DBISQL Data
window. For example, to list the salaries of employees in the R & D department
(department ID 100), type the following:
CALL SalaryList (100)
Employee ID Salary
102 45700.000
105 62000.000
160 57490.000