Java, SQL

SQL stored procedure

Contents

Introduction to SQL stored procedures

A stored procedure is just like a method in Java, it can include more than one SQL statement, and it can take parameters. Moreover we can save the SQL code and reuse it again.

Delimiter

First we have to know some thing called delimiter.

The default delimiter in MySQL is the semicolon ; and the delimiter marks the end of a statement. MySQL provides us a command that can change the delimiter:

mysql> delimiter $$

From now on we will end our statement with $$, let’s check:

mysql> select 'Pavatil' as "Name"$$

And we have:

+---------+
| Name    |
+---------+
| Pavatil |
+---------+

Well, funny but quite useless we may think. Maybe so if not for the stored procedures đŸ˜€

SQL stored procedure

Now let’s think about the stored procedure, we know that they can contain many SQL statements, each statement ends with a semicolon. But the statement that creates a procedure will also end with a semicolon. This seems confusing for MySQL to understand what we want to do. So in order to address this problem we can simply change the delimiter to $$ and after we create the procedure we changed the delimiter back to the semicolon ;

A simple procedure:

mysql> delimiter $$ 
mysql> create procedure TestQuery() 
       begin 
       select * from Customers; 
       end$$ 
mysql> delimiter ;

Perfect. Now we can call our simple procedure with the call keyword (unsurprisingly)

mysql> call TestQuery;

And the output:

+------------+-----------+-----------+
| CustomerId | FirstName | LastName  |
+------------+-----------+-----------+
|          1 | Tom       | Rex       |
|          2 | Luke      | Skywalker |
|          3 | Ken       | Star      |
+------------+-----------+-----------+

Well clearly we can add many statements to a procedure.

mysql> delimiter $$ 
mysql> create procedure NewTest() 
       begin 
       select * from Customers; 
       select * from Sales;
       end$$ 
mysql> delimiter ;

Now we can call the NewTest:

mysql> call NewTest;

And the contents of both tables will be output.

To delete a procedure:

mysql> drop procedure NewTest;

Now we know how to create and delete a procedure, how to modify it? We simply delete it and create new one with the same name, great đŸ˜€

A procedure will be stored in the database just like a table. And one more thing, they can take parameters. We can drop the TestQuery procedure and create a new one with the parameter. Basically the parameter is just like a Java method’s parameters, just that in SQL parameter’s name first, then parameter’s type:

mysql> drop procedure TestQuery;
mysql> delimiter $$ 
mysql> create procedure TestQuery(String varchar(50)) 
       begin 
       select * from Customers
       where FirstName = String; 
       end$$ 
mysql> delimiter ;

Now, we can test our new procedure:

mysql> call TestQuery('Tom');

And we have the output:

+------------+-----------+----------+
| CustomerId | FirstName | LastName |
+------------+-----------+----------+
|          1 | Tom       | Rex      |
+------------+-----------+----------+

Keep this procedure, we will use it in the jdbc section.

Leave a Reply