MySQL5 stored procedure entry creation, deletion, call details

I. Creating a stored Procedure
1. Basic grammar:
CREATE PROCEDURE Sp_name ()
Begin
.........
End

2. Parameter transfer

Two. Call the stored procedure

1. Basic syntax: Call Sp_name ()

Call statement (invocation of a stored procedure)
Call stored procedure name (parameter list)

The call statement invokes a program that was previously created with the CREATE procedure.
The call statement can return a value to its caller with an argument to the InOut parameter declared as out or.
The stored procedure name must be followed by parentheses, even if the stored procedure has no parameter passing

Note: The stored procedure name must be followed by parentheses, even if the stored procedure has no parameter passing

Three. Delete stored procedures

1. Basic grammar:

DROP PROCEDURE IF exists stored procedure name

Eg:drop PROCEDURE IF EXISTS proc_employee (Proc_employee stored procedure name)

This statement is used to remove a stored program. You cannot delete another stored procedure in one stored procedure, only another stored procedure can be invoked

2. Matters needing attention
(1) cannot delete another stored procedure in one stored procedure, can only call another stored procedure

Four. blocks, conditions, loops

1. Block definition, commonly used
Begin
......
End
You can also alias a block, such as:
Lable:begin
...........
End lable;
You can use leave lable to jump out of blocks and execute code after blocks
2. Conditional statement
If condition Then
Statement
Else
Statement
End If;

3. Circular statements

(1). While loop
[Label:] While expression do

Statements

End while [Label];

(2). Loop loop
[Label:] LOOP

Statements

End LOOP [Label];

(3). Repeat until cycle
[Label:] REPEAT

Statements

UNTIL expression

End REPEAT [Label];

Five. Other common commands
1.show Procedure Status
Displays basic information about stored procedures stored in the database, including the owning database, stored procedure name, creation time, and so on
2.show CREATE PROCEDURE Sp_name
Show details of a stored procedure

Stored subroutines can use the begin ... End Compound statement to contain multiple statements.

Statement_list represents a list of one or more statements. Within Statement_list, each statement must end with a semicolon (;).

Compound statements can be marked. Unless Begin_label exist, End_label cannot be given, and if both exist, they must be the same.

1.8 Declare statement (used to declare local variables)
Declare statements are used to place different project domains into a subroutine: local variables

Declare is used only in begin ... End Compound statement, and must precede any other statement at the beginning of the compound statement.

1.9 Variables in the stored program
1.1 Declare local variables

DECLARE var_name[,...] Type [DEFAULT value]
This statement is used to declare local variables.
To provide a default value for a variable, include a null clause.
Values can be specified as an expression and do not need to be a constant.
If there is no default clause, the initial value is null.
The scope of the local variable is in its declared begin ... End block.
It can be used in nested blocks, except for those that declare variables with the same name.

1.2 Variable SET statement

SET var_name = expr [, var_name = expr]
A set statement in a stored program is an extended version of a generic set statement.
The referenced variable may be a variable declared within a subroutine, or a global server variable.
The SET statement in the stored program is implemented as part of the pre-existing set syntax. This allows set a=x, B=y, ... Such an extension syntax.
The different types of variables (local declaration variables and global and collective variables) can be mixed.
This also allows you to combine local variables with some options that are meaningful only for system variables.

1.3 SELECT ... INTO statement

SELECT col_name[,...] Into var_name[,...] table_expr
This select syntax stores the selected columns directly to the variable.
Therefore, only a single line can be retrieved.
SELECT id,data into X,y from test.t1 LIMIT 1;
Note that the user variable name is not case sensitive in MySQL 5.1.

IMPORTANT: SQL variable names cannot be the same as column names. If Select ... into such an SQL statement contains a reference to the column and contains a local variable with the same name as the column, which the MySQL currently interprets as the name of a variable.

PHP invokes the MySQL stored procedure.

instance One: parameter-free stored procedures

The code is as follows	Copy Code
$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!"); mysql_select_db (' Test ', $conn); $sql = " CREATE PROCEDURE Myproce () Begin INSERT into user (ID, username, sex) VALUES (NULL, ' s ', ' 0 '); End "; mysql_query ($sql);//Create a Myproce stored procedure $sql = "Call Test.myproce ();"; mysql_query ($sql);//

Calls the myproce stored procedure, a new record is added to the database.

Instance two: Stored procedures for incoming parameters

The code is as follows	Copy Code
$sql = " CREATE PROCEDURE Myproce2 (in score int) Begin If score >= Then Select ' Pass '; Else Select ' No '; End If; End "; mysql_query ($sql);//Create a Myproce2 stored procedure $sql = "Call Test.myproce2 (70);"; mysql_query ($sql);//

Call the Myproce2 stored procedure, see the effect, you can see the results under CMD.

Instance three: Stored procedures for outgoing parameters

The code is as follows	Copy Code
$sql = " CREATE PROCEDURE Myproce3 (out score int) Begin Set score=100; End "; mysql_query ($sql);//Create a Myproce3 stored procedure $sql = "Call Test.myproce3 (@score);"; mysql_query ($sql);//Call Myproce3 stored procedure $result = mysql_query (' select @score; '); $array = Mysql_fetch_array ($result); Echo ' <pre> ';p rint_r ($array);

Instance four: InOut stored procedures for outgoing parameters

The code is as follows	Copy Code
$sql = " CREATE PROCEDURE Myproce4 (inout Sexflag int) Begin SELECT * from user WHERE sex = sexflag; End "; mysql_query ($sql);//Create a Myproce4 stored procedure $sql = "Set @sexflag = 1"; mysql_query ($sql);/set gender parameter to 1 $sql = "Call Test.myproce4 (@sexflag);"; mysql_query ($sql);//

Call the Myproce4 stored procedure, see the effect under CMD