mysql存储过程

发布时间:2022-01-16 02:34:04 作者:路人甲 阅读(112)

存储过程可以理解为一个方法,里面可以包含业务逻辑、查询语句;

首先创建一个简单的存储过程

  1. #如果存储过程无法执行,请去掉前面的空格
  2. DELIMITER $$
  3. # 定义存储过程,名称为 no_param 无参数
  4. CREATE PROCEDURE no_param()
  5. # begin ··· end 之间的数据为存储过程主体内容
  6. BEGIN
  7. #定义变量,默认值为0
  8. DECLARE p_out INT DEFAULT 0;
  9. # 变量赋值
  10. SET p_out= 22;
  11. # 输出
  12. SELECT p_out;
  13. END;
  14. $$
  15. DELIMITER ;
  16. # 调用存储过程
  17. CALL no_param();
输出结果

png

分隔符 $$

MySQL默认以”;”为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER ”声明当前段分隔符,让编译器把两个””之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER ;”的意为把分隔符还原。

带输入参数的存储过程

  1. #如果存储过程无法执行,请去掉前面的空格
  2. DELIMITER $$
  3. #in 参数
  4. CREATE PROCEDURE in_param(IN p_in int)
  5. BEGIN
  6. SELECT p_in;
  7. END;
  8. $$
  9. DELIMITER ;
  10. # 调用
  11. CALL in_param(44)
输出结果

44

带输出参数的存储过程

  1. DELIMITER $$
  2. CREATE PROCEDURE out_param(OUT p_out int)
  3. BEGIN
  4. SET p_out= 88 ;
  5. END;
  6. $$
  7. DELIMITER ;
调用输出存储过程
  1. SET @p_out=1;
  2. CALL out_param(@p_out);
  3. SELECT @p_out;

配合查询语句

  1. DELIMITER $$
  2. CREATE PROCEDURE out_param(OUT p_out INT)
  3. BEGIN
  4. #SET p_out= (select count(1) from t_home_article) ;
  5. # 或者这样
  6. SELECT COUNT(1) INTO p_out FROM t_home_article;
  7. END;
  8. $$
  9. DELIMITER ;

条件语句 IF-THEN-ELSE

语句表达式

IF (条件) THEN {条件结果为true执行} ELSE {条件结果为false执行} END IF

使用到存储过程
  1. DELIMITER //
  2. CREATE PROCEDURE proc3(IN parameter int)
  3. BEGIN
  4. DECLARE var int;
  5. SET var=parameter+1;
  6. IF var=0 THEN
  7. INSERT INTO t VALUES (17);
  8. END IF ;
  9. IF parameter=0 THEN
  10. UPDATE t SET s1=s1+1;
  11. ELSE
  12. UPDATE t SET s1=s1+2;
  13. END IF ;
  14. END ;
  15. //
  16. DELIMITER ;

条件语句 CASE-WHEN-THEN-ELSE

语句表达式

相当于程序里面的switch关键字的作用

使用方式
  1. DELIMITER //
  2. CREATE PROCEDURE proc4 (IN parameter INT)
  3. BEGIN
  4. DECLARE var INT;
  5. SET var=parameter+1;
  6. CASE var
  7. WHEN 0 THEN
  8. INSERT INTO t VALUES (17);
  9. WHEN 1 THEN
  10. INSERT INTO t VALUES (18);
  11. ELSE
  12. INSERT INTO t VALUES (19);
  13. END CASE ;
  14. END ;
  15. //
  16. DELIMITER ;

循环语句 WHILE-DO…END-WHILE

  1. DELIMITER //
  2. CREATE PROCEDURE proc5()
  3. BEGIN
  4. DECLARE var INT;
  5. SET var=0;
  6. WHILE var<6 DO
  7. INSERT INTO t VALUES (var);
  8. SET var=var+1;
  9. END WHILE ;
  10. END;
  11. //
  12. DELIMITER ;

循环语句 REPEAT…END REPEAT

此语句的特点是执行操作后检查结果

  1. DELIMITER //
  2. CREATE PROCEDURE proc6 (IN num INT)
  3. BEGIN
  4. DECLARE v INT;
  5. SET v=0;
  6. REPEAT
  7. INSERT INTO t VALUES(v);
  8. SET v=v+1;
  9. # UNTIL 表达式结果为true时退出循环
  10. UNTIL v>num END REPEAT;
  11. SELECT v;
  12. END;
  13. //
  14. DELIMITER ;

循环语句 LOOP…END LOOP

类似c语言的 loop ··· goto loop 语句

  1. DELIMITER //
  2. CREATE PROCEDURE proc7 ()
  3. BEGIN
  4. DECLARE v INT;
  5. SET v=0;
  6. LOOP_LABLE:LOOP
  7. INSERT INTO t VALUES(v);
  8. SET v=v+1;
  9. IF v >=5 THEN
  10. -- v大于等于5时退出循环
  11. LEAVE LOOP_LABLE;
  12. END IF;
  13. END LOOP;
  14. END;
  15. //
  16. DELIMITER ;
LABLES标号

标号可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。

删除存储 过程

  1. #直接删除,如果过程不存在则会报错
  2. DROP PROCEDURE xxx;
  3. # 如果存储过程存在则删除,不存在不会删除,不报错,会有警告
  4. DROP PROCEDURE IF EXISTS xxx;

另外,这里在说下存储过程和函数的区别

  • 函数限制比较多,如不能用临时表,只能用表变量等,而存储过程的限制相对就比较少。
  1. 一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。
  2. 对于存储过程来说可以返回参数,而函数只能返回值或者表对象。
  3. 存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。
  4. 当存储过程和函数被执行的时候,SQLManager会到procedurecache中去取相应的查询语句,如果在procedurecache里没有相应的查询语句,SQLManager就会对存储过程和函数进行编译。
  • Procedurecache:中保存的是执行计划,当编译好之后就执行procedurecache中的executionplan,之后SQLSERVER会根据每个executionplan的实际情况来考虑是否要在cache中保存这个plan,评判的标准一个是这个executionplan可能被使用的频率;其次是生成这个plan的代价,也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

关键字数据库