.Net（VB C#）　LINQのメソッド一覧

LINQのメソッド一覧です。

メソッド名	実行方法	機能	サンプル
Select	遅延実行	シーケンスの各要素を新しいフォームに射影します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
Where	遅延実行	述語に基づいて値のシーケンスをフィルター処理します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
OrderBy	遅延実行	シーケンスの要素を昇順に並べ替えます。	ｻﾝﾌﾟﾙｺｰﾄﾞ
ThenBy	遅延実行	シーケンス内の後続の要素を昇順で配置します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
OrderByDescending	遅延実行	シーケンスの要素を降順に並べ替えます。	ｻﾝﾌﾟﾙｺｰﾄﾞ
ThenByDescending	遅延実行	シーケンス内の後続の要素を降順で配置します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
Cast	遅延実行	IEnumerable の要素を、指定した型にキャストします。	ｻﾝﾌﾟﾙｺｰﾄﾞ
OfType	遅延実行	指定された型に基づいて IEnumerable の要素をフィルター処理します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
ElementAt	即時実行	シーケンス内の指定されたインデックス位置にある要素を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
ElementAtOrDefault	即時実行	シーケンス内の指定されたインデックス位置にある要素を返します。インデックスが範囲外の場合は既定値を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
First	即時実行	シーケンスの最初の要素を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
FirstOrDefault	即時実行	シーケンスの最初の要素を返します。要素が見つからない場合は既定値を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
Last	即時実行	シーケンスの最後の要素を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
LastOrDefault	即時実行	シーケンスの最後の要素を返します。要素が見つからない場合は既定値を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
Single	即時実行	シーケンスの 1 つの特定の要素を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
SingleOrDefault	即時実行	シーケンスの 1 つの特定の要素を返します。そのような要素が見つからない場合は既定値を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
Take	遅延実行	シーケンスの先頭から、指定された数の連続する要素を返します。	ｻﾝﾌﾟﾙｺｰﾄﾞ
TakeWhile	遅延実行	指定された条件を満たされる限り、シーケンスから要素を返した後、残りの要素をスキップします。	ｻﾝﾌﾟﾙｺｰﾄﾞ
Skip	遅延実行	シーケンス内の指定された数の要素をバイパスし、残りの要素を返します。
SkipWhile	遅延実行	指定された条件が満たされる限り、シーケンスの要素をバイパスした後、残りの要素を返します。
Contains	即時実行	指定した要素がシーケンスに格納されているかどうかを判断します。
All	即時実行	シーケンスのすべての要素が条件を満たしているかどうかを判断します。
Any	即時実行	シーケンスに要素が含まれているかどうかを判断します。
SequenceEqual	即時実行	要素の型に対して既定の等値比較子を使用して要素を比較することで、2 つのシーケンスが等しいかどうかを判断します。
Reverse	遅延実行	シーケンスの要素の順序を反転させます。
Repeat	遅延実行	繰り返される 1 つの値を含むシーケンスを生成します。
GroupBy	遅延実行	シーケンスの要素をグループ化します。
Distinct	遅延実行	シーケンスから一意の要素を返します。
Concat	遅延実行	2 つのシーケンスを連結します。
SelectMany	遅延実行	シーケンスの各要素を IEnumerable(Of T) に射影し、結果のシーケンスを 1 つのシーケンスに平坦化します。
Zip	遅延実行	2 つのシーケンスの対応する要素に対して、1 つの指定した関数を適用し、結果として 1 つのシーケンスを生成します。
Range	遅延実行	指定した範囲内の整数のシーケンスを生成します。
Count	即時実行	シーケンス内の要素数を返します。
LongCount	即時実行	シーケンス内の要素数を表す Int64 を返します。
Max	即時実行	値のシーケンスの最大値を返します。
Min	即時実行	値のシーケンスの最小値を返します。
Average	即時実行	数値のシーケンスの平均値を計算します。
Sum	即時実行	数値のシーケンスの合計を計算します。
ToArray	即時実行	IEnumerable から配列を作成します。
ToDictionary	即時実行	IEnumerable から Dictionary を作成します。
ToList	即時実行	IEnumerable から List を作成します。
ToLookup	即時実行	IEnumerable からジェネリックの Lookup を作成します。
Join	遅延実行	一致するキーに基づいて 2 つのシーケンスの要素を相互に関連付けます。
GroupJoin	遅延実行	キーが等しいかどうかに基づいて 2 つのシーケンスの要素を相互に関連付け、その結果をグループ化します。
Intersect	遅延実行	2 つのシーケンスの積集合を生成します。
Union	遅延実行	2 つのシーケンスの和集合を生成します。
Except	遅延実行	2 つのシーケンスの差集合を生成します。
Empty	即時実行	指定した型引数を持つ空の IEnumerable を返します。
DefaultIfEmpty	即時実行	IEnumerable の要素を返します。シーケンスが空の場合は既定値を持つシングルトンコレクションを返します。
AsEnumerable	即時実行	IEnumerable として型指定された入力を返します。

PLSQL 制御文～GOTO文とラベル、NULL文～

PLSQLではGOTO文が使用できます。
しかし、他の言語同様に多用するのは厳禁です。

11g以前ではループのスキップにcontinue文が使用できないので、GOTO文を使用する機会があるかと思います。

GOTO文とラベル、NULL文の基本的な使い方

まずはGOTO文とラベルの使い方です。
ラベルは「「<<ラベル名>>」と定義します。
GOTO文で「GOTO ラベル名;」とすることで、指定したラベルに制御を移すことができます。
処理がないラベルを定義する事はできません。
ラベルの処理が何もない場合はNULL文を使用します。 NULL文は何もしないステートメントを意味します。

DECLARE
    num NUMBER := &任意の数値;
BEGIN
    
    IF (num < 10) THEN
        GOTO under_ten;
    ELSE
        GOTO over_ten;
    END IF;
    
    <<under_ten>>
        SYS.DBMS_OUTPUT.PUT_LINE('10以下');
    
    <<over_ten>>
        NULL;
END;

ループのContinue文の代わりにGOTO文を使用する（11g以前）

11g以前ではContinue文が使用できないので、ループのスキップにGOTO文を使用することがあります。
※11g以降はcontinue文を使用したほうがよいと思います。
Continue文についてはコチラ「PLSQL 制御文～LOOP文～」

以下はGOTO文を使用した、ループのスキップです。
ループカウンタが偶数の場合は、ループ処理をスキップします。

DECLARE
    
BEGIN

    FOR num IN 1..10 LOOP
        IF (num Mod 2 = 0) THEN
            GOTO continue;
        END IF;
        
        SYS.DBMS_OUTPUT.PUT_LINE(num);
        
        <<continue>>
            NULL;
    END LOOP;

END;

ループラベル

ループ開始の直前にラベルを付けるとそのラベルは「ループラベル」になります。
11g以降ではContinue文が使用できますが、「Continue ラベル;」や「Continue ラベル WHEN 条件;」と書くことで、スキップするループを指定することができます。
同様にEXIT文についても「Exit ラベル;」や「Exit ラベル WHEN 条件;」と書くことで、終了するループを指定することができます。
ループをネストした多重ループで、スキップしたいループや終了したいループを指定するときに使用します。

DECLARE
 
BEGIN

    <<loop_1>>
    FOR i IN 1..3 LOOP
         
        <<loop_2>>
        FOR j IN 1..3 LOOP
            
            <<loop_3>>       
            FOR k iN 1..3 LOOP
                dbms_output.put_line('i=' || i || ', j=' || j || ', k=' || k );
                
                --j=2はループ2をスキップ
                CONTINUE loop_2 WHEN ( j = 2);
                --j=3はループ1をスキップ
                IF (j = 3) THEN
                    CONTINUE loop_1;
                END IF;
                
                
                --i=2はループ3を終了
                EXIT loop_3 WHEN ( i = 3);
                --i=3はループ2を終了
                IF (i = 3) THEN
                    EXIT loop_2;
                END IF;
               
            
            END LOOP; --loop1
            
        END LOOP; --loop2
    
    END LOOP; --loop3

   dbms_output.put_line('ループ終了');
END;

出力結果
i=1, j=1, k=1
i=1, j=1, k=2
i=1, j=1, k=3
i=1, j=2, k=1
i=1, j=3, k=1
i=2, j=1, k=1
i=2, j=1, k=2
i=2, j=1, k=3
i=2, j=2, k=1
i=2, j=3, k=1
i=3, j=1, k=1
i=3, j=2, k=1
i=3, j=3, k=1
ループ終了

PLSQL 暗黙カーソル

前回までのカーソルの使い方についてはコチラ
PLSQL SELECTの結果を取得する～取得結果が複数行の場合～
PLSQL カーソルを使用してデータを取得する
PLSQL パラメータ付きカーソル
PLSQL カーソル属性

カーソルには「明示カーソル」と「暗黙カーソル」の2種類があります。

明示カーソル

カーソルに名前をつけているものを明示カーソルと呼びます。
前回までのカーソルを使用したサンプルでは、以下のサンプルのようにDECLARE部でカーソルに対して名前を付けています。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
        
    …以下略…

暗黙カーソル

明示カーソル以外で埋め込まれたSQLが実行されるとき、Oracleによって暗黙的にカーソルが作成され実行されます。

次のコードは暗黙カーソルを使用したSELECT文、UPDATE文です。

DECLARE
    membername Member.MemberName%Type;
    
BEGIN
    
    --暗黙カーソルを使用したSELET文
    SELECT MemberName Rank INTO membername
        FROM Member 
        WHERE MemberId = '1';
        
    --暗黙カーソルを使用したSELET文のLOOP
    FOR rec IN (SELECT * FROM Member) LOOP
        SYS.DBMS_OUTPUT.PUT_LINE(rec.MemberId || ' ' || rec.MemberName);
    END LOOP;
    
    --暗黙カーソルを使用したUPDATE文
    UPDATE Member 
        SET MemberName = 'ishida', Rank = 'C'
        WHERE MemberId = '1';

END;

この暗黙カーソルにも明示カーソルと同じく属性があります。
暗黙カーソルのカーソル名は「SQL」になり、属性値を調べるには「SQL%属性名」とします。
但しカーソル名は「SQL」しかないため、直前に実行したSQLの属性しか調べることができません。

属性	意味
FOUND	カーソルを実行した結果、該当するものがあったかどうか
NOTFOUND	FOUNDの逆値
ISOPEN	カーソルが開いているかどうか暗黙カーソルでは常にfalse
ROWCOUNT	カーソルを処理した結果、処理された行数 SELECTの場合フェッチした件数

下記のコードでは暗黙カーソルの属性を調べています。

DECLARE
    membername Member.MemberName%Type;
    
BEGIN 
          
    -- ■ 暗黙カーソルを使用したSELET文のカーソルFORループ ■ --
    --出力結果
    --ISOPEN=false
    --FOUND=false
    --ROWCOUNT=
    
    FOR rec IN (SELECT * FROM Member WHERE MemberId = '1') LOOP
    
        --ISOPEN属性の確認
        IF (SQL%ISOPEN) THEN
            SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=true');
        ELSE
            SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=false');
        END IF;
      
        --FOUND属性の確認
        IF (SQL%FOUND) THEN
            SYS.DBMS_OUTPUT.PUT_LINE('FOUND=true');
        ELSE
            SYS.DBMS_OUTPUT.PUT_LINE('FOUND=false');
        END IF;
      
        --ROWCOUNT属性の確認
        SYS.DBMS_OUTPUT.PUT_LINE('ROWCOUNT=' || SQL%ROWCOUNT);
        
    END LOOP;
    
    -- ■ 暗黙カーソルを使用したSELET文 ■ --
    --出力結果
    --ISOPEN=false
    --FOUND=true
    --ROWCOUNT=1
    
    SELECT MemberName INTO membername
        FROM Member 
        WHERE MemberId = '1';
        
    --ISOPEN属性の確認
    IF (SQL%ISOPEN) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=true');
    ELSE
        SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=false');
    END IF;
    
    --FOUND属性の確認
    IF (SQL%FOUND) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('FOUND=true');
    ELSE
        SYS.DBMS_OUTPUT.PUT_LINE('FOUND=false');
    END IF;
    
    --ROWCOUNT属性の確認
    SYS.DBMS_OUTPUT.PUT_LINE('ROWCOUNT=' || SQL%ROWCOUNT);
    
    -- ■ 暗黙カーソルを使用したUPDATE文 ■ --
    --出力結果
    --ISOPEN=false
    --FOUND=true
    --ROWCOUNT=2
    
    UPDATE Member 
        SET MemberName = 'ishida', Rank = 'C'
        WHERE MemberId > '1';
 
     --ISOPEN属性の確認
    IF (SQL%ISOPEN) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=true');
    ELSE
        SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=false');
    END IF;
    
    --FOUND属性の確認
    IF (SQL%FOUND) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('FOUND=true');
    ELSE
        SYS.DBMS_OUTPUT.PUT_LINE('FOUND=false');
    END IF;
    
    --ROWCOUNT属性の確認
    SYS.DBMS_OUTPUT.PUT_LINE('ROWCOUNT=' || SQL%ROWCOUNT);       
END;

暗黙カーソルを使用したカーソルFORループでは属性が何も取れないのですね。
明示カーソルを使用したカーソルFORループでの各属性は、コチラ「PLSQL カーソル属性」に書いてます。

PLSQL カーソル属性

前回までのカーソルの使い方についてはコチラ
PLSQL SELECTの結果を取得する～取得結果が複数行の場合～
PLSQL カーソルを使用してデータを取得する
PLSQL パラメータ付きカーソル

今回はカーソルの属性についてです。

カーソルには下記の表のような属性があります。
「カーソル名%属性名」とすることで、属性の値をを取得できます。

属性	意味
FOUND	カーソルを実行した結果、該当するものがあったかどうか
NOTFOUND	FOUNDの逆値
ISOPEN	カーソルが開いているかどうか
ROWCOUNT	カーソルを処理した結果、処理された行数 SELECTの場合フェッチした件数

カーソル属性を確認するために、複数件の結果を返すSELECT文で各属性を出力するコードを実行してみました。

DECLARE
   CURSOR cur IS 
       SELECT * FROM Member;
       
   rec cur%Rowtype;
BEGIN
    --カーソル状態出力
    If (cur%ISOPEN) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('カーソルオープン前:ISOPEN=true');
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('カーソルオープン前:ISOPEN=false');   
    END IF;
    
    --カーソルオープン
    OPEN cur;
    
    --カーソル状態出力
    If (cur%ISOPEN) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('カーソルオープン後:ISOPEN=true');
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('カーソルオープン後:ISOPEN=false');   
    END IF;
    
    
    LOOP
        FETCH cur INTO rec;  
        If (cur%NOTFOUND) THEN
            SYS.DBMS_OUTPUT.PUT_LINE('NOTFOUND = true');  
            EXIT;
        END IF;
        
        SYS.DBMS_OUTPUT.PUT_LINE('フェッチした件数:ROWCOUNT=' || cur%ROWCOUNT);   
        SYS.DBMS_OUTPUT.PUT_LINE(rec.MemberId || ' ' || rec.MemberName);      
    END LOOP;
    

    --カーソルクローズ
    CLOSE cur;

    --カーソル状態出力
    If (cur%ISOPEN) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('カーソルクローズ後:ISOPEN=true');
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('カーソルクローズ後:ISOPEN=false');   
    END IF;
END;

出力結果です。
カーソルオープン前:ISOPEN=false
カーソルオープン後:ISOPEN=true
フェッチした件数:ROWCOUNT=1
1 Yamada
フェッチした件数:ROWCOUNT=2
2 Tanaka
フェッチした件数:ROWCOUNT=3
3 Suzuki
NOTFOUND = true
カーソルクローズ後:ISOPEN=false

同じく、カーソルFORループで各属性を出力するコードを実行してみました。

DECLARE
    CURSOR cur IS
        SELECT * FROM Member;
    
BEGIN 
    
    FOR rec IN  cur LOOP
        --カーソルISOPEN=出力  
        If (cur%ISOPEN) THEN  
            SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=true');  
        ELSE  
            SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=false');     
        END IF;
        --カーソルROWCOUNT出力
        SYS.DBMS_OUTPUT.PUT_LINE('フェッチした件数:ROWCOUNT=' || cur%ROWCOUNT);
        
        
        SYS.DBMS_OUTPUT.PUT_LINE(rec.MemberId);
    END LOOP;
    
    --カーソルISOPEN=出力  
    If (cur%ISOPEN) THEN  
        SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=true');  
    ELSE  
        SYS.DBMS_OUTPUT.PUT_LINE('ISOPEN=false');     
    END IF;
END;

出力結果です。
ISOPEN=true
フェッチした件数:ROWCOUNT=1
1
ISOPEN=true
フェッチした件数:ROWCOUNT=2
2
ISOPEN=true
フェッチした件数:ROWCOUNT=3
3
ISOPEN=false

PLSQL パラメータ付きカーソル

前回までのカーソルの使い方についてはコチラ
PLSQL SELECTの結果を取得する～取得結果が複数行の場合～
PLSQL カーソルを使用してデータを取得する

今回はパラメータ付きカーソルについてです。

カーソルにはパラメータを渡すことができます。
カーソルを定義する際に、カーソル名称とISの間に引数を宣言します。

カーソルFORループを使用せず、明示的にカーソルのオープン、クローズを行うパターンでの使用例

カーソルオープンする際に、カーソルのパラメータを指定する必要があります。

DECLARE
    --カーソル定義
    CURSOR member_csr(prmMemberId Member.MemberId%Type) IS 
        SELECT * FROM Member WHERE MemberId >= prmMemberId;
    --レコード変数
    member_rec member_csr%Rowtype;
BEGIN

    --カーソルオープンする際に引数を指定する
    OPEN member_csr('2');
    
    BEGIN
        
        LOOP
            FETCH member_csr INTO member_rec;
            EXIT WHEN member_csr%NOTFOUND;
            SYS.DBMS_OUTPUT.PUT_LINE(member_rec.memberid || ' ' || member_rec.membername);
        END LOOP;    
        
        --カーソルクローズ
        CLOSE member_csr;
        
    EXCEPTION
        WHEN others THEN  
           --例外が発生してもカーソルをクローズする  
           CLOSE member_csr;  
           --例外を親ブロックへ伝播する。（必要な場合のみ）  
           RAISE;  

    END;
    
END;

カーソルFORループでの使用例

こちらはループごとにパラメータを指定することができます。

DECLARE
    --カーソル定義
    CURSOR member_csr(prmMemberId Member.MemberId%Type) IS 
        SELECT * FROM Member WHERE MemberId = prmMemberId;
   
BEGIN

    FOR idx IN 1..3 LOOP
        --ループ毎にカーソル引数を指定できる
        FOR member_rec IN member_csr(idx) LOOP

            SYS.DBMS_OUTPUT.PUT_LINE(member_rec.memberid || ' ' || member_rec.membername);    

        END LOOP;

    END LOOP;

END;

カーソルのパラメータは型を指定することはできますが、精度は指定できません。

DECLARE
    CURSOR cur(id VARCHAR2) IS
        SELECT * FROM Member WHERE MemberId = id; 
    
BEGIN 
    
    FOR rec IN  cur('1') LOOP
          SYS.DBMS_OUTPUT.PUT_LINE(rec.MemberName);
    END LOOP;
END;

.NET（VB）　LINQをVBで使用する際に注意すること

LINQはC#でしか書いたことがないので、VBのLINQの書き方がわかりません。
来年からしばらくはVBでの開発になるので、VBでのLINQの書き方を調べてみました。

VBでLINQを書く際の注意点です。
他にもあるかもしれませんが、とりあえず以下の2点。

FunctionとSubを書き分ける必要がある。
匿名クラスを集計やグループ化のキーにする場合は、Keyキーワードを使用する必要がある。

以下のようなテストデータで説明します。
【C#】

private class Fruit
{
    public string Name { get; set; }
    public string Rank { get; set; }
    public decimal Price { get; set; }
}

var fruits = new List<Fruit>()
                {
                    new Fruit(){Name = "りんご", Rank = "A" , Price = 1000 },
                    new Fruit(){Name = "みかん", Rank = "A" , Price = 600 },
                    new Fruit(){Name = "ぶどう", Rank = "B" , Price = 1200 },
                    new Fruit(){Name = "りんご", Rank = "B" , Price = 800 },
                    new Fruit(){Name = "みかん", Rank = "A" , Price = 500 }

                };

【VB】

Private Class Fruit
    Public Property Name As String
    Public Property Rank As String
    Public Property Price As Decimal
End Class

Dim fruits = New List(Of Fruit)() From
            {
                New Fruit() With {.Name = "りんご", .Rank = "A", .Price = 1000},
                New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 600},
                New Fruit() With {.Name = "ぶどう", .Rank = "B", .Price = 1200},
                New Fruit() With {.Name = "りんご", .Rank = "B", .Price = 800},
                New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 500}
            }

FunctionとSubを書き分ける必要がある

C#では以下のように何も考えずに　「 => 」と書けばよかったのですが

var nameRankList = fruits.Select(itm => new { itm.Name, itm.Rank });

ameRankList.ToList().ForEach(itm =>
            { Console.WriteLine(string.Format("{0} {1}", itm.Name, itm.Rank)); });

VBでは以下のように値を返すものは Function で、値を返さないものは Sub で書き分ける必要があります。

'値を返すものはFunctionで
Dim nameRankList = fruits.Select(Function(itm) New With {itm.Name, itm.Rank})

'値を返さないものはSub
nameRankList.ToList().ForEach(Sub(itm) _
        Console.WriteLine(String.Format("{0} {1}", itm.Name, itm.Rank)))

Subと書くところを間違えてFunctionと書いてもエラーにならないようです。
ソース元はコチラ → 「C#.NET vs VB.NET 」VB.NET Action デリゲート型に Function ラムダ式を代入
こわいですねぇ
ちょと試してみましょうw

Form上にあるコントロールをすべて無効にするコードです。

Me.Controls.Cast(Of System.Windows.Forms.Control)().ToList().ForEach(Sub(itm) itm.Enabled = False)

Functionに変えてもエラーは出ません。もちろんコントロールも無効になりません。

Me.Controls.Cast(Of System.Windows.Forms.Control)().ToList().ForEach(Function(itm) itm.Enabled = False)

匿名クラスを集計やグループ化のキーにする場合は、Keyキーワードを使用する必要がある

まずC#で、果物名とランクでGroupByしてみます。

var nameList = fruits.GroupBy(itm => new { itm.Name, itm.Rank });
//--出力
//りんご A
//みかん A
//ぶどう B
//りんご B
nameList.ToList().ForEach(grp => { Console.WriteLine("{0} {1}",grp.Key.Name, grp.Key.Rank); });

次にVBのコードです。(NGパターン）
C#と同じ感覚で書くと、以下のようなコードになるかと思います。
出力結果からわかるように全然グループ化されていません。

Dim nameList = fruits.GroupBy(Function(itm) New With {itm.Name, itm.Rank})
'--出力
'りんご A
'みかん A
'ぶどう B
'りんご B
'みかん A
nameList.ToList().ForEach(Sub(grp) Console.WriteLine("{0} {1}", grp.Key.Name, grp.Key.Rank))

VBで匿名クラスをグループ化のキーに使用する場合、
キーに使用するプロパティの前に「Key」キーワードをつける必要があります。

匿名クラスのNameプロパティの前だけにKeyキーワードをつけてみます。
結果は名称だけでグループ化されます。

Dim nameList = fruits.GroupBy(Function(itm) New With {Key itm.Name, itm.Rank})
'--出力
'りんご A
'みかん A
'ぶどう B
nameList.ToList().ForEach(Sub(grp) Console.WriteLine("{0} {1}", grp.Key.Name, grp.Key.Rank))

匿名クラスのNameプロパティ、Rankプロパティの前にKeyキーワードをつけると、名称とランクでグループ化されます。

Dim nameList = fruits.GroupBy(Function(itm) New With {Key itm.Name, Key itm.Rank})
'--出力
'りんご A
'みかん A
'ぶどう B
'りんご B
nameList.ToList().ForEach(Sub(grp) Console.WriteLine("{0} {1}", grp.Key.Name, grp.Key.Rank))

うぅ～ん･･･ﾅﾝﾀﾞｶﾅｧ

以下のコードはSelectで果物リストから名称とランクを抽出してます。
結果も予想通りです。

Dim nameList = fruits.Select(Function(itm) New With {itm.Name, itm.Rank})
'--出力
'りんご A
'みかん A
'ぶどう B
'りんご B
'みかん A
nameList.ToList().ForEach(Sub(itm) Console.WriteLine("{0} {1}", itm.Name, itm.Rank))

で重複を除きたいんでDistinctをくっつけると、ある意味予想通りですが、結果は重複が除去されていません。

Dim nameList = fruits.Select(Function(itm) New With {itm.Name, itm.Rank}).Distinct()
'--出力
'りんご A
'みかん A
'ぶどう B
'りんご B
'みかん A
nameList.ToList().ForEach(Sub(itm) Console.WriteLine("{0} {1}", itm.Name, itm.Rank))

ここでも、匿名クラスの各プロパティの前にKeyキーワードを付けないといけません。

Dim nameList = fruits.Select(Function(itm) New With {Key itm.Name, Key itm.Rank}).Distinct()
'--出力
'りんご A
'みかん A
'ぶどう B
'りんご B
nameList.ToList().ForEach(Sub(itm) Console.WriteLine("{0} {1}", itm.Name, itm.Rank))

C#の匿名クラスは、インスタンス作成後に値を変更することができないイミュータブルなオブジェクトで
VBの匿名クラスは、インスタンス作成後に値を変更することができるミュータブルなオブジェクトなんだそうです。
ミュータブルなオブジェクトなので、VBではKeyキーワードを付けることによってequalsメソッドとgetHashcodeメソッドがオーバーライドされ、オブジェクトが等しいか判定しているんですね。
詳しくはコチラ →　かるあのメモ Key キーワードでは GetHashCode と Equals がオーバーライドされるみたい

つまり、匿名クラスのオブジェクトが同じかどうかを判定したいプロパティに、Keyキーワードをつけなければいけないということです。
以下のコードでは、匿名クラスオブジェクトの犬、猫、鳥は、同じポチという名前ですが、Keyキーワードを付けているプロパティが鳥だけ違います。
equalsメソッドでそれぞれのオブジェクトを比較すると、Keyが付いているプロパティの値が同じであれば、等価と判定されているのがわかります。

Dim dog = New With {Key .Name = "ポチ", .Type = "犬", .Squeak = "わんわん"}
Dim cat = New With {Key .Name = "ポチ", .Type = "猫", .Squeak = "にゃぁ"}
Dim bird = New With {.Name = "ポチ", Key .Type = "鳥", .Squeak = "ちゅんちゅん"}

'--出力
'犬と猫は等価
Console.WriteLine("犬と猫は{0}", (If(dog.Equals(cat), "等価", "等価でない")))

'--出力
'犬と鳥は等価でない
Console.WriteLine("犬と鳥は{0}", (If(dog.Equals(bird), "等価", "等価でない")))

.Net（VB C#）　LINQ Aggregateを使用して集計する

LINQのAggregateを使用して、リストの値を集計します。

いつものごとく
テスト用の高級果物クラスです。
【C#】

private class Fruit
{
    public string Name { get; set; }
    public string Rank { get; set; }
    public decimal Price { get; set; }
}

【VB#】

Private Class Fruit
    Public Property Name As String
    Public Property Rank As String
    Public Property Price As Decimal
End Class

テストデータを作成します。
【C#】

var fruits = new List<Fruit>()
                {
                    new Fruit(){Name = "りんご", Rank = "A" , Price = 1000 },
                    new Fruit(){Name = "みかん", Rank = "A" , Price = 600 },
                    new Fruit(){Name = "ぶどう", Rank = "B" , Price = 1200 },
                    new Fruit(){Name = "りんご", Rank = "B" , Price = 800 },
                    new Fruit(){Name = "みかん", Rank = "A" , Price = 500 }

                };

【VB】

Dim fruits = New List(Of Fruit)() From
            {
                New Fruit() With {.Name = "りんご", .Rank = "A", .Price = 1000},
                New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 600},
                New Fruit() With {.Name = "ぶどう", .Rank = "B", .Price = 1200},
                New Fruit() With {.Name = "りんご", .Rank = "B", .Price = 800},
                New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 500}
            }

果物名別の合計金額を集計します。

まずはGroupByで名前でグループ化します。
つづいてグループ化した各アイテムに対してAgreegateで金額を集計します。
Agreegateの第1引数がseed(種）で、第二引数でseedに対して各アイテムのPriceを加算していきます。
【C#】

var nameSumPriceList = fruits
    .GroupBy(itm => itm.Name)
    .Select(grp =>
        new
        {
            Name = grp.Key,
            SumPrice = grp.Aggregate(0m, (seedSum, curItm) =>  seedSum += curItm.Price)
        });
//--出力--
// りんご 1800
// みかん 1100
// ぶどう 1200
nameSumPriceList.ToList().ForEach(itm =>
 { Console.WriteLine(string.Format("{0} {1}", itm.Name, itm.SumPrice)); });

【VB】

Dim nameSumPriceList = fruits _
            .GroupBy(Function(itm) itm.Name) _
            .Select(Function(grp) _
                        New With
                        {
                            .Name = grp.Key,
                            .SumPrice = grp.Aggregate(0D, Function(seedSum, curItm) seedSum + curItm.Price)
                        })
'--出力--
' りんご 1800
' みかん 1100
' ぶどう 1200
nameSumPriceList.ToList().ForEach(Sub(itm) _
  Console.WriteLine(String.Format("{0} {1}", itm.Name, itm.SumPrice)))

次は果物名別ランク別の最大金額を集計します。
【C#】

//名前別ランク別の最大金額          
var namePriceList = fruits
    .GroupBy(itm => new { itm.Name, itm.Rank })
    .Select(grp =>
        new
        {
            Name = grp.Key.Name,
            Rank = grp.Key.Rank,
            MaxPrice = 
                grp.Aggregate(0m, (seedMax, curItm) => 
                    (seedMax > curItm.Price) ? seedMax : curItm.Price)
        });

//--出力
// りんご A 1000
// みかん A 600
// ぶどう B 1200
// りんご B 800
namePriceList.ToList().ForEach(itm =>
    { Console.WriteLine(string.Format("{0} {1} {2}", itm.Name, itm.Rank, itm.MaxPrice)); });

【VB】
GroupByでグループ化のキーに匿名クラスを使用する際は、キーに使用するプロパティの前にKeyキーワードを付けます。

'名前ランク別の最大金額          
Dim namePriceList = fruits _
        .GroupBy(Function(itm) New With {Key itm.Name, Key itm.Rank}) _
        .Select(Function(grp) _
           New With
           {
               .Name = grp.Key.Name,
               .Rank = grp.Key.Rank,
               .MaxPrice = 
                   grp.Aggregate(0D, Function(seedMax, curItm) 
                       If(seedMax > curItm.Price, seedMax, curItm.Price))
           })

'--出力
' りんご A 1000
' みかん A 600
' ぶどう B 1200
' りんご B 800
namePriceList.ToList().ForEach(
    Sub(itm) Console.WriteLine(String.Format("{0} {1} {2}", itm.Name, itm.Rank, itm.MaxPrice)))

.Net（VB,C#）　LINQ で重複のないデータを抽出する

LINQを使用して、リストから重複のないデータを抽出します。

テスト用の高級果物クラスです。
【C#】

private class Fruit
{
    public string Name { get; set; }
    public string Rank { get; set; }
    public decimal Price { get; set; }
}

【VB】

Private Class Fruit
    Public Property Name As String
    Public Property Rank As String
    Public Property Price As Decimal
End Class

テストデータを作成します。
【C#】

var fruits = new List<Fruit>()
                {
                    new Fruit(){Name = "りんご", Rank = "A" , Price = 1000 },
                    new Fruit(){Name = "みかん", Rank = "A" , Price = 600 },
                    new Fruit(){Name = "ぶどう", Rank = "B" , Price = 1200 },
                    new Fruit(){Name = "りんご", Rank = "B" , Price = 800 },
                    new Fruit(){Name = "みかん", Rank = "A" , Price = 500 }
                };

【VB】

Dim fruits = New List(Of Fruit)() From
            {
                New Fruit() With {.Name = "りんご", .Rank = "A", .Price = 1000},
                New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 600},
                New Fruit() With {.Name = "ぶどう", .Rank = "B", .Price = 1200},
                New Fruit() With {.Name = "りんご", .Rank = "B", .Price = 800},
                New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 500}
            }

果物名で重複のないデータを抽出します。
【C#】

var nameList = fruits.Select(itm => itm.Name).Distinct();
//--出力--
//りんご
//みかん
//ぶどう
nameList.ToList().ForEach(itm => { Console.WriteLine(itm); });

【VB】

 Dim nameList = fruits.Select(Function(itm) itm.Name).Distinct()
'--出力--
' りんご
' みかん
' ぶどう
nameList.ToList().ForEach(Sub(itm) Console.WriteLine(itm))

果物名とランクで重複のないデータを抽出します。
【C#】

var nameRankList = fruits.Select(itm => new { itm.Name, itm.Rank }).Distinct();
//--出力--
// りんご A
// みかん A
// ぶどう B
// りんご B
nameRankList.ToList().ForEach(itm =>
 { Console.WriteLine(string.Format("{0} {1}", itm.Name, itm.Rank)); });

【VB】
VBでは匿名クラスをキー項目にしたい場合、キーにしたい各プロパティの前にKeyキーワードを付けます。

Dim nameRankList = fruits.Select(Function(itm) New With {Key itm.Name, Key itm.Rank}).Distinct()
'--出力--
' りんご A
' みかん A
' ぶどう B
' りんご B
nameRankList.ToList().ForEach(Sub(itm) _
     Console.WriteLine(String.Format("{0} {1}", itm.Name, itm.Rank)))

他にもGroupByして、各グループの先頭1件目を抽出する方法もあります。
【C#】

var nameRankList = fruits.GroupBy(itm => new { itm.Name, itm.Rank })
                    .Select(grp => grp.First());
//--出力--
// りんご A
// みかん A
// ぶどう B
// りんご B
nameRankList.ToList().ForEach(itm =>
                { Console.WriteLine(string.Format("{0} {1}", itm.Name, itm.Rank)); });

【VB】
VBで匿名クラスのプロパティをキー項目にしたい場合、キーにしたい各プロパティの前にKeyキーワードを付けます。

'重複のない名称＆ランク
Dim nameRankList = fruits.GroupBy(Function(itm) New With {Key itm.Name, Key itm.Rank}) _
                        .Select(Function(grp) grp.First())
'--出力--
' りんご A
' みかん A
' ぶどう B
' りんご B
nameRankList.ToList().ForEach(Sub(itm) _
     Console.WriteLine(String.Format("{0} {1}", itm.Name, itm.Rank)))

.NET(VB C#) オブジェクト初期化子、コレクション初期化子、匿名クラス

今年1年C#で書いてたら、来年からVBです。
VBでのLINQの書き方がわかりません。
LINQラブなのでVBでの書き方をまとめていきます。

まずはLINQを書く上ではずせない
オブジェクト初期化子、コレクション初期化子、匿名クラスについてVB、C#での書き方をまとめます。

まずはテスト用のクラスとして高級果物クラスです。

【C#】

private class Fruit
{
    public string Name { get; set; }
    public string Rank { get; set; }
    public decimal Price { get; set; }
}

【VB】

Private Class Fruit
    Public Property Name As String
    Public Property Rank As String
    Public Property Price As Decimal
End Class

オブジェクト初期化子

【C#】

Fruit fruit = new Fruit() { Name = "りんご", Rank = "A", Price = 1000 };

【VB】
Withキーワードを使用します。
※Withキーワード以降で改行しても「_（アンダーバー）」は不要です。（注1）

Dim fuit As New Fruit() With { .Name = "りんご", .Rank = "A", .Price = 1000 }

コレクション初期化子

【C#】

var fruits = new List<Fruit>()
     {
         new Fruit(){Name = "りんご", Rank = "A" , Price = 1000 },
         new Fruit(){Name = "みかん", Rank = "A" , Price = 600 },
         new Fruit(){Name = "ぶどう", Rank = "B" , Price = 1200 },
         new Fruit(){Name = "りんご", Rank = "B" , Price = 800 },
         new Fruit(){Name = "みかん", Rank = "A" , Price = 500 }

     };

【VB】
Fromキーワードを使用します。
※Fromキーワード以降で改行しても「_（アンダーバー）」は不要です。（注1）

Dim fruits = New List(Of Fruit)() From
     {
         New Fruit() With {.Name = "りんご", .Rank = "A", .Price = 1000},
         New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 600},
         New Fruit() With {.Name = "ぶどう", .Rank = "B", .Price = 1200},
         New Fruit() With {.Name = "りんご", .Rank = "B", .Price = 800},
         New Fruit() With {.Name = "みかん", .Rank = "A", .Price = 500}
     }

匿名クラス

【C#】

dynamic myfruit = new { Name = "金のりんご", Rank = "AAA", Price = 9999999999 };

【VB】
Withキーワードを使用します。

Dim myfruit = New With {.Name = "金のりんご", .Rank = "AAA", .Price = 9999999999}

注1
VBで改行「_(アンダーバー)」がVS2010より条件により省略できるようになりました。
省略できる条件はコチラ
Visual Basic 2010 の新機能暗黙の行継続

C#の方がわかり易い。書き易い。
VB11年。C#1年。
C#のほうがイイなぁ・・・

PLSQL カーソルを使用してデータを取得する

以前に「PLSQL SELECTの結果を取得する～取得結果が複数行の場合～」でカーソルの使い方についてまとめました。

以下はその時に使用したコードで、カーソルを使用して、データを取得しています。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
    --変数宣言
    memberid VARCHAR2(4);
    membername VARCHAR2(10);
    rank VARCHAR2(2);
BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    LOOP
      --カーソルから1件データを取り出し、変数に代入
      FETCH member_csr INTO memberid, membername, rank;
      --カーソルにデータがなければ、ループ終了
      EXIT WHEN member_csr%NOTFOUND;
      --出力
      SYS.DBMS_OUTPUT.PUT_LINE(memberid || ' ' || membername || ' ' || rank);    
    END LOOP;
    
    --カーソルクローズ
    CLOSE member_csr;
END;

しかし、このコードではカーソルを開いてから例外が発生すると、カーソルが閉じられません。
そこで「PLSQL 例外処理のネスト」でまとめたように、例外が発生してもカーソルが確実に閉じられるようにします。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
    --変数宣言
    memberid VARCHAR2(4);
    membername VARCHAR2(10);
    rank VARCHAR2(2);
BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    BEGIN

        LOOP
          --カーソルから1件データを取り出し、変数に代入
          FETCH member_csr INTO memberid, membername, rank;
          --カーソルにデータがなければ、ループ終了
          EXIT WHEN member_csr%NOTFOUND;
          --出力
          SYS.DBMS_OUTPUT.PUT_LINE(memberid || ' ' || membername || ' ' || rank);    
        END LOOP;
        
        --カーソルクローズ
        CLOSE member_csr;

    EXCEPTION
        WHEN others THEN
            --例外が発生してもカーソルをクローズする
            CLOSE member_csr;
            --例外を親ブロックへ伝播する。（必要な場合のみ）
            RAISE;
    END;

END;

カーソルFORループ

カーソルFORループを使用すると、カーソルを使用してデータを取得する際の以下の処理が省略できます。

カーソルのデータを格納する変数を宣言する。
カーソルをオープンする。
フェッチで1行分のデータを取り出す。
カーソルを閉じる。

カーソルが自動で閉じられるので、例外発生時にカーソルが確実に閉じられるよう考慮する必要もありません。

カーソルFORループは以下のように定義します。

FOR レコード変数 IN カーソル LOOP
    処理
END LOOP;

カーソルFORループを使用して、データを取得するコードです。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
   
BEGIN

    FOR member_rec IN member_csr LOOP
        SYS.DBMS_OUTPUT.PUT_LINE(member_rec.memberid || ' ' || member_rec.membername);    
    END LOOP;
   
END;

PLSQL 例外処理のネスト

前回の「PLSQL 例外処理」に続き、
今回は例外処理のネストについてまとめます。

例外の発生が予想され、例外を適切に処理したあと正常系の処理を行いたいとします。
以下のコードでは6行目でSELECT文の結果が0件のためno_data_found例外が発生し、
「例外が発生しました。SQLCODE=100、エラーメッセージ=ORA-01403: データが見つかりません。」と表示されます。
「その後の処理」は出力されません。

DECLARE
    name Member.MemberName%Type;
 
BEGIN
    
    SELECT MemberName INTO name 
        FROM Member WHERE MemberId = 99;       
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId=1のMemberName=' || name);
    
    
    SYS.DBMS_OUTPUT.PUT_LINE('その後の処理');
    
EXCEPTION
    WHEN others THEN
        SYS.DBMS_OUTPUT.PUT_LINE('例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
END;

6行目のno_data_foundは予想された処理であり、データがなかったときは例外を握りつぶして正常系の処理に戻りたいとします。
そのような場合は6行目の処理を子ブロックにし例外を処理します。

以下のコードではno_data_found例外が発生してもnull文で例外を握りつぶしているので、「その後の処理」が出力されます。
（null文は「何もしない」ということ）

DECLARE
    name Member.MemberName%Type;
 
BEGIN
    
    BEGIN
        --結果が0件となるSELECT文
        SELECT MemberName INTO name 
            FROM Member WHERE MemberId = 99;       
        SYS.DBMS_OUTPUT.PUT_LINE('MemberId=1のMemberName=' || name);
    EXCEPTION
        --該当データがなければ例外を握りつぶす。
        WHEN no_data_found THEN
            null;
    END;
    
    SYS.DBMS_OUTPUT.PUT_LINE('その後の処理');
    
EXCEPTION
    WHEN others THEN
        SYS.DBMS_OUTPUT.PUT_LINE('例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
END;

子ブロックで処理しなかった例外は親ブロックへ伝播（でんぱ）します。
先ほどの6行目SELECT文を修正して複数行のデータが返るようにします。
すると6行目ではtoo_many_rows例外が発生しますが、子ブロックのEXCEPTION部でハンドリングされていない例外なので
親ブロックのEXCEPTION部のothersハンドラで補足され、
「例外が発生しました。SQLCODE=-1422、エラーメッセージ=ORA-01422: 完全フェッチがリクエストよりも多くの行を戻しました。」と表示されます。

DECLARE
    name Member.MemberName%Type;
 
BEGIN
    
    BEGIN
        --結果が複数件となるSELECT文
        SELECT MemberName INTO name 
            FROM Member;       
        SYS.DBMS_OUTPUT.PUT_LINE('MemberId=1のMemberName=' || name);
    EXCEPTION
        --該当データがなければ例外を握りつぶす。
        WHEN no_data_found THEN
            null;
    END;
    
    SYS.DBMS_OUTPUT.PUT_LINE('その後の処理');
    
EXCEPTION
    WHEN others THEN
        SYS.DBMS_OUTPUT.PUT_LINE('例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
END;

例外は補足して処理するが、正常系の処理には戻らず、親ブロックの例外に伝播してほしい場合もあります。
このような場合はRAISE文により、補足して例外を再発生させます。

以下のコードはカーソルを開いてデータを取得しています。
例外が発生してもカーソルが閉じられるるように、EXCEPTION部をネストして、カーソルを閉じています。
その後は正常系の処理には戻らず、RAISE文により例外を再発生させ、親ブロックのEXCEPTION部に伝播し、エラー内容を出力させています。

DECLARE
    --カーソル宣言
    CURSOR member_csr IS SELECT * FROM Member; 
    --カーソル変数
    member_rec member_csr%Rowtype;
    
    numValue NUMBER;
BEGIN
    
    --カーソル オープン
    OPEN member_csr;
    
    BEGIN
        LOOP
            FETCH member_csr INTO member_rec;
            EXIT WHEN member_csr%NOTFOUND;
            --例外発生
            numValue := 1/0;
        END LOOP;
        
        --正常系処理でのカーソルクローズ
        CLOSE member_csr;
        SYS.DBMS_OUTPUT.PUT_LINE('正常系処理内でカーソルを閉じました。');
        
    EXCEPTION
        WHEN others THEN
            --例外が発生してもカーソルをクローズするようにする
            CLOSE member_csr;
            SYS.DBMS_OUTPUT.PUT_LINE('異常系処理内でカーソルを閉じました。');
            --例外を再発生させる
            RAISE;
    END;
    
    SYS.DBMS_OUTPUT.PUT_LINE('その後の処理');
    
EXCEPTION
    WHEN others THEN
        SYS.DBMS_OUTPUT.PUT_LINE('例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
END;

PLSQL 例外処理

PLSQLの例外はEXCEPTION部で処理します。

DECLARE
    宣言部
    
BEGIN
    実行部

EXCEPTION
    例外処理部
    
END;

実行部で例外が発生すると、例外が発生した以降の処理は実行されず、例外処理部に処理が移ります。
例外処理部では、例外を補足する例外ハンドラを定義し、例外に応じた処理を行います。

例外ハンドラは以下のような構文で定義します。

WHEN 例外名 THEN

複数の例外に対して同じ処理を行いたい場合は、例外名称をORで繋ぎます。

WHEN 例外名1 OR 例外名2 THEN

まとめると以下のようになります。

DECLARE

BEGIN
    
    例外発生！EXCEPTION部へ処理が遷移

    例外発生以降の処理は行われない

EXCEPTION
    
    WHEN 例外名1 THEN
        例外1が発生時の処理
        
    WHEN 例外名2 OR 例外名3 THEN
        例外2、例外3が発生した時の処理
END;

例外の発生を確認してみます。
以前の記事「PLSQL SQL Developer からテーブル作成」で作成したテーブルからMemberId=99のMemberNameを取得します。
しかしMemberId=99のデータがないので例外が発生します。

DECLARE
    name Member.MemberName%Type;
    
BEGIN
    
    SELECT MemberName INTO name 
        FROM Member WHERE MemberId = 99;
        
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId=99のMemberName=' || name);

END;

6行目で「ORA-01403 no data found データが見つかりません。」とエラーが発生します。

例外処理を加えてみます。

DECLARE
    name Member.MemberName%Type;
    
BEGIN
    
    SELECT MemberName INTO name 
        FROM Member WHERE MemberId = 99;
        
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId=99のMemberName=' || name);

EXCEPTION
    WHEN no_data_found THEN
      SYS.DBMS_OUTPUT.PUT_LINE('例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
END;

今度はエラーが発生せず「例外が発生しました。SQLCODE=100、エラーメッセージ=ORA-01403: データが見つかりません」と表示されます。

EXCEPTION部で使用されている
SQLCODEはOracleのエラー番号を返す関数です。
SQLERRMはOracleのエラーメッセージを返す関数です。

WHEN no_data_found THEN はSELECT INTO文でデータが取得できなかった時の例外をキャッチします。

no_data_found以外にも、PLSQLでは最低限必要な例外が事前定義されています。
事前定義されている例外は以下のようなものがあります。

例外	Oracleエラー	SQLCODE値	発生原因
ACCESS_INTO_NULL	06530	-6530	プログラムが未初期化オブジェクトの属性に値を代入しようとしたとき。
CASE_NOT_FOUND	06592	-6592	CASE文のWHEN句で何も選択されておらず、ELSE句もない場合。
COLLECTION_IS_NULL	06531	-6531	プログラムがEXISTS以外のコレクション・メソッドを未初期化のネストした表またはVARRAYに適用しようとしたか、または未初期化のネストした表またはVARRAYの要素に値を代入しようとしたとき。
CURSOR_ALREADY_OPEN	06511	-6511	すでにオープンされているカーソルをオープンしようとしたとき。カーソルをオープンするには、一度クローズする必要があります。カーソルFORループは、参照するカーソルを自動的にオープンします。このため、ループの内側ではカーソルをオープンできません。
DUP_VAL_ON_INDEX	00001	-1	UNIQUE索引によって制約されている列に、重複した値を格納しようとしたとき。
INVALID_CURSOR	01001	-1001	オープンされていないカーソルをクローズするなど、不正なカーソル操作を実行しようとしたとき。
INVALID_NUMBER	01722	-1722	SQL文の中で、文字列が正しい数値を表していなかったために、文字列から数値への変換が失敗したとき。（プロシージャ文では、VALUE_ERRORが呼び出されます。）この例外は、バルクFETCH文のLIMIT句の式が正数に評価されない場合にも呼び出されます。
LOGIN_DENIED	01017	-1017	不正なユーザー名またはパスワードでデータベースにログオンしようとした場合。
NO_DATA_FOUND	01403	+100	SELECT INTO文が行を戻さなかったとき、ネストした表で削除された要素を参照したとき、または索引付き表で未初期化の要素を参照したとき。この例外は、いくつかのSQLファンクションで終了したことを通知するために内部的に使用されているため、問合せの一部として起動されるファンクション内部で呼び出された場合は、この例外が伝播されても信頼しないでください。
NOT_LOGGED_ON	01012	-1012	データベースに接続していないプログラムが、データベース・コールを発行した場合。
PROGRAM_ERROR	06501	-6501	PL/SQLに内部的な問題が発生した場合。
ROWTYPE_MISMATCH	06504	-6504	1つの代入の中に含まれるホスト・カーソル変数とPL/SQLカーソル変数の戻り型に互換性がない場合。オープン・ホスト・カーソル変数をストアド・サブプログラムに渡すとき、実パラメータの戻り型と仮パラメータの戻り型には互換性が必要です。
SELF_IS_NULL	30625	-30625	プログラムがMEMBERメソッドの起動を試行したが、オブジェクト型のインスタンスが初期化されなかった場合。つまり、組込みパラメータSELFがオブジェクトを指している場合です。このパラメータは、常にMEMBERメソッドに最初に渡されるパラメータです。
STORAGE_ERROR	06500	-6500	PL/SQLのメモリーが足りなくなった場合、またはメモリーが破損された場合。
SUBSCRIPT_BEYOND_COUNT	06533	-6533	コレクション中の要素数より大きい索引番号を使用してネストした表またはVARRAYの要素を参照した場合。
SUBSCRIPT_OUTSIDE_LIMIT	06532	-6532	有効範囲外（たとえば-1）の索引番号を使用してネストした表またはVARRAYの要素を参照した場合。
SYS_INVALID_ROWID	01410	-1410	文字列が正しいROWIDを表していなかったために、文字列からユニバーサルROWIDへの変換が失敗した場合。
TIMEOUT_ON_RESOURCE	00051	-51	データベースがリソースを求めて待機しているときにタイムアウトが発生した場合。
TOO_MANY_ROWS	01422	-1422	SELECT INTO文が複数の行を戻した場合。
VALUE_ERROR	06502	-6502	算術エラー、変換エラー、切捨てエラー、またはサイズ制約エラーが発生した場合。たとえば、列値を選択し文字変数に代入するときに、その値が変数の宣言された長さよりも長い場合、PL/SQLはその代入を停止してVALUE_ERRORを呼び出します。プロシージャ文では、文字列から数値への変換が失敗した場合にVALUE_ERRORが呼び出されます。（SQL文では、INVALID_NUMBERが呼び出されます。）
ZERO_DIVIDE	01476	-1476	数値を0（ゼロ）で割ろうとしたとき。

事前に定義されていない例外を補足するには　othersハンドラを使用します。
othersハンドラはすべての例外を補足します。
他の例外をハンドリングしている場合は、一番最後にothersハンドラを書きます。
othersハンドラを他の例外ハンドリングより先に書くとｴﾗｰになります。

先ほどのコードを少し修正して、SELECTの結果が複数行返るようにします。
例外はothersハンドラでキャッチされ、エラーメッセージが表示されます。

DECLARE
    name Member.MemberName%Type;
 
BEGIN
    
    SELECT MemberName INTO name 
        FROM Member;
        
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId=1のMemberName=' || name);
    
    
EXCEPTION
    WHEN no_data_found THEN
        SYS.DBMS_OUTPUT.PUT_LINE('例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
    
    WHEN others THEN
        SYS.DBMS_OUTPUT.PUT_LINE('その他の例外が発生しました。SQLCODE=' || SQLCODE || '、エラーメッセージ=' || SQLERRM);  
  
END;

PLSQL 変数の精度について

以前の記事で「PLSQL 変数、定数」で簡単に変数の使い方を書きました。
よく使う変数のデータ型に以下のようなものがあります。（他にもありますが、私が今のところよく使うのはこれだけ）

NUMBER
VARCHAR2
CHAR
DATE
BOOLEAN

このうち精度を指定するのはNUMBER型、VARCHAR2型、CHAR型です。

DECLARE
    numValue NUMBER(10);
    strValue VARCHAR(10);
    charValue CHAR(10);
    dateValue DATE;
    boolValue BOOLEAN;
BEGIN    
    
    NULL;
    
END;

NUMBER(10)型を設定した変数は10桁の数値が代入できます。
VARCHAR2(10)型を設定した変数は10バイトの文字列が代入できます。
CHAR(10)型を設定した変数は10バイトの文字列が代入できます。

データ型により精度を省略できるものがあります。

NUMBERは精度を指定しないと最大の38桁の数値が代入できます。

CHARは精度指定しないと1バイトの文字が代入できます。
VARCHAR2は精度を指定しないとエラーになります。

NUMBER型の精度が省略できるならVARCHAR2も省略できるハズ～♪と思ったのは私だけではないと思うんだけどなぁ。
ちょっとググるとCHARの精度を省略したら最大バイトになると思っていたナカマがいたぁ　ヾ(´∇｀｡*)ノ　

ちなみにVARCHAR2とCHARに指定できる精度は32767が上限です。

【関連】
PLSQL 変数、定数

PLSQL 制御文～演算子～

PLSQLで使える演算子の一覧です。

演算子	説明
=	左辺と右辺が一致するとTrue
!=	左辺と右辺が一致しないとTrue
<>	左辺と右辺が一致しないとTrue （!= と一緒）
>	左辺が右辺よりも大きいとTrue
<	左辺が右辺よりも小さいとTrue
>=	左辺が右辺以上ならばTrue
<=	左辺が右辺以下ならばTrue
IN	左辺が右辺のリストに含まれていればTrue
NOT IN	左辺が右辺のリストに含まれていなければTrue
BETWEEN x AND y	左辺がxからyの範囲であればTrue
NOT BETWEEN x AND y	左辺がxからyの範囲でなければTrue
LIKE	左辺が右辺に部分一致すればTrue
NOT LIKE	左辺が右辺に部分一致しなければTrue
IS NULL	左辺がNULLであればTrue
IS NOT NULL	左辺がNULLでなければTrue

IN以降の演算子はSQLのWHERE句で使われているものですが、IF文の条件式などでも使用できるんですね。
WHERE句で書いているので馴染みはありますが、条件式を書いているときに、
パッとは思い出せないというか、思いつかないというか、パッとは出てこないです。
慣れですかね・・・

DECLARE
    value NUMBER := 5;
    strValue VARCHAR2(10) := 'ABC';
BEGIN    
    
    --IN演算子
    --出力：リストに含まれる
    IF ( value IN (  1, 3, 5, 7, 9) ) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('リストに含まれる');
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('リストに含まれない');
    END IF;
    
    --BETWEEN演算子
    --出力:1～9の範囲である
    IF ( value BETWEEN 1 AND 9 ) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('1～9の範囲である');    
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('1～9の範囲でない');
    END IF;
    
    --LIKE演算子
    --出力:BCに後方一致する。
    IF ( strValue LIKE '%BC' ) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('BCに後方一致する。');    
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('BCに後方一致しない');
    END IF;
    
    --IS NULL演算子
    --出力：NULLである
    strValue := null;
    IF ( strValue IS NULL) THEN
        SYS.DBMS_OUTPUT.PUT_LINE('NULLである。');    
    ELSE
         SYS.DBMS_OUTPUT.PUT_LINE('NULLでない');
    END IF;
    
END;

PLSQL 制御文～LOOP文～

PLSQLの繰り返し処理には次のようなものがあります。

FOR LOOP
WHILE LOOP（前判定ループ）
LOOP（後判定ループ）

FOR LOOP

決められた回数ループ処理を行います。

FOR カウンタ変数 IN 初期値..最大値 LOOP
    繰り返し処理;
END LOOP;

1～9までの値を出力します。

DECLARE
BEGIN    
    FOR counter IN 1..9 LOOP
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
    END LOOP;
END;

REVERSEを指定すると最大値から最小値までの値を出力します。

FOR カウンタ変数 IN REVERSE 初期値..最大値 LOOP
    繰り返し処理;
END LOOP;

9～1までの値を出力します。

DECLARE
BEGIN    
    FOR counter IN REVERSE 1..9 LOOP
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
    END LOOP;
END;

初期値、最大値は変数を指定することも可能です。

DECLARE
    first NUMBER;
    last NUMBER;
BEGIN    

    first := 1;
    last := 9;
    FOR counter IN first..last LOOP
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
    END LOOP;
END;

WHILE LOOP(前判定ループ）

WHILE LOOP文は、条件に一致する間ループ処理を実行します。
条件に一致しなければ、ループ処理が1回も実行されないことがあります。

WHILE 条件 LOOP
  条件に一致する間、繰り返す処理;
END LOOP;

counter変数が10以下の場合にみ、ループ処理を実行します。

DECLARE
    counter NUMBER(2);
BEGIN

    counter := 0;
    WHIlE counter < 10 LOOP
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
        counter := counter + 1;
    END LOOP;
  
END;

LOOP（後判定ループ）　と EXIT文、EXIT WHEN文

このループ処理は終了条件を書かないと無限ループになります。

LOOP
    繰り返す処理;
END;

ループ終了条件はEXIT文またはEXIT WHEN文で指定します。

EXIT文はループ処理を無条件で終了します。
EXIT WHEN文はWHEN句の条件に一致するとループ処理を終了します。

1～9を出力します。
EXIT文を利用して、counter変数が10になたらループ処理を終了します。

DECLARE
    counter NUMBER;
BEGIN    

    counter := 1;
    LOOP       
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
        counter := counter + 1;
        IF ( counter = 10) THEN
            EXIT;
        END IF;
    END LOOP;
END;

1～9を出力します。
EXIT WHEN文を利用して、counter変数が10になたらループ処理を終了します。

DECLARE
    counter NUMBER;
BEGIN    

    counter := 1;
    LOOP       
        SYS.DBMS_OUTPUT.PUT_LINE(counter);       
        counter := counter + 1;
        
        EXIT WHEN counter = 10;
    END LOOP;
END;

EXIT文、EXIT WHEN文は、LOOP文だけでなくWHILE LOOP文やFOR LOOP文でも使用できます。

CONTINUE文

11g以降はCONTINUE、CONTINUE WHENにより現行のループをスキップできます。

CONTINUE文を使用して、カウンター変数が偶数の場合はループ処理をスキップします。

DECLARE
  
BEGIN    

    FOR counter IN 1..9 LOOP       
        IF ( counter MOD 2 = 0 ) THEN
            CONTINUE;
        END IF;     
        --出力：１ 3 5 7 9
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
    END LOOP;
END;

CONTINUE WHEN文を使用して、カウンター変数が偶数の場合はループ処理をスキップします。

DECLARE
  
BEGIN    

    FOR counter IN 1..9 LOOP       
        CONTINUE WHEN counter MOD 2 = 0;
        --出力：１ 3 5 7 9
        SYS.DBMS_OUTPUT.PUT_LINE(counter);
    END LOOP;
END;

11g以前でCONTINUEと同様の処理を行うには、ラベルを使用した方法があります。
またループをネストした多重ループで、スキップしたいループや終了したいループを指定するときにもラベルが使用できます。
詳しくはコチラ「PLSQL 制御文～GOTO文とラベル、NULL文～」

PLSQL 制御文～CASE文～

Oracle9i以降でのみCASE文が使用できます。
Case文には「単純CASE文」と「検索CASE文」の2種類の書き方があります。

単純CASE文

単純なCASE文の構造は、次のとおりです。

CASE 判定する変数
WHEN リテラル値1 THEN リテラル値１に該当したときの処理
WHEN リテラル値2 THEN リテラル値2に該当したときの処理
ELSE どのリテラル値にも該当しなかった時の処理
END CASE;

注意点はNullを判定できないこと。nullを判定したいときは検索CASE文を使用します。
各判定はC#と違ってフォールスルーではないので、処理ごとにbreakの記述はいりません。

DECLARE
    rank VARCHAR2(1);
    dispRank VARCHAR2(10);
BEGIN
    rank := 'B';

    CASE rank
    WHEN 'A' THEN  dispRank := '上級';
    WHEN 'B' THEN  dispRank := '中級';
    WHEN 'C' THEN  dispRank := '初級';
    ELSE dispRank := 'その他';
    END CASE;
    
    SYS.DBMS_OUTPUT.PUT_LINE(dispRank);
  
END;

検索CASE文

検索CASE文の構造は、次のとおりです。

CASE 
WHEN 条件式1 THEN 条件式１に該当したときの処理
WHEN 条件式2 THEN 条件式2に該当したときの処理
ELSE どの条件式にも該当しなかった時の処理
END CASE;

単純CASE文ではNullを判定できませんが検索CASE文ではnullを判定できます。
各判定はC#と違ってフォールスルーではないので、処理ごとにbreakの記述はいりません。

DECLARE
    rank VARCHAR2(1);
    dispRank VARCHAR2(10);
BEGIN
    rank := null;

    CASE 
    WHEN rank = 'A' THEN  dispRank := '上級';
    WHEN rank = 'B' THEN  dispRank := '中級';
    WHEN rank = 'C' THEN  dispRank := '初級';
    WHEN rank IS NULL THEN dispRank := '未設定';
    ELSE dispRank := 'その他';
    END CASE;
    
    SYS.DBMS_OUTPUT.PUT_LINE(dispRank);
  
END;

PLSQL 制御文～IF文～

PLSQLでのIF文です。
注意点は「ELSEIF」ではなく「ELSIF」であることです。

IF 条件式1 THEN
  条件式１に該当したときの処理;
ELSIF 条件式2 THEN
  条件式2に該当したときの処理;
ELSE
    どの条件にも該当しなかった時の処理;
END IF;

DECLARE
    rank VARCHAR2(1);
    dispRank VARCHAR2(10);
BEGIN
    rank := 'B';
    
    IF ( rank = 'A') THEN
        dispRank := '上級';
    ElSIF ( rank = 'B' ) THEN
        dispRank := '中級';  
    ELSIF ( rank = 'C') THEN
        dispRank := '初級';
    ELSIF ( rank IS NULL ) THEN
        dispRank := '未設定';
    ELSE
        dispRank := 'その他';
    END IF;
    
    --出力：中級
    SYS.DBMS_OUTPUT.PUT_LINE(dispRank);
  
END;

※IF文の条件式は括弧で囲まなくてもOKです。
なんだか括弧で囲まないと落ち着かなくて、好みの問題です。

PLSQL コレクション（配列）　～結合配列の操作～

今回は結合配列を操作するメソッドについてです。

結合配列を操作するメソッドの一覧です。

メソッド名	説明
exists	指定した添字の要素が存在する場合、trueを返す。
count	変数に存在する要素の数を返す。
firs	最初の添字を返す。
last	最後の添字を返す。
prior	指定した添字の一つ前の添字を返す。 ※前の添字とは引数に指定した添字の次に小さい添字。
next	指定した添字の一つ次の添字を返す。 ※次の添字とは引数に指定した添字の次に大きい添字。
delete	配列からすべての要素を削除する。配列から指定の要素を削除する。

DECLARE
    TYPE ary_type IS TABLE OF VARCHAR2(1) INDEX BY VARCHAR2(1);
    ary ary_type;
    
    result BOOLEAN;
BEGIN
    --テストデータ作成
    ary('A') := 'a';
    ary('B') := 'b';
    ary('C') := 'c';
    ary('E') := 'e';
    ary('D') := 'd';
    
    --指定した添字の要素が存在するかを返す
    --出力:true
    IF ( ary.exists('D') ) THEN
      SYS.DBMS_OUTPUT.PUT_LINE('true');
    ELSE
      SYS.DBMS_OUTPUT.PUT_LINE('false');
    END IF;
    
    --配列の要素数を返す
    --出力：5
    SYS.DBMS_OUTPUT.PUT_LINE( ary.count );
    
    --配列の最大の要素を返す
    --出力：A
    SYS.DBMS_OUTPUT.PUT_LINE( ary.first );
    
    --配列の最小の要素を返す
    --出力：E   
    SYS.DBMS_OUTPUT.PUT_LINE( ary.last );
    
    --指定した添字の前の添字を返す
    --出力：A   
    SYS.DBMS_OUTPUT.PUT_LINE( ary.prior('B') );
    
    --指定した添字の次の添字を返す
    --出力：E   
    SYS.DBMS_OUTPUT.PUT_LINE( ary.next('D') );
    
    --指定した添字を削除する
    --出力：4
    ary.delete('B');
    SYS.DBMS_OUTPUT.PUT_LINE( ary.count );
    
    --指定した添字を削除する
    --出力：0
    ary.delete;
    SYS.DBMS_OUTPUT.PUT_LINE( ary.count );
    
END;

PLSQL コレクション（配列）　～結合配列～

PLSQLでコレクション（配列）を使用してみます。

コレクション（配列）の種類は3種類あるようです。

結合配列（PL/SQL表、索引付表　ともいう）
ネストした表（PL/SQL表ともいう）
可変配列(VARRAYともいう)

結合配列以外のネストした表、可変配列は、なにをするためのものなのか、なんのためにあるのか、
今の私には難しすぎて理解できないです。
いつか理解できたときに、残りのふたつの記事を書くことにして、今回は結合配列について書いていきます。

結合配列（PL/SQL表、索引付表ともいう）

まずは結合配列を使ってみます。
結合配列とはHashやMapといった連想配列みたいなものです。
Shift the Oracle PL/SQL コレクション型に結合配列の特徴がまとまってます。

結合配列の特徴の一部

データベースに格納できない
初期化していなくてもNULLではない
実行開始時には既にNULLではなくNULLにできない。（エラーになる）
PL/SQL ブロック内だけで有効 (CREATE TYPE できない)

結合配列の使い方

1. まずは配列の「データ型」を宣言します。

TYPE 配列データ型名 IS TABLE OF 格納する値の型 INDEX BY キーのデータ型

たとえば
キーが数値で値が50バイトの文字列の場合のコレクションのデータ型の宣言
TYPE ary_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

キーが10バイトの文字列で値が50バイトの文字列の場合のコレクションのデータ型の宣言
TYPE ary_type IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(10);

2. 変数を宣言『型』を作っただけでは使用できないので、作った型をデータ型とした変数を宣言します。

3. 変数を使い終わったらnullを代入してメモリ解放できないので、Deleteメソッドを使用してメモリを解放します。

配列データ型名.DELETE;

下記のコードはMemberテーブルより全データを取得し、
配列のキーにMemberId列の値、配列の値にMemberName列の値を設定して配列を作成します。

DECLARE
    --配列の型を宣言
    TYPE aryName_type IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(4);
    --配列変数を宣言
    aryName aryName_type;
    --カーソルを宣言
    CURSOR member_cur IS
        SELECT MemberId, MemberName FROM Member;
    --カーソルデータを格納するレコード変数   
    member_rec member_cur%Rowtype;
BEGIN
    
    OPEN member_cur;
    
    LOOP
        FETCH member_cur INTO member_rec;        
        EXIT WHEN member_cur%NOTFOUND;
        --MemberIdをキーにMemberNameを配列に格納
        aryName(member_rec.MemberId) := member_rec.MemberName;      
    END LOOP;
    
    CLOSE member_cur;
    
    --配列よりMemberId='2'のMemberNameを出力する
    SYS.DBMS_OUTPUT.PUT_LINE( aryName('2') );

    --使い終わったらDELETEメソッドでメモリ解放する
    aryName.DELETE;
END;

配列の値にレコードを設定することもできます。
以下のコードは、配列のキーにレコードのインデックス番号、配列の値にレコードを設定して配列を作成します。

DECLARE
    --カーソル宣言
    CURSOR member_crs IS 
        SELECT MemberId, MemberName FROM Member;
    --カーソルデータを代入するレコード変数
    member_rec member_crs%Rowtype;
    --キーにインデックス番号、値にレコードを持つ配列型を宣言
    TYPE ary_type IS TABLE OF member_crs%Rowtype INDEX BY Binary_Integer;
    --配列型の変数
    ary ary_type;
    --カウンタ変数
    counter NUMBER(4);
  
BEGIN
    
    OPEN member_crs;
    
    counter := 0;
    LOOP
        FETCH member_crs INTO member_rec;
        EXIT WHEN member_crs%NOTFOUND;
        --インデックス番号をキーにレコードを配列に格納
        ary(counter) := member_rec;
        counter := counter + 1;
    END LOOP;
        
    CLOSE member_crs;

    --配列のインデックスの2番目の要素を取り出す
    member_rec := ary(2);
    SYS.DBMS_OUTPUT.PUT_LINE(member_rec.MemberId || ' ' || member_rec.MemberName);
    --使い終わったらDELETEメソッドでメモリ解放する
    ary.DELETE;
END;

配列の値に配列を設定するこで多次元配列にできます。
以下のコードは、二次元配列で九九表のサンプルです。

DECLARE
    TYPE col_type IS TABLE OF NUMBER(2) INDEX BY BINARY_INTEGER;
    TYPE row_type IS TABLE OF col_type INDEX BY BINARY_INTEGER;
    --配列型の変数
    kukuTable row_type;
  
BEGIN
  
    FOR row in 1 .. 9 LOOP
        FOR col in 1..9 LOOP
        kukuTable(row)(col) := row * col;
        END LOOP;
    END LOOP;

    SYS.DBMS_OUTPUT.PUT_LINE( '7 × 9 = '|| kukuTable(7)(9) );
    
END;

PLSQL SELECTの結果を取得する～取得結果が複数行の場合～

前回「PLSQL SELECTの結果を取得する～取得結果が1行の場合～」に続き
今回はSELECTの結果が複数行の場合です。

SELECTの結果が複数行の場合はカーソルを使用します。

カーソルとは
SELECTの結果セットに対して、1行ずつデータを取り出し、順次処理していくためのものです。

カーソルを使用する手順

1. カーソルの宣言する。
   CURSOR カーソル名 IS SELECT文;

2. カーソルを開く。
   OPEN カーソル名;

3. ループ処理を開始

4. 1行ごとにデータを取り出す。
   FETCH カーソル名 INTO 変数

5. データがなければループを終了する
   EXIT WHEN カーソル名%NOTFOUND;

6. カーソルを閉じる。
   CLOSE カーソル名;

カーソルを使用してMemberテーブルの全データを取得し、1件づつ変数に代入し出力します。
※カーソルを使用したデータの取得についてはコチラ「PLSQL カーソルを使用してデータを取得する」

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
    --変数宣言
    memberid VARCHAR2(4);
    membername VARCHAR2(10);
    rank VARCHAR2(2);
BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    LOOP
      --カーソルから1件データを取り出し、変数に代入
      FETCH member_csr INTO memberid, membername, rank;
      --カーソルにデータがなければ、ループ終了
      EXIT WHEN member_csr%NOTFOUND;
      --出力
      SYS.DBMS_OUTPUT.PUT_LINE(memberid || ' ' || membername || ' ' || rank);    
    END LOOP;
    
    --カーソルクロース
    CLOSE member_csr;
END;

前回「PLSQL SELECTの結果を取得する～取得結果が1行の場合～」と同様に
変数宣言のデータ型を「%type属性」にしたパターン。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
    --変数宣言
    memberid Member.MemberId%Type;
    membername Member.MemberName%Type;
    rank Member.Rank%Type;
BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    LOOP
      --カーソルから1件データを取り出し、変数に代入
      FETCH member_csr INTO memberid, membername, rank;
      --カーソルにデータがなければ、ループ終了
      EXIT WHEN member_csr%NOTFOUND;
      --出力
      SYS.DBMS_OUTPUT.PUT_LINE(memberid || ' ' || membername || ' ' || rank);    
    END LOOP;
    
    --カーソルクロース
    CLOSE member_csr;
END;

Memberテーブルの「%ROWTYPE属性」を使用したパターン

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
    --変数宣言
    member_rec Member%Rowtype;
BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    LOOP
      --カーソルから1件データを取り出し、変数に代入
      FETCH member_csr INTO member_rec;
      --カーソルにデータがなければ、ループ終了
      EXIT WHEN member_csr%NOTFOUND;
      --出力
      SYS.DBMS_OUTPUT.PUT_LINE(member_rec.MemberId || ' ' || member_rec.MemberName || ' ' || member_rec.Rank);    
    END LOOP;
    
    --カーソルクロース
    CLOSE member_csr;
END;

カーソルの「%ROWTYPE属性」を使用したパターン
カーソルを使用した場合は「レコード変数名カーソル名%ROWTYPE;」とすることでカーソルの1データを代入できるレコート変数ができます。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
    --レコード型のレコード変数を宣言
    member_rec member_csr%Rowtype;

BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    LOOP
      --カーソルから1件データを取り出し、変数に代入
      FETCH member_csr INTO member_rec;
      --カーソルにデータがなければ、ループ終了
      EXIT WHEN member_csr%NOTFOUND;
      --出力
      SYS.DBMS_OUTPUT.PUT_LINE(member_rec.MemberId || ' ' || member_rec.MemberName || ' ' || member_rec.Rank);    
    END LOOP;
    
    --カーソルクロース
    CLOSE member_csr;
END;

変数宣言のデータ型を自分で定義したレコート型にしたパターン。

DECLARE
    --カーソル定義
    CURSOR member_csr IS 
        SELECT * FROM Member;
     --レコード型の作成
    TYPE member_rec_type IS RECORD
        (
            MemberId  Member.MemberId%type,
            MemberName  Member.MemberName%type,
            Rank  Member.Rank%type
        );
    --レコード型のレコード変数を宣言
    member_rec member_rec_type;

BEGIN
    --カーソルオープン
    OPEN member_csr;
    
    LOOP
      --カーソルから1件データを取り出し、変数に代入
      FETCH member_csr INTO member_rec;
      --カーソルにデータがなければ、ループ終了
      EXIT WHEN member_csr%NOTFOUND;
      --出力
      SYS.DBMS_OUTPUT.PUT_LINE(member_rec.MemberId || ' ' || member_rec.MemberName || ' ' || member_rec.Rank);    
    END LOOP;
    
    --カーソルクロース
    CLOSE member_csr;
END;

また1年ぶりの投稿ですね～

去年のちょうど今頃、在宅の仕事を頂いている会社が傾いてお仕事がなくなってしまいました。
辺境地に住んでいるので仕事先がなくて、唯一あった開発会社に1年間お世話になってました。
なのでブログを書く時間がなかったんです。
ここでのお仕事は.Net(C#)。
面白い仕事ではまったくなかったけれど、個人的にLinqで遊んだり、クロージャで遊んだりしてました。
わたしC#好きだなぁ

来年からは別の会社でお世話になります。
次は.Net(VB)+PLSQL。
正社員として転職したので長～くお世話になる予定です。

PLSQLほとんど経験ないんですよね・・・とりあえずお勉強です。

このブログAndroidで来てくれる人が多いんですけど
しばらくはPLSQLの記事が続きます。

もうAndroid開発することないだろうなぁ・・・
swiftとか興味あったんだけどな・・・

PLSQL SELECTの結果を取得する～取得結果が1行の場合～

今回はPLSQLでSELECTの結果を取得してみます。

下準備としてPLSQL SQL Developer からテーブル作成で作成したMemberテーブルに3件ほどデータを作成します。

DECLARE 
BEGIN
    INSERT INTO Member
        ( MemberId, MemberName, Rank ) VALUES ( 1, 'Yamada', 'B');
    INSERT INTO Member
        ( MemberId, MemberName, Rank ) VALUES ( 2, 'Tanaka', 'C');
    INSERT INTO Member
        ( MemberId, MemberName, Rank ) VALUES ( 3, 'Suzuki', 'A');
END;

それではデータを1件抽出し変数に代入します。
DECLARE部でデータを代入する変数を宣言します。
次に「SELECT 列名 INTO 代入する変数」で取得したデータを変数に代入します。
データが取得できない場合や複数件取得できた場合はエラーになるので注意してください。

DECLARE
    memberid VARCHAR2(4);
    membername VARCHAR2(10);
    rank VARCHAR2(2);
BEGIN
    SELECT MemberId, MemberName, Rank INTO memberid, membername, rank
    FROM Member
    WHERE MemberId = '1';
    
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId:' || memberid);
    SYS.DBMS_OUTPUT.PUT_LINE('MemberName:' || membername);
    SYS.DBMS_OUTPUT.PUT_LINE('Rank:' || rank);
END;

上記の例では変数のデータ型をSELECTで取得する列のデータ型と合わせる必要がありました。
memberid VARCHAR2(4);の「VARCHAR2(4)」部分です。
だけどｲﾁｲﾁ列のデータ型や精度を指定するのはﾒﾝﾄﾞｸｻｲよ！
仕様変更でデータ型や精度が変わるたびに修正するのはﾒﾝﾄﾞｸｻｲよ！
ﾒﾝﾄﾞｸｻｲのはｲﾔなので上記の変数宣言のデータ型を「%type属性」を使用してちょっとﾗｸします。

DECLARE
    memberid Member.MemberId%type;
    membername Member.MemberName%type;
    rank Member.Rank%type;
BEGIN
    SELECT MemberId, MemberName, Rank INTO memberid, membername, rank
    FROM Member
    WHERE MemberId = '1';
    
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId:' || memberid);
    SYS.DBMS_OUTPUT.PUT_LINE('MemberName:' || membername);
    SYS.DBMS_OUTPUT.PUT_LINE('Rank:' || rank);
END;

Oracle Database PL/SQL言語リファレンスによりますと
『%TYPE属性を使用すると事前に宣言されている変数、フィールド、レコード、ネストした表またはデータベース列と同じデータ型の定数、変数、フィールドまたはパラメータを宣言できます。参照先項目が変更されると、宣言は自動的に更新されます。』とあります。

つまり「memberid Member.MemberId%type;」の部分は
MemberテーブルのMemberId列と同じデータ型にし、MemberテーブルのMemberId列のデータ型が変更されても自動的に変更するから大丈夫ということです。

う～ん
でもまだﾒﾝﾄﾞｸｻｲ。Memberテーブルの１データを取得するのに、Memberテーブルに含まれるすべての列分の変数を作るのはﾒﾝﾄﾞｸｻｲよ。
Memberテーブルの列が20列ぐらいあったら、変数も20個用意するの・・・・？ﾑﾘ。

ハイ！そんなときは「%ROWTYPE属性」を使用します。

DECLARE
    member_rec Member%rowtype;
BEGIN
    SELECT * INTO member_rec
    FROM Member
    WHERE MemberId = '1';
    
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId:' || member_rec.MemberId);
    SYS.DBMS_OUTPUT.PUT_LINE('MemberName:' || member_rec.MemberName);
    SYS.DBMS_OUTPUT.PUT_LINE('Rank:' || member_rec.Rank);
END;

「レコード変数名表名%ROWTYPE;」とすることでテーブルの1データを代入できる変数ができるんですね！
ｽﾃｷです。

ｽﾃｷですね～ﾗｸですね～使いまくりたくなりますね～

でもダメなんですね～

今回Memberテーブルは3列しかないですが、Memberテーブルが20列あったとします。
そのうち使用するのは5列だったとしたら、15列分のデータは取得するけど無駄になってしまいます。

そんな場合は、使用する列だけのレコード型を自分で定義し、必要な列だけ変数に代入するようにします。

DECLARE
    --レコード型の作成
    TYPE member_rec_type IS RECORD
        (
            MemberId  Member.MemberId%type,
            MemberName  Member.MemberName%type
        );
    --レコード型のレコード変数を宣言
    member_rec member_rec_type;
BEGIN
    SELECT MemberId, MemberName INTO member_rec
    FROM Member
    WHERE MemberId = '1';
    
    SYS.DBMS_OUTPUT.PUT_LINE('MemberId:' || member_rec.MemberId);
    SYS.DBMS_OUTPUT.PUT_LINE('MemberName:' || member_rec.MemberName);
END;

「TYPE　データ型名 IS RECORD　（フィールド宣言・・・）;」でレコードの「型」を宣言します。
次にデータ型が作成したレコード型の変数を宣言します。

レコード型を作成するのがﾒﾝﾄﾞｸｻｲですが、使い分けが必要なようです。

PLSQL 変数、定数

前回は「Hello World」を直接出力しました。

DECLARE

BEGIN

    SYS.DBMS_OUTPUT.PUT_LINE('Hello World');

END;

今回は「Hello World」を変数に代入して、出力します。
変数はDECLARE部で「変数名変数型」の形式で宣言します。
変数への値の代入は「変数 := 代入する値」で行います。

DECLARE
    --DECLARE部で変数の宣言を行う
    str VARCHAR2(20);
BEGIN
    --変数へ値の代入は「変数 := 代入する値」
    str := 'Hello World 2'; 
    SYS.DBMS_OUTPUT.PUT_LINE(str);
END;

宣言と同時に初期値を設定することも可能です。
初期値を設定しない場合は、変数の値はnullになります。

DECLARE
    --宣言と同時に初期値設定
    str VARCHAR2(50) := 'Default Hello World';
BEGIN
    SYS.DBMS_OUTPUT.PUT_LINE(str);
END;

変数にNOT NULL制約を指定することも可能です。
変数は初期値を設定しなければ値はnullなので、NOT NULL制約を指定する場合は初期値も同時に設定する必要があります。

DECLARE
    --NOT NULL制約を指定
    str VARCHAR2(50) NOT NULL := 'Default Hello World Not Null';
BEGIN
    SYS.DBMS_OUTPUT.PUT_LINE(str);
END;

CONSTキーワードを指定すると定数になります。
「定数名 CONSTANT データ型 := 定数値」と宣言します。

DECLARE
    --定数宣言
    str CONSTANT VARCHAR2(50) := 'Const Hello World';
BEGIN
    SYS.DBMS_OUTPUT.PUT_LINE(str);
END;

定数なので下記のように定数に値を代入しようとすると、エラーになります。

DECLARE
    --定数宣言
    str CONSTANT VARCHAR2(50) := 'Const Hello World';
BEGIN
    --定数に値を代入しようとするのでエラーになる。
    str := 'Change Const Value';
    SYS.DBMS_OUTPUT.PUT_LINE(str);
END;

【関連】
PLSQL 変数の精度について

PLSQL とりあえず最初はHelloWorldを出力する

最初はやっぱりHelloWorldを出力してみます。

まずはPLSQLからの出力結果を表示するためにDBMSウィンドウを表示させます。
メニュー「表示」　> 「DBMS出力」で表示します。

DBMSウィンドウの「＋」ボタンをクリックし、接続先を選択します。

PLSQLでHelloWorld

「ワークシート」に下記のPLSQLを入力し、三角ボタンより実行します。
DBMSウィンドウに「Hello World」が出力されれば成功です。
PLSQL

DECLARE

BEGIN

    SYS.DBMS_OUTPUT.PUT_LINE('Hello World');

END;

PLSQL SQL Developer からテーブル作成

前回「PLSQL 自宅でPLSQL実行環境を作成する」でPLSQLの実行環境を作成しましたが、データベースにテーブルがないとPLSQLを実行するのに不便なので、今回はSQL Developerからデータベースにテーブルを作成します。

「接続」よりデータベースを選択します
「ワークシート」にCreateTable文を記述し、三角ボタンで実行します。

実行したSQL

Create Table Member
(
 MemberId VARCHAR2(4)  NOT NULL ,
 MemberName VARCHAR2(10) ,
 Rank  VARCHAR2(2)  
);
Alter Table Member
Add Primary Key (MemberId)
;

次回以降はこのテーブルを使用してPLSQLを実行していきたいと思います。

PLSQL 自宅でPLSQL実行環境を作成する

PLSQLを習得する必要が出てきたので、自宅に実行環境を用意します。

Oracle Database Express Edition 11g Release 2

無償で使えるOracleデータベース「Oracle Database Express Edition 11g Release 2」をインストールします。

Oracle Database Express Edition 11g Release 2 は無料で使用できる代わりに、下記のような制限があります。

データサイズ4GBまでであること
サービスネーム(データベース名)が「XE」の固定であること
内部エンコードがUTF-8（AL32UTF8）の固定であること

下記サイトよりダウンロード
http://www.oracle.com/technetwork/jp/database/database-technologies/express-edition/downloads/index.html
ダウンロードにはユーザー登録が必要です。
私はWindows64bitOSなので「Oracle Database Express Edition 11g Release 2 for Windows x64」を選択しました。

ダウンロードしたファイルを実行し、インストール
※基本はデフォルトのままでOK

デフォルトインスタンスのSYS，SYSTEMユーザのパスワード入力

実行確認
スタート > すべてのプログラム > Oracle Database 11g Express Edition > SQLコマンドラインの実行　で「SQLコマンドラインの実行」を起動します。
「connect / as sysdba」と入力し、接続できれば成功です。

SQL Developer

つづいてOracleをGUIで操作できるSQL Developerを入手します。
下記サイトよりダウンロード
http://www.oracle.com/technetwork/jp/developer-tools/sql-developer/downloads/index.html
私はWindows64bitOSなので「Windows 64-bit with JDK 8 included」を選択しました。

ダウンロードしたファイルを解凍したフォルダ内に「sqldeveloper.exe」があるので、実行します。
プラスマークよりDB接続を追加します。

接続名：何でもOK
ユーザー名：system
パスワード：Oracleをインストールしたときに設定したパスワード

「テスト」ボタンを押して、ステータス：成功と出たら接続ボタン押下します。

とりあえずこれで、PLSQLを実行できる環境ができました。

GOTO文とラベル、NULL文の基本的な使い方

ループのContinue文の代わりにGOTO文を使用する（11g以前）

ループラベル

明示カーソル

暗黙カーソル

カーソルFORループを使用せず、明示的にカーソルのオープン、クローズを行うパターンでの使用例

カーソルFORループでの使用例

FunctionとSubを書き分ける必要がある

匿名クラスを集計やグループ化のキーにする場合は、Keyキーワードを使用する必要がある

オブジェクト初期化子

コレクション初期化子

匿名クラス

カーソルFORループ

FOR LOOP

WHILE LOOP(前判定ループ）

LOOP（後判定ループ） と EXIT文、EXIT WHEN文

CONTINUE文

単純CASE文

検索CASE文

結合配列（PL/SQL表、索引付表 ともいう）

結合配列の特徴の一部

結合配列の使い方

カーソルを使用する手順

PLSQLでHelloWorld

Oracle Database Express Edition 11g Release 2

SQL Developer

LOOP（後判定ループ）　と EXIT文、EXIT WHEN文

結合配列（PL/SQL表、索引付表ともいう）