MYSQL学习与实验(八)——存储过程实验

发布于 2020-12-04  1935 次阅读


8.存储过程实验

8.1存储过程定义

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时,只需调用“CALL存储过程名字”即可自动完成。

8.2存储过程的优点

1) 封装性

通常完成一个逻辑功能需要多条 SQL 语句,而且各个语句之间很可能传递参数,所以,编写逻辑功能相对来说稍微复杂些,而存储过程可以把这些 SQL 语句包含到一个独立的单元中,使外界看不到复杂的 SQL 语句,只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。

2) 可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

3) 可减少网络流量

由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。

4) 高性能

当存储过程被成功编译后,就存储在数据库服务器里了,以后客户端可以直接调用,这样所有的 SQL 语句将从服务器执行,从而提高性能。但需要说明的是,存储过程不是越多越好,过多的使用存储过程反而影响系统性能。

5) 提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件,存储过程里可以对某些表做相关操作,然后存储过程作为接口提供给外部程序。这样,外部程序无法直接操作数据库表,只能通过存储过程来操作对应的表,因此在一定程度上,安全性是可以得到提高的。

6) 使数据独立

数据的独立可以达到解耦的效果,也就是说,程序可以调用存储过程,来替代执行多条的 SQL 语句。这种情况下,存储过程把数据同用户隔离开来,优点就是当数据表的结构改变时,调用表不用修改程序,只需要数据库管理者重新编写存储过程即可。

8.3创建存储过程

语法格式如下:

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

语法说明如下:

1) 过程名

​ 存储过程的名称,默认在当前数据库中创建。若需要在特定数据库中创建存储过程,则要在名称前面加上数据库的名称,即 db_name.sp_name。需要注意的是,名称应当尽量避免选取与 MySQL 内置函数相同的名称,否则会发生错误。

2) 过程参数

​ 存储过程的参数列表。其中,< 参数名 >为参数名,< 类型 >为参数的类型(可以是任何有效的 MySQL 数据类)。当有多个参数时,参数列表中彼此间用逗号分隔。存储过程可以没有参数(此时存储过程的名称后仍需加上一对括号),也可以有 1 个或多个参数。

​ MySQL 存储过程支持三种类型的参数,即输入参数、输出参数和输入/输出参数,分别用 IN、OUT 和 INOUT 三个关键字标识。其中,输入参数可以传递给一个存储过程,输出参数用于存储过程需要返回一个操作结果的情形,而输入/输出参数既可以充当输入参数也可以充当输出参数。

​ 需要注意的是,参数的取名不要与数据表的列名相同,否则尽管不会返回出错信息,但是存储过程的 SQL 语句会将参数名看作列名,从而引发不可预知的结果。

3) 过程体

​ 存储过程的主体部分,也称为存储过程体,包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始,以关键字 END 结束。若存储过程体中只有一条 SQL 语句,则可以省略 BEGIN-END 标志。

​ 在存储过程的创建中,经常会用到一个十分重要的 MySQL 命令,即 DELIMITER 命令,特别是对于通过命令行的方式来操作 MySQL 数据库的使用者,更是要学会使用该命令。

注意:在 MySQL 中,服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而,在创建存储过程时,存储过程体可能包含有多条 SQL 语句,这些 SQL 语句如果仍以分号作为语句结束符,那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符,而不再去处理存储过程体中后面的 SQL 语句,这样显然不行。

为解决以上问题,通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下:

DELIMITER $$

语法说明如下:

  • $$ 是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“?”或两个“¥”等。
  • 当使用 DELIMITER 命令时,应该避免使用反斜杠“\”字符,因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下 SQL 语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后,任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志,则在 MySQL 命令行客户端输入下列语句即可:

mysql > DELIMITER ;

注意:DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时,必须具有 CREATE ROUTINE 权限。

8.3.1创建存储过程例子

mysql> delimiter $$
mysql> create procedure show_employeeInfo()
    -> begin
    -> select *from employee;
    -> end$$

8.4查看存储过程结构:

show create procedure  show_employeeInfo \G;
在这里插入图片描述

8.5删除存储过程:

DROP PROCEDURE [ IF EXISTS ] <过程名>

语法说明如下:

①过程名:指定要删除的存储过程的名称。

②IF EXISTS:指定这个关键字,用于防止因删除不存在的存储过程而引发的错误。

注意:存储过程名称后面没有参数列表,也没有括号,在删除之前,必须确认该存储过程没有任何依赖关系,否则会导致其他与之关联的存储过程无法运行。

在这里插入图片描述

8.6修改存储过程的语法格式如下:

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性,可能的取值有:

①CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句。

②NO SQL 表示子程序中不包含 SQL 语句。

③READS SQL DATA 表示子程序中包含读数据的语句。

④MODIFIES SQL DATA 表示子程序中包含写数据的语句。

⑤SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。

⑥DEFINER 表示只有定义者自己才能够执行。

⑦INVOKER 表示调用者可以执行。

⑧COMMENT 'string' 表示注释信息。

8.7存储过程调用

CALL 存储过程名([参数…]);

8.8存储函数

和存储过程一样,都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值,主要用于计算并返回一个值。而存储过程没有直接返回值,主要用于执行操作。

8.1.8.1语法在 MySQL 中,使用 CREATE FUNCTION 语句来创建存储函数,其语法形式如下:
CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body

其中:

①sp_name 参数:表示存储函数的名称;

②func_parameter:表示存储函数的参数列表;

③RETURNS type:指定返回值的类型;

④characteristic 参数:指定存储函数的特性,该参数的取值与存储过程是一样的;

⑤routine_body 参数:表示 SQL 代码的内容,可以用 BEGIN…END 来标示 SQL 代码的开始和结束。

注意:在具体创建函数时,函数名不能与已经存在的函数名重名。除了上述要求外,推荐函数名命名(标识符)为 function_xxx 或者 func_xxx。

func_parameter 可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下:

[IN | OUT | INOUT] param_name type;

其中:

①IN 表示输入参数,OUT 表示输出参数,INOUT 表示既可以输入也可以输出;

②param_name 参数是存储函数的参数名称;

③type 参数指定存储函数的参数类型,该类型可以是 MySQL 数据库的任意数据类型。

8.8.2例子

Delimiter $$;
 create function real_income(employeeID_1 varchar(6),employeeID_2 varchar(6)) returns tinyint(1) begin if (select income - outcome from salary where employeeID=employeeID_1) >  (select income - outcome from salary where employeeID=employeeID_2) then return 0; else return 1;  end if ;  end$$

8.9定义变量

MySQL 中可以使用 DECLARE 关键字来定义变量,其基本语法如下:

DECLARE var_name[,…] type [DEFAULT value]

其中:

  • DECLARE 关键字是用来声明变量的;
  • var_name 参数是变量的名称,这里可以同时定义多个变量;
  • type 参数用来指定变量的类型;
  • DEFAULT value 子句将变量默认值设置为 value,没有使用 DEFAULT 子句时,默认值为 NULL。

8.9.1例 1

下面定义变量 my_sql,数据类型为 INT 类型,默认值为 10。SQL 语句如下:

DECLARE my_sql INT DEFAULT 10;

8.9.2. 为变量赋值

MySQL 中可以使用 SET 关键字来为变量赋值,SET 语句的基本语法如下:

SET var_name = expr[,var_name = expr]...

其中:

  • SET 关键字用来为变量赋值;
  • var_name 参数是变量的名称;
  • expr 参数是赋值表达式。

注意:一个 SET 语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开。

8.9.3例 2

下面为变量 my_sql 赋值为 30。SQL 语句如下:

SET my_sql=30;

MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下:

SELECT col_name [...] INTO var_name[,...]
FROM table_name WEHRE condition

其中:

  • col_name 参数表示查询的字段名称;
  • var_name 参数是变量的名称;
  • table_name 参数指表的名称;
  • condition 参数指查询条件。

注意:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行。

8.10例子

8.10.1.创建一个存储过程实例

mysql> delimiter $$

mysql> create procedure show_employeeInfo()

  -> begin

  -> select *from employee;

  -> end$$

Query OK, 0 rows affected (0.49 sec)
在这里插入图片描述

然后查看过程存储创建结构:
在这里插入图片描述

最后调用存储过程函数:

在这里插入图片描述

8.10.2.删除存储过程:使用DROP PROCEDURE语句删除存储过程,语法格式为:

在这里插入图片描述
DROP PROCEDURE [IF EXISTS] 存储过程名;

8.10.3创建一个存储过程,计算employee表中的员工人数,并存储到一个局部变量中,调用存储过程,并查看该变量结果(使用select @variable)

 create procedure count_employee(out count_emp int)  begin set count_emp = (select count(*) from employee) ;  end $$ 
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

调用并查询结果:

8.10.4.创建一个存储过程,比较两个员工的实际收入,若前者比后者高就输出0,否则输出1,员工用其员工编号识别。

Delimiter $$;

 create function real_income(employeeID_1 varchar(6),employeeID_2 varchar(6)) returns tinyint(1) begin if (select income - outcome from salary where employeeID=employeeID_1) >  (select income - outcome from salary where employeeID=employeeID_2) then return 0; else return 1;  end if ;  end$$

这时出现报错:

在这里插入图片描述

这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句,当然也不会修改数据
3 READS SQL DATA 只是读取数据,当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
在MySQL中创建函数时出现这种错误的解决方法:
set global log_bin_trust_function_creators=TRUE;
在这里插入图片描述

问题解决。
然后开始调用函数:

在这里插入图片描述
成功实现需要的功能。

8.11详细的实验资料以及指导PPT请访问笔者GitHub地址:https://github.com/LJF2402901363/database_experiment.git


繁华落尽,雪花漫天飞舞。