The instance parses the MySQL stored procedure and the calling method of the stored procedure, and the mysql Stored Procedure

The instance parses the MySQL stored procedure and the calling method of the stored procedure, and the mysql Stored Procedure

Mysql added the stored procedure function after 5.1. The stored procedure runs inside mysql and the statements have been compiled, which is faster than SQL. The Stored Procedure and mysql are equivalent to shell and linux systems. If you are a programmer, I will tell you that the stored procedure is actually a method. You only need to call this method and input the set parameters to obtain or execute the operation you want. after reading the following stored procedure instances, you will find that the mysql stored procedure is similar to the shell.
The following stored procedure is used to call the stored procedure and input the username and password parameters. The stored procedure stores them in the process_test table. Check the instance.

1. Create a database

mysql>create database db_proc;

2. Create a table

Mysql> create table 'proc _ test' ('id' tinyint (4) not null AUTO_INCREMENT, # id, automatically increasing 'username' varchar (20) not null, # username 'Password' varchar (20) not null, # password primary key ('id') # primary key) ENGINE = MyISAM AUTO_INCREMENT = 50 default charset = utf8; # Set the table engine and Character Set

3. Create a stored procedure

Create procedure mytest (in name varchar (20), in pwd varchar (20) # define the input parameter begin insert into proc_test (username, password) values (name, pwd ); # Insert the passed parameter name and pwd into the table. Do not forget the semicolon end; # note that this semicolon does not forget create procedure mytest (in name varchar (20 ), in pwd varchar (20) # define the passed parameter begin insert into proc_test (username, password) values (name, pwd); # insert the passed parameter name and pwd into the table, don't forget the semicolon end; # Be sure to forget this semicolon.

Iv. Test the Stored Procedure
Usage: name of the call Stored Procedure (input parameter)
Call proc_test ("Never mind", "www.jb51.net ")
Username is "easy to understand" Incoming database, password "www.jb51.net"

5. check whether any data is added to the database.

Select * from proc_test where username = 'Your heart is cool-white '; # if there is any content, it indicates the operation is successful.

6. delete a stored procedure

Drop procdure stored procedure name;

VII. General paging Stored Procedure Code and call

Drop procedure if exists pr_pager; create procedure pr_pager (IN p_table_name VARCHAR (1024),/* Table name */IN p_fields VARCHAR (1024),/* query field */IN p_page_size INT, /* Number of records per page */IN p_page_now INT,/* Current page */IN p_order_string VARCHAR (128),/* sorting condition (contains the ORDER keyword, which can be blank) */IN p_where_string VARCHAR (1024),/* WHERE condition (containing the WHERE keyword, can be empty) */OUT p_out_rows INT/* Total Number of output records */) not deterministic SQL SECURITY DEFINER COMMENT 'paging Stored Procedure 'BEGIN/* defined variable */DECLARE m_begin_row INT DEFAULT 0; DECLARE m_limit_string CHAR (64 ); /* construct statement */SET m_begin_row = (p_page_now-1) * p_page_size; SET m_limit_string = CONCAT ('limit', m_begin_row, ',', p_page_size ); SET @ COUNT_STRING = CONCAT ('select COUNT (*) INTO @ ROWS_TOTAL FROM ', p_table_name, '', p_where_string); SET @ MAIN_STRING = CONCAT ('select', p_fields, 'from', p_table_name, '', p_where_string,'', p_order_string, m_limit_string);/* preprocessing */PREPARE count_stmt FROM @ COUNT_STRING; EXECUTE count_stmt; deallocate prepare count_stmt; SET p_out_rows = @ ROWS_TOTAL; PREPARE main_stmt FROM @ MAIN_STRING; EXECUTE main_stmt; deallocate prepare main_stmt; END;

1. Call to retrieve records:

Call pr_pager ('table name', '*', 25, 1, '','', @ count_rows); call pr_pager ('user', '*', 15, 2, '', 'where id> 3', @ count_rows); call pr_pager ('user', '*', 15, 1, 'Group by password order by id desc ', '', @ count_rows );

2. Call 1 and get the number of items again:

select @count_rows;  select @MAIN_STRING //select sql select @COUNT_STRING //seelct count sql 

Supports multi-Table cascade and grouping:

Copy codeThe Code is as follows:

Call pr_pager ('job j left join enter_job ej on j. job_no = ej. job_no', 'J. *, J. * ', '25', '1', 'Group by J. put_away_user order by J. put_away_user desc ', 'where j. job_table = "enter" ', @ p_out_rows );

<?php function dump_single_form41report($sys_report_id) {   $this->dbConn->setFetchMode(DB_FETCHMODE_ASSOC);   //SET @a=1;CALL dbpi_report.simpleproc(@a);SELECT @a;   $sql = "CALL dbpi_temp.dumpSingleReportForm41($sys_report_id);";   $result = $this->dbConn->query($sql);   if (mysql_error()) {     die (mysql_error().'<b>:</b> dump_single_form41report(...)['.__LINE__.'];<br>'.$sql);   }   return $result; }  function initQueuePool($sys_report_id, $username){   $this->dbConn->setFetchMode(DB_FETCHMODE_ASSOC);   $this->checkPreviousThread($sys_report_id, $username);    $temptablename = "_".$username."_".$sys_report_id;   $sql = "SET @a=".$sys_report_id.";";   $this->dbConn->query($sql);   $sql = "SET @b='".DB_REPORT.".".$temptablename."';";   $this->dbConn->query($sql);   $sql = "SET @c='".DB_PREPRODUCT."';";   $this->dbConn->query($sql);   $sql = "CALL ".DB_REPORT.".fm41_simpleproc(@a,@b,@c);";   $this->dbConn->query($sql); }  

　
A normal query returns only one result set, but a stored procedure returns at least two result sets, one of which is the execution status of the stored procedure. We must clear the execution status before calling another stored procedure again.

<?php $rs=mysql_query("call pr_pager('change_monitor','*',10,1,'','',@p_out_rows)"); while($rows=mysql_fetch_array($rs)){   echo $rows[Schedule]; } $query=mysql_query("select @p_out_rows"); $v=mysql_fetch_array($query);  can't return a result set in the given context

I need php to call the stored procedure and return a result set. I found it very difficult. After searching for a long time, I finally found a solution on a foreign Forum. Here I will localize it.
There are two key points:
1.

define('CLIENT_MULTI_RESULTS', 131072);

2.

$link = mysql_connect("127.0.0.1", "root", "",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error());