Shulou(Shulou.com)06/01 Report--

This paper illustrates the concept, principle and common usage of MySQL stored procedure. Share with you for your reference, the details are as follows:

1. The concept of stored procedure

In some languages, such as pascal, there is a concept called "procedure" procedure, and "function" function. In php, there are no procedures, only functions.

Procedure: several statements are encapsulated, and when called, these wrappers execute

Function: is a "procedure" with a return value

Summary: a procedure is a function with no return value

In MySQL:

We encapsulate a number of sql and give them a name-- process.

Store this procedure in the database-- stored procedure

2. Create a stored procedure

Create procedure procedureName () begin / /-- sql statement end$

3. View existing stored procedures

Show procedure status

4. Delete stored procedures

Drop procedure procedureName

5. Call the stored procedure

Call procedureName ()

6. The first stored procedure

Note: I have changed the end identifier of MySQL to $. If you want to know how to set it to $, please refer to my other article: MySQL trigger.

Create procedure p1 () begin select 2, 3 and end $

Call:

Call p1 ()

Display the results:

7. Introduce variables

Stored procedures are programmable, meaning you can use variables, expressions, and control structures to perform complex functions. In stored procedures, variables are declared with declare:

Declare variable name variable type [default default]

Use:

Create procedure p2 () begin declare age int default 18; declare height int default 180; select concat ('age:', age,' height:', height); end$

Display the results:

8. Introduce expression

In a stored procedure, variables can be legally operated on in sql statements, such as +-* /. The form of assignment of the variable:

Set variable name: = expression

Use:

Create procedure p3 () begin declare age int default 18; set age: = age + 20; select concat ('20 years later age:', age); end$

Display the results:

9. Introduce the selection control structure

Format:

If condition then statementelseif statementelse statementend if

Use:

Create procedure p4 () begin declare age int default 18; if age > = 18 then select 'Adult'; else select 'minor'; end if;end$

Display the results:

10. Pass parameters to the stored procedure

In parentheses that define stored procedures, you can declare parameters, syntax:

[in/out/inout] Parameter name parameter type

Use:

Create procedure p5 (width int,height int) begin select concat ('your area is:', width * height) as area; if width > height then select 'you are fat'; elseif width < height then select 'you are thin'; else select 'you compare square'; end if;end$

Display the results:

11. Use while loop structure

Requirements: from 1 to 100

Use:

Create procedure p6 () begin declare total int default 0; declare num int default 0; while num

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.