SQL_MACRO句

SQL_MACRO句では、ファンクションにSQLマクロとしてのマークを付けて、スカラー式または表式のどちらかとして使用できるようにします。

TABLEマクロは、SQL_MACROの注釈が付いている、TABLEタイプとして定義されたファンクションです。

SCALARマクロは、SQL_MACROの注釈が付いている、SCALARタイプとして定義されたファンクションです。

ビューで参照されるSQLマクロは、常にビューの所有者の権限で実行されます。

AUTHIDプロパティは指定できません。SQLマクロが呼び出されると、ファンクション本体が定義者権限で実行されて返されるテキストが構築されます。結果式は実行者権限で評価されます。SQLマクロ所有者は、起動元のファンクションに継承権限を付与する必要があります。

マクロ注釈付きファンクションをPL/SQLで使用する場合は、通常のファンクション戻り文字またはCLOB型のようにマクロ拡張なしで動作します。

多くのSCALARマクロは、SQL文から直接呼び出せる標準のPL/SQLファンクションとして記述できます。PL/SQLファンクションは、SQLトランスパイラによって、意味的に同等のSQL式に自動的に変換されます。この変換されたSQL式は、元のPL/SQLファンクションへのコールを置換して、実行中に使用されます。

トランスパイルによって、SQLランタイムとPL/SQLランタイムを切り替える必要がなくなり、パフォーマンスが向上します。可能な場合は、SQL文から呼び出されるPL/SQLファンクションに対して、トランスパイルが自動的に実行されます(この機能が明示的に無効になっていない場合)。

SQLトランスパイラの詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。

SQL_MACROの注釈は、次のSQL文で使用できます。

SQLマクロの使用上の制限:
  • TABLEマクロは、問合せ表式のFROM句でのみ使用できます。
  • SCALARマクロは、問合せ表式のFROM句では使用できません。これは、選択リスト、WHERE句およびORDER BY句など、PL/SQLファンクションを使用可能な任意の場所で使用できます。
  • Scalarマクロには、表引数を指定できません。
  • SQLマクロは、仮想列式、ファンクション索引、エディショニング・ビューまたはマテリアライズド・ビューでは使用できません。
  • タイプ・メソッドには、SQL_MACROで注釈を付けることはできません。

ここでのトピック

構文

sql_macro_clause ::=

sql_macro_clause.epsの説明が続きます
図sql_macro_clause.epsの説明

セマンティクス

sql_macro_clause

sql_macro_clauseは、ファンクション内で1回のみ使用できます。SQLマクロ・ファンクションを作成するには、ファンクション定義にsql_macro_clauseを含めます。SQLマクロ・ファンクションを定義前に宣言する場合は、ファンクションの宣言でsql_macro_clauseを指定する必要があります。

SCALARまたはTABLEを指定していない場合、TABLEがデフォルトになります。

SCALAR

マクロ・ファンクションをスカラー式で使用可能にする場合は、SCALARを指定します。

TABLE (デフォルト)

マクロ・ファンクションを表式で使用可能にする場合は、TABLEを指定します。

sql_macro_clauseの制限

SQL_MACROの注釈は、RESULT_CACHE、PARALLEL_ENABLEおよびPIPELINEDには使用できません。DETERMINISTICプロパティは指定できませんが、SQLマクロは常に暗黙で決定的になります。

SQLマクロ・ファンクションには、VARCHAR2、CHARまたはCLOBの戻り型が必要です。

例

例14-36 Emp_doc: Scalarマクロを使用した列のJSONまたはXML文書への変換

emp_doc SQLマクロは、従業員フィールドを文書文字列(JSONまたはXML)に変換します。
The macro is implemented as a tree of nested macros with the following call graph structure.
emp_doc()
   ==> emp_json()
          ==> name_string()
          ==> email_string()
                ==> name_string()
          ==> date_string()
   ==> emp_xml 
          ==> name_string()
          ==> email_string()
                ==> name_string()
          ==> date_string()
date_stringファンクションは、日付を4桁の年、月(01-12)および日(1-31)としてフォーマットした文字列に変換します。
CREATE FUNCTION date_string(dat DATE) 
                    RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
             TO_CHAR(dat, 'YYYY-MM-DD')
          }';
END;
/
name_stringファンクションは、first_nameおよびlast_nameの各単語の最初の文字を大文字、他の文字をすべて小文字に設定します。フォーマット済の名前とフォーマット済の姓をスペースで連結して、結果の文字列の先頭と末尾のスペースを削除します。
CREATE FUNCTION name_string(first_name VARCHAR2,
                                 last_name VARCHAR2)
                     RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
          TRIM(INITCAP(first_name) || ' ' || INITCAP(last_name))
          }';
END;
/
email_stringは、name_stringファンクションを使用してfirst_nameとlast_nameでEメール・アドレスを設定し、すべてのスペースをピリオドに置き換え、デフォルト・ドメイン名のexample.comを追加します。
CREATE FUNCTION email_string(first_name VARCHAR2,
                                  last_name VARCHAR2,
                                  host_name VARCHAR2 DEFAULT 'example.com')
                    RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
          REPLACE(LOWER(name_string(first_name, last_name)),' ','.') || '@' || host_name
          }';
END;
/
emp_json SQLマクロは、JSONドキュメント文字列を返します。
CREATE FUNCTION emp_json(first_name VARCHAR2 DEFAULT NULL,
                              last_name VARCHAR2 DEFAULT NULL,
                              hire_date DATE DEFAULT NULL,
                              phone_num VARCHAR2 DEFAULT NULL)
                    RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
          JSON_OBJECT(
             'name'      : name_string(first_name, last_name),
             'email'     : email_string(first_name, last_name),
             'phone'     : phone_num,
             'hire_date' : date_string(hire_date)
             ABSENT ON NULL)
          }';
END;
/
emp_xml SQLマクロは、XML文書の文字列を返します。
CREATE FUNCTION emp_xml(first_name VARCHAR2 DEFAULT NULL,
                             last_name VARCHAR2 DEFAULT NULL,
                             hire_date DATE DEFAULT NULL,
                             phone_num VARCHAR2 DEFAULT NULL)
                    RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
       XMLELEMENT("xml",
                  CASE WHEN first_name || last_name IS NOT NULL THEN
                     XMLELEMENT("name", name_string(first_name, last_name))
                  END,
                  CASE WHEN first_name || last_name IS NOT NULL THEN
                     XMLELEMENT("email", email_string(first_name, last_name))
                  END,
                  CASE WHEN hire_date IS NOT NULL THEN
                     XMLELEMENT("hire_date", date_string(hire_date))
                  END,
                  CASE WHEN phone_num IS NOT NULL THEN
                     XMLELEMENT("phone", phone_num)
                  END)
           }';
END;
/
emp_doc SQLマクロは、従業員フィールドをJSON (デフォルト)またはXML文書の文字列に返します。
CREATE FUNCTION emp_doc(first_name VARCHAR2 DEFAULT NULL,
                             last_name VARCHAR2 DEFAULT NULL,
                             hire_date DATE DEFAULT NULL,
                             phone_num VARCHAR2 DEFAULT NULL,
                             doc_type VARCHAR2 DEFAULT 'json')
                    RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
     DECODE(LOWER(doc_type),
            'json', emp_json(first_name, last_name, hire_date, phone_num),
            'xml', emp_xml(first_name, last_name, hire_date, phone_num))
         }';
END;
/
この問合せは、部門30のJSONドキュメント文字列にあるすべての従業員をリストするためのスカラー式で使用されるemp_doc SQLマクロを示しています。 
SELECT department_id,
            emp_doc(first_name => e.first_name, hire_date => e.hire_date) doc
FROM hr.employees e
WHERE department_id = 30
ORDER BY last_name;
結果:

     30 {"name":"Shelli","email":"shelli@example.com","hire_date":"2015-12-24"}
     30 {"name":"Karen","email":"karen@example.com","hire_date":"2017-08-10"}
     30 {"name":"Guy","email":"guy@example.com","hire_date":"2016-11-15"}
     30 {"name":"Alexander","email":"alexander@example.com","hire_date":"2013-05-18"} 
     30 {"name":"Den","email":"den@example.com","hire_date":"2012-12-07"}
     30 {"name":"Sigal","email":"sigal@example.com","hire_date":"2015-07-24"}
この問合せは、XML文書文字列にあるすべての従業員をリストするためのスカラー式で使用されるemp_doc SQLマクロを示しています。
SELECT deptno,
            emp_doc(first_name => ename, hire_date => hiredate, doc_type => 'xml') doc
FROM scott.emp
ORDER BY ename;
結果:
20 <xml><name>Adams</name><email>adams@example.com</email><hire_date>1987-05-23</hire_date></xml>
30 <xml><name>Allen</name><email>allen@example.com</email><hire_date>1981-02-20</hire_date></xml>
30 <xml><name>Blake</name><email>blake@example.com</email><hire_date>1981-05-01</hire_date></xml> 
10 <xml><name>Clark</name><email>clark@example.com</email><hire_date>1981-06-09</hire_date></xml> 
20 <xml><name>Ford</name><email>ford@example.com</email><hire_date>1981-12-03</hire_date></xml> 
...
30 <xml><name>Ward</name><email>ward@example.com</email><hire_date>1981-02-22</hire_date></xml>

VARIABLE surname VARCHAR2(100)
EXEC :surname := 'ellison'
WITH e AS (SELECT emp.*, :surname lname FROM emp WHERE deptno IN (10,20))
SELECT deptno,
       emp_doc(first_name => ename, last_name => lname, hire_date => hiredate) doc
FROM e
ORDER BY ename;
結果:
10 {"name":"Clark Ellison","email":"clark.ellison@example.com","hire_date":"1981-06-09"} 
20 {"name":"Ford Ellison","email":"ford.ellison@example.com","hire_date":"1981-12-03"}
20 {"name":"Jones Ellison","email":"jones.ellison@example.com","hire_date":"1981-04-02"}
10 {"name":"King Ellison","email":"king.ellison@example.com","hire_date":"1981-11-17"}
10 {"name":"Miller Ellison","email":"miller.ellison@example.com","hire_date":"1982-01-23"}
20 {"name":"Scott Ellison","email":"scott.ellison@example.com","hire_date":"1987-04-19"}
20 {"name":"Smith Ellison","email":"smith.ellison@example.com","hire_date":"1980-12-17"}

例14-37 Env:スカラー式でのScalarマクロの使用

env SQLマクロは、現在のセッションについて説明するコンテキスト・ネームスペースUSERENVに関連付けられたパラメータの値にラッパーを提供します。 

CREATE PACKAGE env AS
   FUNCTION current_user RETURN VARCHAR2 SQL_MACRO(SCALAR);
   FUNCTION current_edition_name RETURN VARCHAR2 SQL_MACRO(SCALAR);
   FUNCTION module RETURN VARCHAR2 SQL_MACRO(SCALAR);
   FUNCTION action RETURN VARCHAR2 SQL_MACRO(SCALAR);
END;
/
CREATE PACKAGE BODY env AS
   FUNCTION current_user RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
     BEGIN
        RETURN q'{SYS_CONTEXT('userenv','SESSION_USER')}';
     END;
   FUNCTION current_edition_name RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
     BEGIN
        RETURN q'{SYS_CONTEXT('userenv','CURRENT_EDITION_NAME')}';
     END;
   FUNCTION module RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
     BEGIN
        RETURN q'{SYS_CONTEXT('userenv','MODULE')}';
     END;
   FUNCTION action RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
     BEGIN
        RETURN q'{SYS_CONTEXT('userenv','ACTION')}';
     END;
END;
/

現在のユーザー情報を選択します。

SELECT env.current_user, env.module, env.action FROM DUAL;
結果:
SCOTT   SQL*PLUS

例14-38 Budget :表式でのTableマクロの使用

この例は、表式で使用するbudgetというSQLマクロを示しています。このマクロは、特定の役職の従業員に対する部署ごとの合計給与を返します。 

CREATE FUNCTION budget(job VARCHAR2) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
   RETURN q'{SELECT deptno, SUM(sal) budget 
             FROM scott.emp
             WHERE job = budget.job
             GROUP BY deptno}';
END;

この問合せは、表式で使用されるSQLマクロbudgetを表示します。 

SELECT * FROM budget('MANAGER');

結果:

    
DEPTNO     BUDGET 
---------- ----------   
        20       2975  
        30       2850 
        10       2450

例14-39 Take: 多相ビューでのTableマクロの使用

この例では、表マクロを作成します。このマクロは、表tから最初のn行を返します。
CREATE FUNCTION take (n NUMBER, t DBMS_TF.table_t) 
                      RETURN VARCHAR2 SQL_MACRO IS
BEGIN
   RETURN 'SELECT * FROM t FETCH FIRST take.n ROWS ONLY';
END;
/
この問合せでは、表部門の最初の2行が戻されます。
SELECT * FROM take(2, dept);
結果:
    DEPTNO DNAME          LOC   
---------- -------------- -------------   
        10 ACCOUNTING     NEW YORK       
        20 RESEARCH       DALLAS   
VAR row_count NUMBER
EXEC :row_count := 5

WITH t AS (SELECT * FROM emp NATURAL JOIN dept ORDER BY ename)
SELECT ename, dname FROM take(:row_count, t);
結果:
ENAME      DNAME  
---------- --------------    
ADAMS      RESEARCH     
ALLEN      SALES     
BLAKE      SALES   
CLARK      ACCOUNTING   
FORD       RESEARCH

例14-40 Range :表式でのTableマクロの使用

この例では、範囲[first, stop]の行の等差数列を生成するSQLマクロを作成します。最初の行は値firstから始まり、それ以降の各行の値は、直前の行の値よりもstepだけ大きな値になります。

次の引数の組合せにより、生成される行数がゼロになります。
  • step < 0およびfirst < stop
  • step = 0
  • step > 0およびfirst > stop
/*  PACKAGE NAME: GEN
 *  SQL TABLE MACROS:
 *     range(stop  : number to generate starting from zero)
 *     range(first : starting number of the sequence (default=0), 
 *           stop  : generate numbers up to, but not including this number, 
 *           step  : difference between each number in the sequence (default=1) )
*/
CREATE PACKAGE gen IS 
   FUNCTION range(stop NUMBER)
            RETURN VARCHAR2 SQL_MACRO(TABLE);

   FUNCTION range(first NUMBER DEFAULT 0, stop NUMBER, step NUMBER DEFAULT 1)
            RETURN VARCHAR2 SQL_MACRO(TABLE);

   FUNCTION tab(tab TABLE, replication_factor NATURAL)
            RETURN TABLE PIPELINED ROW POLYMORPHIC USING gen;

   FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, replication_factor NATURAL)
            RETURN DBMS_TF.DESCRIBE_T;

   PROCEDURE fetch_rows(replication_factor NATURALN);
END gen;
/
CREATE PACKAGE BODY gen IS 
   FUNCTION range(stop NUMBER)
            RETURN VARCHAR2 SQL_MACRO(TABLE) IS
   BEGIN 
      RETURN q'{SELECT ROWNUM-1 n FROM gen.tab(DUAL, stop)}'; 
   END;

   FUNCTION range(first NUMBER DEFAULT 0, stop NUMBER, step NUMBER DEFAULT 1)
           RETURN VARCHAR2 SQL_MACRO(TABLE) IS
   BEGIN
      RETURN q'{
             SELECT first+n*step n FROM gen.range(ROUND((stop-first)/NULLIF(step,0)))
             }';
   END;
 
   FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, replication_factor NATURAL) 
            RETURN DBMS_TF.DESCRIBE_T AS
   BEGIN 
      RETURN DBMS_TF.DESCRIBE_T(row_replication => true);
   END;

  PROCEDURE fetch_rows(replication_factor NATURALN) as
  BEGIN 
    DBMS_TF.ROW_REPLICATION(replication_factor); 
  END;
END gen;
/

gen.get_range SQLマクロは、表式で使用します。

この問合せは、ゼロから始まる連続する5行を返します。 
SELECT * FROM gen.range(5);
結果:
         0 
         1 
         2 
         3 
         4
この問合せは、5から始まり、10未満までのシーケンスを返します。
SELECT * FROM gen.range(5, 10);
結果:
         5  
         6  
         7  
         8   
         9
この問合せは、ゼロから始まり、0.1ずつ増分して1で停止するシーケンスを返します。
SELECT * FROM gen.range(0, 1, step=>0.1);
結果:
 
         0  
        .1  
        .2  
        .3  
        .4  
        .5  
        .6  
        .7   
        .8   
        .9
この問合せは、5から始まり、2ずつ減少して-6で停止(これを含まない)するシーケンスを返します。
SELECT * FROM gen.range(+5,-6,-2);
結果:
      5 
      3   
      1   
     -1 
     -3 
     -5

関連トピック