Description:
/*c program start */
#include<stdlib.h>
#include<mysql.h>
#include<string.h>
#include<stdio.h>
main()
{
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
int i;
int j;
char inp[100];
mysql_init(&mysql);
mysql_real_connect(&mysql,"192.168.0.104","root","","test",NULL,NULL,0);
while(1)
{
printf("\n\nPlease Enter The SQL:");
gets(&inp);
strcpy(inp,"call look_the_main('000060',null)");
if(mysql_query(&mysql,&inp))
{
printf("Error!!\n");
return;
}
res=mysql_store_result(&mysql);
j=mysql_num_fields(res);
while(row=mysql_fetch_row(res))
{
for(i=0;i<j;i++)
printf("%s\t",row[i]);
printf("\n");
}
mysql_free_result(res);
}
mysql_close(&mysql);
}
/*end the c program*/
/*create the table/*
drop table day_list;
create table day_list ( day_id int primary key auto_increment , day_code char(6),day_day date,day_time time, day_price double(7,3),day_quantity int,day_flag char(1));
/*end */
/*prodedure def*/
delimiter //
drop procedure look_the_main;//
create procedure look_the_main(num char(6),quan int)
begin
declare qu int;
if quan is null
then
select 10000/(sum(day_price)/count(day_price)) into quan from day_list where day_code=num;
end if ;
select day_code,day_day,sum(day_price)/count(day_price) as ad_price ,sum(day_quantity) as quantity ,count(*) as count ,quan ,day_flag from day_list where day_flag='B' and day_quantity >= quan and day_code=num group by day_day;
select day_code,day_day,sum(day_price)/count(day_price) as ad_price ,sum(day_quantity) as quantity ,count(*) as count ,quan ,day_flag from day_list where day_flag='S' and day_quantity >= quan and day_code=num group by day_day;
end ;
//
/* end def*/
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.03,273,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.16,433,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.16,3,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.25,10,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.25,2,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.4,1,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,4,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,26,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,16,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,45,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.57,14,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.5,185,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.52,21,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.56,18,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.58,52,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.5,113,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.57,41,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.56,44,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.52,60,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.56,24,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.55,23,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.56,20,'B');
How to repeat:
!1
Suggested fix:
!!