一、背景
MariaDB 10.3發(fā)布已經(jīng)有一段時(shí)間了官辽，里面提到了SQL_MODE新增了ORACLE選項(xiàng)蛹磺，可以支持部分的PL/SQL語(yǔ)法，算是一個(gè)比較“新鮮”的更新同仆，所以打算安裝體驗(yàn)一下萤捆，并測(cè)試支持情況。
二俗批、測(cè)試環(huán)境
硬件：
CPU：i5-8250U （四核八線程）
內(nèi)存：8GB
磁盤：240GB SSD
系統(tǒng)平臺(tái)：win10 x64
數(shù)據(jù)庫(kù)：MariaDB-10.3.9-winx64
三俗或、準(zhǔn)備測(cè)試數(shù)據(jù)
use test;
CREATE TABLE t_A (
id int,
code int,
name VARCHAR(10)
);
CREATE TABLE t_B (
id int,
code int,
name VARCHAR(10)
);
INSERT INTO t_A(id,code,name) VALUES(1,2,'A');
INSERT INTO t_A(id,code,name) VALUES(2,1,'B');
INSERT INTO t_A(id,code,name) VALUES(3,5,'C');
INSERT INTO t_A(id,code,name) VALUES(4,6,'D');
INSERT INTO t_A(id,code,name) VALUES(5,7,'E');
INSERT INTO t_B(id,code,name) VALUES(1,3,'AA');
INSERT INTO t_B(id,code,name) VALUES(1,4,'BB');
INSERT INTO t_B(id,code,name) VALUES(2,1,'CC');
INSERT INTO t_B(id,code,name) VALUES(1,2,'DD');
INSERT INTO t_B(id,code,name) VALUES(7,5,'GG');
CREATE TABLE temp (
id int,
code int,
name VARCHAR(50)
);
四、測(cè)試oracle語(yǔ)法支持
1岁忘、體驗(yàn)一下SQL_MODE="ORACLE"
Oracle中的示例文檔中代碼(Sample 1. FOR Loop)：
-- available online in file 'sample1'
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;</pre>
我們看看如果是這樣一段代碼辛慰，要在MySQL中運(yùn)行，應(yīng)該看起來(lái)是怎樣的干像。下面是修改在MySQL5.7中運(yùn)行的代碼：
CREATE PROCEDURE dowhile()
BEGIN
DECLARE x INT DEFAULT 100;
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
COMMIT;
SET x=x+100;
SET i=i+1;
END WHILE;
END;
以上兩種帅腌，主要的不同包括：
MySQL代碼必須在一個(gè)存儲(chǔ)過(guò)程中執(zhí)行辱志，所以這里創(chuàng)建了dowhile
- DECLARE語(yǔ)法不一樣，Oracle DECLARE在BEGIN之前狞膘，MySQL則在BEGIN里面
- MySQL不支持FOR... IN... LOOP的語(yǔ)法揩懒，這里改用WHILE來(lái)實(shí)現(xiàn)；MySQL也不支持"1..10"這種寫法
- 數(shù)據(jù)類型不同挽封，Oracle中是NUMBER已球，MySQL是INT
- 變量賦值不同，Oracle使用了“:=”辅愿，MySQL是 “SET .. = ...”
我們先看看智亮，前一段Oracle的代碼，在MariaDB里面是否能夠不做修改的運(yùn)行：
這個(gè)簡(jiǎn)單的示例可以正常運(yùn)行：
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.9-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use test
Database changed
MariaDB [test]> select * from temp;
Empty set (0.003 sec)
MariaDB [test]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)
MariaDB [test]> set session sql_mode="ORACLE";
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)
MariaDB [test]> delimiter /
MariaDB [test]> DECLARE
-> x NUMBER := 100;
-> BEGIN
-> FOR i IN 1..10 LOOP
-> IF MOD(i,2) = 0 THEN -- i is even
-> INSERT INTO temp VALUES (i, x, 'i is even');
-> ELSE
-> INSERT INTO temp VALUES (i, x, 'i is odd');
-> END IF;
-> x := x + 100;
-> END LOOP;
-> COMMIT;
-> END;
-> /
Query OK, 10 rows affected (0.020 sec)
MariaDB [test]> select * from temp/
+------+------+-----------+
| 列 1 | 列 2 | 列 3 |
+------+------+-----------+
| 1 | 100 | i is odd |
| 2 | 200 | i is even |
| 3 | 300 | i is odd |
| 4 | 400 | i is even |
| 5 | 500 | i is odd |
| 6 | 600 | i is even |
| 7 | 700 | i is odd |
| 8 | 800 | i is even |
| 9 | 900 | i is odd |
| 10 | 1000 | i is even |
+------+------+-----------+
10 rows in set (0.000 sec)
2点待、測(cè)試oracle的外關(guān)聯(lián)語(yǔ)法
oracle專用標(biāo)準(zhǔn)的關(guān)聯(lián)查詢阔蛉，以兩個(gè)關(guān)聯(lián)鍵值的左關(guān)聯(lián)SQL為例。
select *
from t_a a,t_b b
where a.id=b.id(+) and a.code=b.code(+)
and a.id!=8;
轉(zhuǎn)換為ANSI標(biāo)準(zhǔn)的left /right/full join 的寫法如下：
select *
from t_a a
left join t_b b on a.id=b.id and a.code=b.code
where a.id!=8;
測(cè)試情況：
MariaDB [test]> select *
-> from t_a a
-> left join t_b b on a.id=b.id and a.code=b.code
-> where a.id!=8;
-> /
+------+------+------+------+------+------+
| id | code | name | id | code | name |
+------+------+------+------+------+------+
| 2 | 1 | B | 2 | 1 | CC |
| 1 | 2 | A | 1 | 2 | DD |
| 3 | 5 | C | NULL | NULL | NULL |
| 4 | 6 | D | NULL | NULL | NULL |
| 5 | 7 | E | NULL | NULL | NULL |
+------+------+------+------+------+------+
5 rows in set (0.004 sec)
MariaDB [test]> select *
-> from t_a a,t_b b
-> where a.id=b.id(+) and a.code=b.code(+)
-> and a.id!=8;
-> /
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') and a.code=b.code(+)
and a.id!=8' at line 3
MariaDB [test]>
不支持oracle的（+）表示外關(guān)聯(lián)的語(yǔ)法
最后
目前還沒(méi)有完整量化評(píng)估MariaDB對(duì)PL/SQL的支持情況癞埠，對(duì)于oracle的外關(guān)聯(lián)語(yǔ)法（+）在oracle中的廣泛使用状原，目前還沒(méi)有得到支持。也許在后續(xù)的版本中苗踪，會(huì)逐步完善對(duì)oracle語(yǔ)法的兼容性颠区。
