How to create, delete, and call a stored procedure in mysql5

This article will introduce some of my learning notes during the mysql Stored Procedure (create, delete, and call). If you need to learn the stored procedure, you can go to the reference page.

1. Create a stored procedure
1. Basic Syntax:
Create procedure sp_name ()
Begin
.........
End

 

2. parameter transfer

Ii. Call the Stored Procedure

1. Basic Syntax: call sp_name ()

CALL Statement (Stored Procedure CALL)
CALL stored procedure name (parameter list)
 

The CALL statement calls a previously created program using create procedure.
The CALL statement can return a value to its caller using the INOUT parameter declared as OUT or.
The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters passed

Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

Iii. delete stored procedures

1. Basic Syntax:

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. You can only call another stored procedure.

2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

4. blocks, conditions, and loops

1. Block definition, commonly used
Begin
......
End;
You can also create an alias for the block, such:
Lable: begin
...........
End lable;
You can use leave lable to jump out of the block and execute code after the block.
2. conditional statements
If condition then
Statement
Else
Statement
End if;

3. Loop statements

(1). while Loop
[Label:] WHILE expression DO

Statements

End while [label];

(2) loop
[Label:] LOOP

Statements

End loop [label];

(3). repeat until Loop
[Label:] REPEAT

Statements

UNTIL expression

End repeat [label];

5. Other Common commands
1. show procedure status
Displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2. show create procedure sp_name
Displays detailed information about a stored procedure.

 

You can use the in... END compound statement to store subprograms to contain multiple statements.

Statement_list indicates the list of one or more statements. Each statement in statement_list must end with a semicolon.

Compound statements can be marked. Unless begin_label exists, end_label cannot be given, and if both exist, they must be the same.

1.8 DECLARE Statement (used to DECLARE local variables)
The DECLARE statement is used to place different projects to a sub-program: local variables

DECLARE is only used in the in... END compound statement and must BEGIN with the compound statement before any other statement.

1.9 variables in the storage 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 DEFAULT clause.
The value can be specified as an expression and does not need to be a constant.
If no DEFAULT clause exists, the initial value is NULL.
The scope of a local variable is within the declared BEGIN... END block.
It can be used in nested blocks, except those that declare variables with the same name.
 

1.2 SET statement

SET var_name = expr [, var_name = expr]
The SET statement in the storage Program is an extended version of the general SET statement.
The referenced variable may be a variable declared in the 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 the SET a = x, B = y,... extension syntax.
Different variable types (local declaration variables and global and collective variables) can be mixed.
This also allows you to combine local variables with some options that only make sense to system variables.
 

1.3 SELECT... INTO statement

SELECT col_name [,...] INTO var_name [,...] table_expr
This SELECT syntax stores the selected columns directly to variables.
Therefore, only a single row can be retrieved.
SELECT id, data INTO x, y FROM test. t1 LIMIT 1;
Note that user variable names are case-insensitive in MySQL 5.1.
 

Important: the SQL variable name cannot be the same as the column name. If an SQL statement such as SELECT... INTO contains a reference to a column and a local variable with the same name as the column, MySQL interprets the reference as the name of a variable.

PHP calls the MySQL stored procedure.

Example 1: stored procedure without Parameters

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 );//

When the stored procedure of myproce is called, a new record is added to the database.

Example 2: Stored Procedure of input parameters

The Code is as follows:	Copy code
$ SQL =" Create procedure myproce2 (in score int) Begin If score> = 60 then Select 'pass '; Else Select 'no '; End if; End; "; Mysql_query ($ SQL); // create a stored procedure of myproce2 $ SQL = "call test. myproce2 (70 );"; Mysql_query ($ SQL );//

Calling the stored procedure of myproce2 has no effect. You can see the result in cmd.

Example 3: Stored Procedure of outgoing Parameters

The Code is as follows:	Copy code
$ SQL =" Create procedure mypropushed (out score int) Begin Set score = 100; End; "; Mysql_query ($ SQL); // create a stored procedure of myproc4 $ SQL = "call test. myproc4( @ score );"; Mysql_query ($ SQL); // call the stored procedure of myproc4 $ Result = mysql_query ('select @ score ;'); $ Array = mysql_fetch_array ($ result ); Echo '<pre>'; print_r ($ array );

Example 4: inout Stored Procedure of 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 stored procedure of myproce4 $ SQL = "set @ sexflag = 1 "; Mysql_query ($ SQL); // set the Gender parameter to 1. $ SQL = "call test. myproce4 (@ sexflag );"; Mysql_query ($ SQL );//

Call the stored procedure of myproce4. See the result in cmd.