かきスタンプ

福岡でフリーランスの物流系のエンジニアやってます。

PL/SQL:update文の where条件に、 配列を使用する

以前、配列を where句における in の条件式として使用するというエントリを書きましたが、update文にて、この書き方をすると、ORA-00902: データ型が無効です。というエラーが発生します。
 
ですので、update文の where 条件にて、配列を引数に取るには、forall を使ってバルク処理をする事になります。

<記述例>
  type TYPE_NUM_LIST   is table of number  index by binary_integer;
  PROCEDURE MY_PROCEDURE_02_1 (
     IN_NUM_LIST_PARAM_1        IN  TYPE_NUM_LIST
    ,OUT_RETURN_CODE            OUT NUMBER
  ) IS
  
  -- 【※重要※】 .NETの配列を引数に渡したとき、インデックスは1から開始する
  headerIndex number := 1;
  BEGIN

    --登録データがなかった場合、-1を返して終了する
    IF (IN_NUM_LIST_PARAM_1.COUNT <= 0) THEN
      OUT_RETURN_CODE := -1;
      RETURN;
    END IF;

    --トランザクション開始
    SET TRANSACTION NAME 'TRANSACTION_MY_PROCEDURE_02_1'; 

    --更新(まとめてupdate)
    forall i in headerIndex..IN_NUM_LIST_PARAM_1.count
      update  TABLE1
         set  COLUMN2 = 9
             ,COLUMN3 = 'Param1'
       where  1=1
         and  ID = IN_NUM_LIST_PARAM_1(i)
       ;

    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      OUT_RETURN_CODE := -1;
      ROLLBACK;

  END MY_PROCEDURE_02_1;

上記では、配列のインデックスを 1 から開始しています。
ODP.NET Managed Driver を使用し、.NET から Oracle に引数を渡して実行していますが、その時、配列のインデックスが、1 から開始するという、謎の挙動に頭を悩ませたからです。
 
インデックスを 0から開始させる方法を見つけきれなかったので、苦肉の策として上記のようなコードを書きました。

Oracle12c:PL/SQLのデバッグ実行に必要な設定【忙しい人向け】

Oracle 12cにて、PL/SQLデバッグするために必要な設定

  • DEBUG CONNECT SESSION 権限の許可
  • DBMS_DEBUG_JDWP に対する EXECUTE 権限
  • デバッグするストアド・プロシージャーに対する EXECUTE 権限
  • JDWP ACL 実行権限
  • ストアドプロシージャが "Compiled for Debug"であること

多すぎだろ!
 
 
という訳で、以下 PL/SQLデバッグ実行するために必要な設定を反映させるコマンド。

<注意点>
  • ローカルインストールされた DBを対象にしています。リモートに対して実行する場合、「localhost」「127.0.0.1」を、読み替えて下さい。
  • スキーマ名「KAKIP2」に権限を付与しています。適宜読み替えて下さい。
  • PUBLIC に権限を付与するのが嫌な方は、スキーマ名を指定してください。

 

SYSDBA 権限にて、sql plus から実行。
grant execute on SYS.UTL_ENCODE to PUBLIC;
grant execute on SYS.UTL_RAW to PUBLIC;
grant execute on SYS.UTL_I18N to PUBLIC
grant execute on SYS.UTL_SMTP to PUBLIC;
grant execute on SYS.DBMS_LOB to PUBLIC;
grant execute on SYS.DBMS_RANDOM to PUBLIC;
grant execute on SYS.DBMS_DEBUG_JDWP to PUBLIC;
grant execute on DBMS_DEBUG_JDWP to PUBLIC;

grant execute on SYS.UTL_HTTP to PUBLIC;
grant execute on SYS.UTL_TCP to PUBLIC;

grant debug connect session to PUBLIC;
grant debug any procedure to PUBLIC;
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
     acl         => 'localmail.xml',
     description => 'local mail acl',
     principal   => 'KAKIP2',
     is_grant    => true,
     privilege   => 'connect'
     );
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
     acl       => 'localmail.xml',
     principal => 'KAKIP2',
     is_grant  => true,
     privilege => 'resolve'
     );

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl  => 'localmail.xml',
     host => 'localhost'
     );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl  => 'localmail.xml',
     host => '127.0.0.1'
     );
END;
/
COMMIT;
begin
       dbms_network_acl_admin.append_host_ace
       (host=>'127.0.0.1',
        ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
                       principal_name=>'KAKIP2',
                       principal_type=>sys.XS_ACL.PTYPE_DB) );
    end;
/
後は、対象のファンクションを「デバッグ用にコンパイル

f:id:kakisoft:20180514235310p:plain

PL/SQL:配列を WHERE句における in の条件式として使用する

こんな感じ。

  type TYPE_NUM_LIST   is table of number  index by binary_integer;
  --////////////////////////////////
  --   配列データをINに渡して参照
  --////////////////////////////////
  PROCEDURE MY_PROCEDURE_02_4 (
     IN_NUM_LIST      IN  TYPE_NUM_LIST --配列型の引数
    ,OUT_LIST         OUT SYS_REFCURSOR     
    ,OUT_RETURN_CODE  OUT NUMBER
  ) IS

  BEGIN

    open OUT_LIST for 
      select
          *
      from
          TABLE1
      where  1=1
        and  ID in (select * from table(IN_NUM_LIST) )
    ;

  EXCEPTION
    WHEN OTHERS THEN
      OUT_RETURN_CODE := -1;
      ROLLBACK;

  END MY_PROCEDURE_02_4;

table関数を使って、配列をオブジェクト化しています。
 
 

【注意点】update文にて使用すると、エラーが発生します。

詳細は、こちらをご参照下さい。

Oracle(PL/SQL):配列型の引数に、値を設定してデバッグする

Oracle開発ツールのいくつかは、PL/SQL の引数が配列の場合、自由に値がセットできなくて、苦労するケースもあるかと思います。
 
Oracle SQL Developer のデバッグ実行にて、『PL/SQL ブロック』を編集することで、配列型の引数に、好きな値を設定する事ができます。

f:id:kakisoft:20180510224409p:plain

Oracle SQL Developer:出力結果を、列名を含めてコピー

Oracle SQL Developerにて、select文の結果を、「Ctrl + A」「Ctrl + C」⇒「Ctrl + V」と操作すると、レコードのみがペーストされます。
 
「Ctrl + A」「Ctrl + Shift + C」⇒「Ctrl + V」で、列名を含めてコピーが可能です。 f:id:kakisoft:20180510223512p:plain

f:id:kakisoft:20180510223517p:plain

WPF:DataGrid の ItemsSource に、配列データを設定すると妙な出力結果になるんで、その場合は ListView を使おうという話

郵便番号/住所/緯度経度データ等の地理情報を、XML or JSON で取得できるサービス『HeartRails Geo API』にて、エリア情報を叩くと、こういう値が返ってきます。

{
    "response": {
        "area": [
            "北海道",
            "東北",
            "関東",
            "中部",
            "近畿",
            "中国",
            "四国",
            "九州"
        ]
    }
}

この結果を、グリッドの ItemsSource に流し込んで、いい感じに表示しようと思い、以下のように書いたら、何とも微妙な結果になりました。  
 
Http リクエストを投げるところと、デシリアライズのところは省略しています。
また、受け側は dynamic型にしています。
(フォーム部分は省略しています。といっても、「myDataGrid01」という Name の DataGrid があれば何でもいい訳ですが。)

csソース1
List<dynamic> areaList = new List<dynamic>();
foreach (var item in responseData.response.area) // 「responseData」にデシリアライズされたデータが格納されています。
{
    areaList.Add(item);
}
myDataGrid01.ItemsSource = areaList;
実行結果1

f:id:kakisoft:20180430193003p:plain  
余計なものが色々入ってます。 必用なのは Valueなので、指定して抽出。

csソース2
List<dynamic> areaList = new List<dynamic>();
foreach (var item in responseData.response.area)
{
    areaList.Add(item.Value);
}
myDataGrid01.ItemsSource = areaList;
実行結果2

f:id:kakisoft:20180430193006p:plain  

なぜか Lengthが取れるという妙な結果に・・・。
json のパースの仕方がどうこうという訳でなく、DataGrid の ItemsSource に、配列を放り込むと、こうなるみたいです。
なので、List<string> のデータでも、同様の現象が起こります。

対策

どうしても DataGridで表現しなければならないなら、配列の部分を Dictionaryにする or 自作のクラスを定義して、それに放り込むかですが、本来のデータに不純物を入れ込む事になるので、ちょっと避けたい。

という訳で、こういった場合は DataGridでなく、ListView を使う方法が最良ではないでしょうか。
以下では、「myListView01」という Name の ListView を定義しています。

csソース3
List<dynamic> areaList = new List<dynamic>();
foreach (var item in responseData.response.area)
{
    areaList.Add(item);
}
myListView01.ItemsSource = areaList;
実行結果3

f:id:kakisoft:20180430193009p:plain  
 
 
DataGrid は色々できて便利なんですが、その分操作が難しかったり、変にバッドノウハウを知っておかないとハマる原因になったりするので、やる事がシンプルなら、ListViewでいい気がします。

C#:DBから取得したModelのリストを、Modelを継承した要素で表現する

こういう状況。

  • Model定義されたクラスがある
  • Model のデータは、DBなどから参照し、List にしている
  • List の中身を、Model を拡張したクラスで表現したい(画面制御のみで使用するプロパティを付与したい)
(例)
List<Users>        //Model(のリスト)
List<ExtendUsers>  //Modelを拡張したクラス(のリスト)

こういうのがあって、List<ExtendUsers> に、List<Users> の要素をブチ込みたい、といったケース。

ストレートにダウンキャストが使えないんで、以下のような工夫をしてみた。
 
分割しているのは、全部繋げるとスクロールバーが一番下にしか来なくて、読みづらかったためです。

//親クラス
class Users
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Position { get; set; }
}
//子クラス
class ExtendUsers : Users
{
    public bool IsChecked { get; set; } //画面の制御用として、子だけで使いたいプロパティ

    public ExtendUsers(Users value) //コンストラクタの引数に、親のインスタンスを渡す
    {
        //---------------------
        //  親要素の全プロパティをリストアップし、子に同じ値を設定
        //---------------------
        PropertyInfo[] propertyInfoinfoArray = value.GetType().GetProperties();  //プロパティをリストアップ
        foreach (PropertyInfo item in propertyInfoinfoArray) //リストアップしたプロパティをループで回す
        {
            var property = value.GetType().GetProperty(item.Name);  //プロパティを取得
            property.SetValue(this, item.GetValue(value));          //子に親と同じ値をセット
        }
    }
}
//Model定義されたクラスのリスト(ここに書いてると、そう見えないけど、そういう事にしといて下さい。)
private List<Users> _users;
//Model定義されたクラスを拡張した要素を詰め込んだリスト
private List<ExtendUsers> _extendUsers;

private void MyButton01_Click()
{
    _extendUsers = new List<ExtendUsers>();
    foreach (var item in _users) //Model定義された要素が入ったリストをループ回す
    {
        ExtendUsers el = new ExtendUsers(item); //子のインスタンス作成時、親のインスタンスを引数に渡す。(詳細は上記を参照)
        _extendUsers.Add(el); //親のインスタンスと同じ値を設定した子を、リストに追加
    }
}
//============================
// サンプルソースを動かすために用意した。本当はDBから引っ張ってくる
//============================
private void SetUsersList()
{
    _users = new List<Users>();
    _users.Add(new Users { Id = 1, Name = "Tanaka", Position = 1 });
    _users.Add(new Users { Id = 2, Name = "Yamada", Position = 1 });
    _users.Add(new Users { Id = 3, Name = "Watanabe", Position = 2 });
}
概要
  • 親要素が入ったリストをループで回している
  • 子のインスタンスを作成する時、コンストラクタに親のインスタンスを渡している
  • コンストラクタにて、親のプロパティの一覧を精査し、同じ値を子に設定している

 
 
もっといい方法がありそうだけど、今の自分にはこの辺が限界。