-- 存储例程;
1.存储例程是存储在服务器中的一组指定的SQL语句:
1.客户机无需不断重新发出单个语句,而可以改为引用存储例程;
2.存储例程类型:
1.存储过程:通过CALL语句调用过程,这些过程可以使用输出变量或结果集传回值;
2.存储函数:在语句中调用函数,这些函数可返回标量值;
-- 存储例程的使用;
1.集中式客户机功能:通过存储例程,您可以在数据库中集中创建一个语句或一系列语句,以供使用不同编程语
言编写或在不同平台上运行的多个客户机应用程序使用;
2.安全性:
1.存储例程为需要最高安全级别的应用程序提供了一个解决方法;
2.例如,银行针对所有常用操作均使用存储过程和函数,这提供了一致,安全的环境;
3.可对例程进行编码,以确保正确记录了每个操作,在此类设置中,应用程序和用户无法直接访问数据库表,只能执行特定的存储例程;
4.在Oracle中还可以针对存储过程进行加密;
3.性能改进:
1.客户机按名称调用存储例程,而不发送例程中所包含的整组语句;
2.因为服务器和客户机之间需要发送的信息变少了,所以存储例程可提升性能;
3.哪岂不是数据库服务器端的压力变重了?
4.函数库:通过存储例程,可以在数据库服务器中使用函数库,这些库用作数据库的API;
-- 存储例程:问题;
1.增加了服务器负载:
1.在数据库自身中执行存储例程可增加服务器负载并降低应用程序的性能;
2.可以运行测试并运用常识来确保在数据库本身中包含逻辑所带来的方便比可能引发的性能问题更为显著;
2.开发工具有限:
1.MySQL中支持存储例程的开发工具不像在更通用的编程语言中那样成熟和明确;
2.此局限性会使存储例程的编写和调试过程更加困难,在决策过程中需要加以考虑;
3.语言功能和速度有限:
1.虽然在许多情况下在数据库本身中包含逻辑具有很大的优势,但是与其他编程语言相比,在可实现的内容方面仍有局限;
2.存储例程在数据库上下文中执行,与客户机应用程序中的例程相比,在处理大量数据时性能较好,但是客户机应用程序语言可能具有更强大,更通用的处理,集成或其他库功能;
4.调试和概要分析功能有限;
-- 执行存储例程;
1.执行过程:使用CALL语句来调用存储过程,存储过程使用输出变量或结果集传回值;
2.执行函数:从语句内部调用函数(即,通过调用相应函数的名称),函数返回标量值;
3.每个存储例程均与特定数据库相关联,有多重含义:
1.USE :调用例程时,MySQL会在该例程运行期间执行隐式USE ,不能在存储例程内发出USE语句;
2.限定名称:可使用例程的数据库名称限定例程名称;执行此操作可引用当前数据库以外的例程;例如,要调用与test数据库相关联的存储过程p或函数f,请使用CALL test.p()或test.f();
3.删除数据库时,也会删除与其关联的所有存储例程;
4.SELECT语句:
1.仅限存储过程:函数不可以使用;
2.直接将结果集发送到客户机;
-- 存储过程:示例;
mysql> USE world;
mysql> DELIMITER //
mysql> CREATE PROCEDURE record_count ()
BEGIN
SELECT (SELECT COUNT(*) FROM Country) 'CountryCount', (SELECT COUNT(*) FROM City) 'CityCount', (SELECT COUNT(*) FROM CountryLanguage) 'CLCount';
END//
mysql> DELIMITER ;
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1(IN v_name varchar(20))
BEGIN
SELECT t1.Name, sum(t2.population) FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode AND t1.name = v_name GROUP BY t1.Name;
END$$
mysql> DELIMITER ;
mysql> CALL p1('China');
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(IN v_name varchar(20), OUT v_population INT)
BEGIN
SELECT sum(t2.population) INTO v_population FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode AND t1.name = v_name GROUP by t1.Name;
SELECT 0;
END$$
mysql> call p2('China', @v_1);
mysql> select @v_1;
1.复合语句:
1.通过在存储例程中使用BEGIN...END语法并使用触发器,可以创建复合语句;
2.BEGIN...END块可包含零个或多个语句,空复合语句是合法的,而且复合语句中的语句数量没有上限;
2.分隔符:
1.在BEGIN...END语法中,必须使用分号[;]终止每个语句;
2.由于MySQL客户机使用分号作为SQL语句的默认终止字符,在以交互方式或针对批处理使用MySQL命令行客户机时,必须使用DELIMITER语句更改此设置;
3.此更改可确保客户机不会将复合语句中的分号解释为语句分隔符,并确保客户机不会过早地将CREATEPROCEDURE语句发送到服务器;当创建存储例程的语句以[//]终止时,客户机会先将该语句发送到服务器,然后再发出第二个DELIMITER语句将语句分隔符重置为分号;
-- 存储函数:示例;
mysql> USE world;
mysql> DELIMITER //
mysql> CREATE FUNCTION pay_check (gross_pay FLOAT(9,2), tax_rate FLOAT (3,2))
RETURNS FLOAT(9,2) NO SQL
BEGIN
DECLARE net_pay FLOAT(9,2) DEFAULT 0;
SET net_pay=gross_pay - gross_pay * tax_rate;
RETURN net_pay;
END//
mysql> DELIMITER ;
1.RETURNS子句:用于确定此函数要返回的值的类型;
2.特征:通过多个特征,可确定有关例程所使用的数据的性质;
1.CONTAINS SQL表示例程不包含用于读取或写入数据的语句(为默认值);
2.NO SQL表示例程不包含任何SQL语句;
3.READS SQL DATA表示例程包含用于读取数据的语句(例如,SELECT)而不包含用于写入数据的语句;
4.MODIFIES SQL DATA表示例程包含用于写入数据的语句(例如,INSERT/DELETE/UPDATE);
5.在启用了二进制日志记录后,如果创建函数时未指定特征项,则MySQL会产生一个错误;
3.DECLARE语句:
1.在存储例程中使用DECLARE语句来声明本地变量并初始化用户变量;
2.可将DEFAULT子句添加到DECLARE语句的结尾,以便为用户变量指定初始值;如果省去DEFAULT子句,则用户变量的初始值为NULL;
4.SET语句:通过SET语句,您可以使用=或:=作为赋值运算符来向定义的变量赋值;
5.RETURN语句:用于终止存储函数的执行,并将值表达式返回给函数调用方;
-- 检查存储例程;
1.SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION:
1.这些语句为MySQL扩展,类似于SHOW CREATE TABLE;
2.这些语句返回可用于重新创建指定例程的具体字符串;
3.这些语句的主要限制之一是您必须知道过程或函数的名称,并且必须确定其为过程或函数,然后才能尝试查看相应信息;
2.SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS:
1.这些语句特定于MySQL;
2.它们可返回例程的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.这些语句有一个优点:可基于LIKE模式显示特定例程,如果未指定任何模式,则会根据所使用的语句,列出所有存储过程或所有存储函数的信息;
3.INFORMATION_SCHEMA.ROUTINES:包含存储例程(过程和函数)的相关信息,并返回可同时在SHOW CREATE ...和SHOW ... STATUS语句中找到的大部分详细信息,以包 含用于创建存储例程的实际语法;
mysql> SELECT routine_name, routine_schema, routine_type, definer
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_name LIKE 'film%';
4.MySQL系统数据库中与编程组件关联的表
1.mysql.event表,包含MySQL服务器中所存储事件的相关信息;
2.mysql.proc表,包含MySQL服务器中的存储过程和函数的相关信息;
3.mysql.procs_priv表,为引用存储过程的用户提供访问控制授予详细信息;
-- 存储例程和执行安全性;
1.存储过程和函数的使用涉及多个特权:
1.CREATE ROUTINE:创建存储例程;
2.ALTER ROUTINE:更改或删除存储例程;
3.EXECUTE:执行存储例程;
4.GRANT OPTION:将特权授予其他帐户;
2.默认操作:
1.创建存储例程时,MySQL会自动向您的帐户授予对该例程的EXECUTE和ALTER ROUTINE特权;
2.拥有撤消特权以及GRANT OPTION特权的用户稍后可撤消或删除这些特权;在创建例程后,可以通过发出SHOW GRANTS语句来验证这些特权;
3.授予特权:
1.当在全局级别或数据库级别授予所有特权时,GRANT ALL语句包括除GRANT OPTION之外的所有存储例程特权;要授予GRANT OPTION特权,请在该语句结尾包含WITH GRANT OPTION子句;
2.可以在单个例程级别授予EXECUTE,ALTER ROUTINE和GRANT OPTION特权,但仅限于已经存在的例程;
3.要授予对单个例程的特权,可使用其数据库名称限定例程,并提供关键字PROCEDURE或FUNCTION以指示例程类型;
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost' WITH GRANT OPTION;
-- 测验;
A
-- 触发器;
1.据库触发器是数据库中所维护的命名数据库对象,将在修改表中数据时被激活;
2.功能:
1.在插入或更新数据之前对数据进行检查并验证删除和更新;
2.充当数据过滤器,在插入或更新之前修改超出范围的数据;
3.修改INSERT/UPDATE/DELETE的行为方式;
4.对于不支持外键的存储引擎,模仿外键的行为;
5.提供日志记录功能;
6.自动创建汇总表;
3.功能概括:
1.触发器可以提高表中数据的功能和安全性级别;
2.可以使用触发器控制对特定数据的访问权限,执行特定日志记录或对数据本身进行审计;
-- 创建触发器;
1.CREATE TRIGGER语句;
CREATE TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } ON table_name
FOR EACH ROW triggered_statement
2.例子:
CREATE TRIGGER City_AD AFTER DELETE
ON City FOR EACH ROW
INSERT INTO DeletedCity(ID, Name) VALUES (OLD.ID, OLD.Name);
1.trigger_name是为触发器指定的名称;
2.table_name是要与触发器关联的表的名称;
3.BEFORE和AFTER指示激活触发器的时间(是在触发事件之前或是之后);
4.而INSERT/UPDATE/DELETE指示具体的事件;
5.表名OLD和NEW是指触发器可查看的虚拟表,这些表分别包含UPDATE或DELETE语句所修改的数据的旧版本,或INSERT或UPDATE语句所添加的数据的新版本;
补充:模拟物化视图的例子;
1.创建一个汇总表;
CREATE TABLE ps(name char(52), population BIGINT);
INSERT INTO ps
SELECT t1.NAME, sum(t2.population) FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode GROUP by t1.Name order by 2 desc;
2.创建更新触发器,一种动作时间的触发器只能创建一个;
DELIMITER $$
CREATE TRIGGER City_AU AFTER UPDATE
ON City FOR EACH ROW
UPDATE ps SET population = population + new.population - old.population;$$
DELIMITER ;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
3.更新记录用来模拟记录发生变化;
SELECT * FROM City WHERE id = 1890;
UPDATE City SET population = population + 1 WHERE id = 1890;
4.查看汇总表反应变化结果;
SELECT * FROM ps WHERE name = 'China';
SELECT sum(population) FROM City WHERE CountryCode='CHN';
-- 触发器事件;
1.BEFORE和AFTER关键字是指触发器的激活时间,相对于数据修改语句(INSERT/UPDATE/DELETE)将更改写入底层数据库的时间;
2.BEFORE关键字可使触发器在涉及的数据修改之前执行,可使用BEFORE触发器捕获无效数据条目并在写入表之前对其进行更正或拒绝;
3.AFTER关键字定义在数据修改成功后执行的触发器,可使用AFTER触发器记录或审计数据库中的数据修改;
-- 触发器错误处理;
MySQL按如下方式处理触发器执行期间发生的错误:
1.BEFORE触发器失败:包含相应行操作的事务将回滚;
2.AFTER触发器执行:BEFORE触发器事件和行操作必须成功执行;
3.对于非事务表,事务不可用:只有触发了触发器的语句会回滚,但在发生错误之前执行的所有更改仍然会生效;
-- 检查触发器;
1.SHOW CREATE TRIGGER trigger_name:
1.此语句返回可用于重新创建指定触发器的具体字符串;
2.您必须知道触发器的名称才能运行此语句;
3.对于SHOW CREATE TRIGGER语句,不存在LIKE或WHERE语法;
2.SHOW TRIGGERS:
1.此语句为MySQL扩展;
2.它可返回触发器的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.此语句有一个优点:可基于LIKE模式或WHERE子句中提供的条件来显示特定触发器;如果未指定条件,则此语句会显示所有触发器的信息;
3.INFORMATION_SCHEMA.TRIGGERS:
1.包含SHOW命令所显示的所有数据;
2.完整地呈现在所有数据库中可用的触发器;
-- 删除触发器;
1.使用如下语法可显式删除触发器:
1.DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
2.使用IF EXISTS可防止因尝试删除不存在的触发器而出现的错误;
2.通过删除以下项,可隐式删除触发器:
1.针对其定义触发器的表;
2.包含触发器的数据库;
-- 对触发器的限制;
1.不允许使用的语句包括:
1.SQL预处理语句;
2.显示或隐式COMMIT和ROLLBACK;
3.返回结果集的语句(无法返回结果集,请通过使用SELECT ...INTO var_list,或者通过使用光标和FETCH语句方法在触发器中处理结果集);
4.FLUSH语句;
5.用于修改要应用触发器的表的语句;
2.以下更改不会触发触发器:
1.级联外键所导致的更改;
2.在基于行的复制过程中导致的更改;
-- 触发器特权;
1.要执行CREATE TRIGGER和DROP TRIGGER命令,需要有TRIGGER特权;
2.需要有其他特权才能在触发器中使用OLD和NEW:
1.要使用SET NEW.col_name = value为列赋值,需要对该列拥有UPDATE特权;
2.要在表达式中使用NEW.col_name以引用新的列值,需要对该列拥有SELECT特权;
-- 测验;
b
-- 事件;
1.事件:
1.MySQL事件是按调度表运行的任务,这些事件可称为“调度事件”;
2.创建事件时,会将其创建为命名数据库对象,其中包含在特定时间执行或定期重复发生的SQL语句(或存储过程);
3.与UNIX crontab或Windows任务调度程序类似;
CREATE EVENT event_name
ON SCHEDULE schedule DO sql_statement
4.必须选项:
1.event_name:事件是模式对象,与表,存储过程和触发器一样;event_name必须是有效的标识符,且可以按正常方式通过模式名称进行引用和/或限定;
2.schedule:调度表是一种规则,用于指定MySQL执行与事件相关联的操作的时间;
3.sql_statement:必须包含按调度表执行的有效SQL语句或存储过程语句;此语句受到的限制与适用于存储函数和动态SQL的限制相同;通常会使用CALL语句调用过程来执行实际操作;
2.事件调度程序
1.创建事件后,该事件将存储在数据库中,以便按调度表执行;
2.event_scheduler线程会监视所有事件的调度表,在到达调度表中的时间时,该线程会启动一个新的线程来执行每个事件;
3.默认情况下,event_scheduler线程设置为OFF,您必须显式启用该线程,方法为修改全局event_scheduler服务器变量的值,将其设置为ON;还可以通过将服务器变量添加到选项文件中(以便更改在启动时生效)或动态使用SET语法来实现;
4.如果在event_scheduler设置为DISABLED的情况下启动服务器,则无法在MySQL正在运行时通过SET语句将其启用;而必须停止MySQL并在启用该选项的情况下重新启动MySQL;
5.启用event_scheduler线程后,您可以在SHOW PROCESSLIST的输出(以及INFORMATION_SCHEMA等效项PROCESSLIST)中看到该线程;
3.事件语句:
1.SET GLOBAL event_scheduler = {ON | OFF};
2.CREATE EVENT:创建事件;
3.ALTER EVENT:更改事件;
4.DROP EVENT:删除事件;
-- 调度表;
1.调度表是用于指定事件执行时间的规则;
2.有两种类型的调度操作:
1.执行一次(使用AT关键字);
2.重复执行(使用EVERY关键字):必须定义事件的重复频率;还可以定义一个时间段,以确定应重复执行事件的期限;
3.由事件调度程序执行调度事件:
1.事件调度程序是mysqld进程中的一个单独线程,负责执行调度事件;
2.调度程序会检查是否应执行事件,如果应执行事件,则会创建新的连接来执行操作;
4.使用事件来自动地定期执行任务;
5.示例:
1.表数据的常规CSV转储:加载数据仓库或者将数据导出到文件中;
2.ANALYZE TABLE;
3.自动地定期执行(维护)任务,如更新汇总表或刷新查询中的表(物化视图仿真);
补充:
1.创建一个一次性执行的作业,2分钟10秒后插入一条数据到test.t中;
CREATE EVENT e_at_insert_to_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL '2:10' MINUTE_SECOND
DO INSERT INTO test.t values(NOW());
2.创建一个重复执行的作业,每天3点执行一次;
CREATE EVENT e_every_insert_to_table
ON SCHEDULE EVERY 1 DAY STARTS date_add(curdate(), INTERVAL 3 HOUR)
DO INSERT INTO test.t values(NOW());
3.DO子句中使用BEGIN...END关键字;
DELIMITER $$
CREATE EVENT e_event_do
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
INSERT INTO test.t values(NOW());
COMMIT;
END$$
DELIMITER ;
补充:
1.SCHEDULE子句的语法:
AT timestamp [+ INTERVAL interval] | EVERY interval [STARTS timestamp [+ INTERVAL interval]] [ENDS timestamp [+ INTERVAL interval]]
2.SCHEDULE子句可包含以下变量元素:
1.timestamp:DATETIME或TIMESTAMP类型的表达式;
2.interval:用于指定持续时间,持续时间的表示方法为,指定一个整数数量,后跟用于定义特定种类持续时间的关键字;
- YEAR
- QUARTER
- MONTH
- DAY
- HOUR
- MINUTE
- WEEK
- SECOND
- YEAR_MONTH
- DAY_HOUR
- DAY_MINUTE
- DAY_SECOND
- HOUR_MINUTE
- HOUR_SECOND
- MINUTE_SECOND
-- 事件调度程序和特权;
1.必须拥有SUPER特权才能设置全局event_scheduler变量;
2.必须拥有EVENT特权才能创建,修改或删除事件;
3.使用GRANT分配特权(仅限在模式级别):
mysql> GRANT EVENT ON myschema.* TO user1@srv1;
mysql> GRANT EVENT ON *.* TO user1@srv1;
4.使用REVOKE取消事件特权:
1.REVOKE EVENT ON myschema.* FROM user1@srv1;
2.撤消用户帐户的EVENT特权不会删除或禁用该帐户已创建的任何事件;
5.mysql表:
1.用户的EVENT特权存储在mysql.user和mysql.db表的Event_priv列中;
2.在这两种情况下,该列均存储值“Y”或“N”之一,“N”是默认值;仅当给定用户拥有全局EVENT特权时,该用户的mysql.user.Event_priv值才会设为“Y”;
3.对于模式级别的EVENT特权,GRANT会在mysql.db中创建一个行,并按如下所示设置该行的列值:
1.Db:模式的名称;
2.User:用户的名称;
3.Event_priv:“Y”;
4.不必直接操作这些表,因为GRANT EVENT和REVOKE EVENT语句会对其执行所需的操作;
-- 事件执行特权;
1.事件使用事件定义者的特权进行执行:如果定义者无权执行某任务,则事件无法执行该任务;
2.示例
1.user1@srv1只能为myschema创建SELECT事件:
CREATE EVENT e_store_ts
ON SCHEDULE EVERY 10 SECOND
DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
2.由于缺少INSERT特权,该事件不会在表中创建任何行;
3.可以通过错误日志查看对应的信息;
-- 检查事件;
1.SHOW CREATE EVENT event_name:
1.此语句显示重新创建给定事件所需的CREATE EVENT语句;
2.必须提供事件名称才能查看该事件的相关信息;
2.SHOW EVENTS:
1.此语句为MySQL扩展;
2.它可返回事件的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.此语句有一个优点:可基于LIKE模式或WHERE子句中提供的条件来显示特定事件
3.INFORMATION_SCHEMA.EVENTS;
-- 删除事件;
1.使用如下语法可显式删除事件:DROP EVENT [IF EXISTS] [schema_name.]event_name;
2.使用IF EXISTS可防止因尝试删除不存在的事件而出现的错误;
3.您必须对包含要删除的事件的数据库拥有EVENT特权;
-- 测验;
b
-- SIGNAL和RESIGNAL;
1.MySQL支持使用SIGNAL和RESIGNAL:
1.可用于引发特定的SQLSTATE;
2.与其他语言中的异常处理类似;
3.在处理错误时用于高级流控制;
4.这些命令为存储例程和触发器提供了一种将错误返回给应用程序或最终用户的方法;
2.使用SIGNAL抛出错误或警告状态:
1.SIGNAL用于向处理程序,应用程序的外侧部分或客户机提供错误信息;
2.例如,在存储过程中使用SIGNAL向调用该存储过程的代码抛出错误;该代码可以以适当的方式处理该错误;
3.使用RESIGNAL转发先前的SIGNAL所引发的错误或警告状态;
1.RESIGNAL用于在条件处理程序执行期间传递可用的错误条件信息;
2.可在存储过程或函数,触发器或事件内的复合语句中使用RESIGNAL;
3.例如,如果存储过程p调用了存储过程q,且q使用SIGNAL抛出一个错误,则p可声明一个处理程序,以处理来自q的信号;p 中的处理程序可使用RESIGNAL语句向调用p的代码抛出相同的错误信息;
-- 课后练习;
补充:动态执行sql语句:
1.在ORACLE中动态执行sql语句使用:EXECUTE IMMEDIATE 'sql_statement';
2.在MySQL中动态执行sql的语法:
1.PREPARE stmt_name FROM preparable_stmt;
2.EXECUTE stmt_name [USING@var_name [,@var_name ]...];
3.{DEALLOCATE | DROP} PREPARE stmt_name;
3.例子:
1.准备sql语句:SET v_sql= concat('SELECT', ' * ', ' FROM ', ' DUAL;');
2.SET @v_sql=v_sql; -- 注意很重要,将连成成的字符串赋值给一个变量,可以之前没有定义,但要以@开头;
3.PREPARE stmt FROM @v_sql; -- 预处理需要执行的动态SQL,其中stmt是一个变量;
4.EXECUTE stmt; -- 执行SQL语句,如果sql语句中有变量的话,可以使用USING子句后面跟变量的值;
5.DEALLOCATE PREPARE stmt; -- 释放掉预处理段;