mysql存储过程教程_MySQL存储过程教程

最新推荐文章于 2024-06-22 21:12:31 发布

danpu0978

最新推荐文章于 2024-06-22 21:12:31 发布

阅读量167

点赞数

文章标签：数据库 mysql java python sql

原文链接：https://www.javacodegeeks.com/2018/06/mysql-stored-procedure-tutorial.html

版权

mysql存储过程教程

1.简介

在企业级应用程序中，始终需要定期对数据库执行某些特定的任务集，例如数据库清理，处理工资单，创建具有多个默认条目以及更多其他内容的新实体。这样的任务可能涉及对每个任务执行多个查询。如果有一种方法可以将这些任务分组为一个任务并执行，则可以简化此过程。 MySQL存储过程正是用于此目的。 MySQL存储过程是一段预编译SQL代码，可以执行这些代码以运行多个任务以及执行某些逻辑操作。 MySQL过程类似于通用编程语言中的函数。本文介绍了使用实际用例场景逐步创建MySQL过程的方法。

2. MySQL过程入门

首先，让我们创建一个简单的零动作过程。零操作过程是不会真正执行任何数据库任务但会向我们提供该过程成功执行的反馈的过程。在开始使用代码之前，这里是进行本教程的先决条件。

MySQL服务器启动并运行
MySQL Shell运行

启动MySQL Shell之后，选择教程的数据库。如果您没有数据库，请使用以下命令创建并选择数据库。

mysql> create schema procedure_training;
mysql> use procedure_training;

这些命令将创建架构并选择它。现在，让我们了解创建第一个过程之前没有变量的简单过程创建的语法。

mysql> DELIMITER //
mysql> CREATE PROCEDURE PROCEDURE_NAME()
    -> begin
    -> PROCEDURE_BODY
    -> end
    -> //

过程在其语句中包含换行符和分号。因此，创建过程之前的第一步是更改定界符，该定界符决定语句何时实际结束。因此，编写代码DELIMITER //的目的是表明该语句仅在MySQL提示符遇到//符号时结束。
要创建一个过程，请从语句CREATE PROCEDURE PROCEDURE_NAME() ，其中PROCEDURE_NAME可以替换为您选择的名称。下BEGIN语句包含关键字BEGIN 。它指示该过程的主体从该点开始。下一部分可以是单个语句，也可以是多个语句，以替换上面的代码中的PROCEDURE_BODY 。最后，一旦过程主体完成，请输入关键字END以表明过程主体在此结束。

现在我们对语法有了基本的了解，让我们使用下面的代码创建第一个过程。

mysql> DELIMITER //
mysql> CREATE PROCEDURE myfirstprocedure()
    -> begin
    -> select 'This is my first procedure';
    -> end
    -> //
Query OK, 0 rows affected (0.06 sec)

在上面的代码中，我们创建了一个名为myfirstprocedure的过程。为了简化过程，我们在正文中仅添加了一条语句来显示注释。只要按预期编写了整个代码，您将看到如上所示的输出。

成功执行查询后，您可以使用下面显示的简单命令调用该过程。

mysql> delimiter ;
mysql> call myfirstprocedure();
+----------------------------+
| This is my first procedure |
+----------------------------+
| This is my first procedure |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

可以看出，我首先将分隔符改回分号，以避免编写//结束语句的麻烦。更改后，执行call语句以执行该过程。由于正文仅包含注释，因此如上所示显示。现在让我们创建一个稍微复杂的过程。

3.创建一个过程以插入记录

插入过程用于在创建特定记录时插入一堆预定义的条目。一个完美的用例是在创建用户时为该用户分配很少的默认访问权限。为了进行这样的活动，我们需要一个工作表。使用以下提供的create语句创建表：

CREATE TABLE `procedure_training`.`user_roles` (
  `id` INT NOT NULL,
  `rolename` VARCHAR(45) NULL,
  `userid` INT NULL,
  PRIMARY KEY (`id`));

下一步是创建一个过程，为userid值1插入一堆角色。这可以使用以下过程完成。

DELIMITER //
 CREATE PROCEDURE insertdefaultroles()
   BEGIN
   insert into user_roles values (1, 'admin',1), (2, 'employee',1),(3,'learner',1);
   END //
 DELIMITER ;

上述过程在执行时在表中插入3条记录。相同的输出如下所示。

mysql> call insertdefaultroles();

Query OK, 3 rows affected (0.14 sec)

mysql> select * from user_roles;
+----+----------+--------+
| id | rolename | userid |
+----+----------+--------+
|  1 | admin    |      1 |
|  2 | employee |      1 |
|  3 | learner  |      1 |
+----+----------+--------+
3 rows in set (0.01 sec)

4.删除程序

当您需要重新创建过程（如表）时，需要先删除过程，然后再重新创建它。在像Oracle DB这样的备用数据库中，支持使用CREATE OR REPLACE而不是普通的CREATE来创建或替换过程。但是，在MySQL中，必须先删除该过程，然后才能重新创建它。随着教程的进行，我们将需要频繁删除该过程。删除过程的查询类似于MySQL表的DROP查询。下面显示了删除过程insertdefaultroles的查询。

mysql> DROP PROCEDURE insertdefaultroles;

5.程序变量

正如您在上面的代码中已经注意到的那样，我们提供了静态用户ID和主键值。使用一次后，该过程将无法使用。因此，我们需要一种使这些值自动化的方法。让我们首先从主键的值开始。

5.1在过程中添加变量

使用变量的第一步是声明它。声明之后，我们需要获取表中当前的主键变量的最大值。下面的代码完全一样。

DROP PROCEDURE insertdefaultroles; 
DELIMITER //
 CREATE PROCEDURE insertdefaultroles()
   BEGIN
   DECLARE pkvalue int;
   select max(id) into pkvalue from user_roles;
   insert into user_roles values (pkvalue+1, 'admin',1), (pkvalue+2, 'employee',1),(pkvalue+3,'learner',1);
   END //
 DELIMITER;

该代码包含一个删除过程语句，用于在重新创建该过程之前删除该过程。如果不这样做，则会遇到一个错误，指出该过程已存在。执行完代码后，再次调用该过程以查看结果。结果将是随着主键值的增加，新条目已添加到表中。

mysql> call insertdefaultroles();
Query OK, 3 rows affected (0.07 sec)
mysql> select * from user_roles;
+----+----------+--------+
| id | rolename | userid |
+----+----------+--------+
|  1 | admin    |      1 |
|  2 | employee |      1 |
|  3 | learner  |      1 |
|  4 | admin    |      1 |
|  5 | employee |      1 |
|  6 | learner  |      1 |
+----+----------+--------+
6 rows in set (0.00 sec)

5.2添加过程参数

尽管解决了动态主键的麻烦，但用户ID仍然是静态的。不能像主键一样从数据库中检索用户ID。因此，需要以某种方式将用户标识传递给过程。程序允许为此使用功能参数。

DROP PROCEDURE insertdefaultroles; 
DELIMITER //
 CREATE PROCEDURE insertdefaultroles(uid int)
   BEGIN
   DECLARE pkvalue int ;
   select max(id) into pkvalue from user_roles;
   insert into user_roles values (pkvalue+1, 'admin',uid), (pkvalue+2, 'employee',uid),(pkvalue+3,'learner',uid);
   END //
DELIMITER ;

上面的代码声明了一个这样的过程。此过程将用户ID放入变量uid 。该变量可用于替换查询中用户ID的值。执行该过程，并确保执行成功。

要运行该过程，请像在常规函数中传递用户ID一样传递用户ID。下面的代码也显示了执行和执行结果。

mysql> call insertdefaultroles(2);
Query OK, 3 rows affected (0.10 sec)

mysql> select * from user_roles;
+----+----------+--------+
| id | rolename | userid |
+----+----------+--------+
|  1 | admin    |      1 |
|  2 | employee |      1 |
|  3 | learner  |      1 |
|  4 | admin    |      1 |
|  5 | employee |      1 |
|  6 | learner  |      1 |
|  7 | admin    |      2 |
|  8 | employee |      2 |
|  9 | learner  |      2 |
+----+----------+--------+
9 rows in set (0.01 sec)

可以看出，在过程参数中传递了新的用户ID 2，并且在查询中已将其替换。现在，过程调用将为用户标识2插入默认角色。

6.从过程中返回特定值

到目前为止，我们要么返回过程执行的结果，要么通过执行查询返回评论。现在需要执行某种计算并返回评估值。例如，让我们计算具有特定角色的用户数量。为了计算用户数，您需要按特定角色搜索用户并获取记录数。为此，请执行以下查询。

$ mysql> select count(*) from user_roles where rolename = 'admin';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

如上所示，查询输出用户数。用户的数量可以作为过程中的值返回。为此，您需要以与注释类似的方式选择值。下面的代码为您显示了这种实现。

DROP PROCEDURE countusers; 
DELIMITER //
 CREATE PROCEDURE countusers(role varchar(45))
   BEGIN
   DECLARE usercount int ;
   select count(*) into usercount from user_roles where rolename = role;
   select usercount;
   END //
 DELIMITER ;

上面的代码创建了一个过程，该过程将count的值转换为变量usercount并选择它以返回相关值。可以注意到，第一个查询的输出未显示，因为它被随后的查询遮盖了。在过程中，输出始终取决于最终执行的查询，除非在两次查询之间有意选择了变量。

7.程序中的条件流

到目前为止，我们已经看到了过程的简单用例。这些用例涉及的查询并不十分复杂。在本节中，我们将继续有条件地分支流程。这是程序真正应用的地方。条件流使您可以根据查询或所传递的变量来决定需要执行哪些操作。在编写包含案例的过程之前，让我们了解其语法。下面的代码片段显示了包含案例的过程的过程语法。

CREATE PROCEDURE procedure_name
BEGIN
CASE case_value
    WHEN expression THEN queries_to_be_executed
    [WHEN expression THEN queries_to_be_executed] ...
    [WHEN expression THEN queries_to_be_executed] ...
    [ELSE queries_to_be_executed]
END CASE
END

在以上语法中，方括号中提到的项目是可选的。在上述过程中， WHEN指示if条件的开始。关键字CASE表示条件分支的开始。 expression语句可以是任何变量或要求值的表达式。如果表达式的计算结果为true，则THEN用于指示主体的开始。

其余语句是可选的。它们用于添加更多条件（如果有条件）和其他条件（以防提供的表达式均未被评估为真）。现在，继续编写实际过程。我们将使用简单的body语句很好地理解条件分支。通常，您可以在主体中使用任何SQL查询语句。

CREATE PROCEDURE firstcaseprocedure(variable varchar(10))
BEGIN
CASE variable
    WHEN 'first' THEN select 'First condition executed';
    WHEN 'second'  THEN select 'Second condition executed';
    WHEN 'third'  THEN select 'Third condition executed';
    ELSE select 'Else condition executed';
END CASE;
END;

在上面的代码中，请仔细检查放置的分号和单引号。如果您遇到任何错误，它们都是导致错误的主要原因。一旦创建了过程，就为每个测试用例测试过程执行。该过程的测试如下所示。

mysql> call firstcaseprocedure('first');
+--------------------------+
| First condition executed |
+--------------------------+
| First condition executed |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call firstcaseprocedure('second');
+---------------------------+
| Second condition executed |
+---------------------------+
| Second condition executed |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call firstcaseprocedure('third');
+--------------------------+
| Third condition executed |
+--------------------------+
| Third condition executed |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call firstcaseprocedure('xyz');
+-------------------------+
| Else condition executed |
+-------------------------+
| Else condition executed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

从上面的执行结果可以看出，获得的输出是有条件的，基于提供的参数。尽管我们提供了参数，但是也可以在过程中定义参数。例如，考虑一个更新学生是否通过或失败的程序。在这样的程序中，该程序动态地获取分数并检查学生是否通过或失败，并相应地更新记录。

下面显示了具有类似实现的示例过程。该表已假定存在，因为该示例仅是为了阐明一个用例。

CREATE PROCEDURE passorfail(sid int)
BEGIN
DECLARE marks int;
select student_marks into marks from student_result where student_id = sid;
CASE 
    WHEN marks<35 THEN select 'Fail';
    WHEN marks<50 THEN select 'C Grade';
    WHEN marks<70 THEN select 'B Grade';
    ELSE select 'A Grade';
END CASE;
END;

在此，请注意大小写之间的差异。 case变量在语句之间不再通用。每当我们需要使用比较或逻辑运算符时，就需要按照上面显示的方式使用它。条件marks<35也可以由涉及不同变量的任何相关条件代替。

在上面的代码中，使用CASE语句进行流控制。但是，通常我们更喜欢使用IF…。 ELSE简化了使用表达式的过程。与CASE不同，在CASE ，您只能评估条件中的一个特定变量，而IF…ELSE则允许您检查具有不同组合的多个变量。让我们了解过程中IF…ELSE语句的语法。

IF search_condition THEN query_statements
    [ELSEIF search_expression THEN query_statements] ...
    [ELSE statement_list]
END IF

上面的语法可以更新，并可以在任何过程主体中使用。方括号中的语句是可选的，如果不需要，可以避免。如果search_condition评估为true，则query_statements部分可以包含要执行的单个或多个SQL条件。上面的CASE…WHEN语句在下面的代码中转换为IF…ELSE。

CREATE PROCEDURE passorfail(sid int)
BEGIN
DECLARE marks int;
select student_marks into marks from student_result where student_id = sid;
IF marks<35 THEN select 'Fail';
ELSEIF marks<50 THEN select 'C Grade';
ELSEIF marks<70 THEN select 'B Grade';
ELSE select 'A Grade';
END IF
END;

观察它引入的简单性。即使是外行，也更容易理解条件。

8.遍历记录

请注意，在上述学生标记的使用案例中，我们所做的只是基于提供的ID打印学生的及格分数。如前所述的过程用于执行大型预定义任务。因此，如果可以仅通过调用程序来更新每个学生的成绩，那将更有用。为此，让我们创建一个表并向其中添加一些细节。执行以下查询以进行相同的操作。

mysql> create table student_marks (student_id int, marks int, grade varchar(5));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into student_marks values (1, 50,null),(2,60,null),(3,34,null),(4,70,null),(5,43,null);
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student_marks;
+------------+-------+-------+
| student_id | marks | grade |
+------------+-------+-------+
|          1 |    50 | NULL  |
|          2 |    60 | NULL  |
|          3 |    34 | NULL  |
|          4 |    70 | NULL  |
|          5 |    43 | NULL  |
+------------+-------+-------+
5 rows in set (0.01 sec)

现在，我们将创建一个检查每个学生成绩并为每个学生分配分数的过程。在继续之前，让我们了解语法和术语。该代码段显示了包含循环的过程的语法。

CREATE PROCEDURE curdemo()
BEGIN
  //Declaration of variables
  //Open cursors

  read_loop: LOOP
    //FETCH Cursors
    //Loop through cursors
    IF done THEN
      LEAVE read_loop;
    END IF;
    //BODY
    END IF;
  END LOOP;
  //Close cursors
END;

在以上语法中，要注意的术语是Cursor 。游标类似于指向数据库中行的指针。您可以将结果集分配给游标。分配后，游标可以帮助您逐一遍历记录。 read_loop标签用于从特定标签继续迭代。因此，当您编写read_loop:LOOP ，代码将不断旋转，直到遇到语句LEAVE read_loop 。现在我们已经了解了语法，让我们编写第一个过程并在其中进行迭代。

CREATE PROCEDURE iterdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE sid int;
  DECLARE mks int;
  DECLARE cur1 CURSOR FOR select student_id,marks from student_mks;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  
  read_loop: LOOP
    FETCH cur1 INTO sid, mks;
    IF  done THEN LEAVE read_loop; END IF;
    IF mks<35 THEN update student_marks set  grade ='Fail' where student_id = sid;
	ELSE update student_marks set  grade ='Pass' where student_id = sid; 
	END IF;
  END LOOP;
  CLOSE cur1;
END;

上面的过程将根据评分分数来分配学生的成绩状态-学生是否通过了考试。该过程围绕表student_marks的记录执行循环。光标基本上指向所选的记录。从上面的代码中可以看出，光标cur1指向student_marks表的所有记录。在执行循环read_loop时， cur1游标用于逐行获取数据。语句FETCH cur1 into sid,mks将当前行的数据放入变量中，并将光标移至下一行。因此，正在为主体中提供的if条件处理每一行。 if条件检查分数并确定学生是否通过了考试。因此，表中的行将被更新。调用该过程时，您可以检查结果是否类似于以下所示：

mysql> delimiter ;
mysql> call iterdemo();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from student_marks;
+------------+-------+-------+
| student_id | marks | grade |
+------------+-------+-------+
|          1 |    50 | Pass  |
|          2 |    60 | Pass  |
|          3 |    34 | Fail  |
|          4 |    70 | Pass  |
|          5 |    43 | Pass  |
+------------+-------+-------+
5 rows in set (0.00 sec)

可以看出，只有分数低于35的学生的成绩才会更新为不及格。我们还可以使用IF ELSE阶梯来更新学生的确切成绩。下面的代码执行相同的操作。

mysql> drop procedure iterdemo;
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER //
mysql> 
mysql> CREATE PROCEDURE iterdemo()
    -> BEGIN
    ->   DECLARE done INT DEFAULT FALSE;
    ->   DECLARE sid int;
    ->   DECLARE mks int;
    ->   DECLARE cur1 CURSOR FOR select student_id,marks from student_marks;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->   OPEN cur1;
    ->   read_loop: LOOP
    ->     FETCH cur1 INTO sid, mks;
    ->     IF  done THEN LEAVE read_loop; END IF;
    ->     IF mks ELSEIF mks ELSEIF mks ELSE update student_marks set  grade ='A' where student_id = sid; 
    -> END IF;
    ->   END LOOP;
    ->   CLOSE cur1;
    -> END;//
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call iterdemo;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from student_marks;
+------------+-------+-------+
| student_id | marks | grade |
+------------+-------+-------+
|          1 |    50 | B     |
|          2 |    60 | B     |
|          3 |    34 | Fail  |
|          4 |    70 | A     |
|          5 |    43 | C     |
+------------+-------+-------+
5 rows in set (0.00 sec)

上面的代码处理多个IF条件的标记值，并相应地更新等级。这正是过程可以简单地帮助完成主要任务的方式。

9.结论

总而言之，过程是要执行的一组规则和查询，以便以简单的方式执行数据库密集型任务。使用这些过程，您可以执行常规SQL查询，有条件地分支查询流，并遍历记录列表，并在必要时进行批量更新。

翻译自: https://www.javacodegeeks.com/2018/06/mysql-stored-procedure-tutorial.html

mysql存储过程教程

danpu0978

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql存储过程教程_MySQL存储过程教程

mysql存储过程教程1.简介在企业级应用程序中，始终需要定期对数据库执行某些特定的任务集，例如数据库清理，处理工资单，创建具有多个默认条目以及更多其他内容的新实体。这样的任务可能涉及对每个任务执行多个查询。如果有一种方法可以将这些任务分组为一个任务并执行，则可以简化此过程。 MySQL存储过程正是用于此目的。 MySQL存储过程是一段预编译SQL代码，可以执行这些代码以运行多个任务...
复制链接

扫一扫