catalog_get_categories_list
When a visitor selects a department, the categories that belong to that department must be
displayed. The categories will be retrieved by the catalog_get_categories_list stored procedure,
which returns the list of categories in a specific department. The stored procedure needs
to know the ID of the department for which to retrieve the categories.
-- Create catalog_get_categories_list stored procedure
CREATE PROCEDURE catalog_get_categories_list(IN inDepartmentId INT)
BEGIN
SELECT category_id, name
FROM category
WHERE department_id = inDepartmentId
ORDER BY category_id;
END$$
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 131
catalog_get_category_details
When the visitor selects a particular category, we need to display its name and description.
Execute this code, using the $$ delimiter, to create the procedure:
-- Create catalog_get_category_details stored procedure
CREATE PROCEDURE catalog_get_category_details(IN inCategoryId INT)
BEGIN
SELECT name, description
FROM category
WHERE category_id = inCategoryId;
END$$
catalog_count_products_in_category
This function returns the number of products in a category. This data will be necessary when
paginating the lists of products, and we??™ll need to be able to calculate how many pages of
products we have in a category.
Note that, unlike the previous procedures you??™ve written, this time, we return a single
value rather than a set of data.
Pages:
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228