MariaDB 10.3のPL/SQLを試してみた
Oracle Databaseには、独自のプログラム言語としてPL/SQLが提供されています。
PL/SQLで作成されたプロシージャやファンクションは、データベース内で高速に動作するとともに、アプリケーション側から複雑なビジネスロジックを隠蔽でき、とても有用なものですが、一方Oracle Database内でしか使用できないものであったため、他データベースへの移行の際の障壁になる事もしばしばありました。
MariaDB社では、MariaDB 10.3(2018/03/30現在ではRC版)からPL/SQLの構文に対応しました。
SQL_MODE=ORACLE From MariaDB 10.3
今回は、簡単なPL/SQLを使用したプロシージャ作成し、動作検証をしてみたいと思います。
検証環境
今回は、Dockerを使用して環境を作成しました。
バージョンは以下の通りです。
| DB | バージョン |
|---|---|
| Oracle | 12.1.0.2 |
| MariaDB | 10.3.5 |
お試しの際には事前にDockerのインストールをお願いします。
また、PL/SQLスクリプト配置用のscriptsディレクトリを作成しています。
|
1 2 |
$ cd <WORK DIR> $ mkdir scripts |
MariaDB 10.3の準備
以下のコマンドでMariaDB 10.3.5のコンテナを起動します。
|
1 |
$ docker run --rm -d --name mariadb1035 -v $(pwd)/scripts:/scripts -e MYSQL_ROOT_PASSWORD=password mariadb:10.3.5 |
次にテスト接続を行います。
|
1 |
$ docker exec -it mariadb1035 mysql -uroot -ppassword -e status |
以上でMariaDBは利用する準備ができました。
Oracle 12.1の準備
Oracle社ではDockerはサポートしておりませんが、Docker ImageをBuildするためのスクリプトを提供しており、簡単にテスト環境を構築できます。
Oracleではデータベース構築済みのDocker Image自体は提供していないため、Oracle Databseのバイナリパッケージは入手する必要があります。
(12.1.0.2.0) – Standard Edition (SE2) の Linux x86-64 からFile 1、File 2のリンクから
以下のファイルをダウンロードしてください。
- linuxamd64_12102_database_se2_1of2.zip
- linuxamd64_12102_database_se2_2of2.zip
その後Oracleのgithubリポジトリをcloneします。
|
1 |
$ git clone https://github.com/oracle/docker-images.git |
詳しい使用方法は同梱されている各READMEをご確認頂くとして、今回はシングルインスタンスのStandard Edition 2のOracleデータベースを手早く作成します。
先程ダウンロードしたzipファイルを以下の場所に配置します。
|
1 2 |
$ mv linuxamd64_12102_database_se2_1of2.zip docker-images/OracleDatabase/SingleInstance/12.1.0.2 $ mv linuxamd64_12102_database_se2_2of2.zip docker-images/OracleDatabase/SingleInstance/12.1.0.2 |
スクリプトを使用して、Docker ImageをBuildします。
弊社環境ではdockerのボリュームサイズチェック(checkSpace.sh)に抵触し、かつ回避できなかったため、チェックを無効にしています。
|
1 2 |
$ cd docker-images/OracleDatabase/SingleInstance/ $ sed -i 's/\(REQUIRED_SPACE_GB=\).*$/\11/g' ./dockerfiles/12.1.0.2/checkSpace.sh |
build fail “checkSpace.sh: ERROR”
Dockerイメージが配置されるパーティション(/var/lib/docker/)に12GB以上の容量があるかご確認ください。
|
1 |
$ ./buildDockerImage.sh -v 12.1.0.2 -s |
作成されたImageを起動します
起動後、データベースの作成完了後、接続可能な状態となります。
|
1 2 |
$ docker run -d --name oracle_se2_12102 -v $(pwd):/scripts -e ORACLE_PWD=password oracle/database:12.1.0.2-se2 $ docker logs -f oracle_se2_12102 |
以下のコマンドで、データベースに接続できたら準備完了です。
|
1 |
$ docker exec -it oracle_se2_12102 sqlplus sys/password@orclpdb1 as sysdba |
今回は作業の簡略化のためにSYSユーザーを使用します。
データセットの準備
kaggleからデータセットを取得します。
データセットは、kaggleのページから検索してダウンロードするか、kaggle-api 経由でダウンロードが可能です。
作成したscriptsディレクトリ配下に展開します。
|
1 2 3 |
$ cd scripts $ kaggle datasets download -d residentmario/ramen-ratings -w $ unzip ramen-ratings.zip |
Importするための前処理を行います。
|
1 2 3 4 5 |
$ sed -i '1d' ramen-ratings.csv $ sed -i 's/Unrated/\\N/g' ramen-ratings.csv $ sed -i '/^"$/d' ramen-ratings.csv $ sed -i 's/,"$/,/g' ramen-ratings.csv $ sed -i 's/,$/,\\N/g' ramen-ratings.csv |
それぞれのDBに以下のようにIMPORTしてください。
MariaDB
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ docker exec -it mariadb1035 mysql -uroot -ppassword MariaDB> CREATE DATABASE test; MariaDB> CREATE TABLE "test"."ramen_ratings" ( "review_no" int(11) NOT NULL, "brand" varchar(50) DEFAULT NULL, "variety" varchar(100) DEFAULT NULL, "style" varchar(10) DEFAULT NULL, "country" varchar(50) DEFAULT NULL, "stars" varchar(50) DEFAULT NULL, "top_ten" varchar(50) DEFAULT NULL, PRIMARY KEY ("review_no") ); MariaDB> LOAD DATA INFILE '/scripts/ramen-ratings.csv' INTO TABLE test.ramen_ratings FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'; |
Oracle
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$ docker exec -it oracle_se2_12102 sqlplus sys/password@orclpdb1 as sysdba SQL> !cp /scripts/ramen-ratings.csv /home/oracle SQL> CREATE OR REPLACE DIRECTORY external_data AS '/home/oracle' ; SQL> GRANT READ,WRITE ON DIRECTORY EXTERNAL_DATA TO SYS; SQL> CREATE TABLE TEMP( review_no NUMBER ,brand VARCHAR2(50) ,variety VARCHAR2(100) ,style VARCHAR2(10) ,country VARCHAR2(20) ,stars VARCHAR2(20) ,top_ten VARCHAR2(20) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY external_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ) LOCATION ('ramen-ratings.csv') ); SQL> CREATE TABLE ramen_ratings AS SELECT * FROM TEMP; SQL> DROP TABLE TEMP; |
PL/SQLのテスト
現段階では、PL/SQLの全ての構文をサポートしているわけではありません。
サポートされる構文は、Supported Syntax in Oracle Modeにリストされています。
MariaDBでPL/SQLを実行するためには、set sql_mode=Oracle;を実行する必要がありますので、プロシージャ作成前には忘れずに実行してください。
それではいくつかプロシージャを作成し、実行してみます。
簡単なプロシージャの作成
最初にORACLEで、ラーメンの一覧を表示するプロシージャを作成してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> CREATE OR REPLACE PROCEDURE GET_RAMEN( in_flavor in varchar2 , in_rating in number) IS CURSOR c_ramen_rating$( var_flavor varchar2 ,var_rating number) IS SELECT brand ,variety ,style ,country ,stars FROM ramen_ratings WHERE variety like '%' || var_flavor || '%' and stars >= TO_NUMBER(var_rating) ; BEGIN FOR rec IN c_ramen_rating$(in_flavor, in_rating) LOOP dbms_output.put_line( 'Brand : ' || rec.brand ); dbms_output.put_line( 'Variety : ' || rec.variety); dbms_output.put_line( 'Style : ' || rec.style ); dbms_output.put_line( 'Country : ' || rec.country); dbms_output.put_line( 'Stars : ' || rec.stars ); END LOOP; END; / |
動作確認をします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> set serveroutput on SQL> call get_ramen('Soy Sauce',5); Brand : Kiki Noodle Variety : Scallion Oil & Soy Sauce Noodle Style : Pack Country : Taiwan Stars : 5 Brand : The Kitchen Food Variety : Instant Kampua Dark Soy Sauce Style : Pack Country : Sarawak Stars : 5 Brand : Nissin Variety : Raoh Backfat Rich Soy Sauce Flavor Style : Bowl Country : Japan Stars : 5 Call completed. |
正常に、評価が5以上の醤油ラーメンの一覧が表示されました。
それでは、MariaDBで同じプロシージャを作成し、試してみます。
残念ながら、DBMS_OUTPUTのようなパッケージはMariaDBには存在しないので、PUT_LINEプロシージャを作成してみます。
まずは、PACKAGEから作成しましょう。
|
1 2 3 4 5 6 7 8 9 |
MariaDB> DELIMITER / MariaDB> CREATE OR REPLACE PPACKAGE DBMS_OUTPUT AS PROCEDURE PUT_LINE(item in varchar2); END; / MariaDB> DELIMITER ; |
PACKAGEができたので確認します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
MariaDB [test]> show create package dbms_output\G *************************** 1. row *************************** Package: dbms_output 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 Create Package: CREATE DEFINER="root"@"localhost" PACKAGE "dbms_output" AS PROCEDURE PUT_LINE(item in varchar2); END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci |
次にPACKAGE BODYを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
MariaDB> DELIMITER / MariaDB> 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; / MariaDB> DELIMITER ; |
同じように確認しましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
MariaDB> show create package body DBMS_OUTPUT\G *************************** 1. row *************************** Package body: DBMS_OUTPUT 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 Create Package Body: CREATE DEFINER="root"@"localhost" PACKAGE BODY "DBMS_OUTPUT" AS PROCEDURE PUT_LINE(item in varchar2) IS BEGIN select item as 'Output'; EXCEPTION WHEN OTHERS THEN NULL; END; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci |
それでは実行してみます。
|
1 2 3 4 5 6 |
MariaDB [test]> call dbms_output.put_line('Enjoy PL/SQL!'); +---------------+ | Output | +---------------+ | Enjoy PL/SQL! | +---------------+ |
簡易的なものですがDBMS_OUTPUT.PUT_LINEができました。
それでは、やっと目的のプロシージャを作成してみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
MariaDB> DELIMITER / MariaDB> CREATE OR REPLACE PROCEDURE GET_RAMEN( in_flavor in varchar2 , in_rating in number) IS CURSOR c_ramen_rating$( var_flavor varchar2 ,var_rating number) IS SELECT brand ,variety ,style ,country ,stars FROM ramen_ratings WHERE variety like '%' || var_flavor || '%' and stars >= CAST(var_rating AS DECIMAL) ; BEGIN FOR rec IN c_ramen_rating$(in_flavor, in_rating) LOOP dbms_output.put_line( '\n'|| 'Brand : ' || rec.brand || '\n'|| 'Variety : ' || rec.variety || '\n'|| 'Style : ' || rec.style || '\n'|| 'Country : ' || rec.country || '\n'|| 'Stars : ' || rec.stars ); END LOOP; END; / MariaDB> DELIMITER ; |
上記には、基本的なカーソルの作成、FOR LOOP文が含まれる他、変数型にORACLEのNUMBER,VARCHAR2を使用しています。
ただし、TO_NUMBERはMariaDBに存在しないためCASTを使用しています。
また、CAST中ではNUMBER型は使用できなかったようですので、DECIMALを使用しています。
そして、これは必須ではありませんが、dbms_outputの出力結果を見やすくなるように変更しています。
それでは実行してみます。
|
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 32 33 34 35 36 |
MariaDB [test]> call get_ramen('Soy Sauce',5); +---------------------------------------------+ | Output | +---------------------------------------------+ | Brand : Nissin Variety : Raoh Backfat Rich Soy Sauce Flavor Style : Bowl Country : Japan Stars : 5 | +---------------------------------------------+ 1 row in set (0.004 sec) +---------------------------------------------+ | Output | +---------------------------------------------+ | Brand : The Kitchen Food Variety : Instant Kampua Dark Soy Sauce Style : Pack Country : Sarawak Stars : 5 | +---------------------------------------------+ 1 row in set (0.005 sec) +---------------------------------------------+ | Output | +---------------------------------------------+ | Brand : Kiki Noodle Variety : Scallion Oil & Soy Sauce Noodle Style : Pack Country : Taiwan Stars : 5 | +---------------------------------------------+ 1 row in set (0.005 sec) |
基本的なプロシージャなら問題なく実行できるようです。
(DECLARE..)BEGIN ENDのみの構文もサポートされているので、一時的にPL/SQLを使って何かをしたい場合に役立ちます。
|
1 2 3 4 5 6 7 8 9 |
MariaDB> CREATE TABLE dummy(i INT PRIMARY KEY,v VARCHAR(100)); MariaDB> DELIMITER / MariaDB> BEGIN FOR i IN 1..1000 LOOP insert into dummy values (i ,'test'|| i); END LOOP; COMMIT; END; MariaDB> DELIMITER ; |
ダミーデータが簡単に作成できました。
|
1 2 3 4 5 6 7 |
MariaDB> SELECT count(i) FROM dummy; +----------+ | count(i) | +----------+ | 1000 | +----------+ 1 row in set (0.001 sec) |
所感
現段階でも、様々なPL/SQLの構文がサポートされており、ORACLEフレーバーなプロシージャやファンクションの開発が可能となっていました。
中でもBEGIN..END構文は、既存のMariaDBのファンクションを使用してよりカジュアルに手続き処理を行う事ができるようになると思います。
一方で、REF_CURSOR等対応していない構文もありますし、ORACLEに事前定義されている強力なパッケージ群はMariaDBでは使用できないというような事から、ORACLEからそのままソースコードを移行して動作するレベルではありません。
まだ本機能はベータ版ですので、今後更に機能が拡充されることを期待しましょう。
