Share this page : facebooktwitterlinkedinmailfacebooktwitterlinkedinmail
Join
Tables
Customer table:
+----------+-----------+-----------+-------------+--------------+-----------+-------------+

| CUS_CODE | CUS_LNAME | CUS_FNAME | CUS_INITIAL | CUS_AREACODE | CUS_PHONE | CUS_BALANCE |

+----------+-----------+-----------+-------------+--------------+-----------+-------------+

|    10010 | Ramas     | Alfred    | A           | 615          | 844-2573  |        0.00 |

|    10011 | Dunne     | Leona     | K           | 713          | 894-1238  |        0.00 |

|    10012 | Smith     | Kathy     | W           | 615          | 894-2285  |      345.86 |

|    10013 | Olowski   | Paul      | F           | 615          | 894-2180  |      536.75 |

|    10014 | Orlando   | Myron     | NULL        | 615          | 222-1672  |        0.00 |

|    10015 | O'Brian   | Amy       | B           | 713          | 442-3381  |        0.00 |

|    10016 | Brown     | James     | G           | 615          | 297-1228  |      221.19 |

|    10017 | Williams  | George    | NULL        | 615          | 290-2556  |      768.93 |

|    10018 | Farriss   | Anne      | G           | 713          | 382-7185  |      216.55 |

|    10019 | Smith     | Olette    | K           | 615          | 297-3809  |        0.00 |

+----------+-----------+-----------+-------------+--------------+-----------+-------------+

Line table:
+------------+-------------+----------+------------+------------+

| INV_NUMBER | LINE_NUMBER | P_CODE   | LINE_UNITS | LINE_PRICE |

+------------+-------------+----------+------------+------------+

|       1001 |           1 | 13-Q2/P2 |       1.00 |      14.99 |

|       1001 |           2 | 23109-HB |       1.00 |       9.95 |

|       1002 |           1 | 54778-2T |       2.00 |       4.99 |

|       1003 |           1 | 2238/QPD |       1.00 |      38.95 |

|       1003 |           2 | 1546-QQ2 |       1.00 |      39.95 |

|       1003 |           3 | 13-Q2/P2 |       5.00 |      14.99 |

|       1004 |           1 | 54778-2T |       3.00 |       4.99 |

|       1004 |           2 | 23109-HB |       2.00 |       9.95 |

|       1005 |           1 | PVC23DRT |      12.00 |       5.87 |

|       1006 |           1 | SM-18277 |       3.00 |       6.99 |

|       1006 |           2 | 2232/QTY |       1.00 |     109.92 |

|       1006 |           3 | 23109-HB |       1.00 |       9.95 |

|       1006 |           4 | 89-WRE-Q |       1.00 |     256.99 |

|       1007 |           1 | 13-Q2/P2 |       2.00 |      14.99 |

|       1007 |           2 | 54778-2T |       1.00 |       4.99 |

|       1008 |           1 | PVC23DRT |       5.00 |       5.87 |

|       1008 |           2 | WR3/TT3  |       3.00 |     119.95 |

|       1008 |           3 | 23109-HB |       1.00 |       9.95 |

+------------+-------------+----------+------------+------------+

Invoice table:
+------------+----------+---------------------+

| INV_NUMBER | CUS_CODE | INV_DATE            |

+------------+----------+---------------------+

|       1001 |    10014 | 2016-01-16 00:00:00 |

|       1002 |    10011 | 2016-01-16 00:00:00 |

|       1003 |    10012 | 2016-01-16 00:00:00 |

|       1004 |    10011 | 2016-01-17 00:00:00 |

|       1005 |    10018 | 2016-01-17 00:00:00 |

|       1006 |    10014 | 2016-01-17 00:00:00 |

|       1007 |    10015 | 2016-01-17 00:00:00 |

|       1008 |    10011 | 2016-01-17 00:00:00 |

+------------+----------+---------------------+
Product:
+----------+-------------------------------------+---------------------+-------+-------+---------+------------+--------+
| P_CODE | P_DESCRIPT | P_INDATE | P_QOH | P_MIN | P_PRICE | P_DISCOUNT | V_CODE |
+----------+-------------------------------------+---------------------+-------+-------+---------+------------+--------+
| 11QER/31 | Power painter, 15 psi., 3-nozzle | 2015-11-03 00:00:00 | 8 | 5 | 109.99 | 0.00 | 25595 |
| 13-Q2/P2 | 7.25-in. pwr. saw blade | 2015-12-13 00:00:00 | 32 | 15 | 14.99 | 0.05 | 21344 |
| 14-Q1/L3 | 9.00-in. pwr. saw blade | 2015-11-13 00:00:00 | 18 | 12 | 17.49 | 0.00 | 21344 |
| 1546-QQ2 | Hrd. cloth, 1/4-in., 2x50 | 2016-01-15 00:00:00 | 15 | 8 | 39.95 | 0.00 | 23119 |
| 1558-QW1 | Hrd. cloth, 1/2-in., 3x50 | 2016-01-15 00:00:00 | 23 | 5 | 43.99 | 0.00 | 23119 |
| 2232/QTY | B&D jigsaw, 12-in. blade | 2015-12-30 00:00:00 | 8 | 5 | 109.92 | 0.05 | 24288 |
| 2232/QWE | B&D jigsaw, 8-in. blade | 2015-12-24 00:00:00 | 6 | 5 | 99.87 | 0.05 | 24288 |
| 2238/QPD | B&D cordless drill, 1/2-in. | 2016-01-20 00:00:00 | 12 | 5 | 38.95 | 0.05 | 25595 |
| 23109-HB | Claw hammer | 2016-01-20 00:00:00 | 23 | 10 | 9.95 | 0.10 | 21225 |
| 23114-AA | Sledge hammer, 12 lb. | 2016-01-02 00:00:00 | 8 | 5 | 14.40 | 0.05 | NULL |
| 54778-2T | Rat-tail file, 1/8-in. fine | 2015-12-15 00:00:00 | 43 | 20 | 4.99 | 0.00 | 21344 |
| 89-WRE-Q | Hicut chain saw, 16 in. | 2016-02-07 00:00:00 | 11 | 5 | 256.99 | 0.05 | 24288 |
| PVC23DRT | PVC pipe, 3.5-in., 8-ft | 2016-02-20 00:00:00 | 188 | 75 | 5.87 | 0.00 | NULL |
| SM-18277 | 1.25-in. metal screw, 25 | 2016-03-01 00:00:00 | 172 | 75 | 6.99 | 0.00 | 21225 |
| SW-23116 | 2.5-in. wd. screw, 50 | 2016-02-24 00:00:00 | 237 | 100 | 8.45 | 0.00 | 21231 |
| WR3/TT3 | Steel matting, 4'x8'x1/6", .5" mesh | 2016-01-17 00:00:00 | 18 | 5 | 119.95 | 0.10 | 25595 |
+----------+-------------------------------------+---------------------+-------+-------+---------+------------+--------+

To find the customer who bought ‘Claw hammer’, suppose you

SELECT FROM

WHERE

DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME CUSTOMER JOIN INVOICE USING (CUS_CODE)

JOIN LINE USING (INV_NUMBER)

JOIN PRODUCT USING (P_CODE) P_DESCRIPT = 'Claw hammer';

 

Writing MySQL stored procedure

We are going to develop a simple stored procedure named GetAllProducts()  to help you get familiar with the syntax. The GetAllProducts()  stored procedure selects all products from the products  table.

Launch the mysql client tool and type the following commands:

mysql> DELIMITER //

mysql> CREATE PROCEDURE GetAllProducts()

   -> BEGIN

   ->SELECT *  FROM products;

   ->END //

mysql> DELIMITER ;

Let’s examine the stored procedure in greater detail:

  • The first command is DELIMITER // , which is not related to the stored procedure syntax. The DELIMITER statement changes the standard delimiter which is semicolon ( ; ) to another. In this case, the delimiter is changed from the semicolon( ; ) to double-slashes // .
    Why do we have to change the delimiter? Because we want to pass the stored procedure to the server as a whole rather than letting mysql tool interpret each statement at a time.  Following the END keyword, we use the delimiter //  to indicate the end of the stored procedure. The last command ( DELIMITER; ) changes the delimiter back to the semicolon (;).
    You can see how the system use this method in the MySQLWorkbench later.
  • We use the CREATE PROCEDURE  statement to create a new stored procedure. We specify the name of stored procedure after the CREATE PROCEDURE  statement. In this case, the name of the stored procedure is GetAllProducts . We put the parentheses after the name of the stored procedure.
  • The section between BEGIN and END  is called the body of the stored procedure. You put the declarative SQL statements in the body to handle business logic. In this stored procedure, we use a simple SELECT statement to query data from the products table.
By MySQLWorkbench

First,expand the database name under which you want to create the procedure, right mouse click on the Stored Procedures ( or Roiutine) and choose “Create Stored Procedure…” menu item.

Next, Change the ‘new_procedure’ into the name you like, enter the stored procedure code between “BEGIN” and “END”,  click the Apply button.

Then, you can review the code before MySQL stores it in the database. Click Apply button if everything is good. Note that in the “Review the SQL Script to be Applied on the database” window, you can see the system change delimiter into $$ and after that change it back to “;” .

Calling stored procedures

In order to call a stored procedure, you use the following SQL command:

EXEC procedure_name(parameter_list);

MySQL:

CALL STORED_PROCEDURE_NAME();