Basic MySQL tutorials-stored procedures-Basic mysql tutorials

Basic MySQL tutorials-stored procedures-Basic mysql tutorials

Note:

1. When using phpMyAdmin to create a stored procedure, change the delimiter '//'.

 

2. You can only create a stored procedure in the database management tool, but cannot dynamically create a stored procedure using php. php can only call the stored procedure. parameters must be added after the connection.

 

Stored Procedure

A stored procedure is a collection of one or more MySQL statements for future use. It can be considered as a batch, although their role is not limited to batch processing.

In my opinion, stored procedures are a set of business logic and processes. You can create tables, update data, and delete tables in stored procedures.

Why use stored procedures?

A simple Stored Procedure

create procedure porcedureName () begin     select name from user; 
end;  

The stored procedure is created using create procedure. The business logic and SQL are written between begin and end. Call porcedureName (); can be used in mysql to call the process.

-- Call porcedureName ();

 

The stored procedure has no parameters, but the user name of the user table is queried during the call. The call result is as follows:

Name

Admin

Admin1

Admin2

Admin3

 

 

 

 

 

Delete stored procedure

Drop procedure if exists porcedureName; -- no parentheses ()

 

Stored procedure using parameters

create procedure procedureName(     out min decimal(8,2),     out avg decimal(8,2),     out max decimal(8,2) ) BEGIN     select MIN(price) INTO min from order;     select AVG(price) into avg from order;     select MAX(price) into max from order; END;  

 

This process uses three parameters to obtain the minimum, average, and maximum prices of the Order table. Each parameter must have a specified class

Type. Here, the decimal value (8, 2) is used. The keyword OUT indicates that the parameter is used to pass OUT from the stored procedure.

A value (returned to the caller)

MySQL supports IN (passed to the stored procedure), OUT (transferred from the stored procedure, as used here), and INOUT (for the stored procedure incoming and outgoing) parameters. The Stored Procedure Code is located in the BEGIN and END statements. As previously seen, they are a series of SELECT statements used to retrieve values and save them to corresponding variables (by specifying the INTO keyword)

To call the modified stored procedure, you must specify three variable names, as shown in the following figure: (all MySQL variables must start .)

-- Because the process specifies three parameters, the call must match the call procedureName (@ min, @ avg, @ max) parameter );

The call does not have any output, but only assigns the call result to the variable passed in during the call (@ min, @ avg, @ max ). Then you can call to display the variable value.

select @min, @avg, @max; 

 

The result is as follows:

@ Min	@ Avg	@ Max
42.00	601.00	2222.00

 

 

 

 

 

Enter a user ID using the in parameter and return the total price of all orders of the user.

create procedure getTotalById (     in userId int,     out total decimal(8,2) ) BEGIN     select SUM(r.price) from order r     where r.u_id = userId     into total; END;  

 

Call Stored Procedure

call getTotalById(1, @total); select @total;  

The total price of all orders of the user is returned.

In a complex process, you can obtain all the order prices of the user based on the user ID, and dynamically choose whether to add taxes. The Code is designed as follows:

Create procedure getTotalByUser2 (in userId int, in falg boolean, -- whether to add a tax mark out total decimal () begin DECLARE tmptotal DECIMAL (); DECLARE taxrate int DEFAULT 6; -- select SUM (r. price) from order r where r. u_id = userId into tmptotal; if flag then select tmptotal + (tmptotal/1000 * taxrate) into tmptotal; end if; select tmptotal into total; END;

 

In this process, three parameters are input: User ID, whether to add tax and the total returned price. Within the process, two local variables tmptotal and taxrate are defined, and the query results are assigned to temporary variables, determine whether to add tax. Finally, the local variable value is assigned to the output parameter.

Call getTotalByUser2 (1, false, @ total); -- do not add tax call getTotalByUser2 (1, true, @ total); -- add tax select @ total;