文章目录

• 一、存储过程定义
• 二、存储过程特点
• 三、存储过程语法
• 四、定义变量
• 五、流程控制语句
• • 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 $$ --定义存储过程结束标记，可自定义--创建 使用procedurecreate procedure [procedure_name]([param_type][param_name][data_type],...)begin[存储体];--分号不可少end $$delimiter;-- 创建存储过程示例delimiter $$create procedure getUserById(in userId int)beginselect * from tb_user where user_id=userId;end $$delimiter;

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

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

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

2、删除

-- 删除proceduredrop 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 CASEOr: 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_listEND 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     SELECTparam ;    SET param = param - 1 ;      END WHILE ;  END $$DELIMITER ;

2、repeat

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

先执行，后判断

语法

[begin_label:] REPEAT    statement_listUNTIL search_conditionEND REPEAT [end_label]

实例

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

3、loop语句

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

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

语法

[begin_label:] LOOP    statement_listEND 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 ;    SELECTparam ;    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