MySQL之数据库编程（创建存储过程）

        存储过程是存放在数据库中的一段程序，是数据库对象之一。它由声明式的SQL语句（如CREATE、UPDATE和SELECT等语句）和过程式SQL语句（如IF-THEN-ELSE语句组成）。存储过程可以由程序、触发器或另一个存储过程来调用它而激活，实现代码段中的SQL语句。

使用存储过程的优点：

1、存储过程在服务器端运行，执行速度快。

2、存储过程执行一次后，其执行代码就驻留在高速缓冲存储器，在以后的操作中，只须从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。

3、确保数据库的安全。使用存储过程可以完成所有数据库操作，并可以通过编程方式控制上述操作对数据库信息访问的权限。

目录

 1、存储过程

1.1、创建存储过程语法：

1.2、存储过程体

1.2.1、局部变量

 1.2.2、使用SET语句赋值

 2、显示存储过程

3、调用存储过程

4、删除存储过程 

5、流程控制语句

5.1、分支语句

5.1.1、IF语句

 5.1.2、CASE语句

 5.2、循环语句

5.2.1、WHILE语句

 5.2.2、REPEAT语句

5.2.3、LOOP语句

5.3、存储过程的嵌套

 

 1、存储过程

1.1、创建存储过程语法：

CREATE PROCEDURE 存储过程名（[ 参数[,...] ] ）存储过程体

         在MySQL中，服务器处理语句时是以分号为结束标志的。但是在创建存储过程时，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序遇到第1个分号就会认为程序结束，这肯定是不行的。因此这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。

语法格式：DELIMITER $$

例：将MySQL结束符修改为2个'#'符号 

DELIMITER ##;

 执行完这条命令后，程序结束的标志就换为符号“##”了。

要想恢复使用分号“；”作为结束符，运行 DELIMITER; 这条命令就可以了。

 例：编写一个存储过程，其功能是删除一个特定会员的信息。

DELIMITER $$
CREATE PROCEDURE del(IN sfz CHAR(6))
BEGIN
    DELETE FROM Members WHERE 身份证号=sfz;
END $$
DELIMITER ;

1.2、存储过程体

1.2.1、局部变量

        在存储过程中可以声明局部变量，他们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。

语法格式：DECLARE 变量[ ,... ]类型 [ DEFAULT 值 ]

例：声明1个整型变量和2个字符变量 

DECLARE num INT(4);
DECLARE str1,str2 VARCHAR(6);

        局部变量只能在BEGIN...END语句块中声明。局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGIN...END语句块中使用该变量，其他语句块中不可以使用它。

         在存储过程中也可以声明用户变量，不能混淆这两种变量。局部变量和用户变量的区别在于：局部变量前面没有使用@符号，局部变量在其所在的BEGIN...END语句块处理完后就消失了，而用户变量存在于整个会话当中。

 1.2.2、使用SET语句赋值

语法格式：SET 变量名=表达式

例：在存储过程中给局部变量num赋值为1，str赋值为hello

SET num=1,str='hello';

1.2.3、SELECT...INTO语句

        使用SELECT...INTO语句可以把选定的列值直接存储到变量中，但返回的结果只能有一行。语法格式：SELECT 列名 [,...] INTO 变量名 [,...] 数据来源表达式

例：在存储过程体中将Book表中书名为“计算机基础”的作者姓名和出版社的值分别赋给变量name和publish 

SELECT 作者，出版社 INTO name,publish
FROM Book
WHERE 书名='计算机基础';

 2、显示存储过程

        要想查看数据库中有哪些存储过程，可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息，可使用SHOW CREATE PROCEDURE sp_name命令，其中sp_name是存储过程的名称。

查询当前数据库中的存储过程：

SHOW PROCEDURE STATUS;

查看存储过程的创建代码：

SHOW CREATE PROCEDURE 存储过程名;

3、调用存储过程

        存储过程创建完后，可以在程序、触发器或者存储过程中被调用，调用时都必须使用CALL语句。语法格式：CALL 存储过程名（[参数]）

 例：创建存储过程实现查询Members表中会员人数的功能，并执行。

1、创建查询Members表中会员人数的存储过程

CREATE PROCEDURE query_members()
SELECT COUNT(*)FROM Members;

 2、调用该存储过程

CALL query_members();

4、删除存储过程 

        存储过程创建后需要删除时使用DROP PROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

语法格式：DROP PROCEDURE [ IF EXISTS ] 存储过程名

例：删除存储过程quer()  

DROP PROCEDURE IF EXISTS quer();

5、流程控制语句

        在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句和LEAVE语句

5.1、分支语句

5.1.1、IF语句

IF-THEN-ELSE语句是控制程序根据不同条件执行不同的操作

语法格式：

 IF 条件1 THEN 语句序列1

[ ELSEIF 条件2 THEN 语句序列2 ]...

[ ELSE 语句序列e ]

END IF

例：创建存储过程，判断输入的两个参数n1和n2哪一个更大，结果放在变量result中 

1、存储过程中设n1和n2为输入参数，result为输出参数 

DELIMITER $$
CREATE PROCEDURE cp_num(IN n1 INTEGER,IN n2 INTEGER,OUT result CHAR(6))
BEGIN
IF n1>n2 THEN
SET result='大于';
ELSEIF n1=n2 THEN
SET result='等于';
ELSE
SET result='小于';
END IF;
END$$
DELIMITER;

 2、调用该存储过程

CALL cp_num(4,5,@D);
SELECT @D;

 5.1.2、CASE语句

        CASE语句可以应用于选择列，也可以应用于存储过程中，两者用法略有不同，以下是介绍CASE语句应用在存储过程中。

语法格式：

CASE 表达式

WHEN 值1 THEN 语句序列1

[WHEN 值2 THEN 语句序列2]...

[ELSE 语句序列e]

END CASE

或者

CASE

WHEN 条件1 THEN 语句序列1

[WHEN 条件2 THEN 语句序列2]...

[ELSE 语句序列e]

END CASE

例：创建一个存储过程，当给定参数为U时返回“上升”，给定参数为D时返回“下降”，给定其他参数时返回“不变”。

DELIMITER $$
CREATE PROCEDURE var_cp(IN str VARCHAR(1),OUT direct VARCHAR(4))
BEGIN
CASE str
WHEN'U'THEN SET direct='上升';
WHEN'D'THEN SET direct='下降';
ELSE SET direct='不变';
END CASE;
END$$
DELIMITER ;
 
#或
 
CASE
WHEN str='U'THEN SET direct='上升';
WHEN str='D'THEN SET direct='下降';
ELSE SET direct='不变';
END CASE;

 5.2、循环语句

        MySQL支持3条用来创建循环的语句，分别是WHILE、REPEAT和LOOP语句。在存储过程中可以定义0个、1个或多个循环语句。

5.2.1、WHILE语句

语法格式：

[ 开始标号：]WHILE 条件 DO

程序段

END WHILE [ 结束标号 ]

说明：语句先判断条件是否为真，为真则执行程序段中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。

开始标号和结束标号是WHILE语句的标注。除非开始标号存在，否则不能单独出现结束标号，并且如果两者都出现，他们的名字必须是相同的。

例：创建1个带WHILE执行5次循环的存储过程。 

DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE a INT DEFAULT 5;
WHILE a>0 DO
SET a=a-1;
END WHILE;
END$$
DELIMITER ;

当调用该存储过程时，首先判断a的值是否大于0，如果大于0则执行a-1，否则结束循环。 

 5.2.2、REPEAT语句

语法格式：

[ 开始标号：]REPEAT

程序段

UNTIL 条件

END REPEAT [ 结束标号 ]

说明：REPEAT语句先执行程序段中的语句，然后判断条件是否为真，不为真则停止循环，为真则继续循环。REPEAT也可以被标注。

例：创建1个带WHILE执行5次循环的存储过程。(上面的不同写法) 

REPEAT
a=a-1;
UNTIL a<1;
END REPEAT;

 REPEAT语句和WHILE语句的区别在于：REPEAT语句先执行语句，后进行判断。而WHILE语句是先判断，条件为真时才执行语句。

5.2.3、LOOP语句

语法格式：

[ 开始标号：]LOOP

程序段

END LOOP[结束标号]

说明：LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，程序段是需要重复执行的语句。在循环体内的语句一直重复运行至循环被退出，退出时通常伴随着一个LEAVE语句。LEAVE语句经常和BEGIN...END或循环一起使用。

语法格式：LEAVE 语句标号 

语句标号是语句中标注的名字，该名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

 例：创建1个带WHILE执行5次循环的存储过程。(上面的不同写法) 

DELIMITER $$
CREATE PROCEDURE doloop()
BEGIN
SET @a=5;
Label:LOOP
SET @a=@a-1;
IF @a<1 THEN
LEAVE Label;
END IF;
END LOOP Label;
END$$
DELIMITER ;

 语句中，先定义了一个用户变量并赋值为5，接着进入LOOP循环，标注为Label，执行减1语句，然后判断用户变量a是否小于1，是则使用LEAVE语句跳出循环。

5.3、存储过程的嵌套

例： 创建一个存储过程sell_insert()，其作用是向Sell表中插入一行数据。创建另外一个存储过程sell_update，在其中调用第1个存储过程，如果给定参数为0，则修改由第1个存储过程插入记录的“是否发货”字段为“已发货”，如果给定参数为1则删除第1个存储过程插入的记录，并将操作结果输出。

第1个存储过程：向Sell表中插入一行数据 

CREATE PROCEDURE sell_insert()
INSERT INTO Sell VALUES('17','43045','IS-01',4,30,'2022-08-21',NULL,NULL);

 第2个存储过程：调用第1个存储过程，并输出结果

DELIMITER $$
CREATE PROCEDURE sell_update(IN X INT(1), OUT STR CHAR(8))
BEGIN
CALL sell_insert();
CASE
WHEN x=0 THEN
UPDATE Sell SET 是否发货='已发货'WHERE 订单号='17';
SET STR='修改成功';
WHEN X=1 THEN
DELETE FROM Sell WHERE 订单号='17';
SET STR='删除成功';
END CASE;
END $$
DELIMITER;