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 ](https://gist.github.com/ttlatex/10ce7f2cd1f44815727925f4474dd34c)をプロジェクトに配置しておく。DapperはODP.NETのカーソルや連想配列の情報を持っていないのでSqlMapper.IDynamicParametersを拡張したクラスが必要。

##今回使用したPL/SQL

```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;
/
```

##ファンクションでのカーソル受取
```csharp
        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を用いること


##プロシージャでのカーソル受取
```csharp
        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を用いること

##戻り値が複数ある場合
```csharp
        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メソッドを戻り値の個数分使用する必要がある


##連想配列受け渡し
```csharp
        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に指定すべきプロパティについて](http://space.geocities.jp/nequomame/dotnet/odpdotnet_02/odpdotnet_02_91_01.html)

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