3.1 概述

  • 存储过程和函数是事先编译并存储在数据库中的一段SQL语句的集合
  • 存储过程没有返回值
  • 存储函数有返回值
  • 优点
    • 减少数据在数据库和应用服务器之间的传输，提高处理效率

3.2 创建存储过程

  1. CREATE PROCEDURE procedure_name(参数)
  2. begin
  4. -- SQL语句
  6. end;//需要指定分隔符
  1. delimiter $
  3. create procedure pro_test01()
  4. begin
  5.     select * from a;//不会;立即执行了
  6. end$
  8. delimiter ;//复原

3.3 调用存储过程

  1. call pro_test01();

3.4 查看存储过程

  • 查找proc表

    1. select name from mysql.proc where db = 'db_name';

  • 查询存储过程的状态信息

    1. show procedure status\G;

  • 查询某个存储过程的定义

    1. show create procedure db_name.pro_test01\G;

    3.5 删除存储过程

    1. drop procedure pro_test01;

    3.6 语法结构

  • 存储过程是可以编程的，意味着可以使用变量、表达式、控制结构来完成比较复杂的功能。

3.6.1 变量

  • DECLARE

    • 定义一个局部变量

    • 作用范围只能在BEGIN…END块中 ```sql delimiter $ create procedure test02() begin

      declare num int default 10; select concat(‘num:’,num); end$

delimiter ;

  2. - SET
  3.    - 给变量赋值
  4.    - 可以赋值常量或表达式
  5. ```sql
  6. delimiter $
  7. create procedure test03()
  8. begin
  10.     declare name varchar(20);
  11.   set name = 'mysql';
  12.   select name;
  13. end$
  15. delimiter ;

  • SELECT INTO ```sql delimiter $ create procedure test03() begin

    declare num int; select count(*) into num from city; select concat(“city表中的记录数:”,num); end$

delimiter ;

call test03();

  1. <a name="J9rr9"></a>
  2. ### 
  3. <a name="Xvk2J"></a>
  4. ### 3.6.2 IF条件判断
  6. ```sql
  7. delimiter $
  9. crete procedure test04()
  10. begin
  12.     declare height int default 175;
  13.   declare description varchar(50) default '';
  15.   if hegiht >= 180 than 
  16.       set description = 'tall';
  17.   elseif height>=170 and height <180 than
  18.       set description = 'ok';
  19.   else
  20.       set description = 'short';
  21.   end if;
  23.   select height,description;
  24. end$

3.6.3 传递参数

  • IN
    • 输入参数（默认） ```sql delimiter $

crete procedure test05(IN height int) begin

declare description varchar(50) default ‘’;

if hegiht >= 180 than set description = ‘tall’; elseif height>=170 and height <180 than set description = ‘ok’; else set description = ‘short’; end if;

select height,description; end$

call test05(175);

  2. - OUT 
  3.    - 输出参数（返回值）
  4.    - @varname：用户会话变量，类似于全局变量
  5.    - @@global：系统变量
  6. ```sql
  7. delimiter $
  9. crete procedure test05(IN height int,OUT description varchar(10))
  10. begin
  12.   if hegiht >= 180 than 
  13.       set description = 'tall';
  14.   elseif height>=170 and height <180 than
  15.       set description = 'ok';
  16.   else 
  17.       set description = 'short';
  18.   end if;
  20. end$
  22. call test05(175,@description);
  24. select @description;
  26. set @name='aaa';
  • INOUT
    • 既是输出又是输入

3.6.4 case结构

  1. delimiter $
  2. create procedure pro_test7(month int)
  3. begin
  5.     declare result varchar(10);
  6.   case
  7.           when month >= 1 and month<=3 then
  8.           set result = "第一季度";
  9.       when month >= 4 and month<=6 then
  10.           set result = "第二季度";
  11.       when month >= 7 and month<=9 then
  12.           set result = "第三季度";
  13.       else
  14.           set result = "第四季度";
  15.   end case;
  16.   select result;
  18. end$

3.6.5 while

  • 满足条件循环
    • while [statement] do…end while; ```sql delimiter $

create procedure pro_test8(n int) begin

  1. declare totle int default 0;

declare num int default 1;

  1. while  num<=n do
  2.   set total = total+num;
  3. set num = num+1;

end while;

select total;

end$

  1. <a name="BdUAR"></a>
  2. ### 
  3. <a name="bmyOz"></a>
  4. ### 3.6.6 repeat
  6. - 满足条件退出循环
  7.    - repeat [statement] util... end repeat;
  8. ```sql
  9. delimiter $
  11. create procedure pro_test9(n int)
  12. begin
  14.     declare totle int default 0;
  16.   repeat
  17.           set total = total+n;
  18.       set n = n-1;
  19.       until n = 0 -- ！！！！！！！！！！！！！没有分号！！！！！！！！！！！！！！！
  20.   end repeat;
  22.   select total;
  24. end$

3.6.7 loop

  • 退出循环需要其他语句定义（LEAVE）
    • c:loop
      • [statement]
      • if [statement] then
        • leave c;
      • end if;
    • end loop c; ```sql delimiter $

create procedure pro_test10(n int) begin

  1. declare totle int default 0;

c:loop

  1.     set total = total+n;
  2. set n = n-1;
  3. if n<0 then
  4.     leave c;
  5. endif;

end loop c;

select total;

end$

  1. <a name="jwuE6"></a>
  2. ### 
  3. <a name="kA9YV"></a>
  4. ### 3.6.8 游标/光标
  6. - 用来**存储查询结果集**的数据类型，在存储过程和函数中可以使用光标**对结果集进行循环处理**。
  8. - 声明光标（DECLARE）
  9. - OPEN光标
  10. - FETCH光标
  11.    - 每抓取一条数据，fetch指针向下移动一个
  12. - CLOSE光标
  13. ```sql
  14. create procedure pro_test01()
  15. begin
  17.     declare eid int(11);//和表中的类型要一致
  18.   declare e_name varchar(50);
  20.     declare emp_result cursor for select * from emp;
  21.   open emp_result;
  22.   fetch emp_result into e_id,e_name;//每fetch一次读取一行
  23.   select concat('id=',e_id,'name=',e_name);
  24.   close emp_result;
  26. end$
  28. call pro_test01();//读取一行
  1. create procedure pro_test01()
  2. begin
  4.     declare eid int(11); -- 和表中的类型要一致
  5.   declare e_name varchar(50);
  6.   declare has_data int default 1; -- has_data==0，退出循环
  8.   declare emp_result cursor for select * from emp;
  9.   declare exit HANDLER for not found set has_data=0; --退出条件写在定义游标之后
  11.   open emp_result;
  13.   repeat 
  14.       fetch emp_result into e_id,e_name;//每fetch一次读取一行
  15.       select concat('id=',e_id,'name=',e_name);
  16.     until has_data = 0
  17.   end repeat;
  19.   close emp_result;
  21. end$
  23. call pro_test01(); -- 循环读取

3.7 存储函数

  • 类似，有返回值 ```sql create function func1(countryID int) returns int begin declare cnum int; select count(*) into cnum from city where country_id = countryID; return cnum; end$

select func1(1);

drop function func1; ```