mysql 存储程序

1
2
3
4
5

CREATE FUNCTION 存储函数名称([参数列表])
    RETURNS 返回值类型
BEGIN
    函数体内容
END

1
2
3
4
5
6
7

CREATE FUNCTION get_phone(qname VARCHAR (45))
    RETURNS VARCHAR(11)
BEGIN
RETURN (SELECT phone
        from t
        where name = qname);
END EOF

1
2
3
4
5
6
7
8
9

CREATE FUNCTION get_phone(qname VARCHAR (45))
    RETURNS VARCHAR(11)
BEGIN
    DECLARE ph varchar(11) default ""; # 局部变量
    SET @ii = 100; # 自定义变量
    SET ph = (select phone from t where name = qname); # 给局部变量赋值

    RETURN  ph ;
END EOF

1
2
3
4

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

1
2
3
4
5
6
7

CREATE PROCEDURE my_operate(pname varchar (45))
BEGIN
     SELECT * FROM t;
     INSERT INTO t(phone, name) VALUES("15214254125", "卢俊义");
     SELECT * FROM t;
     SELECT * from t where name = pname;
END EOF

1

参数类型 [IN | OUT | INOUT] 参数名 数据类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE PROCEDURE my_arg(
            in pname varchar (45),
            out ophone char(11),
            inout io_name varchar(45)
          )
BEGIN
     SELECT * FROM t;
     INSERT INTO t(phone, name) VALUES("15225632145", "公孙胜");
     SELECT * FROM t;
     SELECT phone from t where name = pname into ophone;
     SET pname = "公孙胜";
     SET io_name = "公孙胜";
END EOF

1

FETCH 游标名 INTO 变量1, 变量2, ... 变量n

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE PROCEDURE my_cursor()
BEGIN
    DECLARE v_phone char(11);
    DECLARE v_name varchar(45);

    DECLARE done INT DEFAULT 0;
    DECLARE t_cursor CURSOR FOR SELECT `phone`, `name` FROM t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN t_cursor;

    flag: LOOP
        FETCH t_cursor INTO v_phone, v_name;
        IF done = 1 THEN
            LEAVE flag;
        END IF;
        SELECT v_phone, v_name, done;
    END LOOP flag;

    CLOSE t_cursor;

END EOF

1
2
3
4
5
6
7
8

CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TRIGGER my_trigger
     BEFORE INSERT ON t
     FOR EACH ROW
     BEGIN
         IF NEW.name = 'admin' THEN
             SET NEW.name = 'valid';
         ELSEIF NEW.name = '' THEN
            SET NEW.name = '无名氏';
         ELSE
            SET NEW.my_join = CONCAT(NEW.name, "--", NEW.phone);
         END IF;
 END EOF

1

SET GLOBAL event_scheduler = ON;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点| 
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

1
2
3
4
5
6
7

CREATE EVENT insert_t1_event
ON SCHEDULE
AT '2022-01-03 11:20:11' # 或者 AT DATE_ADD(NOW(), INTERVAL 2 DAY)
DO
BEGIN
    INSERT INTO t(phone, name) VALUES('15210214254', '宋江');
END

1
2
3
4
5
6
7

CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'
DO
BEGIN
    INSERT INTO t(phone, name) VALUES('15210214254', '宋江');
END