MySQL Database stored procedures

Introduction to Stored Procedures

stored procedures can be easily understood as one or moreA collection of SQL statements that are used to implement a set of SQL statements about the operations of a table as a whole .The most important features of stored procedures in practical applications are improved execution efficiency and SQL code encapsulation capabilities, In particular , the SQL Code encapsulation feature, if there is no stored procedure, when the external program accesses the database ( For example , PHP), to organize a lot of SQL statements, especially when the business logic is complex, a lot of SQL and conditions in the php Code, it makes people shudder, with SQL stored procedures, business logic can encapsulate stored procedures, This is not only easy to maintain, but also high execution efficiency.

For example: Use stored procedures in an e-commerce site to implement commodity order Processing

How a stored procedure is a programming language that also contains data types, process controls, inputs and outputs, and its own library of functions

create a stored procedure

Basic syntax

Create procedure Sp_nane () function can have parameters or not

Begin

---------// code body

End

Call Sp_nane () stored procedure name // calling stored procedure

example mysql>use mysql

Mysql>delimiter $$// defining separators

Mysql>create procedure Sp_nane () function can have parameters or no

Begin

Select user from user;

end$$

mysql>delimiter;

Viewing stored procedures

method One show procedure status

Example:Show procedure Status \g

method Two view system table Information_schema.routines

Example:select * from Information_schema.routines\g

method Three view the stored procedure definition information through the show Crerate procedure statement

Example:show Crerate procedure Proce_user\g

To Delete a stored procedure

Drop procedure stored procedure name do not add () at the time of deletion .

Example:drop procedure proce_param_inout;

core of Process Control core

Order Branch Loops

Loop control

While---do--end while// first judge and then execute

Instance:

Mysql>delimiter $$// defining separators

Mysql>create table t1 (id int);

Mysql>create procedure Proce_while () function can have parameters or no

Begin

Declare I int; Defining Variables

Set I=1; A variable is initialized, and null if not initialized

while i<5 Do

Insert into T1 values (i);

Set i=i+1;

End while;

end$$

mysql>delimiter;

Pepeat---until end pepeat perform the re-judgment first

Instance:

Mysql>delimiter $$// defining separators

Mysql>create procedure proce_repeat () function can have parameters or no

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

Instance:

Mysql>delimiter $$// defining separators

Mysql>create procedure Proce_loop () function can have parameters or no

Begin

Declare I int default 1; define variables, initialize

Loop_label:loop// define a label, just like a tag

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 type1) in to the stored procedure, the inside of the stored procedure will change the value, the caller will not see

Instance:

Mysql>delimiter $$// defining separators

Mysql>create procedure proce_param_in (in ID int)// ID field is passed with in value , the int type of

Begin

if (ID is null) then// if the ID variable is a null value

Select ' ID is null '  As Id_null;

Else

Set i=i+1;

End if;

Select ID as id_inside; print The value of the ID

end$$

mysql>delimiter;

2) the out stored procedure internally passes the value to the caller, does not accept the external value of the

Instance:

Mysql>delimiter $$// defining separators

Mysql>create procedure proce_param_out (out ID int)// ID field is passed with out value , int type of

Begin

Select ID as id_inside_1; ID initial value is null

If (ID is not NULL) then// if the ID variable is not a null value

Set id=id+1;

Select ID as id_inside_2; print The value of the ID

Else

Select into ID; input to ID

End if;

Select ID as id_inside_3;

end$$

mysql>delimiter;

3) InOut can accept the caller's value, and will return the result of the last change to the caller

Instance:

Mysql>delimiter $$// defining separators

Mysql>create procedure proce_param_inout (inout ID int)// ID field is passed with out value , the int type of

Begin

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

If (ID is not NULL) then// if the ID variable is not a null value

Set id=id+1;

Select ID as id_inside_2; print The value of the ID

Else

Select into ID; input to ID

End if;

Select ID as id_inside_3;

end$$

mysql>delimiter;

This article is from "Happy Learning" blog, please be sure to keep this source http://983865387.blog.51cto.com/9838888/1917421

MySQL Database stored procedures