When developing TShirtShop, we??™ll save all the data access code as MySQL stored procedures
inside the tshirtshop database. The syntax for creating stored procedures is
DELIMITER $$
CREATE PROCEDURE
(, ... )
BEGIN
END$$
DELIMITER;
Note that the delimiter can be defined as something other than $$. The key is to define it
as something different than the default delimiter, the semicolon.
You can??™t create a stored procedure if your database already has a procedure with the same
name. To remove an existing stored procedure, you use the DROP PROCEDURE command. To change
the body or parameters of an existing procedure, you need to delete it using DROP PROCEDURE and
create it again. MySQL supports a command named ALTER PROCEDURE, but unlike with other database
applications, it can??™t be used to update the body or parameters of an existing procedure.
For the data tier of the departments list, you need to create stored procedure called
catalog_get_departments_list. This procedure returns a list with the IDs and names of the
departments in TShirtShop, and it will be called by business tier methods that need this data.
Let??™s implement catalog_get_departments_list in the following exercise.
Exercise: Creating MySQL Stored Procedures
1. Load phpMyAdmin (http://localhost/phpmyadmin/) into your favorite browser.
Pages:
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162