Analysis of mysql stored procedure instance called by php

This article mainly introduces the mysql stored procedure called by php, and analyzes various common operations and usage skills of the mysql stored procedure called by php based on various common examples, which has some reference value, for more information about how to call the mysql stored procedure in php, see the example in this article. Share it with you for your reference. The specific analysis is as follows:

Mysql stored procedure creation syntax, the code is as follows:

Create procedure and create function:

The code is as follows:

Create procedure sp_name ([proc_parameter [,...])

[Characteristic...] routine_body

Create function sp_name ([func_parameter [,...])

RETURNS type

[Characteristic...] routine_body

Proc_parameter:

[IN | OUT | INOUT] param_name type

Func_parameter:

Param_name type

Type:

Any valid MySQL data type

Characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}

| SQL SECURITY {DEFINER | INVOKER}

| COMMENT 'string'

Routine_body:

Valid SQL procedure statement or statements

After reading this, we can start to write some simple stored procedures. First, we should establish the stored procedures, Create procedure (subroutine) and Create function (function). The code is as follows:

The code is as follows:

Create procedure sp_Name ([proc_parameter])
Routine_body

Here, the parameter type can be in out inoutt, which means the same as the word. IN indicates the passed parameter, and OUT indicates the outgoing parameter, INOUT indicates the parameters passed in but finally returned. the code is as follows:

The code is as follows:

Create functionsp_Name ([func_parameter])
Returns type
Routine_body

The Returns type specifies the returned type. the given type is the same as the returned type. Otherwise, an error is returned. The following is a simple example. the code is as follows:

The code is as follows:

Mysql> delimiter //
Mysql> create procedure g
-> Begin
-> Select version () I
-> End
-> //
Query OK, 0 rows affected

Mysql> call getversion (@
-> //
Query OK, 0 rows affected

Mysql> select @;
-> //
+ --------------------- +
| @ A |
+ --------------------- +
| 5.0.45-community-nt |
+ --------------------- +
1 row in set (0.05 sec)

A stored procedure for obtaining the current mysql version. how can php be combined with the mysql stored procedure? the following code is from Baidu:

The code is as follows:

Drop table if exists user;
Create table user (
Id int unsigned not null auto_increment,
Name varchar (20) not null,
Pwd char (32) not null,
Primary key (Id)
);

The code for adding a user's stored procedure is as follows:

The code is as follows:

Delimiter //
Create procedure insertuser (in username varchar (20), in userpwd varchar (32 ))
Begin
Insert into welefen. user (Name, Pwd) values (username, md5 (userpwd ));
End
//

The code for verifying a user's stored procedure is as follows:

The code is as follows:

Delimiter //
Create procedure validateuser (in username varchar (20), out param1)
Begin
Select Pwd into param1 from welefen. user where Name = username;
End
//

The stored procedure for password modification is as follows:

The code is as follows:

Delimiter //
Create procedure modifyPwd (in username varchar (20), in userpwd varchar (32 ))
Begin
Update welefen. user set Pwd = md5 (userpwd) where Name = username;
End
//

The code for deleting a user's stored procedure is as follows:

The code is as follows:

Delimiter //
Create procedure deleteuser (in username varchar (20 ))
Begin
Delete from welefen. user where Name = username;
End
//

On the client side, we provide the following code:

The code is as follows:

<? Php
If (! Mysql_connect ("localhost", "root", "welefen ")){
Echo "failed to connect to database ";
}
If (! Mysql_select_db ("welefen ")){
Echo "failed to select database table
";
}

$ Insert_user = array ("welefen", "welefen"); // The welefen here is the user name and password respectively.
If (mysql_query ("call insertuser ('$ insert_user [0]', '$ insert_user [1]')") {
Echo "added user $ insert_user [0] successfully
";
} Else {
Echo "adding user $ insert_user [0] failed
";
}

$ Validate_user = array ("welefen", "welefen"); // The welefen here is the user name and password respectively.
Mysql_query ("call validateuser ('$ validate_user [0]', @ )");
$ Pwd = mysql_query ("select @ ");
$ Result = mysql_fetch_array ($ Pwd );
If ($ result [0] = md5 ($ validate_user [1]) {
Echo "user $ validate_user [0] correct verification
";
} Else {
Echo "user $ validate_user [0] verification error
";
}

$ Modify_Pwd = array ("welefen", "weilefeng"); // welefen indicates the username weilefeng as the new password.
If (mysql_query ("call modifyPwd ('$ modify_Pwd [0]', '$ modify_Pwd [1]')") {
Echo "user $ modigy_Pwd [0] password modified successfully
";
} Else {
Echo "user $ modigy_Pwd [0] password modification failed
";
}

$ Delete_user = array ("welefen"); // welefen is the user name
If (mysql_query ("call deleteuser ('$ delete_user [0]')") {
Echo "user $ delete_user [0] deleted successfully
";
} Else {
Echo "user $ delete_user [0] failed to delete
";
}
?>

In this way, php calls the mysql stored procedure. In fact, these simple applications cannot use the stored procedure. the actual application is much more complex than this one. we can see that, the creation of the mysql storage process can greatly reduce the pressure on the customer service end, but increases the pressure on the database service, the advantages and disadvantages have to be measured.

I hope this article will help you with php programming.