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

The following to understand the detailed process of storing the mysql database, I believe you will benefit a lot after reading, the text is not much in the essence, hope to store the detailed process of the mysql database this short content is what you want.

Brief introduction of stored procedure

Stored procedures can be simply understood as a collection of one or more sql statements. Stored procedures are used to execute a set of sql statements about table operations as a whole. The most important features of stored procedures in practical applications improve execution efficiency and sql code encapsulation, especially sql code encapsulation, if there is no stored procedure. When an external program accesses the database (such as php), it is necessary to organize a lot of sql statements, especially when the business logic is more complex, a lot of sql and conditions are sandwiched in the php code, which makes people shudder. With sql stored procedures, business logic can encapsulate stored procedures, which is not only easy to maintain, but also efficient.

For example, using stored procedures to process commodity orders in e-commerce sites

How a stored procedure is a programming language that also contains data types, flow control, input and output, and its own function library

Create a stored procedure

Basic grammar

The Create procedure sp_nane () function may or may not have arguments

Begin

-/ / Code body

End

Call sp_nane () stored procedure name / / call stored procedure

Instance mysql > use mysql

Mysql > Delimiter $$/ / define the delimiter

The Mysql > Create procedure sp_nane () function may or may not have arguments

Begin

Select user from user

End$$

Mysql > Delimiter

View stored procedures

Method one: show procedure status

Example: show procedure status\ G

Method 2 check the system table information_schema.routines

Example: select * from information_schema.routines\ G

Method 3 View the stored procedure definition information through the show crerate procedure statement

Example: show crerate procedure proce_user\ G

Delete stored procedure

Do not add () when deleting the name of the Drop procedure stored procedure.

Example: drop procedure proce_param_inout

Core sequence branch cycle in process control core

Cycle control

WHILE-DO--END WHILE / / judge before execution

Example:

Mysql > Delimiter $$/ / define the delimiter

Mysql > create table T1 (id int)

The Mysql > Create procedure proce_while () function may or may not have arguments

Begin

Declare i int; / / define variables

Set iTunes 1; / / initializes the variable. If it is not initialized, it will be null

While iDelimiter

Pepeat---until end pepeat executes before judging

Example:

Mysql > Delimiter $$/ / define the delimiter

The Mysql > Create procedure proce_repeat () function may or may not have arguments

Begin

Declare i int default 1; / / define variables, initialize

Repeat

Insert into T1 values (I)

Set i=i+1

Until I > = 6

End repeat

End$$

Mysql > Delimiter

Loop--end loop

Example:

Mysql > Delimiter $$/ / define the delimiter

The Mysql > Create procedure proce_loop () function may or may not have arguments

Begin

Declare i int default 1; / / define variables, initialize

Loop_label:loop / / defines a label, just like the label

Insert into T1 values (I)

Set i=i+1

If I > = 6 then

Leave loop_label

End if

End loop

End$$

Mysql > Delimiter

Input and output type 1) In passes parameters to the stored procedure, which will change the value inside the stored procedure and will not be seen by the caller

Example:

Mysql > Delimiter $$/ / define the delimiter

Mysql > Create procedure proce_param_in (in id int) / / the value passed by in is the id field, the value of type int

Begin

If (id is null) then / / if the id variable is null

Select'id is null' as id_null

Else

Set i=i+1

End if

Select id as id_inside; / / print the value of id

End$$

Mysql > Delimiter

2) the Out stored procedure passes values internally to the caller, and does not accept external values

Example:

Mysql > Delimiter $$/ / define the delimiter

Mysql > Create procedure proce_param_out (out id int) / / the value passed by out is the id field, the value of type int

Begin

The initial value of Select id as id_inside_1; / / id is null

If (id is not null) then / / if the id variable is not null

Set id=id+1

Select id as id_inside_2; / / print the value of id

Else

Select 100,100 into id; / / input 100into id

End if

Select id as id_inside_3

End$$

Mysql > Delimiter

3) inout can accept the value passed by the caller and return the result of the last change to the caller

Example:

Mysql > Delimiter $$/ / define the delimiter

Mysql > Create procedure proce_param_inout (inout id int) / / the value passed by out is the id field, the value of type int

Begin

The Select id as id_inside_1; / / id value is the value passed in by the caller

If (id is not null) then / / if the id variable is not null

Set id=id+1

Select id as id_inside_2; / / print the value of id

Else

Select 100,100 into id; / / input 100into id

End if

Select id as id_inside_3

End$$

Mysql > Delimiter

After reading this article on the detailed process of storing mysql database, many readers will want to know more about it. For more industry information, you can follow our industry information section.

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.