Oracle INTEGER型の引数を持つPL/SQLプログラムの挙動

Oracle BINARY_INTEGER型の引数を持つPL/SQLプログラムの挙動の姉妹記事です。

"Oracle BINARY_INTEGER型の引数を持つPL/SQLプログラムの挙動"をまとめているときに、疑問のある動きがありました。その動きをまとめてみます。

INTEGER型とは

INTEGER型は、NUMBER型のSUBTYPEです。"PL/SQLの事前定義のデータ型"という公式ドキュメントなどに記されています。SYSが保有するSTANDARDと呼ばれるパッケージに定義されています。

subtype INTEGER  is NUMBER(38,0);

この定義にあるように、小数部が0桁となっています。つまり整数です。

PL/SQL内での挙動

このタイプには小数を入れることはできません。少し試してみましょう。実験環境はいつものOracle 11g XE on Linuxです。

DECLARE
	V_NUM		INTEGER;
BEGIN
	V_NUM := 1.3;
	DBMS_OUTPUT.PUT_LINE ( '1.3 ⇒ ' || V_NUM );
	V_NUM := 1.5;
	DBMS_OUTPUT.PUT_LINE ( '1.5 ⇒ ' || V_NUM );
	V_NUM := 1.8;
	DBMS_OUTPUT.PUT_LINE ( '1.8 ⇒ ' || V_NUM );
END;

実行すると以下のようになります。

1.3 ⇒ 1
1.5 ⇒ 2
1.8 ⇒ 2

四捨五入されているのが分かります。

引数で渡した場合の挙動

"Oracle BINARY_INTEGER型の引数を持つPL/SQLプログラムの挙動"と同じように、引数で渡してみます。

まずはFUNCTION定義です。

CREATE OR REPLACE FUNCTION FNC_TEST001 (
	IN_VALUE		IN	INTEGER
) RETURN INTEGER IS
	V_VALUE		INTEGER;
BEGIN
	V_VALUE := IN_VALUE;
	DBMS_OUTPUT.PUT_LINE ( 'IN_VALUE = [' || IN_VALUE || ']' );
	DBMS_OUTPUT.PUT_LINE ( 'V_VALUE  = [' || V_VALUE || ']' );
	RETURN V_VALUE;
END;

SELECTで呼び出してみます。

SELECT FNC_TEST001( 1.8 ) AS RESULT FROM DUAL;

抽出結果はこのようになります。

RESULT
1.8

INTEGER型を経由しているにもかかわらず、1.8といった小数値が返されました。

途中経過を見てみましょう。SQLPLUSでDBMS_OUTPUT.PUT_LINEを出力するには、"SET SERVEROUTPUT ON"が必要です。これをセットして出力すると、このようになります。

SQL> SET SERVEROUTPUT ON;
SQL> SELECT FNC_TEST001( 1.8 ) AS RESULT FROM DUAL;

    RESULT
----------
       1.8

IN_VALUE = [1.8]
V_VALUE  = [1.8]
SQL>

このように、引数で渡された値、ローカル変数に代入された値、戻り値に戻された値、いずれのINTEGER型にも小数値1.8が入っていることが分かります。

INTEGER型変数に固定値1.8を代入した時には四捨五入されています。しかし、INTEGER型⇒INTEGER型の代入の場合には、四捨五入されずにそのまま代入されています。

もとより引数で渡された値が、INTEGERの制約を外れて小数値が入っているということが、期待する挙動ではありません。

自衛的にFNC_TEST001で対処するなら、定数評価できない0加算をするか、異なるSUBTYPEのNUMBER型に代入すれば、四捨五入してくれます。下のFNC_TEST001の中で、試したい処理のIF文をTRUEに変えれば、それぞれの動作を確認できます。しかし、事情を知らないと何のために代入しているのかわからない、意味不明なコードのように見えます。

CREATE OR REPLACE FUNCTION FNC_TEST001 (
	IN_VALUE		IN	INTEGER
) RETURN INTEGER IS
	V_VALUE			INTEGER;
	V_VALUE_NUM		NUMBER;
	V_VALUE_NUM38	NUMBER(38,0);
BEGIN
	-- IN_VALUEには、INTEGER型にもかかわらず、小数値が入っていることがあり得る
	DBMS_OUTPUT.PUT_LINE ( 'IN_VALUE = [' || IN_VALUE || ']' );

	-- INTEGER型に代入することでは、小数部を取り除くことはできない
	IF FALSE THEN
		V_VALUE := IN_VALUE;
	END IF;

	-- IN_VALUEに定数評価できてしまう0を加算しても、小数部を取り除くことはできない
	IF FALSE THEN
		V_VALUE := IN_VALUE + ( 1.1 - 1.0 - 0.1 );
	END IF;

	-- IN_VALUEに定数評価できない0を加算すれば、V_VALUEには四捨五入された値が代入される
	IF FALSE THEN
		V_VALUE := IN_VALUE + TRUNC ( DBMS_RANDOM.VALUE );
	END IF;

	-- INTEGER型と互換性のあるNUMBER型に代入することでは、小数部を取り除くことはできない
	IF FALSE THEN
		V_VALUE_NUM38 := IN_VALUE;
		DBMS_OUTPUT.PUT_LINE ( 'V_VALUE_NUM38 = [' || V_VALUE_NUM38 || ']' );
		V_VALUE := V_VALUE_NUM38;
	END IF;

	-- INTEGER型と互換性のないNUMBER型に代入すると、V_VALUEには四捨五入された値が代入される
	IF TRUE THEN
		V_VALUE_NUM := IN_VALUE;
		DBMS_OUTPUT.PUT_LINE ( 'V_VALUE_NUM = [' || V_VALUE_NUM || ']' );
		V_VALUE := V_VALUE_NUM;
	END IF;

	DBMS_OUTPUT.PUT_LINE ( 'V_VALUE  = [' || V_VALUE || ']' );
	RETURN V_VALUE;
END;

SQL> SELECT FNC_TEST001( 1.8 ) AS RESULT FROM DUAL;

    RESULT
----------
         2

IN_VALUE = [1.8]
V_VALUE_NUM = [1.8]
V_VALUE  = [2]
SQL>

まとめ

"Oracle BINARY_INTEGER型の引数を持つPL/SQLプログラムの挙動"を作っているときに、他の整数型だとどのような挙動をするだろうか、という思いの中でINTEGER型の挙動に気づきました。両者を合わせると、"PL/SQLプログラムの引数の部分で暗黙の型変換を期待するのは危険"であり、むしろ、"暗黙の型変換が起こらないことを強く意識する必要がある"と言えそうです。

テストでは固定値1.8を使用しました。実際に問題が起こるとすると、式の計算結果が小数になるケースでしょう。

PL/SQLなら、"長い式を引数に渡す"よりは"いったん変数に代入して、引数には変数を渡す"とでもするほうが、安全性の意味でも可読性の意味でも良いかもしれません。

SQLでは"いったん変数に代入"というわけにはいきません。もともとの"Oracle BINARY_INTEGER型の引数を持つPL/SQLプログラムの挙動"でも、PL/SQLプログラムの一部の動きに注目して動きを確認するために、SQLを使って試そうとしたときのものでした。このようなシチュエーションでは、いったん変数に入れることはできません。

引数がINTEGER型なら、呼び出された側で対策することができます。しかし、引数がBINARY_INTEGER型の場合は、呼び出される時点で切捨てが行われるため、呼び出された側で対策することはできません。そして、Oracle公式FUNCTIONには、文字列加工FUNCTIONを中心にBINARY_INTEGER型を引数に持つものがそれなりにあります。そのため、"呼び出す側を作る開発者が気を付ける"しかありません。

もしかすると12c以降のどこかで、INTEGER型に小数値が入ってしまうことがないように修正されているかもしれません。