SQL高级特性：视图、存储过程、游标、事务（二）

目录

一、视图

1、是什么（定义）

2、什么情境下使用（功能）

3、怎样用

4、优缺点

二、存储过程

1、是什么（定义）

2、什么情境下使用（功能）

3、怎样用

4、优缺点

三、游标

1、是什么（定义）

2、什么情境下使用（功能）

3、怎样用

4、优缺点

四、事务

1、是什么（定义）

2、什么情境下使用（功能）

3、怎样用

4、优缺点

---

一、视图

1、是什么（定义）

        在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

        视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

2、什么情境下使用（功能）

（1）基本项

1）将用户限定在表中的特定行上。

        例如，只允许雇员看见工作跟踪表内记录其工作的行。

2）将用户限定在特定列上。

        例如，对于那些不负责处理工资单的雇员，只允许他们看见雇员表中的姓名列、办公室列、工作电话列、邮件列和部门列，而不能看见任何包含工资信息或个人信息的列。

3）将多个表中的列联接起来，使它们看起来象一个表。

4）聚合信息而非提供详细信息。

        例如，显示一个列的和，或列的最大值和最小值。

（2）拓展项

        一个视图可以引用另一个视图。

3、怎样用

（1）怎样创建、更新和删除视图

1）创建

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Notes：

        with check option用于保证所有update和insert语句都满足视图定义中的条件，如果不满足条件，则update和insert语句返回错误。示例如下：该视图拒绝任何age字段为null的条目update或insert。

CREATE VIEW view_name AS
SELECT name,age 
FROM table_name
WHERE age is not Null
with check option

2）更新

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

3）删除

DROP VIEW view_name

（2）怎样在视图中进行数据的增删改查

1）查询

SELECT * FROM view_name
WHERE condition

2）增、删、改

        视图能对查询出来的记录进行修改，并将修改后的数据回送到源表中。

        视图除了进行查询记录外，也可以利用视图进行插入、更新、删除记录的操作，减少对基表中信息的直接操作，提高了数据的安全性。 在视图上使用INSERT语句添加数据时，要符合以下规则：

（1）使用INSERT语句向数据表中插入数据时，用户必须有插入数据的权利。
（2）由于视图只引用表中的部分字段，所以通过视图插入数据时只能明确指定视图中引用的字段的取值。而那些表中并未引用的字段，必须知道在没有指定取值的情况下如何填充数据，因此视图中未引用的字段必须具备下列条件之一。
         该字段允许空值。
         该字段设有默认值。
         该字段是标识字段，可根据标识种子和标识增量自动填充数据。
         该字段的数据类型为timestamp或uniqueidentifier。
（3）视图中不能包含多个字段值的组合，或者包含使用统计函数的结果。
（4）视图中不能包含DISTINCT或GROUP BY子句。
（5）如果视图中使用了WITH CHECK OPTION，那么该子句将检查插入的数据是否符合视图定义中SELECT语句所设置的条件。如果插入的数据不符合该条件，SQL Server会拒绝插入数据。
（6）不能在一个语句中对多个基础表使用数据修改语句。因此，如果要向一个引用了多个数据表的视图添加数据时，必须使用多个INSERT语句进行添加。

4、优缺点

（1）优点

1）视图着重于特定数据。
        视图可以让用户或者程序开发人员只看到他们所需要的数据，而不需要把表中的所有信息与字段暴露出来，这样增强了数据的安全性。
2）简化数据的操作，易维护。
        我们可以将经常用到的多表联合查询出来的数据，或特定的结果集定义为视图，这样就起到了模块化数据的作用。我们在使用这些数据时直接查询该视图就可以，而不用到处写长长的SQL语句，这样也起到易维护的作用。
3）视图可以限定查询数据。
        比如：对于不同的用户，我们只提供部分数据给他。这样，我们就可以在视图中限定结果集，然后返回该视图给他。这样，无论用户怎么对视图定义查询条件，他也不能查询出我们不想提供给他的数据。

（2）缺点

    视图其实就是一段SQL语句，它的结果都是每次调用时动态生成的。如果不合理的定义视图，必然带来性能上的损耗。

二、存储过程

1、是什么（定义）

        存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在服务器端的数据库中，利用存储过程可以加速SQL语句的执行。

        自定义存储过程，由用户创建并能完成某一特定功能的存储过程，存储过程既可以有参数又有返回值，但是它与函数不同，存储过程的返回值只是指明执行是否成功，

        存储过程并不能像函数那样被直接调用，只能利用 execute 来执行存储过程。

2、什么情境下使用（功能）

        存储过程说白了就是一堆 SQL 的合并，中间加了点逻辑控制。存储过程处理比较复杂的业务时比较实用。

3、怎样用

以MySQL为例：

（1）查看存储过程

-- 显示所有数据库中所有存储过程的基本信息，如所属数据库、存储过程名、创建时间等
show procedure status;

-- 显示指定数据库中所有存储过程的基本信息，如 Demo 数据库
show procedure status where db='Demo';

（2）创建存储过程

-- 定义结束符为“$$”，mysql默认结束符为“;”
-- 意思是告诉mysql解释器，该段命令是否已经结束了，即标识一段命令起始和结束
delimiter $$

-- 创建存储过程
-- sp_char_split_inser：存储过程名称
-- strs：存储过程参数名称
-- in：表示该参数为输入参数；out：表示该参数为输出参数；inout：表示该参数为输入输出参数。不写时默认为in，即输入参数。
create procedure sp_char_split_inser(in strs text)
begin 
    declare i int default 0;
    declare leng int default 0;
    declare word char(1);
    
    -- 判断字符串是否为空或空字符串
    if(strs is not null && strs <> '') then 
        -- 获取字符串长度
        set leng = char_length(strs);
        -- 循环
        while i < leng do 
            -- 获取第一个字符
            set word=left(strs,1);
            if(word is not null && word <> '') then 
                -- 判断该条数据是否存在
                if not exists(select 1 from demo.charinfo where Hanzi=word limit 1) then 
                    -- 插入数据
                    insert into demo.charinfo(Hanzi) values(word);
                end if;
            end if;
            -- 截取除第一个字符之外的所有字符
            set strs=substring(strs,2);
            set i=i+1;
        end while;
    end if;
end;
-- 命令结束
$$
delimiter ;

（3）调用存储过程

-- 调用存储过程
set @s='测试文字';
call sp_char_split_inser(@s);
call sp_char_split_inser('测试一下');

（4）删除存储过程

-- 删除存储过程
drop procedure sp_char_split_inser;
drop procedure if exists sp_char_split_inser;

4、优缺点

（1）优点

        1）存储过程极大的提高SQL语言的功能和灵活性
        存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

        2）可保证数据的安全性和完整性

        安全性：通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
        完成性：通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

        3）极大地改善SQL语句的性能

        在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。 这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。

        4）可以降低网络的通信量

        客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语句相比自然数据量少了很多。

（2）缺点

        1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

        2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

        3）"重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

        4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

（3）总结

        1）适当的使用存储过程，能够提高我们SQL查询的性能。

        2）存储过程不应该大规模使用，滥用。

        3）随着众多ORM 的出现，存储过程很多优势已经不明显。

        4）SQL最大的缺点还是SQL语言本身的局限性——SQL本身是一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑，还是交给代码去处理吧。

三、游标

1、是什么（定义）

        游标是一段私有的SQL工作区,也就是一段内存区域,用于暂时存放受SQL语句影响到的数据。通俗理解就是将受影响的数据暂时放到了一个内存区域的虚表中，而这个虚表就是游标。

2、什么情境下使用（功能）

        游标的主要针对操作对象是从数据库返回的结果集，广义上可以理解为是从结果集中一行一行读取数据的方法，一种机制，狭义上也可以将游标理解为所要操作的结果集，因为在操作的最开始就需要给游标指定一个要操作的结果集（corsor c is select ....），也可以将游标理解为操作的每一行的而形成的临时文件，里面存放的是读取出来的该行的副本，这个临时文件提供了向前遍历和向后遍历以及处理数据的能力。

3、怎样用

（1）类型

隐式游标：增删改等操作Oracle都会自动创建游标，暂时保存操作结果，也就是能够回滚的操作都会引发游标的创建。

显示游标：由开发人员通过程序显式控制,用于从表中取出多行数据,并将多行数据一行一行的单独进行处理。

（2）属性

属性	注释
%rowcount	受sql影响的行数
%found	Boolean值，是否还有数据
%notfound	Boolean值，是否已无数据
%isopen	游标是否打开

      当然如果我们想获得隐式游标的属性，通过%前边加上SQL即可得到。例如SQL%rowcount。

（3）具体使用

 1）语法

declare
    --1、定义记录类型的变量 和 利用cursor关键字声明游标
begin
    --2、打开游标
    --3、利用fetch读取数据
    --4、释放游标
end;

2）无参游标

	declare
	--定义记录类型的变量
	v_user user%rowtype;
	--1、利用cursor关键字声明游标
	cursor user_cur is select * from user;
	begin
	    --2、打开游标
		open user_cur;
	    --3、利用fetch读取数据
		fetch user_cur into v_user;
		while user_cur%found loop
		  dbms_output.put_line(v_user.userName);
		end loop;
		--4、释放游标
		close user_cur;
	end;

3）带参游标

	declare
	  --定义记录类型的变量
	  v_User user%rowtype;
	  --1，利用cursor关键字声明带参数的游标
	  cursor user_Cur(v_UserNo number) is
	    select * from user where  userNo=v_UserNo;
	begin
	  --2,打开带参数的游标，使之更加灵活 。
	  open user_Cur(1012);
	  --3,利用fetch读取数据
	  loop
	       fetch user_Cur into v_User;
	       exit when user_Cur%notfound;
	       dbms_output.put_line(v_user.userName);
	  end loop;
	  --4，释放游标
	  close user_Cur;
	end;

4、优缺点

（1）优点

        游标允许应用程序对查询语句select 返回的 结果集 中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力。

（2）缺点

        处理大数据量时，效率低下，占用内存大。所以，我们都要求尽量避免使用游标，游标使用时会对行加锁，可能会影响其他业务的正常进行。而且，数据量大时其效率也较低效。另外，内存也是其中一个限制。

四、事务

1、是什么（定义）

        事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。这组工作单元，要不全部成功，要不全部不成功。

        一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。

2、什么情境下使用（功能）

        确保数据完整、安全、一致性。

        确保线程之间操作不会相互影响。

3、怎样用

（1）事务分类

        自动提交事务：每条单独的语句都是一个事务。

        显式事务：每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。

        隐式事务：在前一个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。

        批处理级事务：只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚。

（2）事务四大特性(ACID)

        一个逻辑工作单元必须有四个属性，称为原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）属性，只有这样才能成为一个事务。

（3）事务隔离级别

（4）具体使用

1）MYSQL的事务处理主要有两种方法
方法1：用begin,rollback,commit来实现
    begin开始一个事务
    rollback事务回滚
    commit 事务确认
方法2：直接用set来改变mysql的自动提交模式
    mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
    set autocommit = 0 禁止自动提交
    set autocommit = 1 开启自动提交
    来实现事务的处理。

4、优缺点

（1）优点

原子性、一致性、持久性通过数据库日志实现。

批处理数据库操作。

保证数据库级联操作的正确性。

（2）缺点

执行较慢。

需要较多的磁盘空间保存事务日志。

执行更新需要内存较多。

 

参考文章：

SQLServer事务详细讲解及优缺点介绍：http://www.manongjc.com/article/17534.html

MySQL——事务(Transaction)详解:https://blog.csdn.net/w_linux/article/details/79666086

数据库事务的概念及其实现原理：https://www.cnblogs.com/takumicx/p/9998844.html