stored procedures and storage functions for MySQL optimization topics

Turn from the Internet.

When a large system is established, it is found that a lot of SQL operations are overlapping, the individual calculations are the same, for example: In the business system, the calculation of a work order. When this happens, we use the stored procedure as a great optimization. So, what are stored procedures and stored functions?

I. Introduction to the MySQL storage process (technical text):

A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. stored procedures are useful when you want to perform the same functions on different applications or platforms, or encapsulate specific functionality. stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed. stored procedures often have the following advantages:

1) The stored procedure can achieve a faster execution speed.

If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. when you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.

Experience: Compile and optimize, fast!

2) Stored procedures allow standard components to be programmed.

After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.

Experience: encapsulation and abstraction, simple invocation

3) Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.

Experience: Powerful, logical and powerful

4) Stored procedures can be used as a security mechanism to make full use of them.

The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

Experience: Limitations and security

5) Stored procedures can reduce network traffic.

For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, This greatly increases network traffic and reduces network load.

Experience: Reduce network traffic (encapsulation is good)

Second, what is the storage function (custom function)? ：

Encapsulates a piece of SQL code that accomplishes a specific function and must return a result. The rest of the features are basically the same as stored procedures.

Three, the difference between storage function and stored procedure (technical article):

1) The stored function has only one return value, and the stored procedure cannot have a return value. That means you can use return. ( a function can return a return value or a Table object and never return a result set.)

2) The function can have only input parameters , and cannot takein, and the stored procedure may have more than one in,out,inout parameter.

3) Statements in stored procedures are more powerful, and stored procedures can implement complex business logic , and functions have many limitations, such as the inability to use statements such as insert,update,delete,create in functions The storage function only completes the work of the query, accepts input parameters and returns a result, that is, the function implementation of the function is relatively strong. For example: Duration calculation, price calculation.

4) stored procedures can call storage functions. However, a function cannot call a stored procedure.

5) stored procedures are generally performed as a separate part (call invocation). a function can be called as part of a query statement.

Iv. MySQL creates one of the simplest stored procedures (technical text):

"Pr_add" is a simple MySQL stored procedure that has two input parameters of type int "A", "B", and returns the and of the two parameters.

V. Characteristics of the MySQL stored procedure (technical text):

The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name ()

(

[In|out|inout] Parameter datatype

)

Begin

MySQL statement;

End

MySQL Stored Procedure parameters If you do not explicitly specify "in", "Out", and "InOut", the default is "in". in practice, we do not explicitly specify parameters that are "in".

1 MySQL Stored Procedure name "()" is required, even if there is not a parameter, also need "()"

2 MySQL Stored procedure parameter, cannot add "@" before parameter name, for example: "@a int". The following create stored procedure syntax is wrong in MySQL (correct in SQL Server). The variables in the MySQL stored procedure do not need to be "@" before the variable name, although the MySQL client user variable should be added "@".

CREATE PROCEDURE Pr_add ( @a int,//error B int//correct)

3 The parameters of a MySQL stored procedure cannot specify a default value.

4 The MySQL stored procedure does not need to precede the procedure body with "as". The SQL Server stored procedure must be added with the "as" keyword.

CREATE PROCEDURE Pr_add (a int, b int) as-error, MySQL does not require "as" begin MySQL statement ...; End

5 if the MySQL stored procedure contains more than one MySQL statement, the BEGIN END keyword is required.

CREATE PROCEDURE Pr_add (a int, b int) begin MYSQL Statement 1 ...; MySQL statement 2 ...; End

6 at the end of each statement in the MySQL stored procedure, add a semicolon ";"

... declare c int; If a is null and then set a = 0; End If; ... end;

7 the "return" keyword cannot be used in a MySQL stored procedure.

Set C = A + b;select c as sum; /* Return c;-cannot be used in MySQL stored procedures. return can only appear in the function. */end;

8 Call the MySQL stored procedure, you need to add "()" After the procedure name, even if there is not a parameter, also need "()", call out and inout parameter format is @arguments_name form.

Call Pr_no_param ();

9 because the MySQL stored procedure parameter does not have a default value, you cannot omit the parameter when you call the MySQL stored procedure. can be substituted with null.

Call Pr_add (NULL);

1, the actual combat premise (technical text):

Need MySQL 5 and above, I use the MySQL client navicat Premium, posted code is I compile without errors. If the reader does not have the client installed or the error is reported on your computer, this is delimiter//and delimiter, and the delimiter is the meaning of the separator, because MySQL defaults to ";" As a delimiter, if we do not declare the separator, then the compiler will treat the stored procedure as an SQL statement, the process of compiling the stored procedure will be error-free, so you have to use the delimiter keyword in advance to declare the current segment delimiter, so that MySQL will ";" As the code in the stored procedure, the code is not executed and the delimiter is restored after it is exhausted

2, variable

Use declare to declare, default assignment defaults, set assignment

Java Code Replication Code

DECLARE counter INT DEFAULT 0; SET counter = counter+1;

3, condition judgment

If then, ELSEIF, ELSE, END if

DROP PROCEDURE IF EXISTS discounted_price; CREATE PROCEDURE Discounted_price (Normal_price NUMERIC (8, 2), Out Discount_price NUMERIC (8, 2)) BEGIN IF (normal_price ; Then SET discount_price = Normal_price * 0.8; ELSEIF (Normal_price > normal_price<=500) then SET discount_price = normal_price * 0.9; ELSE SET discount_price = Normal_price; END IF; Select Discount_price as Price; End;call Discounted_price (600.0, @discount);//out parameter can be called with an @ arbitrary string

4, loop

Loop, END Loop

drop procedure if exists simple_loop;create procedure Simple_loop (out counter int) BEGIN DECLARE temp int default 0; Set counter=0; My_loop:loop set counter=counter+1; Set temp=temp+1; If counter=10 then leave My_loop; End If; End Loop My_loop; Select Temp as Result;end;call simple_loop (@a);

5. While do, END while

DROP PROCEDURE IF EXISTS simple_while; CREATE PROCEDURE Simple_while (out counter int) BEGIN DECLARE temp int default 0; SET counter = 0; While counter! = Ten do SET counter =counter+1; Set TEMP =temp+1; END while; Select Counter as Temp1; END; Call Simple_while (@a);

6, REPEAT, untill

Drop PROCEDURE if exists simple_repeat;create PROCEDURE simple_repeat (out counter int) BEGIN set counter=0; REPEAT set counter=counter+1; Until counter=10 end repeat; Select Counter as Temp;end;call simple_repeat (@q);

7, storage method

The difference between a storage method and a stored procedure

1, the parameter list of the stored method only allows parameters of the in type, and it is not necessary or allowed to specify the IN keyword

2, the storage method returns a single value, the type of the value defined in the head of the stored method

3, the storage method can be called inside the SQL statement

4, the storage method cannot return a result set

Grammar:

Create function ([function parameter [,....]]) Returns return type

Begin

If

Return (the returned data)

Else

Return (the returned data)

End If;

End

A simple instance of a storage function

Drop function if exists purchase_and_redeem_function;

CREATE function purchase_and_redeem_function (date int)

Returns varchar (80)

BEGIN

Return (SELECT tbalance from User_purchase_and_redeem WHERE report_date=date); This SQL statement is written according to its own database table.

END;

Select Purchase_and_redeem_function (20140501);//This is the call store function

8, Trigger

Trigger triggers when a database table is modified by a DML statement such as INSERT, UPDATE, or delete

Typical application scenarios for triggers are important business logic, improved performance, monitoring table modifications, etc.

Triggers can be triggered before or after DML statements are executed

DROP TRIGGER Sales_trigger; CREATE TRIGGER sales_triggerbefore INSERT on Salesfor each rowbeginif new.sale_value > Thenset new.free_shipping = ' Y '; Elseset new.free_shipping = ' N '; END if;if new.sale_value > Thenset new.discount = new.sale_value *. 15; Elseset new.discount = 0; END IF; END;

stored procedures and storage functions for MySQL optimization topics