What is mysql stored procedure

mysql stored procedure: first operate the database language SQL statements need to be compiled first when executed; then executed, and the stored procedure is a set of SQL statements to complete a specific function, which is stored in the database after compilation , the user calls and executes the stored procedure by specifying its name and giving parameters.

##More related free learning recommendations: mysql tutorial(Video)

Introduction to stored procedures

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

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 function on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as a simulation of the object-oriented approach in programming. It allows control over how data is accessed.

Stored procedures usually have the following advantages:

Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements, are highly flexible, and can complete complex judgments and more complex operations.

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 of the stored procedure. And database professionals can modify stored procedures at any time without affecting 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, the stored procedure will execute much faster than batch processing. Because stored procedures are precompiled. When a stored procedure is run for the first time, the query is analyzed and optimized by the optimizer and an execution plan is finally stored in the system table. The batch Transaction-SQL statement must be compiled and optimized every time it is run, and the speed is relatively slower.

Stored procedures can reduce network traffic. For operations on 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 call is transmitted over the network statements, thereby greatly increasing network traffic and reducing network load.

Stored procedures can be fully utilized as a security mechanism. By restricting the permissions for executing a certain stored procedure, the system administrator can limit the access permissions of the corresponding data, avoid unauthorized users from accessing the data, and ensure the security of the data.

About MySQL’s stored procedures

Stored procedures are an important function of database storage, but MySQL did not support stored procedures before 5.0, which made MySQL in the application Big discounts. Fortunately, MySQL 5.0 has finally begun to support stored procedures, which can greatly improve the processing speed of the database and also improve the flexibility of database programming.

MySQL stored procedure creation

(1). Format

MySQL stored procedure creation format:

CREATE PROCEDURE process name ([ Process parameters [,...]]) [Characteristics...] Process body

Here is an example:

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

Note:

Required here Note that in the two sentences DELIMITER // and DELIMITER ;, DELIMITER means delimiter, because MySQL uses ";" as the delimiter by default. If we do not declare a delimiter, the compiler will treat the stored procedure as an SQL statement. Then the compilation process of the stored procedure will report an error, so you must use the DELIMITER keyword to declare the current segment delimiter in advance, so that MySQL will treat ";" as the code in the stored procedure and will not execute these codes. After use, you must use the delimiter. reduction.

The stored procedure may have input, output, input and output parameters as needed. There is an output parameter s, the type is int. If there are multiple parameters, use "," to separate them.

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

(2). Declaration separator

In fact, regarding the declaration separator, the above annotation has been written very clearly. There is no need to say more, but one thing to note is: if it is When using the MySQL Administrator management tool, you can create it directly and no longer need to declare it.

(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:

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

• IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

• OUT 输出参数:该值可在存储过程内部被改变，并可返回

• INOUT 输入输出参数:调用时指定，并且可被改变和返回

代码示例

header('Content-Type:text/html;charset=UTF-8');
$conn = mysql_connect('localhost','root','123456') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);

实例一：无参的存储过程

$sql = "create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end;
";
$res = mysql_query($sql);//创建一个myproce的存储过程
$sql = "call test.myproce();";
$res = mysql_query($sql);//调用myproce的存储过程，则数据库中将增加一条新记录。
var_dump($res);

实例二：传入参数的存储过程

//$sql = "create procedure myproce2(in score int)
//begin
//if score >= 60 then
//select 'pass';
//else
//select 'no';
//end if;
//end;
//";
//mysql_query($sql);//创建一个myproce2的存储过程
//$sql = "call test.myproce2(90);";
//$reslut = mysql_query($sql);//调用myproce2的存储过程,看不到效果，可以在cmd下看到结果。
//$array = mysql_fetch_array($reslut);
//var_dump($array);

The above is the detailed content of What is mysql stored procedure. For more information, please follow other related articles on the PHP Chinese website!