# 8.视图存储过程游标

## 前言

1️⃣ ：[mysql环境准备](https://blog.csdn.net/sinat_38816924/article/details/105478479)

2️⃣ ：[简单的表查询](https://blog.csdn.net/sinat_38816924/article/details/105718525)

3️⃣ ：[通配符+正则表达式](https://blog.csdn.net/sinat_38816924/article/details/105737660)

4️⃣ ：[mysql函数与分组](https://blog.csdn.net/sinat_38816924/article/details/105748596)

5️⃣ ：[子查询*联结查询*组合查询](https://blog.csdn.net/sinat_38816924/article/details/105775052)

6️⃣ ：[mysql全文本搜索](https://blog.csdn.net/sinat_38816924/article/details/105776310)

:seven: ：[表的增删改查](https://blog.csdn.net/sinat_38816924/article/details/105880094)

第一部分：我们准备环境：安装数据库+创建数据库+创建用户+授权。

第二部分：环境准备好后，进行简单的单表查询：导入表+表查询。

第三部分：通配符和正则表达式，增强where的筛选功能。

第四部分：对列进行计算处理称为字段。复杂的字段用函数处理。按照不同组汇总，进行分组操作。

第五部分：在多表上进行查询：子查询+联结查询＋组合查询。

第六部分：mysql的全文本搜索。

第七部分：表的删改查。

**前七部分组成表的增删改查。** **之后都是一个一个小模块的介绍。**

现在是第八部分：参考《mysql必知必会》chapter 22\~24，**视图+存储过程+游标**

\[toc]

## 摘要与总结

视图是虚拟的表；存储过程是一条或多条MySQL语句的集合；对于填有数据的游标，根据需要取出（检索）各行； <br>

## 视图

推荐：[深入解析MySQL视图VIEW](https://www.cnblogs.com/geaozhang/p/6792369.html)

Ｑ-A，来自上面链接。

Q：什么是视图？视图是干什么用的？

A：

1. 视图（view）是一种虚拟存在的表，是一个逻辑表，**本身并不包含数据。作为一个select语句**保存在数据字典中的。
2. 通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

基表：用来创建视图的表叫做基表base table

Q：为什么要使用视图？

A：因为视图的诸多优点，如下

1. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

```
# 视图的创建
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id 
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
with check option; -- 对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。这里没啥用

# 使用视图和表类似
SELECT * FROM productcustomers
WHERE prod_id='TNT2';
```

需要注意的是：因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中

**对于视图的修改，必须正确的确定被更新的基数据，才能通过视图更新作用到基表上。**

所以对于计算列之类，无法确定更新到基数据上的dml是无法（~~无法？~~）操作的。

## 存储过程

推荐：[MySQL存储过程的创建及调用](https://www.cnblogs.com/geaozhang/p/6797357.html)

参考：[MySQL存储过程与函数](https://www.myfreax.com/mysql-function-and-stored-procedure/)

Ｑ：什么是存储过程? 存储过程简单来说，就是为以后的使用而保存的**一条或多条MySQL语句的集合**。可将其视为批文件，虽然它们的作用不仅限于批处理。

Q：存储过程的作用

1. 通过把处理封装在容易使用的单元中，简化复杂的操作
2. 统一接口，确保数据的安全

Q：相关的操作（基本都是那一套，创建/查看/使用/修改/删除）

创建存储过程、查看当前所用于的存储过程、执行存储过程、删除存储过程

```
-- 存储过程
-- name:ordertotal
-- parameters: ordernum=order_num
--             taxable = 0 or 1 
--             ototal = order total varibale
DELIMITER $$
drop procedure if exists ordertotal$$

create procedure ordertotal(
    in ordernum int,
    in taxable boolean,
    out ototal decimal(8,2)
)comment 'get total variable with tax'
begin
    -- declare varibale for total
    declare total decimal(8,2);
    -- declare tax precent
    declare tax_prec int default 6;

    -- get total variable without tax
    select sum(quantity*item_price)
    from orderitems
    where order_num=ordernum
    into total;

    -- is taxable
    if taxable then
        select total+(total/100*tax_prec) into total;
    end if;

    -- return total with tax
    select total into ototal;
end$$
DELIMITER ;

call ordertotal(20005,1,@total);
select @total;
```

## 游标

推荐：[MySQL游标的简单实践](https://www.cnblogs.com/geaozhang/p/6817637.html) 参考：[mysql游标最后一行重复的问题](https://my.oschina.net/u/1383934/blog/667444)

Q：为什么需要游标

MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或多行）。使用简单的SELECT语句，例如，没有办法得到第一行、下一行或前10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，**需要对检索出来的行，一行一行处理**。这就是使用游标的原因。

我想了下，一行一行的检索交给where来处理，检索出来的操作可以给列来处理，相对而言就可以替代游标的效果了。不知道是不是这样。

Q：什么是游标

**游标（ cursor）** 是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而**是被该语句检索出来的结果集。** (这个恰好和视图是相反的)

Q：游标的操作

创建，打开，使用，关闭

```
-- 游标
-- 将total with tax单独放入一个表中
delimiter $$
drop procedure if exists prosessorders$$

create procedure prosessorders()
begin

    -- 声明一个boolen变量，用于游标终止循环
    declare done boolean default 0;
    -- 声明一个变量存储total
    declare total decimal(8,2);
    -- 声明一个变量从游标每次取值
    declare ordernum int;

    -- 声明个游标
    declare ordernums cursor
    for
    select order_num from orders;
    -- declare continue handler
    -- declare continue handler for sqlstate '02000' set done=1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 

    -- 创建一个表，保存totals
    drop table if exists ordertotals;
    create table if not exists ordertotals
    (order_num int,total decimal(8,2));

    -- 打开游标
    open ordernums;

    -- loop through all the row
    repeat
        fetch ordernums into ordernum;

        if !done then
            -- 根据order_num,从orderitems中获取totals
            call ordertotal(ordernum,1,total);

            -- 把计算出来的结果，插入表中
            insert into ordertotals(order_num,total)
            values(ordernum,total);
        end if;
    until done end repeat;

    -- 关闭游标
    close ordernums;
end$$
delimiter ;

call prosessorders();
select * from ordertotals;--  order by order_num;
```

## 参考文章

[深入解析MySQL视图VIEW](https://www.cnblogs.com/geaozhang/p/6792369.html)

[MySQL存储过程的创建及调用](https://www.cnblogs.com/geaozhang/p/6797357.html)

[MySQL存储过程与函数](https://www.myfreax.com/mysql-function-and-stored-procedure/)

[MySQL游标的简单实践](https://www.cnblogs.com/geaozhang/p/6817637.html)

[mysql游标最后一行重复的问题](https://my.oschina.net/u/1383934/blog/667444)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dacao-1.gitbook.io/database/shi-tu-cun-chu-guo-cheng-you-biao.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.