Hiding SQL in a Stored Procedure

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.




It doesn’t work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren’t that bad. Minor tweaks really.




The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.




So here is what I found.




My create table command and inserts ran unchanged. I did run into an issue with the timestamp though.



mysql> create table test_data (
-> name text,
-> address text,
-> create_date timestamp );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test_data values (
-> 'lewis',
-> '123 abc st',
-> timestamp '2001-01-01 buy cipro online india 10:00:00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_data values (
-> 'george',
-> '456 def dr',
-> timestamp '2091-01-01 10:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> select * from test_data;
+--------+------------+---------------------+
| name | address | create_date |
+--------+------------+---------------------+
| lewis | 123 abc st | 2001-01-01 10:00:00 |
| george | 456 def dr | 0000-00-00 00:00:00 |
+--------+------------+---------------------+
2 rows in set (0.00 sec)

Notice the timestamp in the “george” record is all 0s. I figure that’s a configurable issue but I don’t really care to research it at this moment so I’ll just delete it and use a timestamp that’s a little closer to NOW.



mysql> delete from test_data where name = 'george';
Query OK, 1 row affected (0.03 sec)
mysql> insert into test_data values (
-> 'george',
-> '456 def dr',
-> timestamp '2021-01-01 10:00:00');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test_data;
+--------+------------+---------------------+
| name | address | create_date |
+--------+------------+---------------------+
| lewis | 123 abc st | 2001-01-01 10:00:00 |
| george | 456 def dr | 2021-01-01 10:00:00 |
+--------+------------+---------------------+
2 rows in set (0.00 sec)
mysql>





Ok. Now I’m ready to go. I look at the proc that I wrote for Postgres:



CREATE OR REPLACE FUNCTION get_data_by_creation(
timestamp without time zone,
timestamp without time zone)
RETURNS SETOF test_data
AS
$$
SELECT name, address, create_date
FROM test_data
WHERE create_date >= $1
AND create_date <= $2;
$$
LANGUAGE 'sql' VOLATILE;

That’s obviously not going to work but like I said above, the changes are fairly minor. I need to add a delimiter call and drop the postgres specific stuff:

delimiter //
CREATE PROCEDURE get_data_by_creation(
IN param1 timestamp,
IN param2 timestamp)
BEGIN
SELECT name, address, create_date
FROM test_data
WHERE create_date >= param1
AND create_date <= param2;
END;
//

That compiles fine. Now for the test. I can’t use select so I will do a call. I write three call statements: one to return both records, one to “george” and one to return “lewis”.

call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');
call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');
call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

When I run these, I get the expected results:

mysql> call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');
+--------+------------+---------------------+
| name | address | create_date |
+--------+------------+---------------------+
| lewis | 123 abc st | 2001-01-01 10:00:00 |
| george | 456 def dr | 2021-01-01 10:00:00 |
+--------+------------+---------------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.05 sec)

mysql> call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');
+--------+------------+---------------------+
| name | address | create_date |
+--------+------------+---------------------+
| george | 456 def dr | 2021-01-01 10:00:00 |
+--------+------------+---------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');
+-------+------------+---------------------+
| name | address | create_date |
+-------+------------+---------------------+
| lewis | 123 abc st | 2001-01-01 10:00:00 |
+-------+------------+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Sweet! This code is actually not all that far from Oracle’s PL/SQL. I’ll do up an example of that next.




LewisC

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.