mysql 存储过程 视图_MySQL视图和存储过程

视图

视图是虚拟的表，只包含使用时动态检索数据的查询。

视图作用：重用SQL语句；简化复杂的SQL操作；使用表的组成部分；保护数据；更改数据格式和表示。

CREATE VIEW创建视图；SHOW CREATE VIEW viewname查看创建视图语句；DROP VIEW viewname删除视图。

CREATE VIEW productcustomers AS

SELECT cust_name, cust_contact, proid_id

FROM customers, orders, orderitems

WHERE customers.cust_id = orders.cust_id

AND orderitems.order_num = order.order_num;

视图可以用来过滤数据，和计算字段结合使用。

并非所有视图是可更新的，对视图增删，实际上是对基表增删。如果视图含有以下操作，则不能视图更新：

分组group by ,having

联结

子查询

并

聚集函数min() count() sum()

存储过程

保存一条或者多条SQL语句的集合，可以视为批处理。

MySQL执行存储过程的语句为CALL，CALL接受存储过程的名字以及需要传递给它的任意参数

CALL productpricing(@pricelow,@pricehigh,@priceaverage);//执行存储过程，计算返回产品最低最高平均价格

返回产品平均价格的存储过程

CREATE PROCEDURE productpricing()

GEGIN

SELECT Avg(prod_price) AS priceaverage

FROM product;

END;

删除存储过程 DROP PROCEDURE productpricing;

CREATE PROCEDURE ordertotal(

IN onumber INT,

OUT ototal DECIMAL(8,2)

)

BEGIN

SELECT Sum(item_price*quantity)

FROM orderitems

WHERE order_num = onumber

INTO ototal;

END;

//调用存储过程

CALL ordertotal(20005, @total);

SELECT @total;//查询存储过程结果

游标

游标是一个存储在MySQL服务器上的数据库查询，不是SELECT语句，而是被该语句检索出来的结果集。存储游标后，可以根据需要滚动浏览其中数据。

游标主要用于交互式应用，用户需要滚动屏幕上的数据。在MySQL游标只能用于存储过程和函数。

游标用DECLARE创建

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

END;

OPEN CURSOR打开游标，CLOSE CURSOR；关闭游标。在游标被打开后，可以使用FETCH语句分别访问每一行。

CREATE PROCEDURE processorders()

BEGIN

-- Declare local variables//注释

DECLARE o INT;

-- Declare the cursor

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

-- Open the cursor

OPEN ordernumbers;

--Get order number

FETCH ordernumbers INTO o;

--Close the cursor

CLOSE ordernumbers;

END;

触发器

创建触发器，需要给出4条信息：唯一的触发器名；触发器关联的表；触发器应该相应的活动(DELETE INSERT UPDATE)；触发器何时执行。

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'//每个成功的插入都会显示Product added消息

只有基表支持触发器，视图不支持。每个表最多6个触发器，INSERT前后，UPDATE前后，DELETE前后。

CREATE TRIGGER newworder AFTER INERT ON orders FOR EACH ROW SELECT NEW.order_num;//NEW表示新插入行

INSERT INTO orders(order_date, cust_id)

VALUES (Now(), 10001);

//输出order_num 20010

DELETE触发器，可以引用OLD的虚拟表，只读不能更新。

UPDATE触发器，可以引用OLD的虚拟表访问以前的值，引用NEW访问新的更新值。