LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@manabian

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

Last updated at Posted at 2023-04-09

概要

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

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

Cursor について

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

特定のSELECT文またはDML文の処理に関する情報を格納しておく、SQLのプライベート領域を指すポインタです。

引用元：6.2 カーソルの概要 (oracle.com)

参考リンク

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

Azure SQL Database への移行

SSMA による変換

Oracle Database の Cursor を置換するロジックについては、次のホワイトペーパーに記載されている。

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

移行方針

特になし。

変換例

変換例 1 For ループのカーソル変換

Oracle Database のコード
DECLARE
    CURSOR emp_cur IS SELECT * FROM HR.departments WHERE manager_id IS NULL;
BEGIN
    FOR emp_rec IN emp_cur LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.DEPARTMENT_ID||' ' || emp_rec.DEPARTMENT_NAME);
    END LOOP;
END;
Azure SQL Database への変換

次の T-SQL のコードに変換される。

BEGIN

   DECLARE
      @EMP_REC$DEPARTMENT_ID numeric(4, 0), 
      @EMP_REC$DEPARTMENT_NAME varchar(30), 
      @EMP_REC$MANAGER_ID numeric(6, 0), 
      @EMP_REC$LOCATION_ID numeric(4, 0)

   DECLARE
       EMP_CUR CURSOR LOCAL FORWARD_ONLY FOR 
         SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
         FROM HR.DEPARTMENTS
         WHERE DEPARTMENTS.MANAGER_ID IS NULL

   OPEN EMP_CUR

   WHILE 1 = 1
   
      BEGIN

         FETCH EMP_CUR
             INTO @EMP_REC$DEPARTMENT_ID, @EMP_REC$DEPARTMENT_NAME, @EMP_REC$MANAGER_ID, @EMP_REC$LOCATION_ID

         IF @@FETCH_STATUS = -1
            BREAK

         PRINT ISNULL(CAST(@EMP_REC$DEPARTMENT_ID AS nvarchar(max)), '') + ' ' + ISNULL(@EMP_REC$DEPARTMENT_NAME, '')

      END

   CLOSE EMP_CUR

   DEALLOCATE EMP_CUR

END
GO

上記のコードを実行すると、下記が出力される。

120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll

変換例 2 パラメータ付きカーソル変換

Oracle Database のコード
DECLARE
    CURSOR emp_cur(tgt_manager_id IN HR.departments.manager_id%type) IS SELECT * FROM HR.departments WHERE manager_id = tgt_manager_id;
BEGIN
    DBMS_OUTPUT.PUT_LINE('-- manger_id:100');
    FOR emp_rec IN emp_cur(100) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.DEPARTMENT_ID||' ' || emp_rec.DEPARTMENT_NAME);
    END LOOP;
END;
Azure SQL Database への変換

次の T-SQL のコードに変換される。

BEGIN

   PRINT '-- manger_id:100'

   DECLARE
      @EMP_REC$DEPARTMENT_ID numeric(4, 0), 
      @EMP_REC$DEPARTMENT_NAME varchar(30), 
      @EMP_REC$MANAGER_ID numeric(6, 0), 
      @EMP_REC$LOCATION_ID numeric(4, 0)

   DECLARE
      @CURSOR_PARAM_EMP_CUR_TGT_MANAGER_ID numeric(6, 0)

   SET @CURSOR_PARAM_EMP_CUR_TGT_MANAGER_ID = 100

   DECLARE
       EMP_CUR CURSOR LOCAL FORWARD_ONLY FOR 
         SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
         FROM HR.DEPARTMENTS
         WHERE DEPARTMENTS.MANAGER_ID = @CURSOR_PARAM_EMP_CUR_TGT_MANAGER_ID

   OPEN EMP_CUR

   WHILE 1 = 1
   
      BEGIN

         FETCH EMP_CUR
             INTO @EMP_REC$DEPARTMENT_ID, @EMP_REC$DEPARTMENT_NAME, @EMP_REC$MANAGER_ID, @EMP_REC$LOCATION_ID

         IF @@FETCH_STATUS = -1
            BREAK

         PRINT ISNULL(CAST(@EMP_REC$DEPARTMENT_ID AS nvarchar(max)), '') + ' ' + ISNULL(@EMP_REC$DEPARTMENT_NAME, '')

      END

   CLOSE EMP_CUR

   DEALLOCATE EMP_CUR

END
GO

上記のコードを実行すると、下記が出力される。

-- manger_id:100
90 Executive

変換例 3 WHERE CURRENT OF 句による更新処理

Oracle Database のコード
DECLARE
  CURSOR emp_cur IS SELECT * FROM HR.departments WHERE manager_id IS NULL FOR UPDATE;
BEGIN
  FOR emp_rec IN emp_cur LOOP
    IF emp_rec.DEPARTMENT_ID < 200 THEN
      UPDATE HR.departments SET DEPARTMENT_ID = DEPARTMENT_ID
          WHERE CURRENT OF emp_cur;
    END IF;
  END LOOP;
END;
Azure SQL Database への変換

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

BEGIN

   DECLARE
      @EMP_REC$DEPARTMENT_ID numeric(4, 0), 
      @EMP_REC$DEPARTMENT_NAME varchar(30), 
      @EMP_REC$MANAGER_ID numeric(6, 0), 
      @EMP_REC$LOCATION_ID numeric(4, 0)

   DECLARE
       EMP_CUR CURSOR LOCAL FORWARD_ONLY FOR 
         SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
         FROM HR.DEPARTMENTS 
            WITH ( UPDLOCK )
         WHERE DEPARTMENTS.MANAGER_ID IS NULL

   OPEN EMP_CUR

   WHILE 1 = 1
   
      BEGIN

         FETCH EMP_CUR
             INTO @EMP_REC$DEPARTMENT_ID, @EMP_REC$DEPARTMENT_NAME, @EMP_REC$MANAGER_ID, @EMP_REC$LOCATION_ID

         IF @@FETCH_STATUS = -1
            BREAK

         IF @EMP_REC$DEPARTMENT_ID < 200
            UPDATE HR.DEPARTMENTS
               SET 
                  DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
            WHERE CURRENT OF EMP_CUR

      END

   CLOSE EMP_CUR

   DEALLOCATE EMP_CUR

END
GO

変換例 4 OPEN-FOR-USING によるカーソル処理

Oracle Database のコード

OPEN-FOR-USING によるカーソル処理を実施しているコードの変換仕様を説明する。

DECLARE
    TYPE cv_type IS REF CURSOR;
    cur_cv         cv_type;
    dpt_rec        hr.departments%rowtype;
    tgt_manager_id NUMBER(10);
BEGIN
    tgt_manager_id := 200;
    OPEN cur_cv FOR 'SELECT * FROM HR.departments
          WHERE  manager_id = :val'
        USING tgt_manager_id;

    LOOP
        FETCH cur_cv INTO dpt_rec;
        EXIT WHEN cur_cv%notfound;
        dbms_output.put_line(dpt_rec.department_id
                             || ' '
                             || dpt_rec.department_name);
    END LOOP;

    CLOSE cur_cv;
END;
Azure SQL Database への変換

次の T-SQL のコードに変換される。

BEGIN

   DECLARE
      @CUR_CV CURSOR

   DECLARE
      @DPT_REC$DEPARTMENT_ID numeric(4, 0), 
      @DPT_REC$DEPARTMENT_NAME varchar(30), 
      @DPT_REC$MANAGER_ID numeric(6, 0), 
      @DPT_REC$LOCATION_ID numeric(4, 0), 
      @TGT_MANAGER_ID numeric(10)

   SET @TGT_MANAGER_ID = 200

   DECLARE
      @auxiliary_cursor_definition_sql nvarchar(max)

   DECLARE
      @auxiliary_exec_param nvarchar(max)

   IF (cursor_status('variable', N'@CUR_CV') > -2)
      DEALLOCATE @CUR_CV

   SET @auxiliary_cursor_definition_sql = 'SET @auxiliary_tmp_cursor = CURSOR LOCAL FOR ' + 'SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
FROM HR.DEPARTMENTS
WHERE DEPARTMENTS.MANAGER_ID = @VAL' + '; OPEN @auxiliary_tmp_cursor'

   SET @auxiliary_exec_param = '@VAL float(53) ,@auxiliary_tmp_cursor cursor OUTPUT'

   EXECUTE sp_executesql @auxiliary_cursor_definition_sql, @auxiliary_exec_param, @TGT_MANAGER_ID, @CUR_CV  OUTPUT

   WHILE 1 = 1
   
      BEGIN

         FETCH @CUR_CV
             INTO @DPT_REC$DEPARTMENT_ID, @DPT_REC$DEPARTMENT_NAME, @DPT_REC$MANAGER_ID, @DPT_REC$LOCATION_ID

         /*
         *   SSMA warning messages:
         *   O2SS0113: The value of @@FETCH_STATUS might be changed by previous FETCH operations on other cursors, if the cursors are used simultaneously.
         */

         IF @@FETCH_STATUS <> 0
            BREAK

         PRINT ISNULL(CAST(@DPT_REC$DEPARTMENT_ID AS nvarchar(max)), '') + ' ' + ISNULL(@DPT_REC$DEPARTMENT_NAME, '')

      END

   CLOSE @CUR_CV

   DEALLOCATE @CUR_CV

END
GO

上記のコードを実行すると、下記が出力される。

10 Administration

変換例 5 カーソル属性の変換

Oracle Database のコード

カーソル属性の変換を実施しているコードの変換仕様を説明する。

DECLARE 
    CURSOR emp_cur IS SELECT * FROM HR.departments WHERE manager_id IS NULL;
    emp_rec emp_cur%ROWTYPE;
BEGIN
    OPEN emp_cur;
        LOOP
            FETCH emp_cur INTO emp_rec;
            EXIT WHEN emp_cur%NOTFOUND;
                DBMS_OUTPUT.put_line(emp_rec.DEPARTMENT_NAME||' '||emp_rec.DEPARTMENT_NAME);
        END LOOP;
    CLOSE emp_cur;
END;
Azure SQL Database への変換

次の T-SQL のコードに変換される。

BEGIN

   DECLARE
      @EMP_REC$DEPARTMENT_ID numeric(4, 0), 
      @EMP_REC$DEPARTMENT_NAME varchar(30), 
      @EMP_REC$MANAGER_ID numeric(6, 0), 
      @EMP_REC$LOCATION_ID numeric(4, 0)

   DECLARE
       EMP_CUR CURSOR LOCAL FOR 
         SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
         FROM HR.DEPARTMENTS
         WHERE DEPARTMENTS.MANAGER_ID IS NULL

   OPEN EMP_CUR

   WHILE 1 = 1
   
      BEGIN

         FETCH EMP_CUR
             INTO @EMP_REC$DEPARTMENT_ID, @EMP_REC$DEPARTMENT_NAME, @EMP_REC$MANAGER_ID, @EMP_REC$LOCATION_ID

         /*
         *   SSMA warning messages:
         *   O2SS0113: The value of @@FETCH_STATUS might be changed by previous FETCH operations on other cursors, if the cursors are used simultaneously.
         */

         IF @@FETCH_STATUS <> 0
            BREAK

         PRINT ISNULL(@EMP_REC$DEPARTMENT_NAME, '') + ' ' + ISNULL(@EMP_REC$DEPARTMENT_NAME, '')

      END

   CLOSE EMP_CUR

   DEALLOCATE EMP_CUR

END
GO

上記のコードを実行すると、下記が出力される。

Treasury Treasury
Corporate Tax Corporate Tax
Control And Credit Control And Credit
Shareholder Services Shareholder Services
Benefits Benefits
Manufacturing Manufacturing
Construction Construction
Contracting Contracting
Operations Operations
IT Support IT Support
NOC NOC
IT Helpdesk IT Helpdesk
Government Sales Government Sales
Retail Sales Retail Sales
Recruiting Recruiting
Payroll Payroll

変換例 6 Oracle データベースリンク機能によりテーブルを参照しているカーソル処理

Oracle Database のコード
DECLARE
    CURSOR emp_cur IS SELECT * FROM HR.departments@test.test.test WHERE manager_id IS NULL;
BEGIN
    FOR emp_rec IN emp_cur LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.DEPARTMENT_ID||' ' || emp_rec.DEPARTMENT_NAME);
    END LOOP;
END;
Azure SQL Database への変換

次の T-SQL のコードに変換される。Oracle データベースリンク機能によりテーブルを参照しているカーソル処理を変換する場合には、 O2SS0083 、あるいは、O2SS0556 の issues が発生する。

BEGIN

   DECLARE
      /* 
      *   SSMA error messages:
      *   O2SS0005: The source datatype 'emp_cur%ROWTYPE' was not recognized.
      */

      @EMP_REC varchar(8000)

   /* 
   *   SSMA error messages:
   *   O2SS0200: Select list item * cannot be converted.
   *   O2SS0563: Conversion of database link 'HR.departments@TEST.TEST.TEST' is not supported.
   *   O2SS0083: Identifier manager_id cannot be converted because it was not resolved.

   DECLARE
       EMP_CUR CURSOR LOCAL FORWARD_ONLY FOR 
         SELECT 
         FROM HR.DEPARTMENTS
         WHERE MANAGER_ID IS NULL
   */



   OPEN EMP_CUR

   /* 
   *   SSMA error messages:
   *   O2SS0256: Cannot get row description in FOR loop query or cursor 'emp_cur'.

   WHILE 1 = 1
   
      BEGIN

         FETCH EMP_CUR
             INTO @EMP_REC

         IF @@FETCH_STATUS = -1
            BREAK

         /* 
         *   SSMA error messages:
         *   O2SS0083: Identifier emp_rec.DEPARTMENT_ID cannot be converted because it was not resolved.
         *   O2SS0083: Identifier emp_rec.DEPARTMENT_NAME cannot be converted because it was not resolved.

         PRINT ISNULL(CAST(EMP_REC.DEPARTMENT_ID AS nvarchar(max)), '') + ' ' + ISNULL(CAST(EMP_REC.DEPARTMENT_NAME AS nvarchar(max)), '')
         */



      END
   */



   CLOSE EMP_CUR

   DEALLOCATE EMP_CUR

END
GO
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?