MySQL存储对象

MySQL存储对象

存储过程包含存储过程与存储函数

存储程序包含存储过程、触发器和事件

存储对象包含存储程序和视图

1、存储过程与存储函数

1、创建存储过程

delimiter //      #定义分隔符
CREATE PROCEDURE dorepeat1(p1 INT) #创建存储过程dorepeat1
BEGIN
SET @x = 0;		#设置变量x等于0
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; #执行循环，直到变量x的值大于p1
END
//
delimiter ;

CALL dorepeat1(10000);	#调用存储过程

SELECT @x;		#查询变量的值

2、创建存储函数

CREATE FUNCTION hello (s CHAR(20))		# 创建存储函数hello
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');

select hello('world');  # 调用存储函数

2、触发器

CREATE TABLE test1(a1 INT);  # 创建表test1
CREATE TABLE test2(a2 INT);	 # 创建表test2
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); # 创建表test3
CREATE TABLE test4(
 a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 b4 INT DEFAULT 0
);  # 创建表test4

delimiter |				# 定义分隔符
CREATE TRIGGER testref BEFORE INSERT ON test1 # 创建触发器testref,在往test1插入数据前，触发执行
 FOR EACH ROW	
 BEGIN
 INSERT INTO test2 SET a2 = NEW.a1;    # 往test2表插入数据
 DELETE FROM test3 WHERE a3 = NEW.a1;  # 删除test3表满足条件的数据
 UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;  # 更新test4表满足条件的数据
 END;
|
delimiter ;
#往test3表插入数据
INSERT INTO test3 (a3) VALUES
 (NULL), (NULL), (NULL), (NULL), (NULL),
 (NULL), (NULL), (NULL), (NULL), (NULL);
#往test4表插入数据
INSERT INTO test4 (a4) VALUES
 (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
#往test1表插入数据，触发器testref将会执行
INSERT INTO test1 VALUES 
 (1), (3), (1), (7), (1), (8), (4), (4);

在触发器主体中，OLD和NEW关键字使您能够访问受触发器影响的行中的列。OLD和NEW是MySQL触发器的扩展

在插入触发器中，只能使用NEW.col_name；没有旧行。在删除触发器中，只能使用OLD.col_name；没有新行。在更新触发器中，您可以使用OLD.col_name在更新行之前引用行的列，并使用NEW.col_name在更新行后引用行的列。

触发器不能使用CALL语句来调用将数据返回到客户端或使用动态SQL的存储过程。(允许存储的过程通过OUT或INOUT参数将数据返回到触发器。)

触发器不能使用显式或隐式开始或结束事务的语句，如启动事务、提交或回滚。（允许回滚到保存点，因为它不会结束事务。）

3、事件

MySQL事件调度程序管理事件的调度和执行，即根据调度运行的任务

1、启用和禁用任务调度

SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;
#同样的，也可以用下面替换
SET GLOBAL event_scheduler = OFF;
SET @@GLOBAL.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@GLOBAL.event_scheduler = 0;

也可以在命令行启动服务时，添加参数

--event-scheduler=DISABLED

同样也可以在服务器配置文件(my.cnf或Windows的my.ini)中，包含服务器读取它的行(例如，在[mysqld]部分中)：

event_scheduler=DISABLED

2、创建事件

CREATE EVENT e_insert
 ON SCHEDULE
 EVERY 7 SECOND
 DO
 INSERT INTO myschema.mytable;

4、视图

# 创建表t
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;
# 查询视图
SELECT * FROM v;