1.数据库名:test;
表名:chengjibiao;
字段:Id int , Name char , Englishint , Maths int , Physis int ;
2.存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`query` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `query`( in N int, out schar(254) )
BEGIN
declare a,b,c,d,e int;
declare f char(100);
declare g decimal(4,2);
set e=1;
drop table if exists zongping;
create table zongping(NUM int,Name char(255),English int,Mathsint,Physis int,Total int,aver decimal(4,2));
repeat
select English,Maths,Physis,Name into a,b,c,f from chengjibiaowhere Id=e;
set d=a+b+c;
set g=(a+b+c)/3;
insert into zongping(NUM,Name,English,Maths,Physis,Total,aver)values( e,f,a,b,c,d,g );
set e=e+1;
until e=N
end repeat;
select *from zongping as s;
drop table zongping;
END $$
DELIMITER ;
3.主函数(在文件main.c中):
#include "stdio.h"
#include "stdlib.h"
#include "string.h"
#include "/usr/include/mysql/mysql.h"
#include "/usr/include/mysql/mysql_version.h"
#include "/usr/include/mysql/errmsg.h"
int main( int argc,char **argv[] )
{
MYSQL mysql;
MYSQL_ROW results,record;
char query[50],s[254];
/**链接数据库**/
if(!mysql_init(&mysql))
{
printf("mysql_init failed!/n");
return 0;
}
if(!mysql_real_connect(&mysql,"localhost","root","123456","test",0,NULL,CLIENT_MULTI_STATEMENTS))
{
printf("mysql_real_connect() failed!/n");
mysql_close(&mysql);
return 0;
}
/**调用存储过程**/
strcpy(query,"callquery(7,@s)");
mysql_real_query(&mysql,query,(unsigned int)strlen(query));
/**取得存储过程返回值**/
mysql_query(&mysql,"SELECT @s ");
results =mysql_store_result(&mysql);
/**输出返回值**/
printf( "Id Name Maths English Physis Total aver/n" );
while((record =mysql_fetch_row(results)))
{
printf("%1s%10s%8s%12s%11s%10s%10s/n", record[0], record[1],record[2],record[3],record[4],record[5],record[6]);
}
mysql_free_result(results);
mysql_close(&mysql);
return 0;
}
(编译:gcc -o main main.c -L/usr/lib/mysql/ -lmysqlclient -lz)
4.
成绩表:
+----+-------+-------+---------+--------+
| Id | Name| Maths | English |Physis |
+----+-------+-------+---------+--------+
|1 | liu |90|80 |86 |
|2 | zhang |88 |86 |87 |
|3 | xiao|78|88 |98 |
|4 | wang|77|87 |97 |
|5 | li|79 |89 |99 |
|6 | yi|67 |77 |87 |
运行程序得到的结果:
IdNameMathsEnglishPhysisTotalaver
1liu90808625685.33
2zhang88868726187.00
3xiao78889826488.00
4wang77879726187.00
5li79899926789.00
6yi67778723177.00