Mysql stored procedure instance called in a php program

Php and mysql are inherently a pair. next I will introduce how to call the stored procedure that has been written in mysql in the php program. let's take a look at the specific implementation methods. mysql stored procedure creation syntax, code: CREATEPRO... php and mysql are inherently a pair. next I will introduce how to call the stored procedure that has been written in mysql in the php program. let's take a look at the specific implementation methods.

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

Create procedure and CREATE FUNCTION

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:

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:

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:

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:

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:

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:

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:

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:

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:

 ";}$ Insert_user = array (" welefen "," welefen "); // Here, the welefen is the user name and password if (mysql_query ("call insertuser ('$ insert_user [0]', '$ insert_user [1]')") {echo "added user $ insert_user [0] successfully
";} Else {echo" failed to add user $ insert_user [0]
";}$ Validate_user = array (" welefen "," welefen "); // The welefen here is the user name and password mysql_query ("call validateuser ('$ validate_user [0]', @ )"); $ Pwd = mysql_query ("select @ a"); $ 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 is 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 "); // 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.