Autonomous Database で SQLマクロ機能を試す

Oracle Database 上で PL/SQL による複雑なロジックを記述する場合にはファンクションやプロシージャを作成します。ファンクションやプロシージャはそれぞれ CREATE FUNCTION文やCREATE PROCEDURE 文を使って作成します。複数の関連するファンクションをまとめてグループ化する場合にはパッケージ・オブジェクトを作成することもあります。ファンクションやプロシージャは大変便利ですが、呼び出しにはある程度の負荷が発生します。このため多くの回数実行されるとパフォーマンス上のネックになる可能性があります。Oracle Database 19c （19.7 以降）の新機能である「SQL マクロ」は呼び出しのオーバーヘッドを削減しつつ、ファンクションの機能を実現することができます。
SQL マクロは C 言語のプリプロセッサにおける #define 文や C++ 言語における inline 関数を SQL に持ち込むものと考えても良いでしょう。基本的には他の RDBMS で利用できるシンプルな関数の移植等を想定しています。

SQLマクロの基本

CREATE FUNCTION 文の RETURN 句のデータ型の後に SQL_MACRO 句を記述します。文字列を返すだけなので CREATE FUNCTION 文の実行時点ではテーブルの存在等はチェックされません。

SQL> CREATE FUNCTION mbudget
  2  RETURN VARCHAR2 SQL_MACRO
  3  IS
  4  BEGIN
  5    RETURN q'(SELECT deptno, sum(sal) budget
  6     FROM emp GROUP BY deptno)';
  7  END;
  8  /

ファンクションが作成されました。

テーブル型

SQL_MACRO 句を SQL_MACRO(TABLE) または SQL_MACRO と記述するとテーブル型（複数の値を返す）ファンクションが作成されます。SELECT 文自体を返します。オンプレミスの Oracle Database 19c の SQL マクロはこのタイプのみサポートされます。

SQL> CREATE OR REPLACE FUNCTION table_list        
RETURN VARCHAR2 SQL_MACRO
IS 
BEGIN 
  RETURN q'(SELECT object_name FROM dba_objects WHERE object_type='TABLE')';
END;
/

テーブル型の SQL マクロは SELECT 文の FROM 句に記述して実行します。実行計画を確認すると SQL マクロの実行は出力されません。

SQL> SELECT * FROM table_list() WHERE object_name LIKE 'OGCS%';

OBJECT_NAME
--------------------------------------------------------------------------------
OGCS_GRAPHS$
OGCS_GRAPH_TAGS$
…

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  3jxjp7fxkss79, child number 1
-------------------------------------
SELECT * FROM table_list() WHERE object_name LIKE 'OGCS%'

Plan hash value: 2133568685

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |       |       |    65 (100)|          |
|*  1 |  VIEW                                    | DBA_OBJECTS       |    24 |  1896 |    65   (0)| 00:00:01 |
…

スカラー型

SQL_MACRO 句を SQL_MACRO(SCALAR) と記述するとスカラー型の SQL マクロを作成できます。スカラー型の SQL マクロを作成する場合、実行される関数を示す文字列を返します。オンプレミスの Oracle Database 19c (19.12) では以下のようにエラーが発生します。

on-premise.Oracle.Database.19c

SQL> CREATE FUNCTION clip(lo VARCHAR2, x VARCHAR2, hi VARCHAR2)
  2     RETURN VARCHAR2 SQL_MACRO(SCALAR)
  3  IS
  4     BEGIN
  5       RETURN 'least(greatest(x, lo), hi)';
  6  END;
  7  /

警告: ファンクションが作成されましたが、コンパイル・エラーがあります。

SQL> SHOW ERROR
FUNCTION CLIPのエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/29     PLS-00103: 記号"("が見つかりました。 次のうちの1つが入るとき:
         ; is default authid as cluster order using external
         deterministic parallel_enable pipelined aggregate
         result_cache accessible rewrite

Oracle Cloud 上の Autonomous Database のバージョンは Oracle Database 19c ですが、スカラー型の SQL マクロを記述することができます。実行方法は通常のストアド・ファンクションと同じです。以下は Autonomous Database 上での実行例です。

Autonomous.Oracle.Database.19c

SQL> SELECT banner_full FROM v$version;

BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.1.0

SQL> CREATE FUNCTION clip(lo VARCHAR2, x VARCHAR2, hi VARCHAR2)
  2     RETURN VARCHAR2 SQL_MACRO(SCALAR)
  3  IS
  4     BEGIN
  5       RETURN 'least(greatest(x, lo), hi)';
  6  END;
  7  /

ファンクションが作成されました。

SQL> SELECT clip('data1', 'data2', 'data3') FROM DUAL;

CLIP(
-----
data2