Creating Procedures with Multiple Queries

JDBC for MySQL - Herong's Tutorial Examples

∟Creating Procedures with Multiple Queries

This section describes how to create a procedure with multiple queries.

To do more tests on CallableStatement objects, I created another stored procedure with multiple queries:

herong> local\mysql\bin\mysql -u Herong -pTopSecret

mysql> USE HerongDB;
Database changed

mysql> -- Changing the command delimiter to '$'
mysql> DELIMITER '$';

mysql> -- Creating the procedure
mysql> CREATE PROCEDURE HeadTail(OUT Size INTEGER)
    -> BEGIN
    ->   SELECT ID, FirstName, LastName, BirthDate, ModTime
    ->     FROM Profile ORDER BY ID LIMIT 0, 10;
    ->   SELECT ID, FirstName, LastName, BirthDate, ModTime
    ->     FROM Profile ORDER BY ID DESC LIMIT 0, 10;
    ->   SELECT COUNT(*) INTO Size FROM Profile;
    -> END$
Query OK, 0 rows affected (0.00 sec)

mysql> -- Testing the procedure
mysql> CALL HeadTail(@Size)$
+-------+-----------+----------+------------+---------------------+
| ID    | FirstName | LastName | BirthDate  | ModTime             |
+-------+-----------+----------+------------+---------------------+
| 60006 | 547       | 22741    | 2000-12-31 | 2027-01-01 00:00:00 |
| 60007 | a2f       | 8954f    | NULL       | NULL                |
| 60008 | 2632      | 74f88    | NULL       | NULL                |
| 60009 | 1680      | a1708    | NULL       | NULL                |
| 60010 | 1234      | 984fc    | NULL       | NULL                |
| 60011 | 20d4      | 789e8    | NULL       | NULL                |
| 60012 | 2037      | 79ba0    | NULL       | NULL                |
| 60013 | f71       | d6b24    | NULL       | NULL                |
| 60014 | 34f       | add23    | NULL       | NULL                |
| 60015 | 2310      | 889ca    | NULL       | NULL                |
+-------+-----------+----------+------------+---------------------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.06 sec)

mysql> -- Check the output parameter
mysql> SELECT @Size$
+-------+
| @Size |
+-------+
| 10001 |
+-------+
1 row in set (0.00 sec)