语句参数Statement Parameters

参数是 SQL 语句中的变量。A parameter is a variable in an SQL statement. 例如,假设 Parts 表包含名为 PartID、Description 和 Price 的列。For example, suppose a Parts table has columns named PartID, Description, and Price. 若要添加没有参数的部件,需要构造如下所示的 SQL 语句:To add a part without parameters would require constructing an SQL statement such as:

INSERT INTO Parts (PartID, Description, Price) VALUES (2100, 'Drive shaft', 50.00)  

尽管此语句插入新订单,但它不是订单输入应用程序的好解决方案,因为在应用程序中要插入的值不能进行硬编码。Although this statement inserts a new order, it is not a good solution for an order entry application because the values to insert cannot be hard-coded in the application. 一种替代方法是使用要插入的值在运行时构造 SQL 语句。An alternative is to construct the SQL statement at run time, using the values to be inserted. 这也不是一个很好的解决方案,因为在运行时构造语句的复杂性很高。This is also not a good solution, because of the complexity of constructing statements at run time. 最佳解决方案是将VALUES子句的元素替换为问号(?)或参数标记The best solution is to replace the elements of the VALUES clause with question marks (?), or parameter markers:

INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)  

参数标记然后被绑定到应用程序变量。The parameter markers are then bound to application variables. 若要添加新行,应用程序只需要设置变量的值并执行语句。To add a new row, the application has only to set the values of the variables and execute the statement. 然后,驱动程序检索变量的当前值并将其发送至数据源。The driver then retrieves the current values of the variables and sends them to the data source. 如果多次执行该语句,则应用程序可以通过准备语句使该过程更高效。If the statement will be executed multiple times, the application can make the process even more efficient by preparing the statement.

刚才显示的语句可能在订单输入应用程序中硬编码为插入新行。The statement just shown might be hard-coded in an order entry application to insert a new row. 但参数标记并不限于垂直应用程序。However, parameter markers are not limited to vertical applications. 对于任何应用程序,都可以避免在运行时通过与文本之间的转换来构建 SQL 语句。For any application, they ease the difficulty of constructing SQL statements at run time by avoiding conversions to and from text. 例如,刚才显示的部分 ID 很可能以整数的形式存储在应用程序中。For example, the part ID just shown is most likely stored in the application as an integer. 如果未使用参数标记构造 SQL 语句,则应用程序必须将部件 ID 转换为文本,并且数据源必须将其转换回整数。If the SQL statement is constructed without parameter markers, the application must convert the part ID to text and the data source must convert it back to an integer. 通过使用参数标记,应用程序可以将部分 ID 作为整数发送到驱动程序,这通常可以将其作为整数发送到数据源。By using a parameter marker, the application can send the part ID to the driver as an integer, which usually can send it to the data source as an integer. 这会保存两个转换。This saves two conversions. 对于长数据值,这一点非常重要,因为此类值的文本格式经常超出了 SQL 语句允许的长度。For long data values, this is very important, because the text forms of such values frequently exceed the allowed length of an SQL statement.

参数仅在 SQL 语句的某些位置有效。Parameters are valid only in certain places in SQL statements. 例如,它们不允许在选择列表中(由select语句返回的列的列表),也不允许它们作为二元运算符(如等号(=))的两个操作数,因为无法确定参数类型。For example, they are not allowed in the select list (the list of columns to be returned by a SELECT statement), nor are they allowed as both operands of a binary operator such as the equal sign (=), because it would be impossible to determine the parameter type. 通常,参数仅在数据操作语言(DML)语句中有效,而不是在数据定义语言(DDL)语句中。Generally, parameters are valid only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements. 有关详细信息,请参阅附录 C: SQL 语法中的参数标记For more information, see Parameter Markers in Appendix C: SQL Grammar.

当 SQL 语句调用过程时,可以使用命名参数。When the SQL statement invokes a procedure, named parameters can be used. 命名参数由其名称标识,而不是按其在 SQL 语句中的位置标识。Named parameters are identified by their names, not by their position in the SQL statement. 可以通过调用SQLBindParameter来绑定参数,但参数由 IPD (实现参数描述符)的 SQL_DESC_NAME 字段标识,而不是由SQLBindParameterParameterNumber参数标识。They can be bound by a call to SQLBindParameter, but the parameter is identified by the SQL_DESC_NAME field of the IPD (implementation parameter descriptor), not by the ParameterNumber argument of SQLBindParameter. 还可以通过调用SQLSetDescFieldSQLSetDescRec来绑定它们。They can also be bound by calling SQLSetDescField or SQLSetDescRec. 有关命名参数的详细信息,请参阅本节后面的按名称绑定参数(命名参数)For more information about named parameters, see Binding Parameters by Name (Named Parameters), later in this section. 有关描述符的详细信息,请参阅描述符For more information about descriptors, see Descriptors.

本部分包含下列主题。This section contains the following topics.