SQLファンクションで使用されるRETURNING句

18.2 SQLファンクションで使用されるRETURNING句

SQLファンクションjson_array、json_arrayagg、json_mergepatch、json_object、json_objectagg、json_query、json_serialize、json_transformおよびjson_valueは、オプションでRETURNING句を受け入れます。この句は、ファンクションから返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING句なし)について説明します。

json_valueの場合は、RETURNING句でSQLデータ型BINARY_DOUBLE、BINARY_FLOAT、BOOLEAN、CHAR、CLOB、DATE (オプションのキーワードPRESERVE TIMEまたはTRUNCATE TIMEを指定)、DOUBLE PRECISION、FLOAT、INTEGER、NUMBER、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND、NCHAR、NCLOB、NVARCHAR2、RAW^脚注1、REAL、SDO_GEOMETRY、TIMESTAMP、TIMESTAMP WITH TIME ZONEおよびVARCHAR2のいずれかを使用できます。ユーザー定義のオブジェクト型またはコレクション型を使用することもできます。

(JSONのブール値が対象である場合の戻り型の詳細は、SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用を参照してください。)

ノート:

Oracle SQLデータ型DATEのインスタンスには、時間コンポーネントが含まれます。JSONデータでは、ISO 8601日時値を表す文字列を使用できます。つまり、時間コンポーネントを使用できます。

デフォルトで、RETURNING DATEを含むjson_valueは、時間コンポーネントがゼロ(ゼロ時間、分および秒)のSQL DATE値を返します。デフォルトでは、問い合せたJSONスカラー値の時間コンポーネントは、返されたSQL DATEインスタンスで切り捨てられます。ただし、時間の切捨てが行われる前に、ISO 8601の日時文字列で表される値にタイムゾーン・コンポーネントがある場合は、値は最初にUTCに変換され、タイムゾーン情報が考慮されます。

RETURNING DATE PRESERVE TIMEを使用すると、このデフォルトの切捨て動作をオーバーライドし、問い合せたJSONスカラー値の時間コンポーネント(存在する場合)を保持できます。(RETURNING DATE TRUNCATE TIMEの使用は、デフォルトの動作であるRETURNING DATEとまったく同じ効果があります。)

(同じ考慮事項が、TRUNCATE TIMEに対応する項目メソッドdate()と、PRESERVE TIMEに対応するdateWithTime()に適用されます。)

json_array、json_arrayagg、json_mergepatch、json_object、json_objectagg、json_query、json_serializeおよびjson_transformの場合、VARCHAR2、CLOB、BLOBまたはJSONを使用できます。^脚注2

BLOBの結果はAL32UTF8文字セットです。json_serializeによって返されるデータ型に関係なく、返されるデータはテキストのJSONデータを表します。

必要に応じて、VARCHAR2の長さ(デフォルト: 4000)、およびNUMBERの精度とスケールを指定できます。

データ型SDO_GEOMETRYは、Oracle Spatial and Graphデータ向けです。特に、これは、json_valueをGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。

json_query (のみ)の場合、データベース初期化パラメータcompatibleが20以上で、入力データがJSONデータ型のとき:

デフォルトの戻り型(RETURNING句なし)もJSONです。

それ以外の場合、デフォルトの戻り型はVARCHAR2(4000)です。
戻りデータ型に関係なく、デフォルトでは、返されるデータをスカラーJSON値にすることができます。

戻りデータ型の直後にキーワードDISALLOW SCALARSを含めることで、この動作をオーバーライドできます。json_queryの呼出しでは、スカラーではないJSON値のみが返されます(RFC 8259がサポートされていない場合と同じ動作になります)。

RETURNING句は、オプションのキーワード(PRETTYおよびASCII)も受け入れます(戻りデータ型がJSONの場合を除く)。両方を使用する場合は、PRETTYがASCIIの前にくる必要があります。キーワードPRETTYはjson_valueには指定できません。

キーワードPRETTYを使用すると、改行文字の挿入およびインデントにより、戻されたデータがわかりやすいように出力されます。デフォルトの動作では、わかりやすいようには出力されません。

キーワードASCIIを使用すると、標準のASCII Unicodeエスケープ・シーケンスを使用することにより、戻されるデータ内の非ASCII Unicode文字がすべて自動的にエスケープされます。デフォルトの動作では、非ASCII Unicode文字はエスケープされません。

RETURNING句でVARCHAR2を指定した場合、値のスカラーは次のように表されます。

ブール値は、小文字の文字列"true"および"false"で表されます。
null値は、SQL NULLで表されます。
JSONの数値は、正準な形式で表されます。このため、JSONの数値では、出力の文字列がテキストの入力データの表現と異なる可能性があります。正準な形式で表された場合、数値は次のようになります。
- SQL NUMBERの精度と範囲の制限の対象となる場合があります。
- SQL NUMBERの制限事項の対象とならない場合には、次のようになります。
  - 精度は40桁に制限されます。
  - オプションの指数は、9桁と1つの符号(+または-)に制限されます。
  - 使用できる符号(-、+)、小数点(.)および指数のインジケータ(E)を含めて、テキスト全体が48文字に制限されます。
正準な形式のJSONの数値は、次のようなものです。
- JSONの数値です。(JSONデータで数値として解析されます。)
- 先行するプラス(+)符号はありません。
- 必要な場合にのみ小数点(.)が使用されます。
- その数値が小数(0と1の間)の場合は、小数点の前に単一のゼロ(0)が置かれます。
- 必要な場合にのみ指数表記法(E)が使用されます。これが当てはまるのは、特に、出力文字数が制限されすぎている場合です(VARCHAR2(N)のNが小さいことによる)。

OracleではSQL/JSON標準を拡張し、戻りデータ型がVARCHAR2(N)の場合は、データ型の直後にオプションのキーワードTRUNCATEを指定できるようにしました。TRUNCATEが存在し、かつ戻り値がNよりも長い場合は、値が切り捨てられ、先頭N文字のみが戻されます。TRUNCATEがない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。

返される値が緩慢な処理のために自動型変換される場合、キーワードTYPE (STRICT)を使用してこれを回避できます。

たとえば、この問合せは、デフォルトの動作(TYPE (LAX))では文字列"1"がJSON数値に自動的に変換されるため、数値1を返します:

SELECT json_value('{"a" : "1"}', '$.a' RETURNING NUMBER;

TYPE (STRICT)を使用すると、型変換を回避できます。この問合せは値を返しません:

SELECT json_value('{"a" : "1"}', '$.a' RETURNING NUMBER TYPE (STRICT);

TYPE (STRICT)を使用することは、関連する"only"データ型変換項目メソッドを適用することと同等です。たとえば、この2つの問合せは等価です。値が数値であるPONumberフィールドのみが考慮(投影)されます。

SELECT json_value(data, '$.PONumber.numberOnly()') FROM j_purchaseorder
  RETURNING NUMBER;

SELECT json_value(data, '$.PONumber') FROM j_purchaseorder
  RETURNING NUMBER TYPE (STRICT);

LOBを返すことができるJSONの任意のSQLファンクションの場合、デフォルトではLOBは参照によって返されます。かわりに、キーワードVALUEを使用して戻り型(ファンクションに応じてCLOB、BLOBまたはNCLOB)に従うことで、値ベースのLOBを返すことができます。たとえば:

SELECT json_value(...) FROM ... RETURNING CLOB VALUE;

値ベースのLOBは、解放し忘れた場合にデータベース・サーバーで蓄積できないため、一般的に効率的です。