The C language calls the mysql stored procedure and the mysql Stored Procedure
This article explains how to call the stored procedure of mysql database in C language.
The following is a SC table that stores the course selection records of students, including course number, student number, average score, volume score, and total score.
Database Table creation process:
Create table class (
Cno varchar (8) not null,
Sno varchar (8) not null,
Ordinary_score int,
Last_score int,
All_score int
);
Stored Procedure
Parameter columns enclosed by parentheses must always exist. If no parameter exists, use an empty parameter column (). Each parameter is an IN parameter by default. To specify other parameters, you can use the keyword IN (default, default) OUT or INOUT before the parameter name.
The IN parameter is passed IN only
The OUT parameter is output-only.
The INOUT parameter is both input and input, that is, bidirectional transmission.
Specifying the parameter IN, OUT, or INOUT is valid only for PROCEDURE. (FUNCTION parameters are always considered as IN parameters)
Set up the storage process and pass in the ratio of normal time to x, volume to y, ratio of normal time to pert, student ID, and course number. The creation process is as follows:
delimiter //CREATE PROCEDURE cal_grade(x INT,y INT,out t int,pert float,s VARCHAR(8),c VARCHAR(8))LABEL_PROC:BEGIN IF ( x < 0 || x > 100 ) THEN SET t = -1; LEAVE LABEL_PROC; END IF; IF ( y < 0 || y > 100 ) THEN SET t = -2; LEAVE LABEL_PROC; END IF; SET t = ROUND( x*pert + y*(1-pert) ); UPDATE sc SET ordinary_score=x,last_score=y WHERE sno=s AND cno=c AND tno=tn;END LABEL_PROC //delimiter ;
C language call
# Include <stdio. h> # include "mysql. h "int main () {MYSQL * my_connection; MYSQL_RES * res_ptr; MYSQL_ROW sqlrow; char buf [100]; my_connection = mysql_init (NULL ); // the last parameter of the following connection must be CLIENT_MULTI_STATEMENTS. Otherwise, the select error: PROCEDURE *** can't return a result set in the given context my_connection = mysql_real_connect (my_connection, "localhost", "root", "root", "test", 0, NULL, CLIENT_MULTI_STATEMENTS ); Sprintf (buf, "call cal_grade (% d, % d, @ t, % f, % s, % s)", 10, 10, 0.3, 123,456 ); if (mysql_query (my_connection, buf) sprintf (stderr, mysql_error (my_connection); else {// get the return parameter @ t, @ t is the output parameter mysql_query (my_connection, "select @ t"); res_ptr = mysql_store_result (my_connection); if (res_ptr) {sqlrow = mysql_fetch_row (res_ptr); if (! Strcmp (sqlrow [0], "-1") printf ("average score is out of range \ n"); else if (! Strcmp (sqlrow [0], "-2") printf ("the volume score is out of the range \ n"); else printf ("total score: % s \ n ", sqlrow [0]);} mysql_free_result (res_ptr);} mysql_close (my_connection); return 0 ;}
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.