Let??™s see what this is all about.
Exercise: Adding Data Tier Stored Procedures to the Database
1. Use phpMyAdmin to execute and create the stored procedures described in the following steps. Also, don??™t
forget to set $$ as the delimiter before executing the code.
2. Execute this code, which creates the catalog_get_departments stored procedure in your tshirtshop
database. catalog_get_departments is the simplest stored procedure you??™ll write here. It returns the list of
departments with their IDs, names, and descriptions. It is similar to the catalog_get_departments_list
stored procedure called to fill the departments list from the storefront, but this one also returns the descriptions.
-- Create catalog_get_departments stored procedure
CREATE PROCEDURE catalog_get_departments()
BEGIN
SELECT department_id, name, description
FROM department
ORDER BY department_id;
END$$
3. Execute the following code, which creates the catalog_add_department stored procedure in your
tshirtshop database. This procedure inserts a new department into the database.
-- Create catalog_add_department stored procedure
CREATE PROCEDURE catalog_add_department(
IN inName VARCHAR(100), IN inDescription VARCHAR(1000))
BEGIN
INSERT INTO department (name, description)
VALUES (inName, inDescription);
END$$
CHAPTER 10 ?– CATALOG ADMINISTRATION: DEPARTMENTS AND CATEGORIES 294
4.
Pages:
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414