Article from: http://blog.csdn.net/jccz_zys/archive/2007/07/08/1682810.aspx
Mysql5.0 and later support stored procedures. At present, MySQL's Alpha alpha version has also been released. 6.0 not only supports the vast majority of Functions of large databases such as Oracle, such as stored procedures, views, triggers, and jobs, but also fixes bugs in these functions, 6.0.1 also supports 64-bit windows and tablespace.
Access MySql in C/C ++. Common Operations are simple services. Use C to embed simple query, insert, update, and other operations. As the business becomes more complicated, it takes a lot of SQL operations to complete a business function. embedding these statements into the C code will make the C code more complicated and unclear. At this time, naturally, I thought of a stored procedure to encapsulate all the database logic. Through C, I can simply call the MySQL stored procedure interface, which greatly reduces the workload of C programmers, it also facilitates the separation of front-end business processing logic from database processing logic. The following describes how to call a stored procedure in C.
1. Create a table firstCreate Table student (ID int auto_increment, name varchar (20), age tinyint, remark varchar (50), primary key (ID ));
2. Insert several pieces of informationInsert into student values (1, "zhouys", 90, ""); Commit;
3. View user informationMysql> select * from student; + ------ + ----------- + ------ + ---------- + | ID | Name | age | remark | + ------ + ----------- + ------ + ---------- + | 1 | zhouys | 90 | + ------ + ----------- + ------ + ----------- + 1 row in SET (0.00 Sec) mysql>
4. Create a stored procedureDelimiter // create procedure querystudent (in in_id int, #0-character Id 1-digit ID # out out_ret int, # Return result out out_name varchar (20 ), # name out out_age int # age) label_a: Begin declare v_name varchar (20); declare v_age tinyint; # parameter judgment if (in_id <= 0) then set out_ret =-1; # ID error leave label_a; end if; Select name, age into v_name, v_age from student where id = in_id limit 1; if v_age is null then set out_ret =-2; # Don't found leave label_a; end if; Set out_ret = 0; Set out_name = v_name; Set out_age = v_age; end; // delimiter;
5. Calling stored procedures in C LanguageCall method or step:
5.1 initialize the MySQL handleIf (! Mysql_init (& MySQL) {printf ("mysql_init failed! /N "); Return 0 ;}
5.2 connect to MySQL// Login or connect if (! Mysql_real_connect (& MySQL, "localhost", "root", "", "billingdb", 0, null, client_multi_statements) {printf ("mysql_real_connect () failed! /N "); mysql_close (& MySQL); Return 0 ;}
5.3 call the Stored Procedure// Call strcpy (query, "Call querystudent (1, @ ret, @ out_name, @ out_age)"); printf ("query SQL = [% s]/n ", query); ret = mysql_real_query (& MySQL, query, (unsigned INT) strlen (query ));
5.4 query and save the result setMysql_query (& MySQL, "select @ ret, @ out_name, @ out_age"); // get result if (RET) {printf ("error exec query: % s/n ", mysql_error (& MySQL);} else {printf ("[% s] exec... /n ", query);} Results = mysql_store_result (& MySQL );
5.5 obtain query resultsWhile (record = mysql_fetch_row (results) {printf ("[% s]-[% s]-[% s]/n", record [0], record [1], record [2]);} a stored procedure returns only one row, ^ _ ^.
5.6 release the resource and MySQL connection handleMysql_free_result (results); mysql_close (& MySQL );
6. ConclusionThe MySQL stored procedure can implement quite powerful functions. Here we will only introduce some practices and hope to help you. You are also welcome to discuss...