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

最近会社で聞かれて答えたことをまとめてみました。

以下の動作をOracle 11g XE on Linuxで試しています。Oracle 11g XEも会社と同じ動きでした。

事の発端

事の発端は、私が即席で作って渡したPL/SQLプログラムが思ったような動きをしないという質問でした。問題個所を紐解くと、"特定の文字列の中にある一文字をランダムに返す"という処理でした。その処理がたまに空文字列を返すというのです。

以下のような処理になっていました。

X := SUBSTR ( 'ABCDE', DBMS_RANDOM.VALUE * 5 + 1, 1 );

問題は、SUBSTRの第二引数です。SUBSTRの第二引数には、切り出す文字列の開始位置を1オリジンで指定します。その特性上、整数を指定することになります。しかし、小数値を渡していたのが問題でした。

式の値"DBMS_RANDOM.VALUE * 5 + 1"は1以上6未満のランダム数値です。PL/SQLは整数への暗黙のキャスト時に四捨五入するから、5.5以上の値は6になります。文字列の終端を超えた開始位置を指定しているから、空文字列になるんだといった説明をしました。

試すならこんな感じですね。実行すると、ときどき空文字列が返されることが分かります。

BEGIN
	FOR V_IDX IN 1 .. 100 LOOP
		DBMS_OUTPUT.PUT_LINE (
			'[' || SUBSTR ( 'ABCDE', DBMS_RANDOM.VALUE * 5 + 1, 1 ) || ']'
		);
	END LOOP;
END;

SQLで試すほうが簡単かな。こんな感じのSQLですね。ほら、ときどき空文字列が...ん？返されない？

SELECT	SUBSTR ( 'ABCDE', DBMS_RANDOM.VALUE * 5 + 1, 1 )
FROM	ALL_OBJECTS
WHERE	ROWNUM <= 100

PL/SQLからSUBSTRを呼び出すときと、SQLからSUBSTRを呼び出すときで、動きが違う？

"プログラムのどこが誤っているか"というだけの質問が、大ごとになってきました。

問題個所の再確認

会社では色々と試行錯誤しました。その経緯は省略します。最終的に、問題事象を直接的に示すものは以下です。

BEGIN
	BEGIN
		DBMS_OUTPUT.PUT_LINE ( 'PL/SQLで計算 = [' || SUBSTR ( 'ABCDE', 5.8, 1 ) || ']' );
	END;
	DECLARE
		V_CHR	VARCHAR2(32767);
	BEGIN
		SELECT	SUBSTR ( 'ABCDE', 5.8, 1 )
		INTO	V_CHR
		FROM	DUAL
		;
		DBMS_OUTPUT.PUT_LINE ( 'SQLで計算 = [' || V_CHR || ']' );
	END;
END;

これを実行すると、以下の結果となります。

PL/SQLで計算 = []
SQLで計算 = [E]

SUBSTRの第二引数に、PL/SQLでは四捨五入されて"6"が渡され、SQLのSELECT句では切り捨てられて"5"が渡されているようだ、という結果になりました。

SQLやPL/SQLでは"普通は四捨五入"と説明していたら、C/Java畑の人から"普通は切り捨てじゃないのか？"と質問されたり。以下のようなSQLで四捨五入されることを確認できます。

SELECT CAST ( 1.8 AS INTEGER ) FROM DUAL

会社ではこのあたりまで確認したところでストップ。整数を指定する場所に小数を指定しない、ちゃんと整数を指定することが大切、という結論になりました。

ここからは、会社で試さなかったことをもう少し掘り下げてみます。

SUBSTRのラッパーを作ってみる

SUBSTRを呼び出すラッパーのユーザーFUNCTIONを作ってみるとどうなるでしょうか？

CREATE OR REPLACE FUNCTION FNC_SUBSTR_TEST01 (
		STR1	IN	VARCHAR2
	,	POS		IN	NUMBER
	,	LEN		IN	NUMBER
)
RETURN VARCHAR2 IS
BEGIN
	RETURN SUBSTR ( STR1, POS, LEN );
END;

実行すると、このようになります。

WITH
DATA AS (
				SELECT 0 AS VALUE FROM DUAL WHERE 0 = 1
	UNION ALL	SELECT 1.0 AS VALUE FROM DUAL
	UNION ALL	SELECT 1.3 AS VALUE FROM DUAL
	UNION ALL	SELECT 1.8 AS VALUE FROM DUAL
	UNION ALL	SELECT 2.0 AS VALUE FROM DUAL
	UNION ALL	SELECT 4.8 AS VALUE FROM DUAL
	UNION ALL	SELECT 5.0 AS VALUE FROM DUAL
	UNION ALL	SELECT 5.3 AS VALUE FROM DUAL
	UNION ALL	SELECT 5.8 AS VALUE FROM DUAL
	UNION ALL	SELECT 6.0 AS VALUE FROM DUAL
)

SELECT	VALUE
	,	SUBSTR ( 'ABCDE', VALUE, 1 )	AS	"SUBSTR"
	,	FNC_SUBSTR_TEST01 ( 'ABCDE', VALUE, 1 )	AS	"FNC_SUBSTR_TEST01"
FROM	DATA

VALUE	SUBSTR	FNC_SUBSTR_TEST01
1	A	A
1.3	A	A
1.8	A	B
2	B	B
4.8	D	E
5	E	E
5.3	E	E
5.8	E
6

両者で動きが異なります。動きが異なるということは"プログラム"か"外部環境"か"外部入力"のどれかが異なるということです。今回の場合、外部環境や外部入力は同じように見えます。すると、プログラムが異なるということになります。

プログラムを比較してみることにします。とはいっても、標準プログラムにソースはありません。比較できるのはI/O部分だけです。

SELECT	*
FROM	ALL_PROCEDURES AP
WHERE	AP.OWNER || '.' || AP.OBJECT_NAME || '.' || AP.PROCEDURE_NAME IN ( 'SYSTEM.FNC_SUBSTR_TEST01.', 'SYS.STANDARD.SUBSTR' )
ORDER BY
		AP.OWNER
	,	AP.OBJECT_NAME
	,	AP.PROCEDURE_NAME
	,	AP.OVERLOAD

OWNER	OBJECT_NAME	PROCEDURE_NAME	...	OVERLOAD	OBJECT_TYPE	...
SYS	STANDARD	SUBSTR		1	PACKAGE
SYS	STANDARD	SUBSTR		2	PACKAGE
SYSTEM	FNC_SUBSTR_TEST01				FUNCTION

SELECT	AA.*
FROM	ALL_ARGUMENTS AA
WHERE	AA.OWNER || '.' || AA.PACKAGE_NAME || '.' || AA.OBJECT_NAME IN ( 'SYSTEM..FNC_SUBSTR_TEST01', 'SYS.STANDARD.SUBSTR' )
ORDER BY
		AA.OWNER
	,	AA.PACKAGE_NAME
	,	AA.OBJECT_NAME
	,	AA.OVERLOAD
	,	AA.POSITION

OWNER	OBJECT_NAME	PACKAGE_NAME	...	OVERLOAD	ARGUMENT_NAME	POSITION	DATA_TYPE	...
SYS	SUBSTR	STANDARD		1		0	VARCHAR2
SYS	SUBSTR	STANDARD		1	STR1	1	VARCHAR2
SYS	SUBSTR	STANDARD		1	POS	2	BINARY_INTEGER
SYS	SUBSTR	STANDARD		1	LEN	3	BINARY_INTEGER
SYS	SUBSTR	STANDARD		2		0	CLOB
SYS	SUBSTR	STANDARD		2	STR1	1	CLOB
SYS	SUBSTR	STANDARD		2	POS	2	BINARY_INTEGER
SYS	SUBSTR	STANDARD		2	LEN	3	BINARY_INTEGER
SYSTEM	FNC_SUBSTR_TEST01					0	VARCHAR2
SYSTEM	FNC_SUBSTR_TEST01				STR1	1	VARCHAR2
SYSTEM	FNC_SUBSTR_TEST01				POS	2	NUMBER
SYSTEM	FNC_SUBSTR_TEST01				LEN	3	NUMBER

引数が異なります。SUBSTRの第2引数はBINARY_INTEGERでした。BINARY_INTEGERはハードウェア算術計算で高速に計算してくれる整数型です。

引数をBINARY_INTEGER型に変えたFUNCTIONも作ってみます。

CREATE OR REPLACE FUNCTION FNC_SUBSTR_TEST02 (
		STR1	IN	VARCHAR2
	,	POS		IN	BINARY_INTEGER
	,	LEN		IN	BINARY_INTEGER
)
RETURN VARCHAR2 IS
BEGIN
	RETURN SUBSTR ( STR1, POS, LEN );
END;

WITH
DATA AS (
				SELECT 0 AS VALUE FROM DUAL WHERE 0 = 1
	UNION ALL	SELECT 1.0 AS VALUE FROM DUAL
	UNION ALL	SELECT 1.3 AS VALUE FROM DUAL
	UNION ALL	SELECT 1.8 AS VALUE FROM DUAL
	UNION ALL	SELECT 2.0 AS VALUE FROM DUAL
	UNION ALL	SELECT 4.8 AS VALUE FROM DUAL
	UNION ALL	SELECT 5.0 AS VALUE FROM DUAL
	UNION ALL	SELECT 5.3 AS VALUE FROM DUAL
	UNION ALL	SELECT 5.8 AS VALUE FROM DUAL
	UNION ALL	SELECT 6.0 AS VALUE FROM DUAL
)

SELECT	VALUE
	,	SUBSTR ( 'ABCDE', VALUE, 1 )			AS	"SUBSTR"
	,	FNC_SUBSTR_TEST01 ( 'ABCDE', VALUE, 1 )	AS	"FNC_SUBSTR_TEST01"
	,	FNC_SUBSTR_TEST02 ( 'ABCDE', VALUE, 1 )	AS	"FNC_SUBSTR_TEST02"
FROM	DATA

VALUE	SUBSTR	FNC_SUBSTR_TEST01	FNC_SUBSTR_TEST02
1	A	A	A
1.3	A	A	A
1.8	A	B	A
2	B	B	B
4.8	D	E	D
5	E	E	E
5.3	E	E	E
5.8	E		E
6

FNC_SUBSTR_TEST02はSUBSTRと同じ動きになりました。そうすると、NUMBER(小数)からBINARY_INTEGERへの型変換時に切り捨てられているようだということになります。

BINARY_INTEGERへの型変換の挙動を確認する

"BINARY_INTEGER型を引数に持つ"という特徴に特化したFUNCTIONを作成してみます。BINARY_INTEGER型の値を受け取り、その値をそのまま返すFUNCTIONです。

CREATE OR REPLACE FUNCTION FNC_ARG_TEST01 (
		VALUE	IN	BINARY_INTEGER
)
RETURN BINARY_INTEGER IS
BEGIN
	RETURN VALUE;
END;

これを使って、SQLとPL/SQLの動作を比較してみます。

DECLARE
	V_VALUE	BINARY_INTEGER;
BEGIN
	BEGIN
		FOR V_REC IN (
			SELECT	FNC_ARG_TEST01(1.8) AS VALUE
			FROM	DUAL
		) LOOP
			DBMS_OUTPUT.PUT_LINE ( 'SQL = [' || V_REC.VALUE || ']' );
		END LOOP;
	END;
	BEGIN
		DBMS_OUTPUT.PUT_LINE ( 'PL/SQL = [' || FNC_ARG_TEST01(1.8) || ']' );
	END;
END;

結果はこうなります。

SQL = [1]
PL/SQL = [2]

ここまでで分かったことは、以下の内容です。

• BINARY_INTEGER型の引数に小数を渡すと、SQL内で呼び出す場合は切り捨てられた値が渡される。
• BINARY_INTEGER型の引数に小数を渡すと、PL/SQL内で呼び出すときは四捨五入された値が渡される。

なお、単純代入は四捨五入です。以下の結果は2になります。

DECLARE
	V_VALUE	BINARY_INTEGER;
BEGIN
	V_VALUE := 18 / 10; -- 1.8;
	DBMS_OUTPUT.PUT_LINE ( '単純代入 = [' || V_VALUE || ']' );
END;

まとめ

以下のようなことが分かりました。

• BINARY_INTEGER型の引数に小数を渡すと、SQL内で呼び出す場合は切り捨てられた値が渡される。
• BINARY_INTEGER型の引数に小数を渡すと、PL/SQL内で呼び出すときは四捨五入された値が渡される。
• PL/SQL内で、BINARY_INTEGER型の変数に小数を入れると、四捨五入される。

SQLでBINARY_INTEGER型の引数に小数を渡すときだけ、切捨てとなるようです。この観点で公式やネットを調べてみました。しかし、この挙動の理由に辿り着くことはできませんでした。

標準で提供されている文字列操作系のFUNCTIONを中心に、処理の高速化のためにBINARY_INTEGER型の引数を使用している個所は随所にあると思います。普段から引数のデータ型を意識することが大切です。このような教訓を再認識しつつも、もやもや感の残る結果になってしまいました。