按名称绑定参数(命名参数)Binding Parameters by Name (Named Parameters)

某些 Dbms 允许应用程序按名称(而不是在过程调用中的位置)来指定存储过程的参数。Certain DBMSs allow an application to specify the parameters to a stored procedure by name instead of by position in the procedure call. 此类参数称为命名参数Such parameters are called named parameters. ODBC 支持使用命名参数。ODBC supports the use of named parameters. 在 ODBC 中,命名参数仅用于对存储过程的调用,而不能用于其他 SQL 语句。In ODBC, named parameters are used only in calls to stored procedures and cannot be used in other SQL statements.

驱动程序将检查 IPD 的 SQL_DESC_UNNAMED 字段的值,以确定是否使用了命名参数。The driver checks the value of the SQL_DESC_UNNAMED field of the IPD to determine whether named parameters are used. 如果 SQL_DESC_UNNAMED 未设置为 SQL_UNNAMED,驱动程序将使用 IPD 的 SQL_DESC_NAME 字段中的名称来标识参数。If SQL_DESC_UNNAMED is not set to SQL_UNNAMED, the driver uses the name in the SQL_DESC_NAME field of the IPD to identify the parameter. 若要绑定参数,应用程序可以调用SQLBindParameter来指定参数信息,然后可以调用SQLSETDESCFIELD来设置 IPD 的 SQL_DESC_NAME 字段。To bind the parameter, an application can call SQLBindParameter to specify the parameter information and then can call SQLSetDescField to set the SQL_DESC_NAME field of the IPD. 使用命名参数时,过程调用中参数的顺序并不重要,并且忽略参数的记录号。When named parameters are used, the order of the parameter in the procedure call is not important and the parameter's record number is ignored.

未命名参数和命名参数之间的区别在于描述符的记录编号与过程中的参数编号之间的关系。The difference between unnamed parameters and named parameters is in the relationship between the record number of the descriptor and the parameter number in the procedure. 使用未命名参数时,第一个参数标记与参数描述符中的第一条记录相关,后者又与过程调用中的第一个参数(在创建顺序中)相关。When unnamed parameters are used, the first parameter marker is related to the first record in the parameter descriptor, which in turn is related to the first parameter (in creation order) in the procedure call. 使用命名参数时,第一个参数标记仍与参数描述符的第一条记录相关,但在此过程中,说明符的记录号和参数号之间的关系将不再存在。When named parameters are used, the first parameter marker is still related to the first record of the parameter descriptor, but the relationship between the record number of the descriptor and the parameter number in the procedure does not exist anymore. 命名参数不使用描述符记录号到过程参数位置的映射;相反,描述符记录名称将映射到过程参数名称。Named parameters do not use the mapping of the descriptor record number to the procedure parameter position; instead, the descriptor record name is mapped to the procedure parameter name.

备注

如果启用了 IPD 的自动填充,则驱动程序将填充描述符,以便描述符记录的顺序与过程定义中的参数顺序匹配,即使使用的是命名参数也是如此。If automatic population of the IPD is enabled, the driver will populate the descriptor such that the order of the descriptor records will match the order of the parameters in the procedure definition, even if named parameters are used.

如果使用命名参数,则所有参数都必须为命名参数。If a named parameter is used, all parameters must be named parameters. 如果任何参数不是命名参数,则任何参数 ca 都不是命名参数。If any parameter is not a named parameter, then none of the parameters ca be named parameters. 如果存在命名参数和未命名参数的混合,则行为取决于驱动程序。If there were a mixture of named parameters and unnamed parameters, the behavior would be driver-dependent.

作为命名参数的示例,假设已按如下所示定义 SQL Server 存储过程:As an example of named parameters, suppose a SQL Server stored procedure has been defined as follows:

CREATE PROCEDURE test @title_id int = 1, @quote char(30) AS <blah>  

在此过程中,第一个参数@title_id的默认值为1。In this procedure, the first parameter, @title_id, has a default value of 1. 应用程序可以使用以下代码调用此过程,以便仅指定一个动态参数。An application can use the following code to invoke this procedure such that it specifies only one dynamic parameter. 此参数是名为 "@quote" 的命名参数。This parameter is a named parameter with the name "@quote".

// Prepare the procedure invocation statement.  
SQLPrepare(hstmt, "{call test(?)}", SQL_NTS);  
  
// Populate record 1 of ipd.  
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,  
                  30, 0, szQuote, 0, &cbValue);  
  
// Get ipd handle and set the SQL_DESC_NAMED and SQL_DESC_UNNAMED fields  
// for record #1.  
SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_DESC, &hIpd, 0, 0);  
SQLSetDescField(hIpd, 1, SQL_DESC_NAME, "@quote", SQL_NTS);  
  
// Assuming that szQuote has been appropriately initialized,  
// execute.  
SQLExecute(hstmt);