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 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:

Copy code

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 it, we can start to write some simple stored procedures.

First, Create procedure and Create function)

The Code is as follows:	Copy code
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 is a parameter that indicates the incoming but the final return.

The Code is as follows:	Copy code
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:	Copy code
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 stored procedure of mysql.

// Baidu knows the following:

The Code is as follows:	Copy code
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) );

Add a user's stored procedure:

The Code is as follows:	Copy code
Delimiter // Create procedure insertuser (in username varchar (20), in userpwd varchar (32 )) Begin Insert into welefen. user (Name, Pwd) values (username, md5 (userpwd )); End //

Verify the user's stored procedure:

The Code is as follows:	Copy code
Delimiter // Create procedure validateuser (in username varchar (20), out param1) Begin Select Pwd into param1 from welefen. user where Name = username; End //

Password Change stored procedure:

The Code is as follows:	Copy code
Delimiter // Create procedure modifyPwd (in username varchar (20), in userpwd varchar (32 )) Begin Update welefen. user set Pwd = md5 (userpwd) where Name = username; End //

Delete a user's stored procedure:

 

The Code is as follows:	Copy code
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 program:

The Code is as follows:

Copy code

<? Php If (! Mysql_connect ("localhost", "root", "welefen ")){ Echo "failed to connect to Database "; } If (! Mysql_select_db ("welefen ")){ Echo "failed to select database table <br> "; } $ 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 <br> "; } Else { Echo "failed to add user $ insert_user [0] <br> "; } $ 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] verification is correct <br> "; } Else { Echo "User $ validate_user [0] verification error <br> "; } $ 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 "the password of $ modigy_Pwd [0] is successfully modified <br> "; } Else { Echo "failed to change the password of user $ modigy_Pwd [0] <br> "; } $ Delete_user = array ("welefen"); // welefen is the user name If (mysql_query ("call deleteuser ('$ delete_user [0]')") { Echo "User $ delete_user [0] deleted successfully <br> "; } Else { Echo "User $ delete_user [0] failed to delete <br> "; } ?>

In this way, php calls the mysql stored procedure. In fact, these simple applications cannot use the stored procedure, and the actual application is much more complicated than this. it can be seen 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.