046.htm

Delphi基础知识讲座

<p>DO</p>
<p>BEGIN</p>
<p>EXCEPTION customer_check;</p>
<p>　</p>
<p>UPDATE customer</font><font face="宋体" lang="ZH-CN"></p>
<p></font><font face="Arial">SET on_hold = &quot;*&quot;</font><font face="宋体"
lang="ZH-CN"></p>
<p></font><font face="Arial">WHERE cust_no = :cust_no;</p>
<p>　</p>
<p>SUSPEND;</p>
<p>END</p>
<p>　</p>
<p>UPDATE sales</p>
<p>SET order_status = &quot;shipped&quot;, ship_date = 'NOW'</p>
<p>WHERE po_number = :po_num;</p>
<p>SUSPEND;</p>
<p>END</p>
<p>　</p>
<p>Parameters:</p>
<p>PO_NUM INPUT CHAR(8) </p>
<p>　</font><font face="宋体" lang="ZH-CN"></p>
<p>　　该过程只带有一个输入参数：</font><font face="Arial">PO_NUM</font><font
face="宋体" lang="ZH-CN">，类型是</font><font face="Arial">CHAR(8)</font><font
face="宋体" lang="ZH-CN">。</p>
<p>　　在客户端使用该过程的</font><font face="Arial">TStoreProc</font><font
face="宋体" lang="ZH-CN">部件是</font><font face="Arial">ShipOrderProc</font><font
face="宋体" lang="ZH-CN">，其主要属性如下表：</p>
<p>　</font><font face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">表</font><font face="Arial">18.19
ShipOrderProc</font><font face="宋体" lang="ZH-CN">部件主要属性的取值</font><font
face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">━━━━━━━━━━━━━━━━━━━━━━━━━━━━</font><font
face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">　　　属性名　　　　　　　　　</font><font
face="Arial"> </font><font face="宋体" lang="ZH-CN">属</font><font face="Arial"> </font><font
face="宋体" lang="ZH-CN">性</font><font face="Arial"> </font><font face="宋体"
lang="ZH-CN">值</p>
<p>　</font><font face="Arial"> </font><font face="宋体" lang="ZH-CN">　</font><font
face="Arial"> </font><font face="宋体" lang="ZH-CN">────────────────────────────</p>
<p>　</font><font face="Arial"> </font><font face="宋体" lang="ZH-CN">　　</font><font
face="Arial">DatabaseName EmployeeDemoDB</p>
<p>ParamBindMode pbByName</p>
<p>Params PO_NUM(</font><font face="宋体" lang="ZH-CN">输入参数，</font><font
face="Arial">String</font><font face="宋体" lang="ZH-CN">类型</font><font face="Arial">)</p>
<p>StoredProcName SHIP_ORDER</p>
<p></font><font face="宋体" lang="ZH-CN">━━━━━━━━━━━━━━━━━━━━━━━━━━━━</p>
<p>　</p>
<p>　　客户端执行</font><font face="Arial">SHIP_ORDER</font><font face="宋体"
lang="ZH-CN">的程序如下：</p>
<p>　</p>
<p></font><font face="Arial">procedure TFrmExecProc.BtnShipOrderClick(Sender: TObject);</p>
<p>begin</p>
<p>with DmEmployee do</p>
<p>begin</p>
<p>ShipOrderProc.Params[0].AsString := SalesTable['PO_NUMBER'];</p>
<p>ShipOrderProc.ExecProc;</p>
<p>SalesTable.Refresh;</p>
<p>end;</p>
<p>end;</p>
<p>　</font><font face="宋体" lang="ZH-CN"></p>
<p>　　当用户按</font><font face="Arial">ShipOrder</font><font face="宋体"
lang="ZH-CN">按钮时，执行这段程序。程序中先准备输入参数，用</font><font
face="Arial">ExecProc</font><font face="宋体" lang="ZH-CN">方</p>
<p>法执行存储过程。调用</font><font face="Arial">SalesTable.Refresh</font><font
face="宋体" lang="ZH-CN">方法刷新数据显示。</p>
<p>　　在</font><font face="Arial">CSDEMO</font><font face="宋体" lang="ZH-CN">应用程序中另一个使用存储过程的</font><font
face="Arial">TStoredProc</font><font face="宋体" lang="ZH-CN">部件是</font><font
face="Arial">DeleteEmployeeProc</font><font face="宋体" lang="ZH-CN">。它完成的任务是删除</font><font
face="Arial">Employee</font><font face="宋体" lang="ZH-CN">表中的记录，并修改所有相关的表，</font><font
face="Arial"> </font><font face="宋体" lang="ZH-CN">以维护数据的一致性。其属性如下：</p>
<p>　</font><font face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">表</font><font face="Arial">18.20
DeleteEmployeeProc</font><font face="宋体" lang="ZH-CN">部件主要属性的取值</font><font
face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">　</font><font face="Arial"> </font><font
face="宋体" lang="ZH-CN">━━━━━━━━━━━━━━━━━━━━━━━━━━</font><font
face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">　　　属性名　　　　　　　　　</font><font
face="Arial"> </font><font face="宋体" lang="ZH-CN">属</font><font face="Arial"> </font><font
face="宋体" lang="ZH-CN">性</font><font face="Arial"> </font><font face="宋体"
lang="ZH-CN">值</font><font face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">　</font><font face="Arial"> </font><font
face="宋体" lang="ZH-CN">──────────────────────────</font><font
face="Arial"></p>
<p></font><font face="宋体" lang="ZH-CN">　　　</font><font face="Arial">DataBaseName
EmployeeDemoDB</p>
<p>ParamBindMode PbByName</p>
<p>Params EMP_NUM(</font><font face="宋体" lang="ZH-CN">输入参数，整型</font><font
face="Arial">)</p>
<p>StoredProcName DELETE_EMPLOYEE</p>
<p></font><font face="宋体" lang="ZH-CN">━━━━━━━━━━━━━━━━━━━━━━━━━━</p>
<p>　</p>
<p>　　存储过程</font><font face="Arial">DELETE_EMPLOYEE</font><font face="宋体"
lang="ZH-CN">的程序如下：</p>
<p>　</p>
<p></font><font face="Arial">PROCEDURE DELETE_EMPLOYEE</p>
<p>DECLARE VARIABLE any_sales INTEGER;</p>
<p>BEGIN</p>
<p>any_sales = 0;</p>
<p>SELECT count(po_number)</p>
<p>FROM sales</p>
<p>WHERE sales_rep = :emp_num</p>
<p>INTO :any_sales;</p>
<p>IF (any_sales &gt; 0) THEN</p>
<p>BEGIN</p>
<p>EXCEPTION reassign_sales;</p>
<p>SUSPEND;</p>
<p>END</p>
<p>UPDATE department</p>
<p>SET mngr_no = NULL</p>
<p>WHERE mngr_no = :emp_num;</p>
<p>UPDATE project</p>
<p>SET team_leader = NULL</p>
<p>WHERE team_leader = :emp_num;</p>
<p>DELETE FROM employee_project</p>
<p>WHERE emp_no = :emp_num;</p>
<p>DELETE FROM salary_history</p>
<p>WHERE emp_no = :emp_num;</p>
<p>DELETE FROM employee</p>
<p>WHERE emp_no = :emp_num;</p>
<p>SUSPEND;</p>
<p>END</p>
<p>　</p>
<p>Parameters:</p>
<p>EMP_NUM INPUT INTEGER </p>
<p>　</font><font face="宋体" lang="ZH-CN"></p>
<p>　　从上述存储过程的例子中，我们看到存储过程在维护服务器上的数据一致性方面有很强的能力，它节省了系统开销，提高了客户端的性能。</p>
<p>　</font><font face="Arial"></p>
<p>18.4.2.5 </font><font face="宋体" lang="ZH-CN">事务控制编程</p>
<p>　</p>
<p>　　在客户</font><font face="Arial">/</font><font face="宋体" lang="ZH-CN">服务器应用程序中，事务控制是一项很重要的技术。它对于提高系统的可靠性，维护数据一致性有着重要的意义。</p>
<p>　　</font><font face="Arial">Delphi</font><font face="宋体" lang="ZH-CN">中提供了事务的隐式和显式两种控制方法。其中显式控制的性能较高，下面介绍</font><font
face="Arial">Delphi</font><font face="宋体" lang="ZH-CN">事务显式控制的编程方法。</p>
<p>　　</font><font face="Arial">Delphi</font><font face="宋体" lang="ZH-CN">担当事务控制任务的部件是</font><font
face="Arial">TDatabase </font><font face="宋体" lang="ZH-CN">。</font><font
face="Arial">TDatabase </font><font face="宋体" lang="ZH-CN">用于事务控制的属性是</font><font
face="Arial">TransIsolation</font><font face="宋体" lang="ZH-CN">，方法有</font><font
face="Arial">StartTranstion</font><font face="宋体" lang="ZH-CN">、</font><font
face="Arial">Commit</font><font face="宋体" lang="ZH-CN">和</font><font face="Arial">Rollback</font><font
face="宋体" lang="ZH-CN">。关于这些属性和方法作用和使用方法请参阅客户</font><font
face="Arial">/</font><font face="宋体" lang="ZH-CN">服务器事务管理。</p>
<p>　　在</font><font face="Arial">CSDEMO</font><font face="宋体" lang="ZH-CN">中</font><font
face="Arial">TDatabase </font><font face="宋体" lang="ZH-CN">部件为</font><font
face="Arial">EMployeeDatabase</font><font face="宋体" lang="ZH-CN">，其</font><font
face="Arial">TransIsolation</font><font face="宋体" lang="ZH-CN">属性值为</font><font
face="Arial">tiReadCommitted</font><font face="宋体" lang="ZH-CN">，意为如果存在多个同时事务访问数据库，则其中任一事务只能读其它事务提交的了数据。</p>
<p>　　</font><font face="Arial">CSDEMO</font><font face="宋体" lang="ZH-CN">中演示事务控制的窗体是</font><font
face="Arial">TFrmTransDemo</font><font face="宋体" lang="ZH-CN">。</p>
<p>　　</font><font face="Arial">DBGrid1</font><font face="宋体" lang="ZH-CN">中显示</font><font
face="Arial">EmployeeTable</font><font face="宋体" lang="ZH-CN">中的内容。当窗口显示时，</font><font
face="Arial">EmployeeDatabase</font><font face="宋体" lang="ZH-CN">开始一次事务控制并激活</font><font
face="Arial">EmployeeTable</font><font face="宋体" lang="ZH-CN">：</p>
<p>　</p>
<p></font><font face="Arial">procedure TFrmTransDemo.FormShow(Sender: TObject);</p>
<p>begin</p>
<p>DmEmployee.EmployeeDatabase.StartTransaction;</p>
<p>DmEmployee.EmployeeTable.Open;</p>
<p>end;</p>
<p>　</p>
<p></font><font face="宋体" lang="ZH-CN">当窗口被关闭或隐藏时，</font><font
face="Arial">EmployeeDatabase</font><font face="宋体" lang="ZH-CN">提交事务：</p>
<p>　</p>
<p></font><font face="Arial">procedure TFrmTransDemo.FormHide(Sender: TObject);</p>
<p>begin</p>
<p>DmEmployee.EmployeeDatabase.Commit;</p>
<p>end;</p>
<p>　</font><font face="宋体" lang="ZH-CN"></p>
<p>　　窗口中有两个按钮</font><font face="Arial">BtnCommitEdits</font><font
face="宋体" lang="ZH-CN">和</font><font face="Arial">BtnUndoEdits</font><font
face="宋体" lang="ZH-CN">。按下</font><font face="Arial">BtnCommitEdits</font><font
face="宋体" lang="ZH-CN">按钮将提交当前事务，并开始新的事务控制并刷新数据。</p>
<p>　</p>
<p></font><font face="Arial">procedure TFrmTransDemo.BtnCommitEditsClick(Sender: TObject);</p>
<p>begin</p>
<p>if DmEmployee.EmployeeDatabase.InTransaction and</p>
<p>(MessageDlg('Are you sure you want to commit your changes?',</p>
<p>mtConfirmation, [mbYes, mbNo], 0) = mrYes) then</p>
<p>begin</p>
<p>DmEmployee.EmployeeDatabase.Commit;</p>
<p>DmEmployee.EmployeeDatabase.StartTransaction;</p>
<p>DmEmployee.EmployeeTable.Refresh;</p>
<p>end else</p>
<p>MessageDlg('Can''t Commit Changes: No Transaction Active', mtError, [mbOk], 0);</p>
<p>end;</p>
<p>　</font><font face="宋体" lang="ZH-CN"></p>
<p>　　按下</font><font face="Arial">BtnUndoEdits</font><font face="宋体"
lang="ZH-CN">按钮将返转当前事物，恢复原来的数据，开始新的事务控制，并刷新数据的显示。</p>
<p>　</p>
<p></font><font face="Arial">procedure TFrmTransDemo.BtnUndoEditsClick(Sender: TObject);</p>
<p>begin</font><font face="宋体" lang="ZH-CN"></p>
<p></font><font face="Arial">if DmEmployee.EmployeeDatabase.InTransaction and</font><font
face="宋体" lang="ZH-CN"></p>
<p></font><font face="Arial">(MessageDlg('Are you sure you want to undo all changes made
during the ' +</p>
<p>'current transaction?', mtConfirmation, [mbYes, mbNo], 0) = mrYes) then</p>
<p>begin</p>
<p>DmEmployee.EmployeeDatabase.Rollback;</p>
<p>DmEmployee.EmployeeDatabase.StartTransaction;</p>
<p>DmEmployee.EmployeeTable.Refresh;</p>
<p>end else</p>
<p>MessageDlg('Can''t Undo Edits: No Transaction Active', mtError, [mbOk], 0);</p>
<p>end;</font></p>
<hr color="#EE9B73" size="1" width="94%">

</TD>
<TD CLASS="tt3" VALIGN="bottom" width="8%"  background="bg.gif"><strong><A HREF="047.htm" ><FONT style="FONT-SIZE: 9pt">后一页</font></A><BR>
<A HREF="045.htm" ><FONT style="FONT-SIZE: 9pt">前一页</font></A><BR>

<A HREF="index.html" ><FONT style="FONT-SIZE: 9pt">回目录</font></A><BR>
<A HREF="../../../../../index.htm"><FONT style="FONT-SIZE: 9pt">回首页</font></A><BR>
</strong>
</TD>
</TR>
</table>
</BODY></HTML>