Combining the Power of SQL and Procedural Statements with MariaDB’s Oracle Compatibility Mode

Back in MariaDB Server 10.3, we added a new SQL mode (SQL_MODE=ORACLE) to provide compatibility with a subset of Oracle PL/SQL, a procedural extension to the Structured Query Language (SQL). With the recent release of MariaDB Community Server 10.6, we’ve continued on our journey to expand MariaDB’s PL/SQL compatibility by adding several functions such as ROWNUM(), ADD_MONTHS() and TO_CHAR(). This is so cool for a number of reasons but to start, MariaDB remains the only database to implement open source Oracle compatibility so this functionality is available to everyone. To truly understand what that means, it’s important for us to take a brief step back so that you know why we started down this path to begin with.

The Power of Procedural and Declarative Programming

By combining the data manipulation power of the SQL language with procedural capabilities, developers have a plethora of exciting, new opportunities. Using PL/SQL, developers have the ability to execute SQL statements that focus on the declarative approach of “what to do” while also being provided the ability to control the flow of operations, or “how to do” it.

Wait, what? OK, consider the following traditional SQL statement.

SELECT id, name, has_kids FROM friends;

Simple, elegant and effective. SQL is a beautiful language, but it has its limitations. For instance, what if you wanted to loop through the results returned from the SELECT statement execution? And then, while parsing through the results, check each result using a conditional statement (e.g. if has_kids is true) so that you could perform specific operations based on those conditions?

Quick note: This is purely hypothetical. I’m sure that if you have friends with kids and friends without kids you treat them all the same. But, for the sake of this example, let’s say you don’t. OK? OK, cool.

But with all those iterations and conditions happening it’s possible that you’ll run into an error. Wouldn’t it be nice if you could handle errors (i.e. exceptions) directly on the database side, without having to first return the original results back to the application code to be processed? Well, that’s just too bad, because you’re using plain ol’ SQL, and none of those things are possible.

Fear not! Instead, come with me to the wonderful world of PL/SQL!

Through the use of something known as a PL/SQL Block, you can combine the SQL you know and love with the procedural capabilities that will allow you to control the flow of operations using features such as the ability to declare variables, handle conditions, loop through data, manage exceptions, and so much more.

PL/SQL blocks are handled within MariaDB Server, where the SQL and procedural statements are parsed and executed accordingly.

Pretty cool, right? It gets so much better! But back to our previous example. You can use a single PL/SQL block to accomplish everything I indicated before.

For instance:

DELCARE 
	CURSOR c IS SELECT id, name, has_kids FROM friends;
BEGIN 
	FOR friend_row IN c LOOP
		IF friend_row.has_kids == true THEN
			/* Do something */
		ELSE
			/* Do something else */
		END IF;
	END LOOP;
EXCEPTION
	WHEN no_data_found THEN
		/* Handle no data found */
	WHEN others THEN
		/* Handle all other kinds of exceptions */
END;

Learn More

Powerful stuff, right? Well, this blog post is really just the tip of the iceberg. A little something to whet your appetite, if you will. But if you’d like to know more, I highly recommend watching a webinar we recently presented, MariaDB and PL/SQL: Combining the Power of SQL and Procedural Statements. In it, we dive into much more detail as to why we’ve made it our mission to add and expand PL/SQL support and we include a variety of examples, and a live coding demonstration, that give you a first hand look at how you can use PL/SQL within MariaDB! Happy coding, friends!