instance of invoking MySQL stored procedure in PHP program

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

When we have finished, we can begin to write some simple stored procedures.

First create a stored procedure, create PROCEDURE (subroutine), create function (functions)

The code is as follows	Copy Code
Create procedure Sp_name ([Proc_parameter]) Routine_body

The parameter types here can be in-out inoutt, meaning is the same as the meaning of the word, in representation is the incoming parameter, out is the argument that is outgoing, INOUT is the parameter that passes in but eventually returns.

The code is as follows	Copy Code
Create Functionsp_name ([Func_parameter]) Returns type Routine_body

The Returns type specifies the returned types, where the given type is the same as the type of the return value, or an error is made.

The following is a simple example:

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 (@a    ->// Query OK, 0 rows a ffected Mysql> Select @a    ->// +---------------------+ | @a  & nbsp;               | +---------------------+ | 5.0.45-community-nt | +---------------------+ 1 row in Set (0.05 sec)

A stored procedure that gets the current version of MySQL. So how does PHP combine with MySQL's stored procedures?

The following from Baidu know:

The code is as follows	Copy Code
Drop table if exists user; Create Table User ( Id int unsigned NOT NULL auto_increment, Name varchar is not NULL, PWD char (not NULL), Primary Key (Id) );

To add a stored procedure for a user:

The code is as follows	Copy Code
Delimiter// Create procedure Insertuser (in username varchar (m), in userpwd varchar (32)) Begin Insert into Welefen.user (name,pwd) VALUES (USERNAME,MD5 (USERPWD)); End //

To authenticate a user's stored procedures:

The code is as follows	Copy Code
Delimiter// Create procedure ValidateUser (in username varchar (from), out param1) Begin Select Pwd into param1 from Welefen.user where name=username; End //

To modify a stored procedure for a password:

The code is as follows	Copy Code
Delimiter// Create procedure Modifypwd (in username varchar (m), in userpwd varchar (32)) Begin Update Welefen.user set PWD=MD5 (userpwd) where name=username; End //

To 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 //

At the client, we give the following procedure:

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 "Select database table failed <br>"; } $insert _user=array ("Welefen", "Welefen");/the Welefen here are username, password if (mysql_query ("Call Insertuser (' $insert _user[0] ', ' $insert _user[1]")) { echo "Add user $insert_user[0] Success <br>"; }else { echo "Add user $insert_user[0] Failed <br>"; } $validate _user=array ("Welefen", "Welefen");/the Welefen here are username, password mysql_query ("Call ValidateUser (' $validate _user[0] ', @a)"); $PWD =mysql_query ("select @a"); $result =mysql_fetch_array ($PWD); if ($result [0]==md5 ($validate _user[1])) { echo "User $validate_user[0] Verify correct <br>"; }else { echo "User $validate_user[0] validation error <br>"; } $modify _pwd=array ("Welefen", "Weilefeng"); Welefen for user name weilefeng new password if (mysql_query ("Call Modifypwd (' $modify _pwd[0] ', ' $modify _pwd[1]")) { echo "User $modigy_pwd[0] Password modification success <br>"; }else { echo "User $modigy_pwd[0] Password modification failed <br>"; } $delete _user=array ("Welefen"); Welefen for User name if (mysql_query ("Call DeleteUser (' $delete _user[0] ')") { echo "User $delete_user[0] Delete success <br>"; }else { echo "User $delete_user[0] Delete failed <br>"; } ?>

This completes, PHP calls MySQL stored procedures, in fact, these simple applications, the use of stored procedures, the actual application is more complex than this. Can be seen that the establishment of the MySQL stored procedures can greatly reduce the pressure on the customer service side, but increased the pressure of database services, the pros and cons of the actual to measure.