8.视图存储过程游标

前言

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

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

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

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

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

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

第七部分：表的删改查。

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

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

[toc]

摘要与总结

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

视图

推荐：深入解析MySQL视图VIEW

Ｑ-A，来自上面链接。

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

A：

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

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

Q：为什么要使用视图？

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

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

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

# 视图的创建
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存储过程的创建及调用

参考：MySQL存储过程与函数

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

Q：存储过程的作用

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

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游标的简单实践参考：mysql游标最后一行重复的问题

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;

参考文章

上一页7. 表的增删改查下一页9. 触发器和事务处理

最后更新于4年前