存储过程学习小记 mysql

1、概念

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2、优点

存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

3、缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

2、创建语法

drop procedure if exists procedure_name;
delimiter ;; //暂把分隔符改为;;，可为其他合法的任意非;符号
//存储过程开始
create procedure procedure_name()
begin
//代码块中使用英文分号作为分隔符
end
;;
delimiter ; //把分隔符修改为英文分号

3、调用

call procedure_name();

4、参数简介

在MySQL中，参数有三种模式：IN，OUT或INOUT。

IN - 是默认模式。在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。另外，IN类型的参数的值是只读的。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
OUT - 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。out类型的参数是形参，默认形参值为null。
INOUT - INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。

5、实例

（1）in参数实例

drop procedure if exists pro_param_in;
delimiter ;;
create procedure pro_param_in(in num int)
begin
declare number int ;
set number = num;
select number;
end
;;
delimiter ;
set @num=5;
call pro_param_in(@num);

运行结果：

存储过程学习小记 mysql

（2）out参数实例

drop PROCEDURE if EXISTS pro_on_out;
delimiter ;;
create PROCEDURE pro_on_out(out out_num int)
BEGIN
	select out_num; //形参，初始化为null
	set out_num = 64;
	select out_num;
END
;;
delimiter ;
set @num = 4;
call pro_on_out(@num);

运行结果：

存储过程学习小记 mysql

（3）inout参数实例

drop PROCEDURE if EXISTS pro_on_inout;
delimiter ;;
create PROCEDURE pro_on_inout(inout inout_num int)
BEGIN
	select inout_num;
	set inout_num = 200;
	select inout_num;
END
;;
delimiter ;
set @num = 99;
call pro_on_inout(@num);

运行结果：

存储过程学习小记 mysql

（4）if else实例

drop procedure if exists pro_else;
delimiter ;;
create procedure pro_else(in id int)
begin
    if id > 0 then
        select '> 0' as id;
    else if id = 0 then
        select '= 0' as id;
    else
        select '< 0' as id;
    end if;
end
;;
delimiter ;
set @p=-12;
call pro_else(@p);

运行结果：

存储过程学习小记 mysql

（5）case when语句实例

drop procedure if exists pro_case;
delimiter ;;
create procedure pro_case(id int)  
begin  
    case id  
    when 1 then     
    select 'one' as res;  
    when 2 then  
    select 'two' as res;  
    when 3 then   
    select 'three' as res;  
    else  //else必须有，否则when不到true值时会报错，else中如果没有逻辑代码可使用begin end代替
    select 'no val' as res;  
    end case;  
end
;;
delimiter ;
set @id=2;
call pro_case(@id);

运行结果：

存储过程学习小记 mysql

6、查询某个数据库中的所有存储过程

show procedure status where db = 'db_name';

实例结果：

存储过程学习小记 mysql

7、查看某个存储过程的信息

show create procedure db_name.procedure_name;

存储过程学习小记 mysql

8、修改存储过程

可以先删除，再重新创建

存储过程学习小记 mysql

相关推荐