Mysql 简明教程

MySQL - Stored Procedure

The MySQL Stored Procedure

MySQL stored procedure 是预先编译的一组 SQL 语句，可以使用它来随时重新执行。存储过程可用于执行不同的数据库操作，例如，插入、更新或删除数据。

A MySQL stored procedure is a group of pre-compiled SQL statements that can be reused anytime. Stored procedures can be used to perform different database operations such as such as inserting, updating, or deleting data.

Syntax

在 MySQL 中创建存储过程的基本语法如下 −

The basic syntax to create a stored procedure in MySQL is as follows −

DELIMITER //
CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name parameter_datatype)
BEGIN
    -- SQL statements to be executed
END //
DELIMITER;

其中，

Where,

  1. The CREATE PROCEDURE statement is used to create the procedure.

  2. The SQL statements that need to be executed are placed between the BEGIN and END keywords.

Creating a Procedure

我们可以使用以下步骤在 MySQL 中创建存储过程 −

We can use the following steps to create a stored procedure in MySQL −

  1. Choose a name for the procedure.

  2. Write the SQL query of the procedure.

  3. Execute the procedure with different parameters.

Example

若要了解存储过程，让我们考虑 CUSTOMERS 表，其中包括客户的个人详细信息，例如他们的 ID、姓名、年龄、地址和薪水，如下所示 −

To understand a stored procedure, let us consider the CUSTOMERS table which contains the personal details of customers including their ID, name, age, address and salary as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在使用 INSERT 语句向该表中插入值，如下所示：

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

获得的 CUSTOMERS 表如下：−

The CUSTOMERS table obtained is as follows −

现在，让我们创建一个名为 'GetCustomerInfo' 的过程，它无需任何参数就能获取 CUSTOMERS 表中年龄大于 25 的所有记录 −

Now, let us create a procedure named 'GetCustomerInfo' without any parameters to retrieve all the records from CUSTOMERS table where age is greater than 25 −

DELIMITER //
CREATE PROCEDURE GetCustomerInfo()
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE > 25;
END //

Verification

若要验证更改，我们使用 CALL 指令执行过程，如下面的查询中所示 −

To verify the changes, we execute the procedure using the CALL command as shown in the query below −

CALL GetCustomerInfo(); //

产生的结果如下 −

The result produced is as follows −

Stored Procedure Parameter Types

存储过程可以有不同类型的参数，用于决定在执行期间将传递哪些值。以下是 SQL 中不同类型的存储过程参数 −

Stored procedures can have different types of parameters, which are used to decide the values that will be passed during execution. Following are the different types of stored procedure parameters in SQL −

  1. Input parameters − These parameters are used to pass values from the calling program or user to the stored procedure.

  2. Output parameters − These parameters are used to return values from the stored procedure to the calling program or user.

  3. Input/Output parameters − These parameters allow a stored procedure to accept input values and return output values.

  4. Table-valued parameters − These parameters are used to pass a table variable as a parameter to a stored procedure.

  5. Default parameters − These parameters are used to specify a default value that will be used if no value is passed for the parameter.

  6. Cursor parameters − These parameters are used to pass a cursor to a stored procedure.

  7. Output XML parameters − These parameters are used to return XML data from a stored procedure.

现在让我们看看 SQL 中一些最常见的存储过程参数类型：

Now, let us take a look at some of the most common types of stored procedure parameters in SQL −

Procedure with IN parameter

IN 参数是默认参数，用来接收调用程序的输入值。该值在执行过程时传递。

The IN parameter is the default parameter and is used to receive the input value from the calling program. The value is passed at the time of procedure execution.

Example

在以下查询中，我们创建了一个存储过程，该过程将客户的 ID 作为输入参数并返回该客户的详细信息。

In the following query, we are creating a stored procedure that takes a customer’s ID as an input parameter and returns that customer’s details.

DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
END //

Verification

为了执行存储过程并为“CustomerAge”参数传递值，我们将使用 CALL 命令，如下所示：

To execute the stored procedure and pass a value for the 'CustomerAge' parameter, we will use the CALL command as shown below −

CALL GetCustomerInfo(23); //

以下是上面代码的输出： -

Following is the output of the above code −

Procedure with OUT parameter

OUT 参数用来将输出值发送到调用程序。为输出参数创建过程时，有必要指定 OUT 关键字。调用时，带有 '@' 前缀的变量用来保存返回的值。

The OUT parameter is used to send the output values to the calling program. It is necessary to specify the OUT keyword to an output parameter when creating the procedure. At the time of calling, a variable, prefixed with '@' is used to hold the returned value.

然后我们可以对变量使用 SELECT 语句来显示过程的输出。

We can then use the SELECT statement on the variable to display the output of the procedure.

Example

在以下查询中，我们创建了一个存储过程，该过程将客户的 ID 作为输入参数并使用输出参数“Cust_Salary”返回该客户的 SALARY。

In the following query, we are creating a stored procedure that takes customer’s ID as an input parameter and returns that customer’s SALARY using an output parameter "Cust_Salary".

DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID INT, OUT Cust_Salary DECIMAL(18,2))
BEGIN
SELECT Salary INTO Cust_Salary FROM CUSTOMERS WHERE ID = CustomerID;
END //
DELIMITER ;

Verification

为了验证所创建过程的运行，我们使用 CALL 命令调用它：

To verify the working of the procedure created, we call it using the CALL command −

CALL GetCustomerSalary(3, @S);

输出值将存储在所传递的“@S”参数中；可以使用 SELECT 语句进一步显示，如下所示：

The output value will be stored in the '@S' parameter passed; which is further displayed using the SELECT statement as shown below −

SELECT @S AS SALARY;

我们得到了如下输出 −

We get the output as shown below −

Procedure with INOUT parameter

INOUT 参数是 IN 和 OUT 参数的组合，用来在单一参数中传递和接收存储过程中的数据。

The INOUT parameter is the combination of IN and OUT parameter and is used to pass and receive data from the stored procedure in a single parameter.

INOUT 关键字用来在存储过程中声明 INOUT 参数。

The INOUT keyword is used to declare an INOUT parameter in a stored procedure.

Example

在以下查询中，我们使用薪水作为 INOUT 参数来输入薪水并保存由过程返回的输出。

In the following query, we are using salary as the INOUT parameter to input the salary and hold the output returned by the procedure.

存储过程使用 IN 参数 cust_id 检索客户的当前工资。然后将工资提高 10%，并将增加后的工资存储在 INOUT 参数 salary 中 −

The stored procedure retrieves the current salary of the customer using the IN parameter cust_id. It then increases the salary by 10% and stores the increased salary in the INOUT parameter salary −

DELIMITER //
CREATE PROCEDURE increaseSalary(IN cust_id INT, INOUT salary DECIMAL(18, 2))
BEGIN
SELECT SALARY INTO salary FROM CUSTOMERS WHERE ID = cust_id;
SET salary = salary * 1.1;
UPDATE CUSTOMERS SET SALARY = @salary WHERE ID = cust_id;
END //
DELIMITER ;

Verification

我们必须在使用以下 SET 命令调用过程之前设置 INOUT 参数的输入值 −

We must set the input value of the INOUT parameter before calling the procedure using the following SET command −

SET @salary = 50000.00;

现在，我们检查通过使用以下语句调用过程是否检索输出值 −

Now, we check whether the output value is retrieved by calling the procedure using the following statement −

CALL increaseSalary(7, @salary);

为了验证结果，我们使用以下 SELECT 语句检索存储在 INOUT 参数中的最终增加工资 −

To verify the result, we retrieve the final increased salary stored in the INOUT parameter using the following SELECT statement −

SELECT @salary as INCREASED_SALARY;

获得的输出如下 −

The output obtained is as follows −

请注意，由于我们将输出存储在一个变量中，这些查询不会对原始表做出任何更改。

Make a note that these queries won’t make any changes to the original table, as we are storing the output in a variable.

Deleting a Stored Procedure

我们可以使用 DROP PROCEDURE 语句删除存储过程。

We can delete a stored procedure using the DROP PROCEDURE statement.

Syntax

SQL 中删除存储过程的基本语法如下：

Following is the basic syntax to delete a stored procedure in SQL −

DROP PROCEDURE [IF EXISTS] procedure_name;

Example

在下例中，我们正在删除名为“GetCustomerSalary”的存储过程：

In the following example, we are deleting a stored procedure named "GetCustomerSalary" −

DROP PROCEDURE IF EXISTS GetCustomerSalary;

Output

执行上面的代码后，我们得到以下输出： -

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.01 sec)

Advantages of Stored Procedure

以下是存储过程的优点 -

Following are the advantages of stored procedures −

  1. Improved Performance − Stored procedures are pre-compiled, so they can be executed faster than typical SQL statements.

  2. Code Reuse − Stored procedures can be called from different client applications allowing for code reusability.

  3. Reduced Network Traffic − Stored procedures are executed on the server returning only the results to the client, thereby reducing network traffic and improving application performance.

  4. Better Security − Stored procedures can be used to enforce security rules and prevent unauthorized access to sensitive data.

  5. Simplified Maintenance − Stored procedures make it easy to maintain code by storing SQL code in a single location.

Drawbacks of Stored Procedure

以下是存储过程的缺点 -

Following are the disadvantages of stored procedures −

  1. Increased Overhead − Stored procedures consume more server resources than simple SQL statements when used frequently or for complex operations.

  2. Limited Portability − Stored procedures cannot be easily moved from one database management system (DBMS) to another since they are often specific to a particular DBMS.

  3. Debugging Challenges − Stored procedures are hard to debug when multiple layers of code are involved.

  4. Security Risks − Stored procedures pose a security risk if they are used to access sensitive data.

Stored Procedure Using Client Program

我们还可以使用客户端程序执行存储过程。

We can also perform Stored Procedure Using Client Program.

Syntax

Example

以下是这些程序 −

Following are the programs −