文章目录

  • 定义
  • 作用
  • MySQL写法
  • 输入in和输出out
  • 存储过程的传出参数IN
  • 存储过程条件语句
  • 存储过程循环语句
  • 存储过程游标
  • 触发器


定义

存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。

作用

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
复杂操作可将存储过程封装起来与数据库提供的事务处理结合一起使用。
重复使用,可减少数据库开发人员的工作量。
可以指定只有部分用户具有对存储过程的使用权。

MySQL写法

create procedure 名称()

begin
.........;
end;

call 名称();
create procedure test2()

begin
  -- 使用 declare语句声明一个变量,一句declare只声明一个变量，变量必须先声明后使用.变量具有数据类型和长度，与mysql的SQL数据类型保持一致
  declare username varchar(32) default '';
  -- 使用set语句给变量赋值,变量可以通过set来赋值，也可以通过select into的方式赋值
  set username='a';
  -- 将users表中id=1的名称赋值给username，可以select into后直接获得into的变量的值，也可以再用一个select返回变量值
  select name into username from users where id=1;
  -- 返回变量
  select username;
end;

要注意的是begin和end可以多重嵌套，存储过程中变量的作用域，作用范围在begin和end块之间，end结束变量的作用范围即结束：

begin
 -- 有多个begin-end嵌套时第一个begin下的变量可以视作全局变量
declare username varchar(32) default '';
begin

end;
end;

输入in和输出out

存储过程的传出参数IN

说明：
1传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。
2IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
3如果调用存储过程中需要修改和返回值，可以使用OUT类型参数
4传出参数out：在调用存储过程中，可以改变其值，并可返回，不能用于传入参数值；
5调用存储过程时，out参数也需要指定，但必须是变量，不能是常量；
6如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

create procedure mypro(in a int,in b int,out sum int)
begin
set sum = a+b;
end;

call mypro(1,2,@sum);-- 调用存储过程，传参如果不是常数而是变量，那么创建、赋值、传参都要用到@
select @sum as sum;-- 显示过程输出结果，如果不想带@，用as即可换名

存储过程条件语句

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


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


-- case 条件语句
create procedure test(in num int)
begin
case -- 条件开始
when num<0 then select '负数';
when num=0 then select '不是正数也不是负数';
else select '正数';
end case; -- 条件结束
end;
-- 调用过程
call test(1);

存储过程循环语句

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


repeat...until...end repeat;

存储过程游标

游标是保存查询结果的临时区域，保存了查询的临时结果，实际上就是结果集

declare xxx_cur cursor for 条件语句（比如select name from users where id%2=0）;

触发器

触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作。
下面的代码功能是创建一个触发器users_insert，每次对users表进行插入后都要进行一次对oplog表的插入操作

-- create trigger xxx before/after 对表操作(insert/update/delete on 表名)
create trigger users_insert after insert on users
    for each row 
    begin
        insert into oplog(userid,username,action,optime)
        values(NEW.id,NEW.name,'insert',now());
    end;