Select the
tshirtshop database from the Database combo box in the left side of the window.
2. Select the SQL tab, and change the delimiter to $$ as shown in Figure 4-12.
3. Execute the following code, which creates the catalog_get_departments_list stored procedure:
CHAPTER 4 ?– CREATING THE PRODUCT CATALOG: PART 1 83
-- Create catalog_get_departments_list stored procedure
CREATE PROCEDURE catalog_get_departments_list()
BEGIN
SELECT department_id, name FROM department ORDER BY department_id;
END$$
Figure 4-12. Creating the catalog_get_deparments_list stored procedure
How It Works: MySQL Stored Procedures
Let??™s break down in parts the catalog_get_departments_list stored procedure. On the first line, we??™re
defining the stored procedure name:
PROCEDURE catalog_get_departments_list()
The body of the stored procedure is between BEGIN and END$$. The following code snippet represents the typical
way we??™ll code our stored procedures. The bold line represents the query we??™re interested in, and the rest is auxiliary
code required to define the body of the stored procedure.
BEGIN
SELECT department_id, name FROM department ORDER BY department_id;
END$$
So what happens here? The code that performs the actual functionality is written between BEGIN and END$$. The
syntax may look weird at first, but what it does is pretty straightforward.
Pages:
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163