Execute this code, which creates the catalog_update_department stored procedure in your tshirtshop
database. This stored procedure updates the name and description of an existing department using the
UPDATE SQL statement.
-- Create catalog_update_department stored procedure
CREATE PROCEDURE catalog_update_department(IN inDepartmentId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000))
BEGIN
UPDATE department
SET name = inName, description = inDescription
WHERE department_id = inDepartmentId;
END$$
5. Execute the following code, which creates the catalog_delete_department stored procedure in your
tshirtshop database. This procedure deletes an existing department from the database, but only if no
categories are related to it.
-- Create catalog_delete_department stored procedure
CREATE PROCEDURE catalog_delete_department(IN inDepartmentId INT)
BEGIN
DECLARE categoryRowsCount INT;
SELECT count(*)
FROM category
WHERE department_id = inDepartmentId
INTO categoryRowsCount;
IF categoryRowsCount = 0 THEN
DELETE FROM department WHERE department_id = inDepartmentId;
SELECT 1;
ELSE
SELECT -1;
END IF;
END$$
6. Finally, load the admin.php page in your browser, and admire your results. You should be able to add,
delete, and edit departments, as shown in Figures 10-2, 10-3, and 10-9.
Administering Categories
Because the categories administration page is based on the same steps and concepts as the
departments administration page, we??™ll quickly list the steps you need to follow.
Pages:
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415