行の有無を調べる(ORACLE)

マスタのチェックなどによく使います。

条件に合致した行を調べるのに SQLのcount関数を使ったりしますが、有り無しだけを調べるのであれば、rownumを使うといいです。

select 'HOGE' from table where ....
and
rownum <= 1

VB.NETで行の有無を調べる場合、count関数だと OracleDataReader.read して、列内容を調べる必要があるのに対し、rownumを使った場合は、目的の行がない場合、結果が返ってこないので一手間省けます。

サンプル

    ''' 
    ''' Count関数で行有無を調べる
    ''' 
    ''' 
    ''' 
     Private Function isPresenceByCount() As Boolean

        Const cmdSelect As String = "select count(EMPNO) from EMPTEST where EMPNO = :P_EMPNO"
        Dim rd As Oracle.DataAccess.Client.OracleDataReader = Nothing
        Dim isPresence As Boolean = False

        'ストップウオッチ
        Dim sw As New System.Diagnostics.Stopwatch()

        Try
            'ストップウォッチを開始する 
            sw.Start()

            Using cn As New Oracle.DataAccess.Client.OracleConnection(My.Settings.ConnectionString)
                cn.Open()

                Using cmd As New Oracle.DataAccess.Client.OracleCommand(cmdSelect, cn)
                    cmd.BindByName = True
                    cmd.Parameters.Add("P_EMPNO", Oracle.DataAccess.Client.OracleDbType.Int32).Value = CInt(TextBox1.Text)
                    rd = cmd.ExecuteReader
                    If rd.Read Then
                        If rd.IsDBNull(0) Then
                            If rd.GetInt32(0) > 0 Then
                                isPresence = True
                            End If
                        End If
                    End If
                    rd.Close()

                End Using

                cn.Clone()

            End Using

            Return isPresence

        Catch ex As Exception
            Throw ex
        Finally
            If Not IsNothing(rd) Then
                If Not rd.IsClosed Then
                    rd.Close()
                End If
                rd.Dispose()
            End If
        End Try
    End Function

    ''' 
    ''' ROWNUMで行有無を調べる
    ''' 
    ''' 
    ''' 
    Private Function isPresenceByROWNUM() As Boolean

        Const cmdSelect As String = "select 'HOGE' from EMPTEST where EMPNO = :P_EMPNO and ROWNUM <= :P_ROWNUM"
        Dim rd As Oracle.DataAccess.Client.OracleDataReader = Nothing
        Dim isPresence As Boolean = False

        Try
            Using cn As New Oracle.DataAccess.Client.OracleConnection(My.Settings.ConnectionString)
                cn.Open()

                Using cmd As New Oracle.DataAccess.Client.OracleCommand(cmdSelect, cn)
                    cmd.BindByName = True
                    cmd.Parameters.Add("P_EMPNO", Oracle.DataAccess.Client.OracleDbType.Int32).Value = CInt(TextBox1.Text)
                    cmd.Parameters.Add("P_ROWNUM", Oracle.DataAccess.Client.OracleDbType.Int32).Value = 1
                    rd = cmd.ExecuteReader
                    If rd.Read Then
                        isPresence = True
                    End If
                    rd.Close()

                End Using

                cn.Clone()

            End Using

            Return isPresence

        Catch ex As Exception
            Throw ex
        Finally
            If Not IsNothing(rd) Then
                If Not rd.IsClosed Then
                    rd.Close()
                End If
                rd.Dispose()
            End If
        End Try
    End Function

履歴

2011/08/10
公開
Loading