MySQL 5.0 Stored Procedure Experience Summary

The following articles mainly describe the experience of the MySQL 5.0 stored procedure, including the correct creation, deletion, and call of the MySQL database Stored Procedure and other common commands, the following is a detailed description of the MySQL 5.0 Stored Procedure experience.

MySQL 5.0 Stored Procedure learning summary:

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) MySQL 5.0 stored procedures cannot delete another stored procedure in one stored procedure, but 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

MySQL 5.0 displays the 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.

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 mode matches "Guy Harrison" LIKE "Guy %" True

REGEXP Regular Expression matches "Guy Harrison" REGEXP "[Gg] reg" False

Is null 0 is null False

Is not null 0 is not null True

Logical operators

AND)

 
 

  
  
AND TRUE FALSE NULL   
  
  
TRUE TRUE FALSE NULL   
  
  
FALSE FALSE FALSE NULL   
  
  
NULL NULL NULL NULL  
 
 

OR (OR)

 
 

  
  
OR TRUE FALSE NULL   
  
  
TRUE TRUE TRUE TRUE   
  
  
FALSE TRUE FALSE NULL   
  
  
NULL TRUE NULL NULL  
 
 

Exclusive or (XOR)

 
 

  
  
XOR TRUE FALSE NULL   
  
  
TRUE FALSE TRUE NULL   
  
  
FALSE TRUE FALSE NULL   
  
  
NULL NULL NULL NULL  
 
 

Bitwise operators

| Bit or

& Bit and

<Left shift

> Right shift

~ Bitwise non (single object operation, bitwise inversion)

The above content is a summary of the MySQL 5.0 stored procedure, hoping to help you in this regard.