mysql存储过程与存储示例

定义：就是数据库 SQL 语言层面的代码封装与重用。

 

mysql存储过程使用场景：

减少客户端和服务器的网络运输，降低网络负载。适合复杂的业务逻辑需要多条sql的业务逻辑。

 

优点:

1、与sql不同，存储过程只在创建的时候进行编译，以后每次执行存储过程都不需要编译，因此，可以大大提高数据库的执行速度；

2、创建一次就可以重复使用，减少开发人员的工作量；

3、安全性能高，可以屏蔽对底层数据库对象的直接访问；

缺点

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

 

声明存储过程:

MYSQL 存储过程中的关键语法

声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

CREATE PROCEDURE demo_in_parameter(IN p_in int)  

存储过程开始和结束符号:

BEGIN .... END    

变量赋值:

SET @p_in=1  

变量定义:

DECLARE l_int int unsigned default 4000000; 

创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

存储过程体:

create function 存储函数名(参数)

二、存储过程的参数

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

二、存储过程的创建和调用

• 存储过程就是具有名字的一段代码，用来完成一个特定的功能。
• 创建的存储过程保存在数据库的数据字典中。

1、in 输入参数

DELIMITER //
 CREATE PROCEDURE in_param(IN p_in int)
 BEGIN
   SELECT p_in;
   SET p_in=2;
   SELECT p_in;
 END
//
DELIMITER;



set @p_in=6;
call in_param(@p_in);

2、out 输出参数

 

DELIMITER //
 CREATE PROCEDURE out_param(Out p_out int)
 BEGIN
   SELECT p_out;
   SET p_out=2;
   SELECT p_out;
 END;
//
DELIMITER;

set @p_out=6;
call out_param(@p_out);

 

 

3、inout 输入输出参数

INOUT 输入输出参数
DELIMITER //
 CREATE PROCEDURE inout_param(INOUT p_inout int)
 BEGIN
    SELECT p_inout;
    SET p_inout=4;
    SELECT p_inout;
 END;
//
DELIMITER;

set @p_inout=5;
call inout_param(@p_inout);

 

4.1变量定义与赋值

DECLARE variable_name datatype [DEFAULT value];

 

select "hello world" into @name;
select @name;

create PROCEDURE GreetWorld() 
BEGIN
select concat(@greeting,"world");
END;

SET @greeting="";
call GreetWorld();

create PROCEDURE p1() set @p_param="p1";
create PROCEDURE P2() select CONCAT("the last procedure is",@p_param);
call p1();
call P2();

 

示例1：数据库操作

 

DELIMITER //
CREATE PROCEDURE getAddress(
  OUT  region_id int,
  OUT  region_name varchar(25)
)
  BEGIN
  SELECT regionid into region_id  FROM t_area_region limit 1;
  SELECT region into region_name FROM t_area_region limit 1;
  END;
//
DELIMITER;



CALL getAddress(@region_id,@region_name);
SELECT  @region_id;
SELECT  @region_name;

 

 

删除mysql存储过程

drop PROCEDURE GreetWorld;

 

 

查询数据库包含的存储过程

SELECT name FROM mysql.proc where db="jiapoint";
show create PROCEDURE jiapoint.getAddress;

 

 

 

 

 

示例二：操作数据库 if else

DELIMITER //
create PROCEDURE if_proc(IN if_in int)
BEGIN
   DECLARE var int;
   set var=if_in+1;
   IF var=2 THEN 
   insert into t_area_region(regionid,region,father) values ("44030501","石盐","440305");
   END IF;
   IF var=3 THEN
   update t_area_region set region="建安" where rid=1;
   ELSE
   delete from t_area_region where rid=1;
   END IF;
END;
//
DELIMITER;

运行

set @if_in=0;
call if_proc(@if_in);