取mysql游标数据库_MySQL数据库实操教程(19)——详解存储过程及游标使用

版权声明

存储过程概述

在开发过程中，我们经常会遇到重复使用某一功能的情况。为此，MySQL引人了存储过程(Stored Procedure)这一技术。所谓存储过程指的就是一条或多条SQL语句的集合。存储过程可以将一些列复杂操作封装成一个代码块，以便重复使用，从而极大地减少数据库开发人的工作量提升开发效率。SQL语句需要先编译然后执行，而存储过程可将为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字通过传参的方式对其进行调用。存储过程可看做是编程的函数，它允许以传参调用的访问方式。

存储过程入门示例

第一步

准备数据，代码如下：

DROP TABLE IF EXISTS student;CREATE TABLE student ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) DEFAULT 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');1

2

3

4

5

6

7

8

9

10

11

12

13

14

创建学生表并插入数据。

第二步

编写存储过程，代码如下：mysql> DELIMITER //mysql> CREATE PROCEDURE procedureHelloWorld(IN sage INT) -> BEGIN -> SELECT * FROM student WHERE age>sage; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;1

2

3

4

5

6

7

在该存储过程中我们期望依据传入的指定年纪查询出符合条件的学生；详解如下：MySQL默认以;为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理从而造成编译过程会报错。所以需要事先用DELIMITER //声明当前段分隔符，让编译器把两个//之间的内容当做存储过程的代码。在编写完存储过程之后利用DELIMITER ;把分隔符还原为默认分隔符;。DELIMITER也可以指定其他符号作为结束符。需要格外注意的是：DELIMITER与要设定的结束符之间一定要有一个空格，否则设定无效。

利用CREATE PROCEDURE 存储过程名称(参数)创建存储过程，即示例代码：CREATE PROCEDURE procedureHelloWorld(IN sage INT)

存储过程的过程体以BEGIN开始以END 结束

图示如下：

第三步

调用存储过程，代码如下：

call procedureHelloWorld(15);1

利用call 存储过程名(参数);调用存储过程，即示例代码：call procedureHelloWorld(15);类似地，可利用DROP PROCEDURE 存储过程名;删除存储过程。

结果如下：

至此，我们完成了存储过程的入门程序。假若您没有完全看懂该代码也无关紧要，毕竟只是让大家对存储过程先有个大概了解；接下来，我们将从基础语法开始全面深入地学习存储过程。

存储过程的参数

MySQL存储过程的定义中可不传参，假若传参则有三种类型：IN,OUT,INOUT,形式如下：CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])1IN表示输入参数，表示该参数的值必须在调用存储过程时指定

OUT表示输出参数，可在存储过程内改变该值并将其返回

INOUT表示输入输出参数，可在调用存储过程时指定该参数并在存储体中改变该值并将其返回

IN参数

示例代码如下：

mysql> DELIMITER //mysql> CREATE PROCEDURE findStudent(IN sage INT) -> BEGIN -> SELECT * FROM student WHERE age=sage; -> END //Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;mysql> call findStudent(16);1

2

3

4

5

6

7

8

9

在此定义存储过程findStudent，该存储过程中有个IN参数，该参数为INT类型叫做sage

图示如下：

通过call findStudent(16);并出入16作为IN参数调用存储过程。

其实，我们还可以在调用存储过程中使用用户变量。

用户变量语法，形式如下：@var_name1

创建用户变量，方式如下：

SET 用户变量=初始值;1

查看用户变量的值，方式如下：SELECT 用户变量;1

当定义用户变量后可方便开发过程的代码编写，只要连接未关闭我们均可直接使用该变量。当然，当连接关闭时所有客户变量将自动释放。

示例如下：

mysql> SET @age=16;Query OK, 0 rows affected (0.00 sec)mysql> call findStudent(@age);1

2

3

4

OUT参数

示例代码如下：mysql> DROP PROCEDURE IF EXISTS countStudent;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE countStudent(OUT total INT) -> BEGIN -> SELECT COUNT(*) INTO total FROM student; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql>mysql> SET @number=0;Query OK, 0 rows affected (0.00 sec)mysql>mysql> CALL countStudent(@number);Query OK, 1 row affected (0.00 sec)mysql> SELECT @number;1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

定义存储过程countStudent，该存储过程中有个OUT参数，该参数为INT类型叫做total。在存储体中将统计的结果利用INTO存入total中。在调用存储过程中将用户变量@number作为参数传入，然后利用SELECT @number参看结果。

图示如下：

INOUT参数

示例代码如下：

mysql> DROP PROCEDURE IF EXISTS searchStudentGender;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE searchStudentGender(INOUT message VARCHAR(50)) -> BEGIN -> SELECT gender INTO message FROM student WHERE sname=message; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql>mysql> SET @info='lili';Query OK, 0 rows affected (0.00 sec)mysql> CALL searchStudentGender(@info);Query OK, 1 row affected (0.00 sec)mysql> SELECT @info;1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

定义存储过程searchStudentGender，该存储过程中有个INOUT参数，该参数为VARCHAR(50)类型叫做message。在该示例中message既当做输入参数又当做输出参数，即输入参数为学生的姓名lili返回的是学生的性别male。在调用存储过程时将初始值为lili的用户变量@info传入存储过程，调用存储过程结束后再次查询@info的值为male。

图示如下：

变量

在编写存储过程中有时需要使用变量保存数据处理过程中的值。这些变量的作用范围为BEGIN…END，语法如下：DECLARE varName dataType [DEFAULT value];1

在该语法中：关键字DECLARE用于定义变量，varNarne为局部变量的名称，dataType为局部变量的类型，可选项DEFAULT value为变量默认值。

在定义变量之后，可使用SET为变量赋值或者修改变量的默认值，语法如下：

SET varName = value；1

变量使用示例1

代码如下：mysql> DELIMITER //mysql> CREATE PROCEDURE varTest(IN number1 INT) -> BEGIN -> -- 声明变量number2 -> DECLARE number2 INT; -> -- 声明变量result -> DECLARE result INT; -> -- 为变量number2赋值 -> SET number2=99; -> -- 为变量result赋值 -> SET result=number1+number2; -> -- 显示result -> SELECT result; -> END //Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;mysql>mysql> -- 调用存储过程mysql> CALL varTest(1);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

图示如下：

变量使用示例2

代码如下：

DROP PROCEDURE IF EXISTS findStudent;DELIMITER //CREATE PROCEDURE findStudent(IN studentID CHAR(6))BEGINDECLARE studentName VARCHAR(50);DECLARE studentGender VARCHAR(50);-- 将查询结果保存至变量studentName和studentGender中SELECT sname,gender INTO studentName,studentGender FROM student WHERE sid=studentID;-- 显示studentName和studentGender中保存的值SELECT studentName,studentGender;END //DELIMITER ;call findStudent('S_1001');1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

图示如下：

流程控

通过之前的学习，我已经对存储过程有了基本的了解。接下来，我们来学习在编写存储过程中非常重要的流程控制。流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块。MySQL中的流程控制语句包括：IF语句、CASE语句、 LOOP 语句、WHILE 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句、WHILE语句。

IF

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句；它与我们在编程语言中学的if、else if、else非常类似；语法格式如下：IF expr_condition THEN statement_list [ELSE expr_condtion THEN statement_list] ... [ELSE statement_list]END IF1

2

3

4

示例如下：

mysql> DROP PROCEDURE IF EXISTS testIF;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE testIF(IN num INT) -> BEGIN -> DECLARE result VARCHAR(20); -> IF num < -1 THEN -> SET result = 'negative number'; -> ELSEIF num = 0 THEN -> SET result = 'number is zero'; -> ELSE -> SET result = 'positive number'; -> END IF; -> SELECT result; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testIF(-9527);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

图示如下：

CASE

CASE是另一个条件判断的语句，该语句有两种语法格式。

第一种语法格式如下：CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] ... [ELSE statement_list]END CASE;1

2

3

4

5

示例如下：

mysql> DELIMITER //mysql> CREATE PROCEDURE testCASE1(IN num INT) -> BEGIN -> DECLARE result VARCHAR(20); -> CASE num -> WHEN 1 THEN SET result = 'num is 1'; -> WHEN 2 THEN SET result = 'num is 2'; -> ELSE SET result = 'num not 1 or 2'; -> END CASE; -> -> SELECT result; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testCASE1(1);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

图示如下：

第二种语法格式如下：CASE WHEN expr_condition THEN statement_list [WHEN expr_condition THEN statement_list] ... [ELSE statement_list]END CASE;1

2

3

4

5

示例如下：

mysql> DROP PROCEDURE IF EXISTS testCASE2;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE testCASE2(IN num INT) -> BEGIN -> DECLARE result VARCHAR(20); -> CASE -> WHEN num < 0 THEN SET result = 'negative number'; -> WHEN num = 0 THEN SET result = 'number is zero'; -> ELSE SET result = 'positive number'; -> END CASE; -> -> SELECT result; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testCASE2(1);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

图示如下：

LOOP

LOOP是一种循环语句，用来重复执行某些语句。在执行过程中可使用LEAVE语句跳出循环，也可以嵌套IF等判断语句。语法格式如下：[loop_label:] LOOP statement_listEND LOOP [loop_label]1

2

3

在该语法中，loop_label表示LOOP语句的标注名称，该参数可省略。

示例如下：

mysql> DROP PROCEDURE IF EXISTS testLOOP;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testLOOP(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> add_loop: LOOP -> SET sumResult=sumResult+start; -> SET start=start+1; -> IF start > end THEN -> LEAVE add_loop; -> END IF; -> END LOOP add_loop; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testLOOP(0,100);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

图示如下：

ITERATE

ITERATE表示再次循环，该语句作用是将执行顺序转到语句段开头处再执行，它与编程语言的continue非常类似；请注意：该语句只可出现在LOOP、REPEAT、WHILE语句内；语法格式如下：ITERATE label1

示例如下：

mysql> DROP PROCEDURE IF EXISTS testITERATE;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testITERATE(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> add_loop: LOOP -> SET sumResult=sumResult+start; -> SET start=start+1; -> IF start <= end THEN -> ITERATE add_loop; -> ELSE LEAVE add_loop; -> END IF; -> END LOOP add_loop; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testITERATE(0,100);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

结果如下：

REPEAT语句

REPEAT表示带判断条件的循环过程。每次语句执行完毕之后，会对条件表达式进行判断，若表达式为TRUE则循环结束，否则重复执行。REPEAT非常类似于do…while，语法格式如下：[repeat_label:] REPEAT statement_listUNTIL expr_condtionEND REPEAT [repeat_label]1

2

3

4

在该语法中，repeat_label表示REPEAT的标注名称，该参数可省略。

示例如下：

mysql> DROP PROCEDURE IF EXISTS testREPEAT;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testREPEAT(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> REPEAT -> SET sumResult = sumResult + start; -> SET start=start+1; -> UNTIL start > end -> END REPEAT; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testREPEAT(0,100);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

图示如下：

WHILE

WHILE用于带判断条件的循环过程。它与REPEAT不同，WHILE语句先判断表达式，为真则执行循环内的语句，否则退出循环。WHILE与编程语言的while非常类似；语法格式如下：[while_label:] WHILE expr_condition DO statement_listEND WHILE [while_label]1

2

3

在该语法中，while_label表示WHILE的标注名称，该参数可以省略。

示例如下：

mysql> DROP PROCEDURE IF EXISTS testWHILE;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testWHILE(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> WHILE start <= end DO -> SET sumResult = sumResult + start; -> SET start=start+1; -> END WHILE; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testWHILE(0,100);1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

图示如下：

游标

在数据库中游标(cursor)是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段。就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。例如，在编写存储过程时，查询语句可能会返回多条记录或大量记录，此时则需要使用游标来逐条读取查询结果集中的记录。

游标使用步骤为：定义游标、打开游标、使用游标、关闭游标、释放游标；在此，我们对一一进行介绍。

定义游标

语法如下：DECLARE 游标名 CURSOR FOR select_statement;1

打开游标

语法如下：

OPEN 游标名;1

使用游标

语法如下：DECLARE 变量1 与对应列值相同的数据类型DECLARE 变量2 与对应列值相同的数据类型DECLARE 变量3 与对应列值相同的数据类型FETCH NEXT FROM 游标 [ INTO 变量名1,变量名2,变量名3,…] ]1

2

3

4

在该语法中，利用DECLARE声明变量用于存储查询出来的数据；利用FETCH 获取数据并保存至DECLARE声明的变量中；其中，FETCH NEXT表示获取下一行数据

在使用游标时请注意：在初始状态中，游标默认指向数据集的第一行数据之前。所以，在使用游标时应该先执行FETCH NEXT操作让其指向第一行数据。

关闭游标

语法如下：

CLOSE 游标名;1

释放游标

语法如下：DEALLOCATE 游标名;1

在正式进入到游标的学习之前，我们先准备测试数据，代码如下：

DROP TABLE IF EXISTS student;CREATE TABLE student ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) DEFAULT 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');1

2

3

4

5

6

7

8

9

10

11

12

13

14

游标使用示例1

代码如下：mysql> DROP PROCEDURE IF EXISTS cursorTest1;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE cursorTest1() -> BEGIN -> -- 声明与对应列类型相同的4个变量 -> DECLARE studentID CHAR(6); -> DECLARE studentName VARCHAR(50); -> DECLARE studentAge INT; -> DECLARE studentGender VARCHAR(50); -> -> -- 定义游标studentCursor -> DECLARE studentCursor cursor for SELECT * FROM student; -> -- 打开游标 -> OPEN studentCursor; -> -- 使用游标获取列的值 -> FETCH next FROM studentCursor INTO studentID,studentName,studentAge,studentGender; -> -- 显示结果 -> SELECT studentID,studentName,studentAge,studentGender; -> -- 关闭游标 -> CLOSE studentCursor; -> END //Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;mysql> -- 调用存储过程mysql> CALL cursorTest1();1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

在该存储过程中，查询后得到的结果集为表中所有数据。在此，利用FETCH next将结果集中的第一条数据的sid,sname,age,gender对应地保存至studentID,studentName,studentAge,studentGender变量中。

图示如下：

游标使用示例2

代码如下：

mysql> DROP PROCEDURE IF EXISTS cursorTest2;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE cursorTest2() -> BEGIN -> -- 声明与对应列类型相同的4个变量 -> DECLARE studentID CHAR(6); -> DECLARE studentName VARCHAR(50); -> DECLARE studentAge INT; -> DECLARE studentGender VARCHAR(50); -> -> -- 声明计数器 -> DECLARE count INT DEFAULT 0; -> -> DECLARE total INT DEFAULT 0; -> -> -- 定义游标studentCursor -> DECLARE studentCursor cursor for SELECT * FROM student; -> -> set total = (SELECT count(*) FROM student); -> -> -- 打开游标 -> OPEN studentCursor; -> -- 利用REPEAT语句循环取出结果集中的数据 -> REPEAT -> FETCH next FROM studentCursor INTO studentID,studentName,studentAge,studentGender; -> SELECT studentID,studentName,studentAge,studentGender; -> SET count=count+1; -> UNTIL count=total -> END REPEAT; -> -> -- 关闭游标 -> CLOSE studentCursor; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> -- 调用存储过程mysql> CALL cursorTest2();1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

在该示例中利用REPEAT语句循环取出结果集中的每条数据并进行展示。在循环过程中FETCH next不断地指向结果集的下一条数据。

结果如下：