VB.NETとPLSQLで複数件をinsertする

VB.NETとPLSQLで複数件をinsertするサンプルです。
VB.NETから配列を渡します。

確認環境
・Microsoft Visual Studio Community 2019
・Oracle Express Edition 11g Release 2
・Oracle.ManagedDataAccess 19.5.0
・Windows 10

目次

VB.NETのコード

VB.NETのコードです。

Imports Oracle.ManagedDataAccess.Client

Public Class Class3
    ''' <summary>
    ''' VBから配列を渡して登録する
    ''' </summary>
    Sub getDBtest1()

        Dim ar1() As String = {"1", "2", "3"} '配列
        Dim ar2() As String = {"鈴木", "田中", "佐藤"} '配列

        Dim connString As New String(
            "User Id=hr;Password=hr;Data Source=localhost/XE;")
        Using conn As New OracleConnection(connString)
            Dim cmd As New OracleCommand()

            Try
                conn.Open()
                cmd.Connection = conn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "TEST_SYAIN_INS.INS_SYAIN"

                Dim syainId As OracleParameter = New OracleParameter(
                    "IN_ID", OracleDbType.Decimal)
                syainId.CollectionType =
                    OracleCollectionType.PLSQLAssociativeArray
                syainId.Direction = ParameterDirection.Input
                syainId.Value = ar1
                syainId.Size = ar1.Length

                cmd.Parameters.Add(syainId)

                Dim syainName As OracleParameter = New OracleParameter(
                    "IN_NAME", OracleDbType.Varchar2)
                syainName.CollectionType =
                    OracleCollectionType.PLSQLAssociativeArray
                syainName.Direction = ParameterDirection.Input
                syainName.Value = ar2
                syainName.Size = ar2.Length

                cmd.Parameters.Add(syainName)

                cmd.Parameters.Add(New OracleParameter(
                  "OUT_SQLCODE", OracleDbType.Decimal)
                  ).Direction = ParameterDirection.Output
                cmd.Parameters.Add(New OracleParameter(
                  "OUT_SQLERR", OracleDbType.Varchar2)
                  ).Direction = ParameterDirection.Output

                cmd.ExecuteNonQuery()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
    End Sub
End Class

9,10行目は、配列です。
12,13行目は、接続情報です。useridとパスワードとデータソースを指定しています。
14行目は接続です。
20,21行目は接続先のPLSQLを指定しています。
23,33行目は、送信する値です。
26,36行目は、PLSQLAssociativeArrayを指定しています。Associative Array=連想配列です。
28,38行目は、配列をvalueにセットしています。

43,46行目は、受信する値です。SQLCODEとSQLERRを指定しています。
50行目は、ExecuteNonQueryメソッドで実行しています。
52行目は、例外が発生したときにcatchされて実行されます。
55行目のEnd Usingで接続はcloseします。

 

PLSQLの仕様部

PLSQLの仕様部のコードです。

CREATE OR REPLACE PACKAGE TEST_SYAIN_INS
IS
/*
*  配列で登録のテスト用
*/
TYPE t_id1       IS TABLE OF syain.id%TYPE index by binary_integer;
TYPE t_syainName IS TABLE OF syain.name%TYPE index by binary_integer;

PROCEDURE INS_SYAIN(
IN_ID     IN  t_id1,
IN_NAME   IN  t_syainName,
OUT_SQLCODE OUT NUMBER,
OUT_SQLERR  OUT VARCHAR2
);
END;

6,7行目は、連想配列を宣言しています。%TYPEは、DBのテーブルの項目と同じ型とサイズになります。
10,11行目は、INとなっています。6,7行目で宣言した型で入力される値を受け取ります。

 

PLSQLの本体部

PLSQLの本体部のコードです。

CREATE OR REPLACE PACKAGE BODY TEST_SYAIN_INS
IS
/*
*  配列で登録のテスト用
*/
PROCEDURE INS_SYAIN(
  IN_ID IN t_id1,
  IN_NAME IN t_syainName,
  OUT_SQLCODE OUT NUMBER,
  OUT_SQLERR  OUT VARCHAR2
)
IS
BEGIN
  DELETE FROM SYAIN;

  --FOR i IN 1..IN_ID.COUNT LOOP
  FOR i IN IN_ID.first..IN_ID.last LOOP
    INSERT INTO syain VALUES (IN_ID(i),IN_NAME(i),'romaji');
  END LOOP;
  commit;

EXCEPTION
  WHEN OTHERS THEN
    OUT_SQLCODE := SQLCODE; --正常終了時は0
    OUT_SQLERR := SQLERRM; --エラー情報
    rollback;

END INS_SYAIN;
END;

14行目は、一旦テーブルのデータをDeleteで削除しています。
17から19行目は、取得した配列の数分ループ処理をしてInsert文を実行しています。
16行目の書き方でも可能です。添字の最初は1から始まります。
20行目は、commitで処理を確定させます。
22行目は、例外発生時に実行されます。
24行目のSQLCODEは、正常終了時は0が入ります。
25行目のSQLERRMは、エラー情報が入ります。
26行目は、rollbackでロールバック処理を行います。

関連の記事

VB.NET Oracle11gに接続してselectするサンプル
VB.NETとPLSQLのREF CURSORでselectする

△上に戻る