Next is the SQL code that creates the catalog_get_department_details stored procedure.
Execute it using phpMyAdmin. Don??™t forget to set the $$ delimiter!
-- Create catalog_get_department_details stored procedure
CREATE PROCEDURE catalog_get_department_details(IN inDepartmentId INT)
BEGIN
SELECT name, description
FROM department
WHERE department_id = inDepartmentId;
END$$
As you can see, a stored procedure is very much like a PHP function, in that it receives
parameters, executes some code, and returns the results. In this case, we have a single input
(IN) parameter named inDepartmentId, whose data type is int. Take note of the naming convention
we??™ve chosen for parameter names; the name of the parameter includes the parameter
direction (in), and uses camel casing.
MySQL also supports output (OUT) parameters and input-output (INOUT) parameters.
You??™ll see examples of using these a little later. The official documentation page for the CREATE
PROCEDURE command, which contains the complete details about using parameters with stored
procedures, is located at http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html.
The catalog_get_department_details stored procedure returns the names and descriptions
of all departments whose department_ids match the one specified by the inDepartmentId
input parameter. The WHERE clause (WHERE department_id = inDepartmentId) is used to request
the details of that specific department.
Pages:
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227