MacLochlainns Weblog

As I continue down the warren hole of Persistent Stored Modules (SQL/PSM) in MySQL, I keep wondering about that mad hare, Johnny Depp. Alice isn’t a programming language to teach me anything in this dream. Moreover, TIm Burton’s tale this seems oddly familiar, like a child’s story gone mad.

A quick update on comparative SQL expression assignments between PL/SQL and MySQL. When you want to filter a value through SQL functions before assigning it to another variable in MySQL, it’s not like PL/SQL. Just like the new Alice in Wonderland movie isn’t like the book.

The programmatic differences lies in their origins. PL/SQL evolved from Pascal through Ada to become a recursive language where you can call SQL from PL/SQL and PL/SQL from SQL. MySQL implemented PSMs from the ANSI SQL:2003 specification, which didn’t see it the same way, apparently (a disclaimer since I’ve not read the details of the specification).

Personally, I think PL/SQL is easier to write but I’ve been using it for almost 20 years. Naturally, there may be a consistency thread on this that I’m missing and an opportunity that I may exploit. After all, it is dark in this warren hole.

Oracle PL/SQL Assignments from SQL Expressions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Enable output printing.
SET SERVEROUTPUT ON SIZE 1000000
 
-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  lv_left_operand := UPPER(SUBSTR(lv_right_operand,1,3));
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

Oracle also supports this syntax, which isn’t frequently used because it’s much more verbose syntactically. It is also equivalent to the PSM syntax adopted by MySQL.

-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  SELECT UPPER(SUBSTR(lv_right_operand,1,3)) INTO lv_left_operand FROM dual;
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

That means we can do it like the White Queen wants it or the Red Queen wants it in Oracle. Flexibility in PL/SQL is clearly broader because of the assignment options. Not so in MySQL, as you’ll see.

MySQL PSM Assignment from SQL Expressions

First, MySQL’s PSM approach doesn’t support anonymous blocks. The example must create a stored function or procedure, and then call it. A procedure seems like the best fit for the example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Conditionally drop procedure.
SELECT 'DROP PROCEDURE IF EXISTS assignit' AS "Statement";
DROP PROCEDURE IF EXISTS assignit;
 
-- Create the proceudre
SELECT 'CREATE PROCEDURE assignit' AS "Statement";
 
DELIMITER $$
 
-- Define the procedure.
CREATE PROCEDURE assignit()
BEGIN
  /* Declare a source variable. */
  DECLARE lv_right_operand VARCHAR(10) DEFAULT 'March';
 
  /* Define a target variable for the assignment. */
  DECLARE lv_left_operand  VARCHAR(3);
 
  /* Assign the modified value through the SELECT-INTO model. */
  SELECT UCASE(SUBSTRING(lv_right_operand,1,3)) INTO lv_left_operand;
 
  /* Display assigned value. */
  SELECT lv_left_operand;
 
END;
$$
 
DELIMITER ;
 
-- Call the procedure.
CALL assignit();

The only question here in the warren is: Who’s the White Queen; and who’s the Red Queen. Which semantic should I choose? My I hope is that I wake up before it’s … oops, off with his head. Actually, 3D or not, I’ll probably not see it, that’s the new Alice in Wonderland film.

Likewise, when my students wake up and read this they’ll know I was just answering a question on how to perform assignments in MySQL stored procedures. By the way, I’ve updated this assignment process in my Debugging MySQL Procedures post.

As an aside, I’ve got a new MySQL debugger that I’m testing later in the week. When I complete the test cases, I’ll post a review.