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 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