mysql存储过程学习笔记

通过学习这篇博客得出的心得https://blog.csdn.net/qq_33157666/article/details/87877246

一些乱七八糟的笔记

1.存储过程的定义看起来像定义一个方法（有形式参数，有返回值），调用完后还可以修改传入参数的值（java中的地址传递），虽然很像方法，但不能搞混，因为存储过程是不支持重载的，就算参数列表的数量，类型不一样，也无法定义同名的存储过程。
2.关于定义变量时，如果变量名和表中的列名不巧的同名，那么恭喜你，日后可能会踩坑。当语句中出现名词时，会优先找变量名，再去找列名。
例如这里，如果不在列名前添加表名的话，mysql就会把id当成我们传入的参数，而不是列名

CREATE PROCEDURE test(id INT)
BEGIN
IF(id = 1)
THEN
	UPDATE USER SET age=10 WHERE id = 1;
ELSEIF(id = 2)
THEN 
	UPDATE USER SET age=20 WHERE USER.id = 2;
ELSE
	UPDATE USER SET age=30;
END IF;
END;

3.begin … end 在存储过程中，就相当于限定方法体的{ } 括号。在循环while中就有用到

存储过程基础

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end

存储过程有三种类型，分别是in , out , inout。不加的话默认是in类型。

1. IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
2. out 类型参数：在调用存储过程中，可以改变其值，并可返回，类似java传递变量地址，所以可能会在方法中修改传入值
3. 可变变量INOUT:调用时可传入值，在调用过程中，可修改其值，同时也可返回值；也是in和out的结合

触发器

触发器的使用跟spring里的AOP大同小异，本质都是面向切面。可以吧一个触发器理解为一个切面，当你进行了一个动作后，触发器会根据你的动作做出不同响应，例如把你增删改的数据和时间给记录下来。

关键词是这些

DELIMITER ||
create trigger  触发器名字 after insert on 表名
    for each row 
    begin 
       语句块
    end;

接下来可以看几个例子

# user增加后，在表oplag记录
DELIMITER ||
CREATE TRIGGER tr_user_insert AFTER INSERT ON `user` 
FOR EACH ROW
BEGIN
	INSERT INTO `oplag`(userid,username,`action`,optime)
	VALUES(NEW.id,New.name,"insert",NOW());
END;

# 在删除user后，记录下来
DELIMITER ||
CREATE TRIGGER tr_user_delete11 AFTER DELETE ON `user` 
FOR EACH ROW
BEGIN
	INSERT INTO `oplag`(userid,username,`action`,optime)
	VALUES(OLD.id,OLD.name,"delete",NOW());
END;

# 在修改user后，记录下旧数据和新数据
DELIMITER ||
CREATE TRIGGER tr_user_update AFTER UPDATE ON `user` 
FOR EACH ROW
BEGIN
	INSERT INTO `oplag`(userid,username,`action`,optime)
	VALUES(OLD.id,OLD.name,"update_old",NOW());
	
	INSERT INTO `oplag`(userid,username,`action`,optime)
	VALUES(NEW.id,NEW.name,"update_new",NOW());
END;

关于DELIMITER ||这条语句和DELIMITER $$我也不清楚是什么意思，不过不加就会报错，我也很好奇

流程控制

1.if语句

关键词是这些

if() then...else...end if;

# 多条件判断
if() then...
elseif() then...
else ...
end if;

示例，没啥好讲的，感觉学过编程的人看一眼就知道怎么用了，需要注意的是then关键字不要写成than了，被坑了就难受了

DELIMITER $$
CREATE PROCEDURE test(id INT)
BEGIN
IF(id = 1)
THEN
	UPDATE USER SET age=10 WHERE USER.id = 1;
ELSEIF(id = 2)
THEN 
	UPDATE USER SET age=20 WHERE USER.id = 2;
ELSE
	UPDATE USER SET age=30;
END IF;
END;

2.case语句

关键词是这些

case ...
when ... then....
when.... then....
else ... 
end case;

随便放一段代码上来，和switch语句的使用差不多。大意就是users表中，根据userid获取status值，如果status为1，则修改score为10；如果status为2，则修改为20，如果status3，则修改为30；否则修改为40。

 create procedure testcate(userid int)
    begin 
        declare my_status int default 0;
        select status into my_status from users where id=userid;
 
        case my_status
            when 1 then update users set score=10 where id=userid;
            when 2 then update users set score=20 where id=userid;
            when 3 then update users set score=30 where id=userid;
            else update users set score=40 where id=userid;
        end case;
    end;

循环控制

1.while

关键字如下

while(表达式) do 
BEGIN
   ......  
END;
end while;

注意别漏了end while

DELIMITER $$
CREATE PROCEDURE whileTest()
BEGIN
   DECLARE i INT DEFAULT 0;
   WHILE(i<5) DO
   BEGIN
      SET i=i+1; # 相当于i++
      INSERT INTO `user`(NAME,age,email)
      VALUES(CONCAT("Alex",i),i+30,"@qq.com");
   END;
   END WHILE;
END;

2.do while

关键字

repeat...until...end repeat;

until , conj.直到…为止。很明显这就是场景的do while流程语句，至少先执行一遍再判断。do = repeat,until = while

DELIMITER $$
CREATE PROCEDURE repeatTest()
BEGIN
   DECLARE i INT default 1;
   REPEAT
      BEGIN
         SET i =i+1;
         UPDATE `user` SET age=age+1 WHERE user.id = 1;
      END;
   UNTIL i >=10
   END REPEAT;
END;

关于游标和自定义函数感觉没啥用，没有记录，以上