您现在的位置是：主页 > news > 海阳有没有做企业网站的/哪个行业最需要推广
海阳有没有做企业网站的/哪个行业最需要推广
admin2025/5/1 6:31:49【news】
简介海阳有没有做企业网站的,哪个行业最需要推广,模板做的网站如何下载地址,计算机毕业论文5000字文章目录问题数据库表描述发生问题的存储过程写法Navicat调用结果SQL语句直接调用预编译表达式调用修改后的存储过程问题由于在项目中需要在多个同样格式表中，查询同一条件结果。为此编写了一个存储过程，通过游标的方式获取指定命名格式的表名。然后编写…
文章目录
问题
数据库表描述
发生问题的存储过程写法
Navicat调用结果
SQL语句直接调用
预编译表达式调用
修改后的存储过程
问题
由于在项目中需要在多个同样格式表中，查询同一条件结果。为此编写了一个存储过程，通过游标的方式获取指定命名格式的表名。然后编写动态SQL语句分别查询每个表，再使用UNION ALL合并结果一次性返回。通过Navicat工具调用存储过程可以正常获得结果，在C API直接通过SQL语句也可以直接查询，但通过预编译表达式的方法调用，则提示MYSQL_NO_DATA没有对应的结果。
MySQL版本 5.6.37 (x64)
数据库表描述
在数据库test中，有两张表分别为personlist1、personlist2，表的内容分别为：
发生问题的存储过程写法
CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryAllZhang`()
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE tablename varchar(255);
DECLARE SqlSelectResult varchar(1024) DEFAULT("");
DECLARE cur CURSOR FOR select table_name from information_schema.tables where table_name like "personlist%";
DECLARE CONTINUE HANDLER FOR not found SET stopflag = 1 ;
OPEN cur;
FETCH cur into tablename;
if stopflag = 0 then
SET SqlSelectResult = CONCAT("select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end if;
while stopflag = 0 Do
SET SqlSelectResult = CONCAT(SqlSelectResult," UNION ALL select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end WHILE;
close cur;
SET SqlSelectResult = CONCAT("",SqlSelectResult,";");
set @sql = SqlSelectResult;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
END
Navicat调用结果
SQL语句直接调用
如果通过mysql_real_query执行SQL语句，调用存储过程，可以正常获得结果，关键代码如下所示：
string query = "CALL QueryAllZhang();";
int ret= mysql_real_query(connection,query.c_str(),query.length());
if (ret)
{
printf("Error exec query: %s\n",mysql_error(connection));
}
else
{
printf("[%s] exec...\n", query.c_str());
}
result = mysql_store_result(connection);
while((row = mysql_fetch_row(result))) {
printf("[%s]\n", row[0]);
}
mysql_free_result(result);
对应的输出结果如下：
[CALL QueryAllZhang();] exec…
[zhang1]
[zhang3]
[zhang2]
[zhang4]
然而这种直接执行SQL语句的方法，当存储过程有参数时，直接拼接SQL语句会有注入问题，风险很大。
预编译表达式调用
参考MySQL官方例子，编写了如下的C代码：
MYSQL_STMT *stmt;
int status;
/* initialize and prepare CALL statement with parameter placeholders */
stmt = mysql_stmt_init(connection);
if (!stmt)
{
printf("Could not initialize statement\n");
return -1;
}
char *query = "call QueryAllZhang();";
status = mysql_stmt_prepare(stmt, query, strlen(query));
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
char name[256] = {0};
status = mysql_stmt_execute(stmt);
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
/* 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(connection->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");
return -2;
}
memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_fields);
rs_bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
rs_bind[0].buffer = name;
rs_bind[0].buffer_length = 255;
status = mysql_stmt_bind_result(stmt, rs_bind);
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
/* fetch and display result set rows */
while (1)
{
status = mysql_stmt_fetch(stmt);
// 一打开游标就会在这里捕获到MYSQL_NO_DATA
if (status == 1 || status == MYSQL_NO_DATA)
{
break;
}
printf("[%s]\n",(char*)rs_bind[0].buffer);
}
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);
if(status != 0)
{
return -9;
}
}
} while (status == 0);
mysql_stmt_close(stmt);
如上代码中的注释，无法获得结果，能获取到查询结果的列名，输出结果如下：
Number of columns in result: 1
Data: this result set is produced by the procedure
End of procedure output
为此，我对存储过程进行简化，修改为如下：
CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryAllZhang`()
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE tablename varchar(255);
DECLARE SqlSelectResult varchar(1024) DEFAULT("");
DECLARE cur CURSOR FOR select table_name from information_schema.tables where table_name like "personlist%";
DECLARE CONTINUE HANDLER FOR not found SET stopflag = 1 ;
set @sql = "select table_name from information_schema.tables";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
OPEN cur;
FETCH cur into tablename;
if stopflag = 0 then
SET SqlSelectResult = CONCAT("select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end if;
while stopflag = 0 Do
SET SqlSelectResult = CONCAT(SqlSelectResult," UNION ALL select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end WHILE;
close cur;
SET SqlSelectResult = CONCAT("",SqlSelectResult,";");
END
将执行查询的代码提到OPEN cur之前，可以获得数据库中的表列表，输入结果如下：
Number of columns in result: 1
Data: this result set is produced by the procedure
[CHARACTER_SETS]
[COLLATIONS]
[COLLATION_CHARACTER_SET_APPLICABILITY]
[COLUMNS]
[COLUMN_PRIVILEGES]
[ENGINES]
…
如果将查询代码挪到close cur之后，则无法获得结果。为此，我查询了有关的资料，但还没有发现原因(暂时无解)。为此，我放弃使用游标，改用通过临时表的方式编译查询结果，再动态执行SQL语句进行查询。
修改后的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryAllZhang`()
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE tablename varchar(255);
DECLARE SqlSelectResult varchar(1024) DEFAULT("");
DECLARE temp_index int;
DECLARE temp_count int;
// 若不存在临时表，则创建
CREATE TEMPORARY TABLE if not exists tmp_table(
rowid int primary key,
rowname varchar(255)
);
// 清空临时表内容
truncate tmp_table;
// 没插入一条结果，对应ID自增，方便后续控制循环获取结果
insert into tmp_table(SELECT (@i := @i + 1),table_name from information_schema.tables,(SELECT @i := 0) b where table_name like "personlist%");
set temp_index = 1;
select count(*) from tmp_table into temp_count;
if temp_count != 0
then
select rowname from tmp_table where rowid = temp_index into tablename;
SET SqlSelectResult = CONCAT("select name from ",tablename," where name like 'zhang%'");
set temp_index = temp_index + 1;
while temp_index <= temp_count do
select rowname from tmp_table where rowid = temp_index into tablename;
SET SqlSelectResult = CONCAT(SqlSelectResult," UNION ALL select name from ",tablename," where name like 'zhang%'");
set temp_index = temp_index + 1;
END while;
SET SqlSelectResult = CONCAT("",SqlSelectResult,";");
set @sql = SqlSelectResult;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
end if;
END
再通过预编译表达式方式调用存储过程，得到如下结果：
Number of columns in result: 1
Data: this result set is produced by the procedure
[zhang1]
[zhang3]
[zhang2]
[zhang4]
End of procedure output
可以正常获得结果了。。。虽然通过临时表的方式，会有写入再读出的IO问题，但暂时实现了防止SQL注入，又能在存储过程内从多个指定格式表中查询结果。另外，MySQL中的临时表，当关闭连接时，会自动删除临时表，也可以手动执行删除。
