C语言调用存储过程并且获得返回值

时间:2020-12-06 21:15:41

作者:刘晨晖

 

1. 数据库名:test

  表名:chengjibiao;

  字段:Id  int , Name char , English int , Maths int , Physis int ;

 

2. 存储过程:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`query` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `query`( in N int, out s char(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,Maths int,Physis int,Total int,aver decimal(4,2));

  repeat

  select English,Maths,Physis,Name into a,b,c,f from chengjibiao where 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,"call query(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 |

 

 

 

 

 

运行程序得到的结果:

Id    Name   Maths   English   Physis  Total      aver

  1    liu     90          80     86     256     85.33

  2  zhang     88          86     87     261     87.00

  3   xiao     78          88     98     264     88.00

  4   wang     77          87     97     261     87.00

  5     li     79          89     99     267     89.00

  6     yi     67          77     87     231     77.00