Detailed explanation of mysql stored procedures and examples of calling MYSQL stored procedures from PHP

This article mainly introduces the detailed explanation of mysql stored procedures and examples of PHP calling MYSQL stored procedures. It has certain reference value. Now I share it with you. Friends in need can refer to it

Mysql stored procedure detailed explanation

##1.                         Introduction to stored procedures

Our commonly used operating database languageSQLstatements need to be executed when executing Compile first, then execute, and the stored procedure (Stored Procedure) is a set of SQL statements to complete a specific function. After compilation, it is stored in the database. The user calls and executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

#A stored procedure is a programmable function that is created and saved in the database. It can be composed of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same function on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as a simulation of the object-oriented approach in programming. It allows control over how data is accessed.

Stored procedures usually have the following advantages:

(1). Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements, are highly flexible, and can complete complex judgments and more complex operations.

(2). Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statements of the stored procedure. And database professionals can modify stored procedures at any time without affecting the application source code.

(3). Stored procedures can achieve faster execution speed. If an operation contains a large amount of Transaction-SQL code or is executed multiple times, the stored procedure will execute much faster than batch processing. Because stored procedures are precompiled. When a stored procedure is run for the first time, the query is analyzed and optimized by the optimizer and an execution plan is finally stored in the system table. The batch Transaction-SQL statement must be compiled and optimized every time it is run, and the speed is relatively slower.

(4). Stored procedures can reduce network traffic. For operations on the same database object (such as query, modification), if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when called on the client computer When this stored procedure is executed, only the call statement is transmitted over the network, thereby greatly increasing network traffic and reducing network load.

(5). Stored procedures can be fully utilized as a security mechanism. By restricting the permissions for executing a certain stored procedure, the system administrator can limit the access permissions of the corresponding data, avoid unauthorized users from accessing the data, and ensure the security of the data.

##2. AboutMySQL Stored procedures

Stored procedures are an important function of database storage, but MySQL did not support stored procedures before5.0, which makesMySQL Great discount on application. Fortunately, MySQL 5.0 has finally begun to support stored procedures, which can greatly improve the processing speed of the database and also improve the flexibility of database programming.

3. MySQLCreation of stored procedures

(1). Format

MySQL Format of stored procedure creation: CREATE PROCEDURE Process name ([Process parameters [,...]])
[Characteristic ...] Process Body

Here is an example:

1. mysql> DELIMITER //  
   mysql> CREATE PROCEDURE proc1(OUT s int)  
       -> BEGIN
       -> SELECT COUNT(*) INTO s FROM user;  
       -> END
       -> //  
   mysql> DELIMITER ;

Note:

#(1 ) What needs to be noted here is the two sentences DELIMITER // and DELIMITER;, DELIMITER means the delimiter, because MySQL defaults to ";" as the delimiter, if we do not declare separator, then the compiler will treat the stored procedure as a SQL statement, and the compilation process of the stored procedure will report an error, so DELIMITER## must be used in advance. The # keyword declares the current segment delimiter, so that MySQL will treat ";" as the code in the stored procedure , these codes will not be executed, and the separator must be restored after use.

(2) The stored procedure may have input, output, input and output parameters as needed. Here is an output parameter s, the type is int. If there are multiple parameters, use "," to separate them.

(3) The beginning and end of the process body use BEGIN is identified with END.

In this way, one of our MySQL stored procedures is completed, isn’t it very easy? ?It doesn’t matter if you don’t understand it. Next, we will explain it in detail.

(2). Declaration separator

In fact, regarding the declaration separator, the above annotation has been written very clearly. No need to say more, but one thing to note is: if you use MySQL When using the Administrator management tool, you can create it directly and no longer need to declare it.

(3). Parameters

##MySQLThe parameters of stored procedures are used in the definition of stored procedures. There are three parameter types,IN,OUT,INOUT,Form such as:

##CREATE PROCEDURE([[IN |OUT |INOUT] Parameter name Data type ...])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

Ⅰ. IN参数例子

创建:

1. mysql > DELIMITER //  
   mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
   -> BEGIN
   -> SELECT p_in;   
   -> SET p_in=2;   
   -> SELECT p_in;   
   -> END;   
   -> //  
   mysql > DELIMITER ;

执行结果:

1. mysql > SET @p_in=1;  
   mysql > CALL demo_in_parameter(@p_in);  
   +------+ 
   | p_in |  
   +------+ 
   |   1  |   
   +------+ 
   +------+ 
   | p_in |  
   +------+ 
   |   2  |   
   +------+ 
   mysql> SELECT @p_in;  
   +-------+ 
   | @p_in |  
   +-------+ 
   |  1    |  
   +-------+

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

Ⅱ.OUT参数例子

创建:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
-> BEGIN
-> SELECT p_out;  
-> SET p_out=2;  
-> SELECT p_out;  
-> END;  
-> //  
mysql > DELIMITER ;

执行结果:

mysql > SET @p_out=1;  
mysql > CALL sp_demo_out_parameter(@p_out);  
+-------+ 
| p_out |   
+-------+ 
| NULL  |   
+-------+ 
+-------+ 
| p_out |  
+-------+ 
|   2   |   
+-------+ 
mysql> SELECT @p_out;  
+-------+ 
| p_out |  
+-------+ 
|   2   |  
+-------+

Ⅲ. INOUT参数例子

创建:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)  
-> BEGIN
-> SELECT p_inout;  
-> SET p_inout=2;  
-> SELECT p_inout;   
-> END;  
-> //   
mysql > DELIMITER

执行结果:

1. mysql 
   mysql > CALL demo_inout_parameter(@p_inout) ;  
   +---------+  
   | p_inout |  
   +---------+  
   |    1    |  
   +---------+  
   +---------+  
   | p_inout |   
   +---------+  
   |    2    |  
   +---------+  
   mysql > SELECT @p_inout;  
   +----------+  
   | @p_inout |   
   +----------+  
   |    2     |  
   +----------+

(4). 变量

Ⅰ. 变量定义

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)

例如:

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

Ⅱ. 变量赋值

 SET 变量名 = 表达式值 [,variable_name = expression ...]

Ⅲ. 用户变量

ⅰ. 在MySQL客户端使用用户变量

1. mysql > SELECT 'Hello World' into @x;  
   mysql > SELECT @x;  
   +-------------+ 
   |   @x        |  
   +-------------+ 
   | Hello World |  
   +-------------+ 
   mysql > SET @y='Goodbye Cruel World';  
   mysql > SELECT @y;  
   +---------------------+ 
   |     @y              |  
   +---------------------+ 
   | Goodbye Cruel World |  
   +---------------------+ 
   mysql > SET @z=1+2+3;  
   mysql > SELECT @z;  
   +------+ 
   | @z   |  
   +------+ 
   |  6   |  
   +------+

ⅱ. 在存储过程中使用用户变量

1. mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
   mysql > SET @greeting='Hello';  
   mysql > CALL GreetWorld( );  
   +----------------------------+ 
   | CONCAT(@greeting,' World') |  
   +----------------------------+ 
   |  Hello World               |  
   +----------------------------+

ⅲ. 在存储过程间传递全局范围的用户变量

1. mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
   mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);  
   mysql> CALL p1( );  
   mysql> CALL p2( );  
   +-----------------------------------------------+ 
   | CONCAT('Last procedure was ',@last_proc  |  
   +-----------------------------------------------+ 
   | Last procedure was p1                         |  
   +-----------------------------------------------+

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理

(5). 注释

MySQL存储过程可使用两种风格的注释

双模杠：--

该风格一般用于单行注释

c风格： 一般用于多行注释

例如：

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc1 --name存储过程名 
-> (IN parameter1 INTEGER)   
-> BEGIN
-> DECLARE variable1 CHAR(10);   
-> IF parameter1 = 17 THEN
-> SET variable1 = 'birds';   
-> ELSE
-> SET variable1 = 'beasts';   
-> END IF;   
-> INSERT INTO table1 VALUES (variable1);  
-> END
-> //  
mysql > DELIMITER ;

4.      MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

5.      MySQL存储过程的查询

我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。

我们可以用

select name from mysql.proc where db=’数据库名’;
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';

进行查询。

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？

答案是：我们可以查看存储过程的详细，但是需要用另一种方法：

SHOW CREATE PROCEDURE 数据库.存储过程名;

就可以查看当前存储过程的详细。

6.      MySQL存储过程的修改

ALTER PROCEDURE

更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

7.      MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROP PROCEDURE

从MySQL的表格中删除一个或多个存储过程。

8.      MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3()  
     -> begin
     -> declare x1 varchar(5) default 'outer';  
     -> begin
     -> declare x1 varchar(5) default 'inner';  
     -> select x1;  
     -> end;  
     -> select x1;  
     -> end;  
     -> //  
mysql > DELIMITER

 (2). 条件语句

Ⅰ. if-then -else语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then
     -> update t set s1=s1+1;  
     -> else
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

Ⅱ. case语句：


1. mysql > DELIMITER //  
   mysql > CREATE PROCEDURE proc3 (in parameter int)  
        -> begin
        -> declare var int;  
        -> set var=parameter+1;  
        -> case var  
        -> when 0 then
        -> insert into t values(17);  
        -> when 1 then
        -> insert into t values(18);  
        -> else
        -> insert into t values(19);  
        -> end case;  
        -> end;  
        -> //  
   mysql > DELIMITER ;

(3). 循环语句

Ⅰ. while ···· end while：

1. mysql > DELIMITER //  
   mysql > CREATE PROCEDURE proc4()  
        -> begin
        -> declare var int;  
        -> set var=0;  
        -> while var<6 do  
        -> insert into t values(var);  
        -> set var=var+1;  
        -> end while;  
        -> end;  
        -> //  
   mysql > DELIMITER ;

Ⅱ. repeat···· end repeat：

它在执行操作后检查结果，而while则是执行前进行检查。

1. mysql > DELIMITER //  
   mysql > CREATE PROCEDURE proc5 ()  
        -> begin
        -> declare v int;  
        -> set v=0;  
        -> repeat  
        -> insert into t values(v);  
        -> set v=v+1;  
        -> until v>=5  
        -> end repeat;  
        -> end;  
        -> //  
   mysql > DELIMITER ;

Ⅲ. loop ·····end loop:

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

1. mysql > DELIMITER //  
   mysql > CREATE PROCEDURE proc6 ()  
        -> begin
        -> declare v int;  
        -> set v=0;  
        -> LOOP_LABLE:loop  
        -> insert into t values(v);  
        -> set v=v+1;  
        -> if v >=5 then
        -> leave LOOP_LABLE;  
        -> end if;  
        -> end loop;  
        -> end;  
        -> //  
   mysql > DELIMITER ;

Ⅳ. LABLES 标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

(4). ITERATE迭代

Ⅰ. ITERATE:

通过引用复合语句的标号,来从新开始复合语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

9.      MySQL存储过程的基本函数 

(1).字符串类

CHARSET(str) //Return the string character set
##CONCAT (string2 [,... ]) // Connection string
INSTR (string ,substring ) //Returnsubstring# The position where ## first appears in string , does not exist and returns 0LCASE (string2 ) //
Convert to lowercaseLEFT (string2 ,length ) //
Take length characters from the left in string2LENGTH (string) //string
LengthLOAD_FILE (file_name) //
Read content from fileLOCATE (substring, string [,start_position] )
Same as INSTR,but you can specify the starting positionLPAD (string2 ,length ,pad ) //
Repeat using pad to add to string starts with , until the string length is ##lengthLTRIM (string2 ) //Remove leading spaces
REPEAT (string2 ,count ) //
Repeatcount Times ##REPLACE (str ,search_str ,replace_str ) //
Use ## in str #replace_strReplacesearch_strRPAD (string2 ,length ,pad) //at## After #str
, add , with pad until the length is lengthRTRIM (string2) //Remove trailing spacesSTRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 

mysql> select substring('abcd',0,2);  
+-----------------------+ 
| substring('abcd',0,2) |  
+-----------------------+ 
|                       |  
+-----------------------+ 
1 row in set (0.00 sec)  
mysql> select substring('abcd',1,2);  
+-----------------------+ 
| substring('abcd',1,2) |  
+-----------------------+ 
|     ab                |  
+-----------------------+ 
1 row in set (0.02 sec)  
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

(2).数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如：
(1)默认变为整形值

mysql> select round(1.23);  
+-------------+ 
| round(1.23) |  
+-------------+ 
|           1 |  
+-------------+ 
1 row in set (0.00 sec)  
mysql> select round(1.56);  
+-------------+ 
| round(1.56) |  
+-------------+ 
|           2 |  
+-------------+ 
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据

mysql> 
+----------------+ 
| round(1.567,2) |  
+----------------+ 
|           1.57 |  
+----------------+ 
1 row in set (0.00 sec) 
SIGN (number2 ) //

(3).日期时间类

ADDTIME (date2 ,time_interval ) //Add time_interval to date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //Convert time zone
CURRENT_DATE ( ) //Current date
CURRENT_TIME ( ) //Current time
CURRENT_TIMESTAMP ( ) //Current timestamp
DATE (datetime ) //Return the date part of datetime
DATE_ADD (date2, INTERVAL d_value d_type) //in## Add date or time to #date2DATE_FORMAT (datetime ,FormatCodes ) //
Use formatcodesFormat displaydatetimeDATE_SUB (date2, INTERVAL d_value d_type) //
Subtract one from date2 TimeDATEDIFF (date1,date2) //
Difference between two datesDAY (date) //
Return The day of the dateDAYNAME (date) //
English weekDAYOFWEEK (date) //week(1-7), 1 is Sunday
DAYOFYEAR (date) //Day of the year
EXTRACT (interval_name FROM date ) //Fromdate Extract the specified part of the date
MAKEDATE (year,day) //gives the year and the day of the year,Generate date string
MAKETIME (hour, minute, second) //Generate time string
MONTHNAME (date) // English month name
NOW ( ) //Current time
SEC_TO_TIME (seconds ) // seconds Convert number to time
STR_TO_DATE (string,format) //Convert string to time, to formatFormat display
TIMEDIFF (datetime1, datetime2) //Two time differences
TIME_TO_SEC (time) //Time to seconds]
WEEK (date_time [,start_of_week ]) //Week
YEAR (datetime) //Year
DAYOFMONTH(datetime) //Day of the month
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0

SQRT(number2) //开平方

PHP调用MYSQL存储过程实例

实例一：无参的存储过程

$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
$sql = "
create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end; 
";
mysql_query($sql);//创建一个myproce的存储过程
$sql = "call test.myproce();";
mysql_query($sql);//调用myproce的存储过程，则数据库中将增加一条新记录。

实例二：传入参数的存储过程

$sql = "
create procedure myproce2(in score int)
begin
if score >= 60 then
select 'pass';
else
select 'no';
end if;
end; 
";
mysql_query($sql);//创建一个myproce2的存储过程
$sql = "call test.myproce2(70);";
mysql_query($sql);//调用myproce2的存储过程,看不到效果，可以在cmd下看到结果。

实例三：传出参数的存储过程

$sql = "
create procedure myproce3(out score int)
begin
set score=100;
end; 
";
mysql_query($sql);//创建一个myproce3的存储过程
$sql = "call test.myproce3(@score);";
mysql_query($sql);//调用myproce3的存储过程
$result = mysql_query('select @score;');
$array = mysql_fetch_array($result);
echo &#39;<pre class="brush:php;toolbar:false">&#39;;print_r($array);

实例四：传出参数的inout存储过程

$sql = "
create procedure myproce4(inout sexflag int)
begin
SELECT * FROM user WHERE sex = sexflag;
end; 
";
mysql_query($sql);//创建一个myproce4的存储过程
$sql = "set @sexflag = 1";
mysql_query($sql);//设置性别参数为1
$sql = "call test.myproce4(@sexflag);";
mysql_query($sql);//调用myproce4的存储过程,在cmd下面看效果

实例五：使用变量的存储过程 

$sql = "
create procedure myproce5(in a int,in b int)
begin
declare s int default 0;
set s=a+b;
select s;
end; 
";
mysql_query($sql);//创建一个myproce5的存储过程
$sql = "call test.myproce5(4,6);";
mysql_query($sql);//调用myproce5的存储过程,在cmd下面看效果

实例六：case语法

$sql = "
create procedure myproce6(in score int)
begin
case score
when 60 then select '及格';
when 80 then select '及良好';
when 100 then select '优秀';
else select '未知分数';
end case;
end; 
";
mysql_query($sql);//创建一个myproce6的存储过程
$sql = "call test.myproce6(100);";
mysql_query($sql);//调用myproce6的存储过程,在cmd下面看效果

实例七：循环语句

$sql = "
create procedure myproce7()
begin
declare i int default 0;
declare j int default 0;
while i<10 do
set j=j+i;
set i=i+1;
end while;
select j;
end; 
";
mysql_query($sql);//创建一个myproce7的存储过程
$sql = "call test.myproce7();";
mysql_query($sql);//调用myproce7的存储过程,在cmd下面看效果

实例八：repeat语句

$sql = " 
create procedure myproce8()
begin
declare i int default 0;
declare j int default 0;
repeat
set j=j+i;
set i=i+1;
until j>=10
end repeat;
select j;
end; 
";
mysql_query($sql);//创建一个myproce8的存储过程
$sql = "call test.myproce8();";
mysql_query($sql);//调用myproce8的存储过程,在cmd下面看效果

实例九：loop语句

$sql = "
create procedure myproce9()
begin
declare i int default 0;
declare s int default 0;
loop_label:loop
set s=s+i;
set i=i+1;
if i>=5 then
leave loop_label;
end if;
end loop;
select s;
end; 
";
mysql_query($sql);//创建一个myproce9的存储过程
$sql = "call test.myproce9();";
mysql_query($sql);//调用myproce9的存储过程,在cmd下面看效果

实例十：删除存储过程

mysql_query("drop procedure if exists myproce");//删除test的存储过程
实例十：存储过程中的游标

以上就是本篇文章的全部内容了，更多相关内容请关注PHP中文网。

相关推荐：

php调用mysql存储过程和函数的方法

The above is the detailed content of Detailed explanation of mysql stored procedures and examples of calling MYSQL stored procedures from PHP. For more information, please follow other related articles on the PHP Chinese website!