Mysql 简明教程

MySQL - Stored Procedure

The MySQL Stored Procedure

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

Syntax

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

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

其中，

  1. CREATE PROCEDURE 语句用于创建过程。

  2. 需要执行的 SQL 语句放在 BEGIN 和 END 关键字之间。

Creating a Procedure

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

  1. 为过程选择一个名称。

  2. 写下过程的 SQL 查询。

  3. 使用不同的参数执行过程。

Example

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

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 语句向该表中插入值，如下所示：

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 表如下：−

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

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

Verification

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

CALL GetCustomerInfo(); //

产生的结果如下 −

Stored Procedure Parameter Types

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

  1. Input parameters − 这些参数用来传递调用程序或用户的值到存储过程。

  2. Output parameters − 这些参数用来从存储过程返回值到调用程序或用户。

  3. Input/Output parameters − 这些参数允许存储过程接受输入值并返回输出值。

  4. Table-valued parameters − 这些参数用来将表变量作为参数传递到存储过程。

  5. Default parameters − 这些参数用来指定如果参数没有传递值，将使用该默认值。

  6. Cursor parameters − 这些参数用来将游标传递到存储过程。

  7. Output XML parameters − 这些参数用来从存储过程返回 XML 数据。

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

Procedure with IN parameter

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

Example

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

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

Verification

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

CALL GetCustomerInfo(23); //

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

Procedure with OUT parameter

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

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

Example

在以下查询中，我们创建了一个存储过程，该过程将客户的 ID 作为输入参数并使用输出参数“Cust_Salary”返回该客户的 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 命令调用它：

CALL GetCustomerSalary(3, @S);

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

SELECT @S AS SALARY;

我们得到了如下输出 −

Procedure with INOUT parameter

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

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

Example

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

存储过程使用 IN 参数 cust_id 检索客户的当前工资。然后将工资提高 10%，并将增加后的工资存储在 INOUT 参数 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 参数的输入值 −

SET @salary = 50000.00;

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

CALL increaseSalary(7, @salary);

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

SELECT @salary as INCREASED_SALARY;

获得的输出如下 −

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

Deleting a Stored Procedure

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

Syntax

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

DROP PROCEDURE [IF EXISTS] procedure_name;

Example

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

DROP PROCEDURE IF EXISTS GetCustomerSalary;

Output

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

Query OK, 0 rows affected (0.01 sec)

Advantages of Stored Procedure

以下是存储过程的优点 -

  1. 存储过程是预编译的，因此执行速度比典型的 SQL 语句更快。

  2. 存储过程可以从不同的客户端应用程序调用，从而允许代码重用。

  3. 存储过程在服务器上执行，仅将结果返回给客户端，从而减少网络流量并提高应用程序性能。

  4. 存储过程可用于强制执行安全规则并防止对敏感数据的未经授权的访问。

  5. 存储过程通过将 SQL 代码存储在单个位置来简化代码维护。

Drawbacks of Stored Procedure

以下是存储过程的缺点 -

  1. 存储过程比简单的 SQL 语句消耗更多的服务器资源，当频繁使用或进行复杂操作时。

  2. 存储过程通常特定于某些 DBMS，因此不能轻易地从一个 DBMS 移动到另一个 DBMS。

  3. 当涉及多个代码层时，存储过程很难调试。

  4. 如果存储过程用于访问敏感数据，则会造成安全风险。

Stored Procedure Using Client Program

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

Syntax

Example

以下是这些程序 −