"CREATE PROCEDURE" - Creating a Simple Procedure

<< MySQL - JBDC CallableStatement

<< JDBC Tutorials - Herong's Tutorial Notes

This section describes how to create a simple procedure that runs a simple query.

In order to test CallableStatement objects, I created a very simple stored procedure that runs a query to return "Hello world!":

C:\>\local\mysql\bin\mysql -u Herong -pTopSecret

mysql> USE HerongDB;
Database changed

mysql> DELIMITER '/';

mysql> CREATE PROCEDURE HelloWorld()
    -> BEGIN
    ->   SELECT 'Hello world!' AS Message;
    -> END/
Query OK, 0 rows affected (0.00 sec)

mysql> CALL HelloWorld()/
+--------------+
| Message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Note that "DELIMITER '/';" was used to change the command delimiter from ";" to "/", so that I can use ";" as part of the stored procedure definition statement.

"CALL HelloWorld()/" is a command to test the stored procedure.

If made a mistake in the stored procedure, you can delete it from the server by the "DROP PROCEDURE HelloWorld" command.

Sections in This Chapter

Overview of CallableStatement Objects

"CREATE PROCEDURE" - Creating a Simple Procedure

Creating Procedures with IN and OUT Parameters

Creating Procedures with INOUT Parameters

Creating Procedures with Multiple Queries

Creating CallableStatement Objects with prepareCall()

Capturing ResultSet with executeQuery()

Creating CallableStatement Objects with Parameters

Common Errors with CallableStatement Parameters

Creating CallableStatement Objects with INOUT Parameters

Retrieving Multiple ResultSet Objects

Executing Stored Procedures without Permission

getProcedures() - Listing Stored Procedures