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固有のパッケージなどは別途調査が必要