Mysql 5.0 Stored Procedure Learning

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 ()

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

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

Reference content is as follows:
If condition then
Statement
Else
Statement
End if;

3. Loop statements

(1). while Loop

Reference content is as follows:
[Label:] WHILE expression DO
Statements
End while [label];

(2) loop

Reference content is as follows:
[Label:] LOOP
Statements
End loop [label];

(3). repeat until Loop

Reference content is as follows:
[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.