php odbc sqlserver update操作,模拟定位的 Update 和 Delete 语句 - SQL Server | Microsoft Docs...

模拟定位更新和删除语句Simulating Positioned Update and Delete Statements

01/19/2017

本文内容

如果数据源不支持定位的 update 和 delete 语句，则驱动程序可以模拟这些语句。If the data source does not support positioned update and delete statements, the driver can simulate these. 例如，ODBC 游标库模拟定位的 update 和 delete 语句。For example, the ODBC cursor library simulates positioned update and delete statements. 用于模拟定位的 update 和 delete 语句的常规策略是将定位语句转换为搜索语句。The general strategy for simulating positioned update and delete statements is to convert positioned statements to searched ones. 这是通过将 WHERE CURRENT of 子句替换为用于标识当前行的搜索 where 子句来完成的。This is done by replacing the WHERE CURRENT OF clause with a searched WHERE clause that identifies the current row.

例如，由于 CustID 列唯一标识 Customers 表中的每一行，因此定位的 delete 语句For example, because the CustID column uniquely identifies each row in the Customers table, the positioned delete statement

DELETE FROM Customers WHERE CURRENT OF CustCursor

可能被转换为might be converted to

DELETE FROM Customers WHERE (CustID = ?)

驱动程序可以在WHERE子句中使用以下行标识符之一：The driver may use one of the following row identifiers in the WHERE clause:

其值用于唯一标识表中的每一行的列。Columns whose values serve to identify uniquely every row in the table. 例如，通过 SQL_BEST_ROWID 调用 SQLSpecialColumns 将返回为此目的而提供服务的最优列或一组列。For example, calling SQLSpecialColumns with SQL_BEST_ROWID returns the optimal column or set of columns that serve this purpose.

由某些数据源提供的伪列，目的是唯一标识每个行。Pseudo-columns, provided by some data sources, for the purpose of uniquely identifying every row. 它们还可以通过调用 SQLSpecialColumns来检索。These may also be retrievable by calling SQLSpecialColumns.

唯一索引(如果可用)。A unique index, if available.

结果集中的所有列。All the columns in the result set.

驱动程序在其构造的 WHERE 子句中使用的确切列取决于驱动程序。Exactly which columns a driver should use in the WHERE clause it constructs depends on the driver. 在某些数据源上，确定行标识符可能会很高。On some data sources, determining a row identifier can be costly. 但是，执行速度更快，并保证模拟语句最多更新或删除一行。However, it is faster to execute and guarantees that a simulated statement updates or deletes at most one row. 根据基础 DBMS 的功能，设置行标识符可能需要很高的成本。Depending on the capabilities of the underlying DBMS, using a row identifier can be expensive to set up. 但是，执行速度更快，并保证模拟语句仅更新或删除一行。However, it is faster to execute and guarantees that a simulated statement will update or delete only one row. 使用结果集中所有列的选项通常更容易进行设置。The option of using all the columns in the result set is usually much easier to set up. 但是，执行速度较慢，并且如果列没有唯一地标识行，则可能会导致意外更新或删除行，尤其是在结果集的选择列表不包含基础表中存在的所有列的情况下。However, it is slower to execute and, if the columns do not uniquely identify a row, can result in rows being unintentionally updated or deleted, especially when the select list for the result set does not contain all the columns that exist in the underlying table.

根据驱动程序支持的上述策略，应用程序可以选择它希望驱动程序与 SQL_ATTR_SIMULATE_CURSOR 语句属性一起使用的策略。Depending upon which of the preceding strategies the driver supports, an application can choose which strategy it wants the driver to use with the SQL_ATTR_SIMULATE_CURSOR statement attribute. 尽管应用程序可能会在无意中更新或删除行的情况下出现异常情况，但应用程序可以通过确保结果集中的列唯一标识结果集中的每一行来消除此风险。Although it might seem odd for an application to risk unintentionally updating or deleting a row, the application can remove this risk by ensuring that the columns in the result set uniquely identify each row in the result set. 这样就可以节省驱动程序的工作量。This saves the driver the effort of having to do this.

如果驱动程序选择使用行标识符，则会截获创建结果集的 SELECT FOR UPDATE 语句。If the driver chooses to use a row identifier, it intercepts the SELECT FOR UPDATE statement that creates the result set. 如果选择列表中的列不能有效地标识行，则驱动程序会将所需的列添加到选择列表的末尾。If the columns in the select list do not effectively identify a row, the driver adds the necessary columns to the end of the select list. 某些数据源具有始终唯一标识行的单列，如 Oracle 中的 ROWID 列;如果此类列可用，则驱动程序将使用此类。Some data sources have a single column that always uniquely identifies a row, such as the ROWID column in Oracle; if such a column is available, the driver uses this. 否则，驱动程序将为FROM子句中的每个表调用SQLSpecialColumns ，以检索唯一标识每行的列的列表。Otherwise, the driver calls SQLSpecialColumns for each table in the FROM clause to retrieve a list of the columns that uniquely identify each row. 此方法产生的一个常见限制是，如果 from 子句中有多个表，则游标模拟失败。A common restriction that results from this technique is that cursor simulation fails if there is more than one table in the FROM clause.

无论驱动程序如何标识行，在将其发送到数据源之前，它通常会将子句 更新为 SELECT for update 语句。No matter how the driver identifies rows, it usually strips the FOR UPDATE OF clause off the SELECT FOR UPDATE statement before sending it to the data source. FOR update of 子句仅用于定位的 update 和 delete 语句。The FOR UPDATE OF clause is used only with positioned update and delete statements. 不支持定位更新和删除语句的数据源通常不支持。Data sources that do not support positioned update and delete statements generally do not support it.

当应用程序提交要执行的定位更新或删除语句时，驱动程序会将 WHERE CURRENT of 子句替换为包含行标识符的 where 子句。When the application submits a positioned update or delete statement for execution, the driver replaces the WHERE CURRENT OF clause with a WHERE clause containing the row identifier. 这些列的值是从驱动程序为在 WHERE 子句中使用的每个列维护的缓存中检索的。The values of these columns are retrieved from a cache maintained by the driver for each column it uses in the WHERE clause. 驱动程序替换了 WHERE 子句后，它会将语句发送到数据源以便执行。After the driver has replaced the WHERE clause, it sends the statement to the data source for execution.

例如，假设应用程序提交以下语句以创建结果集：For example, suppose that the application submits the following statement to create a result set:

SELECT Name, Address, Phone FROM Customers FOR UPDATE OF Phone, Address

如果应用程序已将 SQL_ATTR_SIMULATE_CURSOR 设置为请求唯一性保证，并且如果数据源未提供始终唯一标识行的伪列，则该驱动程序将为 Customers 表调用 SQLSpecialColumns ，发现 CustID 是 customers 表的键，并将其添加到选择列表，并将其添加到 select 列表，并将其用于子句的 UPDATE 语句：If the application has set SQL_ATTR_SIMULATE_CURSOR to request a guarantee of uniqueness and if the data source does not provide a pseudo-column that always uniquely identifies a row, the driver calls SQLSpecialColumns for the Customers table, discovers that CustID is the key to the Customers table and adds this to the select list, and strips the FOR UPDATE OF clause:

SELECT Name, Address, Phone, CustID FROM Customers

如果应用程序未请求保证唯一性，驱动程序将仅去除子句 更新 ：If the application has not requested a guarantee of uniqueness, the driver strips only the FOR UPDATE OF clause:

SELECT Name, Address, Phone FROM Customers

假设应用程序滚动结果集，并提交以下位置的 update 语句来执行，其中，"用户名" 是结果集上的游标的名称：Suppose the application scrolls through the result set and submits the following positioned update statement for execution, where Cust is the name of the cursor over the result set:

UPDATE Customers SET Address = ?, Phone = ? WHERE CURRENT OF Cust

如果应用程序未请求保证唯一性，驱动程序将替换 WHERE 子句，并将 CustID 参数绑定到其缓存中的变量：If the application has not requested a guarantee of uniqueness, the driver replaces the WHERE clause and binds the CustID parameter to the variable in its cache:

UPDATE Customers SET Address = ?, Phone = ? WHERE (CustID = ?)

如果应用程序未请求保证唯一性，则驱动程序将替换 WHERE 子句，并将此子句中的名称、地址和电话参数绑定到其缓存中的变量：If the application has not requested a guarantee of uniqueness, the driver replaces the WHERE clause and binds the Name, Address, and Phone parameters in this clause to the variables in its cache:

UPDATE Customers SET Address = ?, Phone = ?

WHERE (Name = ?) AND (Address = ?) AND (Phone = ?)