SQL存储过程和视图

 1 存储过程

存储过程是事先编写好、存储在数据库中的一组SQL命令集合。用来完成对数据库的指定操作。

1.1 优缺点

优点：

1）提高系统性能。创建时进行编译，随后存放在数据库服务器的过程高速缓存中，之后不需要再次执行分析和编译操作，节省了分析、解析和优化SQL代码所需的时间。

2）可自动完成需要预先执行的任务。

缺点：

1）每个连接的内存使用量将增加。

2）开发和维护困难、可移植性差。

1.2 mysql变量

sql变量按范围可划分为三类：

1）全局变量（系统变量），对所有会话生效，需具备super权限才能设置。SET GLOBAL 变量名。

注意：全局变量不能被定义只能修改，只能设置已存在的系统变量，否则报错

2）会话变量，对当前会话生效。SET @变量名或者SET @@变量名。

3）局部变量，作用返回在BEGIN与END之间。

DECLARE	1）设置局部变量。 2）不能以@开始
SET	1）可以以@开始 2）定义会话变量或设置全局变量。

表DECLARE 与 SET的区别

DROP VARIABLE 变量名，删除变量。

1.2.1 变量赋值

1）使用set。 set @变量名 = 变量值 或 set @变量名 := 变量值;

SET @val1 = "123";

SET @val2 := "abc";

2）使用SELECT。必须使用 “:=”，不能使用 “=”(其此时代表比较是否相等)。 注意：SELECT赋值变量时，变量前面一定要是@。

SELECT @变量名 := 变量值;

SELECT @val3 := "edf";

SELECT @变量名:= 字段名 FROM table_name WHERE ... LIMIT 0,1; (如果查询结果不止一个，则取结果的最后一个)

SELECT @money := money FROM student WHERE money > 0 LIMIT 0,1;

1.3 定义

存储过程有三种类型变量：1）IN，输入参数；2）OUT，输出参数；3）INOUT，输入/输出参数。

其基本语法如下；

1）语句必须在BEGIN与END之间。

2）DECLARE 来声明变量，默认值在其后面加DEFAULT 值。

3）改变变量值，使用SET 变量=值。

4）调用使用CALL。

5）mysql 不支持 CREATE OR REPLACE PROCEDURE 语法，可以用DROP PROCEDURE IF EXISTS 存储过程名;来删除特定的存储过程。

DROP PROCEDURE IF EXISTS demo;

CREATE PROCEDURE demo(IN p_age INT,OUT p_count INT)

BEGIN

SELECT @count := COUNT(*) FROM student WHERE age > p_age;

SET p_count = @count;

END;

CALL demo(18,@count);

SELECT @count;

1.3.1 条件控制 IF ELSE 与 CASE

只能在BEGIN 及 END 之间使用IF ELSE。需要以 END IF 结尾。

DROP PROCEDURE IF EXISTS demo;

CREATE PROCEDURE demo(IN p_num INT)

BEGIN

DECLARE tempChar CHAR(24);

IF p_num = 1 THEN SET tempChar = '一';

ELSEIF p_num = 2 THEN SET tempChar = '二';

ELSE SET tempChar = "其他";

END IF;

SELECT tempChar;

END;

CALL demo(1);

CALL demo(5);

注意：不能在SELECT 中直接使用IF ELSE,下面代码是错误的：

SELECT

    IF p_num = 1 THEN '一';

    ELSE '其他';

    END IF;

CASE WHEN使用范围更广，可在SELECT中直接使用，需要以END结尾。

SELECT

    CASE age

    WHEN 17 THEN '十七'

    WHEN 18 THEN '十八'

    ELSE '其他'

    END AS age

FROM student       

1.3.2 循环 LEAVE与ITERATE

循环需要有个标签，LEVAE相当于Java的break，而ITERATE相当于continue。循环体位于 标签名:LOOP 与 END LOOP 标签名之间。

DROP PROCEDURE IF EXISTS demo;

CREATE PROCEDURE demo(IN p_num INT)

BEGIN

  DECLARE p_count INT DEFAULT 0;

    DECLARE p_pos INT DEFAULT 0;

    myLabel: LOOP

    SET p_pos = p_pos + 1;

    IF p_pos > p_num THEN

               LEAVE myLabel;

    END IF;

    IF MOD(p_pos,2) = 1 THEN

               ITERATE myLabel;

    END IF;

    SET p_count = p_count + 1;

  END LOOP myLabel;

    SELECT p_count;

END;

1.4 与函数对比

	存储过程	函数
返回值	返回0个、一个或多个结果集	有且只有一个结果值。
调用方式	call调用	可以直接在SELECT中使用。
参数	有三种类型 IN、OUT、INOUT	只有类似IN类型参数
其他限制	可以用临时表	不能用临时表

表 存储过程与函数的对比

2 视图

视图是一种虚拟表（逻辑表），本身并不包含数据，作为一个SELECT语句保存在数据字典中。由一个或多个表（报告视图）查询而动态生成的表。

2.1 优缺点

优点：

简单、安全、数据独立。

缺点：

1）性能较低。

2）维护复杂，每当修改与视图相关的基表的表结构时，都必须进行视图更改操作。

3）修改限制，当用户试图修改视图信息时，数据库必须把它转化为对基表的某些信息的修改。对于比较复杂的试图，可能就不能进行修改了。

2.2 定义

简单创建视图的语法为：

CREATE VIEW 视图名 AS SELECT 查询语句;

对于单表的视图，我们可以像对待基表一样对其进行增删改操作（结果最终会作用于基表）。

2.2.1 创建语法

CREATE

　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 

　　[DEFINER = { user | CURRENT_USER }] 

　　[SQL SECURITY { DEFINER | INVOKER }]

VIEW view_name [(column_list)]

AS select_statement 

　　[WITH [CASCADED | LOCAL] CHECK OPTION]

ALGORITHM：创建视图使用的算法。UNDEFINED，默认值，不指定算法（没指定时一般采用merge算法）；MERGE，合并算法，在基于视图创建新的视图时，将创建旧视图所使用的SELECT语句与将要创建新的视图的SELECT语句进行合并处理，效率更高；TEMPTABLE，临时表算法，在基于视图创建新的视图时，先执行旧视图的SELECT语句，然后再执行新视图的SELECT语句，效率较低。

DEFINER：视图创建者。user 为指定创建的用户；CURRENT_USER为当前登录用户。

SQL SECURITY：视图的安全策略。DEFINER 默认值，验证是否拥有对视图本身的权限；INVOKER，验证对视图的权限及对视图所涉及到的表的权限。

WITH：更改视图数据时，对更改的数据进行检查。基本检查策略是，当更改视图中的数据时，如更改之后的结果不符合创建该视图的权限，则不允许。CASCADED和LOCAL都具备基础检查策略。LOCAL，如果该视图关联了其他视图，对视图进行数据操作时，只需满足当前视图的创建条件即可。CASCADED，既要符合当前视图的条件，也要符合关联视图的条件。

CREATE

ALGORITHM=MERGE

DEFINER=CURRENT_USER

SQL SECURITY INVOKER

VIEW student_view

AS SELECT * FROM student

WITH CASCADED CHECK OPTION;

通过GRANT <权限> ON <数据对象> TO <数据库用户> 来进行授权：

GRANT SELECT,UPDATE ON study.student_view TO 'root'@'localhost';

通过REVOKE <权限> ON <数据对象> FROM <数据库用户> 来移除授权：

REVOKE SELECT ON study.student_view FROM 'root'@'localhost';