SQL Server Migration Assistant for Oracle  による Oracle Database から Azure SQL Database への移行検証～Oracle ネストしたサブプログラム編～


## 概要

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

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)


### ネストしたサブプログラム について

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

> PL/SQLブロック内で作成されるサブプログラム

引用元：[8.2 ネストしたサブプログラム、パッケージ・サブプログラムおよびスタンドアロン・サブプログラム (oracle.com)](https://docs.oracle.com/cd/F19136_01/lnpls/plsql-subprograms.html#GUID-E03F512B-1A30-43B4-8DCA-64DA68AE7CE4)


Oracle Database では、PL/SQLブロック内内にサブプログラムを記述することが可能である。

### 参考リンク

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

## Azure SQL Database への移行

### SSMA による変換

Azure SQL ファミリーには同等の機能が存在しないため、SSMA の`local modules conversion`の設定値である次の値に応じた変換が実施される。

1. Inline
2. Stored procedure

次のホワイトペーパーにて、詳細が記載されている。

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

### 移行方針

特になし

### 変換例

#### 変換例 1 変数が共有されているネストしたサブプログラム

##### Oracle Database のコード

```sql
CREATE OR REPLACE PROCEDURE HR.nest_subprogram_001 IS
    tgt_manager_id hr.departments.manager_id%TYPE := 200;
    dpt_name       hr.departments.department_name%TYPE;

    PROCEDURE get_dept_name_by_manger IS
    BEGIN
        SELECT
            department_name
        INTO dpt_name
        FROM
            hr.departments
        WHERE
            manager_id = tgt_manager_id;

    END get_dept_name_by_manger;

BEGIN
    tgt_manager_id := 100;
    get_dept_name_by_manger();
    dbms_output.put_line(dpt_name);
END;
/
```

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

`local modules conversion`が`Inline`の場合には、SSMA により次のように変換される。

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_001  
AS 
   BEGIN

      DECLARE
         @TGT_MANAGER_ID numeric(6, 0) = 200, 
         @DPT_NAME varchar(30)

      SET @TGT_MANAGER_ID = 100

      BEGIN
         BEGIN
            SELECT @DPT_NAME = DEPARTMENTS.DEPARTMENT_NAME
            FROM HR.DEPARTMENTS
            WHERE DEPARTMENTS.MANAGER_ID = @TGT_MANAGER_ID
         END
      END

      PRINT @DPT_NAME

   END
GO
```

`local modules conversion`が`Stored procedure`の場合には、SSMA により次のように変換される。

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_001  
AS 
   BEGIN

      DECLARE
         @TGT_MANAGER_ID numeric(6, 0) = 200, 
         @DPT_NAME varchar(30)

      SET @TGT_MANAGER_ID = 100

      EXECUTE HR.NEST_SUBPROGRAM_001$GET_DEPT_NAME_BY_MANGER @TGT_MANAGER_ID = @TGT_MANAGER_ID, @DPT_NAME = @DPT_NAME  OUTPUT

      PRINT @DPT_NAME

   END
GO
```

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_001$GET_DEPT_NAME_BY_MANGER  
   @TGT_MANAGER_ID numeric(6, 0),
   @DPT_NAME varchar(30)  OUTPUT
AS 
   BEGIN
      SELECT @DPT_NAME = DEPARTMENTS.DEPARTMENT_NAME
      FROM HR.DEPARTMENTS
      WHERE DEPARTMENTS.MANAGER_ID = @TGT_MANAGER_ID
   END
GO
```

#### 変換例 2 変数が共有されているネストしたサブプログラム

##### Oracle Database のコード

```sql
DECLARE
    PROCEDURE get_dept_name_by_manger (
        tgt_manager_id hr.departments.manager_id%TYPE,
        dpt_name       OUT hr.departments.department_name%TYPE
    ) IS
    BEGIN
        SELECT
            department_name
        INTO dpt_name
        FROM
            hr.departments
        WHERE
            manager_id = tgt_manager_id;

    END get_dept_name_by_manger;

    PROCEDURE pring_dept_name_by_manager_100 IS
        tgt_manager_id hr.departments.manager_id%TYPE;
        dpt_name       hr.departments.department_name%TYPE;
    BEGIN
        tgt_manager_id := 100;
        get_dept_name_by_manger(tgt_manager_id, dpt_name);
        dbms_output.put_line(dpt_name);
    END pring_dept_name_by_manager_100;

    PROCEDURE pring_dept_name_by_manager_200 IS
        tgt_manager_id hr.departments.manager_id%TYPE;
        dpt_name       hr.departments.department_name%TYPE;
    BEGIN
        tgt_manager_id := 200;
        get_dept_name_by_manger(tgt_manager_id, dpt_name);
        dbms_output.put_line(dpt_name);
    END pring_dept_name_by_manager_200;
BEGIN
    pring_dept_name_by_manager_100();
    pring_dept_name_by_manager_200();
END;
```

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


`local modules conversion`が`Inline`の場合には、SSMA により次のように変換される。

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_002  
AS 
   BEGIN

      BEGIN
         BEGIN

            DECLARE
               @PRING_DEPT_NAME_BY_MANAGER_100$TGT_MANAGER_ID numeric(6, 0), 
               @PRING_DEPT_NAME_BY_MANAGER_100$DPT_NAME varchar(30)

            SET @PRING_DEPT_NAME_BY_MANAGER_100$TGT_MANAGER_ID = 100

            BEGIN

               DECLARE
                  @GET_DEPT_NAME_BY_MANGER$TGT_MANAGER_ID numeric(6, 0)

               DECLARE
                  @GET_DEPT_NAME_BY_MANGER$DPT_NAME varchar(30)

               SET @GET_DEPT_NAME_BY_MANGER$TGT_MANAGER_ID = @PRING_DEPT_NAME_BY_MANAGER_100$TGT_MANAGER_ID

               SET @GET_DEPT_NAME_BY_MANGER$DPT_NAME = @PRING_DEPT_NAME_BY_MANAGER_100$DPT_NAME

               BEGIN

                  SET @GET_DEPT_NAME_BY_MANGER$DPT_NAME = NULL

                  SELECT @GET_DEPT_NAME_BY_MANGER$DPT_NAME = DEPARTMENTS.DEPARTMENT_NAME
                  FROM HR.DEPARTMENTS
                  WHERE DEPARTMENTS.MANAGER_ID = @GET_DEPT_NAME_BY_MANGER$TGT_MANAGER_ID

               END

               SET @PRING_DEPT_NAME_BY_MANAGER_100$DPT_NAME = @GET_DEPT_NAME_BY_MANGER$DPT_NAME

            END

            PRINT @PRING_DEPT_NAME_BY_MANAGER_100$DPT_NAME

         END
      END

      BEGIN
         BEGIN

            DECLARE
               @PRING_DEPT_NAME_BY_MANAGER_200$TGT_MANAGER_ID numeric(6, 0), 
               @PRING_DEPT_NAME_BY_MANAGER_200$DPT_NAME varchar(30)

            SET @PRING_DEPT_NAME_BY_MANAGER_200$TGT_MANAGER_ID = 200

            BEGIN

               DECLARE
                  @GET_DEPT_NAME_BY_MANGER$TGT_MANAGER_ID$2 numeric(6, 0)

               DECLARE
                  @GET_DEPT_NAME_BY_MANGER$DPT_NAME$2 varchar(30)

               SET @GET_DEPT_NAME_BY_MANGER$TGT_MANAGER_ID$2 = @PRING_DEPT_NAME_BY_MANAGER_200$TGT_MANAGER_ID

               SET @GET_DEPT_NAME_BY_MANGER$DPT_NAME$2 = @PRING_DEPT_NAME_BY_MANAGER_200$DPT_NAME

               BEGIN

                  SET @GET_DEPT_NAME_BY_MANGER$DPT_NAME$2 = NULL

                  SELECT @GET_DEPT_NAME_BY_MANGER$DPT_NAME$2 = DEPARTMENTS.DEPARTMENT_NAME
                  FROM HR.DEPARTMENTS
                  WHERE DEPARTMENTS.MANAGER_ID = @GET_DEPT_NAME_BY_MANGER$TGT_MANAGER_ID$2

               END

               SET @PRING_DEPT_NAME_BY_MANAGER_200$DPT_NAME = @GET_DEPT_NAME_BY_MANGER$DPT_NAME$2

            END

            PRINT @PRING_DEPT_NAME_BY_MANAGER_200$DPT_NAME

         END
      END

   END
GO
```

`local modules conversion`が`Stored procedure`の場合には、SSMA により次のように変換される。

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_002  
AS 
   BEGIN

      EXECUTE HR.NEST_SUBPROGRAM_002$PRING_DEPT_NAME_BY_MANAGER_100 

      EXECUTE HR.NEST_SUBPROGRAM_002$PRING_DEPT_NAME_BY_MANAGER_200 

   END
GO
```

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_002$GET_DEPT_NAME_BY_MANGER  
   @TGT_MANAGER_ID numeric(6, 0),
   @DPT_NAME varchar(30)  OUTPUT
AS 
   BEGIN

      SET @DPT_NAME = NULL

      SELECT @DPT_NAME = DEPARTMENTS.DEPARTMENT_NAME
      FROM HR.DEPARTMENTS
      WHERE DEPARTMENTS.MANAGER_ID = @TGT_MANAGER_ID

   END
GO
```

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_002$PRING_DEPT_NAME_BY_MANAGER_100  
AS 
   BEGIN

      DECLARE
         @TGT_MANAGER_ID numeric(6, 0), 
         @DPT_NAME varchar(30)

      SET @TGT_MANAGER_ID = 100

      EXECUTE HR.NEST_SUBPROGRAM_002$GET_DEPT_NAME_BY_MANGER @TGT_MANAGER_ID, @DPT_NAME  OUTPUT

      PRINT @DPT_NAME

   END
GO
```

```sql
CREATE PROCEDURE HR.NEST_SUBPROGRAM_002$PRING_DEPT_NAME_BY_MANAGER_200  
AS 
   BEGIN

      DECLARE
         @TGT_MANAGER_ID numeric(6, 0), 
         @DPT_NAME varchar(30)

      SET @TGT_MANAGER_ID = 200

      EXECUTE HR.NEST_SUBPROGRAM_002$GET_DEPT_NAME_BY_MANGER @TGT_MANAGER_ID, @DPT_NAME  OUTPUT

      PRINT @DPT_NAME

   END
GO
```