ブログ

MariaDB 10.3 PL/SQL対応状況を調査

検証DB Version

Oracle 12.1.0.2.0
MariaDB 10.3.7

事前準備

MariaDBには DBMS_OUTPUT のようなパッケージは存在しないので下記ブログを参考に PUT_LINE プロシージャを新規作成

MariaDB 10.3のPL/SQLを試してみた
https://www.s-style.co.jp/blog/2018/04/1680/

set sql_mode=oracle;

create database plsql;
use plsql;

DELIMITER /
CREATE OR REPLACE PACKAGE
  DBMS_OUTPUT
AS
  PROCEDURE PUT_LINE(item in varchar2);
END;
/
DELIMITER ;

DELIMITER /
CREATE OR REPLACE PACKAGE BODY
  DBMS_OUTPUT
AS
  PROCEDURE PUT_LINE(item in varchar2)
IS 
BEGIN
  select item as 'Output';
EXCEPTION 
WHEN OTHERS THEN
  NULL;
END;
END;
/
DELIMITER ;

PL/SQL実行

PL/SQL は下記サイトから抜粋

“PL/SQL 101” ～ PL/SQLの基礎を学ぶ～
http://www.oracle.com/technetwork/jp/database/articles/plsql101/index.html

PL/SQL #1

Oracle

set serveroutput ON
DECLARE
  l_message  
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
END;
 /

MariaDB

DELIMITER /
DECLARE
  l_message
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
END;
/

実行結果一致
Oracle実行結果

Hello World!

PL/SQLプロシージャが正常に完了しました。

MariaDB実行結果

+--------------+
| Output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.078 sec)

PL/SQL #2

Oracle

set serveroutput ON
DECLARE
  l_message  
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/

MariaDB

DELIMITER /
DECLARE
  l_message
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/

実行結果一致

Oracle実行結果

Hello World!

PL/SQLプロシージャが正常に完了しました。

MariaDB実行結果

+--------------+
| Output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.001 sec)

PL/SQL #3

Oracle

set serveroutput ON
DECLARE
  l_message  
  VARCHAR2 (100) := 'Hello';
BEGIN
  DECLARE
    l_message2     VARCHAR2 (100) := 
      l_message || ' World!'; 
  BEGIN
    DBMS_OUTPUT.put_line (l_message2);
  END;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line 
   (DBMS_UTILITY.format_error_stack);
END;
/

MariaDB

DELIMITER /
DECLARE
  l_message
  VARCHAR2 (100) := 'Hello';
BEGIN
  DECLARE
    l_message2 VARCHAR2 (100) :=
    l_message || ' World!';

  BEGIN
    DBMS_OUTPUT.put_line (l_message2);
  END;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line
    (DBMS_UTILITY.format_error_stack);
END;
/

実行結果一致

Oracle実行結果

Hello World!

PL/SQLプロシージャが正常に完了しました。

MariaDB実行結果

+--------------+
| Output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.000 sec)

PL/SQL #4

Oracle

set serveroutput ON
CREATE OR REPLACE PROCEDURE 
hello_place (place_in IN VARCHAR2)
IS
  l_message  VARCHAR2 (100);
BEGIN
  l_message  := 'Hello ' || place_in;
  DBMS_OUTPUT.put_line (l_message);
END hello_place;
/
BEGIN
   hello_place ('World');
   hello_place ('Universe');
END;
/

MariaDB

DELIMITER /
CREATE OR REPLACE PROCEDURE
  hello_place (place_in IN VARCHAR2)
IS
  l_message VARCHAR2 (100);
BEGIN
  l_message := 'Hello ' || place_in;
  DBMS_OUTPUT.put_line (l_message);
END hello_place;
/

BEGIN
  hello_place ('World');
  hello_place ('Universe');
END;
/

実行結果一致

>result:Oracle実行結果

Hello World
Hello Universe

PL/SQLプロシージャが正常に完了しました。

MariaDB実行結果

+-------------+
| Output      |
+-------------+
| Hello World |
+-------------+
1 row in set (0.001 sec)

+----------------+
| Output         |
+----------------+
| Hello Universe |
+----------------+
1 row in set (0.001 sec)

PL/SQL #5

Oracle

set serveroutput ON
CREATE OR REPLACE PROCEDURE
  hello_world
IS
  l_message
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
END hello_world;
/
BEGIN
  hello_world;
END;
/

MariaDB

DELIMITER /
CREATE OR REPLACE PROCEDURE 
  hello_world
IS
  l_message
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
END hello_world;
/

BEGIN
  hello_world;
END;
/

実行結果一致

Oracle実行結果

Hello World!

PL/SQLプロシージャが正常に完了しました。

MariaDB実行結果

+--------------+
| Output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.001 sec)

PL/SQL #6

Oracle

set serveroutput ON
DECLARE
  l_variable VARCHAR2 (10) := 'Logic';
  l_fixed  CHAR (10) := 'Logic';
BEGIN
  IF l_variable = l_fixed
  THEN
    DBMS_OUTPUT.put_line ('Equal');
  ELSE
    DBMS_OUTPUT.put_line ('Not Equal');
  END IF;
END;
/

MariaDB

DELIMITER /
DECLARE
  l_variable VARCHAR2 (10) := 'Logic';
  l_fixed  CHAR (10) := 'Logic';
BEGIN
  IF l_variable = l_fixed
  THEN
    DBMS_OUTPUT.put_line ('Equal');
  ELSE
    DBMS_OUTPUT.put_line ('Not Equal');
  END IF;
END;
/

結果が 不一致 となる
Oracle `CHAR` 型は値の長さが10になるまで空白でパディングする為

Oracle実行結果

Not Equal

PL/SQLプロシージャが正常に完了しました。

MariaDB実行結果

+--------+
| Output |
+--------+
| Equal  |
+--------+
1 row in set (0.032 sec)

トランザクション

検証用テーブル作成

CREATE TABLE test_tbl1
(
  col1 number,
  col2 number,
  col3 varchar2(20)
);

CREATE TABLE test_tbl2
(
  col1 number
);

INSERT INTO test_tbl2 VALUES(4);
INSERT INTO test_tbl2 VALUES(5);
INSERT INTO test_tbl2 VALUES(10);
COMMIT;

CREATE TABLE test_tbl3
(
  col1 number,
  col2 number,
  col3 varchar2(20)
);

CREATE TABLE test_tbl3_log
(
  col1 number,
  col2 number,
  col3 varchar2(20),
  col4 timestamp
);

クエリ実行

Oracle

set serveroutput ON
DECLARE
    x  NUMBER := 100;
BEGIN
    FOR i IN 1..10 LOOP
        IF MOD(i,2) = 0 THEN     -- i is even
            INSERT INTO test_tbl1 VALUES (i, x, 'i is even');
        ELSE
            INSERT INTO test_tbl1 VALUES (i, x, 'i is odd');
        END IF;

        IF MOD(x,500) = 0 THEN
            COMMIT;
        END IF;
        IF MOD(x,300) = 0 THEN
            ROLLBACK;
        END IF;
        x := x + 100;
    END LOOP;
    ROLLBACK;
END;
/

select * from test_tbl1;

MariaDB

set autocommit=0;

DELIMITER /
DECLARE
    x  NUMBER := 100;
BEGIN
    FOR i IN 1..10 LOOP
        IF MOD(i,2) = 0 THEN     -- i is even
            INSERT INTO test_tbl1 VALUES (i, x, 'i is even');
        ELSE
            INSERT INTO test_tbl1 VALUES (i, x, 'i is odd');
        END IF;

        IF MOD(x,500) = 0 THEN
            COMMIT;
        END IF;
        IF MOD(x,300) = 0 THEN
            ROLLBACK;
        END IF;
        x := x + 100;
    END LOOP;
    ROLLBACK;
END;
/
DELIMITER ;

実行結果

Oracle実行結果

      COL1	 COL2 COL3
---------- ---------- --------------------
	 4	  400 i is even
	 5	  500 i is odd
	10	 1000 i is even

3行が選択されました。

MariaDB実行結果

+------+------+-----------+
| col1 | col2 | col3      |
+------+------+-----------+
|    4 |  400 | i is even |
|    5 |  500 | i is odd  |
|   10 | 1000 | i is even |
+------+------+-----------+
3 rows in set (0.000 sec)

ファンクション

Oracle

set serveroutput ON
CREATE OR REPLACE FUNCTION func_getWa (
   v_MaxNum NUMBER) RETURN NUMBER IS
   v_Result NUMBER;
BEGIN
   v_Result := (v_MaxNum * (v_MaxNum + 1))/2;
   RETURN v_Result;
END;
/

select func_getwa(10) from dual;

MariaDB

DELIMITER /
CREATE OR REPLACE FUNCTION func_getWa (
   v_MaxNum NUMBER) RETURN NUMBER IS
   v_Result NUMBER;
BEGIN
   v_Result := (v_MaxNum * (v_MaxNum + 1))/2;
   RETURN v_Result;
END;
/
DELIMITER ;

select func_getwa(10);

Oracle実行結果

FUNC_GETWA(10)
--------------
	    55

MariaDB実行結果

+----------------+
| func_getwa(10) |
+----------------+
|             55 |
+----------------+

トリガー

Orcale

set serveroutput ON
CREATE OR REPLACE PACKAGE Pack_getWa AS
   PROCEDURE proc_getWa(v_MaxNum IN NUMBER);
   FUNCTION func_getWa(v_MaxNum NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY Pack_getWa AS

   PROCEDURE proc_getWa (
      v_MaxNum IN NUMBER) IS
      v_Result NUMBER;
   BEGIN
      v_Result := (v_MaxNum * (v_MaxNum + 1))/2;
      DBMS_OUTPUT.PUT_LINE('Sum from 1 to '||v_MaxNum||' : '||v_Result);
   END proc_getWa;

   FUNCTION func_getWa (
      v_MaxNum NUMBER) RETURN NUMBER IS
      v_Result NUMBER;
   BEGIN
      v_Result := (v_MaxNum * (v_MaxNum + 1))/2;
      RETURN v_Result;
   END func_getWa;

END Pack_getWa;
/

select Pack_getWa.func_getWa(10) from dual;

MariaDB

DELIMITER /
CREATE OR REPLACE PACKAGE Pack_getWa AS
   PROCEDURE proc_getWa(v_MaxNum IN NUMBER);
   FUNCTION func_getWa(v_MaxNum NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY Pack_getWa AS

   PROCEDURE proc_getWa (
      v_MaxNum IN NUMBER) IS
      v_Result NUMBER;
   BEGIN
      v_Result := (v_MaxNum * (v_MaxNum + 1))/2;
      select 'Sum from 1 to '||v_MaxNum||' : '||v_Result as Wa;
   END proc_getWa;

   FUNCTION func_getWa (
      v_MaxNum NUMBER) RETURN NUMBER IS
      v_Result NUMBER;
   BEGIN
      v_Result := (v_MaxNum * (v_MaxNum + 1))/2;
      RETURN v_Result;
   END func_getWa;

END Pack_getWa;
/

Oracle実行計画

PACK_GETWA.FUNC_GETWA(10)
-------------------------
                       55
      COL1 PACK_GETWA.FUNC_GETWA(COL1)
---------- ---------------------------
         4                          10
         5                          15
        10                          55

MariaDB実行計画

+-----------------------+
| Wa                    |
+-----------------------+
| Sum from 1 to 10 : 55 |
+-----------------------+

+---------------------------+
| Pack_getWa.func_getWa(10) |
+---------------------------+
|                        55 |
+---------------------------+

+------+-----------------------------+
| col1 | Pack_getWa.func_getWa(col1) |
+------+-----------------------------+
|    4 |                          10 |
|    5 |                          15 |
|   10 |                          55 |
+------+-----------------------------+

検証結果

可否	項目	備考
◯	トランザクション	自動コミットを無効にすることで可能
–	バインド変数など	プログラム言語からの使用となるため一旦保留
◯	ストアド・プロシージャの作成、使用
◯	ファンクションの作成、クエリでの使用
◯	パッケージ・プロシージャの作成、使用
◯	データベース・トリガー可否	`insert` or `update` or `delete` のように指定はできないそのため、状態を表す `inserting`, `updating`, `deleting` は無い

基本的な機能は問題なく実行可能
細かな構文やOracle固有のパッケージなどは別途調査が必要

検証環境
CPU	8 core Intel (Core i7-4790K)
MEMORY	32GB
Storage	SSD 240GB(RAID0)
OS	CentOS Linux release 7.3.1611(Core)

MariaDB 10.3 PL/SQL動作検証

検証DB Version

事前準備

PL/SQL実行

PL/SQL #1

PL/SQL #2

PL/SQL #3

PL/SQL #4

PL/SQL #5

PL/SQL #6

トランザクション

検証用テーブル作成

クエリ実行

実行結果

ファンクション

トリガー

検証結果

MariaDB ColumnStore : NYC Taxi & Limousine Commission (TLC) データ可視化にチャレンジ