API调用存储过程并传递参数 MYSQL C++

关于MYSQL API调用存储过程大致分为四步：

第一步：创建存储过程，关键字是PROCEDURE

sql = "CREATE  PROCEDURE `p_test`(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";

第二步：定义变量并赋值；

sql="SET @A=1; SET @B=2; SET @C=3;";

第三步：调用存储过程

sql="call p_test (@A, @B, @C)";

第四步：获取存储过程的结果

sql="SELECT @A, @B, @C";

完整代码

#define _CRT_SECURE_NO_WARNINGS
#include<iostream>
#include<stdio.h>
#include<mysql.h>
#include<thread>
#include<map>
#include<sstream>
#include<fstream>
//计时
#include<chrono>
using namespace std;
using namespace chrono;


int main()
{
	const char* host = "127.0.0.1";
	const char* user = "root";
	const char* password = "******";
	const char* database = "database_test";
	int port = 3306;

	MYSQL mysql;
	mysql_init(&mysql);
	mysql_library_init(0, NULL, NULL);
	//防止乱码
	mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "GBK");

	//支持多条sql语句 CLIENT_MULTI_STATEMENTS   clientflag  默认是不支持的
	//if (!mysql_real_connect(&mysql, host, user, password, database, port, 0, 0))
	if (!mysql_real_connect(&mysql, host, user, password, database, port, NULL, CLIENT_MULTI_STATEMENTS))
	{
		cout << "failed to connect" << host << mysql_error(&mysql) << endl;
	}

	else
	{
		cout << "successfully connected " << host <<endl;
	}
	
	//
	string sql = "";
	//创建存储过程
	//IN输入
	//SELECT p_in,p_out,p_inout ;把输入参数打印出来
	//set p_in=100; p_out=200;p_inout=300;修改值
	sql = "CREATE  PROCEDURE `p_test`(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";
	int re = mysql_query(&mysql, sql.c_str());
	if (re != 0)
	{
		cout << "mysql_query failed " << mysql_error(&mysql) << endl;
	}

	//2、定义变量并复制
	cout << "IN A=1,B=2,C=3" << endl;
	sql = "SET @A=1;SET @B=2;SET @C=3;";
	re = mysql_query(&mysql, sql.c_str());
	if (re != 0)
	{
		cout << "failed mysql_error " << mysql_error(&mysql) << endl;
	}
	//0表示有结果，1表示没有结果；大于1 表示出错
	do
	{
		cout << "set affect " << mysql_affected_rows(&mysql) << endl;

	} while (mysql_next_result(&mysql)==0);

	//3、调用存储过程
	sql = "call p_test(@A,@B,@C)";
	re = mysql_query(&mysql, sql.c_str());
	if (re != 0)
	{
		cout << "failed mysql_error " << mysql_error(&mysql) << endl;
	
	}
	cout << "IN Proc:";
	do
	{
		MYSQL_RES* res = mysql_store_result(&mysql);
		if (!res)
		{
			continue;//如果没有
		}
		else
		{
			//字段数
			int fcount = mysql_num_fields(res);

			//打印结果集
			for (;;)
			{
				//提取一行记录
				MYSQL_ROW row = mysql_fetch_row(res);
				if (!row)
				{
					break;
				}
				for (int i = 0;i < fcount;i++)
				{
					if (row[i])
					{
						//判断是否为NULL
						cout << row[i] << " ";
					}
					else
					{
						//OUT变量它的传进来的参数是没有用的，inout传进去的参数是有用的
						cout <<  " NULL ";
					}
				}
				cout << endl;
			}
		}
		mysql_free_result(res);

	} while (mysql_next_result(&mysql)==0);

	//4、获取存储过程的结果
	sql = "SELECT @A,@B,@C";
	re = mysql_query(&mysql, sql.c_str());
	if (re != 0)
	{
		cout << "failed mysql_error " << mysql_error(&mysql) << endl;
	}
	MYSQL_RES* res = mysql_store_result(&mysql);
	cout << "out: ";
	MYSQL_ROW row= mysql_fetch_row(res);
	cout << " in= " << row[0];
	cout << " out= " << row[1];
	cout << " inout= " << row[2];
	cout << endl;
	//释放
	mysql_free_result(res);

	mysql_close(&mysql);
	mysql_library_end();
	system("pause");
	return 0;
}

分析结果：

输出为

我们发现，输入in是1，set设置成100，返回还是1，因为他只是输入，没有修改

输出out 首先为2，set设置为200，返回是200；但是IN Proc: B的位置是NULL，原因是B是OUT输出， OUT变量它的传进来的参数是没有用的，inout传进去的参数是有用的；

inout既是输入，又是输出，首先是3，set设置为300，返回300