Of Stack

The instance resolves the stored procedures in MySQL and the methods that the stored procedures call

  • 2021-01-06 00:46:11
  • OfStack

mysql added the functionality of stored procedures after 5.1. Stored procedures run inside mysql, and statements are already compiled, which is faster than sql. Stored procedures and mysql correspond to shell and linux systems. If you're a programmer, a stored procedure is actually a method. You just call the method and enter its parameters to get or perform any action you want. Looking at the following stored procedure examples, you can see that mysql stored procedures are similar to shell.
The contents of the following stored procedure are: call the stored procedure, and pass in the user name, password parameters. The stored procedure stores them in the process_test table. 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, Automatic growth 
 `username` varchar(20) NOT NULL, # The user name 
 `password` varchar(20) NOT NULL, # password 
 PRIMARY KEY (`id`) # A 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 parameters passed in 
 begin
 insert into proc_test(username,password) values(name,pwd);
# Passing in the parameters name and pwd Insert the table, don't forget the semicolon 
 end; # Notice the semicolon and don't forget it 

create procedure mytest(in name varchar(20),in pwd varchar(20))# Define the parameters passed in 
 begin
 insert into proc_test(username,password) values(name,pwd);
# Passing in the parameters name and pwd Insert the table, don't forget the semicolon 
 end; # Notice the semicolon and don't forget it

4. Test calls stored procedures
Usage: call stored procedure name (passed parameter)
call proc_test(" The heart is cool ","www.ofstack.com")
username for "absolutely heart is cold white open" pass the password "www.ofstack.com"

5. Check whether there is any added data in the database 


select * from proc_test where username= 'The heart is cool white open ';# If there's content, it says it's successful

6. Delete stored procedures 


drop procdure  Stored procedure name ;

7. Generic paging stored procedure code and call 


DROP PROCEDURE IF EXISTS pr_pager; 
CREATE PROCEDURE pr_pager( 
  IN   p_table_name    VARCHAR(1024),    /* The name of the table */ 
  IN   p_fields      VARCHAR(1024),    /* Query field */ 
  IN   p_page_size     INT,         /* Number of records per page */ 
  IN   p_page_now     INT,         /* The current page */ 
  IN   p_order_string   VARCHAR(128),     /* Sorting conditions ( contains ORDER The keyword , Can be null )*/  
  IN   p_where_string   VARCHAR(1024),    /*WHERE conditions ( contains WHERE The keyword , Can be null )*/   
  OUT  p_out_rows     INT          /* Total output records */   
) 
NOT DETERMINISTIC 
SQL SECURITY DEFINER 
COMMENT ' Paging stored procedures ' 
   
BEGIN 
 
  /* Define variables */ 
  DECLARE m_begin_row INT DEFAULT 0; 
  DECLARE m_limit_string CHAR(64); 
 
  /* Construct sentences */   
  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); 
 
  /* 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. Record fetch call: 


call pr_pager(' The name of the table ', '*', 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. After calling 1, call the number of items: 


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

Support multi-table cascading, grouping: 


call pr_pager('job j left join enter_job ej on j.job_no=ej.job_no','j.*,ej.*','25','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->dbConn->query($sql); 
}


A normal query returns only one result set, whereas a stored procedure returns at least two result sets, one of which is the execution state of the stored procedure. We must clear the 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

We need php to call the stored procedure and return 1 result set. We need php to call the stored procedure and return 1 result set. We need php to call the stored procedure.
There are two key points:
1. 


mysql>CREATE TABLE `proc_test` (
 `id` tinyint(4) NOT NULL AUTO_INCREMENT, #ID, Automatic growth 
 `username` varchar(20) NOT NULL, # The user name 
 `password` varchar(20) NOT NULL, # password 
 PRIMARY KEY (`id`) # A primary key 
 ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8; # Set the table engine and character set
0

2. 


mysql>CREATE TABLE `proc_test` (
 `id` tinyint(4) NOT NULL AUTO_INCREMENT, #ID, Automatic growth 
 `username` varchar(20) NOT NULL, # The user name 
 `password` varchar(20) NOT NULL, # password 
 PRIMARY KEY (`id`) # A primary key 
 ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8; # Set the table engine and character set
1

Related articles: