Example to parse stored procedures in Mysql and call methods for stored procedures _mysql

MySQL added the function of the stored procedure after 5.1, the stored procedure runs inside MySQL, the statements have been compiled, faster than SQL. Stored procedures and MySQL are equivalent to Shell and Linux systems. If you're a programmer, I'll tell you that the stored procedure is actually a method, you just call the method and enter the parameters that it sets to get or perform the action you want. Looking at the following stored procedure examples, you will find that the MySQL stored procedure is similar to the shell.
The following stored procedure content is: Invoke stored procedure, and incoming username, password parameter. The stored procedure stores them in the Process_test table. See examples

One, create a database

Mysql>create database Db_proc;

Second, create a table

Mysql>create TABLE ' proc_test ' (
 ' id ' tinyint (4) Not NULL auto_increment, #ID, automatically grow
 ' username ' varchar ' NULL, #用户名
 ' password ' varchar NOT NULL, #密码
 PRIMARY KEY (' id ') #主键
 ) Engine=myisam auto_increment=50 DEFA ULT Charset=utf8; #设置表引擎和字符集

Third, create a stored procedure

CREATE PROCEDURE mytest (in name varchar (m), in pwd varchar) #定义传入的参数
 begin
 inserts into Proc_test (username, Password) values (NAME,PWD);
#把传进来的参数name和pwd插入表中, don't forget semicolon end
 #注意这个分号别忘记了

CREATE PROCEDURE mytest (in name varchar (), in pwd varchar (20)) # Define incoming parameters
 begin
 INSERT INTO proc_test (Username,password) values (NAME,PWD);
#把传进来的参数name和pwd插入表中, don't forget the semicolon end
 ; #注意这个分号别忘记了

Iv. Test Call stored Procedures
usage: Call stored procedure name (parameters passed in)
Call Proc_test ("Heart is Lang Baikai", "www.jb51.net")
Username is "Lang Baikai" incoming database, password "www.jb51.net"

V. See if there are any data added in the database

SELECT * from proc_test where username= ' absolute heart is Lang Baikai '; #如果有内容说明成功了

Vi. Deleting stored procedures

Drop procdure stored procedure name;

Vii. Common paging stored procedure Code and calls

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,///per page record number/in P_page_now int,/* Current page */in p_order_string VAR  CHAR (128),/* Sort condition (contains order keyword, nullable)/in p_where_string VARCHAR (1024),/*where condition (including where keyword, nullable) * * P_out_rows INT/* Output RECORD total * * * Deterministic SQL security definer COMMENT ' paging stored procedure ' BEGIN/* Set 
  The semantic variable * * DECLARE m_begin_row INT DEFAULT 0; 
 
  DECLARE m_limit_string CHAR (64); 
  /* Construction 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_s 
 
  Tring); /* pretreatment/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. Fetch record Call:

Call Pr_pager (' table name ', ' * ', 1, ', ', ', @count_rows); 
Call Pr_pager (' user ', ' * ', 2, ', ' where id>3 ', @count_rows); 
Call Pr_pager (' user ', ' * ', # 1, ' Group by password order by id desc ', ', @count_rows); 

2. Call 1 and then fetch the number of bar calls:

Select @count_rows;  
Select @MAIN_STRING//select SQL 
Select @COUNT_STRING//seelct COUNT sql 

Supports multiple table cascading, grouping:

Copy Code code as follows:

Call Pr_pager (' job J-left join Enter_job EJ on J.job_no=ej.job_no ', ' j.*,ej.* ', ', ', ' 1 ', ' Group by Ej.put_away_user ORDER by Ej.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-&GT;dbconn->query ($sql);  
 }

　
A normal query that returns only one result set, while a stored procedure returns at least two result sets, one of which is the execution state of the stored procedure. We have to clear this execution state before we can call 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

Need PHP to call the stored procedure, return a result set, found very difficult, looking for a long time, finally in the Foreigner's Forum to find solutions, localized here.
The key is two 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 ());