本节介绍 C API 中对使用
CALL
语句执行的存储过程的准备语句支持:
使用准备好的语句执行的存储过程
CALL
可以通过以下方式使用:
存储过程可以产生任意数量的结果集。所有结果集的列数和列的数据类型不必相同。
-
过程返回后,调用应用程序可以使用
OUT
和 参数 的最终值。INOUT
这些参数作为额外的单行结果集返回,该结果集位于过程本身生成的任何结果集之后。该行包含OUT
和INOUT
参数的值,按照它们在过程参数列表中声明的顺序排列。有关未处理条件对过程参数的影响的信息,请参阅 条件处理和 OUT 或 INOUT 参数。
下面的讨论展示了如何通过 C API 将这些功能用于准备好的语句。要
CALL
通过
PREPARE
and
EXECUTE
语句使用准备好的语句,请参阅
CALL 语句。
执行准备好的
CALL
语句的应用程序应该使用一个循环来获取结果,然后调用
mysql_stmt_next_result()
以确定是否有更多结果。结果由存储过程生成的任何结果集和后跟指示过程是否成功终止的最终状态值组成。
如果过程有OUT
或
INOUT
参数,则最终状态值之前的结果集包含它们的值。要确定结果集是否包含参数值,请测试是否
在连接处理程序
的成员中
SERVER_PS_OUT_PARAMS
设置了该位
:server_status
MYSQL
mysql->server_status & SERVER_PS_OUT_PARAMS
下面的示例使用准备好的
CALL
语句来执行一个存储过程,该过程生成多个结果集并通过
OUT
和INOUT
参数将参数值返回给调用者。该过程采用所有三种类型的参数 ( IN
, OUT
,
INOUT
),显示它们的初始值,分配新值,显示更新后的值,然后返回。因此,该过程的预期返回信息由多个结果集和一个最终状态组成:
执行该过程的代码:
MYSQL_STMT *stmt;
MYSQL_BIND ps_params[3]; /* input parameter buffers */
int int_data[3]; /* input/output values */
bool is_null[3]; /* output value nullability */
int status;
/* set up stored procedure */
status = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
test_error(mysql, status);
status = mysql_query(mysql,
"CREATE PROCEDURE p1("
" IN p_in INT, "
" OUT p_out INT, "
" INOUT p_inout INT) "
"BEGIN "
" SELECT p_in, p_out, p_inout; "
" SET p_in = 100, p_out = 200, p_inout = 300; "
" SELECT p_in, p_out, p_inout; "
"END");
test_error(mysql, status);
/* initialize and prepare CALL statement with parameter placeholders */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
printf("Could not initialize statement\n");
exit(1);
}
status = mysql_stmt_prepare(stmt, "CALL p1(?, ?, ?)", 16);
test_stmt_error(stmt, status);
/* initialize parameters: p_in, p_out, p_inout (all INT) */
memset(ps_params, 0, sizeof (ps_params));
ps_params[0].buffer_type = MYSQL_TYPE_LONG;
ps_params[0].buffer = (char *) &int_data[0];
ps_params[0].length = 0;
ps_params[0].is_null = 0;
ps_params[1].buffer_type = MYSQL_TYPE_LONG;
ps_params[1].buffer = (char *) &int_data[1];
ps_params[1].length = 0;
ps_params[1].is_null = 0;
ps_params[2].buffer_type = MYSQL_TYPE_LONG;
ps_params[2].buffer = (char *) &int_data[2];
ps_params[2].length = 0;
ps_params[2].is_null = 0;
/* bind parameters */
status = mysql_stmt_bind_param(stmt, ps_params);
test_stmt_error(stmt, status);
/* assign values to parameters and execute statement */
int_data[0]= 10; /* p_in */
int_data[1]= 20; /* p_out */
int_data[2]= 30; /* p_inout */
status = mysql_stmt_execute(stmt);
test_stmt_error(stmt, status);
/* process results until there are no more */
do {
int i;
int num_fields; /* number of columns in result */
MYSQL_FIELD *fields; /* for result set metadata */
MYSQL_BIND *rs_bind; /* for output buffers */
/* the column count is > 0 if there is a result set */
/* 0 if the result is only the final status packet */
num_fields = mysql_stmt_field_count(stmt);
if (num_fields > 0)
{
/* there is a result set to fetch */
printf("Number of columns in result: %d\n", (int) num_fields);
/* what kind of result set is this? */
printf("Data: ");
if(mysql->server_status & SERVER_PS_OUT_PARAMS)
printf("this result set contains OUT/INOUT parameters\n");
else
printf("this result set is produced by the procedure\n");
MYSQL_RES *rs_metadata = mysql_stmt_result_metadata(stmt);
test_stmt_error(stmt, rs_metadata == NULL);
fields = mysql_fetch_fields(rs_metadata);
rs_bind = (MYSQL_BIND *) malloc(sizeof (MYSQL_BIND) * num_fields);
if (!rs_bind)
{
printf("Cannot allocate output buffers\n");
exit(1);
}
memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_fields);
/* set up and bind result set output buffers */
for (i = 0; i < num_fields; ++i)
{
rs_bind[i].buffer_type = fields[i].type;
rs_bind[i].is_null = &is_null[i];
switch (fields[i].type)
{
case MYSQL_TYPE_LONG:
rs_bind[i].buffer = (char *) &(int_data[i]);
rs_bind[i].buffer_length = sizeof (int_data);
break;
default:
fprintf(stderr, "ERROR: unexpected type: %d.\n", fields[i].type);
exit(1);
}
}
status = mysql_stmt_bind_result(stmt, rs_bind);
test_stmt_error(stmt, status);
/* fetch and display result set rows */
while (1)
{
status = mysql_stmt_fetch(stmt);
if (status == 1 || status == MYSQL_NO_DATA)
break;
for (i = 0; i < num_fields; ++i)
{
switch (rs_bind[i].buffer_type)
{
case MYSQL_TYPE_LONG:
if (*rs_bind[i].is_null)
printf(" val[%d] = NULL;", i);
else
printf(" val[%d] = %ld;",
i, (long) *((int *) rs_bind[i].buffer));
break;
default:
printf(" unexpected type (%d)\n",
rs_bind[i].buffer_type);
}
}
printf("\n");
}
mysql_free_result(rs_metadata); /* free metadata */
free(rs_bind); /* free output buffers */
}
else
{
/* no columns = final status packet */
printf("End of procedure output\n");
}
/* more results? -1 = no, >0 = error, 0 = yes (keep looking) */
status = mysql_stmt_next_result(stmt);
if (status > 0)
test_stmt_error(stmt, status);
} while (status == 0);
mysql_stmt_close(stmt);
该过程的执行应产生以下输出:
Number of columns in result: 3
Data: this result set is produced by the procedure
val[0] = 10; val[1] = NULL; val[2] = 30;
Number of columns in result: 3
Data: this result set is produced by the procedure
val[0] = 100; val[1] = 200; val[2] = 300;
Number of columns in result: 2
Data: this result set contains OUT/INOUT parameters
val[0] = 200; val[1] = 300;
End of procedure output
该代码使用两个实用例程
test_error()
和
test_stmt_error()
来检查错误并在发生错误时打印诊断信息后终止:
static void test_error(MYSQL *mysql, int status)
{
if (status)
{
fprintf(stderr, "Error: %s (errno: %d)\n",
mysql_error(mysql), mysql_errno(mysql));
exit(1);
}
}
static void test_stmt_error(MYSQL_STMT *stmt, int status)
{
if (status)
{
fprintf(stderr, "Error: %s (errno: %d)\n",
mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
exit(1);
}
}