3.6.4准备的CALL语句支持

本节描述C API中对使用的存储过程的准备语句支持调用声明:

使用prepared执行的存储过程调用语句可以以以下方式使用:

  • 存储过程可以产生任意数量的结果集。对于所有结果集,列的数量和列的数据类型不需要相同。

  • 的最终值而且INOUT在过程返回后,调用应用程序可以使用参数。这些参数作为附加的单行结果集返回,后跟过程本身产生的任何结果集。属性的值而且INOUT参数在过程参数列表中声明的顺序。

    有关未处理条件对过程参数的影响的信息,请参见条件处理和OUT或INOUT参数

下面的讨论将展示如何通过C API为准备好的语句使用这些功能。使用准备调用语句通过准备而且执行语句,看到调用语句

执行预处理的调用语句应该使用一个循环来获取结果,然后调用mysql_stmt_next_result ()以确定是否有更多的结果。结果由存储过程产生的任何结果集组成,后面跟着指示过程是否成功终止的最终状态值。

如果程序有INOUT参数,则在最终状态值之前的结果集包含它们的值。要确定结果集是否包含参数值,请测试SERVER_PS_OUT_PARAMS位设置在server_status的成员MYSQL连接处理程序:

mysql - > server_status & SERVER_PS_OUT_PARAMS

下面的示例使用了一个prepared调用语句来执行存储过程,该存储过程生成多个结果集,并通过而且INOUT参数。该过程接受所有三种类型的参数(INOUT),显示它们的初始值,分配新值,显示更新后的值,并返回。因此,过程的预期返回信息由多个结果集和最终状态组成:

  • 的一个结果集选择显示初始参数值:1030..(参数被调用者赋值,但这个赋值预期是无效的:参数被视为直到在过程中赋值。)

  • 的一个结果集选择显示修改后的参数值:One hundred.200300

  • 一个包含final的结果集而且INOUT参数值:200300

  • 最后的状态包。

执行过程的代码:

MYSQL_STMT *支撑;MYSQL_BIND ps_params [3];/*输入参数buffers */ int int_data[3];/*输入/输出值*/ my_bool is_null[3];/*输出值nullability */ int状态;/*设置存储过程*/ status = mysql_query(mysql, "DROP procedure IF EXISTS p1");test_error (mysql、状态);status = mysql_query(mysql, "CREATE PROCEDURE p1(" IN p_in INT, " OUT p_out INT, " " INOUT p_inout INT ")" 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);

执行该过程应产生以下输出:

数据:该结果集由过程val[0] = 10产生;val[1] =零;val [2] = 30;数据:该结果集由过程val[0] = 100产生;val [1] = 200;val [2] = 300;数据:这个结果集包含OUT/INOUT参数val[0] = 200;val [1] = 300;过程输出结束

该代码使用了两个实用程序例程,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));退出(1);}}静态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));退出(1);}}