Mysql database Stored Procedures

1. Create a stored procedure

1. Basic Syntax:

Create procedure sp_name ()

Begin

.........

End

Ii. Call the Stored Procedure

1. Basic Syntax:

Call sp_name ()

Note: The stored procedure must be enclosed in parentheses, even if the stored procedure is not stored.

Iii. delete stored procedures

1. Basic Syntax:

Drop procedure ap_name //

Note: You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

4. blocks, conditions, and cycles

1. Block definition: Common

Begin

.......

End;

You can also give the block an alias.

.........

End label;

You can use the leave label to jump out of the block.

2. conditional statements

If condition then

Statment

Else

Statement

Else 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 procdure status

Displays the basic information of 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.

Operators used in mysql stored procedures

Mysql Stored Procedure learning Summary-Operators
Arithmetic Operators

+ Add SET var1 = 2 + 2; 4
-Subtract SET var2 = 3-2; 1
* Multiply by SET var3 = 3*2; 6
/Except SET var4 = 10/3; 3.3333
DIV Division SET var5 = 10 DIV 3; 3
% Modulo SET var6 = 10% 3; 1

Comparison Operators

> Greater than 1> 2 False
<Less than 2 <1 False
<= Less than or equal to 2 <= 2 True
>=Greater than or equal to 3> = 2 True
BETWEEN is 5 BETWEEN two values BETWEEN 1 AND 10 True
Not between two values 5 not between 1 AND 10 False
IN the set, 5 IN (,) is False.
Not in is not in the set 5 not in (,) True
= Equal to 2 = 3 False
<> ,! = Not equal to 2 <> 3 False
<=> Strictly compare whether two NULL values are equal NULL <=> NULL True
LIKE simple pattern matching "Guy Harrison" LIKE "Guy %" True
REGEXP regular expression match "Guy Harrison" REGEXP "[Gg] reg" False
Is null 0 is null False
Is not null 0 is not null True
Logical operators

AND)

 

Author "Stand Up"