ASP 中使用 Oracle Objects for OLE (OO4O) 實現資料存取（下）

完整程式碼範例：查詢所有記錄

<%
' 建立 OraSession 物件
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

' 開啟資料庫連線建立 OraDatabase
Set OraDatabase = OraSession.OpenDatabase("", "user/password", CInt(0))

' 執行 SQL 建立 OraDynaset
Set OraDynaset = OraDatabase.DbCreateDynaset("SELECT * FROM mytable1", CInt(0))
%>
<html><body>
<h3>Retrieve All Records in MYTABLE1 (Oracle) Using OO4O</h3>
<table border=1 id="Table1">
<%
Do While OraDynaset.EOF = FALSE
  Response.Write ""
  Response.Write OraDynaset.Fields("ID")
  Response.Write ""
  Response.Write OraDynaset.Fields("UserName")
  Response.Write ""
  Response.Write OraDynaset.Fields("Phone")
  Response.Write ""
  Response.Write OraDynaset.Fields("Email")
  Response.Write ""
  OraDynaset.MoveNext
Loop

' 釋放物件
Set OraSession = Nothing
%>
</table>
<a href="JavaScript:window.history.go(-1)">Back</a> |
<a href="index.htm">Home</a>
</body></html>

更新記錄範例

<%
fID = Request.Form("ID")
fUserName = Request.Form("UserName")
fPhone = Request.Form("Phone")
fEmail = Request.Form("Email")

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("", "user/password", CInt(0))

' 針對特定 ID 建立 Dynaset
Set OraDynaset = OraDatabase.CreateDynaset("SELECT * FROM mytable1 WHERE ID = " & fID, CInt(0))

Do While OraDynaset.EOF = FALSE
  OraDynaset.Edit
  OraDynaset.Fields("UserName").Value = fUserName
  OraDynaset.Fields("Phone").Value = fPhone
  OraDynaset.Update
  OraDynaset.MoveNext
Loop

Set OraSession = Nothing
%>
<html><body>
<h3>Record Updated Successfully!</h3>
<a href="RetrieveAllRec.asp">View Result</a>
</body></html>

刪除記錄

<%
Set OraDynaset = OraDatabase.CreateDynaset("SELECT * FROM mytable1 WHERE ID = " & fID, CInt(0))
Do While OraDynaset.EOF = FALSE
  OraDynaset.Delete
  OraDynaset.MoveNext
Loop
%>

使用預存程序建議

將商業邏輯封裝於 PL/SQL 預存程序，ASP 僅呼叫 OraDatabase.CreatePLSQLBlock 或透過 ADO 執行，提升安全性、效能與維護性。OO4O 完整支援 PL/SQL 執行。