Since MariaDB Corporation started with the project to bring the PL/SQL and other Oracle-based functionalities to MariaDB Sever, I’ve got very curious and started having a looking around to see how was that going. Most of the time, what calls the attention of database and system administrators, or Database Operations guys is the new things that pop up times to times and makes it worst checking and testing. At this time, it was not any different, as I worked with PL/SQL before, I felt that it is a giant step for MariaDB users to put together SQL script in a more structured way. I am not saying that the standards supported natively by MariaDB is not good, I am just saying that it could be good to innovate and get new procedural scripts writing on SQL to use the PL/SQL features.
The first step is really to get a server of your preference running MariaDB 10.3 ++ to get the features announced here https://mariadb.com/kb/en/library/mariadb-1030-release-notes/. For the time I’m writing this blog post, 10.3.1 is the latest release of the MariaDB 10.3 series, which has the following changelog, https://mariadb.com/kb/en/library/mariadb-1031-release-notes/. Regardless of the InnoDB 5.7.19 being added to MariaDB removing the XtraDB Storage Engine e many other new things, this blog post will be dedicated mainly to write code in PL/SQL, observing all the features that were released until now on MariaDB 10.3.1.
After getting the MariaDB Server 10.3.1++ running on your system, I’m running that on a CentOS 7.3, just for the records, you need to add the initial configurations to make the MariaDB Server (you must pay attention to the version, it’s important) to understand the PL/SQL and that’s done configuring the global variable @@sql_mode with Oracle as its value, as you can see below:
#: /etc/my.cnf.d/server.cnf | grep sql_mode [mysqld] sql_mode=oracle |
You may get in doubt where you place the sql_mode configs, so you can follow what is being shown above. Edit the file /etc/my.cnf.d/server.cnf and add the sql_mode=oracle under the [mysqld] section, it’s gonna work for you as it worked for me. An alternative you have is to set the @@sql_mode value globally on the runtime, so, you can have it until the next start.
MariaDB [(none)]> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SET GLOBAL sql_mode=ORACLE; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> SELECT @@sql_mode\G *************************** 1. row *************************** @@sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER 1 row in set (0.000 sec) |
By the way, it’s important to add the configs to yours MariaDB Server’s configuration file to avoid for PL/SQL programs to stop working after a restart. You need to be aware that, when you enable the @@sql_mode as ORACLE to be able to use the PL/SQL on MariaDB 10.3, the native MySQL’s syntax for creating routines which adhere is fairly close to the SQL:2003 standard won’t be available anymore – any attempts to develop a native SQL procedure is going to fail with a problem on recognizing syntax.
MariaDB [mydb]> delimiter / MariaDB [mydb]> create procedure p1 (a int) -> begin -> declare var int default 0; -> while a > var do -> insert into t1 set i=var; -> set var = var +1; -> end while; -> end; -> / 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 'begin declare var int default 0; while a > var do insert into t1' at line 3 Query OK, 0 rows affected (0.000 sec) |
As we are running MariaDB 10.3 with @@sql_mode as oracle, I’m going to create a table and will create in a sequence a simple PL/SQL procedure that will prepares (parses) and immediately executes the defined dynamic SQL statement.
The created table:
MariaDB [mydb]> show create table t1/ +-------+-------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------+ | t1 | CREATE TABLE "t1" ( "i" int(11) NOT NULL, PRIMARY KEY ("i") ) | +-------+-------------------------------------------------------------------+ 1 row in set (0.000 sec) |
The PL/SQL simple procedure with a static value to be inserted on table mydb.t1:
MariaDB [mydb]> delimiter / MariaDB [mydb]> create or replace procedure p1 -> as -> a mydb.t1.i%TYPE := 1; -> begin -> execute immediate 'insert into mydb.t1 (i) values (:a)' USING a; -> end; -> / Query OK, 0 rows affected (0.005 sec) |
Now we can call the procedure p1:
MariaDB [mydb]> call mydb.p1/ Query OK, 1 row affected (0.002 sec) MariaDB [mydb]> select * from t1/ +---+ | i | +---+ | 1 | +---+ 1 row in set (0.000 sec) |
But most of the time you want to pass parameters with the value to be inserted on a table and even, being worked by the procedure and then, inserted on tables. For the sake of simplicity, I’m going to pass one parameter to the procedure and then, insert it into the table t1.
Creating the procedure p2:
MariaDB [mydb]> delimiter / MariaDB [mydb]> create or replace procedure p2 (i int) -> as -> a mydb.t1.i%TYPE := i; -> begin -> execute immediate 'insert into mydb.t1 (i) values (:a)' USING a; -> end; -> / Query OK, 0 rows affected (0.002 sec) |
Calling the procedure p2:
MariaDB [mydb]> call p2(100)/ Query OK, 1 row affected (0.003 sec) MariaDB [mydb]> select * from t1/ +-----+ | i | +-----+ | 1 | | 100 | +-----+ 2 rows in set (0.000 sec) |
Conclusion
This small article shows you how to configure MariaDB 10.3, already available for download at MariaDB website, to be configured with @@sql_mode as oracle as it permits you to create procedures. Functions, triggers, packages, etc using PL/SQL, the Oracle’s procedural language. On a next blog, I’m going to bring up more sophisticated constructions for the support MariaDB has to PL/SQL.
