MySQL Stored Procedure Instance

File:
MySQL stored procedures. rar
Size:
467KB
Download:
Download
MySQL 5.0 later version to support stored procedures, stored procedures with consistency, efficiency, security and architecture, and so on, this section explains how PHP manipulates MySQL stored procedures through specific examples.
Example Description
In order to ensure data integrity, consistency, improve the performance of applications, often using stored process technology. The prior version of MySQL 5.0 does not support stored procedures, and as MySQL technology becomes more sophisticated, stored procedures will be widely used in future projects. This example describes the creation of a stored procedure in a later version of MySQL 5.0.
Technical Essentials
A stored procedure consists of a name, a list of arguments, and a set of SQL statements that can include many SQL statements. The following is a procedure for defining a stored procedure:
CREATE PROCEDURE Proc_name (in Parameter integer)
Begin
DECLARE variable varchar (20);
If Parameter=1 Then
Set variable= ' MySQL ';
Else
Set variable= ' PHP ';
End If;
INSERT into TB (name) values (variable);
End
The creation of a stored procedure in MySQL starts with the keyword CREATE PROCEDURE, followed by the name and parameters of the stored procedure. The stored procedure names for MySQL are case-insensitive, for example, PROCE1 () and Proce1 () represent the same stored procedure name. The stored procedure name cannot duplicate the built-in function in the MySQL database.
The parameters of a stored procedure are generally made up of 3 parts. The first part can be in, out, or inout. In means that parameters are passed in to the stored procedure; out represents outgoing parameters; InOut indicates that the defined parameters can be passed to the stored procedure and can be modified by the stored procedure, and the stored procedure defaults to the passed parameters, so parameter in can be omitted. The second part is the name of the parameter. The third part is the type of the parameter, which is the type of all fields available in the MySQL database, and if there are multiple parameters, the parameters can be separated by commas.
The statement block for the MySQL stored procedure begins with begin and ends with end. The statement body can contain declarations of variables, control statements, SQL query statements, and so on. Because the inner statement of the stored procedure ends with a semicolon, the statement end flag should be ";" before the stored procedure is defined. Change to a different character, and the probability that the character will appear in the stored procedure should also be low, which can be changed with the keyword delimiter. For example:
Mysql>delimiter//
After the stored procedure is created, it can be deleted using the following statement, parameter proc_name refers to the stored procedure name.
drop procedure Proc_name
implementation Process
(1) The MySQL stored procedure is created under the command prompt, so you should open the Command Prompt window first.
(2) After entering the command prompt window, you should first log into the MySQL database server and enter the following command at "command Prompt":
(3) Change the statement ending symbol, this instance changes the statement terminator to "//". The code is as follows:
(4) You should first select a database before creating the stored procedure. The code is as follows:
(5) Create a stored procedure.
(6) Call the stored procedure through the invoke statement.
Extrapolate

--Build a table
Use test;
CREATE TABLE User (
ID Mediumint (8) unsigned NOT NULL auto_increment,
Name Char (+) NOT null default ' ',
Pass char (+) NOT null default ' ',
Note Text NOT NULL,
Primary KEY (ID)
) Engine=innodb Charset=utf8;
--Example One
Delimiter//
CREATE PROCEDURE Proc_name (in Parameter integer)
Begin
If Parameter=0 Then
SELECT * from the user order by ID ASC;
Else
SELECT * from the user order by id desc;
End If;
End
//
delimiter;
Show warnings;
Call Proc_name (1);
Call Proc_name (0);
--Example Two
drop procedure Proc_name;
Delimiter//
CREATE PROCEDURE Proc_name (in Parameter integer)
Begin
DECLARE variable varchar (20);
If Parameter=1 Then
Set variable= ' Windows ';
Else
Set variable= ' Linux ';
End If;
Select parameter;
End
//
delimiter;
Show warnings;
Call Proc_name (1);
Call Proc_name (0);
Attachment: MySQL stored procedure. rar


This article comes from Chinaunix blog, if you look at the original point:Http://blog.chinaunix.net/u3/93470/showart_2000195.html

MySQL Stored Procedure Instance