mysql--存储过程实战

一、存储过程格式

      desc student
      delimiter //
      CREATE PROCEDURE 名称（参数列表）
      BEGIN
     	SQL语句块
      END
      //
      delimiter ;
  
 

注意：

• 存储名称的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。
• 参数类型要改变，可在参数名之前使用关键词 OUT或INOUT

二、创建存储过程

例子：  

      delimiter //
      CREATE PROCEDURE pro1()
      BEGIN
      select * from student;
      END
      //
      delimiter ;
      call pro1();
  
 

解析：

（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔 符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当 前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。 
（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用”,”分割开。 
（3）过程体的开始与结束使用BEGIN与END进行标识。 

三、存储过程定义

• 声明变量： declare 变量名  数据类型 
• 参数类型：

 

1. in参数：输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
2. OUT 输出参数:该值可在存储过程内部被改变，并可返回 
3. INOUT 输入输出参数:调用时指定，并且可被改变和返回

四、无参数存储实例

• 注意存储过程写好后，需要先运行一下。保存到数据库后，才可以调用这个存储过程，否则在调用的时候会报错，提示存储过程不存在。

      delimiter //
      CREATE PROCEDURE pro1()
      BEGIN
      select * from student;
      END
      //
      delimiter ;
      call pro1();  //调用无参数的存储过程
  
 

五、有参数存储过程实例

• 参数类型为 in

      delimiter //
      CREATE PROCEDURE pro2(s_sex VARCHAR(10))
      BEGIN
      select * from student where sex=s_sex;
      END
      //
      delimiter ;
      set @s_sex='男';
      CALL pro2(@s_sex);
  
 

pro2存储过程使用了in类型的参数。在调用pro2存储过程时候，需要先给s_sex参数赋值（set @s_sex='男';），然后再调用pro2存储过程时将s_sex传入进去（CALL pro2(@s_sex);）。

• 参数类型为 out

      delimiter //
      CREATE PROCEDURE pro3(out s_name varchar(20))
      BEGIN
      DECLARE s_name varchar(12);
      DECLARE department varchar(20);
      set s_name='测试1';
      set department='测试地址1';
      INSERT INTO student (s_name,department) values(s_name,department);
      END
      //
      delimiter ;
  
 

      set @s_name='张三强';
      CALL pro3(@s_name);
      select * from student;
  
 

测试结果：

调用pro3存储过程时给s_name参数赋值，但是在运行存储过程时，s_name参数值被改变了，说明了out类型的该值可在存储过程内部改变，并可以返回.往往是用于获取存储过程里的参数值

• 参数类型为inout

      delimiter //
      CREATE PROCEDURE pro4(out s_name varchar(12),INOUT sex varchar(6),IN department varchar(13))
      BEGIN
      DECLARE sex VARCHAR(6)DEFAULT('男');
      set s_name='王明';
      set department='雄安新区';
      INSERT INTO student(s_name,sex,department)VALUES (s_name,sex,department);
      END
      //
      delimiter ;
  
 

      set @s_name='张三';
      set @department='北京';
      set @sex='女';
      CALL	pro4(@s_name,@department,@sex);
      select * from student;
  
 

运行结果：

调用pro4存储过程，sex参数为inout类型在存储过程运行过程中改变了值的结果。

六、存储过程if判断语句

• if条件使用格式

      if 条件 then
          执行语句
      elseif 条件 then
          执行语句
      else
          执行语句
      end if;
  
 

存储过程if判断实例

      delimiter //
      CREATE PROCEDURE pro5(sex varchar(12))
      BEGIN
      DECLARE s_name varchar(12);
      DECLARE department varchar(21)DEFAULT '北京';
      IF sex='男' THEN
      set s_name='张三';
      INSERT INTO student (s_name,department) VALUES (s_name,department);
      ELSE
      set s_name='张丽丽';
      INSERT INTO student (s_name,sex,department) VALUES (s_name,sex,department);
      END IF;
      END
      //
      delimiter ;
  
 

pro5存储过程判断sex参数男，女。来改变插入数据的s_name名称。如果sex为男，则插入数据的s_name=张三。如果sex为女，则插入数据的s_name=张丽丽。

    ①、 sex=男，执行结果

      set @sex='男';
      CALL pro5(@sex);
      select * FROM student;
  
 

    ②、sex=女，执行结果

      set @sex='女';
      CALL pro5(@sex);
      select * FROM student;
  
 

七、存储过程while循环

      while  条件 do
         sql语句
      end while;
  
 

• while循环实例

      delimiter //
      CREATE PROCEDURE pro6(count int)
      BEGIN
      DECLARE s_name varchar(12);
      DECLARE department varchar(20);
      DECLARE i int; -- 设置一个自增长变量i
      set i=1;			-- 自增长i赋值
      WHILE i<=count DO
      SELECT CONCAT('变量i的值=',i);  -- 查看每次循环i的值
      set s_name=CONCAT('测试',i); -- CONCAT()是个函数
      set department='测试地址1';
      INSERT INTO student (s_name,department) values(s_name,department);
      set i=i+1;  -- 自增长i加1
      END WHILE;
      END
      //
      delimiter ;
  
 

CALL pro6(3);
 

pro6存储过程使用了while循环，在调用的时候需要传入循环的次数，结果会创建相应次数的数据。

八、查询存储过程

• 查询有多少个存储过程：

SHOW PROCEDURE STATUS;
 

•    查询某个存储过程详细信息

      SHOW CREATE PROCEDURE 存储过程名称;
      select * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='存储过程名称';
  
 

文章来源: brucelong.blog.csdn.net，作者：Bruce小鬼，版权归原作者所有，如需转载，请联系作者。

原文链接：brucelong.blog.csdn.net/article/details/79786539