Dapperを使ったPL/SQLの実行でカーソルや連想配列の取得を試した

はじめに

やりたいこと
・Dapperを利用してPL/SQLのカーソルを取得する
・Dapperを利用してPL/SQLの連想配列を取得する

環境
　.Net Framework 4.6.1
　Dapper 1.50.4
　ODP.NET 12.1.21

PL/SQL呼び出しコード

　準備としてOracleDynamicParameters.cs をプロジェクトに配置しておく。DapperはODP.NETのカーソルや連想配列の情報を持っていないのでSqlMapper.IDynamicParametersを拡張したクラスが必要。

今回使用したPL/SQL

CREATE OR REPLACE
PACKAGE A1_SAMPLE
IS
  FUNCTION F1(IN_ID IN VARCHAR2) RETURN SYS_REFCURSOR;
  PROCEDURE P1(IN_ID IN VARCHAR2, OUT_CUR OUT SYS_REFCURSOR);
  PROCEDURE P2(IN_ID IN VARCHAR2, OUT_CUR1 OUT SYS_REFCURSOR, OUT_CUR2 OUT SYS_REFCURSOR);
  TYPE STR_ARRAY IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
  PROCEDURE P3(IN_ID_LIST IN STR_ARRAY, OUT_NAME_LIST OUT STR_ARRAY);
END;
/

CREATE OR REPLACE
PACKAGE BODY A1_SAMPLE
IS

  FUNCTION F1(IN_ID IN VARCHAR2) RETURN SYS_REFCURSOR
  IS
    l_cursor SYS_REFCURSOR;
  BEGIN
    OPEN l_cursor FOR
      SELECT 'a' FIRST_NAME, 'b' LAST_NAME FROM DUAL;
    RETURN l_cursor;
  END;

  PROCEDURE P1(IN_ID IN VARCHAR2, OUT_CUR OUT SYS_REFCURSOR)
  IS
  BEGIN
    OPEN OUT_CUR FOR
      SELECT 'a' FIRST_NAME, 'b' LAST_NAME FROM DUAL;
  END;

  PROCEDURE P2(IN_ID IN VARCHAR2, OUT_CUR1 OUT SYS_REFCURSOR, OUT_CUR2 OUT SYS_REFCURSOR)
  IS
  BEGIN
    OPEN OUT_CUR1 FOR
      SELECT 'a' FIRST_NAME, 'b' LAST_NAME FROM DUAL;

    OPEN OUT_CUR2 FOR
      SELECT 'c' FIRST_NAME, 'd' LAST_NAME FROM DUAL;
  END;

  PROCEDURE P3(IN_ID_LIST IN STR_ARRAY, OUT_NAME_LIST OUT STR_ARRAY)
  IS
  BEGIN
    FOR I IN 1..IN_ID_LIST.COUNT LOOP
      SELECT 'a' INTO OUT_NAME_LIST(I) FROM DUAL;
    END LOOP;
  END;

END;
/

ファンクションでのカーソル受取

        public class MyDto
        {
            public string FIRST_NAME { get; set; }
            public string LAST_NAME { get; set; }
        }

        public static void F1()
        {
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                var param = new OracleDynamicParameters();
                param.Add("IN_ID", "1", dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
                param.Add("rv", dbType: OracleDbType.RefCursor, direction: ParameterDirection.ReturnValue);

                var cursor = connection.Query<MyDto>("A1_SAMPLE.F1", param, commandType: CommandType.StoredProcedure);
            }
        }

注意点
・バインドパラメータをもれなく宣言すること
・ファンクションの戻り値のバインドパラメータ名は任意の値(ここでは"rv")
・戻り値のパラメータにはParameterDirection.ReturnValueを用いること

プロシージャでのカーソル受取

        public class MyDto
        {
            public string FIRST_NAME { get; set; }
            public string LAST_NAME { get; set; }
        }

        public static void P1()
        {
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                var param = new OracleDynamicParameters();
                param.Add("IN_ID", "1", dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
                param.Add("OUT_CUR", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);

                var cursor = connection.Query<MyDto>("A1_SAMPLE.P1", param, commandType: CommandType.StoredProcedure);
            }
        }

注意点
・戻り値のパラメータにはParameterDirection.Outputを用いること

戻り値が複数ある場合

        public class MyDto
        {
            public string FIRST_NAME { get; set; }
            public string LAST_NAME { get; set; }
        }

        public static void P2()
        {
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                var param = new OracleDynamicParameters();
                param.Add("IN_ID", "1", dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
                param.Add("OUT_CUR1", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
                param.Add("OUT_CUR2", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);

                connection.Execute("A1_SAMPLE.P2", param, commandType: CommandType.StoredProcedure);
                var cursor1 = param.GetRefCursor<MyDto>("OUT_CUR1");
                var cursor2 = param.GetRefCursor<MyDto>("OUT_CUR2");
            }
        }

注意点
・ExecuteメソッドではなくQueryメソッドを使用した場合は片方の戻り値しか取得できない
・GetRefCursorメソッドを戻り値の個数分使用する必要がある

連想配列受け渡し

        public static void P3()
        {
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                var param = new OracleDynamicParameters();
                var input = new string[] { "1", "2", "3" };
                param.Add("IN_ID_LIST", input, dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input, collectionType: OracleCollectionType.PLSQLAssociativeArray);
                param.Add("OUT_NAME_LIST", dbType: OracleDbType.Varchar2, direction: ParameterDirection.Output, collectionType: OracleCollectionType.PLSQLAssociativeArray, size: 200, associateiveArrayCount: 200);

                connection.Execute("A1_SAMPLE.P3", param, commandType: CommandType.StoredProcedure);
                var output = param.Get<OracleString[]>("OUT_NAME_LIST").Select(x => x.Value).ToList();
            }
        }

注意点
・連想配列のパラメータにはOracleCollectionType.PLSQLAssociativeArrayを指定する必要がある
・可変長データが戻り値として返ってくる場合はあらかじめサイズを指定しておく必要がある。size: 要素のサイズ associateiveArrayCount: 要素数
・今回のプログラムではOracleDbType.Varchar2以外の可変長データは連想配列として受け取れない。(未実装)

補足

　受け渡しの型で主にVarchar2を使用しているが、他の型ではパラメータの設定方法が異なる。型による設定の違いは下記リンクが参考になる。
　（補足）OracleParameterに指定すべきプロパティについて

参考サイト
https://medium.com/@CodeCoo/call-oracle-store-procedure-with-dapper-c-b4176f636e11