萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

VB 連接 Oracle 資料庫完整範例:ADO 連線、記錄集操作、資源釋放

完整 VB6/VBA 連接 Oracle 代碼模組


' ==========================================
' 模組級變數宣告
' ==========================================
Option Explicit

Private OraDB As ADODB.Connection
Private OraRS As ADODB.Recordset
Private OraDB_Open As Boolean
Private OraConstr As String
Private strOraUser As String
Private strOraPWD As String
Private OraDBNetName As String
Private strOraPID As String
Private strName As String
Private strPsex As String
Private OraDBtablename As String

' ==========================================
' 開啟資料庫連線
' ==========================================
Public Sub OpenOraDB()
    On Error GoTo ToExit
    OraDB_Open = False
    
    Set OraDB = New ADODB.Connection
    
    ' OLE DB Provider for Oracle 連接字串
    OraConstr = "Provider=OraOLEDB.Oracle.1;" & _
                "Password=" & strOraPWD & ";" & _
                "User ID=" & strOraUser & ";" & _
                "Data Source=" & OraDBNetName & ";" & _
                "Persist Security Info=False"
    
    ' 使用伺服器端游標
    OraDB.CursorLocation = adUseServer
    
    ' 開啟連線
    OraDB.Open OraConstr
    OraDB_Open = True
    
    Exit Sub

ToExit:
    ' MsgBox "連接資料庫伺服器錯誤,網路恢復後可繼續使用。", vbInformation, "錯誤訊息"
    OraDB_Open = False
    Err.Raise vbObjectError + 513, "OpenOraDB", Err.Description
End Sub

' ==========================================
' 關閉資料庫連線
' ==========================================
Public Sub CloseOraDB()
    If OraDB_Open = True Then
        If (OraDB.State = adStateOpen) Then
            OraDB.Close
            Set OraDB = Nothing
            OraDB_Open = False
        End If
    End If
End Sub

' ==========================================
' 開啟記錄集並寫入資料
' ==========================================
Public Sub InsertData()
    On Error GoTo ErrHandler
    
    Set OraRS = New ADODB.Recordset
    OraRS.ActiveConnection = OraDB
    OraRS.CursorLocation = adUseServer
    OraRS.LockType = adLockBatchOptimistic
    
    ' 動態 SQL
    Dim strOraRS As String
    strOraRS = "SELECT * FROM " & OraDBtablename
    
    ' 開啟靜態游標、樂觀鎖定
    OraRS.Open strOraRS, OraDB, adOpenStatic, adLockOptimistic
    
    ' 新增記錄
    OraRS.AddNew
    OraRS.Fields("PID").Value = strOraPID
    OraRS.Fields("pname").Value = strName
    OraRS.Fields("psex").Value = strPsex
    OraRS.Update
    
    ' 關閉記錄集
    OraRS.Close
    Set OraRS = Nothing
    Exit Sub

ErrHandler:
    If Not OraRS Is Nothing Then
        If OraRS.State = adStateOpen Then OraRS.Close
        Set OraRS = Nothing
    End If
    Err.Raise vbObjectError + 514, "InsertData", Err.Description
End Sub

關鍵點解析

項目說明
ProviderOraOLEDB.Oracle.1 為 Oracle 官方 OLE DB 提供者,需安裝 Oracle Client
CursorLocationadUseServer 伺服器端游標,適合大量資料;adUseClient 客戶端游標支援離線操作
LockTypeadLockBatchOptimistic 批次樂觀鎖定,適合斷線式更新;adLockOptimistic 即時樂觀鎖定
CursorTypeadOpenStatic 靜態游標(快照),不反映他人變更;adOpenKeyset/adOpenDynamic 反映度較高
錯誤處理使用 On Error GoTo 統一跳轉,確保資源在異常時正確釋放

現代化建議 (VB.NET / C#)


' VB.NET 使用 ODP.NET (Oracle.ManagedDataAccess)
Imports Oracle.ManagedDataAccess.Client

Using conn As New OracleConnection("User Id=scott;Password=tiger;Data Source=orcl;")
    conn.Open()
    Using cmd = conn.CreateCommand()
        cmd.CommandText = "INSERT INTO employees (emp_id, emp_name) VALUES (:id, :name)"
        cmd.Parameters.Add(":id", 1001)
        cmd.Parameters.Add(":name", "John Chen")
        cmd.ExecuteNonQuery()
    End Using
End Using
  • 優先使用 ODP.NET Managed Driver(無需安裝 Oracle Client,單一 DLL 部署)
  • 採用 Using 區塊自動釋放資源
  • 參數化查詢防止 SQL 注入
  • 非同步模式 await cmd.ExecuteNonQueryAsync() 提升 UI 回應

常見連線問題

ORA-12154: TNS:could not resolve the connect identifier specified
tnsnames.ora 設定錯誤,或 Data Source 名稱拼寫錯誤
ORA-12541: TNS:no listener
監聽器未啟動,lsnrctl start
Provider cannot be found
未安裝 Oracle Client / OLE DB Provider,或位元數不符(32-bit App 需 32-bit Client)
權限不足
用戶缺乏 CREATE SESSION、INSERT 權限
copyright © 萬盛學電腦網 all rights reserved