SQL Server Migration Assistant for Oracle  による Oracle Database から Azure SQL Database への移行検証～PL/SQL 基礎編～


## 概要

### 本記事の位置付けについて

Oracle Database から Azure SQL Database へ SQL Server Migration Assistant for Oracle （SSMA）によるプログラム移行検証結果の一部である。次の記事にて個別機能別記事へのリンクを整理している。

- [SQL Server Migration Assistant for Oracle による Oracle Database から Azure SQL Database への移行検証記事のまとめ - Qiita](https://qiita.com/manabian/items/0ec96b66033a7bd03a25)


### PL/SQL について

Oracle Database ドキュメントにて、次のように記載されている。

> SQLの手続き型拡張機能としてオラクル社が提供する言語であり、移植性を備えた高性能のトランザクション処理言語です。

引用元：[1 PL/SQLの概要 (oracle.com)](https://docs.oracle.com/cd/F19136_01/lnpls/overview.html#GUID-2FBCFBBE-6B42-4DB8-83F3-55B63B75B1EB)

異なるデータベースへ移行する際には、SQL 方言への対応が必要となる。。データベースサービス固有の SQL 記法を SQL 方言（SQL dialects）と言われており、Oracle Database 固有の記法を Oracle 方言と表現されることもある。移行元コードにて Oracle 方言を特定し、適切な移行方法を検討することが重要である。


### 参考リンク

- ホワイトペーパー
    - Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database

## Azure SQL Database への移行

### SSMA による変換

Oracle 方言への対応については、SSMA により自動的に変換される。自動変換できない記法については、issues として報告される。

階層問合せのクエリは、WITH 句のクエリに変換される。詳細については、次のホワイトペーパーに記載されている。

- Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database

### 移行方針

特になし。

### 変換例

#### 変換例 1 `SELECT *`のコード

##### Oracle Database のコード

`SELECT *`のコードは、カラム名に展開される。

```sql
SELECT * FROM HR.DEPARTMENTS;
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
SELECT
    DEPARTMENTS.DEPARTMENT_ID,
    DEPARTMENTS.DEPARTMENT_NAME,
    DEPARTMENTS.MANAGER_ID,
    DEPARTMENTS.LOCATION_ID
FROM HR.DEPARTMENTS
GO
```

#### 変換例 2 ROWNUM によるレコード数の制限

##### Oracle Database のコード

```sql
SELECT
    DEPARTMENT_ID,
    DEPARTMENT_NAME,
    MANAGER_ID,
    LOCATION_ID
FROM HR.DEPARTMENTS
WHERE
    ROWNUM <= 10;
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
SELECT TOP (10)
   DEPARTMENTS.DEPARTMENT_ID,
   DEPARTMENTS.DEPARTMENT_NAME,
   DEPARTMENTS.MANAGER_ID,
   DEPARTMENTS.LOCATION_ID
FROM HR.DEPARTMENTS
GO
```

#### 変換例 3 四則演算によるデータ型の暗黙的変換

##### Oracle Database のコード

文字型のカラムに対して数値を加算することによる暗黙的変換も想定通りに変換される。

```sql
WITH SRC AS (
SELECT '1' AS COL_1 FROM DUAL
UNION ALL
SELECT '2' AS COL_1 FROM DUAL
UNION ALL
SELECT '3' AS COL_1 FROM DUAL
)
SELECT
    COL_1 + 0
    FROM 
	SRC
;   
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
WITH 
   SRC AS 
   (
      SELECT '1' AS COL_1
       UNION ALL
      SELECT '2' AS COL_1
       UNION ALL
      SELECT '3' AS COL_1
   )
SELECT CAST(SRC.COL_1 AS float(53)) + 0
FROM SRC
GO
```


#### 変換例 4 結合演算子(+)による結合

##### Oracle Database のコード

Oracle Database (+) 演算子を利用した SELECT 句は、ANSI 形式の SQL に変換される。

```sql
SELECT
    em.EMPLOYEE_ID, 
    dep.DEPARTMENT_NAME
    FROM 
        HR.employees em, HR.departments dep
    WHERE
        em.DEPARTMENT_ID = dep.DEPARTMENT_ID(+);
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
SELECT EM.EMPLOYEE_ID, DEP.DEPARTMENT_NAME
FROM 
   HR.EMPLOYEES  AS EM 
      LEFT OUTER JOIN HR.DEPARTMENTS  AS DEP 
      ON EM.DEPARTMENT_ID = DEP.DEPARTMENT_ID
GO
```

#### 変換例 5 整数の除算

##### Oracle Database のコード

Azure SQL ファミリーでは int 型のカラムを除算した際に小数点以下の数値を損失するという仕様があるが、除算した場合にデータ型の変更したコードに変換される。

```sql
DECLARE
    int_num int;
BEGIN
    int_num := 10;
    DBMS_OUTPUT.PUT_LINE(int_num / 3);
END;
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
BEGIN

   DECLARE
      @INT_NUM int

   SET @INT_NUM = 10

   PRINT CAST(@INT_NUM AS float(53)) / 3

END
GO
```

#### 変換例 6 NULL 値を含むカラムによる並び替え

##### Oracle Database のコード

カラム値により並び替えを実施した際に、Oracle Database では NULL 値が最後に表示されるのに対して Azure SQL ファミリーでは NULL 値が最初に表示さる仕様‘があるが、`Emulate Oracle null behavior in ORDER BY clauses`の設定値により 同等の動作をしない場合がある。

```sql
WITH SRC AS(
SELECT NULL AS COL_1 FROM DUAL
UNION ALL
SELECT ' ' AS COL_1 FROM DUAL
UNION ALL
SELECT 'A' AS COL_1 FROM DUAL
UNION ALL
SELECT '1' AS COL_1 FROM DUAL
UNION ALL
SELECT 'あ' AS COL_1 FROM DUAL
)
SELECT
    *
    FROM
        src
    ORDER BY
        COL_1;
```

##### Azure SQL Database への変換

SSMA の設定値である`Emulate Oracle null behavior in ORDER BY clauses`の値を`No`に設定した場合、SSMA により次のように変換される。

```sql
WITH 
   SRC AS 
   (
      SELECT NULL AS COL_1
       UNION ALL
      SELECT ' ' AS COL_1
       UNION ALL
      SELECT 'A' AS COL_1
       UNION ALL
      SELECT '1' AS COL_1
       UNION ALL
      SELECT 'あ' AS COL_1
   )
SELECT SRC.COL_1
FROM SRC
ORDER BY SRC.COL_1
GO
```

SSMA の設定値である`Emulate Oracle null behavior in ORDER BY clauses`の値を`YES`に設定した場合、SSMA により次のように変換される。

```sql
WITH 
   SRC AS 
   (
      SELECT NULL AS COL_1
       UNION ALL
      SELECT ' ' AS COL_1
       UNION ALL
      SELECT 'A' AS COL_1
       UNION ALL
      SELECT '1' AS COL_1
       UNION ALL
      SELECT 'あ' AS COL_1
   )
SELECT SRC.COL_1
FROM SRC
ORDER BY 
   CASE 
      WHEN SRC.COL_1 IS NULL THEN 1
      ELSE 0
   END, SRC.COL_1
GO
```

#### 変換例 7 空白の値

##### Oracle Database のコード

Oracle Database では空白の値は NULL として扱わる仕様であり、SSMA により空白は NULL に変換される。

```sql
SELECT '' AS COL_1 FROM DUAL;
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
SELECT NULL AS COL_1
GO
```

#### 変換例 8 NULL を結合（`||`）した場合の項目表示

##### Oracle Database のコード

Oracle Database では、NULL を結合した値を表示した場合に値が表示されるが、Azure SQL Database では値が表示されない。

```sql
DECLARE
    str_1 NVARCHAR2(10):= 'a';
    str_2 NVARCHAR2(10):= '';    
BEGIN
    DBMS_OUTPUT.PUT_LINE(str_1 || str_2);
END;
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
BEGIN

   DECLARE
      @STR_1 nvarchar(10) = 'a', 
      @STR_2 nvarchar(10) = NULL

   PRINT ISNULL(@STR_1, '') + ISNULL(@STR_2, '')

END
GO
```

#### 変換例 9 階層問合せ

##### Oracle Database のコード

階層問合せのコードは、WITH 句のコードに変換される。

```sql
SELECT
    employee_id,
    last_name,
    manager_id,
    level
FROM
    hr.employees
START WITH
    employee_id = 100
CONNECT BY
    PRIOR employee_id = manager_id;
```

##### Azure SQL Database への変換

SSMA により次のように変換される。

```sql
WITH 
   h$cte AS 
   (
      SELECT 
         EMPLOYEES.EMPLOYEE_ID, 
         EMPLOYEES.LAST_NAME, 
         EMPLOYEES.MANAGER_ID, 
         1 AS LEVEL, 
         CAST(row_number() OVER(
            ORDER BY @@spid) AS varchar(max)) AS path
      FROM HR.EMPLOYEES
      WHERE EMPLOYEES.EMPLOYEE_ID = 100
       UNION ALL
      SELECT 
         EMPLOYEES.EMPLOYEE_ID, 
         EMPLOYEES.LAST_NAME, 
         EMPLOYEES.MANAGER_ID, 
         h$cte.LEVEL + 1 AS LEVEL, 
         path + ',' + CAST(row_number() OVER(
            ORDER BY @@spid) AS varchar(max)) AS path
      FROM HR.EMPLOYEES, h$cte
      WHERE h$cte.EMPLOYEE_ID = EMPLOYEES.MANAGER_ID
   )
SELECT h$cte.EMPLOYEE_ID, h$cte.LAST_NAME, h$cte.MANAGER_ID, h$cte.LEVEL
FROM h$cte
ORDER BY h$cte.path
GO
```

#### 変換例 10 リテラル

##### Oracle Database のコード

```sql
SELECT
    'ABC'
    ,12345
    ,'あいうえお'
    ,'漢字'
FROM
    DUAL;
```

##### Azure SQL Database への変換

次のように変換されるが、データベースの照合順序（例：照合順序が UTF-8 でない場合）によっては日本語が文字化けしてしまう場合がある。

```sql
SELECT
    'ABC',
    12345,
    'あいうえお',
    '漢字'
GO
```

マルチバイトのリテラルの場合には、N プレフィックスを設定する。

```sql
SELECT
    'ABC',
    12345,
    N'あいうえお',
    N'漢字'
GO
```

#### 変換例 11 プロシージャ

##### Oracle Database のコード

```sql
CREATE OR REPLACE PROCEDURE OUTPUT_CHAR
IS
    out_char varchar2(20) := '表示されるよ';
BEGIN
    DBMS_OUTPUT.PUT_LINE(out_char);
END;
```

##### Azure SQL Database への変換

Azure SQL Database のプロシージャに変換される。

```sql
CREATE PROCEDURE SSMA.OUTPUT_CHAR  
AS 
   BEGIN

      DECLARE
         @OUT_CHAR varchar(20) = '表示されるよ'

      PRINT @OUT_CHAR

   END
GO
```