本文讨论的是存储程序和视图，存储程序包括：存储过程和函数、触发器、事件。 https://dev.mysql.com/doc/refman/5.7/en/sql-syntax.html
存储过程和函数
语法
存储过程的body 是以BEGIN开头，END结尾的代码块，里面包含set、repeat loop语句
创建存储过程和函数
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,... ]])
[characteristic ... ] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,... ]])
RETURNS type
[characteristic ... ] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
删除存储过程和函数
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
修改存储过程和函数
ALTER PROCEDURE proc_name [characteristic ... ]
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
查看存储过程和函数的定义
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
查看存储过程和函数的状态
SHOW {PROCEDURE|FUNCTION} STATUS
[LIKE 'pattern' | WHERE expr]例子
procedure例子：
delimiter
CREATE PROCEDURE dorepeat (p1 INT)
BEGIN
SET @x = 0;REPEAT SET @x = @x + 1 ; UNTIL @x > p1 END REPEAT ;
END ;
call dorepeat(1000 );
select @x;
functions 例子
CREATE FUNCTION hello (s CHAR(20) )
RETURNS CHAR (50) DETERMINISTIC
RETURN CONCAT ('Hello, ' ,s,'!' ) ;select hello('world' );
查看dorepeat存储过程的定义
SHOW CREATE PROCEDURE dorepeat ;
触发器
语法
创建触发器
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name删除触发器DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
例子
例子1：
CREATE TABLE account (acct_num INT , amount DECIMAL (10 ,2 ));CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
SET @sum = 0 ;INSERT INTO account VALUES (137 ,14.98 ),(141 ,1937.50 ),(97 ,-100.00 );SELECT @sum AS 'Total amount inserted' ;
CREATE TABLE test1(a1 INT );CREATE TABLE test2(a2 INT );CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY );CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
b4 INT DEFAULT 0
);CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;DELETE FROM test3 WHERE a3 = NEW.a1;UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;END ;INSERT INTO test3 (a3) VALUES
(NULL ), (NULL ), (NULL ), (NULL ), (NULL ),
(NULL ), (NULL ), (NULL ), (NULL ), (NULL );INSERT INTO test4 (a4) VALUES
(0 ), (0 ), (0 ), (0 ), (0 ), (0 ), (0 ), (0 ), (0 ), (0 );INSERT INTO test1 VALUES
(1 ), (3 ), (1 ), (7 ), (1 ), (8 ), (4 ), (4 );结果：
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
8 rows in set (0.00 sec)
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
8 rows in set (0.00 sec)
| 2 |
| 5 |
| 6 |
| 9 |
5 rows in set (0.00 sec)
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
10 rows in set (0.00 sec)查看触发器
SHOW CREATE TRIGGER testref;
事件
语法
创建事件
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string' ]
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ... ]
[ENDS timestamp [+ INTERVAL interval] ... ]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}修改事件
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT ] PRESERVE ]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string' ]
[DO event_body]例子
DEMO1：创建事件
SELECT NOW();
CREATE EVENT e_totals
ON SCHEDULE AT '2018-09-02 16:04:19'
DO INSERT INTO test.test1 VALUES (20 );DEMO2：修改事件
CREATE EVENT myevent
ON SCHEDULE
EVERY 6 HOUR
COMMENT 'A sample comment.'
DO
UPDATE myschema.mytable SET mycol = mycol + 1 ;ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR ;视图
https://dev.mysql.com/doc/refman/5.7/en/views.html
语法
创建视图
CREATE
[OR REPLACE ]
[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 ]修改视图
ALTER
[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 ]删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]例子
CREATE TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3, 50), (5, 60);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
| 3 | 50 | 150 |
转播
