【一次性搞懂】数据库视图，函数与存储过程

1.数据库视图

1.1概述

数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。
视图可以查询，但不可以新增、删除、修改。

1.2视图创建与查询

以navicat数据库可视化工具为例，创建一张视图并查询。
在视图->定义好查询语句。

也可以通过上方的视图创建工具（进行创建与查看）。
我一般使用sql语句进行定义。


我的视图取名为了testview,它和其他表一样只不过是虚拟表，可以进行查询也可以带有条件的搜索。

1.3视图的优点分析

1.数据安全：视图只能进行查询不能进行删除与修改，并且可以通过视图进行权限分割，其它数据库或表既不可见也不可以访问。能够对机密数据提供安全保护。
2.简化操作：数据查询逻辑直接定义好，程序员直接通过select * from 视图 就可直接查询想要的数据，逻辑简单化，代码的复杂性降低。

2.数据库函数与存储过程的区别

1.存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用。函数只能进行查询而存储过程可以对数据进行处理（增删改查）。
2.存储过程可以返回多个结果集，也可以无返回结果集；而函数只能返回值或者表对象。

2.1 数据库函数的使用

利用navicat数据库可视化工具创建一个名为test1的函数。

这里可以设置函数传入的变量以及类型，我这里没有设置传入的变量。

我选择返回的数据类型为字符串类型。点击完成。

✨这里MySQL 5.7版本之后的版本对一些函数和存储过程的定义进行了限制，只有具备DETERMINISTIC、NO SQL、READS SQL DATA属性的函数才可以被创建和调用，否则就会报出这个错误。

打开navicat，连接到你的MySQL数据库，然后执行以下命令：

SET GLOBAL log_bin_trust_function_creators = 1;

成功解决数据库创建函数的限制。

这里建立一个简单的数据库函数（返回字符串类型选择长度）：

这里写一个简单的数据库函数例子：

CREATE DEFINER=`root`@`localhost` FUNCTION `test1`() RETURNS varchar(10) CHARSET utf8
BEGIN
	#Routine body goes here...
	DECLARE flag VARCHAR(10);
	DECLARE numtext VARCHAR(20);
	SELECT output_value
	FROM biz_enterprise_info
	WHERE output_value = 1
	INTO numtext;
	IF(numtext = 110000) THEN SET flag = '是';
	ELSEIF(numtext = 500000) THEN SET flag = '是';
	ELSE SET flag = '否';
	END IF;
	RETURN CONCAT(flag,numtext);
END

这里就是我们直接写好的数据库函数了，在写的sql比较复杂且内容可以进行封装的时候，使用数据库函数能降低sql的复杂性，让代码更好维护，并且将业务查询进行封装后提高了代码的复用性。

2.2 存储过程

存储过程和函数类似，但是可以对数据进行处理，并且有三种参数类型可以选择：
（1）.没有参数（无参数无返回）
（2）.仅仅带 IN 类型（有参数无返回）
（3）.仅仅带 OUT 类型（无参数有返回）
（4）既带 IN 又带 OUT（有参数有返回）.
（5）.带 INOUT（有参数有返回）
IN、OUT、INOUT 都可以在一个存储过程中带多个。


注意navicat生成的数据库函数没有设置长度，保存会进行报错❌

设置好长度后成功保存。

以下是一个简单的存储过程示例：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test1`(IN `var1` varchar(10),OUT `var2` varchar(10),INOUT `var3` varchar(10))
BEGIN
	#Routine body goes here...
	 SELECT * from biz_title;
	 SET var3 = 'new value';
END

结果集1：

结果集2：

在存储过程中搭配上sql语句增删改查，能够对数据进行批处理、创建新的表格、修改表格结构、删除旧的表格等。还可用于数据库迁移。