概要
本記事の位置付けについて
Oracle Database から Azure SQL Database へ SQL Server Migration Assistant for Oracle （SSMA）によるプログラム移行検証結果の一部である。次の記事にて個別機能別記事へのリンクを整理している。
ユーザー定義関数について
SQL、または、組込み関数にはない機能をもたせた関数である。
参考リンク
- ホワイトペーパー
- Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database
- Oracle to SQL Server Migration Handbook v 2.0
Azure SQL Database への移行
SSMA による変換
Oracle Database では関数内で次のようなことを実施している場合には、関数だけでなく、ストアードプロシージャが生成される。
- DML ステートメントを含めること
- ストアド プロシージャを呼び出すこと
- トランザクション管理コマンドを実施すること
- 例外処理
Oracle Database では関数の引数として次の方法を実施できるが、Azure SQL ファミリーが対応している位置表記法に変換される。
- 位置表記法
- 名前表記法
- 混合表記法
INLINE pragmaを指定している場合には、エラーとなるため、INLINE pragmaの表記を削除する必要がある。
移行方針
Azure SQL Database では拡張ストアド プロシージャ機能がサポートされていないため、xp_ora2ms_exec2_ex 拡張ストアードプロシージャを用いた関数に変換された場合には、同時に生成されるプロシージャ（$IMPL）への呼び出しに置き換える必要がある。
変換例
変換例 1 基本的な関数
Oracle Database のコード
CREATE OR REPLACE FUNCTION funtions_001 (
in_var_1 VARCHAR2 := ' ',
in_var_2 VARCHAR2 := ' ',
in_var_3 VARCHAR2 := ' '
) RETURN NUMBER IS
return_val NUMBER := 999;
BEGIN
IF in_var_1 != ' ' THEN
return_val := 1;
END IF;
IF in_var_2 != ' ' THEN
return_val := 2;
END IF;
IF in_var_3 != ' ' THEN
return_val := 3;
END IF;
RETURN ( return_val );
END;
Azure SQL Database への変換
SSMA により次のように変換される。
CREATE FUNCTION SSMA.FUNTIONS_001
(
@IN_VAR_1 varchar(max) = ' ',
@IN_VAR_2 varchar(max) = ' ',
@IN_VAR_3 varchar(max) = ' '
)
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
RETURNS float(53)
AS
BEGIN
DECLARE
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@RETURN_VAL float(53) = 999
IF @IN_VAR_1 != ' '
SET @RETURN_VAL = 1
IF @IN_VAR_2 != ' '
SET @RETURN_VAL = 2
IF @IN_VAR_3 != ' '
SET @RETURN_VAL = 3
RETURN @RETURN_VAL
END
GO
変換例 2 ストアードプロシージャに置換される例
Oracle Database のコード
CREATE OR REPLACE FUNCTION funtions_002 (
in_var_1 VARCHAR2 := ' ',
in_var_2 VARCHAR2 := ' ',
in_var_3 VARCHAR2 := ' '
) RETURN NUMBER IS
return_val NUMBER := 999;
BEGIN
IF in_var_1 != ' ' THEN
return_val := 1;
END IF;
IF in_var_2 != ' ' THEN
return_val := 2;
END IF;
IF in_var_3 != ' ' THEN
return_val := 3;
END IF;
RETURN ( return_val );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('エラーです');
END;
Azure SQL Database への変換
SSMA により次のように変換される。
/*
* SSMA error messages:
* O2SS0518: Wrapper functions are not supported by Azure SQL platform. Use $impl procedures instead.
CREATE FUNCTION SSMA.FUNTIONS_002
(
@IN_VAR_1 varchar(max) = ' ',
@IN_VAR_2 varchar(max) = ' ',
@IN_VAR_3 varchar(max) = ' '
)
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
RETURNS float(53)
AS
BEGIN
DECLARE
@active_spid INT,
@login_time DATETIME,
@db_name NVARCHAR(128)
SET @active_spid = ssma_oracle.GET_ACTIVE_SPID()
SET @login_time = ssma_oracle.GET_ACTIVE_LOGIN_TIME()
SET @db_name = DB_NAME()
DECLARE
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@return_value_argument float(53)
/*
* SSMA warning messages:
* O2SS0452: "xp_ora2ms_exec2_ex" when called from within UDF cannot bind to outer transaction. It can lead to dead locks and losing transaction atomicity. Consider calling $impl procedure directly.
*/
EXECUTE master.dbo.xp_ora2ms_exec2_ex
@active_spid,
@login_time,
@db_name,
N'SSMA',
N'FUNTIONS_002$IMPL',
N'true',
@IN_VAR_1,
@IN_VAR_2,
@IN_VAR_3,
@return_value_argument OUTPUT
RETURN @return_value_argument
END
*/
CREATE PROCEDURE SSMA.FUNTIONS_002$IMPL
@IN_VAR_1 varchar(max) = ' ',
@IN_VAR_2 varchar(max) = ' ',
@IN_VAR_3 varchar(max) = ' ',
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@return_value_argument float(53) OUTPUT
AS
BEGIN
DECLARE
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@RETURN_VAL float(53) = 999
BEGIN TRY
IF @IN_VAR_1 != ' '
SET @RETURN_VAL = 1
IF @IN_VAR_2 != ' '
SET @RETURN_VAL = 2
IF @IN_VAR_3 != ' '
SET @RETURN_VAL = 3
SET @return_value_argument = @RETURN_VAL
RETURN
END TRY
BEGIN CATCH
BEGIN
PRINT 'エラーです'
END
END CATCH
END
GO
変換例 3 位置表記法、名前表記法、および、混合表記法によるパラメータの指定
Oracle Database のコード
BEGIN
dbms_output.put_line(SSMA.funtions_001());
dbms_output.put_line(SSMA.funtions_001('1'));
dbms_output.put_line(SSMA.funtions_001(' ', '2'));
dbms_output.put_line(SSMA.funtions_001(' ', in_var_2=>'2'));
dbms_output.put_line(SSMA.funtions_001(in_var_2=>'2'));
END;
Azure SQL Database への変換
SSMA により次のように変換される。
BEGIN
/*
* SSMA warning messages:
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_1.
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_2.
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
*/
PRINT SSMA.FUNTIONS_001(DEFAULT, DEFAULT, DEFAULT)
/*
* SSMA warning messages:
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_2.
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
*/
PRINT SSMA.FUNTIONS_001('1', DEFAULT, DEFAULT)
/*
* SSMA warning messages:
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
*/
PRINT SSMA.FUNTIONS_001(' ', '2', DEFAULT)
/*
* SSMA warning messages:
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
*/
PRINT SSMA.FUNTIONS_001(' ', '2', DEFAULT)
/*
* SSMA warning messages:
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_1.
* O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
*/
PRINT SSMA.FUNTIONS_001(DEFAULT, '2', DEFAULT)
END
GO
変換例 4 INLINE pragma の利用
Oracle Database のコード
BEGIN PRAGMA inline ( funtions_001, 'YES' );
dbms_output.put_line(SSMA.funtions_001());
PRAGMA inline ( funtions_001, 'NO' );
dbms_output.put_line(SSMA.funtions_001());
END;
Azure SQL Database への変換
次のように、エラーとなる。
BEGIN
/*
* SSMA error messages:
* O2SS0004: Unparsed SQL (Parse error at line 2, column 16. Encountered: .)
* [PRAGMA inline ( funtions_001, 'YES' );
* dbms_output.put_line(SSMA.funtions_001());
* PRAGMA inline ( funtions_001, 'NO' );
* dbms_output.put_line(SSMA.funtions_001());] cannot be converted.
*/
DECLARE
@db_null_statement int
END
GO