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

Editor to share with you a brief introduction to mysql stored procedures, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql stored procedure: first, the database language SQL statement needs to be compiled when it is executed; then it is executed, and the stored procedure is a set of SQL statements in order to complete a specific function, which is compiled and stored in the database, and the user calls and executes it by specifying the name of the stored procedure and given parameters.

Brief introduction of stored procedure

Our commonly used database language SQL statements need to be compiled and then executed, and a stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in the database in order to complete a specific function. The user calls and executes the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

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 execute the same function on different applications or platforms, or to encapsulate specific functions. The stored procedure in the database can be regarded as a simulation of the object-oriented method in programming. It allows you to control how data is accessed.

Stored procedures typically have the following advantages:

Stored procedures enhance the functionality and flexibility of the SQL language. The stored procedure can be written with flow control statements, which has strong flexibility and can complete complex judgments and more complex operations.

Stored procedures allow standard components to be programming. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, which has no effect on the application source code.

Stored procedures can achieve faster execution speed. If an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

Stored procedures can reduce network traffic. For the operation of the same database object (such as query, modification), if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus greatly increasing the network traffic and reducing the network load.

Stored procedures can be fully utilized as a security mechanism. By restricting the permissions of a stored procedure, the system administrator can restrict the access to the corresponding data, avoid the access of unauthorized users to the data, and ensure the security of the data.

About the stored procedure of MySQL

Stored procedure is an important function of database storage, but MySQL did not support stored procedure before 5. 0, which greatly reduced the application of MySQL. Fortunately, MySQL 5.0 has finally begun to support stored procedures, which can not only greatly improve the processing speed of the database, but also improve the flexibility of database programming.

The creation of MySQL stored procedure

(1)。 Format

Format of MySQL stored procedure creation: CREATE PROCEDURE procedure name ([procedure parameters [,...]]) [features.] Process body

Here's an example:

Mysql > DELIMITER / / mysql > CREATE PROCEDURE proc1 (OUT s int)-> BEGIN- > SELECT COUNT (*) INTO s FROM user;- > END- > / / mysql > DELIMITER

Note:

It should be noted here that DELIMITER / / and DELIMITER; two sentences, DELIMITER means the delimiter, because MySQL defaults to ";". If we do not declare the delimiter, the compiler will treat the stored procedure as a SQL statement, and the compilation process of the stored procedure will report an error, so the current segment delimiter must be declared with the DELIMITER keyword in advance, so MySQL will set the " "as code in a stored procedure, the code will not be executed and the delimiter will be restored when it is used up.

The stored procedure may have input, output, input and output parameters as needed. Here is an output parameter s of type int, which is separated by "," if there are multiple parameters.

The beginning and end of the process body are identified by BEGIN and END.

(2)。 Declare delimiter

In fact, with regard to the declaration separator, the above comments have been written very clearly, there is no need to say more, just a little note is: if you are using MySQL's Administrator management tool, you can directly create, no longer need to declare.

(3)。 Parameters.

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT, in the form of:

CREATE PROCEDURE ([[IN | OUT | INOUT] parameter name data class.])

IN input parameter: indicates that the value of this parameter must be specified when calling the stored procedure. The value that modifies this parameter in the stored procedure cannot be returned and is the default value.

OUT output parameter: this value can be changed inside the stored procedure and can be returned

INOUT input and output parameters: specified when called, and can be changed and returned

Code example

Header ('Content-Type:text/html;charset=UTF-8'); $conn = mysql_connect (' localhost','root','123456') or die ("data connection error!!"); mysql_select_db ('test',$conn)

Example 1: stored procedure without parameters

$sql = "create procedure myproce () beginINSERT INTO user (id, username, sex) VALUES (NULL, 'mysql_query,' 0'); end;"; $res = mysql_query ($sql); / / create a myproce stored procedure $sql = "call test.myproce ();"; $res = mysql_query ($sql); / / call the myproce stored procedure and a new record will be added to the database. Var_dump ($res)

Example 2: stored procedure for incoming parameters

/ / $sql = "create procedure myproce2 (in score int) / / begin//if score > = 60 then//select 'pass';//else//select' no';//end if;//end;//"; / / mysql_query ($sql); / / create a stored procedure of myproce2 / / $sql = "call test.myproce2 (90);"; / / $reslut = mysql_query ($sql); / / call the stored procedure of myproce2, and you can see the result under cmd. / / $array = mysql_fetch_array ($reslut); / / var_dump ($array); all of the above is a brief introduction to the mysql stored procedure. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.