mysql如何编写存储过程文件 mysql写一个存储过程

文章目录

• 一、存储过程定义
• 二、存储过程特点
• 三、存储过程语法
• 四、定义变量
• 五、流程控制语句

• 1、条件控制语句

• 1、IF语句
• 2、case语句

• 2、循环控制语句

• 1、while语句
• 2、repeat
• 3、loop语句
• 4、leave语句
• 5、iterate

一、存储过程定义

存储过程（Stored Procedure）：一组为了完成特定功能的SQL语句集，存储在数据库中，经过一次编译后不需要再次编译。

二、存储过程特点

1、可以完成复杂的判断和运算

2、执行速度快

3、可重复使用

4、减少网络之间的数据传输，节省开销

通过代码调用存储过程时只需要传存储过程名称以及所需参数即可

三、存储过程语法

1、创建

delimiter $$ --定义存储过程结束标记，可自定义
--创建 使用procedure
create procedure [procedure_name]([param_type][param_name][data_type],...)
begin
		[存储体];--分号不可少
end $$
delimiter;

-- 创建存储过程示例
delimiter $$
create procedure getUserById(in userId int)
begin
	select * from tb_user where user_id=userId;
end $$
delimiter;

delimiter：表示sql语句执行的结束

参数类型说明：param_type[in|out|inout]

• IN：表示调用者需要对存储过程传入参数。
• OUT：表示调用者到一个或多个返回值。
• INOUT：表示调用者既要传入值，又要传出值。

2、删除

-- 删除procedure
drop procedure if exists [procedure_name];

-- 示例
drop procedure if exists getUserById;

3、调用

-- 无参
call procedure_name();
-- 有参
call procedure_name(param1,param2,...)

四、定义变量

变量声明语句必须放在存储体的开始部分

-- 定义变量  declare
-- 语法
-- declare [declare_name][type][default_value(可选)]
declare userId int default 66;
declare userId int;
-- 变量赋值
-- set declare_name=new_value;
set userId=77;

五、流程控制语句

1、条件控制语句

1、IF语句

语法

IF search_condition_1 THEN statement_list_1
    [ELSEIF search_condition_2 THEN statement_list_2] ...
    [ELSE statement_list_n]
END IF

实例

DROP PROCEDURE IF EXISTS get_max_num ;

DELIMITER $$
CREATE PROCEDURE get_max_num (IN a INT, IN b INT, OUT c) 
BEGIN
  IF a>b 
  THEN set c = a ;
  
  ELSE set c = b;
  
  END IF ;
  
END $$ 
DELIMITER ;

SELECT @a;
CALL get_max_num(3,4);
SELECT @a;

2、case语句

存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行

语法

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
Or: 

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

实例

-- case 语句的使用
DROP PROCEDURE test_case;

DELIMITER $$
CREATE PROCEDURE test_case (IN param INT) 
BEGIN
  DECLARE result VARCHAR (10) ;
  CASE param 
    WHEN param = 1 
    THEN SET result = "星期一" ;
    WHEN param = 2 
    THEN SET result = "星期二" ;
    WHEN param = 3 
    THEN SET result = "星期三" ;
    ELSE SET result =  "error";
  END CASE ;
  SELECT result AS "星期" ;
END $$ 
DELIMITER ;

CALL test_case(11);

2、循环控制语句

1、while语句

WHILE语句内的语句或语句群被重复，直至search_condition 为真。

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的

先判断，后执行

语法

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

实例

--  while语句
DROP PROCEDURE IF EXISTS test_while;
DELIMITER $$

CREATE PROCEDURE test_while (IN a INT)
BEGIN
  DECLARE param INT DEFAULT 5 ;
  SET param=a;
  SELECT "yyyy";
  WHILE
    param > 0 DO 
    SELECT 
      param ;
    SET param = param - 1 ;
    
  END WHILE ;
  
END $$
DELIMITER ;

2、repeat

REPEAT语句内的语句或语句群被重复，直至search_condition 为真。

先执行，后判断

语法

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

实例

-- repeat语句
DROP PROCEDURE IF EXISTS test_repeat;
DELIMITER $$
CREATE PROCEDURE test_repeat(IN param INT)
BEGIN
	SET @param=1;
	REPEAT 
	   SET @param=@param+1;
	   SELECT @param;
	UNTIL @param>param
	END REPEAT;
END $$
DELIMITER;

CALL test_repeat(2)

3、loop语句

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。

LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

语法

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

实例

-- loop语句
DROP PROCEDURE test_loop;
DELIMITER$$
CREATE PROCEDURE test_loop (IN param INT) 
BEGIN
  f_loop :
  LOOP
    IF param < 1 
    THEN LEAVE f_loop ;
    ELSE SET param = param - 1 ;
    SELECT 
      param ;
    END IF ;
  END LOOP f_loop ;
END $$

DELIMITER;
CALL test_loop(4);

4、leave语句

这个语句被用来退出任何被标注的流程控制构造。它和BEGIN … END或循环一起被使用

5、iterate

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END