mysql 视图 调用 存储过程 mysql视图和存储过程

MySQL技术内幕——视图和存储程序

• 视图和存储程序

• 使用视图
• 使用存储程序

• 复合语句和语句分隔符
• 存储函数和存储过程
• 触发器
• 事件

• 视图和存储程序的安全性

视图和存储程序

• 存储程序
  是存储函数，存储过程，触发器和事件的总称。
• 存储例程
  特指存储函数，存储过程。

使用视图

视图是一个虚表，它是在表或其他视图的基础上，使用 SELECT 语句来定义的。
查询视图就等效于查询定义它的那条语句，只是视图将细节隐藏了起来。
视图定义里可以包含表达式计算和连接等操作，因此查询视图更易于编写出简单的用于获得所要信息的查询。

一个基本的视图可以只选择表的部分列。

定义一个视图，让它只检索想要的那些列：

CREATE VIEW vpres AS
SELECT last_name, first_name, city, state FROM president;

在视图里也可以使用 SELECT * ，并且只会获得在视图定义里通过名字指定的那些列：

SELECT * FROM vpres;

视图是逻辑语句，逻辑语句的执行结果是一个表。
定义视图后，直接通过引用视图对象即可获得视图关联逻辑语句执行结果。
创建视图时，需拥有对于视图的CREATE VIEW权限，拥有对SELECT语句所选列的操作权限。
及拥有对该语句里其他地方所引用的所有列的SELECT权限。

使用视图时，只能引用视图结果表中存在的列。
定义视图时，显式指定结果表列名。

CREATE VIEW vpres2(ln, fn) AS
SELECT last_name, first_name FROM president;

使用存储程序

存储程序是存储函数，存储过程，触发器和事件的统称。

复合语句和语句分隔符

复合语句由一个BEGIN和END块构成，其间可以包含任意数量的语句。
对于匿名用户，则以“delimiter ”代替。

CREATE PROCEDURE greetings()
BEGIN
	DECLARE user CHAR(77) CHARACTER SET utf8;
	SET user = (SELECT CURRENT_USER());
	IF INSTR(user, '@') > 0 THEN
		SET user = SUBSTRING_INDEX(user, '@', 1);
	END IF;
	IF user = ' ' THEN         # 匿名用户
		SET user = 'earthling';
	END IF;
	SELECT CONCAT('Greetings, ', user, '!') AS greeting;
END;

存储函数和存储过程

存储函数常用在表达式里，会执行计算并返回一个值。
存储过程是用CALL语句来调的独立操作，不能用在表达式里。

适合使用存储过程的情况有两种：
１．只需执行动作，不需返回值。
２．运算结果需返回多个结果集。

存储函数可用CREATE FUNCTION来创建；
存储过程可用CREATE PROCEDURE创建。

CREATE FUNCTION func_name([param_list])
	RETURNS type
	routine_stmt

CREATE PROCEDURE proc_name([param_list])
	routine_stmt

mysql> delimiter $
mysql> CREATE FUNCTION count_born_in_year(p_year INT)
	-> RETURNS INT
	-> READS SQL DATA
	-> BEGIN
	-> 	RETURN (SELECT COUNT(*) FROM president WHERE YEAR(birth) = p_year);
	-> END$
mysql> delimiter ;

SELECT count_born_in_year(1908);

函数无法返回多个值。
要让函数返回多个值，一种办法是编写多个函数，然后在同一条语句里调用所有这些函数；
另一种办法是使用一个存储过程，通过它的OUT参数“返回”这些值。
存储过程应负责计算这些期望值，并把它们赋值给各个参数，在过程返回后，调用者即可访问这些参数。

如果定义的存储函数与某个内建函数同名，则调用它时需用数据库名对函数名进行限定，以避免歧义。

存储过程与存储函数相似，不同之处在于它没有返回值。
它没有RETURNS子句或任何RETURN语句。

mysql> delimiter $
mysql> CREATE PROCEDURE show_born_in_year(p_year INT)
	-> BEGIN
	-> 	SELECT first_name, last_name, brith, death
	-> 	FROM president
	-> 	WHERE YEAR(birth) = p_year;
	-> END$
mysql> delimiter ;

由于没有返回值，存储过程不同于存储函数，不能用在表达式里，只能通过CALL语句来调用它。

mysql> CALL show_born_in_year(1908);

在mysql客户端执行上述语句时，语句的结果集并不是以过程值方式返回的，而是被发送到了客户端。

一个过程可生成多个结果集，其中的每一个结果会被依次发送到客户端。
存储例程也可用来修改表。
存储函数不能对调用该函数的那条语句正读取或写入的表进行修改。
存储过程若从存储函数里调的，也需如此；否则，不用。

存储函数和存储过程的权限

存储函数和存储过程隶属于某个数据库。
如果想创建存储函数或存储过程，则需有该数据库的CREATE ROUTINE权限。
默认下，创建存储例程时，如还没获得EXECUTE和ALL ROUTINE权限，则服务器会自动授予你这些权限，以便你可执行或删除例程。
在删除例程时，服务器将自动撤销那些权限。

如果服务器启用了二进制日志功能，则存储函数还需服从另外一些限制条件，这些条件主要用于保证二进制日志在执行备份和复制操作时的安全性。
具体为，不允许创建不确定或会修改数据的存储函数，这些限制也适合触发器的创建过程。

• 如果未启用 log_bin_trust_function_creators 系统变量，就需具备SUPER权限，才能创建存储函数。
  你创建的所有函数都需是确定的，并且不得修改数据。

为表明此点，需用DETERMINISTIC，NO SQL或READS SQL DATA三者之一来定义存储函数。
如：

CREATE FUNCTION half (p_value DOUBLE)
RETURNS DOUBLE
DETERMINISTIC
BEGIN
	RETURN p_value / 2;
END;

• 如启用了log_bin_trust_function_creators系统变量，就没有任何限制了。

存储过程的参数类型

存储过程的参数分为３种类型。
对IN，调用者会把一个值传递到过程里。
这个过程可对值进行修改，修改对调用者不可见。

OUT，用于返回值。
INOUT，传入此值，且修改后调用者可见。

默认类型为 IN。

CREATE PROCEDURE count_students_by_sex(OUT p_male INT, OUT p_female INT)
BEGIN
	SET p_male = (SELECT COUNT(*) FROM student WHERE sex = 'M');
	SET p_female = (SELECT COUNT(*) FROM student WHERE sex = 'F');
END;

关键字 IN、OUT、INOUT 都不能用于存储函数，触发器和事件。
对存储函数，所有参数都像IN。
触发器和事件则无参数。

触发器

触发器是与特定表相关联的存储过程，其定义会在执行表的INSERT，DELETE或UPDATE语句时，被自动激活。

触发器的定义包含有一条会在触发器被激活时执行的语句。

触发器具有以下几个好处。

• 触发器可检查或修改将被插入或用来更新行的那些新的数据值。
  故，可用于数据完整性约束，还可用于对输入数据进行过滤。
• 触发器可基于某个表达式来为列提供默认值，甚至可为那些只能使用常量默认值进行定义的列类型提供值。
• 触发器可在行删除，更新前先检查行的当前内容。

CREATE TRIGGER trigger_name             # 触发器的名字
	{BEFORE | AFTER}                    # 触发器激活的时机
	{INSERT | UPDATE | DELETE}          # 激活触发器的语句
	ON tbl_name                         # 关联表
	FOR EACH ROW trigger_stmt;          # 触发器内容

trigger_stmt为触发器的语句体，即在触发器被激活时需执行的语句。
在触发器的语句体里，可使用NEW.col_name来引用在INSERT或UPDATE触发器里将被插入或修改的那个新行里的列。
OLD.col_name可用来引用在DELETE或UPDATE触发器里将被删除或修改的原行里的列。
如果想用BEFORE触发器改变列值，且想在值存储到表中之前改变它，可用SET NEW.col_name = value。
例子：

mysql> CREATE TABLE t (percent INT, dt DATETIME);
mysql> delimiter $
mysql> CREATE TRIGGER bi_t BEFORE INSERT ON t
	-> 	 FOR EACH ROW BEGIN
	-> 		IF NEW.percent < 0 THEN
	-> 			SET NEW.percent = 0;
	-> 		ELSEIF NEW.percent > 100 THEN
	-> 			SET NEW.percent = 100;
	-> 		END IF;
	-> 	 END$   NEW.dt = CURRENT_TIMESTAMP;
	-> 	 END$;
mysql> delimiter ;

触发器属于表，需要拥有表的TRIGGER权限，才能为表创建或删除触发器。

事件

MySQL有一个事件调度器，可定时激活多个数据库操作。

事件是一个与计划相关联的存储程序。
计划会定义事件执行的时间或次数，且还可定义事件何时强行退出。

默认下，事件调度器不会运行，因此需先启用它才能使用事件。
把下面两行内容放到某个在服务器启动时会被读取的选项文件里：

[mysqld]
event_scheduler=ON

如果想在运行时停止或启动事件调度器，则可更改event_scheduler系统变量。

SET GLOBAL event_scheduler = OFF;    # 或者0
SET GLOBAL event_scheduler = ON;     # 或者1

设置GLOBAL变量需SUPER权限。

如果服务器启动时设置event_scheduler为DISABLED，则运行时可查看其状态，无法更改。

创建事件的方法是，使用 CREATE EVENT 语句，其基本语法如下：

CREATE EVENT event_name
	ON SCHEDULE
		{AT datetime | EVERY expr interval [STARTS datetime] [ENDS datetime]}
	DO event_stmt

事件属于数据库，需拥有数据库的EVENT权限才能创建或删除其触发器。

CREATE EVENT expire_web_session
	ON SCHEDULE EVERY 4 HOUR
	DO
		DELETE FROM web_session
		WHERE last_visit < CURRENT_TIMESTAMP - INTERVAL 1 DAY;

在EVERY子句后面，还可包含可选子句STARTS datetime和ENDS datetime，用于指定事件的第一次和最后一次执行时间。

默认下，EVERY事件在被创建后会立刻开始第一次执行，且会定时持续执行，永不停止。

DO子句负责定义事件的语句体部分，是一条在事件被触发时执行的SQL语句。
可以是一条简单语句，也可是使用BEGIN和END的复合语句。

如果创建只执行一次的事件，用AT调度类型，而不要使用 EVERY 。

CREATE EVENT one_shot
	ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
	DO ...;

如果要禁用某个事件，让它不再执行，或要重新激活某个被禁用的事件，可用ALTER EVENT：

ALTER EVENT event_name DISABLE;
ALTER EVENT event_name ENABLE;

视图和存储程序的安全性

定义视图时，要设置一条用于以后调的SELECT语句。
定义存储程序时，也有相同操作，即需要定义一个以后执行的对象。
后续执行用户可能不是创建其的用户。

默认下，服务器会使用定义该对象的那个用户的账户。

定义存储程序或视图时，显式指定定义者的方法是，在该对象的CREATE语句里包含一个DEFINER=accout子句。

CREATE DEFINER = 'sampadm'@'localhost' PROCEDURE count_students()
	SELECT COUNT(*) FROM student;

如果没给出DEFINER子句，默认使用同一个账户。

如果你拥有SUPER权限，则可使用任何一种语法正确的账户名来作为DEFINER值。
账户不存在时，出现警告。
如果没SUPER权限，则只能把定义者设为你自己的账户。
既可用完整账户名，也可用CURRENT_USER。

对视图和存储例程（包括存储函数，存储过程），还可以用SQL SECURITY特性，用于实现对执行时访问检查的附加控制。
SQL SECURITY特性的允许值为DEFINER（以定义者权限执行）或INVOKER（以对象调用者权限执行）。

适用SQL SECURITY INVOKER场合是：只想以调用者所拥有的权限来执行视图或存储程序。

例如：以调用者权限访问数据库的某个表。

CREATE SQL SECURITY INVOKE VIEW v
	AS SELECT CONCAT(User, '@', Host) AS Account, Password FROM mysql.user;

服务器会自动调触发器和事件，它们没有SQL SECURITY特性，总是以定义者权限来执行。

如果视图或存储程序以定义者权限运行，但定义者账户不存在，
则出现错误。

在视图或存储程序里，CURRENT_USER()会默认返回与该对象的DEFINER属性相对应的账户。
对于视图和定义时带有SQL SECURITY INVOKER特性的存储例程，CURRENT_USER()会返回调用用户的账户。

学习参考资料：

《MySQL技术内幕》第5版