存储过程

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，它可以有SQL语句和一些特殊的控制结构组成。


常用的操作数据库语言SQL语句在执行的时候需要先编译，然后执行。

存储过程经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数来调用执行它。

创建和执行存储过程

创建存储过程
CREATE PROCEDURE sp_name()
BEGIN
......
END
执行存储过程
CALL sp_name();


删除存储过程

drop procedure sp_name;

前期准备

create table employee(
    eno  int(6)  primary key,
    ename varchar(20),
    eage  int(6)
)
insert into employee values (1,"李一",18);
insert into employee values (2,"李二",19);
insert into employee values (3,"李三",20);
insert into employee values (4,"李四",21);

select * from employee;

创建存储过程pro_demo1
create procedure pro_demo1()
begin
  select eno,ename 
  from employee
  where eno=1;
end
执行存储过程

call pro_demo1();

变量声明

语法：
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。
要给变量提供一个默认值，可以使用DEFAULT子句，值可以被指定为一个表达式，也可以为一个常数。如果没有DEFAULT子句，初始值为NULL。
局部变量的作用范围在它被声明的BEGIN...END块内。

mysql的变量只能声明在存储过程中。

示例

创建存储过程pro_demo2
create procedure pro_demo2()
begin
   /*  声明变量*/
  declare empno int default 2;
  declare empname varchar(20) default   "demo2";
  /*  输出变量*/
  select empno,empname ;
end
执行存储过程

call pro_demo2();

给变量赋值

语法：
-声明时默认赋值
DECLARE var_name[,...] type [DEFAULT value]


-为变量直接赋值
SET var_name1=value1[, var_name2=value2];


-将查询结果赋值给变量
SELECT ... INTO var_name

要求：SELECT命令返回并且只能返回一条记录，且字段数匹配。（不允许多条记录）

示例

创建存储过程pro_demo3
create procedure pro_demo3()
begin
    declare empno int default 2;
    declare empname varchar(20) default "demo2";
    declare empage int default 18;
       /*赋值*/
    select eno,ename from employee where eno=3
    into empno,empname;
    set empage=26;
    select empno,empname,empage ;
end
执行存储过程
call pro_demo3();

条件判断

语法：
if 条件 then
   statement
else
   statement
end if

示例

创建存储过程pro_demo4
create procedure pro_demo4()
begin
  declare empage int default 0;
  select eage from employee where ename='李四' into empage;
   if empage>=20 then
         select "恭喜李四已经符合本岗位的年龄要求" as 结果;
   else 
         select "很遗憾李四不符合本岗位的年龄要求" as 结果;
   end if;
end


执行存储过程

call pro_demo4();

循环结构

语法：
while循环
WHILE expression DO
  statements
END WHILE


repeat循环
REPEAT
  statements
UNTIL expression

END REPEAT

示例

创建存储过程pro_demo5_1
create procedure pro_demo5_1()
begin
    declare count int;
    declare empno int default 0;
    select max(eno) from employee into count;
    repeat
       set empno=empno+1;
       until empno=count
    end repeat;
    set empno=empno+1;
    insert into employee values(empno,'李五',22);
    select * from employee where eno=empno;
end
执行存储过程
call pro_demo5_1();

示例

创建存储过程pro_demo5_2
create procedure pro_demo5_2()
begin
    declare count int;
    declare empno int default 0;
    declare empname varchar(20) character set utf8;
    set empname='李六';
    select max(eno) from employee into count;
    while empno<count do
set empno=empno+1;
      end while;
    set empno=empno+1;
    insert into employee values(empno,empname,23);
    select * from employee where eno=empno;
end
执行存储过程

call pro_demo5_2();

带参数的存储过程

语法：
MySQL在定义存储过程时还可以使用参数。
CREATE PROCEDURE([[IN|OUT|INOUT]参数名 参数类型...])
共有三种参数类型，IN|OUT|INOUT
 -IN:输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，默认为IN。
 -OUT:输出参数:该值无法调用时指定，可在存储过程内部被改变，并可返回。
 -INOUT输入输出函数:调用时指定，并可被改变和返回。

语法：
MySQL在定义存储过程时还可以使用参数。
CREATE PROCEDURE([[IN|OUT|INOUT]参数名 参数类型...])
共有三种参数类型，IN|OUT|INOUT
 -IN:输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，默认为IN。
 -OUT:输出参数:该值无法调用时指定，可在存储过程内部被改变，并可返回。

 -INOUT输入输出参数:调用时指定，并可被改变和返回。

IN参数示例：((形参)必须接收对方(实参)传来的值，但(形参)自己的变化不会影响对方(实参))

创建存储过程pro_demo6_1
create procedure pro_demo6_1(in num int)
begin
  set num=20;
end


create procedure pro_use_demo6_1()
begin
  declare x int default 10;
  call pro_demo6_1(x);
  select x;
end
执行存储过程

call pro_use_demo6();

OUT参数示例((形参)不接收对方(实参)传来的值，但(形参)自己的变化会影响对方(实参))

创建存储过程pro_demo6_2
create procedure pro_demo6_2(out num int)
begin
/*1:输出为NULL2:注释掉，输出x=20*/
  select concat("num=",num) as result; 
  set num=20;
end


create procedure pro_use_demo6_2()
begin
  declare x int default 10;
  call pro_demo6_2(x);
  select x;
end
执行存储过程

call pro_use_demo6_2();

INOUT参数示例:((形参)既接收对方(实参)传来的值，(形参)自己的变化还会影响对方(实参))

创建存储过程pro_demo6_3
create procedure pro_demo6_3(inout num int)
begin
/*1:输出为num=10,2:注释掉，输出x=20*/
  select concat("num=",num) ; 
  set num=20;
end


create procedure pro_use_demo6_3()
begin
  declare x int default 10;
  call pro_demo6_3(x);
  select x;
end
执行存储过程

call pro_use_demo6_3();

存储过程的优点

存储过程只在创建时编译，使用存储过程可以提高数据库的执行速度。


当对数据库进行复杂操作时，如对多个表进行增，删，改，查，可以将复杂的操作封装在存储过程中。


存储过程可以重复使用，可以减少数据库开发人员的工作量。


安全性高，可以限定只有少数用户对存储过程的使用权。